Sharepoint: shrink database on a fast way

  • [アーティクル]

Hello,

we all know that large SQL databases in SharePoint will have large transaction logs on SQL in case the database is much in use. In case you are using only SharePoint backup and no SQL backup you may have a problem with very large transaction logs and you need to shrink the transaction logs manually.

How to shrink transaction logs in a fast way?

Solution 1. normally you need to do a database backup (a FULL backup) of each database then the transaction log is empty. But the transaction log file is still so large. That's not nice and because the file is empty but has a large size on the file system. 

My way to reduce the transaction log is a bit faster:

Execute this on each sharepoint database:

USE MyDatabase

GO

BACKUP LOG MyDatabase WITH TRUNCATE_ONLY

GO

DBCC SHRINKFILE (MyDatabase_log, 1)

GO

As result you will see a Transaciton Log file with a size of 1 MB.

Is that nice?

regards

Patrick

Comments

  • Anonymous
    January 01, 2003
    Hello! <a href="dotnetfollower.com/.../a> is a way to shrink sharepoint database transaction log for MS SQL Server 2008. Thanks!

  • Anonymous
    January 01, 2003
    Hi Tom, thx for this last support statement for SQL 2008 R2... support rules are always in important things in the world... :-)

  • Anonymous
    January 01, 2003
    Hi Adeeva, your right that the whole script will not run on SQL2008, it was created for SQL 2005. Shrinking a database with "DBCC SHRINKFILE (MyDatabase_log, 1)" still runs on SQL 2008. But before executing this statement it's important to backup the transaction log. Before shrink the transaction log file you need to select your Backup model e.g. SIMPLE or FULL. I prefer FULL. My script below do this:

  1. SET BACKUP MODEL, I prefer FULL.
  2. Then create you create a Backup Device, for my database
  3. start the backup for data file and transaction log
  4. shrink the db file. (steps 2 and 3 can be cut if you don't want to backup your files) Script: ===== USE master; GO -- set the backup recovery model to FULL ALTER DATABASE MyDatabase  SET RECOVERY FULL; GO -- Create logical backup devices, 'MyDatabaseData' and 'MyDatabaseLog' USE master GO EXEC sp_addumpdevice 'disk', 'MyDatabaseData', 'Z:SQLDATABackupsMyDatabase_Data.bak'; GO EXEC sp_addumpdevice 'disk', 'MyDatabaseLog', 'X:SQLDATABackupsMyDatabase_Log.bak'; GO -- Back up the full MyDatabase database. BACKUP DATABASE MyDatabase TO MyDatabaseData; GO -- Back up the MyDatabase transaction log. BACKUP LOG MyDatabase TO MyDatabaseLog; -- now it's time to shrink the database file dbcc shrinkfile (MyDatabaseData_log,1) Regards Patrick

  • Anonymous
    February 03, 2010
    Dude, I don't think this option will run under SQL 2008.

  • Anonymous
    February 18, 2010
    Thanks for the script. Saved me some trial and error. Nice to add the dump devices too, much neater.

  • Anonymous
    August 09, 2011
    thx for SQL 2008 skrink link... here is the link which works: dotnetfollower.com/.../sharepoint-how-to-shrink-transaction-log-file

  • Anonymous
    February 23, 2012
    On SQL Server 2008 R2 "TRUNCATE_ONLY" is not supported anymore use  " TO DISK='NUL:' " instead