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.
    satya, Aug 2, 2004
    #2

  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 />
    Teddy004, Sep 23, 2004
    #2

Share This Page