October 31, 2012 at 7:17 am
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:
RecordCountReservedSizeDataSizeIndexSizeUnusedSize
318947 168080 KB167528 KB352 KB200 KB
278226 98808 KB97864 KB56 KB888 KB
303083 57072 KB55360 KB48 KB1664 KB
129920 30032 KB29824 KB152 KB56 KB
319546 26416 KB17656 KB384 KB8376 KB
249201 12216 KB11048 KB56 KB1112 KB
108542 8344 KB7064 KB112 KB1168 KB
125661 7416 KB6256 KB56 KB1104 KB
29755 3912 KB3848 KB40 KB24 KB
215 1616 KB1568 KB16 KB32 KB
6043 528 KB504 KB16 KB8 KB
1789 272 KB216 KB16 KB40 KB
1458 248 KB200 KB8 KB40 KB
1720 208 KB176 KB16 KB16 KB
1864 208 KB176 KB16 KB16 KB
1354 144 KB64 KB24 KB56 KB
103 72 KB40 KB8 KB24 KB
217 32 KB16 KB16 KB0 KB
7 16 KB8 KB8 KB0 KB
0 96 KB8 KB32 KB56 KB
18 16 KB8 KB8 KB0 KB
1 16 KB8 KB8 KB0 KB
2 16 KB8 KB8 KB0 KB
15 16 KB8 KB8 KB0 KB
39 16 KB8 KB8 KB0 KB
13 16 KB8 KB8 KB0 KB
0 0 KB0 KB0 KB0 KB
What could be taking all of the space?
November 1, 2012 at 3:01 pm
Probably empty space left by deletions. Try shrinking the database and see if the unused space goes away. It should.
November 1, 2012 at 3:19 pm
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
November 1, 2012 at 4:55 pm
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
That's a good point, except that he mentioned only the mdf file, not the entire database.
November 1, 2012 at 11:22 pm
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
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.
November 2, 2012 at 6:16 am
Still stumped. I ran the query provided, and it only shows 800MB free in the data file:
UsedSpaceMBUnusedSpaceMBType
26.969446.66Log
23448.25800.00Data
23475.2110246.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_ROWSRESERVEDDATAINDEX_SIZEUNUSEDUSED_MBUSED_GB
320511169616169040368208165.64060.1618
27884199000980645688096.67970.0944
303652570725539248163255.73440.0544
1124114846446624160168047.32810.0462
13017433552333761601632.76560.0320
32013033080219764801062432.30470.0315
249922122801108056114411.99220.0117
1085428344706411211688.14840.0080
125866741662725610887.24220.0071
297684104404040244.00780.0039
2151616156816321.57810.0015
60195525281680.53910.0005
178927221616400.26560.0003
14582482008400.24220.0002
186721618416160.21090.0002
172020817616160.20310.0002
13541446424560.14060.0001
10372408240.07030.0001
21732161600.03130.0000
24332161600.03130.0000
7168800.01560.0000
15168800.01560.0000
18168800.01560.0000
13168800.01560.0000
39168800.01560.0000
1168800.01560.0000
2168800.01560.0000
000000.00000.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 🙁
November 2, 2012 at 7:51 am
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
November 2, 2012 at 8:09 am
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!
November 2, 2012 at 8:12 am
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.
November 2, 2012 at 10:58 am
You could also try running DBCC UPDATEUSAGE & then checking stated table sizes again.
When the db's not in use though.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply