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

Question

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.

See this other [Stackoverflow question](https://stackoverflow.com/questions/52200197/ms-sql-how-can-i-find-time-point-when-log-file-was-shrink) — Max, Nov 03 '22 at 12:03

score 2 · Answer 1 · answered Nov 03 '22 at 12:09

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

This helps going forward, but probably want to capture `duration` and `size_change_kb` more importantly than all those username fields, so the OP can measure "success." — Stuck at 1337, Nov 03 '22 at 12:17
Thanks @SQLpro, however, this won't help as currently I don't have the XE set up, also this is Production environment, so can't set up XE easily (without going through Change Management). But thanks for good idea. Cheers. — Eddie Kumar, Nov 03 '22 at 15:33

Stuck at 1337 · Answer 2 · 2022-11-03T15:34:42.570

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.

Thanks @RhythmWasALurker, I had to remove the UPPER() as it was returning error that it can't take data-type "NText" (which is datatype of "TextData" field. Other than this default trace solution worked great. Many thanks. — Eddie Kumar, Nov 03 '22 at 15:28

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

2 Answers2