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

-- Filename : USP_DBSIZEWATCH.SQL

*/

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

CREATE PROCEDURE [dbo].[usp_DBSizeWatch]

AS

SET NOCOUNT On

--Declare current date and Time

declare @dtpk datetime

select @dtpk=getdate()

print @dtpk

-- Delete work table just in Case

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DBFileSize23]') AND OBJECTPROPERTY(id, N'IsTable') = 1)

BEGIN

DROP TABLE [dbo].[DBFileSize23]

End

-- Create master table to hold the information

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DB_SizeWatch]') AND OBJECTPROPERTY(id, N'IsTable') = 1)

BEGIN

Create table master.dbo.DB_SizeWatch(

[dbname] varchar (128),

[logsize] real,

[logused] real,

[logpercentused] real,

[logfree] real,

[dbfilesize] real,

[dbfileused] real,

[dbfilefree] real,

[dbsize] real,

[CurrentDate] datetime)

End

-- Clean out old entries

DELETE FROM master.dbo.DB_SizeWatch

WHERE datediff(dd,[CurrentDate], getdate()) >= 182

-- Create Temp work table

Create table master.dbo.DBFileSize23(

[dbname] varchar (128),

[logsize] real,

[logused] real,

[logpercentused] real,

[logfree] real,

[status] tinyint,

[dbfilesize] real,

[dbfileused] real,

[dbfilefree] real,

[dbsize] real,

[CurrentDate] datetime)

DECLARE @dbname nvarchar(100),

@logsize real,

@logused real,

@bytesperpage dec(15,0),

@pagesperMB dec(15,0),

@reserved dec(15,0),

@unused dec(15,0),

@dbsize dec(15,0),

@pages int,

@SQLstring nvarchar(900)

-- Pull data from the dbcc into the temp table

INSERT INTO master.dbo.DBFileSize23 (dbname,logsize,logpercentused,status)

EXECUTE ('dbcc sqlperf (logspace)')

-- Having that table gives us a nice list of databases

DECLARE logcursor CURSOR READ_ONLY For

SELECT [dbname], [logsize], [logused]

FROM master.dbo.DBFileSize23

-- Open cursor

OPEN logcursor

-- Fetch first records

FETCH NEXT FROM logcursor

INTO @dbname, @logsize, @logused

-- Fetch the basic information about the server (this will apply to all db's)

SELECT @bytesperpage = low

FROM master.dbo.spt_values

WHERE [number] = 1

AND [type] = 'E'

SET @pagesperMB = 1048576 / @bytesperpage

WHILE(@@FETCH_STATUS = 0)

BEGIN

-- Build SQL String

SET @SQLstring = 'UPDATE master.dbo.DBFileSize23 SET [dbsize] =

((SELECT sum(convert(dec(15),size))

FROM [' + @dbname + '].[dbo].sysfiles WHERE (status & 64 = 0)) + (SELECT sum(convert(dec(15),size))

FROM [' + @dbname + '].[dbo].sysfiles

WHERE (status & 64 <> 0))) / ' + cast(@pagesperMB as nvarchar(20)) + ',

dbfilesize = (select sum(convert(dec(15),size))

FROM [' + @dbname + '].[dbo].sysfiles where (status & 64 = 0)) / ' + cast(@pagesperMB as nvarchar(20)) + ',

dbfilefree = ((select sum(convert(dec(15),size))

FROM [' + @dbname + '].[dbo].sysfiles where (status & 64 = 0)) -

(SELECT sum(convert(dec(15),reserved)) FROM [' + @dbname + '].[dbo].sysindexes

WHERE indid in (0, 1, 255))) / ' + cast(@pagesperMB as nvarchar(20)) + '

WHERE dbname = ''' + @dbname + ''''

EXEC sp_executesql @SQLstring

-- Fetch next record

FETCH NEXT FROM logcursor

INTO @dbname, @logsize, @logused

End

CLOSE logcursor

DEALLOCATE logcursor

-- Cal the rest of the fields

UPDATE master.dbo.DBFileSize23 SET dbfileused = dbfilesize - dbfilefree, logfree = logsize - ((logpercentused /100) * logsize)

UPDATE master.dbo.DBFileSize23 SET logused = round(logsize - logfree,2), currentdate = @dtpk

--select * from DBFileSize23

-- Insert code here to dump into table

INSERT INTO master.dbo.DB_SizeWatch ([dbname], [logsize], [logused], [logpercentused], [logfree], [dbfilesize], [dbfileused],

[dbfilefree], [dbsize], [CurrentDate])

SELECT [dbname], [logsize], [logused], [logpercentused], [logfree], [dbfilesize], [dbfileused],

[dbfilefree], [dbsize], [CurrentDate]

FROM master.dbo.DBFileSize23

DROP TABLE master.dbo.DBFileSize23

--select * from DB_SizeWatch

GO