This Blog provides information about running SAP Applications on the Microsoft Platform. The Blog is written by people who are working with SAP on the Microsoft Platform for decades.
Quite a few customers have implemented SQL Server Database compression on very large SAP databases and have reduced their actual database size by such a large factor that they now have terabytes of free space allocated to the SAP database files that they wish to reclaim.
These customers have several options to perform, but the options can be roughly categorized into:
In this article, we will take a look into the details of shrinking the data files in the second category and the way how we helped a few customers this year to get the process working.
What we usually recommend to use for shrinking the data files after the compression has been finished is the SQL functionality ‘DBCC SHRINKFILE’. This is a command we’ve commented about in several previous SAP Notes and whitepapers in less than positive way. But, in this single scenario it is at the end unavoidable for customers who can’t afford the downtime of a full export/import of the database in an SAP Database Independent System Copy.
Using DBCC SHRINKFILE, one needs to keep several things in mind:
And, most importantly, we have one last behavior which we lately became aware of when several customers with extremely high workloads, especially on a small number of tables, tried to shrink files. There are some situations where SHRINKFILE will move certain page content which requires an update of SQL Server dictionary data. Some examples of these cases are: moving content of the root page of an index; or moving the first data page; or moving IAM pages. In these cases, SHRINKFILE will request a schema modification (Sch_M) lock (something we discussed in this blog: http://blogs.msdn.com/b/saponsqlserver/archive/2014/01/17/new-functionality-in-sql-server-2014-part-3-low-priority-wait.aspx ) before processing that object and continuing to shrink the file.
As we describe in detail in the blog above, uncommitted read queries will bypass the lock wait list and therefore a high enough uncommitted read load on a table can continuously delay the grant of a SCH_M lock for that same table. So in the situations described above where SHRINKFILE needs to place a SCH_M lock on a table and there’s also a high enough uncommitted read workload preventing the grant of the SCH_M lock, several of our customers were unable to complete SHRINKFILE because the process was strangling their workload and hardly shrinking the files.
The solution that we applied with those customers did require a short down time of the SAP application and the usage of a SQL Server functionality which we normally don’t support with SAP Netweaver. The way we solved the issue is to isolate the table(s) which constantly were blocking the progress of SHRINKFILE and thereby even strangling major business processes into a separate SQL Server Filegroup. This method was only used in a few cases where it literally was not possible to get SHRINKFILE progressing because one really couldn’t find a way around changing the workload. In more detail the following was done:
With some customers it was enough to just move one table. With some other customers it was a handful tables. The most important points to note and consider are:
As mentioned already, the method described in this blog is something which was utilized in a few cases where no other workaround was possible to get the database files shrunk during normal online hours. The workaround steps described here must be tested very thoroughly and we suggest you test it repeatedly.
Most importantly, we want to reiterate that this workaround of utilizing a second filegroup is only to be used temporarily in order to complete the file space reclamation. Over the years, people have often asked us if they should use multiple filegroups for other reasons and so far our testing with SAP has always shown the current, single filegroup method is the best architecture for SAP NetWeaver systems. This is why all SAP NetWeaver system are installed in a single, default filegroup and you must ensure you revert your system to that same architecture if you use the workaround documented here.
Thanks a lot to Leslie Moser from SAP for really detailing some of the steps described in this scenario.
Juergen, very timely for us at Lubrizol - thanks.
Here are some notes from what we saw:
We tested the shrinkfile on our SAP databases.
After the compression was completed, the database were allocated at 4.2 TB, with only 1.4 TB being used with 8 datafiles.
We ran the shrink command in our test environment (with SAP down), which has 65 GB of memory and SQL allocated 50 GB of memory.
It took 1 hour 50 minutes for first file shrink from 525,000 MB to 475,000 MB.
After reboot, the second file took 1 hour 25 minutes.
On our production server, it has 132 GB of memory and 80 GB is allocated to SQL.
It only took 14 minutes 25 second to shrink from 525,000 MB to 475,000 MB.
After first run, the other 7 files each one took about 5 minutes to finish.
Our assumption is that the memory does make big difference in the shrink process.
P.S. The test databases were a copy of Production from < 4 weeks back (if that helps)