Check Restore Backup Shrink execution time & estimated time

Have you ever wondered how to estimate the time for a restore, backup or even check how much time will take your shrink operation?These days I was thrilled to find out that starting with SQL 2005 there are some dynamic views that can be queried for this info:

Here you go:

Start your Restore/Backup or Shrink operation from SQL Query or directly from SSMS Interface.

You must find out what is your session_id.

1. Identify your process :

  • Status (Runnable)
  • Login (yours)
  • DBname
  • Command (RESTORE DATABASE / BACKUP DATABASE / DBCCfilescompact / DBCC SHRINKDATABASE others..)
Use master
GO
sp_who2 active

2. Find out the percent done and estimated time of completion:

SELECT    percent_complete, start_time, command, b.name,
DATEADD(ms,estimated_completion_time,GETDATE()),
(estimated_completion_time/1000/60)
FROM sys.dm_exec_requests a
INNER JOIN sys.databases b ON a.database_id = b.database_id where session_id=93

3. General Information about Shrink
 
Let me point 3 important things concerning the Shrink operation:

  1. The Database shrinked will get very fragmented. This is because all the space free inside the database is located everywhere in many data files. This, by Shrink will be be given back to the OS forcing the existing data files to be re-arranged and so, the indexes formed in a certain order will loose their order.
  2. Nevertheless sometimes it is the single option when massive deletes (or index removal) are done and you need desperately the space.
  3. To have a successful operation: All user table indexes must be checked for fragmentation and perform dbcc dbreindex, alter index rebuild or alter index reorganize – which suites you best.
  4. I personally prefer to do Alter Index Rebuild for indexes with fragmentation bigger than 30 % and page count bigger than 10000.

    Here are some commands to collect the fragmentation inside one database also using dynamic views.

    SELECT * INTO master.dbo.TEMPINDEXES FROM sys.dm_db_index_physical_stats (5, NULL, NULL, NULL , 'LIMITED')
    
    SELECT t.database_id, s.name, s.type,t.object_id, t.index_type_desc, t.avg_fragmentation_in_percent, t.page_count, *
    FROM master.dbo.TEMPINDEXES t inner join sysobjects s on t.[OBJECT_ID]  = s.id
    order by t.avg_fragmentation_in_percent desc

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>