Post reply

Alternative to sp_spaceused

  • October 3, 2011 at 4:09 pm

    #245840

    Hi,

    I need to get unallocated and actual database size for all databases on the server. One way to do this is to make a copy of sp_spaceused and place it in each DB and call for another proc. Is there another way to get unallocated and actual size from one centralized location like sp_spaceused but with something that could be easily put into a temp table?

    Thanks

  • October 3, 2011 at 4:18 pm

    #1389899

    use the info from table master.sys.master_files and fileproperty.

    see http://www.sqlservercentral.com/Forums/Topic1182966-149-1.aspx#bm1183474

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

  • October 3, 2011 at 4:42 pm

    #1389901

    george sibbald (10/3/2011)

    use the info from table master.sys.master_files and fileproperty.

    see http://www.sqlservercentral.com/Forums/Topic1182966-149-1.aspx#bm1183474

    Thanks, I'm trying to monitor DB growth at database level not at a file level for all DBs. Does anyone have a script for that?

  • October 4, 2011 at 12:39 am

    #1389957

    Thanks, I'm trying to monitor DB growth at database level not at a file level for all DBs. Does anyone have a script for that?

    Maybe this template will be usefull for you:

    select

    db_name() as database_name,

    sum(total_pages)*8 as reservedKB,

    sum(used_pages)*8 as usedKB,

    sum(total_pages-used_pages)*8 as unallocatedKB

    from

    sys.allocation_units

    You can look how sp_spaceused gets data by running (and modify it for your needs)

    exec sp_helptext 'sp_spaceused'

  • October 4, 2011 at 2:24 am

    #1389984

    Lexa (10/3/2011)

    george sibbald (10/3/2011)

    use the info from table master.sys.master_files and fileproperty.

    see http://www.sqlservercentral.com/Forums/Topic1182966-149-1.aspx#bm1183474

    Thanks, I'm trying to monitor DB growth at database level not at a file level for all DBs. Does anyone have a script for that?

    do you want the actual space used by the database rather than file size? You can monitor that via the size of the full backup which is held in msdb..backupset (backup_size and type = 'D')

    i.e for the current database

    declare @dbname sysname

    set @dbname = db_name()

    print @dbname

    select backup_start_date,backup_finish_date,backup_size/1024 as 'size in MB'

    from msdb..backupset

    where database_name = @dbname and type = 'D'

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

  • October 4, 2011 at 8:59 am

    #1390185

    abrukovsky.v (10/4/2011)

    Thanks, I'm trying to monitor DB growth at database level not at a file level for all DBs. Does anyone have a script for that?

    Maybe this template will be usefull for you:

    select

    db_name() as database_name,

    sum(total_pages)*8 as reservedKB,

    sum(used_pages)*8 as usedKB,

    sum(total_pages-used_pages)*8 as unallocatedKB

    from

    sys.allocation_units

    You can look how sp_spaceused gets data by running (and modify it for your needs)

    exec sp_helptext 'sp_spaceused'

    I changed a copy of sp_spaceused and placed it in each DB. But I do not like that solution, I like that sp_spaceused can be called with any DB and get that DBs stats. I guess trying to change actual sys.sp_spaceused is not a good idea.

  • October 4, 2011 at 9:04 am

    #1390190

    I guess trying to change actual sys.sp_spaceused is not a good idea.

    you guess correct 🙂

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

  • October 4, 2011 at 9:09 am

    #1390191

    george sibbald (10/4/2011)

    Lexa (10/3/2011)

    george sibbald (10/3/2011)

    use the info from table master.sys.master_files and fileproperty.

    see http://www.sqlservercentral.com/Forums/Topic1182966-149-1.aspx#bm1183474

    Thanks, I'm trying to monitor DB growth at database level not at a file level for all DBs. Does anyone have a script for that?

    do you want the actual space used by the database rather than file size? You can monitor that via the size of the full backup which is held in msdb..backupset (backup_size and type = 'D')

    i.e for the current database

    declare @dbname sysname

    set @dbname = db_name()

    print @dbname

    select backup_start_date,backup_finish_date,backup_size/1024 as 'size in MB'

    from msdb..backupset

    where database_name = @dbname and type = 'D'

    interesting... I ran the above mentioned script and got 93,238,409.00 MB for one of my databases. The actual size of the DB is not 93 TB, it's about 1 TB.. Maybe it's because we are compressing our backups.

  • October 4, 2011 at 9:48 am

    #1390224

    I changed a copy of sp_spaceused and placed it in each DB. But I do not like that solution, I like that sp_spaceused can be called with any DB and get that DBs stats. I guess trying to change actual sys.sp_spaceused is not a good idea.

    You guess it right. 🙂

    I've suggested to create separate query. You can use dynamic query to change database context. How do you plan to use this information about database size?

  • October 4, 2011 at 11:38 am

    #1390269

    abrukovsky.v (10/4/2011)

    I changed a copy of sp_spaceused and placed it in each DB. But I do not like that solution, I like that sp_spaceused can be called with any DB and get that DBs stats. I guess trying to change actual sys.sp_spaceused is not a good idea.

    You guess it right. 🙂

    I've suggested to create separate query. You can use dynamic query to change database context. How do you plan to use this information about database size?

    I can change the database context but I don't like the fact that I need to have the procedure in every database. In case there is a change, I need to propagate the change to all the DBs. I plan on storing each database's allocated and unallocated space monthly to get a better understanding of the growth over time for hardware planning.

  • October 4, 2011 at 12:11 pm

    #1390285

    Lexa (10/4/2011)

    I can change the database context but I don't like the fact that I need to have the procedure in every database. In case there is a change, I need to propagate the change to all the DBs. I plan on storing each database's allocated and unallocated space monthly to get a better understanding of the growth over time for hardware planning.

    You don't need to propogate changes to all DBs.

    I do similar task this way:

    I store query that retrieves space usage data for every database except model and tempdb with needed details (tables and indexes in my case) in text file.

    Then I run this query on scheduled basis and store results in text file (so I can use them later). You can store the results where you need to.

    No procs, single text file that I can edit if needed.

  • October 4, 2011 at 1:18 pm

    #1390336

    abrukovsky.v (10/4/2011)

    Lexa (10/4/2011)

    I can change the database context but I don't like the fact that I need to have the procedure in every database. In case there is a change, I need to propagate the change to all the DBs. I plan on storing each database's allocated and unallocated space monthly to get a better understanding of the growth over time for hardware planning.

    You don't need to propogate changes to all DBs.

    I do similar task this way:

    I store query that retrieves space usage data for every database except model and tempdb with needed details (tables and indexes in my case) in text file.

    Then I run this query on scheduled basis and store results in text file (so I can use them later). You can store the results where you need to.

    No procs, single text file that I can edit if needed.

    Can you share the query?

  • October 4, 2011 at 11:49 pm

    #1390502

    Lexa (10/4/2011)

    Can you share the query?

    Sure, no problem.

    It will be something like this (I use another script, because I track objects size changes, not db's):

    declare @space_used table (database_name nvarchar(128), reservedKB bigint, usedKB bigint, unallocatedKB bigint)

    declare @sql nvarchar(max)

    set @sql =

    '

    use [?];

    select

    db_name() as database_name,

    sum(total_pages)*8 as reservedKB,

    sum(used_pages)*8 as usedKB,

    sum(total_pages-used_pages)*8 as unallocatedKB

    from

    sys.allocation_units

    '

    insert into @space_used

    exec sp_msforeachdb @sql

    select * from @space_used

    P.S. If you suspect incorrect results be sure to run DBCC UPDATEUSAGE command first (read about it in BOL).

  • October 5, 2011 at 11:25 am

    #1390852

    george sibbald (10/4/2011)

    declare @dbname sysname

    set @dbname = db_name()

    print @dbname

    select backup_start_date,backup_finish_date,backup_size/1024 as 'size in MB'

    from msdb..backupset

    where database_name = @dbname and type = 'D'

    I have just one tweak. backup_size is in bytes so the label should say 'size in KB'.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • October 5, 2011 at 12:04 pm

    #1390870

    Thanks all

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

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