SQL Server Query for Capacity Planning – SQL 2005 & 2008

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 26-12-2010

14

As a proactive DBA its one of responsibility to keep an eye on database’s growth and other file system changes for the databases hosted on SQL instance. However being busy on daily DBA tasks, its not always possible to prepare scripts or manually extract data from system for documentations & trend reporting purposes. I faced the similar challenge while working with large DB systems and came up with a T-SQL script that can do the needful without worries..

Given script collect following important information and save in tbl_CapacityPlanning table inside msdb database for indentifying database size growth trends and changes.You may call dba_CapacityPlanning stored procedure from a SQL Agent job on weekly basis. That’s it, its safe and worry free process to accumulate database’s information over a period of time. Here are the detailed description of data columns fetched from T-SQL script:

    • SQLBuild (SQL Server build number, version)
    • SQLName (SQL Server or instance name)   
    • DBName (Database Name)  
    • LogicalFileName (Logical file name of SQL Server database files)   
    • DBCreationDate (Time when database was created)  
    • DBRecoveryModel (Recovery model of database like Full, simple or bulk logged)  
    • DBCompatibilityLevel  (is this database SQL 2000, 2005 or 2008 compatible)  
    • DBCollation ( Database collation level/ name)  
    • FileType (Data or log file)  
    • FileName (Database File Location)  
    • Growth (Value for growth parameter, it is directly related to Growth Type)  
    • GrowthType (Type of growth, in percentage or in value like MB)  
    • FileID (ID of file inside a particular database)  
    • IsPrimaryFile (meant for first or mdf file)  
    • MaxSize(MB)  (Maximum size of database file, -1= unrestricted) 
    • Size(MB) (Current database file size in MB)
    • UsedSpace(MB) (Current used space in database file size)  
    • AvailableSpace(MB) (Amount of free space in database file, =size-usedspace)  
    • FileStatus (is database file online of offline?, it tells you database file status)   
    • IsOffline (is database file offline?)  
    • IsReadOnly (is database file read only?)  
    • IsReadOnlyMedia   
    • IsSparse

     

    image 

    USE [msdb]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    /*******************************************************************************************
    Purpose        : Populate [tbl_CapacityPlanning] table in msdb database to keep Database &
    files details for Capacity Planning and growth trends.
    Author        : Deepak Kumar
    Dependencies: None, SP is compatible for SQL Server 2005 onwards..                    
    *******************************************************************************************/
    CREATE PROC [dbo].[dba_CapacityPlanning]
    AS
    BEGIN
    SET NOCOUNT ON
    IF  NOT EXISTS (SELECT * FROM MSDB.sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[tbl_CapacityPlanning]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [msdb].[dbo].[tbl_CapacityPlanning](
        [ExecuteTime] [datetime] NULL,
        [SQLBuild] [nvarchar](57) NULL,
        [SQLName] [nvarchar](128) NULL,
        [DBName] [sysname]  NULL,
        [LogicalFileName] [sysname]  NULL,
        [DBCreationDate] [datetime]  NULL,
        [DBRecoveryModel] [nvarchar](60) NULL,
        [DBCompatibilityLevel] [tinyint] NULL,
        [DBCollation] [sysname] NULL,
        [FileType] [nvarchar](60) NULL,
        [FileName] [nvarchar](260)  NULL,
        [Growth] [float] NULL,
        [GrowthType] [varchar](30) NULL,
        [FileID] [int]  NULL,
        [IsPrimaryFile] [bit] NULL,
        [MaxSize(MB)] [float] NULL,
        [Size(MB)] [float] NULL,
        [UsedSpace(MB)] [float] NULL,
        [AvailableSpace(MB)] [float] NULL,
        [FileStatus] [nvarchar](60) NULL,
        [IsOffline] [bit] NULL,
        [IsReadOnly] [bit] NOT NULL,
        [IsReadOnlyMedia] [bit]  NULL,
        [IsSparse] [bit]  NULL
    ) ON [PRIMARY]
    END 
    CREATE table #tmpspc (Fileid int, FileGroup int, TotalExtents int, 
    UsedExtents int, Name sysname, FileName nchar(520)) 
    DECLARE @DatabaseName varchar(500)
    DECLARE curDB cursor for 
    SELECT ltrim(rtrim(name))  from master.sys.databases where state_desc='ONLINE' 
    AND user_access_desc='MULTI_USER'
    open curDB
    fetch curDB into @DatabaseName
    while @@fetch_status = 0
    begin
    insert into #tmpspc exec ('USE [' + @DatabaseName + ']  DBCC SHOWFILESTATS')    
    fetch curDB into @DatabaseName
    end
    close curDB
    deallocate curDB 
     
    create table #tmplogspc (DatabaseName sysname, LogSize float, SpaceUsedPerc float, Status bit)
    insert #tmplogspc EXEC ('dbcc sqlperf(logspace)') 
     
    insert into [msdb].[dbo].[tbl_CapacityPlanning] SELECT getdate() AS [ExecuteTime],
    left(@@version,57) AS [SQLBuild], @@servername AS [SQLName],
    sd.name AS [DBName],
    s.name AS [LogicalFileName],
    sd.create_date AS [DBCreationDate], sd.recovery_model_desc AS [DBRecoveryModel], 
    sd.compatibility_level AS [DBCompatibilityLevel], sd.collation_name AS [DBCollation],
    s.type_desc AS [FileType],
    s.physical_name AS [FileName],
    CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE (s.growth*8)/1024 END AS float) AS [Growth],
    CAST(CASE WHEN s.is_percent_growth=1  THEN '%' Else 'MB' END AS VARCHAR) AS [GrowthType],
    s.file_id AS [FileID],
    CAST(CASE s.file_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsPrimaryFile],
    CASE when s.max_size=-1 then -1 else (s.max_size * CONVERT(float,8))/1024 END AS [MaxSize(MB)],
    (s.size * CONVERT(float,8))/1024 AS [Size(MB)],
    (CAST(tspc.UsedExtents*convert(float,64) AS float))/1024 AS [UsedSpace(MB)],
    ((tspc.TotalExtents - tspc.UsedExtents)*convert(float,64))/1024 AS [AvailableSpace(MB)],
    s.state_desc AS [FileStatus],
    CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],
    s.is_read_only AS [IsReadOnly],
    s.is_media_read_only AS [IsReadOnlyMedia],
    s.is_sparse AS [IsSparse]
    FROM master.sys.master_files AS s 
    INNER JOIN master.sys.databases sd ON sd.database_id=s.database_id
    INNER JOIN #tmpspc tspc ON ltrim(rtrim(tspc.FileName)) = ltrim(rtrim(s.physical_name))
    UNION ALL
    SELECT getdate() AS [ExecuteTime],left(@@version,57) AS [SQLBuild], @@servername AS [SQLName],
    sd.name AS [DBName],
    s.name AS [LogicalName],
    sd.create_date AS [DBCreationDate], sd.recovery_model_desc AS [DBRecoveryModel], 
    sd.compatibility_level AS [DBCompatibilityLevel], sd.collation_name AS [DBCollation],
    s.type_desc AS [FileType],
    s.physical_name AS [FileName],
    CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE (s.growth*8)/1024 END AS float) AS [Growth],
    CAST(CASE WHEN s.is_percent_growth=1  THEN '%' Else 'MB' END AS VARCHAR) AS [GrowthType],
    s.file_id AS [FileID],
    '0' as [IsPrimaryFile],
    CASE when s.max_size=-1 then -1 else (s.max_size * CONVERT(float,8))/1024 END AS [MaxSize(MB)],
    (s.size * CONVERT(float,8))/1024 AS [Size(MB)],
    (tspclog.LogSize * tspclog.SpaceUsedPerc * 10.24)/1024 AS [UsedSpace(MB)],
    ((s.size * CONVERT(float,8))/1024 - (tspclog.LogSize * tspclog.SpaceUsedPerc * 10.24)/1024) 
    AS [AvailableSpace(MB)],
    s.state_desc AS [FileStatus],
    CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],
    s.is_read_only AS [IsReadOnly],
    s.is_media_read_only AS [IsReadOnlyMedia],
    s.is_sparse AS [IsSparse]
    FROM master.sys.master_files AS s
    INNER JOIN master.sys.databases sd ON sd.database_id=s.database_id
    INNER JOIN #tmplogspc tspclog ON 
    tspclog.DatabaseName = sd.name
    WHERE (s.type = 1 ) ORDER BY sd.name, FileID ASC 
     
    -- DROP THE TEMP TABLES
    DROP TABLE #tmpspc
    DROP TABLE #tmplogspc
    END 
     
    GO
    -- select * from msdb..tbl_CapacityPlanning 

    Comments (14)

    Thanks for the wonderful script Deepak! It well packaged and very thorough, 5 stars. If you like hearing about other approaches then you may want to take look at the PowerShell below. It is part of a solution *similar* to yours which produces XML files for loading into a remote server.

    [Reflection.Assembly]::Load(“Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91″) | Out-Null
    [Reflection.Assembly]::Load(“Microsoft.SqlServer.SqlEnum, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91″) | Out-Null
    [Reflection.Assembly]::Load(“Microsoft.SqlServer.SmoEnum, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91″) | Out-Null
    [Reflection.Assembly]::Load(“Microsoft.SqlServer.ConnectionInfo, Version=9.0.242.0, Culture=neutral, `PublicKeyToken=89845dcd8080cc91″) | Out-Null

    [Reflection.Assembly]::Load(“Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91″) | Out-Null
    [Reflection.Assembly]::Load(“Microsoft.SqlServer.SqlEnum, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91″) | Out-Null
    [Reflection.Assembly]::Load(“Microsoft.SqlServer.SmoEnum, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91″) | Out-Null
    [Reflection.Assembly]::Load(“Microsoft.SqlServer.ConnectionInfo, Version=9.0.242.0, Culture=neutral, `PublicKeyToken=89845dcd8080cc91″) | Out-Null

    $SmoServer = New-Object -typeName “Microsoft.SqlServer.Management.Smo.Server”
    $SqlName = $SmoServer.Name.Replace(“[“, “”).Replace(“]”,””)

    $SmoServer | Select-Object -Property “*” -ExcludeProperty @(“Parent”, “*s”) | Export-Clixml -path (“.\” + $SqlName + “_SMOExport.xml”) -depth 1
    $SmoServer.Configuration | Select-Object -Property “*” -ExcludeProperty @(“Parent”, “Properties”) | Export-Clixml -path (“.\” + $SqlName + “_SMOConfigurationExport.xml”) -depth 2
    $SmoServer.Information | Select-Object -Property “*” -ExcludeProperty @(“Parent”, “Properties”) | Export-Clixml -path (“.\” + $SqlName + “_SMOInformationExport.xml”) -depth 1
    $SmoServer.Settings | Select-Object -Property “*” -ExcludeProperty @(“Parent”, “Properties”) | Export-Clixml -path (“.\” + $SqlName + “_SMOSettingsExport.xml”) -depth 2

    $SmoServer.Databases | ForEach-Object {Select-Object -InputObject $_ -Property “*” -ExcludeProperty @(“Mirroring*”, “Parent”, “*s”) | Export-Clixml -path (“.\” + $SqlName + “_” + $_.Name + “_SMOExport.xml”) -depth 1}
    $SmoServer.Databases | ForEach-Object {$_.FileGroups | ForEach-Object {Select-Object -InputObject $_ -Property “*” -ExcludeProperty @(“Parent”, “*s”) | Export-Clixml -path (“.\” + $SqlName + “_” + $_.Parent.Name + “_” + $_.Name + “_SMOExport.xml”) -depth 1}}
    $SmoServer.Databases | ForEach-Object {$_.FileGroups | ForEach-Object {$_.Files | ForEach-Object {Select-Object -InputObject $_ -Property “*” -ExcludeProperty @(“Parent”, “Properties”) | Export-Clixml -path (“.\” + $SqlName + “_” + $_.Parent.Parent.Name + “_” + $_.Parent.Name + “_” + $_.Name + “_SMOExport.xml”) -depth 1}}}

    This is an excellent script. Can you please add the qaurterly growth and sum the spaceused, free space for mdf and ldf and also forcast the yearly growth?

    @Raisa: you may query tbl_CapacityPlanning table for quarterly or yearly growth pattern. I will suggest creating a view using group by duration.

    Can you please provide the script to capture the monthly growth formula to this script or view script to capture the monthly growth? Thanks in advance for your help…

    Msg 102, Level 15, State 1, Procedure dba_CapacityPlanning, Line 105
    Incorrect syntax near ‘ASC’.

    could you pls sort out above error plss

    Vik, not sure how you ran this but no error here.
    Had to copy the query above to Word first to make sure that all of the query form was OK. Direct copy/paste into SSMS was no good.
    Then the stored procedure dba_CapacityPlanning needs be executed in order to create the table tbl_CapacityPlanning.
    Finally, remove the comments ( — ) from the last select query to

    select * from msdb..tbl_CapacityPlanning

    and execute only this part of it. Should work.

    @Deepak: I did run the script and I really like it. I am a little unclear how to best used this for trending use. In a post above, you mention to create a view that can then be queried.
    Can you give us a synopsis of the different steps to use this stored procedure for trending?
    Thank you for sharing your work.

    […] SQL Server Query for Capacity Planning – SQL 2005 & 2008 … – As a proactive DBA its one of responsibility to keep an eye on database’s growth and other file system changes for the databases hosted on SQL instance. […]

    […] SQL Server Query for Capacity Planning – … – As a proactive DBA its one of responsibility to keep an eye on database’s growth and other file system changes for the databases hosted on SQL instance. […]

    […] SQL Server Query for Capacity Planning – … – As a proactive DBA its one of responsibility to keep an eye on database’s growth and other file system changes for the databases hosted on SQL instance. […]

    […] SQL Server Query for Capacity Planning … – As a proactive DBA its one of responsibility to keep an eye on database’s growth and other file system changes for the databases hosted on SQL instance. […]

    Write a comment

    *

    © 2010 SQLKnowledge.com Increase your website traffic with Attracta.com