May 12, 2020 at 1:17 pm
Hi All,
I have doubt on database backups. I usually use below query to know the estimation completion time of the backup process.
When I take a Full database backup of one of our app database which is around 3TB out of which .ldf file size is 1059 GB.
As and when the backup is getting completed, the query shows [PercentComplete] = 100% and [Time remaining [hh:mm:ss] = 0:00:00.
My question is, even after showing 100% completion time, for my database it takes extra 15-20 mins to actually complete the backup and the spid to disappear. Want to know, does the db engine does something extra at the time of backup completion? Is it something to do with the transaction log? how to track this time or is there a way to see that in errorlog or any trace flag needs to be turned on
to get that information?
SQL Server version is : SQL Server 2012 Enterprise Edition SP4
Query to track backups and restore time.
======================================
SELECT sysdb.NAME,
dmv.PERCENT_COMPLETE AS [PercentComplete],
dmv.ESTIMATED_COMPLETION_TIME/60000 AS [Time_Remaining_in_Minutes],
CONVERT(varchar(12), DATEADD(minute,dmv.ESTIMATED_COMPLETION_TIME/60000,0), 114) as "Time remaining [hh:mm:ss]",
[sql text] = SUBSTRING (qt.text, dmv.statement_start_offset/2, (CASE WHEN dmv.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE dmv.statement_end_offset END - dmv.statement_start_offset)/2),
[Parent Query] = qt.text
FROM MASTER..SYSDATABASES sysdb
inner join sys.dm_exec_requests dmv on sysdb.DBID=dmv.DATABASE_ID AND (dmv.COMMAND LIKE '%backup%' or dmv.COMMAND LIKE '%restore%')
CROSS APPLY sys.dm_exec_sql_text(dmv.sql_handle)as qt
ORDER BY 2 desc, 3 desc
go
Thanks,
Sam
May 12, 2020 at 1:49 pm
The backup process marks a checkpoint and then begins copying data pages. This is what you see for progress and estimate. At the end of the data pages being copied, another checkpoint (or marker), and the process must now copy log records from the checkpoint until the data pages were completed. This is to ensure that any changes made during the backup can be rolled forward or rolled back, depending on the state of the transaction when the backup copy finishes.
If you have a lot of activity during the backup, there may be lots of log records to copy over.
https://www.sqlskills.com/blogs/paul/debunking-a-couple-of-myths-around-full-database-backups/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply