October 19, 2006 at 6:21 am
Does anyone have an automated process for tracking database growth ? I guess what I am looking for is a stored prod to run to insert info on database size into a table and be able to query that table to see growth of a database over time. Currently I manually go into Ent. manager once a month and use taskpad and keep this info in a word doc.
October 19, 2006 at 6:30 am
Search the script section on this site, for example
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1789
I use this
set nocount on
/*
Create the temp tables to hold the results of DBCC
commands until the information is entered into
DatabaseSpaceUsage
*/
CREATE TABLE #logspace
(DBName varchar( 100),
LogSize float,
PrcntUsed float,
status int)
CREATE TABLE #dataspace
(FileID int,
FileGrp int,
TotExt int,
UsdExt int,
LFileNm varchar( 100),
PFileNm varchar( 100))
/*
Get the log space
*/
INSERT INTO #logspace
EXEC ('dbcc sqlperf( logspace)')
insert DatabaseSpaceUsage
select dbname,
logsize,
(logsize * (PrcntUsed/100)),
PrcntUsed, --(1 - ( PrcntUsed / 100))*100,
dbname,
'Log',
getdate()
from #logspace
/*
Get the data space
Use a cursor to loop through the results from DBCC
since you have to run this command from each database
with a USE command.
*/
declare @db char( 40), @cmd char( 500)
declare dbname cursor
for select DBName from #logspace
open dbname
fetch next from dbname into @db
while @@fetch_status = 0
begin
select @cmd = 'use [' + rtrim( @db) + '] dbcc showfilestats'
insert #dataspace
exec( @cmd)
insert DatabaseSpaceUsage
select substring( lFileNM, 1, 20),
((cast(TotExt as numeric( 18, 4))* 32) / 512),
((cast(UsdExt as numeric( 18, 4))* 32) / 512),
(cast(UsdExt as numeric( 18, 4)) * 100) / cast(TotExt as numeric( 18, 4)),
--((cast(TotExt - UsdExt as numeric( 18, 4))* 32) / 512),
@db,
'Data',
getdate()
from #dataspace
fetch next from dbname into @db
delete #dataspace
end
deallocate dbname
/*
Drop the temporary tables
*/
drop table #logspace
drop table #dataspace
It is based on examples on this site, might even been the one I linked above
Edited:
p.s. I run this daily as a scheduled job before my database backups (so that the log file stats are accurate)
Far away is close at hand in the images of elsewhere.
Anon.
October 19, 2006 at 7:06 am
Hope this link help you out
http://www.sqlservercentral.com/memberservices/updatescript.asp?Approve=y&scriptid=1789
Ram
October 20, 2006 at 10:12 am
I track remaining free space per drive (from xp_fixeddrives) on my production servers by running a scheduled job on each server every four hours that adds the free space for data, log, and backup drives to a table. I then created a spreadsheet with an autostart macro that connects to each server and pulls down new data from each server. The numeric data goes to hidden pages, the visible page is a graph that shows a line for every drive. It is immediately obvious with a quick glance whether there are any sudden dips.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply