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

Database Size Expand / Collapse
Posted Monday, August 27, 2012 3:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:49 AM
Points: 76, Visits: 506
In My Production server i am facing following problem.

Totally my database size showing 134 GB.But my data size is 10GB and index size is 3 GB.whenever i use this sp_spaceused command.
i am getting following result

database_name database_size unallocated space
XYZ 137353.13 MB 122962.30 MB

reserved data index_size unused
------------------ ------------------ ------------------ -------
14720464 KB 11105104 KB 3625280 KB -9920 KB

How to reduce this MDF file size ? what is unallocated space?
Post #1350283
Posted Monday, August 27, 2012 3:52 AM



Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 14,849, Visits: 12,895
It means your database was once 134 GB big, but now it contains only for 10GB on data.
When data is deleted, space isn't automatically released. You can shrink your database files, but there are a whole bunch of reasons not to do that. (index fragmentation is one of them).

If you think your database will never be this big again, you can use DBCC SHRINKFILE.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1350290
Posted Monday, August 27, 2012 5:01 AM



Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 43,469, Visits: 40,619
Please don't cross post. It just wastes peoples time and fragments replies.

No replies to this thread please. Direct replies to:

Gail Shaw
Microsoft Certified Master: SQL Server, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1350314
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse