0

I have a client that restarts his SQL Server instance to clear an application problem he is encountering.

I find this is very odd, I don't think you should ever restart the database in normal operation. I believe his transaction log is simply full and restarting may be counting as a checkpoint? Is this a valid theory? He's in simple recovery mode - what I can do to check if this is the case?

Do we know of any other reasons why restarting the SQL Server service would make a problem disappear for a few days?

marc_s
  • 8,932
  • 6
  • 45
  • 51
Philip
  • 137
  • 1
  • 6
  • You need to add specifics about the problem being "cleared". – Hannah Vernon May 01 '15 at 20:34
  • 1
    There's lots of reasons, what is the symptom of the problem? If the tlog was full in simple recovery mode and couldn't get more space you'd get an error, or it'd try to 0 out the new space added to the LDF file and then allocate it. During this time writes are blocked and you could see timeouts. – Ali Razeghi - AWS May 01 '15 at 20:37
  • It's a proprietary product - it has no errors in it's log file but it has a "Queue" that appears to be in a restart loop to the db. The connection itself does not fail but the queue in question simply runs bulk_inserts into the db. – Philip May 01 '15 at 21:15
  • You might just be bloating your T-log and reaching your disk space limit. Then the client restarts sql server and everything seems to be normal. That is not a right approach. You should look into the links that I mentioned and start narrowing your problem. This way you address the root cause and not just the symptom. – Kin Shah May 01 '15 at 21:51
  • @Kin, I agree - unfortunately we don't really support the database side of it, only the product. I'm just trying to see why the symptom goes away. Out of personal betterment - is there a list of all the times a checkpoint event is reached? I thought in simple mode as long as all transactions go in, we're good - as simple mode provides ACID compliance - not point-in-time recovery? – Philip May 01 '15 at 22:27

1 Answers1

4

I believe his transaction log is simply full and restarting may be counting as a checkpoint? Is this a valid theory?

No, this is not right. That is the most brutal thing you can do to sql server to just clear out the log (which makes things worse). If there is a long running transaction, then restarting your sql server will put that database in recovery until the rollback happens.

since you are running in simple recovery mode, ONLY a CHECKPOINT - manually or sql server running it at regular intervals will allow SQL Server to reuse the transaction log (the log will be marked for truncation).

Note that TRUNCATE_ONLY is not supported in sql server 2008 and up, but that was used to do a FORCE log truncation (sql server 2005 and earlier versions) - BACKUP LOG <database_name> WITH TRUNCATE_ONLY

Please read - Why Does the Transaction Log Keep Growing or Run Out of Space? (putting it in bold to emphasis to read it until you fully understand and digest the 2 best answers).

Also, make sure you have a good auto growth setting for your database.

Do we know of any other reasons why restarting the MS SQL service would make a problem disappear for a few days?

You should start with How to analyse SQL Server performance

Community
  • 1
Kin Shah
  • 62,225
  • 6
  • 120
  • 236
  • Kin, so - would restarting the server clear a transaction log y/n? At the moment they're reporting their transaction log is fine. I agree, restarting a database seems to be a harsh step, but what do you mean by "makes things worse"? – Philip May 01 '15 at 21:11
  • 1
    Well, it might "clear" the transaction log, In the sense that if there is an active explicit transaction that never has been committed the log would never be truncated as there is always an active transaction running select name,log_reuse_wait_desc from sys.databases and dbcc opentran would show this. Then when the server is restarted the active transaction is rolled back and the log truncated – Spörri May 01 '15 at 21:42