Skip to Content
May 08, 2007 at 10:59 PM

Using SHRINKFILE or not



We are facing the following problem:

In our BW system (3.5 / NW04) running on top of SQL2005 server the database files have over the time grown to an extend, that we reached the currently possible disk capacity. Of curse new data files have been added to the system so that operation can continue. By now the total size of the database is >2.5TB. But lately we ran into a strange problem, that we can’t execute the DBCC CHECKDB any longer on the database.

The problem we are facing is described in Microsoft KB article 926070 (KB926070">KB926070>) telling that while running the CECKDB command there is created a temporary snapshot database that is created on the same drive as the data file in question. But as there is not enough free space on some of the drives this snapshot database can’s be created big enough ending with an SQL Error.

So until now we can se the following solutions to this:

1) Running the CHECKDB while there is low activity in the system, possible reducing the space requirement of the snapshot database and thereby possible let the check run through without errors. Or even running the check on the database while SAP is shut down (no activity).

But as the system is supposed to run 24x7 it’s hardly impossible to find a time slot for this, as runtime would be several hours.

2) Use unload / reload of the whole database to reorder it. Taking into account the size of the database and thereby the time this would take, this also out of the question.

3) Use the SHRINKFILE command on the “full” database files to move some data to the “empty” files and thereby free some space just enough that the CHECKDB can run through without errors.

But use of the SHRINKFILE command is generally not recommended as described in the Microsoft Whitepaper on SQL2005 (SQLServer" target="_blank">">SQLServer for SAP) (Page 30) as it possible have impact on overall performance of the system.

All in all we are facing the problem that we can’t run a CHECKDB on our database, that on the other hand should be done regularly, but none of the mentioned solutions are possible or recommended.

So do we have any other possibilities? Or as we would like to use the SHRINKFILE is there anyone knowing or have experience on what impact this would have regarding performance?

Any comments or help on this?