Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Confused by table size Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 7:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 8, 2014 11:41 AM
Points: 4, Visits: 161
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?
Post #1379296
Posted Thursday, November 1, 2012 3:01 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:58 PM
Points: 367, Visits: 615
Probably empty space left by deletions. Try shrinking the database and see if the unused space goes away. It should.
Post #1380045
Posted Thursday, November 1, 2012 3:19 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:25 PM
Points: 3,136, Visits: 11,490
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



Post #1380056
Posted Thursday, November 1, 2012 4:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:54 PM
Points: 3,637, Visits: 7,932
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1380086
Posted Thursday, November 1, 2012 11:22 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:25 PM
Points: 3,136, Visits: 11,490
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.





Post #1380148
Posted Friday, November 2, 2012 6:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 8, 2014 11:41 AM
Points: 4, Visits: 161
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
Post #1380316
Posted Friday, November 2, 2012 7:51 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:25 PM
Points: 3,136, Visits: 11,490
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

Post #1380376
Posted Friday, November 2, 2012 8:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 8, 2014 11:41 AM
Points: 4, Visits: 161
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!
Post #1380393
Posted Friday, November 2, 2012 8:12 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:25 PM
Points: 3,136, Visits: 11,490
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.

Post #1380395
Posted Friday, November 2, 2012 10:58 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 1,973, Visits: 3,268
You could also try running DBCC UPDATEUSAGE & then checking stated table sizes again.
When the db's not in use though.
Post #1380513
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse