Post reply

What is the best way to monitor growth of my databases?

  • January 7, 2004 at 1:09 am

    #152005

    In the SQL Server 2000 for experienced DBAs book mentioned about monitoring db size growth, so we can manually increase the size of a db, instead of using AutoGrow process.  But the book didn't say how to do the monitoring?  Any suggestions?

    Thanks.

  • January 8, 2004 at 9:01 am

    #489145

    You can schedule a job to run sp_spacesued @updateusgae = true and save the result to your table with datetime. Because sp_spaceused returns two result sets, you need to create your own version to remove the second result set.

    Depend on how large your database and how many database files your database have. If database is small, I will leave the auto growth on. If it is quite large with multiple database files, I would trun off the auto growth and increase all database files size with same ratio at same time that allows data be added into all database file proportional.

     

  • January 8, 2004 at 9:35 am

    #489151

    I actually kill two birds with one stone. Monitor the size of the backups and trend that. Allows me a verification that the backup completed. And I can see if things are growing. The raw space is less important to me than the data size.

  • January 9, 2004 at 4:06 pm

    #489483

    Please see my post @

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=18553&p=2

    for script example to create alert for file growth.

  • January 13, 2004 at 11:19 am

    #489808

    There is a good article in SQL Server Magazine Dec 2002 called "Avoiding the Red Zone" which describes how to set up a stored procedure to work with SQL Server Agent to automatically collect space usage statistics for databases.

Viewing 5 posts - 1 through 4 (of 4 total)

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