Sunday 1 May 2011

Large LDF files in windows SBS 2008

By default the MSSQL database is set to full backup (which doesn't help if your backup software doesn't "play nice" with sql). Problem comes in when after some months, Exchange stop working altogether because the drive has been files by SharePoint's database (namely the replay logs, or .LDF files)

To rectify this, you need to:
  • Connect to the sharepoint database in SBS 2008 with the following link: \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
  • Regain control of the database. As you may or may not know, SharePoint is first installed using the local administrator, which kind of goes in limbo once you enable active directory. You end up with a pretty useless login. Re-enable  the 'sa' login by running the following query on your database: ALTER AUTHORIZATION ON DATABASE::[Database_Name] to sa; 
  • Right click on the database, properties> options. Set backup mode to simple.
  • Right click on the database, task, shrink, shrink files. Select log file, leave the rest as default and press ok. Tada! 20G recovered in 5 minutes (plus one hour to figure the !@#$%^& mess SBS 2008 created in the first place)


2 comments: