Patrick Heyde

Coming from SharePoint Support & Living in SQL BI & Moving to Windows Azure

Sharepoint: shrink database on a fast way

Sharepoint: shrink database on a fast way

  • Comments 7
  • Likes

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
  • Dude, I don't think this option will run under SQL 2008.

  • 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:\SQLDATABackups\MyDatabase_Data.bak';

    GO

    EXEC sp_addumpdevice 'disk', 'MyDatabaseLog',

    'X:\SQLDATABackups\MyDatabase_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

  • Thanks for the script. Saved me some trial and error.

    Nice to add the dump devices too, much neater.

  • Hello!

    <a href="dotnetfollower.com/.../a> is a way to shrink sharepoint database transaction log for MS SQL Server 2008.

    Thanks!

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

  • On SQL Server 2008 R2 "TRUNCATE_ONLY" is not supported anymore

    use  " TO DISK='NUL:' " instead

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment