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

avatar image

basit_khan
80 4 4 5

(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

avatar image

KenJ
24.8k 3 10 19

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

avatar image

basit_khan
80 4 4 5

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

avatar image

tounkara
50 1 1 3

(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:

x279
x100

asked: May 29, 2012 at 04:43 PM

Seen: 9381 times

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

Copyright 2016 Redgate Software. Privacy Policy