June 11, 2011 at 1:43 pm
We have a couple of databases grow quick. I would like to monitor how much size it increases for the database file each day or each week.
Is there a job I can setup in sql agent to monitor those?
We do have backup files, but we only keep it 4 weeks, only on sunday do the fullbackup. So probably we cannot get much from monitoring backup files sizes.
I do want to get the size from databasee file?
Thanks
June 11, 2011 at 2:15 pm
There is no SQL server provided job to do this for you. You can easily make your own job to get the file sizes for the databases and log files and throw the results in a table each night, then do some simple calculations to see if the growth amount is abnormal for that database and email you.
There are several ways to get database file sizes and other info. One way is running sp_helpfile.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
June 11, 2011 at 2:54 pm
Thanks, so I think I can setup a sql job that use transact_sql and run sp_helpfile. to output to a text file.
You said there are serveral ways, so beside sp_helpfile, what else can I use?
Thanks
June 11, 2011 at 2:59 pm
sqlfriends (6/11/2011)
You said there are serveral ways, so beside sp_helpfile, what else can I use?
if you are after ballpark numbers and interested in past history too the easiest way is to check the size of your full backups, dump files grow as database grow.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 11, 2011 at 3:16 pm
How can I monitor the backup file size increase?
June 11, 2011 at 3:18 pm
sqlfriends (6/11/2011)
You said there are serveral ways, so beside sp_helpfile, what else can I use?
The system tables directly.
From Dave P. about the system tables, along with a free script:
From Jeff Moden with a nice script using sp_helpfile:
http://www.sqlservercentral.com/Forums/Topic612574-5-1.aspx
The answer to this question, along with work-saving free scripts are all over the web already.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
June 11, 2011 at 3:25 pm
Thanks a lot, I will do a search in the site.
June 11, 2011 at 5:12 pm
Knowing your growth rates is a very useful thing. To make it easy for yourself in the future once you have created your SQL script being called by your SQL agent job, which you could schedule once a day or more often to get trends of growth , create yourself a DBAUtils DB for example and store your collected results. This will not use a lot of space but you can then produce an rdl (ssrs report) to graphically show the results for management.
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
June 12, 2011 at 5:00 am
You can use the Data Collector if you have Enterprise edition. That will collect disk usage for you and is built into SQL Server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 12, 2011 at 6:03 am
sqlfriends (6/11/2011)
How can I monitor the backup file size increase?
Look at Backup/Restore History Tables in msdb database.
On SS2K8R2 backupfile history table willl do the trick http://msdn.microsoft.com/en-us/library/ms188653.aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply