Showing posts with label ldf. Show all posts
Showing posts with label ldf. Show all posts

Thursday 2 January 2014

Understanding SQL backups on SharePoint databases using the Full recovery mode

Overview:  This post looks at reducing the footprint of the ldf file.  SharePoint related databases with using the Full recovery mode keep all transaction since the last "differential". It explains how SQL is affected by backups such as SQL backups, SP backups and 3rd party backup tools (both SP backup and SQL backup tools).

This post does not discuss why all your databases are in full recovery mode or at competing backup products.  It also contains steps to truncate and then shrink the size of the transaction log.

Note: Shrink a ldf file for it to regrow each week/cycle is bad practice.  The only time to shrink is when the log has unused transactions that are already covered by backups.

Background
  • Using the full recovery model allows you to restore to a specific point in time.
  • A Full backup is referred to as your "base of the differential".
  • A "copy-only" backup cannot be used as a "base of the differential", this becomes important when there are multiple providers backing up SQL databases.
  • After a full backup is taken, "differentials" differential backups can be taken.  Differentials are all changes to your database since the last "base of the differential".  They are cumulative so obviously, they grow bigger for each subsequent differential backup until a new "base of the differential" is taken.
  • To restore, you need the "base of the differential" (last full backup) and the latest "differential" backup.
  • You can also back up the transaction logs (in effect the ldf).  These need to be restored in sequential order, so you need all log file backups.
  • If you still have your database you can produce the "tail-log" backup this will allow you to restore to any point in time.
  • Every backup get a "Log Sequence Number" (LSN), this allows the restore to make a chain of backup for restore purposes.  This chain can be broken using 3rd party tools or switching the database in the simple recovery mode. 
  • A confusing set of terminology is "Shrinking" and "Truncating" that are closely related.  You may notice an ldf file has got extremely large, if you are performing full backups on a scheduled basis this is a good size to keep the ldf at.  You don't want to grow ldf files on the fly as it is extremely resource intensive.  However say your log file has not been purging/removing transactions within a cycle, then you may have a completely oversized ldf file.  In this scenario, you want to perform a full backup and truncate your logs.  This remove committed transactions but the unused records are marked an available to be used again.  You can now perform a "shrink" to reduce the size of the ldf file again, you don't want ldf's growing every cycle so don't schedule the shrinking.
  • "Truncating" is marking committed transactions in the lfd as "free" or available for the writing new transactions too.
  • "Shrinking" will reduce the physical size of the ldf.  Shrinking can reclaim space from the "free" space in the ldf.
 The process to Shrink the transaction log files:

1.> Determine which databases are suitable candidates for shrinking the log file. 
DBCC SQLPERF(LOGSPACE);


2.> Perform a Full backup,
3.>  Next perform a transaction log backup and truncate the database.
 4.> Run DBCC ShrinkFile as shown below (please remember to leave growth so the ldf file is not growing, keep extra room in the ldf - this should be used to reduce the size log file that has grown way to far).  The example below will leave my transaction log at 100MB.
USE AutoSP_Config;
GO
DBCC SHRINKFILE (AutoSP_Config_log, 100);
GO

5.> Verify the ldf file has reduced in size.

Update 2014-02-05: I have Always on my separate SharePoint Reporting Services database.  The SP_RS_ServiceTempDB should be excluded from the availability groups.  Keep it in the simple recovery mode.  The logs grow extremely qu8ickly and it does not need to be in Full recovery mode.

Update 2014-04-25: Always on databases shrinking does not require you to remove the database from the AOAG, you can merely perform a full backup on the primary and then shrink the database. Note: Watch the backup chains you can break them especially if you have a 3rd party backup tool on SharePoint.

Update 2019-04-18:
Change a database from Full recovery model to simple using T-SQL and shrink the ldf file
Use [LiveDB]
            GO 
           ALTER DATABASE [LiveDB] SET RECOVERY SIMPLE WITH NO_WAIT
           GO 
           DBCC SHRINKFILE ('LiveDB',10) 
           GO 

Normal process is to change the db as follows 1) Set Simple Recovery 2) Shrink the Ldf (remember to size so it can handle a full backup cycle) 3) Set Full Recovery  4) Perform a Full backup (start the SQL backup chain again) 

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.

Fix:
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:
http://kerseub.wordpress.com/2012/04/10/sql-server-2008-2008r2-shrink-ldf-files/