Post reply

Any logs store this!!!

  • August 23, 2005 at 8:17 am

    #110597

    HI i have set the recovery model of all(80) db on dev server to simple.

    After 2 days i checked and came to know that the recovery model is full.

    Can anybody know any logs in sql server which logs the recovery model

    change i.e when this model is changed and who changed it.

    Any idea is also welcome

  • August 24, 2005 at 4:11 am

    #584522

    Hi Pavan

    No logs store this information I'm afraid. As it's a Development Server I guess several people have sysadmin access which makes your detective work harder.

    2 suggestions though:

    (i) Save time by using a SQL script to switch all the dbs back to simple recovery. You can generate the script from master..sysdatabases then cut & paste the result into a query window and run it.

    Here's a generation script:

    SELECT 'ALTER DATABASE ' +  name +   ' SET  RECOVERY SIMPLE'

    from master..sysdatabases

    where name not in ('master', 'tempdb', 'msdb', 'distribution')

    order by name

    The result set includes the 'model' db, setting this to simple means all new databases will be created as simple recovery.

    (ii) Use SQL Profiler to audit changes to the sysdatabases table in master so you can see who is the author of future changes.

    Cheers

     

     

  • August 24, 2005 at 4:18 am

    #584523

    Thanks, but I already did those 2 things

  • August 25, 2005 at 7:15 am

    #584943

    This may not help much but I recall reading somewhere that specific DTS jobs may change the recovery model. I wish I could offer more or at least be more confident that I'm not spewing nonsense but that situation rings a bell...

Viewing 4 posts - 1 through 3 (of 3 total)

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