question

basit_khan avatar image
basit_khan asked

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
databasejob
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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...
2 Likes 2 ·
@ThomasRushton Very nice suggestion.
0 Likes 0 ·
basit_khan avatar image
basit_khan answered
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
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
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
0 Likes 0 ·
Just replace `@dbname` with the name of the database you're interested in.
0 Likes 0 ·
tounkara avatar image
tounkara answered
Is there a way to pass the dbnames using a variable instead of typing each one at a time?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.