How to identify the cause why the disk space growths above 80GB

Question

How to identify the cause why the disk space growths above 80GB

Lanny Irwan

Ten Centuries

Points: 1020
More actions
October 21, 2007 at 7:53 pm

#69068

Hi All,
I need your advice. I created an alert in our database server to send an alert when the data file(s) is above 80GB. I received an alert e-mail dated from 19 Oct'07 8PM to 21 Oct'07 5PM. Something has happenned during that period but I don't know from where I should start. I don't find any errors in our SQL error log.
I set 'Automatically growth' to all our database.
For SQL2k5, I may be able to use dynamic views. But how about SQL2K?
Appreciate your advices.
Cheers

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 1

Lanny-

From your post it sounds like you're trying to identify the cause of your database growing beyond 80GB? If yes, you're likely just beginning an investigation that could take some time to resolve.

One common cause of database growth is maintenance plans that include reindexes, etc. so take a look at the scheduled jobs on your server - if you see a job that executes every friday night that correlates to your DB growth you've got a likely avenue to pursue (friday night is just an example)

Beyond that, you're probably going to need a trace to nail down exactly what is causing your DB growth, could be anything from a bulk load to a large sort.

Joe

Lanny Irwan Ten Centuries Points: 1020 More actions · Answer 2

I don't have any maintenance jobs that run from 19 Oct'07 to 21 Oct'07.

I'll check the databases that are having 'Auto Shrink' option and check their database size on the msdb..backupfile and msdb..backupset.

Any other area that I should look at?

Thanks

okhere SSC-Addicted Points: 443 More actions · Answer 3

Hi,

Why not try to schedule a job that runs this script, shown earlier on SQL Server Central forums, that counts the number of rows in every table and stores them for comparison. You can then monitor which tables are the cause for the growth, if this is the cause.

--Get record count for every table in DB1 -- where DB1 is the database you want to monitor

use DB1

select

sysobjects.name TableName,

sysindexes.rows TableRowCount

from

sysobjects

inner join sysindexes on (sysobjects.id = sysindexes.id)

where

(sysobjects.xtype = 'u') and

(sysindexes.indid < 2)

order by

tablename

Lanny Irwan Ten Centuries Points: 1020 More actions · Answer 4

Hi,

Thanks for your advice and I'll take note the script, however I still can't find which database that I should monitor.

Almost all the databases are set to 'Auto Shrink' due to limitation on the disk space while waiting for me to generate the proposal to our infra manager. I have daily job to truncate the transaction log and shrink the transaction log which adds on the complexity.

If I take a look the backup file size for each database based on the backupset and backupfile table from msdb database, the numbers are consistent which means no significant growths from 19 Oct'07 to 21 Oct'07.

Any other area that I should look at? or anything that I should improve moving forwards?

Appreciate all your feedback and comments.

Bob Fazio SSChampion Points: 10794 More actions · Answer 5

EXEC MASTER.dbo.sp_MSforeachdb 'USE ? SELECT DB_NAME() as DBname,8*SUM(size)/1024 as MegUsed FROM sys.database_files where data_space_id <> 0'

That will show you which of your databases is the largest. You can use this to track growth overall also

Actually for 2000 use sysfiles instead of sys.database_files

Bob Fazio SSChampion Points: 10794 More actions · Answer 6

The more I read this question the less I understand what the issue actually is. I get the impression that you received an alert, that tells you that the DB is over 80g and you don't know how to fix the problem. Why do you feel that is an issue? And why did you get this alert?

Lanny Irwan Ten Centuries Points: 1020 More actions · Answer 7

The alert was sent from 19 Oct'07 to 21 Oct'07. The disk space went down after 21 Oct'07, no more alert created.

I want to know what kind of activity has been happenning from 19 Oct'07 to 21 Oct'07. I want to find the culprit that cause my disk space went up to.

Bob Fazio SSChampion Points: 10794 More actions · Answer 8

Assuming you have all of your backup logs, this is one way. http://www.red-gate.com/products/SQL_Log_Rescue/index.htm

Have fun!

Unless you have setup monitoring of past values there are very few options at your disposal. There are a lot of things that you can do to allow you to see the causes more easily in the future, but usually after the fact it is difficult to determine.

If you asked me, the first thing I would look at is what is the autogrowth values set to? If it is the default 10% and your current db size is in the low 70 gig range 🙂 I suspect that it grew, you shrunk and as soon as it needs data it will grow another 7/8G.

If that isn't it, then there are a lot of possiblities. The simplest way I would say would be to restore the DB to the 18th. Set to read-only. Compare sizes to PRD. Roll forward to 19th, 20th, 21st etc. And continue to compare. It might be obvious.