SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Script to analyze table space usage
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/14/2006 :  11:25:53  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 08/09/2007 18:27:43

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 06/02/2006 :  18:57:15  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 06/03/2006 :  05:46:21  Show Profile  Reply with Quote
"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 - 04/06/2010 :  12:17:09  Show Profile  Reply with Quote
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

USA
37143 Posts

Posted - 04/06/2010 :  15:24:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
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)

USA
7020 Posts

Posted - 04/06/2010 :  15:38:06  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 04/06/2010 15:39:28
Go to Top of Page

schiewe
Starting Member

2 Posts

Posted - 04/06/2010 :  18:01:03  Show Profile  Reply with Quote
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

USA
37143 Posts

Posted - 04/06/2010 :  18:03:11  Show Profile  Visit tkizer's Homepage  Reply with Quote


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

Myanmar (Burma)
1 Posts

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

binsel
Starting Member

Canada
5 Posts

Posted - 01/30/2011 :  19:29:58  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 01/30/2011 :  20:39:40  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 01/30/2011 20:40:19
Go to Top of Page

binsel
Starting Member

Canada
5 Posts

Posted - 01/31/2011 :  07:47:34  Show Profile  Reply with Quote
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

Edited by - binsel on 01/31/2011 07:48:39
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/31/2011 :  11:24:44  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000