I have a nightly job that monitors database and log file sizes. Over time the databases grow. Last night some databases actually shrank in size. The logs show no SHRINKFILE OR SHRINKDATABASE events. AUTOSHRINK is OFF. Is there any other way these databases could have shrank?

  • 1
    Were there any restores on those DBs? Jul 16, 2014 at 15:55
  • No restores, just nightly full backups.
    – Andy
    Jul 16, 2014 at 17:32
  • @Andy ok, but are these backups performed with a maintenance plan? Are you 100% certain that the maintenance plan doesn't have other tasks enabled (like shrinking)? Jul 16, 2014 at 17:33

2 Answers 2


You can use the Power of Default Trace to find out the culprit.

provided the default trace files are not rolled over

-- find out the default trace location

FROM fn_trace_getinfo(default);

   --- check the events

[fn_trace_gettable]('Location of Tracefile.trc', DEFAULT) 
  • Well, TextData could be SHRINKDATABASE or ALTER DATABASE, too. I'd just narrow down to events 94, 95 and 116. And maybe 115, too, in case databases were restored to a previous (smaller) version. Jul 16, 2014 at 16:43
  • Good point @AaronBertrand. Will not the search on TextData for word SHRINK find all the events related to it. Agree with you that its better to filter out specifics to narrow down what OP is looking for.
    – Kin Shah
    Jul 16, 2014 at 17:09
  • This worked like a charm! I can now see the culprit. Thanks guys.
    – Andy
    Jul 16, 2014 at 17:57
  • And? I think I speak for everyone here, we are dying to find out why your database shrank!
    – Hannah Vernon
    Jul 16, 2014 at 19:19
  • I am a little embarrassed, but I did learn something. My nightly script reports the size of the db AND the log. It was the logs that shrank, not the db.
    – Andy
    Jul 16, 2014 at 21:38

Shrinking information is not logged in errorlog as such. Are you talking about log file size or data file size or complete SQL database size? All three are different. There can be many reasons for a database size decrease. A few are,

  1. Somebody took a log backup and shrank the log file.

  2. Somebody deleted lots of data from SQL server table and ran shrink database.

  3. Somebody defragmented the index by rebuilding it, then ran shrink database.

  • 2
    2. and 3. won't shrink the physical files. Jul 16, 2014 at 16:45
  • 1
    Ya correct I meant to say that of course he ran shrink database. Database wont just release space unless you ask you to do so or unless auto shrink is enabled
    – Shanky
    Jul 16, 2014 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.