Database size Growth chart report (graphically)

Leave a comment (18) Go to comments

At time DBA need to develop a custom report to get database sizes on each individual SQL instances that are running. At times, we might wanted to use this report and create custom charts on database growth for each server on a weekly/monthly basis.

Here we are taking a example, we wanted to represent database file growth on weekly basis over a period of time that too without using any third party monitoring software.

In short, this sort of result set is expected where Management can be pleased with a graphical report to show the how fast the database file size is growing.

Database size Growth chart report (graphically) sql server storage planning script sql server storage planning sql server db info SQL Server Database Size information sql server capacity planning SQL Script to get historical database size Database size Growth chart report database information

This is very simple to implement, here are the steps

  1. Get the current database information
  2. Store this information in some table inside the SQL Server Database
  3. Make a scheduled job, which will repeat the step one and two (storing current database information inside the table)
  4. Extract the Data from the table which you have in table, table created in step 2
  5. Copy the information on excel and make a chart using the excel charts
  6. That it !!! you are ready with your report that too without any third party monitoring software

Let’s see How we do this..


Download this Script in case you have some syntax error

Step 1 – Get the current database information

This script will give you all the information related to Database size and current state and physical file location in the following columns

DECLARE @DBInfo TABLE

( ServerName VARCHAR(100),

DatabaseName VARCHAR(100),

FileSizeMB INT,

LogicalFileName sysname,

PhysicalFileName NVARCHAR(520),

Status sysname,

Updateability sysname,

RecoveryMode sysname,

FreeSpaceMB INT,

FreeSpacePct VARCHAR(7),

FreeSpacePages INT,

PollDate datetime)

DECLARE @command VARCHAR(5000)

SELECT @command = ‘Use [‘ + ‘?’ + ‘] SELECT

@@servername as ServerName,

+ ”” + ‘?’ + ”” + ‘ AS DatabaseName,

CAST(sysfiles.size/128.0 AS int) AS FileSize,

sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,

CONVERT(sysname,DatabasePropertyEx(”?”,”Status”)) AS Status,

CONVERT(sysname,DatabasePropertyEx(”?”,”Updateability”)) AS Updateability,

CONVERT(sysname,DatabasePropertyEx(”?”,”Recovery”)) AS RecoveryMode,

CAST(sysfiles.size/128.0 – CAST(FILEPROPERTY(sysfiles.name, ‘ + ”” +

‘SpaceUsed’ + ”” + ‘ ) AS int)/128.0 AS int) AS FreeSpaceMB,

CAST(100 * (CAST (((sysfiles.size/128.0 – CAST(FILEPROPERTY(sysfiles.name,

+ ”” + ‘SpaceUsed’ + ”” + ‘ ) AS int)/128.0)/(sysfiles.size/128.0))

AS decimal(4,2))) AS varchar(8)) + ‘ + ”” + ‘%’ + ”” + ‘ AS FreeSpacePct,

GETDATE() as PollDate FROM dbo.sysfiles’

INSERT INTO @DBInfo

(ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status,
Updateability, RecoveryMode, FreeSpaceMB, FreeSpacePct, PollDate)

EXEC sp_MSForEachDB @command

SELECT

ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode, FreeSpaceMB,

FreeSpacePct, PollDate FROM @DBInfo ORDER BY ServerName, DatabaseName

QUERY OUTPUT

Database size Growth chart report (graphically) sql server storage planning script sql server storage planning sql server db info SQL Server Database Size information sql server capacity planning SQL Script to get historical database size Database size Growth chart report database information

ServerName – Server Name where you are running this report
DatabaseName – Database Name for which information is fetched
FileSizeMB – Physical file in MB which is consumed at OS level
LogicalFileName – Logical Database file name
PhysicalFileName – Location, where database file is hosted
Status – What is current status of the database
Updateability – confirms weather database is in read only mode or allowed for updated too
RecoveryMode – Recovery Model of database
FreeSpaceMB – Free size in database file, which is calculated by counting free pages in file, this is unused space inside the database file
FreeSpacePct – % of free space with respect to total file size.
PollDate  - When this information is fetched

If you wanted to have this for one time, you end here but if you wanted to create a historical data too, proceed to remaining steps

Step 2 – Store this information in some table inside the SQL Server Database

Create a table where this information will be saved. You can create this table on your admin database, if you have any. In this example, we will use ‘ADMIN‘ as a database name,

use ADMIN
go
create TABLE  DBInfo
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime)

Step 3 – Make a scheduled job, which will repeat the step one and two (storing current database information inside the table)

Once table is created we need to make a scheduled job so that we can fill data in this table on specified schedule, which a could be a daily or a weekly. Make a new SQL Server Agent Job and write the following code inside

Database size Growth chart report (graphically) sql server storage planning script sql server storage planning sql server db info SQL Server Database Size information sql server capacity planning SQL Script to get historical database size Database size Growth chart report database information

DECLARE @DBInfo TABLE

( ServerName VARCHAR(100),

DatabaseName VARCHAR(100),

FileSizeMB INT,

LogicalFileName sysname,

PhysicalFileName NVARCHAR(520),

Status sysname,

Updateability sysname,

RecoveryMode sysname,

FreeSpaceMB INT,

FreeSpacePct VARCHAR(7),

FreeSpacePages INT,

PollDate datetime)

DECLARE @command VARCHAR(5000)

SELECT @command = ‘Use [‘ + ‘?’ + ‘] SELECT

@@servername as ServerName,

+ ”” + ‘?’ + ”” + ‘ AS DatabaseName,

CAST(sysfiles.size/128.0 AS int) AS FileSize,

sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,

CONVERT(sysname,DatabasePropertyEx(”?”,”Status”)) AS Status,

CONVERT(sysname,DatabasePropertyEx(”?”,”Updateability”)) AS Updateability,

CONVERT(sysname,DatabasePropertyEx(”?”,”Recovery”)) AS RecoveryMode,

CAST(sysfiles.size/128.0 – CAST(FILEPROPERTY(sysfiles.name, ‘ + ”” +

‘SpaceUsed’ + ”” + ‘ ) AS int)/128.0 AS int) AS FreeSpaceMB,

CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,

+ ”” + ‘SpaceUsed’ + ”” + ‘ ) AS int)/128.0)/(sysfiles.size/128.0))

AS decimal(4,2))) AS varchar(8)) + ‘ + ”” + ‘%’ + ”” + ‘ AS FreeSpacePct,

GETDATE() as PollDate FROM dbo.sysfiles’

INSERT INTO @DBInfo (ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status,
Updateability, RecoveryMode, FreeSpaceMB, FreeSpacePct, PollDate)

EXEC sp_MSForEachDB @command

INSERT INTO admin.dbo.DBInfo (ServerName,DatabaseName,FileSizeMB,LogicalFileName,PhysicalFileName,Status,Updateability,RecoveryMode,FreeSpaceMB,FreeSpacePct,PollDate)

SELECT

ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode, FreeSpaceMB,

FreeSpacePct, PollDate FROM @DBInfo

Step 4 – Extract the Data from the table which you have in table, table created in step 2

Sample Query, you can modify the query as per your requirement by specifying database name in where clause

SELECT

ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode, FreeSpaceMB,

FreeSpacePct, PollDate FROM admin.dbo.DBInfo ORDER BY ServerName, DatabaseName

Step 5 – Copy the information on excel and make a chart using the excel charts

Use Date and Size column to build the chart

Step 6 – That it !!! you are ready with your report that too without any third party monitoring software

EOF - Database size Growth chart report (graphically), SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

18 Comments.

  1. hi..i want to know the db size in monthly wise in the particular server..can anyone help me out for this?

  2. Good Post!

    Please also expain how to create charts with the data

    Thanks :)

  3. this is really a good post

    Can you also post how to create charts based on our data

    Thanks!

  4. Hi,

    I think we can do step 5 (copy to excel and generate report) with SSIS :razz:

  5. Hi,

    How to chart this on excel?

  6. Hi,

    i have one more requirement. how to see the last update/write timestamp information for a DB in SQL 2000

  7. hi,

    please help me.

  8. hi,

    could you please help me to get this query work on SQL 2000.

    Msg 197, Level 15, State 1, Line 34
    EXECUTE cannot be used as a source when inserting into a table variable.

  9. Hi,

    And also i want to run this query from centralized SQL server and it needs to fetch the information from multiple SQL servers and together it in central server.

    is any way to do this.

    thanks in advance.

  10. Hi,

    While Executing the code in SQL 2000 am getting the below error. And as per my requirement i need the DB used space also should be displayed. can you please incorporate the modification in that for SQL all version. since am very new entering in DBA. thanks for your help provided so far.

    Msg 197, Level 15, State 1, Line 34
    EXECUTE cannot be used as a source when inserting into a table variable.

  11. It should work in SQL Server 2000 too. Let me know, in case you are getting some issues in running in SQL Server 2000.

  12. HI,

    Thanks a lot……
    i have got your email and it working fine in SQL 2005 & SQL 2008.

    but i need it for SQL 2000 also. could you please help me.

  13. Hi,

    i have tried the same. still am getting the error.

    used Query :

    select @command = use ['and'?'and'] select

    output :

    Msg 156, Level 15, State 1, Line 29
    Incorrect syntax near the keyword ‘use’.
    Msg 911, Level 16, State 1, Line 29
    Could not locate entry in sysdatabases for database ”and’?’and”. No entry found with that name. Make sure that the name is entered correctly.

  14. Msg 170, Level 15, State 1, Line 29
    Line 29: Incorrect syntax near ‘‘’.
    Msg 911, Level 16, State 1, Line 29
    Could not locate entry in sysdatabases for database ” + ‘?’ + ”. No entry found with that name. Make sure that the name is entered correctly.

    hi,

    while running in SQL 2000 am getting this error.
    can you please resolve the issue and also whether this query is applicable for all SQL version like 2005 & 2008.

    • Do you have any special character in any user database name, if the answer is yes Please use ‘[' and ']‘ prior and after ?

  15. This means you are running this query in a case sensitive environment, please change the case of the query to lower case.

  16. Raymond Kamphuis

    Hi i got a problem with the first query, this was the result. Please can you tell me what’s going wrong???

    Msg 102, Level 15, State 1, Line 31
    Incorrect syntax near ‘‘’.
    Msg 911, Level 16, State 1, Line 31
    Database ” + ‘Master’ + ” does not exist. Make sure that the name is entered correctly.

    regards Raymond

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.