Script For Database,Drive & Data file growth monitor

Posted: February 26, 2008 in Sqlserver

Database,Drive,Data file growth monitor

/*****************************************************************************************************
First Create Procedure and execute on Sqlserver2000 server
*****************************************************************************************************/

Use Northwind
go
create procedure SpaceInfo @dbname varchar(100)
as
DECLARE @SQLCode varchar(5000)
SET @SQLCode =
‘USE [‘ + @DBName +’]
DECLARE @SpaceSeeker TABLE ([Group_Name] varchar(50), [File_Name] varchar(100), Logical_Disk char(3),[File_Location] varchar(100),
[Allocated_Space] decimal(15,4), [Actual_Size] decimal(15,4), [Space_Available] decimal(15,4),
[Total_FREE_Drive_Space] decimal(15,2), [Max_Growth] char(16),[File_Growth] varchar(15))
— insert calculations into table

INSERT INTO @SpaceSeeker ([Group_Name] , [File_Name] ,Logical_Disk,[File_Location], [Allocated_Space] , [Actual_Size],

[Total_FREE_Drive_Space] , [Max_Growth] ,[File_Growth])
SELECT
Group_Name = sfg.groupname,
[File_Name] = sf.[name],
Logical_Disk=LEFT([filename],3),
File_Location = [filename],
Allocated_space = sf.[size] * 8 /1024,
Actual_Size = FILEPROPERTY(sf.[name], ”SPACEUSED”) * 8 /1024,
Total_Disk_Space = 0.0,
MAX_GROWTH = CASE
WHEN sf.[maxsize] = 0 THEN ”NO GROWTH SET”
WHEN sf.[maxsize] = -1 THEN ”NO MAX LIMIT SET”
ELSE STR((sf.[maxsize] * 8) /1024)+ ” MB”
END,
File_Growth=CASE
WHEN (sf.status&0x100000) > 0 THEN STR(sf.growth)+” %”
ELSE STR((sf.growth * 8) /1024)+” MB”
END
— join sysfilegroup just to get filegroup name
FROM sysfiles sf (NOLOCK) LEFT OUTER JOIN sysfilegroups sfg (NOLOCK)
ON sf.groupid = sfg.groupid
ORDER BY 1
— make a second pass to update table
— this will store the actual space available in the file
UPDATE @SpaceSeeker
SET Space_Available = ((Allocated_Space) – (Actual_Size ))
–*****************************************************
— this is to update total drive space. it uses xp_fixeddrives and temp table
— reason for temp table is that exec is not support when doing an insert into a table variable
— this statement is a little different in the job
Create TABLE #DRIVESPACE ([Logical_Disk] char(1), [MB_Free] varchar(15))
INSERT #DriveSpace
exec (”master..xp_fixeddrives”)
update @SpaceSeeker
SET Total_FREE_Drive_Space = (MB_FREE)/1024
from #DriveSpace a JOIN @SpaceSeeker b ON a.Logical_Disk =LEFT(b.Logical_Disk,1)
drop table #DriveSpace
–*******************************************************
IF NOT EXISTS (SELECT 1 FROM Northwind.DBO.sysobjects WHERE NAME = ”DBA_DBSize_Growth” AND type = ”U”)
BEGIN
CREATE TABLE Northwind.DBO.DBA_DBSize_Growth
(
[Database_Name] varchar(50), [Group_Name] varchar(50), [File_Name] varchar(50),

Logical_Disk char(3),[File_Location] varchar(100),
[Allocated_Space] decimal(15,4), [Actual_Size] decimal(15,4), [Space_Available]

decimal(15,4),
[Total_FREE_Drive_Space_GB] decimal(15,4), [Max_Growth] char(16),[File_Growth]

varchar(15),
[Size_Date] smalldatetime DEFAULT (GETDATE())
)
END
–INSERT INTO Northwind
INSERT INTO Northwind.DBO.DBA_DBSize_Growth
SELECT ”’ + @DBName + ”’,
Group_Name ,
File_Name ,
Logical_Disk,
File_Location,
Total_Allocated_Space_MB = Allocated_Space ,
Actual_File_Size_MB = Actual_Size,
Space_Available_MB = Space_Available ,
Total_FREE_Drive_Space,
MAX_GROWTH_MB = MAX_GROWTH ,
File_Growth,
Size_Date= GETDATE()
FROM @SpaceSeeker
GO’

–print @SQLCode
EXEC (@SQLCode)
–***************************************************************************************
GO

/********************************************************************************************

Place in job to do a nightly dump into table for monitoring purposes.

*********************************************************************************************/

USE Northwind
GO

DECLARE @DBName varchar(50), @SQL nvarchar(250)

DECLARE tmpDBRetrieval CURSOR FOR
SELECT [name] from master.dbo.sysdatabases
WHERE DBID NOT IN (1,2,3,4)–5,7,8,9,10,11,12) –AND status NOT IN (16,32,64,128,256,512,520)
ORDER BY [name]
OPEN tmpDBRetrieval
FETCH NEXT FROM tmpDBRetrieval
INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N’exec Northwind.dbo.SpaceInfo ”’ + @DBName + ””
EXEC (@SQL)
–print @sql
IF @@ERROR <> 0
BEGIN
GOTO IFERROR
END
IFERROR:
FETCH NEXT FROM tmpDBRetrieval
INTO @DBName

END
CLOSE tmpDBRetrieval
DEALLOCATE tmpDBRetrieval

/***************************************************************************************************

To get result

***************************************************************************************************/

SELECT * FROM DBA_DBSize_Growth

***************************************************************************************************


Thanks,

S.Hassan
Sr. Sqlserver Database Specialist(GNCS)
i-Vantage India Pvt Ltd
501, 5th Floor, Trendset Towers,
Rd No.: 2, Banjara Hills,
Hyderabad – 34, India
+91 (40) 23544450 (O) x 344,
+91 (40) 23543704 (F), +91 9885072079(M)
www.i-vantage.com

BOSTON | CHICAGO | NEW YORK | LONDON | HYDERABAD | BANGALORE | KOLKATA

Advertisements

Comments are closed.