>>>>>/************************************************************** >>>>>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 >>>>>>>>>