TSQL Query to find the DB Maintenance status

 

Are you curious to find the Database Backup / Restore status?
Do you have to find out the status the DBCC CHECKDB ?
Do you have to report when the ROLLBACK would finish ?
Do you want judge how long the Database Shrink will take?

Here is the answer!

The query listed below can be used to find the Percentage of work completed for the following commands:

1. ALTER INDEX REORGANIZE
2. AUTO_SHRINK option with ALTER DATABASE
3. BACKUP DATABASE
4. DBCC CHECKDB
5. DBCC CHECKFILEGROUP
6. DBCC CHECKTABLE
7. DBCC INDEXDEFRAG
8. DBCC SHRINKDATABASE
9. DBCC SHRINKFILE
10. RECOVERY
11. RESTORE DATABASE,
12. ROLLBACK
13. TDE ENCRYPTION

We have to replace the where condition filter based on the requirement.

SELECT command,             sqltext.text,             start_time,             percent_complete,             CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '                   + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '                   + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,             CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '                   + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '                   + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,             dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time FROM sys.dm_exec_requests Req CROSS APPLY sys.dm_exec_sql_text(Req.sql_handle) sqltext WHERE req.command in ('DBCC CHECKDB')

To find the DBCC execution progress

To find the DBCC execution progress;

select percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, * from sys.dm_exec_requests

Server: Msg 8964, Level 16, State 1, Line 1

Problem:

Server: Msg 8964, Level 16, State 1, Line 1

Table
error: Object ID 515532920. The text, ntext, or image node at page
(1:377289), slot 1, text ID 897099563008 is not referenced.



Table error: Object ID
515532920. The text, ntext, or image node at page (1:1124698), slot 1,
text ID 896822476800 is not referenced.

CHECKDB found 0 allocation errors and 8 consistency errors in table 'CC_Document' (object ID 515532920).

CHECKDB found 0 allocation errors and 8 consistency errors in database 'crmproded'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (crmproded ).



Cause:



The text fragments
listed in the CHECKDB output aren't referenced by any rows in the
clustered index. When a data row has a LOB value then usually (unless
you've tweaked the text_in_row option) the LOB value is stored off-row
with a root pointer stored in the data row itself. What's happened in
this case is that some data rows have been deleted but their
corresponding LOB values have not been. This should be benign but we
should try to work out why it happened.

powered by performancing firefox