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
  • 9,052
  • 6
  • 46
  • 52
Philip
  • 137
  • 1
  • 6

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,545
  • 6
  • 124
  • 245