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

BACKUP LOG <Database_Name> WITH TRUNCATE_ONLY

DBCC SHRINKFILE(<Transaction_Log_Name>, 1)

GO

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

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.
Shrink 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 SharePoint Content database log file of 30GB! 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

About thangletoan

Hallo Aloha

Posted on 27/06/2013, in Công nghệ và Giáo dục, Chính sách CNTT, Microsoft SQL Server, SQL server and tagged . Bookmark the permalink. Để lại bình luận.

Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s

%d bloggers like this: