Monday, 18 November 2013

Reducing Ldf sizes for SP Config DB using AOAG

WARNING: DO not follow this post - I have just destroyed a Test farm doing a quick fix to reduce the logs!!!!

Also see this post regarding ldf shrinking

Problem: ldf files grow continuously when the database is in "Full" recovery mode and the database transaction log is not backed up.  This scenario is common in test and developer environment where full backup cycles are not performed regularly. 

This post shows a method of clearing down the transaction logs.  This is merely to created space not for backup purposes.

Scenario:  My scenario is a test environment SQL contains 2 VM's using SQL using Alway On (AOAG) with 2 times 100GB hard drives.  The databases are not backed up and all db's are in the Full Recovery mode.  My SP Config db ldf has grown rather big and needs to be reduced.

Tip:  If you are using always on availability, you can't simply backup the logs, making a checkpoint and shrink the log.  I need to remove the db from the AOAG and then go to the ldf shrink actions, this is painful.

1. On the Primary using SQL Management studio remove the db from the AOAG databases.
2. Backup the config db.  SQL Management Studio > Open the SQL instance where SP databases are located > Right click on the config db e.g. "AutoSP_Config"> Tasks > Backup > Set the "Backup Type" to "Transaction Log" and perform the backup.
3. Run the "CheckPoint" T-SQL cmd
4. Perform the backup again (as done in step 2).
5. Shrink the log.   SQL Management Studio > Right click on the database > Tasks > Shrink
6. After the logs have been reduced add the database back to be a AOAG database.

More Info:


Post a Comment