Thursday, January 9, 2014

Find Status or percentage completed of any DBCC Shrink Databse files

1.First try to find the space used and empty space left in databases

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- DB size.
EXEC sp_spaceused

 --- Table row counts and sizes.
CREATE TABLE #t
(
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT *
FROM   #t

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t

DROP TABLE #t

2. Find the Status or percentage completed of any DBCC Shrink Databse files with below query

SELECT
    percent_complete,
    start_time,
    status,
    command,
    estimated_completion_time,
    cpu_time,
    total_elapsed_time
FROM
    sys.dm_exec_requests
WHERE
    command = 'DbccFilesCompact'

https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context...