I am trying to reduce the size of a SQL Server Database Transaction Log File. Its eating 42.6 GB of drive space!!!! I have been trying to follow this guide...http://luka.manojlovic.net/2008/06/15/reduce-sharepoint-services-30-logldf-files/ but when I run the command \\.\pipe\mssql$microsoft##ssee\sql\query in the query box I get the error message Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '\'. EDIT: The actual file name is.... SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6_log.LDF Go easy on me please, I never use SQL
I don't have SQL Server Management Studio at home, and my Azure VM instance takes an AGE to start, so I can't get screenshots I'm afraid. Looking at this... ... it seems like you have to have to use that string to connect to the SharePoint database server itself. Whereas your comment... ...suggests that you're trying to run "\\.\pipe\mssql$microsoft##ssee\sql\query" as a command in a query window, i.e. after you've connected to a server. So when you open Management Studio and see this dialog... ...the article suggests that you need to put the string "\\.\pipe\mssql$microsoft##ssee\sql\query" in the "Server name" field, and that should connect you to the database instance associated with SharePoint. I'd also imagine that you'd have to do this on the same box that hosts the SharePoint server. I could be wrong though, I know my way around Management Studio & T-SQL fairly well but I haven't worked with SharePoint.
Thanks BLC, I tried the following but it does't look happy. What if I force delete the log file? Whats the worst that can happen lol
...Huh. I'm afraid I've no idea then. Erm... I've no idea how SharePoint would react to that... SQL Server would probably have a bit of a fit, too. Sorry I can't be more help.
Don't delete the log files manually, you will kill the share point server. Try this http://stackoverflow.com/questions/56628/how-do-you-clear-the-sql-server-transaction-log
Ha Ha, I was only kidding lol. I will have a read through that link EDIT: Just to add that this is the view i get in the SQL manager..... I cannot see anything to do with sharepoint. This is an SBS 2008 server though if that makes a difference?
what sbs server is this, will see if i can get a screenshot then? Also are you connecting locally to the SQL DB?
It's just SBS2008 How do you mean locally? The management studio is on the same server as the SQL database if that helps? EDIT: I'm at home now so I can't try anything until tomorrow
The short version is that you need to do a backup of just the transaction log, then you can run a shrink on the log file and get it down to whatever is set as the minimum size. I've not administered Sharepoint for a long while, so I'm not sure which of those databases you need to shrink. At a guess, it looks like that's not the correct database instance. (A database instance is basically a separate install of SQL server on the same machine with it's own separate set of databases). To get a list of all the instances you can run this in Powershell: Code: Get-ItemProperty 'HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL' One of the names it returns should be your Sharepoint install (I guess it'll say sharepoint in the name). Take that name and put .\ in front and type all that into the 'Server Name' box in the 'Connect to server' window (the screenshot in your second post) so it'll say something like .\SHAREPOINT Hopefully it'll then connect to the correct instance and you'll be able to see the database you want to shrink.
Thanks Phuzz, I ran the command but the result isn't what I expected!...... Perhaps there is something really wrong with SQL on this machine
Just to update that I rebooted the server and the original plan worked and I now get this.... I had to take ownership of the config database to get to the properties, but it has successfully shrunk and the backups are working again Thanks for all your help guys, it really is appreciated
Its part of sharepoint that's built into the SBS 2008 OS, it's not even used so I think changing to simple logging sounds like a good idea
Just a tiny comment here (sorry it's so late), shrinking transaction logs is generally not done in the big world of sql server, they get fixed to a size, with daily full backups and periodic transaction log backups. When a backup has completed, the log then is truncated internally or wraps around if there are active transactions, but only expands when it needs to, they never auto-truncate anymore because an unplanned expansion of the log file can be time consuming and usually coincides with periods of activity - when you don't want anything to get in the way of the user interaction. At the end of each full or incremental data backup, the transaction log backups can be discarded. I absolutely do not recommend the use of simple recovery model for a full production database, because the utilisation profile can (depending on workload) make the disks work HARDER yet you lose the potential to perform point-in-time recovery.
