x

How to moniter the growth Rate of SQL Server Database

Hi Team

I am New In SQL Server and i have given a task to create a job which calculate the growth rate of SQL Database .

Is there any query through which i can calculate the growth rate of my SQL Database

Thanks

Basit Khan
more ▼

asked May 29, 2012 at 04:43 PM in Default

basit_khan gravatar image

basit_khan
80 4 4 4

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You'll need a table to store the sizes and record the time you recorded the size. Then schedule a query to record the sizes. How often you run it depends on the level of granularity you want to use for your trend - hourly, daily, weekly etc.

I like to track data file size separately from log size, and the scripts below reflect a very simple version of that. You can get quite sophisticated if you need.

Create the tables (you won't want yours in tempdb, I just put together a sample you can run from end-to-end that cleans up after itself):

USE tempdb
GO
CREATE TABLE dbo.databaseDataFileSize (
    rowId INT IDENTITY(1,1), 
    dbName sysname, 
    dbSize BIGINT, 
    sampleDate DATE, 
    sampleTime TIME)
GO
CREATE TABLE dbo.databaseLogFileSize (
    rowId INT IDENTITY(1,1), 
    dbName sysname, 
    dbSize BIGINT, 
    sampleDate DATE, 
    sampleTime TIME)
GO

Run a query periodically to populate them:

EXEC sp_msforeachdb '
INSERT INTO tempdb.dbo.databaseDataFileSize 
SELECT ''?'', SUM(size) * 8, cast(getdate() as date), cast(getdate() as time) 
FROM [?].sys.database_files
WHERE type = 0;

INSERT INTO tempdb.dbo.databaseLogFileSize 
SELECT ''?'', SUM(size) * 8, cast(getdate() as date), cast(getdate() as time) 
FROM [?].sys.database_files
WHERE type = 1;'
GO

Query the table with excel for pretty trend graphs. Or, just run something in SSMS:

SELECT * FROM tempdb.dbo.databaseDataFileSize
SELECT * FROM tempdb.dbo.databaseLogFileSize

-- this cleans up the tables from the sample
DROP TABLE tempdb.dbo.databaseDataFileSize
DROP TABLE tempdb.dbo.databaseLogFileSize
more ▼

answered May 29, 2012 at 06:26 PM

KenJ gravatar image

KenJ
20.4k 1 4 12

As well as the size of the database files, it's also worth keeping track of the amount of free space or space used within the database...
May 30, 2012 at 08:21 AM ThomasRushton ♦
@ThomasRushton Very nice suggestion.
May 30, 2012 at 09:36 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

Thanks Every one....i have tried to make this query from two table in MSDB Database 1 Backupset and other is backupfile

    SELECT  CONVERT(char, backup_start_date, 111) AS [Date], --yyyy/mm/dd format
        CONVERT(char, backup_start_date, 108) AS [Time],
        @dbname AS [Database Name], [filegroup_name] AS [Filegroup Name], logical_name AS [Logical Filename], 
        physical_name AS [Physical Filename], CONVERT(numeric(9,2),file_size/1048576) AS [File Size (MB)],
        Growth AS [Growth Percentage (%)]
FROM
(
        SELECT  b.backup_start_date, a.backup_set_id, a.file_size, a.logical_name, a.[filegroup_name], a.physical_name,
               (
                       SELECT  CONVERT(numeric(10,2),((a.file_size * 100.00)/i1.file_size)-100)
                       FROM    msdb.dbo.backupfile i1
                       WHERE   i1.backup_set_id = 
                                              (
                                                     SELECT  MAX(i2.backup_set_id) 
                                                     FROM    msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3
                                                             ON i2.backup_set_id = i3.backup_set_id
                                                     WHERE   i2.backup_set_id < a.backup_set_id AND 
                                                             i2.file_type='D' AND
                                                             i3.database_name = @dbname AND
                                                             i2.logical_name = a.logical_name AND
                                                             i2.logical_name = i1.logical_name AND
                                                             i3.type = 'D'
                                              ) AND
                               i1.file_type = 'D' 
               ) AS Growth
        FROM    msdb.dbo.backupfile a JOIN msdb.dbo.backupset b 
               ON a.backup_set_id = b.backup_set_id
        WHERE   b.database_name = @dbname AND
               a.file_type = 'D' AND
               b.type = 'D'

) as Derived
WHERE (Growth <> 0.0) OR (Growth IS NULL)
ORDER BY logical_name, [Date]
Provide the Database name
more ▼

answered May 30, 2012 at 10:46 AM

basit_khan gravatar image

basit_khan
80 4 4 4

That seems like a reasonable approach to looking at historical changes to database backup file sizes.

Going forward, I would still suggest that it makes sense to do trending that is a little more intentional.
May 30, 2012 at 08:25 PM KenJ

tried to run the query but getting the error message below: Msg 137, Level 15, State 2, Line 3 Must declare the scalar variable "@dbname". Msg 137, Level 15, State 2, Line 19 Must declare the scalar variable "@dbname"

Not sure how to solve this. Thx. Gary
Nov 15, 2012 at 02:32 PM tounkara
Just replace @dbname with the name of the database you're interested in.
Nov 15, 2012 at 06:31 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
Is there a way to pass the dbnames using a variable instead of typing each one at a time?
more ▼

answered Nov 15, 2012 at 08:24 PM

tounkara gravatar image

tounkara
50 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x244
x87

asked: May 29, 2012 at 04:43 PM

Seen: 7154 times

Last Updated: Nov 15, 2012 at 08:24 PM