Follow up on database and log size… | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Follow up on database and log size…

What I would like to have is some sort of scheduled job runnig that takes the sizes of all the databases and transactionlog-files and put in a database<br />Something like this:<br /><pre>Db1logdb1db2logdb2<br />07/01/2004251056696<br />07/02/2004251057096<br />07/03/2004261057596<br />07/04/2004261057996<br /></pre><br /><br />Then I could have a report showing with a graph how the databases (and transactionlogs) are growing…<br /><br />Anyone having the needed scripts or can point me in the general direction to where to find someting like it… [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><br />//Teddy<br /><br />Ps. It sould be said that this is my first post on this forum. I have been looking around not finding the answer anywhere, but if I done something wrong (or stepped on someones toes) I ask for forgiveness and will never speak again.. [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]. D.s
Here is something I use for this very purpose. It creates a table called Db_monitor and populates with statistics of ‘mydb’ along with the time stamp.
ALTER proc spm_dbmonitor as
set nocount on
set quoted_identifier off
DBCC UPDATEUSAGE (‘mydb’) WITH NO_INFOMSGS declare @idint– The object id of @objname.
declare @typecharacter(2) — The object type.
[email protected] Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0) create table #spt_space
(reserveddec(15) null,
datadec(15) null,
indexpdec(15) null,
unuseddec(15) null,
DBName varchar( 100) null,
Log_Size decimal (10,3) null,
Prcnt_Used decimal (10,2) null,
status int null
) select @dbsize = sum(convert(dec(15),size))
from mydb.dbo.sysfiles select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = ‘E’
select @pagesperMB = 1048576 / @bytesperpage insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from mydb.dbo.sysindexes
where indid in (0, 1, 255) select @pages = sum(convert(dec(15),dpages))
from mydb.dbo.sysindexes
where indid < 2
select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
from mydb.dbo.sysindexes
where indid = 255
update #spt_space
set data = @pages update #spt_space
set indexp = (select sum(convert(dec(15),used))
from mydb.dbo.sysindexes
where indid in (0, 1, 255))- data update #spt_space
set unused = reserved
– (select sum(convert(dec(15),used))
from mydb.dbo.sysindexes
where indid in (0, 1, 255)) If not exists (Select * from Admin..sysobjects where name = ‘DB_monitor’)
Begin
CREATE TABLE [Admin].[monitor].[DB_monitor] (
[DBName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DBSize] [decimal](15, 3) NOT NULL ,
[DBUsed] AS ([DBSize] – [unallocated_space]) ,
[unallocated_space] [decimal](15, 3) NOT NULL ,
[reserved] [decimal](15, 3) NULL ,
[data_size] [decimal](15, 3) NULL ,
[index_size] [decimal](15, 3) NULL ,
[unused] [decimal](15, 3) NULL ,
[Log_Size] [decimal](15, 3) NULL ,
[Prcnt_LogUsed] [decimal](15, 3) NULL ,
[DT_monitor] [datetime] NOT NULL
) ON [PRIMARY] ALTER TABLE [Admin].[monitor].[DB_monitor] WITH NOCHECK ADD
CONSTRAINT [PK_DBMonitor1] PRIMARY KEY CLUSTERED
(
[DBName],
[DT_monitor]
) ON [PRIMARY] ALTER TABLE [Admin].[monitor].[DB_monitor] WITH NOCHECK ADD
CONSTRAINT [DF__DB_mo__DT_mo__6EF57B66] DEFAULT (getdate()) FOR [DT_monitor] End declare @x varchar(50), @y varchar(255) create table #log_space
(DBName varchar( 100) null,
Log_Size decimal (10,3) null,
Prcnt_Used decimal (10,2) null,
status int null
) set @y = "exec (‘DBCC sqlperf( logspace)’)" set @y = ‘INSERT INTO #log_space (DBName, Log_Size, Prcnt_Used, status)’ + @y
execute(@y) update #spt_space
set DBName = l.DBName, Log_Size = l.log_size, Prcnt_Used = l.Prcnt_Used, status = l.status
from #spt_space s, #log_space l
where l.dbname = ‘mydb’ insert into Admin.monitor.DB_monitor (DBName, DBSize, unallocated_space, reserved, data_size, index_size, unused, Log_Size, Prcnt_LogUsed)
select database_name = s.DBName,
database_size =ltrim(str(@dbsize / @pagesperMB,15,2)),
‘unallocated space’ = ltrim(str((@dbsize – (select sum(convert(dec(15),reserved))
from mydb.dbo.sysindexes
where indid in (0, 1, 255))) / @pagesperMB,15,2)),
reserved = ltrim(str(reserved * d.low / 1024.,15,0)),
data_size = ltrim(str(data * d.low / 1024.,15,0)),
index_size = ltrim(str(indexp * d.low / 1024.,15,0)),
unused = ltrim(str(unused * d.low / 1024.,15,0)),
s.Log_Size, s.Prcnt_Used
from #spt_space s, master.dbo.spt_values d
where d.number = 1
and d.type = ‘E’ and s.DBName = ‘mydb’
Thanks for the quick reply!! (less than 45 minutes, i’m impressed!!!) Haven’t been able to test this (on vacation now sitting on the beach posting this from my mobilephone) but will look at it as soon as I get back!

I should first start to say that I’m quite new to all this with MS SQL…
What I did was to take the text in the post by ykchakri and pasted it in SQL Query Analyser (attached to the msdb-database) If I try to run the query I get:
quote:
Server: Msg 207, Level 16, State 3, Procedure spm_dbmonitor, Line 96
Invalid column name ‘exec (DBCC sqlperf( logspace)’)’.

I can’t really get how I’m supposed to use this… Am I supposed to create a stored procedure with all this or?? (really in the dark thinking I might have taken on to much here…)

I’m using double quotes as string identifiers at some places. So, you need to use SET QUOTED_IDENTIFIER OFF at the beginning. Also, you need to make some changes before you create this stored procedure. Start with the first line, ‘Create proc’ instead of ‘Alter proc’, Change the database name ‘Admin’ to your database name, etc.
I been fidling around with this for a awhile now.. Thisis actually my first stored procedure ever so be nice… [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><pre>CREATE PROCEDURE dbo.sp_DatabaseSizeFollowup AS<br /><br />– *************************************************<br />– *** DECLARE ALL VARIABLES NEEDED<br />DECLARE @databasename varchar(200) — the name of the database which is beeing checked<br />DECLARE @databaseDATA varchar(200) — The name of the column for size of datafiles corresponding tho the database which is beeing checked<br />DECLARE @databaseLOG varchar(200)– The name of the column for size of logfiles corresponding tho the database which is beeing checked<br />DECLARE @sizedata int — Size in KBytes of the datafiles corresponding tho the database which is beeing checked<br />DECLARE @sizelog int — Size in KBytes of the logfiles corresponding tho the database which is beeing checked<br />DECLARE @datum datetime — Todays date <br />DECLARE @datumstr as varchar(20) — Todays date in the format YYYY-MM-DD. This is what is put in the database<br />DECLARE @MM as varchar(5) — Used to create the @datumstr<br />DECLARE @DD as varchar(5) — Used to create the @datumstr<br /><br />– Set the @Datumstr with the date of today.<br />set @datum = (select getdate())<br />SET @datum = (select (getdate()))<br />SET @MM = CONVERT(varchar, month(@datum))<br /> if LEN(@MM) = 1 SET @MM = ‘0’ + @MM<br />SET @DD = CONVERT(varchar, day(@datum))<br /> if LEN(@DD) = 1 SET @DD = ‘0’ + @DD<br />SET @datumstr = CONVERT(varchar, YEAR(@datum)) + ‘-‘ + @MM + ‘-‘ + @DD<br /><br />– *************************************************<br />– *** CREATE THE DATABASE IF NEEDED<br />– Starts to check if the required database exist. <br />– If it doesn’t exist the base for it is created.<br />if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DataBaseSizeFollowup]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)<br /> CREATE TABLE [dbo].[DataBaseSizeFollowup] (<br />[ID] [int] IDENTITY (1, 1) NOT NULL ,<br />[Datum] [varchar] (20) COLLATE Finnish_Swedish_CI_AS NULL ,<br />[master_Data] [int] NULL ,<br />[master_Log] [int] NULL ,<br />[tempdb_Data] [int] NULL, <br />[tempdb_Log] [int] NULL,<br />[model_Data] [int] NULL, <br />[model_Log] [int] NULL, <br />[msdb_Data] [int] NULL, <br />[msdb_Log] [int] NULL <br />) ON [PRIMARY]<br /><br /><br />– *************************************************<br />– *** CREATE ALL THE TABLES NEEDED IN THE DATABASE<br />–<br />– First get all the names of all the databases on the server<br />– and create a cursor for them all.<br />DECLARE dbnames_cursor CURSOR<br />FOR<br /> select dbo.sysdatabases.name<br /> from dbo.sysaltfiles, dbo.sysdatabases<br /> where dbo.sysaltfiles.dbid *= dbo.sysdatabases.dbid <br /> and ((right(rtrim(dbo.sysaltfiles.filename),4)) = ‘mdf’ <br /> or (right(rtrim(dbo.sysaltfiles.filename),4) = ‘.mdf’))<br />OPEN dbnames_cursor<br />FETCH NEXT FROM dbnames_cursor INTO @databasename<br />– Now step trough them all. One by one…<br />– if fetch_status = -1 then FETCH statement failed or the row was beyond the result set.<br />– if fetch_status = -2 then Row fetched is missing.<br />WHILE (@@FETCH_STATUS &lt;&gt; -1) <br />BEGIN<br /> IF (@@FETCH_STATUS &lt;&gt; -2)<br /> BEGIN <br /> — Here we check if the database already have the columns created in <br /> — the DataBaseSizeFollowup-database… If they exist then try the <br /> — the next databasename…<br /> IF not exists (select * from master.dbo.syscolumns where name= @databasename + ‘_DATA’)<br /> Begin <br /> EXEC (‘ALTER TABLE master.dbo.DataBaseSizeFollowup ADD [‘ + @databasename + ‘_DATA] [INT] NULL’)<br /> EXEC (‘ALTER TABLE master.dbo.DataBaseSizeFollowup ADD [‘ + @databasename + ‘_LOG] [INT] NULL’)<br /> END<br /> END<br /> FETCH NEXT FROM dbnames_cursor INTO @databasename<br />END<br />CLOSE dbnames_cursor<br />DEALLOCATE dbnames_cursor<br /><br />– *************************************************<br />– *** CREATE A NEW ROW AND POPULATING IT WITH DATA<br />–<br />– First lets put the date in and create the row<br />INSERT master.dbo.DataBaseSizeFollowup(Datum) VALUES (@datumstr)<br />– Now it’s time to start populating the database.<br />– We step though the databases one more time<br />DECLARE dbnames_cursor CURSOR<br />FOR<br /> select dbo.sysdatabases.name<br /> from dbo.sysaltfiles, dbo.sysdatabases<br /> where dbo.sysaltfiles.dbid *= dbo.sysdatabases.dbid <br /> and ((right(rtrim(dbo.sysaltfiles.filename),4)) = ‘mdf’ <br /> or (right(rtrim(dbo.sysaltfiles.filename),4) = ‘.mdf’))<br />OPEN dbnames_cursor<br />FETCH NEXT FROM dbnames_cursor INTO @databasename<br />– Now step trough them all. One by one…<br />WHILE (@@FETCH_STATUS &lt;&gt; -1)<br />BEGIN<br /> IF (@@FETCH_STATUS &lt;&gt; -2)<br /> BEGIN <br /> — Get the size of the files for database (in KByte)<br /> SET @sizedata = (select sum(dbo.sysaltfiles.size*8192/1024) <br /> from dbo.sysaltfiles <br /> where (((right(rtrim(dbo.sysaltfiles.filename),4) = ‘mdf’) <br /> or (right(rtrim(dbo.sysaltfiles.filename),4) = ‘.mdf’)<br /> or (right(rtrim(dbo.sysaltfiles.filename),4) = ‘ndf’) <br /> or (right(rtrim(dbo.sysaltfiles.filename),4) = ‘.ndf’))<br /> and dbo.sysaltfiles.dbid = (select dbo.sysdatabases.dbid <br /> from dbo.sysdatabases <br /> where dbo.sysdatabases.name = @databasename)))<br /> — Get the size of the files for transactionlog (in KByte)<br /> SET @sizelog = (select sum(dbo.sysaltfiles.size*8192/1024) <br /> from dbo.sysaltfiles <br /> where (((right(rtrim(dbo.sysaltfiles.filename),4) = ‘ldf’) <br /> or (right(rtrim(dbo.sysaltfiles.filename),4) = ‘.ldf’))<br /> and dbo.sysaltfiles.dbid = (select dbo.sysdatabases.dbid <br /> from dbo.sysdatabases <br /> where dbo.sysdatabases.name = @databasename)))<br /> — Get the names of the tables that is supposed to get updated<br /> SET @databaseDATA = ‘[‘ + @databasename + ‘_DATA]'<br /> SET @databaseLOG = ‘[‘ + @databasename + ‘_LOG]'<br /> — Now lets update the database with the new info<br /> EXEC (‘UPDATE master.dbo.DataBaseSizeFollowup SET ‘ + @databaseDATA + ‘ = ‘ + @sizedata + ‘, ‘ + @databaseLOG + ‘ = ‘ + @sizelog + ‘ WHERE Datum = ”’ + @datumstr + ””)<br /> END<br /> FETCH NEXT FROM dbnames_cursor INTO @databasename<br />END<br />CLOSE dbnames_cursor<br />DEALLOCATE dbnames_cursor<br />GO<br /></pre><br /><br /><br />This creates a strored procedure. I have scheduled this to run once everyday and then have an excel-sheet with a data-connection and a diagram showing me all databases on the server… This might be good… *happy*<br /><br />The reason for the datethngy in the stat is to get the date in a swedish format.. I only wanted the date, not the time and in a special format so thiats how I solved it. Iäm sure there is a beter way of doing this.. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />The variablenames is in some swedish and some english… Didn’t want to use @date as a variable name… It’s to easy to get confused with the DATE() function.<br /><br />Oh well, i shouldn’t be rambling about to much… Thanks for all the help!!!<br />
The script in the second message is exactly what I need however I am new to SQL Server and am having problems getting the procedure to compile. I am getting the following error.
Server: Msg 207, Level 16, State 3, Procedure spm_dbmonitor, Line 96
Invalid column name ‘exec (‘DBCC sqlperf(logspace)’)’. Line 96 is
set @y = "exec (‘DBCC sqlperf(logspace)’)"
Any ideas about what is happening? TIA
Larry

]]>