Click here to monitor SSC
SQLServerCentral is supported by Redgate
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: Tuesday, November 29, 2016 9:38 PM
Points: 93, Visits: 628
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 @ 5:26 AM
Points: 15,506, Visits: 13,166
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: Saturday, December 3, 2016 5:18 AM
Points: 45,619, Visits: 44,147
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, M.Sc (Comp Sci)
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