Estimate SQL db Restore Time

Written by Keith Tuomi on . Posted in SQL. Leave a Comment

If you’ve ever had to restore a large SharePoint or other SQL DB, here’s a handy little T-SQL for guesstimating how long a it’s going to take. Run this when one or more Restore’s are in progress. Note that it would spin up actual TimeRemainingMin’s until it actually picks up speed and get’s going (past 0% in the dialog GUI):

SELECT
    d.PERCENT_COMPLETE AS [%Complete],
    d.TOTAL_ELAPSED_TIME/60000 AS ElapsedTimeMin,
    d.ESTIMATED_COMPLETION_TIME/60000   AS TimeRemainingMin,
    d.TOTAL_ELAPSED_TIME*0.00000024 AS ElapsedTimeHours,
    d.ESTIMATED_COMPLETION_TIME*0.00000024  AS TimeRemainingHours,
   s.text AS Command
FROM    sys.dm_exec_requests d
CROSS APPLY sys.dm_exec_sql_text(d.sql_handle)as s
WHERE  d.COMMAND LIKE 'RESTORE DATABASE%'
ORDER   BY 2 desc, 3 DESC

11-22-2013 11-03-25 AM

Keywords:
Method to estimate the recovery time of a SQL Server database
estimated time for restoring a database
Estimated Completion Time for Restores
SQL Server – Restore Database – Time Estimate or Progress
How can I query how much time a SQL server database restore takes?
Working out how long a SQL Server backup/restore will take

T-SQL to get SQL Transaction Log Sizes

Written by Keith Tuomi on . Posted in SharePoint 2010, SharePoint 2013, SQL, SQL, SQL Server 2012. Leave a Comment

When dealing with the slew of SharePoint MS SQL databases that can be found in a typical install, it’s a bit of a time suck to check transaction log sizes manually. I put together the following T-SQL script to quickly show which transaction logs out of all the logs in the particular SQL instance, are above 299 MB in size. Adjust that threshold to your tastes.

declare @LogSpace table
(
DatabaseName varchar(255),
[Log Size (MB)] float,
[Log Space Used (%)] float,
[Status] int)
insert into @LogSpace
execute('dbcc sqlperf(''LogSpace'')')
 
select * from @LogSpace 
where [Log Size (MB)] > 299 
order by [Log Size (MB)] desc 
--order by [Log Space Used (%)] desc