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.

  • If you're not going to manage the transaction log (backup and truncate) then you should set the recovery model from Full back to Simple. – joeqwerty Jul 18 '14 at 16:13
  • Definitely make sure you're including (nolock) in your queries as well, when there's a chance you'll need them. You want your queries queueing up instead of blocking others; blocking alerts can cause havok on performance. – jski Jul 18 '14 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.