How to shrink SQL / SharePoint database / log files

April 5, 2011

SharePoint Config database logs are one thing to keep an eye on since they do have a tendency to grow. If you don’t perform a full farm backup usually the log doesn’t get emptied and it just keeps bloating.

If you’re running SQL Server Express with default installation, you can find the files in C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData

As we see in the example above, even though the database is only 6 megs, the log file grew to 11 gigs. Let’s reduce that:

1. If you don’t have it yet, download and install SQL Server Management Studio express from here.

2. Run the Management Studio and connect to your SQL Server.

3. Expand “Databases” and select your config database “SharePoint_Config”.
    IMPORTANT: If you are connecting to a Small Business Server or you cannot find the correct database use this string in the Server name field when connecting:

\.pipeMSSQL$MICROSOFT##SSEEsqlquery

4. Right Click it, select Tasks –> Shrink –> Files

5. In the new window select Release unused space and click OK.

If that doesn’t decrease the database size much, do the following:

1. First to be on the safe side, let’s back it up (this step is optional)

Select New Query

type the following:

BACKUP LOG [Sharepoint_Config] TO DISK=’D:configLogBackup.bak’
GO

where SharePoint_Config is the name of your config database file and D:configlogbackup.bak is the location and file name of where you’ll make the backup.

And click Execute

This may take a while if your log file is big.

2. Next clear the query (or click New Query again) and enter the following commands

BACKUP LOG [Sharepoint_Config] WITH TRUNCATE_ONLY
USE [SharePoint_Config]
GO

anc click Execute again

3. Clear the query or open another query tab and enter the next command:

DBCC SHRINKFILE (N’SharePoint_Config_log’ , 50)
GO

The 50 in the command above sets the size in MB to truncate the log to. If your config db is of different name, replace the SharePoint_Config part above with your config db name.

And click Execute yet again.

The result you get should be something like below:

and ofcourse the file size:

Finally you got some space!

m4s0n501
  • http://blog.halma.nl/2011/04/windows-2008-sbs-c-schijf-vol/ Arjen Halma

    Thanks for this info, it worked fine.

    Please be warned that in step 3 you need to add _log to the database name :)

  • http://www.it-norge.no Satheesh

    Arjen: What do you mean?

  • http://www.halma.nl Arjen Halma

    Hi,

    DBCC SHRINKFILE (N’SharePoint_Config_log’ , 50)
    GO

    When you follow step 2 and replace SharePoint_Config with the name of your database (example: SharePoint_Config_a8129403-e42a-4a77-9074-cb8006eb49ee)

    When you go to step 3, you can easy read over the _log at the end (i did :P )

  • Pingback: How to shrink SQL / SharePoint database / log files | ADMAzevedo™()

  • Gagandeep Singh

    Thanks, this was helpful.

    However, I wanted to understand if the Log file would shrink automatically, as I had read somewhere that the log file will automatically Shrink if regular backups are taken.

    Could you explain this concept a bit?

    Thank you,

    PS: I’m not a DB expert and would appreciate your feedback.

  • Pingback: SharePoint: Compactación de BDs (I)! - Blog de Juan Carlos González en Geeks.MS()

  • Steve

    Update here as it is a few years old and seems to have had the website backend changed. The article is missing the backslash characters from the commands! For example it is showing .pipeMSSQL$MICROSOFT##SSEEsqlquery whenit should be
    \.pipeMSSQL$MICROSOFT##SSEEsqlquery
    Assuming the slashes in this comment show up fine that is…!

  • Pingback: SharePoint: Truncado de archivos de Log de Bases de Datos! - Blog de Juan Carlos González en Geeks.MS()

More in database, files, log, Microsoft, SharePoint, shrink
This copy of Windows must be activated before you can log on

If you ever wanted to virtualize your Microsoft Windows computer or you get this error, you will often have problems...

Close