Here I am explaining a very general scenario about database backup.
Suppose if we have a large database. The Backup process of this database will take long time but we just want to know how much percent of that backup is completed at the time of Backup process is running.
When we take the backup from Management Studio then it shows the amount of percentage completed in the bottom – left corner progress section.
But what happen, when we take the backup of the database using TSQL.
I have a database GEEKS of 10 GB. When we execute the backup command as mention below..
BACKUP DATABASE GEEKS TO DISK='M:\DATABASE\GEEKS.bak'
Then SSMS shows no information about how much backup process is completed. As shown below..
To know such type of information microsoft provides a special type of DMV “sys.dm_exec_requests”. This DMV provides many columns data to show various information. here we just want to know only the value of column “percent_complete”. So we can execute this command..
select percent_complete,* from sys.dm_exec_requests
where command='BACKUP DATABASE'
Output of this query is shown below…