Database Size - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Database Size

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-08-24 : 11:31:02

hi we had a table that went from 380 mg to 33gig last night now management want break down of the tables sizes
if i run this

DROP TABLE ##SPACEFOREACHTABLE
create table ##SPACEFOREACHTABLE(
DATABASENAME VARCHAR(100),
TABLENAME VARCHAR(1000), ROW BIGINT, RESERVED VARCHAR(1000), DATA VARCHAR(1000), INDEX_SIZE VARCHAR(1000), USED VARCHAR(1000)
)

DECLARE @NAME VARCHAR(1000)
DECLARE C CURSOR FOR SELECT NAME FROM MASTER.DBO.SYSDATABASES WHERE NAME = 'ep_02gess2'
OPEN C
FETCH NEXT FROM C INTO @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (' USE ' + @NAME +
'
INSERT INTO ##SPACEFOREACHTABLE (TABLENAME,ROW , RESERVED , DATA , INDEX_SIZE , USED )
EXEC sp_MsForEachTable "Sp_SpaceUsed ''?''"
UPDATE ##SPACEFOREACHTABLE SET DATABASENAME ='''+ @NAME+'''
')
FETCH NEXT FROM C INTO @NAME
END
CLOSE C
DEALLOCATE C

SELECT * FROM ##SPACEFOREACHTABLE order by TABLENAME ASC

If i take the following

rows reserved data index unused Total(MG)
et_labor 56323272 20090336 17311176 2765472 13688 19619.46875

Total = reservered / 1024 to give mg

If i add up all tables...i get 28544.76563 which is 29 gig
thats close enough to the 33gig

Would take a far spreadsheet to give out to management

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-24 : 11:45:48

I think this script will give you what you are looking for with a lok more analysis.

Just run it in the database you want to analyze.

Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

CODO ERGO SUM

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-08-26 : 08:05:28

Thanks like that script does it in gig too.

Now they republished the database and got the sizes of tables down but im stuck with 33 gig...and lots more unused space

I tried shrink the MDF and LDF still at 33 gig
should i do ...Truncate free space from end of file..after i do shrink

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources