Monitoring databases!!

  • Hi,

    Am working on scripts to help me in daily monitoring tasks..

    I want to get the database names, its file names, total size and available space..till now am able to get all the bits except for the last one

    —————————————————————————

    SELECT a.name as [DBname], b.name AS ‘File Name’ , case when b.type=0 then (select ‘Data’) else ‘Log’ end as [File_type] ,b.physical_name AS ‘Physical Name’, b.size/128 AS ‘Total Size in MB’,

    b.size/128.0 – CAST(FILEPROPERTY(b.name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’

    FROM sys.databases a left outer join sys.master_files b

    on a.database_id = b.database_id

    —————————————————————————

    This gives me list of everything what i want on that server but regarding “available space” it can only retrieve for the current database files..I want that for all the database files on that server..

    i tried adding where clause to it " where name = 'DB name' but this won't return available space for that database files unless this query is run in that DB..

    Could anyone plz help me in this..

    Thanks

    Shree

  • There are a lot of good scripts around, but here is a script that I use frequently (comment out/ remove the drive letter if that is not of interest to you):

    If Object_ID('tempdb.dbo.#UsedSpace') Is Not Null

    Drop Table #UsedSpace

    Create Table #UsedSpace (DBName VarChar(150), FileID SmallInt, FileGroup SmallInt, TotalExtents Int, UsedExtents Int,

    Name NVarChar(256), FileName NVarChar(512))

    exec sp_msforeachdb 'use ?

    Declare @sql VarChar(8000)

    Set @sql = ''DBCC SHOWFILESTATS WITH TABLERESULTS''

    Insert Into #UsedSpace (FileID, FileGroup, TotalExtents, UsedExtents, Name, FileName)

    Exec(@SQL);

    Update #UsedSpace Set DBName = ''?'' Where DBName Is Null;

    '

    Select DBName, Name, (TotalExtents * 64) / 1024 As TotalSpace,

    (UsedExtents * 64) / 1024 As UsedSpace,

    ((TotalExtents * 64) / 1024) - ((UsedExtents * 64) / 1024) As FreeSpace

    From #UsedSpace

    --Where Left(FileName, 1) = 'E'

    Order By ((TotalExtents * 64) / 1024) - ((UsedExtents * 64) / 1024) Desc

    If Object_ID('tempdb.dbo.#UsedSpace') Is Not Null

    Drop Table #UsedSpace

  • Hey Thanks David!!

    Just tried the script you gave..it works great!..however it returns only the data files for all the databases..

    Can we do something to get the log files as well..??

    Thanks

    Shree

  • I forgot that we can use " DBCC SQLPERF(logspace)" to get the details for the log files!!

    Thanks once again David!

    - Shree 🙂

  • Cool, I was just replying when I saw your last post. Glad it will work for you.

  • To monitor databases size, am using " sp_helpdb" - this gives size allocated to all the databases on a server but not the available space!

    i tried using " EXEC sp_msforeachdb @command1="use ? exec sp_spaceused" " this gives me all the details but it gets on to 2 lines for each database 🙁

    Could anyone plzz help in how to find available space for all databases on a server..

    Thanks

    Shree

  • Hi There,

    I had just completed a similar task.Yes, the sp_spaceused returns 2 result set.I ended up reviewing the sp_spaceused and using part of the code to return the results that I want and manage to get it into a single result set.

    Now I have an SSIS package created with the modified sp_spaceused in the script task that would execute daily as a job and take care of reading the information from each database and populating my staging table.

    I suggest that you examine the sp_spaceused and break it part by part to what you want.You can even add on to the script.

    Link to my post http://www.sqlservercentral.com/Forums/Topic840084-147-1.aspx

  • Here is a stored proc I have used to collect space data on multiple db's. it might provide some ideas for you?

    I collect total allocated and total used(reserved). Should be able to get available from that...;-)

    THe table I am inserting into:

    CREATE TABLE [dbo].[dbSpaceDist](

    [DB] [char](40) NULL,

    [DB_Tot] [numeric](9, 2) NULL,

    [DB_Used] [numeric](9, 2) NULL,

    [Log_Tot] [numeric](9, 2) NULL,

    [Log_Used] [numeric](9, 2) NULL,

    [EntryDt] [datetime] NULL

    ) ON [PRIMARY]

    The stored proc:

    CREATE procedure [dbo].[dbspCalcdbaSpaceDist]

    @days int = -732

    as

    /*

    *************************************************************

    Name: dbspCalcdbaSpaceDist

    Description:

    Gather the data and log space for all databases on the system

    and insert the information into DBSpaceDist. The following databases

    are not added to DBSpaceDist:

    pubs

    Northwind

    model

    tempdb

    Usage:exec dbspCalcdbaSpaceDist -90

    Author:

    Input Params:

    -------------

    @days int. Number of days to keep in DBASpaceDist.

    Locals:

    --------

    @errHolds error value

    *************************************************************

    */

    set nocount on

    declare @err int

    select @err = 0

    /*

    Create the temp tables to hold the results of DBCC

    commands until the information is entered into

    DBASpaceDist.

    */

    DECLARE @tbl_logspace table(

    DBName char( 40),

    LogSize float,

    PrcntUsed float,

    status int

    )

    DECLARE @tbl_dataspace table(

    FileID int,

    FileGrp int,

    TotExtint,

    UsdExt int,

    LFileNmvarchar( 100),

    PFileNmvarchar( 100)

    )

    /*

    Get the log space

    */

    INSERT INTO @tbl_logspace

    EXEC ('dbcc sqlperf( logspace)')

    /*

    Get the data space

    Use a cursor to loop through the results from DBCC

    since you have to run this command from each database

    with a USE command.

    for each database

    insert the sum of the size columns and the log info from

    the cursor into a row in dbaSpaceDist table

    */

    declare @db char( 40), @cmd char( 500)

    , @logsize numeric(10,4), @prcntused numeric(10,4)

    declare dbname cursor for

    select DBName, LogSize, PrcntUsed from @tbl_logspace

    FOR READ ONLY

    open dbname

    fetch next from dbname into @db, @logsize, @prcntused

    while @@fetch_status = 0

    begin

    select @cmd = 'use ' + rtrim( @db) + ' dbcc showfilestats'

    insert @tbl_dataspace

    exec( @cmd)

    if @db in

    ('db1',

    'db2',

    'db3',

    'db4',

    'db5',

    'db6',

    'tempdb'

    )

    insert [dbo].[dbSpaceDist]

    ([DB]

    ,[DB_Tot]

    ,[DB_Used]

    ,[Log_Tot]

    ,[Log_Used]

    ,[EntryDt]

    )

    select

    @db,

    ((cast( sum(TotExt) as numeric( 9, 2))* 32) / 512),

    ((cast( sum(UsdExt) as numeric( 9, 2))* 32) / 512),

    @logsize,

    (@logsize * (@prcntused/100)),

    getdate()

    from @tbl_dataspace d

    fetch next from dbname into @db, @logsize, @prcntused

    delete @tbl_dataspace

    end

    close dbname

    deallocate dbname

    /*

    Remove old information from the DBASpaceDist table.

    */

    delete dbSpaceDist

    where entrydt < dateadd( day, @days, getdate())

    return @err

    jg

  • Hello,

    This is what you are looking for :

    USE MASTER

    GO

    CREATE TABLE #TMPFIXEDDRIVES (

    DRIVE CHAR(1),

    MBFREE INT)

    INSERT INTO #TMPFIXEDDRIVES

    EXEC xp_FIXEDDRIVES

    CREATE TABLE #TMPSPACEUSED (

    DBNAME VARCHAR(50),

    FILENME VARCHAR(50),

    SPACEUSED FLOAT)

    INSERT INTO #TMPSPACEUSED

    EXEC( 'sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')

    SELECT C.DRIVE,

    CASE

    WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'

    ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'

    END AS DISKSPACEFREE,

    A.NAME AS DATABASENAME,

    B.NAME AS FILENAME,

    CASE B.TYPE

    WHEN 0 THEN 'DATA'

    ELSE TYPE_DESC

    END AS FILETYPE,

    CASE

    WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'

    ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'

    END AS FILESIZE,

    CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE,

    B.PHYSICAL_NAME

    FROM SYS.DATABASES A

    JOIN SYS.MASTER_FILES B

    ON A.DATABASE_ID = B.DATABASE_ID

    JOIN #TMPFIXEDDRIVES C

    ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE

    JOIN #TMPSPACEUSED D

    ON A.NAME = D.DBNAME

    AND B.NAME = D.FILENME

    ORDER BY DISKSPACEFREE,

    SPACEFREE DESC

    DROP TABLE #TMPFIXEDDRIVES

    DROP TABLE #TMPSPACEUSED

    I use this for space monitoring.

    Hope this helps !

    \\K

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • Thanks a lott for the replies..:)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply