Shrink an SAP database “online”.

Shrinking is bad

First of all: shrinking a database is nothing that you should do on a regular basis, like using this step in a Maintenance Plan or setting it up yourself via SQL Server Agent Jobs.

But if the customers asks for it, especially after having implemented Page Compression to save a lot of disk space – you have to obey 😉

First Approach

Of course we did not start with the production system, we already shrinked the DEV and QA system(s) of many other SAP systems without impact using the following approach:

Step 0: Document the original size and Space Usage.

shrinksap_0

Step 1: SHRINKDATABASE with NOTRUNCATE

-- Shrink database <DatabaseName> with NOTRUNCATE to move all database pages to the first most place in the physical file.
DBCC SHRINKDATABASE (SID, NOTRUNCATE);

shrinksap_1

Step 2: SHRINKDATABASE with TRUNCATEONLY

-- Shrink database <DatabaseName> with TRUNCATEONLY to cut the free space at the end of the physical file.
DBCC SHRINKDATABASE (SID, TRUNCATEONLY);

shrinksap_2

Here we can see a huge saving.

Step 3: Check Fragmentation

/*
    STEP 3 and 5

    Check Fragmentation of Database for documentation purposes.
*/

USE <DatabaseName>

DECLARE @DatabaseID int

SET @DatabaseID = DB_ID()

SELECT DB_NAME(@DatabaseID) AS DatabaseName,
       schemas.[name] AS SchemaName,
       objects.[name] AS ObjectName,
       indexes.[name] AS IndexName,
       objects.type_desc AS ObjectType,
       indexes.type_desc AS IndexType,
       dm_db_index_physical_stats.partition_number AS PartitionNumber,
       dm_db_index_physical_stats.page_count AS [PageCount],
       dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (@DatabaseID, NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000
ORDER BY AvgFragmentationInPercent DESC

shrinksap_3

You can see that the Fragmentation is as high as you can imagine.

Step 4: Index Maintenance

We use Ola Hallengrens Maintenance Solution for Index Optimizing, so this is simply executing the very Job in SQL Server Agent.

Situation afterwards:

shrinksap_4

Now with new unallocated space as the Indexes had to be rebuilt and reorganized. That increases the size of the data file, but far away from the original size.

Step 5: Check Fragmentation again

After the Index Maintenace is done, the Fragmentation of the Indexes look like they should.

Locking, Blocking, Deadlocking

After quite a few systems, where the approach shown above worked out pretty well, we faced problems with the most important production system of the customer. We experienced Deadlocks where the customer’s employees had to sit in front of their SAP and wait for their Transactions to complete.

So we had to killl the Shrink-Job and think of another solution.

We came across the blog post written by Jürgen Thomas

Database Volume is compressed nicely – but now we want to shrink the data files

with all the good recommendations, but we definitely were not allowed to have a downtime for the Shrink process as the project team assumed that it would take to long to run.

So with some additional hints from Clas Hortien (thanks, Clas 🙂  ) we were able to invent a process of shrinking the files, truncating them and so step by step releasing the unused disk space back to the file system

Final Solution

SAP Database

In this special scenario we had to deal with:

  • 2.25 TB production database with 7 equally sized datafiles
  • SAP ECC System (main ERP System)
  • Global Customer, operates 24 x 7
  • No downtime accepted for Shrinking Process
  • No performance impact accepted

Phew…

Shrinking Plot

  • Run on a daily basis between 7 pm and 11 pm. This is the timeslot where the customer accesses the system not too much.
  • On occuring Deadlocks “owned” by the Shrink process: Kill the Shrink Process.

The solution contains three SQL Server Agent Jobs per Data File.

  1. FIT DBA SID Shrink File SIDDATA1
  2. FIT DBA SID Shrink File SIDDATA1 KILL and TRUNCATE
  3. FIT DBA SID Shrink File SIDDATA1 STOP KILL and TRUNCATE JOB

and one single “Prevent” Job, that had to find out, if deadlocks occur and kill the Jobs:

FIT DBA SID Shrink File Prevent Deadlocks

shrinksap_5

Following is the description of what the Job Steps really do. I will explain them for the first data file, the otheres are just the same.

FIT DBA SID Shrink File SIDDATA1

Try to shrink the Datafile 1 to the smallest size possible.

DBCC SHRINKFILE (SIDDATA1, 1);

FIT DBA SID Shrink File SIDDATA1 KILL and TRUNCATE

This job consists of two steps:

KILL

Kill the Shrink Job.

DECLARE @SessionID INT
DECLARE @SQLStatement NVARCHAR(20)
-- select current session
SELECT		@SessionID = r.session_id
			-- , r.*, t.*
FROM		sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE		t.text LIKE '%DBCC SHRINKFILE (SIDDATA1,%'
AND			r.command LIKE 'Dbcc%'
PRINT 'SessionID to be killed: ' + CONVERT(NVARCHAR(14),@SessionID)

SET @SQLStatement = 'KILL ' + CONVERT(NVARCHAR(14),@SessionID) + ';'
PRINT @SQLStatement
EXEC sp_executesql @SQLStatement

TRUNCATE

Afterwards execute the TRUNCATEONLY to release the free space at the end of the data file.

DBCC SHRINKFILE (SIDDATA1, TRUNCATEONLY);

FIT DBA SID Shrink File SIDDATA1 STOP KILL and TRUNCATE JOB

This job now only got one step. It is necessary to also kill the “TRUNCATEONLY” job, because too many “running” jobs would also lead to locking and blocking.

EXEC dbo.sp_stop_job N'FIT DBA SID Shrink File SIDDATA1 KILL and TRUNCATE'

FIT DBA SID Shrink File Prevent Deadlocks

Now this is the watch dog of all Shrink jobs:

DECLARE @WhoIsActive TABLE (
	SessionID INT
	, BlockingSessionID INT
	, SQLStatement NVARCHAR(max)
)

INSERT INTO @WhoIsActive (SessionID, BlockingSessionID, SQLStatement)
SELECT S.session_id
, ISNULL(R.blocking_session_id, 0)
, ST.text
FROM sys.dm_exec_sessions S
INNER JOIN sys.dm_exec_connections C ON S.session_id = C.session_id
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) AS ST
LEFT JOIN sys.dm_exec_requests R ON S.session_id = R.session_id
WHERE S.is_user_process = 1
AND S.session_id <> @@spid -- exclude itself

-- ##########################################################
-- ### SELECT
DECLARE @BlockingShrinkSessionID INT
SELECT	@BlockingShrinkSessionID = SessionID FROM @WhoIsActive
WHERE	SQLStatement LIKE '%DBCC SHRINKFILE (SIDDATA%'

IF EXISTS(SELECT 1 FROM @WhoIsActive WHERE BlockingSessionID = @BlockingShrinkSessionID)
BEGIN

	DECLARE @JobName sysname	 

	DECLARE TheCursor CURSOR FOR
	SELECT name from msdb.dbo.sysjobs jobs WHERE jobs.category_id = 102 AND name like 'FIT DBA SID Shrink File SIDDATA%'

	OPEN TheCursor
	FETCH NEXT FROM TheCursor INTO @JobName   

	WHILE @@FETCH_STATUS = 0
	BEGIN
		   DECLARE @SQLStatement NVARCHAR(256);
		   SET @SQLStatement = 'EXEC msdb.dbo.sp_stop_job N''' + @JobName + ''';'
		   PRINT @SQLStatement;
		   EXEC(@SQLStatement);

		   FETCH NEXT FROM TheCursor INTO @JobName
	END   

	CLOSE TheCursor
	DEALLOCATE TheCursor

	execute msdb.dbo.sp_send_dbmail @profile_name='<PutYourMailProfileHere>',	@recipients='<PutYourEMailRecipientHere>', @subject='FIT DBA SID Shrink PREVENTED FROM BEING DEADLOCKED'

END

Schedule of the Jobs

So how were the Jobs scheduled?

shrinksap_6

  1. We started with the first job on the full hour and scheduled each job hourly.
  2. The “SHRINK” jobs were scheduled to run.
  3. The “KILL and TRUNCATE” jobs were scheduled to run 5 minutes after the “SHRINK” job.
  4. The “STOP KILL and TRUNCATE JOB” jobs were scheduled to run 5 minutes after the “KILL AND TRUNCATE” job.
  5. Between each data file we put 2 minutes “delay”.
  6. The “Watch Dog Job” was scheduled to run throughout the whole timewindow every 10 seconds. This is the time the customer allowed the employees to sit in front of the SAP screen.

Outcome

The customer did not experience any performance impact on the production system, just a small bunch of “Prevent from being deadlocked” emails hit our inbox.

This is the historical decreasement of the size of the database:

shrinksap_7

 

Thank you for reading and best of luck shrinking your databases (if absolutely necessary)

Christoph

 

 

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Shrink an SAP database “online”.

  1. Amir says:

    Hi Christoph,

    Very nice read with all technical steps. Many others will get benefited from this blog. Thanks for sharing.

    This technique that you have devised worked very effectively without posing any risk and the results below are very promising.

    So it seems ‘Compression’ is not bad after all 

    Thank you very much for your efforts leading to this great result and bringing this long pending task to its closure. 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s