Database Size

Question

Post reply

Database Size

vs.satheesh

SSCrazy

Points: 2392
More actions
August 25, 2012 at 4:20 am

#265926

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?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

Is this database upgraded from SQL 2000 version?

What is the log file size?

Please run this query and post the results here:

use dbname

go

select name, type_desc, size/128 'Size(MB)', FILEPROPERTY(name, 'SpaceUsed') / 128 'SpaceUsed(MB)'

from sys.database_files

You can the reduce the MDF file size by using DBCC SHRINKFILE. But set the file size to appropriate size.

Unallocated space = 'Space in the database that has not been reserved for database objects.' (from BOL)

vs.satheesh SSCrazy Points: 2392 More actions · Answer 2

whenever i am executing following query i am getting following result.how to reduce the space

select name, type_desc, size/128 'Size(MB)', FILEPROPERTY(name, 'SpaceUsed') / 128 'SpaceUsed(MB)'

from sys.database_files

name type_desc Size(MB) SpaceUsed(MB)

IEDBNew_DataROWS13733714729

IEDBNew_LogLOG 459 19

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

You can use DBCC SHRINKFILE. For example:

use IEDBNew

go

dbcc shrinkfile(IEDBNew_Data, 30720) -- 30GB

vs.satheesh SSCrazy Points: 2392 More actions · Answer 4

Hi I am getting following result .But Disk space not yet reduced.please help me

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

------ ----------- ----------- ----------- ----------- --------------

71 17579232 128 1885120 1885088

Suresh B. SSC-Insane Points: 22986 More actions · Answer 5

It is because there is used page at the end which needs to be moved to front of the file.

Please run DBCC SHRINKFILE with NOTRUNCATE option as shown in the following example:

use IEDBNew

go

dbcc shrinkfile(IEDBNew_Data, NOTRUNCATE);

go

dbcc shrinkfile(IEDBNew_Data, 30720) -- 30GB

go

Divine Flame SSCoach Points: 15943 More actions · Answer 6

Just remember that shrinking will cause the indexes to be fragmented. Therefore rebuilding the indexes will be required after shrinking has been done.

Sujeet Singh