>>/************************************************************** >>Name: sGetDBMaintInfo >>Description: >> Gather the data and log space for all databases on the system >>and insert the information into DBMaintInfo. The following databases >>are not added to DBMaintInfo: >> pubs >> Northwind >> model >> tempdb >> >>Author: Rollin Burr >>**************************************************************/ >>CREATE PROCEDURE sGetDBMaintInfo AS >> >>-- IF DBMaintInfo table does not yet exist, create it. >>IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DBMaintInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) >>BEGIN >> CREATE TABLE [dbo].[DBMaintInfo] ( >> [DatabaseName] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , >> [DataSpaceUsed_MB] [real] NULL , >> [DataSpaceFree_MB] [real] NULL , >> [TLogSpaceUsed_MB] [real] NULL , >> [TLogSpaceFree_MB] [real] NULL , >> [LastFullBackup] [datetime] NULL , >> [LastDiffBackup] [datetime] NULL , >> [LastTransBackup] [datetime] NULL , >> [EntryDate] [datetime] NOT NULL >> ) ON [PRIMARY] >> >> ALTER TABLE [dbo].[DBMaintInfo] WITH NOCHECK ADD >> CONSTRAINT [PK_DBMaintInfo] PRIMARY KEY CLUSTERED >> ( >> [DatabaseName], >> [EntryDate] >> ) ON [PRIMARY] >> >> ALTER TABLE [dbo].[DBMaintInfo] ADD >> CONSTRAINT [DF_DBMaintInfo_EntryDate] DEFAULT (getdate()) FOR [EntryDate] >>END --IF NOT EXISTS >> >>-- Set up error check >>SET NOCOUNT ON >>DECLARE @err INT >>SET @err = 0 >> >>--Create the temp tables to hold the results of DBCC commands until the information is entered into DBMaintInfo. >>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)') >> >>/* >>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 >> @DBName VARCHAR( 100), >> @LogSize FLOAT, >> @PrcntUsed FLOAT, >> @cmd VARCHAR( 500), >> @DBMIID INT >>DECLARE dbname CURSOR >>FOR SELECT DBName, LogSize, PrcntUsed FROM #logspace >>OPEN dbname >>FETCH NEXT FROM dbname INTO @DBName, @LogSize, @PrcntUsed >>WHILE @@fetch_status = 0 >>BEGIN >> SELECT @cmd = 'use ' + rtrim( @DBName) + ' dbcc showfilestats' >> INSERT #dataspace >> EXEC( @cmd) >> IF @DBName NOT IN ('model','tempdb','pubs','Northwind') >> BEGIN >> INSERT INTO DBMaintInfo (DatabaseName, DataSpaceUsed_MB, DataSpaceFree_MB, >> TLogSpaceUsed_MB, TLogSpaceFree_MB, EntryDate) >> SELECT @DBName, >> ((cast( UsdExt AS NUMERIC( 10, 4))* 32) / 512), >> ((cast( TotExt - UsdExt AS NUMERIC( 10, 4))* 32) / 512), >> @LogSize * (@PrcntUsed/100), >> @LogSize * ((100-@PrcntUsed)/100), >> GETDATE() >> FROM #dataspace >> END >> >> FETCH NEXT FROM dbname INTO @DBName, @LogSize, @PrcntUsed >> DELETE #dataspace >>END >>CLOSE dbname >>DEALLOCATE dbname >> >>-- Drop the temporary tables >>DROP TABLE #logspace >>DROP TABLE #dataspace >> >>-- Remove all data from table that is older than 90 days >>DELETE FROM DBMaintInfo WHERE EntryDate < GETDATE() - 90 >> >>RETURN @err >> >>GO >>>