Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Script to analyze table space usage

Author  Topic 

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-14 : 11:25:53
Edit 2007-8-9:
Added code to show database file sizes. Not really closely related to tables sizes, but a lot of the people who need this want to know why their database it so large, so it may help to know which files, especially the logs, are so large, and if the files have empty space in them.


-- Script to analyze table space usage using the
-- output from the sp_spaceused stored procedure
-- Works with SQL 7.0, 2000, and 2005

set nocount on


print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'

select
[FileSizeMB] =
convert(numeric(10,2),sum(round(a.size/128.,2))),
[UsedSpaceMB] =
convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
[UnusedSpaceMB] =
convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
[Type] =
case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
[DBFileName] = isnull(a.name,'*** Total for all files ***')
from
sysfiles a
group by
groupid,
a.name
with rollup
having
a.groupid is null or
a.name is not null
order by
case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
a.groupid,
case when a.name is null then 99 else 0 end,
a.name




create table #TABLE_SPACE_WORK
(
TABLE_NAME sysname not null ,
TABLE_ROWS numeric(18,0) not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)

create table #TABLE_SPACE_USED
(
Seq int not null
identity(1,1) primary key clustered,
TABLE_NAME sysname not null ,
TABLE_ROWS numeric(18,0) not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)

create table #TABLE_SPACE
(
Seq int not null
identity(1,1) primary key clustered,
TABLE_NAME SYSNAME not null ,
TABLE_ROWS int not null ,
RESERVED int not null ,
DATA int not null ,
INDEX_SIZE int not null ,
UNUSED int not null ,
USED_MB numeric(18,4) not null,
USED_GB numeric(18,4) not null,
AVERAGE_BYTES_PER_ROW numeric(18,5) null,
AVERAGE_DATA_BYTES_PER_ROW numeric(18,5) null,
AVERAGE_INDEX_BYTES_PER_ROW numeric(18,5) null,
AVERAGE_UNUSED_BYTES_PER_ROW numeric(18,5) null,
)

declare @fetch_status int

declare @proc varchar(200)
select @proc = rtrim(db_name())+'.dbo.sp_spaceused'

declare Cur_Cursor cursor local
for
select
TABLE_NAME =
rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME)
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE = 'BASE TABLE'
order by
1

open Cur_Cursor

declare @TABLE_NAME varchar(200)

select @fetch_status = 0

while @fetch_status = 0
begin

fetch next from Cur_Cursor
into
@TABLE_NAME

select @fetch_status = @@fetch_status

if @fetch_status <> 0
begin
continue
end

truncate table #TABLE_SPACE_WORK

insert into #TABLE_SPACE_WORK
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
exec @proc @objname =
@TABLE_NAME ,@updateusage = 'true'


-- Needed to work with SQL 7
update #TABLE_SPACE_WORK
set
TABLE_NAME = @TABLE_NAME

insert into #TABLE_SPACE_USED
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
from
#TABLE_SPACE_WORK

end --While end

close Cur_Cursor

deallocate Cur_Cursor

insert into #TABLE_SPACE
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB,
USED_GB,
AVERAGE_BYTES_PER_ROW,
AVERAGE_DATA_BYTES_PER_ROW,
AVERAGE_INDEX_BYTES_PER_ROW,
AVERAGE_UNUSED_BYTES_PER_ROW

)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB =
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024),4),
USED_GB =
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024*1024),4),
AVERAGE_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),RESERVED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_DATA_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),DATA))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_INDEX_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),INDEX_SIZE))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_UNUSED_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),UNUSED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end
from
(
select
TABLE_NAME,
TABLE_ROWS,
RESERVED =
convert(int,rtrim(replace(RESERVED,'KB',''))),
DATA =
convert(int,rtrim(replace(DATA,'KB',''))),
INDEX_SIZE =
convert(int,rtrim(replace(INDEX_SIZE,'KB',''))),
UNUSED =
convert(int,rtrim(replace(UNUSED,'KB','')))
from
#TABLE_SPACE_USED aa
) a
order by
TABLE_NAME

print 'Show results in descending order by size in MB'

select * from #TABLE_SPACE order by USED_MB desc
go

drop table #TABLE_SPACE_WORK
drop table #TABLE_SPACE_USED
drop table #TABLE_SPACE




CODO ERGO SUM

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2006-06-02 : 18:57:15
wow thanks, worked great!

I had a logging table that was about 1 GIG, which I truncated. Will my db automatically shrink ? i don't have autoshrink so I guess its a manual process!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-06-03 : 05:46:21
"Will my db automatically shrink"

No, see: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=shrink

" i don't have autoshrink"

Good! Its very rarely a good idea to set that to automatic.

Kristen
Go to Top of Page

schiewe
Starting Member

2 Posts

Posted - 2010-04-06 : 12:17:09
This script works great on the sysfiles, but I need to alter it to return all the tables sizes in a database called WS93. Using SQL Server 2005. Any help would be greatly appreciated!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 15:24:22
quote:
Originally posted by schiewe

This script works great on the sysfiles, but I need to alter it to return all the tables sizes in a database called WS93. Using SQL Server 2005. Any help would be greatly appreciated!



Huh? Just run the script in the WS93 database. Sysfiles is a system table in all of the databases, it's even in your WS93. sysfiles is not a database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-04-06 : 15:38:06
Also, system tables are not reported by that script.

It gets the tables names from INFORMATION_SCHEMA.TABLES, and that does not show system tables.

Maybe you just forgot to scroll down to see the script output at the bottom?




CODO ERGO SUM
Go to Top of Page

schiewe
Starting Member

2 Posts

Posted - 2010-04-06 : 18:01:03
Thanks for treading lightly on the newbie. I've gotten this to work as needed. Many thanks!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 18:03:11


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Pi Si
Starting Member

1 Post

Posted - 2010-07-18 : 00:03:25
Please help me. How do I analyze the field space usage in a specific table?
Go to Top of Page

binsel
Starting Member

5 Posts

Posted - 2011-01-30 : 19:29:58
quote:
Originally posted by Michael Valentine Jones

Also, system tables are not reported by that script.

It gets the tables names from INFORMATION_SCHEMA.TABLES, and that does not show system tables.

Maybe you just forgot to scroll down to see the script output at the bottom?

CODO ERGO SUM



Is there a easy way to include system tables? MSDB can get big.

Thanks,

Burhan
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-01-30 : 20:39:40
quote:
Originally posted by binsel

quote:
Originally posted by Michael Valentine Jones

Also, system tables are not reported by that script.

It gets the tables names from INFORMATION_SCHEMA.TABLES, and that does not show system tables.

Maybe you just forgot to scroll down to see the script output at the bottom?

CODO ERGO SUM



Is there a easy way to include system tables? MSDB can get big.

Thanks,

Burhan



Most of the tables in the MSDB database are seen by the INFORMATION_SCHEMA.TABLES view, so it should work fine.

Did you try running the script in MSDB before posting this question?



CODO ERGO SUM
Go to Top of Page

binsel
Starting Member

5 Posts

Posted - 2011-01-31 : 07:47:34
quote:
Originally posted by Michael Valentine Jones

Also, system tables are not reported by that script.

It gets the tables names from INFORMATION_SCHEMA.TABLES, and that does not show system tables.

Maybe you just forgot to scroll down to see the script output at the bottom?

CODO ERGO SUM
Did you try running the script in MSDB before posting this question?

CODO ERGO SUM



No. I usually do not try to run scripts that dont include features I am looking for as claimed by their creators.

Burhan
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-01-31 : 11:24:44
quote:
Originally posted by binsel

quote:
Originally posted by Michael Valentine Jones

Also, system tables are not reported by that script.

It gets the tables names from INFORMATION_SCHEMA.TABLES, and that does not show system tables.

Maybe you just forgot to scroll down to see the script output at the bottom?

CODO ERGO SUM
Did you try running the script in MSDB before posting this question?

CODO ERGO SUM



No. I usually do not try to run scripts that dont include features I am looking for as claimed by their creators.

Burhan



OK, well the tables in MSDB are not system tables, except for the same system tables every other database has, so that was just your assumption that they were.




CODO ERGO SUM
Go to Top of Page
   

- Advertisement -