Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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

Comments

Posted by BudaCli on 24 February 2009

Hi Rob,

This is cool but can you see which table has increased based on the geven scenario?

BudaCli.

What you don't know won't hurt you but what you know will make you plan to know better

Posted by Robert Davis on 24 February 2009

There is nothing concerning table sizes logged to the SQL log. If you want this detail of information, you must track it yourself.

Posted by Elena on 27 February 2009

THis is really very helpful scrip. Thanks!

Posted by natalie.davies on 27 February 2009

You can also query the info held about backups in MSDB:

use msdb

go

select physical_name, file_size, backupset.backup_size, backup_start_date

from backupfile, backupset

where logical_name = '<logical file name>'

and

backupfile.backup_set_id = backupset.backup_set_id

This gives you roughly the size of the datafile, and the size that's being used. Very interesting if you plot it on a graph in excel as you can use trendlines to estimate database growth.

Posted by Robert Davis on 27 February 2009

Excellent suggestion Natalie!!

The problem I had was that the PM was asking for more information than was available in the msdb backup tables. Their database had grown 160 GB's in size over three months and no one knew why or when it had grown so much.

As you suggested, I was able to chart the growth of their database for them and pinpoint when their database had grown by 120 GB's in a single day.

Posted by mukesh152 on 1 March 2009

This is really good script. I was working on a script to capture DB growth data but this will give me immediate result which is great.

Thanks for sharing a script.

Thanks,

Mukesh

Posted by Robert Davis on 4 March 2009

Thanks for the feedback, Mukesh!!

Posted by chava.sreekanth on 10 December 2009

I have executed the above script, I am  not getting any  output.

The fields which are shown are :- BUPDate and BUPSize and they are empty .

Robert:- Can  you  help  me to execute the script , so that I get results.

Thanks

Sreekanth

Posted by Robert Davis on 24 December 2009

Hi Sreekanth. Are you taking full backups of your databases?

Did you make any modifications to the script before executing it?

Did you put change the line near the top to the name of your database? This line:

Set @DBName = '<database name>'

Posted by anil kumar on 26 January 2010

I would like to know if any one manually change the log file growth ..how do we determine.

Anil g

Posted by ajtaylor on 24 October 2011

Thanks Robert, that worked a treat!

Posted by chetanr.jain on 22 June 2012

Very nice script....

Posted by josemespinoza on 23 September 2013

Excellent.

Thanks.

Leave a Comment

Please register or log in to leave a comment.