We need to reduce the size of a SQL database

[Former Member]

What is the best practice for reducing the size of the SQL Database to free up more space.

[Former Member]

u can use DBCC SHRINK method from SQL Server

DBCC SHRINKDATABASE

( database_name [ , target_percent ]

[ , { NOTRUNCATE | TRUNCATEONLY } ]

)

see the BOL for further information

PS: i assume u using SQL Server 2000, i'm not sure wether it still support in 2005 version

[Former Member]

A shrink file only removes contiguous 'FREE' space (Normally at the end of the data file).

One assumes based on the question (Reduce) the DB has not got lots of free space, but rather, is large in size and the user wants to reduce the amount of 'DATA' within the DB

Reorg - removes 'deleted' records, and hence will 'REDUCE' the DB size - how much, who knows, depends on volumes and each system will be different.

In addition you may also want to look at archiving of data. Transaction SARA. You will need to work in combination with you application people and maybe legal as you have a legal requirement in terms of how long you have to keep data.

CMS (Content Management Server) can be used as an archiver. This is a SAP product.

[Former Member]

This depends what you mean by 'reducing'

You can free unused space from the database by reorging the data i.e. it removes deleted records from within the database, but it also controls how much space is left for inserts into the database i.e. free space.

You have two options with the reorg command via the maintenance plan. reorg with original space, or reorg with x (x being the free space YOU want as a %) run the plan with original space, i.e. it wont remove the space left for inserts as designed by SAP. This is by table i.e. some tables are updated more than others and require more free space. No point removing the free space from VBAK or have 100% free space in MANDT (Do so at your own risk - performance)

Run with 'original' first and see how much you get back.

WARNING - your logs will grow during this activity. So either move to simple (recovery) mode (understand what this means to your recovery plan in the case of a fail) else provide the system with F/S space to house the logs. Or setup a SQL job to back the data off to tape at short intervals (set to append to the tape!)

Dont run when everyones logged on, do it over a weekend, when the system is 'free'.

You can also 'recover' free space from the database via the shrink files or shrink DB commands. The shrink commands are best run after a reorg.(you can run via the gui too).

LAST NOTE

Having space in the database is a good thing. This means the database doesn’t have to Auto grow to allocate F/S space in order to store the data once the DB has used the allocated space. Auto grow is bad, it results in bad performance and means you are not doing your job as a DBA.

Message was edited by:

Kevin Morris

[Former Member]

Check out Shrinking Data file Concept the in SQL Server. Mistakenly if you create data file with lorge space you can reduce the space using shrink.If you need more info let me know.

Cheers!

[former_member193399]

Larry,

Why do you want to reduce the size of the sql database? Are you going to move it to a different drive? You are fine as long as the free spaces are evenly available in all the data files i.e. same free space in all the data files. If you still want to reduce the size i have posted a script which reduces to the desired space if it is available: