Database Space Capacity Planning

  • cmille19

    SSCertifiable

    Points: 5920

    Comments posted to this topic are about the item Database Space Capacity Planning

  • philcart

    SSC-Forever

    Points: 47779

    Top stuff 😎

    Now for SQL Server 2000, I would do ... :ermm::crying:

    --------------------
    Colt 45 - the original point and click interface

  • Regan Galbraith

    SSCertifiable

    Points: 5227

    Interesting stuff. we have something similar using SQL agent jobs, and SSRS.

    Unless I missed it, the prediciton doesn't seem to have methods for predicting impact of reindexing and check database activities. Do you have metrics for establishing the peak DB size, or else of predicting the space required for that? We find that it is during the maintenance that our space alerts (85%, 90% etc.) most commonly trigger.

  • Tim Reddish

    Ten Centuries

    Points: 1311

    Unless I'm mistaken the script to create the stored procedures in not present in the download.

    Is this just me or has anyone else noticed?

  • graeme.black

    SSC Veteran

    Points: 272

    Yes the script for the stored procedures is missing from the downloads

  • shashi kant

    SSCommitted

    Points: 1659

    Supurb Article, I was looking for such article.

    Do you have SQL Server 2000 scripts for the same

    Regards

    Shashi Kant Chauhan

  • cmille19

    SSCertifiable

    Points: 5920

    Somehow I left out the two creation statements for the procedures from the download. The definition for both procedures db_space_capacity_sp and vol_space_capacity_sp are included in the article. I've corrected the download to include the file spacedm.procedures.sql. It may take a few days for the update to be published. I've also uploaded a corrected download to my blog, here

  • smthembu

    Hall of Fame

    Points: 3449

    That's good stuff

    It's better to fail while trying, rather than fail without trying!!!

  • cmille19

    SSCertifiable

    Points: 5920

    shashi kant (10/13/2009)


    Supurb Article, I was looking for such article.

    Do you have SQL Server 2000 scripts for the same

    Regards

    Shashi Kant Chauhan

    The code works and has been tested against 2000, 2005 and 2008 servers. The backend repository must be 2005 or 2008. If you don't have a 2005 or 2008 SQL Server, SQL Express will work. Additionally the only pieces that are 2005/2008 specific are the CTEs used in the forecasting procedures. If you really want to use a 2000 repository you could rewrite these queries to uses subqueries instead of CTEs. The other piece that is 2008 specific are the SSRS reports. The reports are very basic and could be re-created using the SSRS wizard for 2000 or 2005 SSRS. Here again a SQL 2008 Express with Advanced Services (includes SSRS) would suffice.

  • smthembu

    Hall of Fame

    Points: 3449

    Hi

    When i execute spaceanalysis.job it fails with the below or is it me

    Msg 206, Level 16, State 2, Procedure sp_add_jobstep, Line 0

    Operand type clash: int is incompatible with uniqueidentifier

    It's better to fail while trying, rather than fail without trying!!!

  • ESL_HSBC

    SSC Veteran

    Points: 280

    Do you have a Spaceanalysis.sln for BIDS 2005 please

  • cmille19

    SSCertifiable

    Points: 5920

    smthembu (10/13/2009)


    Hi

    When i execute spaceanalysis.job it fails with the below or is it me

    Msg 206, Level 16, State 2, Procedure sp_add_jobstep, Line 0

    Operand type clash: int is incompatible with uniqueidentifier

    Updated job script, I was missing the server instance and repository parameters in the job. The two job steps should look like this:

    & C:\bin\Write-VolToDb.ps1 ' Z002\SQL2K8' spacedm

    & C:\bin\Write-DbSpaceToDb.ps1 ' Z002\SQL2K8' spacedm

  • cmille19

    SSCertifiable

    Points: 5920

    eric.simbozel-943459 (10/13/2009)


    Do you have a Spaceanalysis.sln for BIDS 2005 please

    No just 2008, the reports are fairly simply and mainly created from SSRS wizard and then changed to use the stored procedures.

  • cmille19

    SSCertifiable

    Points: 5920

    Wanderer (10/13/2009)


    Interesting stuff. we have something similar using SQL agent jobs, and SSRS.

    Unless I missed it, the prediciton doesn't seem to have methods for predicting impact of reindexing and check database activities. Do you have metrics for establishing the peak DB size, or else of predicting the space required for that? We find that it is during the maintenance that our space alerts (85%, 90% etc.) most commonly trigger.

    I've been using the process described in the article for the past year and haven't had the need to go any further than the data file. You could of course create a similar process that looks at table and index usage, but this would be slightly more complicated. The situation you describe with free space is somewhat trickly for instance I can point to several databases that basically are one giant table. These databases will need more than 15% or 10% free space to reindex. There really isn't a good rule of thumb for percent free space in database. However, by looking at the growth of data files over time which includes used space, as long as you don't shrink files afterward you should be fine.

    Forecasting storage capacity is bit like horseshoes, you want to get things close enough, but anything more complicated may not be worth the effort. I've found I'm able to catch databases before they autogrow and able to detect abnormal growth conditions. But, I also find some false positives such as when a new database is first deployed and the data points are small. I'll go through the list of databases with less than 90 days left and research each one individually. This list is typically small, less than a couple of dozen in an environment with 1000+ databases. The ones that need to increase in size I will do so manually and the ones that are false positives I will ignore after research.

  • einman33

    SSCrazy

    Points: 2753

    Great stuff here, good work!!!

Viewing 15 posts - 1 through 15 (of 93 total)

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