tim laqua dot com Thoughts and Code from Tim Laqua

25Sep/092

Determining when RESTORE DATABASE command will complete (SQL Server 2008)

Ah, I see you just started restoring that 1TB monster and now everyone wants to know when it's going to be finished, where you're at in the process, etc. Fear not, Microsoft is very good at making up fictional numbers for us to use as rough estimates! I usually add 10-20% on top of these estimates just incase the database gremlins wander by to ruin your day again. Or incase you encounter "storage issues."

SELECT 
	 percent_complete AS [PercentComplete]
	,estimated_completion_time/1000.0/60.0 AS [RemainingMinutes]
	,total_elapsed_time/1000.0/60.0 AS [ElapsedMinutes]
	,(estimated_completion_time+total_elapsed_time)/1000.0/60.0 AS [TotalMinutes]
	,DATEADD(MILLISECOND, estimated_completion_time, GETDATE()) AS [EstimatedTimeOfCompletion]
	,st.TEXT AS [CommandSQL]
FROM sys.dm_exec_requests r
	cross apply sys.dm_exec_sql_text(r.sql_handle) st
WHERE command LIKE '%RESTORE DATABASE%'
Tagged as: , , , 2 Comments
25Sep/093

Determining how long a database will be IN RECOVERY (SQL Server 2008)

So, your MSSQL service crashed in the middle of a big transaction? Or you bumped the service while it was rolling back some gigantic schema change (like say a column add on a 800 million row table)? Well, as you prepare your resume in preparation for the fallout from this debockle, you can use the following query to see how much time you have left. Or, I should say, how much time it thinks you have left... which seems to swing wildly up and down... microsoft math ftw.

 
DECLARE @DBName VARCHAR(64) = 'databasename'
 
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
 
INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName
 
SELECT TOP 5
	 [LogDate]
	,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
	,[TEXT]
 
FROM @ErrorLog ORDER BY [LogDate] DESC
Tagged as: , , , , , , , 3 Comments
   

Social Networks

Recent Posts

Bloggers of Note

Family

People who take my money

Categories

Archives