Archive for February, 2008

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


__________________________________________________________________________________________________________________________________________________

Monitor Database Growth For SQLSERVER2005
__________________________________________________________________________________________________________________________________________________

–PART 1

If exists (Select name from sys.objects where name = ‘DBGrowthRate’ and Type = ‘U’)
Drop Table dbo.DBGrowthRate

Create Table dbo.DBGrowthRate (DBGrowthID int identity(1,1), DBName varchar(100), DBID int,
NumPages int, OrigSize_In_MB decimal(10,2), CurSize_In_MB decimal(10,2), GrowthAmt varchar(100),
MetricDate datetime)

Drop table #TempDBSize

Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize_In_MB, CurSize_In_MB, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
‘0.00 MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)

–Drop table #TempDBSize

Select *
from DBGrowthRate
–Above creates initial table and checks initial data

–PART 2
–Below is the code run weekly to check the growth.
Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize2
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

If Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
and Convert(varchar(10),GetDate(),101) > (Select Distinct Convert(varchar(10),Max(MetricDate),101) as MetricDate

from DBGrowthRate)

Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
dgr.CurSize as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
Convert(varchar(100),(Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024))

– dgr.CurSize)) + ‘ MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
join DBGrowthRate dgr
on tds.database_ID = dgr.DBID
Where DBGrowthID = (Select Distinct Max(DBGrowthID) from DBGrowthRate
where DBID = dgr.DBID)

Group by tds.database_ID, tds.DBName, dgr.CurSize)
End
Else
IF Not Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
‘0.00 MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.database_ID)

Group by tds.database_ID, tds.DBName)
End

–Select *
–from DBGrowthRate
—-Verifies values were entered

Drop table #TempDBSize2

_____________________________________________________________________________________________________________


Monitor Database Growth FOR SQLSERVER2000

_____________________________________________________________________________________________________________

–PART 1

If exists (Select name from sys.objects where name = ‘DBGrowthRate’ and Type = ‘U’)
Drop Table dbo.DBGrowthRate

Create Table dbo.DBGrowthRate (DBGrowthID int identity(1,1), DBName varchar(100), DBID int,
NumPages int, OrigSize_In_MB decimal(10,2), CurSize_In_MB decimal(10,2), GrowthAmt varchar(100),
MetricDate datetime)

Drop table #TempDBSize

Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize
from sysdatabase sd
join sysaltfiles mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize_In_MB, CurSize_In_MB, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
‘0.00 MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate where DBName = tds.database_ID)

Group by tds.database_ID, tds.DBName)

–Drop table #TempDBSize

Select *
from DBGrowthRate
–Above creates initial table and checks initial data

–PART 2
–Below is the code run weekly to check the growth.
Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize2
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

If Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
and Convert(varchar(10),GetDate(),101) > (Select Distinct Convert(varchar(10),Max(MetricDate),101) as MetricDate

from DBGrowthRate)

Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
dgr.CurSize as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
Convert(varchar(100),(Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024))

– dgr.CurSize)) + ‘ MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
join DBGrowthRate dgr
on tds.database_ID = dgr.DBID
Where DBGrowthID = (Select Distinct Max(DBGrowthID) from DBGrowthRate
where DBID = dgr.DBID)

Group by tds.database_ID, tds.DBName, dgr.CurSize)
End
Else
IF Not Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
‘0.00 MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.database_ID)

Group by tds.database_ID, tds.DBName)
End

–Select *
–from DBGrowthRate
—-Verifies values were entered

Drop table #TempDBSize2

Thanks
Md. S.Hassan
Sqlserver Database Administrator