Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Confused by table size


Confused by table size

Author
Message
mgrubbs 50094
mgrubbs 50094
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 212
I have a table in a database I inherited that has an mdf file of 25GB, with ~870MB space available. I run a disk utilization by table report, and get this:

RecordCount ReservedSize DataSize IndexSize UnusedSize
318947 168080 KB 167528 KB 352 KB 200 KB
278226 98808 KB 97864 KB 56 KB 888 KB
303083 57072 KB 55360 KB 48 KB 1664 KB
129920 30032 KB 29824 KB 152 KB 56 KB
319546 26416 KB 17656 KB 384 KB 8376 KB
249201 12216 KB 11048 KB 56 KB 1112 KB
108542 8344 KB 7064 KB 112 KB 1168 KB
125661 7416 KB 6256 KB 56 KB 1104 KB
29755 3912 KB 3848 KB 40 KB 24 KB
215 1616 KB 1568 KB 16 KB 32 KB
6043 528 KB 504 KB 16 KB 8 KB
1789 272 KB 216 KB 16 KB 40 KB
1458 248 KB 200 KB 8 KB 40 KB
1720 208 KB 176 KB 16 KB 16 KB
1864 208 KB 176 KB 16 KB 16 KB
1354 144 KB 64 KB 24 KB 56 KB
103 72 KB 40 KB 8 KB 24 KB
217 32 KB 16 KB 16 KB 0 KB
7 16 KB 8 KB 8 KB 0 KB
0 96 KB 8 KB 32 KB 56 KB
18 16 KB 8 KB 8 KB 0 KB
1 16 KB 8 KB 8 KB 0 KB
2 16 KB 8 KB 8 KB 0 KB
15 16 KB 8 KB 8 KB 0 KB
39 16 KB 8 KB 8 KB 0 KB
13 16 KB 8 KB 8 KB 0 KB
0 0 KB 0 KB 0 KB 0 KB

What could be taking all of the space?
jerry-621596
jerry-621596
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 644
Probably empty space left by deletions. Try shrinking the database and see if the unused space goes away. It should.
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 Visits: 11771
There is a good chance that all the space is being used by the transaction log. If the database is in full recovery mode and you are not doing regular transaction log backups, the transaction log space used will just continue to grow.

Try running this script to see what it tells you:
Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8488 Visits: 18099
Michael Valentine Jones (11/1/2012)
There is a good chance that all the space is being used by the transaction log. If the database is in full recovery mode and you are not doing regular transaction log backups, the transaction log space used will just continue to grow.

Try running this script to see what it tells you:
Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762


That's a good point, except that he mentioned only the mdf file, not the entire database.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 Visits: 11771
Luis Cazares (11/1/2012)
Michael Valentine Jones (11/1/2012)
There is a good chance that all the space is being used by the transaction log. If the database is in full recovery mode and you are not doing regular transaction log backups, the transaction log space used will just continue to grow.

Try running this script to see what it tells you:
Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762


That's a good point, except that he mentioned only the mdf file, not the entire database.


I know, but he might have been mistaken. The script I posted the link for should tell the full story about where the space is being used.
mgrubbs 50094
mgrubbs 50094
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 212
Still stumped. I ran the query provided, and it only shows 800MB free in the data file:

UsedSpaceMB UnusedSpaceMB Type
26.96 9446.66 Log
23448.25 800.00 Data
23475.21 10246.66 *** Total for all files ***

I get the extra space in the log file, that isn't what's bothering me.

Here's the second result set detailing each table:
TABLE_ROWS RESERVED DATA INDEX_SIZE UNUSED USED_MB USED_GB
320511 169616 169040 368 208 165.6406 0.1618
278841 99000 98064 56 880 96.6797 0.0944
303652 57072 55392 48 1632 55.7344 0.0544
112411 48464 46624 160 1680 47.3281 0.0462
130174 33552 33376 160 16 32.7656 0.0320
320130 33080 21976 480 10624 32.3047 0.0315
249922 12280 11080 56 1144 11.9922 0.0117
108542 8344 7064 112 1168 8.1484 0.0080
125866 7416 6272 56 1088 7.2422 0.0071
29768 4104 4040 40 24 4.0078 0.0039
215 1616 1568 16 32 1.5781 0.0015
6019 552 528 16 8 0.5391 0.0005
1789 272 216 16 40 0.2656 0.0003
1458 248 200 8 40 0.2422 0.0002
1867 216 184 16 16 0.2109 0.0002
1720 208 176 16 16 0.2031 0.0002
1354 144 64 24 56 0.1406 0.0001
103 72 40 8 24 0.0703 0.0001
217 32 16 16 0 0.0313 0.0000
243 32 16 16 0 0.0313 0.0000
7 16 8 8 0 0.0156 0.0000
15 16 8 8 0 0.0156 0.0000
18 16 8 8 0 0.0156 0.0000
13 16 8 8 0 0.0156 0.0000
39 16 8 8 0 0.0156 0.0000
1 16 8 8 0 0.0156 0.0000
2 16 8 8 0 0.0156 0.0000
0 0 0 0 0 0.0000 0.0000

From that, it looks like the data is less than a Gig.

I looked for any odd data types, like char(8000), but all I saw were some char(6), char(10), etc...although I would think that those would fall under the RESERVED category anyway.

Forgot to mention in my first post, build 10.50.1600, no database snapshots, full text catalogs, etc...

Still stumped Sad
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 Visits: 11771
You should run DBCC CHECKDB first to make sure there is no problem with the database.

If DBCC CHECKDB shows that the database is OK, then
Rebuild any tables with LOB columns (varchar(max), nvarchar(max), varbinary(MAX), text, ntext, image, xml, xml indexes etc.).
Rebuild any tables that are heaps.

It may be easiest to just rebuild all tables.
ALTER TABLE schema_name.table_name REBUILD PARTITION = ALL


mgrubbs 50094
mgrubbs 50094
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 212
It looks like 6 of the tables are heaps. Is it just ALTER TABLE <TABLENAME> REBUILD?

Is there any risk to doing that?

Also, there are no varchar(max), but a few with 150 or 250. Would a reorg with LOB_COMPACTION help here?

Thanks for all the feedback!
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 Visits: 11771
mgrubbs 50094 (11/2/2012)
It looks like 6 of the tables are heaps. Is it just ALTER TABLE <TABLENAME> REBUILD?

Is there any risk to doing that?

Also, there are no varchar(max), but a few with 150 or 250. Would a reorg with LOB_COMPACTION help here?

Thanks for all the feedback!



I changed my last post to say you should run DBCC CHECKDB first, and added the format for the table rebuild.
Gazareth
Gazareth
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2859 Visits: 5320
You could also try running DBCC UPDATEUSAGE & then checking stated table sizes again.
When the db's not in use though.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search