Technical Article

DbSizeTracking

,

Monitor the long term growth of Many databases on Server
-Create the table DbSizeTracking on database of choice
-Create a job to run the script on weekly intervals
-Query the table with section at end of script

-- Create table for size tracking
CREATE table DbSizeTracking (
name varchar(50), 
db_size varchar(20), 
dbowner varchar(50), 
dbid int, 
crdate datetime, 
status varchar(1000),
compatibilityLevel int,
TrackDate datetime )
GO

-- Create SQL SERVER job to run the script below
SET NOCOUNT ON
Create table #dbsize (
dbname varchar(50), 
[size] varchar(20), 
dbowner varchar(50), 
dbid int, 
crdate datetime, 
status char(1000),
compatibilityLevel int)

Insert #dbsize  Exec sp_helpdb

INSERT  DbSizeTracking ([name], db_size, dbowner, dbid, crdate, status,compatibilityLevel,TrackDate)

SELECT dbname, [size], dbowner, dbid, crdate, status,compatibilityLevel, getdate()

FROM #dbsize

Drop table #dbsize
SET NOCOUNT OFF

-- Query the table with the following after several entries
/*
SELECT [Name], db_size, TrackDate
FROM DbSizeTracking
--WHERE [name]= '%dbname%'
ORDER BY [Name], db_size, TrackDate
*/

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating