Wednesday, June 20, 2018

SQL server check the status of the shrink

I have shrink database using SQL server Management Studio visual editor.

Right click database --> tasks --> Shrink --> database

Window comes and shows shrink is running. But this has been there for more than one day. So i need to check the status of the shrinking.



with below script you will get list of the commands and its status, completed percentage etc. look for command DbccFilesCompact , you will get all the details.

--------------------------------------------------------------------------------------------------

SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests

----------------------------------------------------------------------------------------------------

As above  may give a little too large list, you can put a cross apply and get a better output as below script


----------------------------------------------------------------------------------------------------

select T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)
       , R.cpu_time, R.total_elapsed_time, R.percent_complete
from   sys.dm_exec_requests R
       cross apply sys.dm_exec_sql_text(R.sql_handle) T
order by Command

----------------------------------------------------------------------------------------------------



When i query, status was shown as "suspended".
I tried to cancel job by clicking window close button, but did not work.
So i restarted the db server and shrink the log file using the script.


----------------------------------------------------------------------------------------------------

USE [myDb]
GO
DBCC SHRINKFILE (N'myDB_log' , 0, TRUNCATEONLY)
GO

----------------------------------------------------------------------------------------------------


'myDB_log' is the logical name for the log file for the database 'myDB'

No comments:

Post a Comment