http://www.sqlservercentral.com/blogs/robert_davis/2009/02/20/How-do-I-Determine-Database-Growth-If-I-Am-Not-Tracking-It/

Printed 2014/10/01 06:36AM

How do I ... Determine Database Growth If I Am Not Tracking It? -- Error Logs Part III

By Robert Davis, 2009/02/20

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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.