Post reply

SQL Server 2000 Recovery Model switching

  • February 16, 2006 at 7:40 am

    #112712

    I have a sql server 2000 database where the recovery model seems to switch spontaneously from Full to Simple.  I have found on these boards a possible cause involving someone running Spotlight on SQL Server using the database in question instead of the application's own database.  This server does have a demo/freeware version of SoSS installed on it.  Does anyone know of any other causes for this, something with backup software or the like?

  • February 16, 2006 at 8:44 pm

    #621533

    a job maybe? (aside from the obvious that someone is doing it manually), you may want to enable profiler on this server perhaps

  • February 16, 2006 at 11:35 pm

    #621556

    You really need to put the profiler trace. SQL server will not do this by its own 🙂

  • February 17, 2006 at 8:40 am

    #621697

    #1 question....what database? Some databases MUST be SIMPLE (for example, Master) and will switch back if you set them to FULL or anything else.

    -SQLBill

  • March 2, 2006 at 8:02 am

    #624323

    I have same problem too. Did you find out why now? Can you share with me? Thanks.

    We run SQL 2000, I have a job set for one of our production databases transaction log backup. But sometimes the job just failed because the database recovery model was changed from Full to Simple. I don't know why database was changed. Any help will be appreciated.

  • March 7, 2006 at 9:11 pm

    #625313

    If you are running Spotlight on SQL then it is likely that the way you are running it is the problem. Rather than target Spotlight directly at an existing database select when creating a new connection. This database will host working data whilst you are running the monitor. You will still be monitoring the desired SQL system by doing this. If you check your database that is reverting to the Simple recovery model for tables that begin with "qs" and "QS" (unless you know your database should have these tables) it means Spotlight is using this database to host the working data and sets the recovery model to Simple. These will be the Quest Spotlight working tables.

    So to summarise - don't connect Spotlight directly to the production databases, create a new database to host the working data.

  • March 8, 2006 at 12:22 am

    #625325

    Previous post got mangled on posting. The second sentence should read:

    Rather than target Spotlight directly at an existing database select NEW DATABASE when creating a new connection.

    Makes more sense now 🙂

  • March 8, 2006 at 12:28 pm

    #625508

    Thank you very much for your advice and suggestion. It is very useful. For spotlight sometimes we do use directly target one database. It's a problem. We are going to change Spotlight configuration. Hopefully it will solve our problem. Again, I really appreciated your help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply