How did my databases shrink?

Question

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?

@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

score 2 · Answer 1 · answered Jul 16 '14 at 16:31

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%';

answered Jul 16 '14 at 16:31

Kin Shah

60,908
5
111
230

Well, `TextData` could be `SHRINKDATABASE` or `ALTER DATABASE`, too. I'd just narrow down to [events 94, 95 and 116](http://dba.stackexchange.com/questions/48052/what-event-information-can-i-get-by-default-from-sql-server). 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! – Hannah 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
Andy Like I mentioned in my comment :) the first point, thanks for sharing – Shanky Jul 16 '14 at 21:52

score 1 · Answer 2 · edited Feb 03 '15 at 13:00

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,

Somebody took a log backup and shrank the log file.
Somebody deleted lots of data from SQL server table and ran shrink database.
Somebody defragmented the index by rebuilding it, then ran shrink database.

edited Feb 03 '15 at 13:00

TRiG

165
5

answered Jul 16 '14 at 15:57

Shanky

18,018
4
31
54

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

How did my databases shrink?

2 Answers2