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.
| Author |
Topic |
|
rabbott
Starting Member
2 Posts |
 Posted - 2007-06-04 : 15:47:36
|
| Hello,I have a database that the '.mdf' file is just huge. The physical size on disk is approximately 110 gig. I run weekly maintenance plans to rebuild the indexes on it.I ran the 'sp_spaceused' command and got the following results:database size: 106996.25 MBunallocated space: 9614.35 MBreserved: 99716504 KBdata: 13968056 KBindex_size 4002184 KBunused: 81746264 KBI was trying to clear the unused space, the numbers are telling me that I have 81 gig of space unused, but no matter what I do the '.mdf' file will not shrink.I ran the following command: DBCC SHRINKDATABASE ({dbname}, 10,TRUNCATEONLY)Any thoughts?Thanks for the helpRichard |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
rabbott
Starting Member
2 Posts |
Posted - 2007-06-04 : 16:49:38
|
| Thanks for the script Michael. I'll give it a try.One thing that I'm also trying to figure out is how the database '.mdf' got so big in the first place. In our database we flag records as being deleted - we don't actually run a 'delete' statements on them - very few tables ever get dropped so I'm just wondering if the weekly reindexing maintenance plan is causing the file to get bloated? |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-06-04 : 17:06:58
|
Make sure your run the command this way so that you are getting a correct result:exec sp_spaceused @updateusage = 'true' You can use this script to look at the size of individual tables:Script to analyze table space usagehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762CODO ERGO SUM |
|
|
|
|
|
|
trying to shrink the '.mdf' file