How to retrieve the (MB) size of all tables in a database

I have a database under SQL2005 that in a 24 hour period has increased from
200MB to 2GB (this is the dbf file size not the log file size) and there are
no obvious reasons for the increase. We have also tried Shrinking both the D
B
and file with little reduction in size. Is there a way to query the database
and find out the sizes of tables etc to help with further investigation?
Looking at the properties of each in Management studio really isn't feasible
due to the number of objects. TIA
AntonyThe sp below will return all the tables in a database, by reserved size
desc (large to small)
Run this sp in the database of concern:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[BigTables]
AS
/ ****************************************
***********************************
***********
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@.sqlteam.com
* v1.1
*
****************************************
************************************
**********/
declare @.id int
declare @.type character(2)
declare @.pages int
declare @.dbname sysname
declare @.dbsize dec(15,0)
declare @.bytesperpage dec(15,0)
declare @.pagesperMB dec(15,0)
create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'
open c_tables
fetch next from c_tables
into @.id
while @.@.fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @.id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @.id
select @.pages = sum(dpages)
from sysindexes
where indid < 2
and id = @.id
select @.pages = @.pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @.id
update #spt_space
set data = @.pages
where objid = @.id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @.id)
- data
where objid = @.id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @.id)
where objid = @.id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @.id
and objid = @.id
fetch next from c_tables
into @.id
end
select --top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_MB = ltrim(str(reserved * d.low / 1048576.,15,0) + ' ' +
'MB'),
data_MB = ltrim(str(data * d.low / 1048576.,15,0) + ' ' + 'MB'),
index_size_MB = ltrim(str(indexp * d.low / 1048576.,15,0) + ' ' +
'MB'),
unused_MB = ltrim(str(unused * d.low / 1048576.,15,0) + ' ' + 'MB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables|||Antony wrote:
> I have a database under SQL2005 that in a 24 hour period has increased fro
m
> 200MB to 2GB (this is the dbf file size not the log file size) and there a
re
> no obvious reasons for the increase. We have also tried Shrinking both the
DB
> and file with little reduction in size. Is there a way to query the databa
se
> and find out the sizes of tables etc to help with further investigation?
> Looking at the properties of each in Management studio really isn't feasib
le
> due to the number of objects. TIA
> Antony
There is a known bug with the auto-growth function in SQL 2005. Sounds
like that might be your problem.
http://connect.microsoft.com/SQLSer...=12717
7
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This might be useful.
CREATE TABLE #Tables
( [name] nvarchar(20),
[rows] char(11),
[reserved] varchar(18),
[data] varchar(18),
[index_size] varchar(18),
[unused] varchar(18)
)
EXECUTE sp_MSForEachTable 'INSERT INTO #Tables EXECUTE sp_spaceused [?]'
SELECT
[name],
[data]
FROM #Tables
DROP TABLE #Tables
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Antony" <info AT webpc DOT biz> wrote in message news:u0L0oEKFHHA.3212@.TK2MSFTNGP02.phx.gbl
..
>I have a database under SQL2005 that in a 24 hour period has increased from
> 200MB to 2GB (this is the dbf file size not the log file size) and there a
re
> no obvious reasons for the increase. We have also tried Shrinking both the
DB
> and file with little reduction in size. Is there a way to query the databa
se
> and find out the sizes of tables etc to help with further investigation?
> Looking at the properties of each in Management studio really isn't feasib
le
> due to the number of objects. TIA
> Antony|||Thanks everyone for the advice. I found the table that was causing the issue
.
Antony
On 11/30/2006 12:04:10 PM, "Antony" wrote:
>I have a database under SQL2005 that in a 24 hour period has increased from
>200MB to 2GB (this is the dbf file size not the log file size) and there ar
e
>no obvious reasons for the increase. We have also tried Shrinking both the
DB
>and file with little reduction in size. Is there a way to query the databas
e
>and find out the sizes of tables etc to help with further investigation?
>Looking at the properties of each in Management studio really isn't feasibl
e
>due to the number of objects. TIA
>Antony