How to re-use the free space of database

[Former Member]

Hi Techies,

One of our BW is running on MSSQL 2005, having 1.5 TB of data splitted into 3 datafiles (In a single drive).

Appl team deleted some data from PSAs to free up the space at DB level, from DB02 its showing DB size as 1.5 T and free space available in database as 200+G.

But when we start loading the data its using harddrive space instead of free space available in database, Now I want to know the best practice to re-assign the free space to OS.

Shrink space is one option to free up the space, but not a recommended one in Production systems, also takes couple of days for such bigger systems and also may not be release space.

Data movement from object to new object is a way, and data migration from datafile to new datafile is a way I found in some of the forums and these are recommended methods supported by SAP as well as MSSQL.

Is thery any faster/online methods other than above?

Thanks in advance

Nick Loy

[Former Member]

Hi,

As this question comes up frequently, I've written a KBA which covers questions regarding this. Please have a look at:

SAP KBA 1721843 - SQL Server: Database-related post-processing after freeing a significant amount of space

Regards,

Beate

[Former Member]

Hi Nick,

I have the same question (or actual concern) as you.

I think what you are looking for "re-use the free space of DB " is actually saying about how to free up the disc space in OS level instead. Isn't it??

I read many forum threads and article on the web, both SAP and SQL related.

All are saying about not to "shrink" or think of reducing the DB data files sizes after free up some space of the DB.

Here, I really wanna question one more time to you all , just want a solid answer/decision:

As my case, my BW DB data size is reduced 80% after SQL 2008 row page compression.

Before: 800 GB with 79x GB data

After: 800 GB with 160 GB data

The DB files are of 800 GB and after SQL local backup, it result in a 160 GB bak file. It is very nice that I can move or backup it to tape with 3 times shorter time~!! However, the time I need to restore is no difference as before..still 3.X hrs and come out with 800 GB files.....

So, are all of you just compressed the DB with SQL 2008 R2 and benefit from only data size free up then do nothing for the "files size"??

PS: dont need to explain the impact or reason of shrink/reduce size of DB ...I ready many already.

just wanna know your case..

Thanks~~

[Former Member]

Hi Nick!

As a general rule, SAP recommends that it is the SQL Server engine that manages the internal space allocation, and you just grant that there is enough free space in all the datafiles. However, it is possible to go into a deeper detail (and this is something that is not specific for SAP, but the same for any SQL Server engines).

As you already explained, the usual method to return the unused space to the operating system is described in [SAP note 363018 (File management with SQL Server)|https://service.sap.com/sap/support/notes/363018]. In particular, in section D ("Increasing and decreasing database files"). There is more info at the SQL Server Books Online (BOL) help on the command "SHRINKFILE". The shrink should reduce the size of the database, but as you point out, it has some important drawbacks.

However, do not forget that SQL Server follows the so-called "proportional fill strategy" to grant that data is spread among all the datafiles according to the freespace left in each. If you have several files with free space available, the I/O load might be spread out. The SQL Server Books Online (BOL) gives an example how SQL Server would fill two files. See the section entitled: [Using Files and Filegroups|http://64.4.11.252/en-us/library/ms187087(SQL.90).aspx]; That example emphasizes how SQL Server distributes data in the available freespace. In order to grant the best distribution across multiple files they should all have the same size and also be grown by the same amount, so they keep similar amounts of freespace granting that the data is well distributed.

If you add more datafiles to a running database you always risk I/O contention problems as SQL Server will automatically begin to fill the new files until they are filled equally with the old ones. Normally, a good number of datafiles is a number that falls between the number of processors divided by 4 and the number of processors divided by 2 (with at least a minimum of 3 files).

So, as an idea, the "proportional fill strategy" could be the reason not to immediately use the unused space.

Otherwise, I would refer you to the excelent [MSDN articles on this particular|http://msdn.microsoft.com/en-us/library/ms180899(v=sql.90).aspx].

Cheers!

--J.Garcia