<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server Newbies
»
Confused by table size
Confused by table size
Rate Topic
Display Mode
Topic Options
Author
Message
mgrubbs 50094
mgrubbs 50094
Posted Wednesday, October 31, 2012 7:17 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, May 17, 2013 1:42 PM
Points: 4,
Visits: 132
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
jerry-621596
jerry-621596
Posted Thursday, November 01, 2012 3:01 PM
Old Hand
Group: General Forum Members
Last Login: Yesterday @ 11:18 AM
Points: 364,
Visits: 580
Probably empty space left by deletions. Try shrinking the database and see if the unused space goes away. It should.
Post #1380045
Michael Valentine Jones
Michael Valentine Jones
Posted Thursday, November 01, 2012 3:19 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 10:43 AM
Points: 2,945,
Visits: 10,517
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
Luis Cazares
Luis Cazares
Posted Thursday, November 01, 2012 4:55 PM
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 960,
Visits: 1,924
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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1380086
Michael Valentine Jones
Michael Valentine Jones
Posted Thursday, November 01, 2012 11:22 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 10:43 AM
Points: 2,945,
Visits: 10,517
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
mgrubbs 50094
mgrubbs 50094
Posted Friday, November 02, 2012 6:16 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, May 17, 2013 1:42 PM
Points: 4,
Visits: 132
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
Michael Valentine Jones
Michael Valentine Jones
Posted Friday, November 02, 2012 7:51 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 10:43 AM
Points: 2,945,
Visits: 10,517
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
mgrubbs 50094
mgrubbs 50094
Posted Friday, November 02, 2012 8:09 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, May 17, 2013 1:42 PM
Points: 4,
Visits: 132
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
Michael Valentine Jones
Michael Valentine Jones
Posted Friday, November 02, 2012 8:12 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 10:43 AM
Points: 2,945,
Visits: 10,517
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
Gazareth
Gazareth
Posted Friday, November 02, 2012 10:58 AM
UDP Broadcaster
Group: General Forum Members
Last Login: Monday, May 20, 2013 1:53 AM
Points: 1,474,
Visits: 2,342
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 »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
