Sql-server – How did the databases shrink

shrinksql server

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?

Best Answer

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

Related Solutions

Sql-server – How to find databases with a large number of virtual log files

Here is a slightly simpler approach that avoids the cursor and nested exec:

SET NOCOUNT ON;

CREATE TABLE #to
(
  DBName SYSNAME,
  FileCount INT
);

DECLARE @v INT;
SELECT @v = CONVERT(INT, PARSENAME(CONVERT(VARCHAR(32), 
  SERVERPROPERTY('ProductVersion')), 4));

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'CREATE TABLE #ti
  (
    ' + CASE WHEN @v >= 11 THEN 'RecoveryUnitId INT,' ELSE '' END + '    
    FileId int
    , FileSize nvarchar(255)
    , StartOffset nvarchar(255)
    , FSeqNo nvarchar(255)
    , Status int
    , Parity int
    , CreateLSN nvarchar(255)
);';

SELECT @sql = @sql + N'
  INSERT #ti EXEC ' + QUOTENAME(name) 
    + '.sys.sp_executesql N''DBCC LOGINFO WITH NO_INFOMSGS'';
  INSERT #to(DBName,FileCount) SELECT N''' + name + ''', COUNT(*) FROM #ti;
  TRUNCATE TABLE #ti;'
FROM sys.databases
WHERE database_id > 4 AND [state] = 0;

EXEC sp_executesql @sql;

SELECT DBName, FileCount FROM #to -- WHERE FileCount > [some threshold];

DROP TABLE #to;

Sql-server – SQL Server virtual log size

A single transaction log file has both a physical size (that you see on disk), and it's also broken down within the physical file into logical sections called virtual log files (VLFs).

Both auto-growth and auto-shrink operate on the physical transaction log file.

Transaction log truncation (also called "log clearing") operates on the logical sections of the transaction log (VLFs), and does not affect the physical file size. This part is frequently the subject of confusion.

A log file must always grow to accomodate a large transaction; turning off auto-shrink will leave the log file with its maximum needed size, instead of physically decreasing its size.

If you don't have large transactions, it will be safe to disable auto-shrink; the log files will not grow without bound like would happen if the database was in FULL or BULK_LOGGED and you weren't taking transaction log backups.

This behaviour is the same for SQL Server 2005+.

Best Answer

Related Solutions

Sql-server – How to find databases with a large number of virtual log files

Sql-server – SQL Server virtual log size

Related Question