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?
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%';
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
Somebody defragmented the index by rebuilding it, then ran