My company looks after a server which is running SQL Server 2014 Standard Edition. The main application database on the server is replicated out to about 20 subscribers. The server has only been in operation for about a week and at the time of setting it up we moved the existing database and replication across from the old server. At that time we also changed the recovery model from Simple to Full. The performance of the server hasn't been particularly good so far, even though it has 24 logical processors and 32 GB of RAM. However it seems to be SQL Server that has been performing badly due to locks etc. according to the event logs. This morning we couldn't access any tables in Management Studio and after a period of time we decided to try restarting the service. The service did not seem to stop properly and we were forced to restart the server. After restarting the server everything seemed fine until we tried to expand out the 'Databases' tab in Management Studio. It just says 'expanding...' in the UI and after several minutes brings up a timeout error message. After clicking ok on this message we can browse all the system databases as normal but the main application database is not shown. One of the problems that has come to light is that the log file has grown extremely large (over 1 TB). This is a major problem now as you can imagine, since we cannot connect to the database to shrink the log file and no applications can connect to the database Connecting to the database using 'sqlcmd' doesn't work either. We can't 'open' the database. Look forward to hearing if anyone has come across this before and knows of a solution.
First, can you use some tool to do a log backup? That should truncate it for you and should not require you to open the database in the way you speaking of, so long as you can get the server up. It is a command to the server itself and you should be able to execute it from powershell if you want. If that does not resolve the issue, you may want to consider restoring from a backup, presuming you have one that is sufficiently recent. As a last ditch effort, and one that might be somewhat risky, consider forcing SQL Server to rebuild the log file. There are instructions for that [here]. As the article says, there is a risk of consequences going that path. :
Hi Philip, Replication can be a real pain as it can cause lots of locking on tables. It also has a habit of smashing the box to bits it isn't configured properly. There's a lot of good advice here:
http://msdn.microsoft.com/en-us/library/ms151791.aspx On top of this, depending on the type of replication you're using, there are a few other things that you could consider: Ensure that any foreign key constraints are defined as "NOT FOR REPLICATION" (although you should consider the data integrity implications of this first). Run metadata cleanup in defined windows, rather than allowing subscribers to kick it off.
http://msdn.microsoft.com/en-us/library/ms147338.aspx Don't try to synch too many subscribers at once and try to make synchronistions little and often. As always, there is an element of trial and error involved in getting to an optimal setup. Evidence based decision making is key to getting there.