How to reduce allocated Space to DB in mssql 2005

[Former Member]

Hi ,

We are using SAP with Mssql2005 on Windows 2008r2.server.

One week back we provided 400 GB space to My database files (for each 100GB), But right now we don't want that much space for My system.We are planing to use that extra space for another system.

I want to know below points.

1)How to reduce my database free space from Mssql 2005 management studio.

2)What is the Procedure.

3)Is it possible to reduce the Database free size once added to SQL Database files.

Please suggest the procedure, its urgent requirement.

[xymanuel]

In your case, just shrink the Files. The SQL Server will reorganize the pages to the beginning of the files.

But i also recommend you to upgrade du SQL Server 2008 R2 / 2012 and compress the DB!

We started with ~1TB (ERP 4.7 Non Unicode 2005), migrated to SQL Server 2008 R2 ECC 6 EHP 5 and grow up to 1,9 TB. With this size we started to compress the whole DB (first ROW then PAGE).

We ended up with 400GB! As a result of this, the DB response rimes reduce significantly because

1. The storage do not have to read / write as many blocks as before.

2. The SQL Server data cache can now hold much more data than before. (before 1,9 TB vs 190GB Cache, after 400 GB vs 190GB cache. After a 3 day bufferwarmup we do not have longer any cachehitratio drops below 99%)

3. CPU usage because of page compression only goes up ~3%.

Use ABAP Report "msscompress" after the upgrade to compress the tables. (Keep TA Log in mind!!)

SQL Server 2008 r2 has other benefits like online index rebuild or SQL Server Mirroring.

Regards

Manuel

[Former Member]

Hi Manuel Herr,

Thank you for information, its very useful .

[Sriram2009]

Hi Rao

1)How to reduce my database free space from Mssql 2005 management studio.
2)What is the Procedure.

Open the MS Sql 2005 management studio - select the Server Database (SID) - right click - go to Tasks - select shrink - select file check the free space in database files & log file.  before starting the shrink better you can take the full database backup into local disk or tape

3)Is it possible to reduce the Database free size once added to SQL Database files.

    You can create another one more partition to move any one of the database file. kindly refer the SAP Note 363018 - File management for SQL Server

BR

SS

[Former Member]

Below i am attaching the screen shots for your reference.

[Sriram2009]

Hi Rao

What about the log? could share the screen shot of transaction ST04?

BR

SS

[Former Member]

Thank you Sriram for your immediate reply.

[Sriram2009]

Hi Rao

1.  You can perform the Database shrink. During this process keep an eye on transaction log & do it during off peak time. Just go through the Microsoft blog Shrink a File

2. If your are migrating  the database to MS Sql 2008 R2 you can get the database compression feature it may reduce your disk space more than 40%,

Regards

  Sriram

[Former Member]

Hi Sriram,

Is shrinking Database is recommendation for SAP.

We want to shrink Database and we are planing to use that free space for other systems/

Please let me confirm .

[Sriram2009]

Hi Rao

If unused space more you can do the shrink and then run the DBCC check database consistence check

BR

SS