Truncate and Shrink SharePoint Transaction Logs, Databases

Some of the Transaction Logs and databases grown rapidly and on urgent basis I had to truncate the Log files on SharePoint Content Databases. Here is the SQL script to truncate and shrink SharePoint transaction log:

Use <database_name>
GO

ALTER DATABASE <database_name> SET RECOVERY SIMPLE
GO

DBCC SHRINKFILE (Database_Log_Name, 1); 
GO

ALTER DATABASE <database_name> SET RECOVERY FULL
GO

Important: Please note the <database_Name> and <database_log_Name> in above examples are logical names.

This sets the recovery model to “SIMPLE” temporarily, Shrink the Log file and set back to “FULL” recovery model. After executing the SQL script to truncate transaction log in my SharePoint 2010, it recovered me considerable space now!

Shrink SharePoint SQL Server Database, Log Files from SSMS:

Shrinking can be done from SQL Server Management Studio also.

Go to SSMS >> Right-click the target Database >> Tasks >> Shrink >> Files (or Database)

truncate sharepoint database log

Select the File Type and click on “OK” button to start shrinking. Here I’ve selected “Log” File type to shrink SharePoint transaction log files.

shrink sharepoint content database log

Same applies when you want to shrink SharePoint database size. I find it useful when I had to shrink SharePoint search databases.

How about removing the existing Log file and create a new one?

I had a SharePoint Content database log file of 30 GB! So, wanted to remove the existing log and create a new one. Simply add a new log file and remove the existing one from SSMS. Or you can:

  1.     Detach the database
  2.     Rename the log file
  3.     Attach the database without the log file
  4.     Delete the old log file
In case you don’t find any decrease in log file size, Try setting the Database to Single user Mode (Go to : Database Properties >> Options >> Set “Restrict Access” to Single User ), repeat the above steps and then revert the status again to Multi_User!

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

10 thoughts on “Truncate and Shrink SharePoint Transaction Logs, Databases

  • Salaudeen —

    Any chance that this can be done with PowerShell (5.0)? There’s a ton of content DBs and I’d like to script this to get all of the content DBs, and (probably) use a foreach loop to shrink them all.

    I’ve gotten many great tips and tricks from your site in the past, and I’m hoping the answer to this question will be another one! Thanks for your help.

    Reply
    • Hi,

      did you get around this ? I am looking to do the same thing with my content DBs

      Reply
    • I quickly put this together – if still needed;

      $path = “C:scriptswhite.csv”
      $dbs = Get-Content -path $path
      foreach ($n in $dbs)
      {
      $n
      invoke-sqlcmd “USE $n” -ServerInstance sqlsrv400 -QueryTimeout 0
      invoke-sqlcmd go -ServerInstance sqlsrv400
      Invoke-Sqlcmd “DBCC SHRINKDATABASE(N’$n’)” -ServerInstance sqlsrv400 -QueryTimeout 0
      invoke-sqlcmd go -ServerInstance sqlsrv400 -QueryTimeout 0
      }

      Feel free to make it better!

      Reply
    • Thanks, I’ll give that a shot.

      Reply
  • I also believe thence, perfectly indited post!

    Reply
  • Remember to backup your database after you have done the following.:
    Alternatively, You can set the recovery model to “SIMPLE” temporarily, Shrink the Log file and set back to “FULL” recovery model

    https://technet.microsoft.com/en-us/library/ms178052(v=sql.105).aspx

    Read more: https://www.sharepointdiary.com/2012/01/truncate-transaction-logs-of-sharepoint.html#ixzz3dQQIV4tK

    Reply
  • Hello! Very nice blog – I will be checking out your other posts soon.

    On the Shrink Log File, is there any “damage” that can be done just by shrinking the log? Should the content DB be backed up first?

    Also, if on SQL Server 2008 R2, and compatibility is 2005, is there any harm in raising the compatibility? This database is not going to be used in a 2005 environment.

    Thanks!

    Reply
    • 1. Shrinking the log is actually clears inactive part of the log, which consists of list of operations that have been performed and used as a reference for restores, rollbacks,checkpoints, etc. Shrinking the log does not loose any uncommitted log data!

      2. Database compatibility level mostly affects SQL syntax and query parsing, and it should have no impact on performance; anyway, consider it as a best practice.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *