Disk Sace Usage

  • Hello

    Am trying to create a script that will determine the amount of space is been used on both my file server and sql server for all jobs residing on an instance. Once this result has been determined, I will like to populate them into another table, which will be used to develop a report model, using SSRS.

    Am using the xp_fixeddrives query, but it only returns the drive letter and the amount of free space.

    Can anyone provide me with any ideas?

  • use "sp_spaceused" command, for more information, see SQL BOL.

  • I'm sure that if you search in the scripts section of this site you will find at least 4 or 5 scripts dealing with spaceused etc..

    Here's one which deals more with the diskspace side of it:

    http://www.sqldbatips.com/showcode.asp?ID=4

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • even if i use the sp_spaceused, how will i populate hem into a table that i created?

  • INSERT INTO myTable

    Exec sp_spaceused

    [font="Verdana"]Markus Bohse[/font]

  • MarkusB (11/29/2007)


    INSERT INTO myTable

    Exec sp_spaceused

    Since sp_SpaceUsed returns 2 result sets, I'm thinking that's just not going to do everything you'd expect. Right idea, though. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Take a look at the output from these scripts.

    Get Server Database File Information

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

    Script to analyze table space usage

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

  • I used the following script to create the table for the results to be inputed into the space_used table, but doesnt to work as i get the following error msg; Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 128

    Insert Error: Column name or number of supplied values does not match table definition.

    create table Space_Used

    (

    database_name varchar (50),

    database_size varchar (10),

    unallocated_space varchar (10),

    primary key (database_name)

    );

    INSERT INTO space_used

    Exec sp_spaceused

  • I do it like this and then call this into a temp table

    for each server

    --exec sp_diskspace

    CREATE PROCEDURE sp_diskspace

    AS

    /*

    Displays the free space,free space percentage

    plus total drive size for a server

    */

    SET NOCOUNT ON

    DECLARE @hr int

    DECLARE @fso int

    DECLARE @drive char(1)

    DECLARE @odrive int

    DECLARE @TotalSize varchar(20)

    DECLARE @MB bigint ; SET @MB = 1048576

    CREATE TABLE #drives (drive char(1) PRIMARY KEY,

    FreeSpace int NULL,

    TotalSize int NULL)

    INSERT #drives(drive,FreeSpace)

    EXEC master.dbo.xp_fixeddrives

    EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

    DECLARE dcur CURSOR LOCAL FAST_FORWARD

    FOR SELECT drive from #drives

    ORDER by drive

    OPEN dcur

    FETCH NEXT FROM dcur INTO @drive

    WHILE @@FETCH_STATUS=0

    BEGIN

    EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

    EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

    UPDATE #drives

    SET TotalSize=@TotalSize/@MB

    WHERE drive=@drive

    FETCH NEXT FROM dcur INTO @drive

    END

    CLOSE dcur

    DEALLOCATE dcur

    EXEC @hr=sp_OADestroy @fso

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

    SELECT @@servername,drive,

    FreeSpace as 'Free(MB)',

    TotalSize as 'Total(MB)',

    CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'

    FROM #drives

    ORDER BY drive

    DROP TABLE #drives

    RETURN

    GO

  • b_boy (11/29/2007)


    I used the following script to create the table for the results to be inputed into the space_used table, but doesnt to work as i get the following error msg; Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 128

    Insert Error: Column name or number of supplied values does not match table definition.

    create table Space_Used

    (

    database_name varchar (50),

    database_size varchar (10),

    unallocated_space varchar (10),

    primary key (database_name)

    );

    INSERT INTO space_used

    Exec sp_spaceused

    Had a little "free time" on my hands... perhaps this will help...

    --====================================================================================================================

    -- Presets

    --====================================================================================================================

    --===== Setup the environment to prevent blocking, etc

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --Allows "dirty" reads

    SET NOCOUNT ON --Suppresses auto-display of rowcounts for appearance/speed

    --===== Declare local variables

    DECLARE @DBCount INT --Number of databases names to process

    DECLARE @Counter INT --General purpose loop counter

    DECLARE @SQLExec VARCHAR(8000) --Holds executable dynamic SQL

    DECLARE @SQLTmpl VARCHAR(8000) --Holds dynamic SQL template

    --===== Preset Values

    SET @SQLTmpl ='

    --===== Identify the database to use

    USE

    --===== Make sure usage info is up to date for each DB

    DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS

    --===== Get the database information similar to sp_SpaceUsed but in a set based fashion.

    -- (Returns only 1 row for current database for each call)

    INSERT INTO #Results

    (DBName,DBID,DBTotalMB,LogFileMB,DataFileMB,UnallocatedMB,ReservedMB,TotalUsedMB,UnusedMB,DataMB,IndexMB)

    SELECT DBName = DB_NAME(),

    DBID = DB_ID(),

    DBTotalMB = fs.DataFileMB + fs.LogFileMB,

    LogFileMB = fs.LogFileMB,

    DataFileMB = fs.DataFileMB,

    UnallocatedMB = fs.DataFileMB - r.ReservedMB,

    ReservedMB = r.ReservedMB,

    TotalUsedMB = r.TotalUsedMB,

    UnusedMB = r.ReservedMB - r.TotalUsedMB,

    DataMB = r.DataMB,

    IndexMB = r.TotalUsedMB - r.DataMB

    FROM (--==== Derived table "fs" finds total file sizes (Status 64 = Log Device, 128 = Pages per MB)

    SELECT DataFileMB = SUM(CASE WHEN Status & 64 = 0 THEN Size ELSE 0 END)/128.0,

    LogFileMB = SUM(CASE WHEN Status & 64 <> 0 THEN Size ELSE 0 END)/128.0

    FROM dbo.SysFiles

    )fs

    ,

    (--==== Derived table "r" finds types of space

    SELECT ReservedMB = SUM(Reserved)/128.0,

    TotalUsedMB = SUM(Used)/128.0,

    DataMB = SUM(CASE WHEN IndID < 2 THEN DPages

    WHEN IndID = 255 THEN Used

    ELSE 0

    END)/128.0

    FROM dbo.SysIndexes

    WHERE IndID IN (0,1,255)

    )r'

    --====================================================================================================================

    -- Create Temp Tables

    --====================================================================================================================

    --===== Temp table to hold database names to work with and remember how many there are

    IF OBJECT_ID('TempDB..#DatabaseNames','U') IS NOT NULL

    DROP TABLE #DatabaseNames

    SELECT RowNum = IDENTITY(INT,1,1),

    Name

    INTO #DatabaseNames

    FROM Master.dbo.SysDatabases

    ORDER BY Name

    SET @DBCount = @@ROWCOUNT

    --===== Temp table to hold results to be displayed

    IF OBJECT_ID('TempDB..#Results','U') IS NOT NULL

    DROP TABLE #Results

    CREATE TABLE #Results

    (

    DBName SYSNAME PRIMARY KEY CLUSTERED,

    DBID INT,

    DBTotalMB DECIMAL(19,1),

    LogFileMB DECIMAL(19,1),

    DataFileMB DECIMAL(19,1),

    UnallocatedMB DECIMAL(19,1),

    ReservedMB DECIMAL(19,1),

    TotalUsedMB DECIMAL(19,1),

    UnusedMB DECIMAL(19,1),

    DataMB DECIMAL(19,1),

    IndexMB DECIMAL(19,1)

    )

    --====================================================================================================================

    -- Loop through the databases and save the size information for each

    --====================================================================================================================

    --=====

    SET @Counter = 1

    WHILE @Counter <= @DBCount

    BEGIN

    --===== Get the next database name to work on an insert the dynamic SQL using the template as a master

    SELECT @SQLExec = REPLACE(@SQLTmpl,' ',Name)

    FROM #DatabaseNames

    WHERE RowNum = @Counter

    --===== Execute the dynamic SQL to get the size information for the current database name

    EXEC (@SQLExec)

    --===== Bump the loop counter

    SET @Counter = @Counter + 1

    END

    --====================================================================================================================

    -- Return the results

    --====================================================================================================================

    SELECT * FROM #Results

    Could easily be turned into a SPROC.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hello am running this script and am getting this error message:

    Msg 137, Level 15, State 2, Line 10

    Must declare the scalar variable "@db_name".

    trying to figure out where am going wrong?

    /*

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

    * Collect database space

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

    */

    insert

    into #dbspace (database_name,total_space,used_db_space,total_log_space)

    EXEC ('use [' + @db_name + ']

    select

    db_name = db_name(),

    total_space =

    (select

    sum(convert(decimal(35,2),size)) / convert( float, (1048576 /

    (select low from master.dbo.spt_values where number = 1 and type =

    ''E'')))

    from dbo.sysfiles),

    total_db_used =

    (select

    (sum(convert(float, case type when 2 then used_pages else

    data_pages end)) *

    (select

    low

    from

    master..spt_values

    where number = 1 and

    type = ''e''))/1024/1024

    from

    sys.allocation_units),

    total_log_space =

    (select

    sum(convert(decimal(35,2),size)) / convert( float, (1048576 /

    (select

    low

    from

    master.dbo.spt_values

    where

    number = 1 and

    type = ''E'')))

    from

    dbo.sysfiles

    where (status & 0x40)=0x40)' )

    -- end collection of database space

  • Yes... you haven't DECLARED @db_name...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ran that script you posted and i got the following error messages:

    Msg 170, Level 15, State 1, Line 6

    Line 6: Incorrect syntax near '0'.

    Msg 103, Level 15, State 7, Line 32

    The identifier that starts with 'mastermastermastermastermastermastermastermastermastermastermastermastermastermastermastermasterDataMBmastermastermastermasterma' is too long. Maximum length is 128.

  • What do you mean by DECLARE@db_name, and how can i get this thing sorted out?

  • In your code, you never say...

    DELCLARE @db_Name SYSNAME

    That means you have an undefined variable and it's gonna give you an error. Here's the beginning of your code... and there's no declaration for the variable @db_name...

    *

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

    * Collect database space

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

    */

    insert

    into #dbspace (database_name,total_space,used_db_space,total_log_space)

    EXEC ('use [' + @db_name + ']

    select

    db_name = db_name(),

    total_space = ...

    Further, once you declare the variable, you'll need to assign a value, with a correct database name, to it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 20 total)

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