How do I ... Determine Database Growth If I Am Not Tracking It?

If your database has grown considerably and you're not sure when or why it happened, you'll be hard pressed to figure out the answer unless you're tracking changes in database size. Luckily, if you are performing regular full backups, you can get this information from the SQL logs.

When you create a full backup, SQL creates an entry in the SQL Log that looks like the following:

Database backed up. Database: PSP, creation date(time): 2008/12/22(18:25:10), pages dumped: 171, first LSN: 39:41:37, last LSN: 39:59:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'c:\Bak\PSP.bak'}). This is an informational message only. No user action is required.

So we can look for these statements in the SQL logs, parse out the pages dumped, and calculate the size of the used portion of the database.

The Script:

Declare @OldestLog datetime,
     @FirstLog int,
     @SearchText nvarchar(50),
     @DBName sysname
Declare @ErrorLog Table (LogID int identity(1, 1) not null primary key,
                              LogDate datetime null,
                              ProcessInfo nvarchar(100) null,
                              LogText nvarchar(max) null)
Declare @EnumLogs Table (ArchiveNum int not null primary key,
                              ArcDate Datetime not null,
                              LogFileSize bigint not null)

Set nocount On
Set @OldestLog = '2/1/2009'
Set @SearchText = N'pages dumped: '
Set @DBName = '<database name>'

Insert Into @EnumLogs
Exec master..xp_enumerrorlogs

Select Top 1 @FirstLog = ArchiveNum
From @EnumLogs
Where ArcDate < @OldestLog
Order By ArcDate DESC

If @FirstLog Is Null
  Begin
     Select Top 1 @FirstLog = ArchiveNum
     From @EnumLogs
     Order By ArchiveNum DESC
  End

While @FirstLog >= 0
  Begin
     Insert Into @ErrorLog (LogDate, ProcessInfo, LogText)
     Exec master..xp_readerrorlog @FirstLog
     Set @FirstLog = @FirstLog - 1
  End

Select Convert(varchar, LogDate, 101) As BUPDate,
Cast(Cast((Cast(RTrim(LTrim(SubString(LogText, CharIndex(@SearchText, LogText) + Len(@SearchText), CharIndex(',', LogText, CharIndex(@SearchText, LogText)) - CharIndex(@SearchText, LogText) - Len(@SearchText)))) as BigInt) * 8.0)/1024 As Decimal(9, 2)) As varchar) + ' MB' As BUPSize
From @ErrorLog
Where CharIndex('Backup', ProcessInfo) > 0
And CharIndex('Database backed up. Database: ' + @DBName, LogText) > 0
Order By LogDate Asc

Set nocount Off