Database Space Capacity Planning
-
Comments posted to this topic are about the item Database Space Capacity Planning
-
Top stuff 😎
Now for SQL Server 2000, I would do ... :ermm::crying:
--------------------
Colt 45 - the original point and click interface
-
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.
-
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?
-
Yes the script for the stored procedures is missing from the downloads
-
Supurb Article, I was looking for such article.
Do you have SQL Server 2000 scripts for the same
Regards
Shashi Kant Chauhan
-
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
-
That's good stuff
It's better to fail while trying, rather than fail without trying!!!
-
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.
-
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!!!
-
Do you have a Spaceanalysis.sln for BIDS 2005 please
-
smthembu (10/13/2009)
HiWhen 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 -
eric.simbozel-943459 (10/13/2009)
Do you have a Spaceanalysis.sln for BIDS 2005 pleaseNo just 2008, the reports are fairly simply and mainly created from SSRS wizard and then changed to use the stored procedures.
-
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.
-
Great stuff here, good work!!!
Viewing 15 posts - 1 through 15 (of 94 total)
You must be logged in to reply to this topic. Login to reply