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 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 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),dpages)) 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 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