0

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? – James Anderson Jul 16 '14 at 15:55
  • No restores, just nightly full backups. – Andy Jul 16 '14 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)? – Aaron Bertrand Jul 16 '14 at 17:33
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



SELECT * 
FROM fn_trace_getinfo(default);
GO

   --- check the events

SELECT 
    TextData,
    HostName,
    ApplicationName,
    LoginName, 
    StartTime  
FROM 
[fn_trace_gettable]('Location of Tracefile.trc', DEFAULT) 
WHERE TextData LIKE '%SHRINKFILE%'; 
  • 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. – Aaron Bertrand Jul 16 '14 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 '14 at 17:09
  • This worked like a charm! I can now see the culprit. Thanks guys. – Andy Jul 16 '14 at 17:57
  • And? I think I speak for everyone here, we are dying to find out why your database shrank! – Max Vernon Jul 16 '14 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 '14 at 21:38
1

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. – Aaron Bertrand Jul 16 '14 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 '14 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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