SQL Server Performance

Followup Database size

Discussion in 'Contribute Your SQL Server Scripts' started by satya, Aug 2, 2004.

  1. satya Moderator

    Author: Teddy004
    ----------------
    I been fidling around with this for a awhile now.. This is actually my first stored procedure ever so be nice...


    CREATE PROCEDURE dbo.sp_DatabaseSizeFollowup AS

    -- *************************************************
    -- *** DECLARE ALL VARIABLES NEEDED
    DECLARE @databasename varchar(200) -- the name of the database which is beeing checked
    DECLARE @databaseDATA varchar(200) -- The name of the column for size of datafiles corresponding tho the database which is beeing checked
    DECLARE @databaseLOG varchar(200)-- The name of the column for size of logfiles corresponding tho the database which is beeing checked
    DECLARE @sizedata int -- Size in KBytes of the datafiles corresponding tho the database which is beeing checked
    DECLARE @sizelog int -- Size in KBytes of the logfiles corresponding tho the database which is beeing checked
    DECLARE @datum datetime -- Todays date
    DECLARE @datumstr as varchar(20) -- Todays date in the format YYYY-MM-DD. This is what is put in the database
    DECLARE @MM as varchar(5) -- Used to create the @datumstr
    DECLARE @DD as varchar(5) -- Used to create the @datumstr

    -- Set the @Datumstr with the date of today.
    set @datum = (select getdate())
    SET @datum = (select (getdate()))
    SET @MM = CONVERT(varchar, month(@datum))
    if LEN(@MM) = 1 SET @MM = '0' + @MM
    SET @DD = CONVERT(varchar, day(@datum))
    if LEN(@DD) = 1 SET @DD = '0' + @DD
    SET @datumstr = CONVERT(varchar, YEAR(@datum)) + '-' + @MM + '-' + @DD

    -- *************************************************
    -- *** CREATE THE DATABASE IF NEEDED
    -- Starts to check if the required database exist.
    -- If it doesn't exist the base for it is created.
    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DataBaseSizeFollowup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    CREATE TABLE [dbo].[DataBaseSizeFollowup] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [Datum] [varchar] (20) COLLATE Finnish_Swedish_CI_AS NULL ,
    [master_Data] [int] NULL ,
    [master_Log] [int] NULL ,
    [tempdb_Data] [int] NULL,
    [tempdb_Log] [int] NULL,
    [model_Data] [int] NULL,
    [model_Log] [int] NULL,
    [msdb_Data] [int] NULL,
    [msdb_Log] [int] NULL
    ) ON [PRIMARY]


    -- *************************************************
    -- *** CREATE ALL THE TABLES NEEDED IN THE DATABASE
    --
    -- First get all the names of all the databases on the server
    -- and create a cursor for them all.
    DECLARE dbnames_cursor CURSOR
    FOR
    select dbo.sysdatabases.name
    from dbo.sysaltfiles, dbo.sysdatabases
    where dbo.sysaltfiles.dbid *= dbo.sysdatabases.dbid
    and ((right(rtrim(dbo.sysaltfiles.filename),4)) = 'mdf'
    or (right(rtrim(dbo.sysaltfiles.filename),4) = '.mdf'))
    OPEN dbnames_cursor
    FETCH NEXT FROM dbnames_cursor INTO @databasename
    -- Now step trough them all. One by one...
    -- if fetch_status = -1 then FETCH statement failed or the row was beyond the result set.
    -- if fetch_status = -2 then Row fetched is missing.
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
    -- Here we check if the database already have the columns created in
    -- the DataBaseSizeFollowup-database... If they exist then try the
    -- the next databasename...
    IF not exists (select * from master.dbo.syscolumns where name= @databasename + '_DATA')
    Begin
    EXEC ('ALTER TABLE master.dbo.DataBaseSizeFollowup ADD [' + @databasename + '_DATA] [INT] NULL')
    EXEC ('ALTER TABLE master.dbo.DataBaseSizeFollowup ADD [' + @databasename + '_LOG] [INT] NULL')
    END
    END
    FETCH NEXT FROM dbnames_cursor INTO @databasename
    END
    CLOSE dbnames_cursor
    DEALLOCATE dbnames_cursor

    -- *************************************************
    -- *** CREATE A NEW ROW AND POPULATING IT WITH DATA
    --
    -- First lets put the date in and create the row
    INSERT master.dbo.DataBaseSizeFollowup(Datum) VALUES (@datumstr)
    -- Now it's time to start populating the database.
    -- We step though the databases one more time
    DECLARE dbnames_cursor CURSOR
    FOR
    select dbo.sysdatabases.name
    from dbo.sysaltfiles, dbo.sysdatabases
    where dbo.sysaltfiles.dbid *= dbo.sysdatabases.dbid
    and ((right(rtrim(dbo.sysaltfiles.filename),4)) = 'mdf'
    or (right(rtrim(dbo.sysaltfiles.filename),4) = '.mdf'))
    OPEN dbnames_cursor
    FETCH NEXT FROM dbnames_cursor INTO @databasename
    -- Now step trough them all. One by one...
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
    -- Get the size of the files for database (in KByte)
    SET @sizedata = (select sum(dbo.sysaltfiles.size*8192/1024)
    from dbo.sysaltfiles
    where (((right(rtrim(dbo.sysaltfiles.filename),4) = 'mdf')
    or (right(rtrim(dbo.sysaltfiles.filename),4) = '.mdf')
    or (right(rtrim(dbo.sysaltfiles.filename),4) = 'ndf')
    or (right(rtrim(dbo.sysaltfiles.filename),4) = '.ndf'))
    and dbo.sysaltfiles.dbid = (select dbo.sysdatabases.dbid
    from dbo.sysdatabases
    where dbo.sysdatabases.name = @databasename)))
    -- Get the size of the files for transactionlog (in KByte)
    SET @sizelog = (select sum(dbo.sysaltfiles.size*8192/1024)
    from dbo.sysaltfiles
    where (((right(rtrim(dbo.sysaltfiles.filename),4) = 'ldf')
    or (right(rtrim(dbo.sysaltfiles.filename),4) = '.ldf'))
    and dbo.sysaltfiles.dbid = (select dbo.sysdatabases.dbid
    from dbo.sysdatabases
    where dbo.sysdatabases.name = @databasename)))
    -- Get the names of the tables that is supposed to get updated
    SET @databaseDATA = '[' + @databasename + '_DATA]'
    SET @databaseLOG = '[' + @databasename + '_LOG]'
    -- Now lets update the database with the new info
    EXEC ('UPDATE master.dbo.DataBaseSizeFollowup SET ' + @databaseDATA + ' = ' + @sizedata + ', ' + @databaseLOG + ' = ' + @sizelog + ' WHERE Datum = ''' + @datumstr + '''')
    END
    FETCH NEXT FROM dbnames_cursor INTO @databasename
    END
    CLOSE dbnames_cursor
    DEALLOCATE dbnames_cursor
    GO




    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*

    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..

    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.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  2. Teddy004 New Member

    Have had some trouble with this stored procedure and large (more than 1 GB) databases.. Made two changes. First gone from "int" to "bigint" and then shortened a calculation where I had "variable*8192/1024" to "variable*8" and the error "Arithmetic overflow error converting expression to data type int" is now gone.<br /><br />I suppose that if anyone uses this script on a database larger than 1TB then the error might come back...<br /><br />Anyway.. here is the updated script:<br /><hr noshade size="1"><br /><pre><br />CREATE PROCEDURE dbo.sp_DatabaseSizeFollowup AS<br />-- ================================<br />-- By: Bjorn Andersson, Kentor Teknik AB<br />-- bjorn.andersson@kentor.se<br />-- version : 1.1<br />-- Date: 2004-09-23<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 bigint -- Size in KBytes of the datafiles corresponding tho the database which is beeing checked<br />DECLARE @sizelog bigint -- 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*<img src='/community/emoticons/emotion-11.gif' alt='8)' /> <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*<img src='/community/emoticons/emotion-11.gif' alt='8)' /> <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 />

Share This Page