Check if DB shrink was successful & get historical stats on shrinking events (SQL Server)-CodePudding

In SQL Server, the transaction log file was needed to be shrunk, therefore the DBCC SHRINKFILE was executed (we forgot to note down the file-size before execution). Now how can we check if the file shrinking process was succeeded, especially we don't know the initial file size before the shrinking was done.

For clarity: Shrinking process is currently not running, (this is not about checking the on-going progress of shrinking).

Also is there a way to get historical stats on shrinking events?

TIA.

CodePudding user response：

You can do that with extended events by tracking databases_data_file_size_changed event.

Follow this command :

CREATE EVENT SESSION ES_TRACK_DB_FILE_CHANGE 
   ON SERVER 
   ADD EVENT sqlserver.database_file_size_change
      (ACTION(sqlserver.client_app_name,
              sqlserver.client_hostname,
              sqlserver.database_name,
              sqlserver.nt_username,
              sqlserver.server_principal_name,
              sqlserver.session_nt_username,
              sqlserver.sql_text,
              sqlserver.username))
   ADD TARGET package0.event_file
       (SET filename=N'C:\XE_EVENTS\TRACK_DB_FILE_CHANGE.xel')
   WITH (MAX_MEMORY=2048 KB,
         EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
         MAX_DISPATCH_LATENCY=60 SECONDS,
         STARTUP_STATE=ON)
GO

CodePudding user response：

For (recent) historical information, you can consult the default trace (background in this answer):

DECLARE @path nvarchar(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260))   N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT TextData, [Database] = DB_NAME(DatabaseID), LoginName
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 116
  AND UPPER(CONVERT(nvarchar(max), TextData)) LIKE N'%SHRINK%'; 
  -- could be SHRINKDATABASE

However, this just tells you when they happened and who did it. It does not include how much shrinking happened (if any) or even if the file grew (which, yes, is possible via SHRINKFILE). It doesn't even allow you to calculate duration, which might help you infer how much shrinking happened, because the DBCC events are captured when they start.

To capture this information on an ongoing basis, I would say don't just run DBCC SHRINKFILE(), but wrap that in a script that polls for the file sizes before and after. You could use an Extended Events session as described in another answer, but that session doesn't look like it captures size.