asked
Large log file - cannot connect to database
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.
This web site runs on voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
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][1]. As the article says, there is a risk of consequences going that path. [1]:
http://sqlmag.com/blog/recovering-database-missing-transaction-log
Thank-you for your answer, I will perhaps try what you have suggested over the weekend.. Is there any particular tool you would recommend for backing up the log?
In addition to ensuring that you have log backups in place (along with full backups, in fact, make sure you work with the business to ensure you have Recovery Point and Recovery Time Objectives worked out for backups), you also need to ensure that replication is functioning for all twenty subscribers. If any of them is not receiving the replication (assuming it's transactional), that will hold the logs in place. Even log backups won't help in that case. Also, it sounds like you're just hitting general performance issues. It could be any number of things, so I'd suggest starting with basic monitoring and then determine what you may need to do from there to address the performance issues. For help, see [my book on the topic][1]. The [2014 version][2] will be out soon. [1]:
http://www.amazon.com/Server-2012-Query-Performance-Tuning-ebook/dp/B008E6HOIS/ref=sr_1_10?ie=UTF8&qid=1405943468&sr=8-10&keywords=fritchey [2]:
http://www.amazon.com/SQL-Server-Query-Performance-Tuning/dp/1430267437/ref=sr_1_3?ie=UTF8&qid=1405943468&sr=8-3&keywords=fritchey
Hi Grant Thank-you for your answer. It is actually the 2014 version I am working with.. the database was in recovery mode and this was limiting our ability to access it. When the recovery had eventually finished the file grew huger still but I have now been able to shrink the file right down and we are implementing a 'proper' back up routine with the business now to prevent the same thing happening. Many thanks for your help.
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.
17 People are following this question.
