Sunday, April 29, 2012

Script to capture Database growth

Lets say you have 10 sql servers in your environment and you have to capture growth for all the databases in all your sql servers.
Here are simple steps to do this:

1) create linked servers for all those sql servers
2) create an empty database and an empty table like this:
CREATE TABLE [dbo].[DB_size](
    [Instance_Name] [varchar](100) NULL,
    [DB_Name] [varchar](50) NULL,
    [Datafile_size_In_MB] [float] NULL,
    [updated_on] [datetime] NULL
) ON [PRIMARY]
 
3) create a job under sql server agent jobs and add the script as below

insert into dbo.db_size
select (select name from [instance1].MASTER.sys.servers where server_id=0) 'Instance' ,
(select name from [instance1].master.dbo.sysdatabases where dbid=a.dbid)   'database',
cast(SUM(size*8)as float(4))/1024 'Datafile Size in MB' ,GETDATE() 'date'
from [instance1].master.dbo.sysaltfiles a where dbid not in (1,2,3,4,32767) and fileid<>2
group by dbid,size
go
if you have 5 sql servers then prepare 5 insert statements (one for each instance) and add to the job step

4) Schedule your job to run once a week/month 

That's it