October 21, 2007 at 7:53 pm
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
October 21, 2007 at 9:32 pm
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
October 22, 2007 at 12:16 am
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
October 22, 2007 at 12:28 am
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
October 22, 2007 at 5:50 am
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.
October 22, 2007 at 12:24 pm
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
October 22, 2007 at 12:29 pm
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?
October 22, 2007 at 2:22 pm
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.
October 22, 2007 at 2:42 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply