Running out of space on your SBS 2008 box? SharePoint SQL log files are on of the big ways to get space back!

I am writing about this as I found little information around the web on how to do it that actually worked.

Here is my method of Shrinking the log files which is what I found the easiest, PLEASE READ THIS ENTIRELY BEFORE STARTING.

I accept no liability for damage done if you do this and it goes badly or you do it wrong, But I have used this hundreds of times.

Ensure your logged into an administrator account on the server.

Open an Administrator command prompt.

Type: sqlcmd -E -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

1> Type: select name from sys.databases

2> go

This will spit out a whole lot of data, Right click the command box and select all then hit the right hand mouse button which will copy it.

Put this into a text file we will need it further down the track.

Now right click on SQL SERVER MANAGEMENT STUDIO EXPRESS and run as Administrator (This is located under SQL 5 on most sbs 2008 boxes)

In the server box type: \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

and just click login (it uses your current windows authentication so ensure your using an administrator account).

Click and expand Databases -> now you should see one that looks similar to “SharePoint_Config_……….” this is the one we will be focusing on.

Right click on it and select Tasks -> backup

You are going to run two backups, a Full one and a Transaction Log.

Ensure backup type is: full

Under Destination check “Disk” and add a new location for the backup to go (Onto another HD is the best option)

Now run this backup, Repeat the steps above except change Backup Type to: Transaction Log and add a new location with a new filename under destination to create another backup file.

Once this has run right click on the “SharePoint_Config_……….” database again and select “New Query”

In the new Query box you will type the Following EXCEPT you will change the database name to match the one we saved from the cmd prompt earlier.

“BACKUP LOG [SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6] WITH TRUNCATE_ONLY”

NOTE: YOU CANNOT COPY AND PASTE, You must write it out

Now above click on the “TICK” which will parse this query for errors, If it comes back below with no errors then click the Run button and execute.

Once this has completed we will move onto the second and last query to run.

Right click  on the “SharePoint_Config_……….” database again and select “New Query”.

Run this query “DBCC SHRINKFILE (N’SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6_log’,50)”

NOTE: YOU CANNOT COPY AND PASTE, You must write it out

Click the Tick again to parse for errors and then execute.

Once this has completed you have successfully shrunk your Data base logs to 50MB in size, In the last query above if you change the number at the end (in this case 50) to something else it will shrink it to that size. (This is in MB).

Hopefully this will help someone trolling the internet looking for the answer if they didn’t already know how!

It also helps me remember where I put the How to!

TechKiwi is back in action!

Advertisements