We have a user database on which the recovery model keeps changing to Simple.
I go in every day and manually change it to Full, but nearly every night it
automatically gets set back to Simple. Why is this happening?
Note: I have read the previous threads which discuss this problem happening
with the msdb database. But in my case, it isn't msdb - it is a USER
database."CadeBryant" <CadeBryant@.discussions.microsoft.com> wrote in message
news:F90570AE-4AD3-4D2F-B53C-0928E6D6FD0B@.microsoft.com...
> We have a user database on which the recovery model keeps changing to
> Simple.
> I go in every day and manually change it to Full, but nearly every night
> it
> automatically gets set back to Simple. Why is this happening?
> Note: I have read the previous threads which discuss this problem
> happening
> with the msdb database. But in my case, it isn't msdb - it is a USER
> database.
Check for any maintenance jobs, especially any that do attempt fixes on the
databases.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||The only maintenance jobs are for the system DBs - nothing for the user DBs.
"Greg D. Moore (Strider)" wrote:
> "CadeBryant" <CadeBryant@.discussions.microsoft.com> wrote in message
> news:F90570AE-4AD3-4D2F-B53C-0928E6D6FD0B@.microsoft.com...
> > We have a user database on which the recovery model keeps changing to
> > Simple.
> > I go in every day and manually change it to Full, but nearly every night
> > it
> > automatically gets set back to Simple. Why is this happening?
> >
> > Note: I have read the previous threads which discuss this problem
> > happening
> > with the msdb database. But in my case, it isn't msdb - it is a USER
> > database.
>
> Check for any maintenance jobs, especially any that do attempt fixes on the
> databases.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||I suggest you use a server side Profiler trace to catch commands (use filter in the trace) such as
sp_dboption and ALTER DATABASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CadeBryant" <CadeBryant@.discussions.microsoft.com> wrote in message
news:27A46AE4-5BD3-4E09-A433-002A030782DB@.microsoft.com...
> The only maintenance jobs are for the system DBs - nothing for the user DBs.
> "Greg D. Moore (Strider)" wrote:
>> "CadeBryant" <CadeBryant@.discussions.microsoft.com> wrote in message
>> news:F90570AE-4AD3-4D2F-B53C-0928E6D6FD0B@.microsoft.com...
>> > We have a user database on which the recovery model keeps changing to
>> > Simple.
>> > I go in every day and manually change it to Full, but nearly every night
>> > it
>> > automatically gets set back to Simple. Why is this happening?
>> >
>> > Note: I have read the previous threads which discuss this problem
>> > happening
>> > with the msdb database. But in my case, it isn't msdb - it is a USER
>> > database.
>>
>> Check for any maintenance jobs, especially any that do attempt fixes on the
>> databases.
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting
>> sql (at) greenms.com http://www.greenms.com
>>
Showing posts with label changing. Show all posts
Showing posts with label changing. Show all posts
Wednesday, March 28, 2012
Recovery model on user db automatically changes from Full to Simpl
We have a user database on which the recovery model keeps changing to Simple.
I go in every day and manually change it to Full, but nearly every night it
automatically gets set back to Simple. Why is this happening?
Note: I have read the previous threads which discuss this problem happening
with the msdb database. But in my case, it isn't msdb - it is a USER
database.
"CadeBryant" <CadeBryant@.discussions.microsoft.com> wrote in message
news:F90570AE-4AD3-4D2F-B53C-0928E6D6FD0B@.microsoft.com...
> We have a user database on which the recovery model keeps changing to
> Simple.
> I go in every day and manually change it to Full, but nearly every night
> it
> automatically gets set back to Simple. Why is this happening?
> Note: I have read the previous threads which discuss this problem
> happening
> with the msdb database. But in my case, it isn't msdb - it is a USER
> database.
Check for any maintenance jobs, especially any that do attempt fixes on the
databases.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||The only maintenance jobs are for the system DBs - nothing for the user DBs.
"Greg D. Moore (Strider)" wrote:
> "CadeBryant" <CadeBryant@.discussions.microsoft.com> wrote in message
> news:F90570AE-4AD3-4D2F-B53C-0928E6D6FD0B@.microsoft.com...
>
> Check for any maintenance jobs, especially any that do attempt fixes on the
> databases.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>
I go in every day and manually change it to Full, but nearly every night it
automatically gets set back to Simple. Why is this happening?
Note: I have read the previous threads which discuss this problem happening
with the msdb database. But in my case, it isn't msdb - it is a USER
database.
"CadeBryant" <CadeBryant@.discussions.microsoft.com> wrote in message
news:F90570AE-4AD3-4D2F-B53C-0928E6D6FD0B@.microsoft.com...
> We have a user database on which the recovery model keeps changing to
> Simple.
> I go in every day and manually change it to Full, but nearly every night
> it
> automatically gets set back to Simple. Why is this happening?
> Note: I have read the previous threads which discuss this problem
> happening
> with the msdb database. But in my case, it isn't msdb - it is a USER
> database.
Check for any maintenance jobs, especially any that do attempt fixes on the
databases.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||The only maintenance jobs are for the system DBs - nothing for the user DBs.
"Greg D. Moore (Strider)" wrote:
> "CadeBryant" <CadeBryant@.discussions.microsoft.com> wrote in message
> news:F90570AE-4AD3-4D2F-B53C-0928E6D6FD0B@.microsoft.com...
>
> Check for any maintenance jobs, especially any that do attempt fixes on the
> databases.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>
Recovery model on user db automatically changes from Full to Simpl
We have a user database on which the recovery model keeps changing to Simple
.
I go in every day and manually change it to Full, but nearly every night it
automatically gets set back to Simple. Why is this happening?
Note: I have read the previous threads which discuss this problem happening
with the msdb database. But in my case, it isn't msdb - it is a USER
database."CadeBryant" <CadeBryant@.discussions.microsoft.com> wrote in message
news:F90570AE-4AD3-4D2F-B53C-0928E6D6FD0B@.microsoft.com...
> We have a user database on which the recovery model keeps changing to
> Simple.
> I go in every day and manually change it to Full, but nearly every night
> it
> automatically gets set back to Simple. Why is this happening?
> Note: I have read the previous threads which discuss this problem
> happening
> with the msdb database. But in my case, it isn't msdb - it is a USER
> database.
Check for any maintenance jobs, especially any that do attempt fixes on the
databases.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
.
I go in every day and manually change it to Full, but nearly every night it
automatically gets set back to Simple. Why is this happening?
Note: I have read the previous threads which discuss this problem happening
with the msdb database. But in my case, it isn't msdb - it is a USER
database."CadeBryant" <CadeBryant@.discussions.microsoft.com> wrote in message
news:F90570AE-4AD3-4D2F-B53C-0928E6D6FD0B@.microsoft.com...
> We have a user database on which the recovery model keeps changing to
> Simple.
> I go in every day and manually change it to Full, but nearly every night
> it
> automatically gets set back to Simple. Why is this happening?
> Note: I have read the previous threads which discuss this problem
> happening
> with the msdb database. But in my case, it isn't msdb - it is a USER
> database.
Check for any maintenance jobs, especially any that do attempt fixes on the
databases.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Recovery Model Changing
Hello,
We have numerous SQL Servers that we backup using Veritas Backup Exec with
the SQL Server Agents. We have changed all of our database backup models to
FULL. We backup the transaction logs a few times through the day, and do a
full database backup once a night.
A few of our servers, the msdb database specifically, switch their recovery
mode to simple every once in a while. There doesnt seem to be a time pattern
we can follow, but it does seem to occur after one of our transaction log
backups.
Any ideas if this is a known problem with the msdb database, or if this is
caused by Backup Exec or by SQL Server?
Thanks,
JBaileyThat is the design of SQL Server and the Master and MSDB databases. There
is no real reason to take log backups of these when a Full backup can be
done in a matter of seconds.
--
Andrew J. Kelly
SQL Server MVP
"JBailey" <abc@.123.com> wrote in message
news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> Hello,
> We have numerous SQL Servers that we backup using Veritas Backup Exec with
> the SQL Server Agents. We have changed all of our database backup models
to
> FULL. We backup the transaction logs a few times through the day, and do a
> full database backup once a night.
> A few of our servers, the msdb database specifically, switch their
recovery
> mode to simple every once in a while. There doesnt seem to be a time
pattern
> we can follow, but it does seem to occur after one of our transaction log
> backups.
> Any ideas if this is a known problem with the msdb database, or if this is
> caused by Backup Exec or by SQL Server?
> Thanks,
> JBailey
>|||So do the Master and MSDB databases change infrequently enough that
transaction logs backups are unnecessary?
Thanks,
JBailey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e3b06PzzDHA.3220@.tk2msftngp13.phx.gbl...
> That is the design of SQL Server and the Master and MSDB databases. There
> is no real reason to take log backups of these when a Full backup can be
> done in a matter of seconds.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "JBailey" <abc@.123.com> wrote in message
> news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > We have numerous SQL Servers that we backup using Veritas Backup Exec
with
> > the SQL Server Agents. We have changed all of our database backup models
> to
> > FULL. We backup the transaction logs a few times through the day, and do
a
> > full database backup once a night.
> >
> > A few of our servers, the msdb database specifically, switch their
> recovery
> > mode to simple every once in a while. There doesnt seem to be a time
> pattern
> > we can follow, but it does seem to occur after one of our transaction
log
> > backups.
> >
> > Any ideas if this is a known problem with the msdb database, or if this
is
> > caused by Backup Exec or by SQL Server?
> >
> > Thanks,
> > JBailey
> >
> >
>|||Master rarely changes unless you add a new db or Login. MSDB stores a lot
of history for things like scheduled jobs, backups etc. so it can have
regular changes to it. A lot of people aren't worried as much about loosing
a few hours of history but it is up to each individual to determine that.
What I was getting at though is that to backup Master or MSDB fully usually
only takes a second or two so why bother to schedule log backups. If you
want to back up the db's every hour then just do a full backup. The only
thing it doesn't give you is point in time recovery but again that is rarely
what someone is seeking on these dbs.
--
Andrew J. Kelly
SQL Server MVP
"JBailey" <abc@.123.com> wrote in message
news:udWi7N6zDHA.2396@.TK2MSFTNGP09.phx.gbl...
> So do the Master and MSDB databases change infrequently enough that
> transaction logs backups are unnecessary?
> Thanks,
> JBailey
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e3b06PzzDHA.3220@.tk2msftngp13.phx.gbl...
> > That is the design of SQL Server and the Master and MSDB databases.
There
> > is no real reason to take log backups of these when a Full backup can be
> > done in a matter of seconds.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "JBailey" <abc@.123.com> wrote in message
> > news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > We have numerous SQL Servers that we backup using Veritas Backup Exec
> with
> > > the SQL Server Agents. We have changed all of our database backup
models
> > to
> > > FULL. We backup the transaction logs a few times through the day, and
do
> a
> > > full database backup once a night.
> > >
> > > A few of our servers, the msdb database specifically, switch their
> > recovery
> > > mode to simple every once in a while. There doesnt seem to be a time
> > pattern
> > > we can follow, but it does seem to occur after one of our transaction
> log
> > > backups.
> > >
> > > Any ideas if this is a known problem with the msdb database, or if
this
> is
> > > caused by Backup Exec or by SQL Server?
> > >
> > > Thanks,
> > > JBailey
> > >
> > >
> >
> >
>|||Understood. Thanks for the insight.
JBailey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:urOvXl6zDHA.1688@.TK2MSFTNGP10.phx.gbl...
> Master rarely changes unless you add a new db or Login. MSDB stores a lot
> of history for things like scheduled jobs, backups etc. so it can have
> regular changes to it. A lot of people aren't worried as much about
loosing
> a few hours of history but it is up to each individual to determine that.
> What I was getting at though is that to backup Master or MSDB fully
usually
> only takes a second or two so why bother to schedule log backups. If you
> want to back up the db's every hour then just do a full backup. The only
> thing it doesn't give you is point in time recovery but again that is
rarely
> what someone is seeking on these dbs.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "JBailey" <abc@.123.com> wrote in message
> news:udWi7N6zDHA.2396@.TK2MSFTNGP09.phx.gbl...
> > So do the Master and MSDB databases change infrequently enough that
> > transaction logs backups are unnecessary?
> >
> > Thanks,
> > JBailey
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:e3b06PzzDHA.3220@.tk2msftngp13.phx.gbl...
> > > That is the design of SQL Server and the Master and MSDB databases.
> There
> > > is no real reason to take log backups of these when a Full backup can
be
> > > done in a matter of seconds.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "JBailey" <abc@.123.com> wrote in message
> > > news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> > > > Hello,
> > > >
> > > > We have numerous SQL Servers that we backup using Veritas Backup
Exec
> > with
> > > > the SQL Server Agents. We have changed all of our database backup
> models
> > > to
> > > > FULL. We backup the transaction logs a few times through the day,
and
> do
> > a
> > > > full database backup once a night.
> > > >
> > > > A few of our servers, the msdb database specifically, switch their
> > > recovery
> > > > mode to simple every once in a while. There doesnt seem to be a time
> > > pattern
> > > > we can follow, but it does seem to occur after one of our
transaction
> > log
> > > > backups.
> > > >
> > > > Any ideas if this is a known problem with the msdb database, or if
> this
> > is
> > > > caused by Backup Exec or by SQL Server?
> > > >
> > > > Thanks,
> > > > JBailey
> > > >
> > > >
> > >
> > >
> >
> >
>
We have numerous SQL Servers that we backup using Veritas Backup Exec with
the SQL Server Agents. We have changed all of our database backup models to
FULL. We backup the transaction logs a few times through the day, and do a
full database backup once a night.
A few of our servers, the msdb database specifically, switch their recovery
mode to simple every once in a while. There doesnt seem to be a time pattern
we can follow, but it does seem to occur after one of our transaction log
backups.
Any ideas if this is a known problem with the msdb database, or if this is
caused by Backup Exec or by SQL Server?
Thanks,
JBaileyThat is the design of SQL Server and the Master and MSDB databases. There
is no real reason to take log backups of these when a Full backup can be
done in a matter of seconds.
--
Andrew J. Kelly
SQL Server MVP
"JBailey" <abc@.123.com> wrote in message
news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> Hello,
> We have numerous SQL Servers that we backup using Veritas Backup Exec with
> the SQL Server Agents. We have changed all of our database backup models
to
> FULL. We backup the transaction logs a few times through the day, and do a
> full database backup once a night.
> A few of our servers, the msdb database specifically, switch their
recovery
> mode to simple every once in a while. There doesnt seem to be a time
pattern
> we can follow, but it does seem to occur after one of our transaction log
> backups.
> Any ideas if this is a known problem with the msdb database, or if this is
> caused by Backup Exec or by SQL Server?
> Thanks,
> JBailey
>|||So do the Master and MSDB databases change infrequently enough that
transaction logs backups are unnecessary?
Thanks,
JBailey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e3b06PzzDHA.3220@.tk2msftngp13.phx.gbl...
> That is the design of SQL Server and the Master and MSDB databases. There
> is no real reason to take log backups of these when a Full backup can be
> done in a matter of seconds.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "JBailey" <abc@.123.com> wrote in message
> news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > We have numerous SQL Servers that we backup using Veritas Backup Exec
with
> > the SQL Server Agents. We have changed all of our database backup models
> to
> > FULL. We backup the transaction logs a few times through the day, and do
a
> > full database backup once a night.
> >
> > A few of our servers, the msdb database specifically, switch their
> recovery
> > mode to simple every once in a while. There doesnt seem to be a time
> pattern
> > we can follow, but it does seem to occur after one of our transaction
log
> > backups.
> >
> > Any ideas if this is a known problem with the msdb database, or if this
is
> > caused by Backup Exec or by SQL Server?
> >
> > Thanks,
> > JBailey
> >
> >
>|||Master rarely changes unless you add a new db or Login. MSDB stores a lot
of history for things like scheduled jobs, backups etc. so it can have
regular changes to it. A lot of people aren't worried as much about loosing
a few hours of history but it is up to each individual to determine that.
What I was getting at though is that to backup Master or MSDB fully usually
only takes a second or two so why bother to schedule log backups. If you
want to back up the db's every hour then just do a full backup. The only
thing it doesn't give you is point in time recovery but again that is rarely
what someone is seeking on these dbs.
--
Andrew J. Kelly
SQL Server MVP
"JBailey" <abc@.123.com> wrote in message
news:udWi7N6zDHA.2396@.TK2MSFTNGP09.phx.gbl...
> So do the Master and MSDB databases change infrequently enough that
> transaction logs backups are unnecessary?
> Thanks,
> JBailey
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e3b06PzzDHA.3220@.tk2msftngp13.phx.gbl...
> > That is the design of SQL Server and the Master and MSDB databases.
There
> > is no real reason to take log backups of these when a Full backup can be
> > done in a matter of seconds.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "JBailey" <abc@.123.com> wrote in message
> > news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > We have numerous SQL Servers that we backup using Veritas Backup Exec
> with
> > > the SQL Server Agents. We have changed all of our database backup
models
> > to
> > > FULL. We backup the transaction logs a few times through the day, and
do
> a
> > > full database backup once a night.
> > >
> > > A few of our servers, the msdb database specifically, switch their
> > recovery
> > > mode to simple every once in a while. There doesnt seem to be a time
> > pattern
> > > we can follow, but it does seem to occur after one of our transaction
> log
> > > backups.
> > >
> > > Any ideas if this is a known problem with the msdb database, or if
this
> is
> > > caused by Backup Exec or by SQL Server?
> > >
> > > Thanks,
> > > JBailey
> > >
> > >
> >
> >
>|||Understood. Thanks for the insight.
JBailey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:urOvXl6zDHA.1688@.TK2MSFTNGP10.phx.gbl...
> Master rarely changes unless you add a new db or Login. MSDB stores a lot
> of history for things like scheduled jobs, backups etc. so it can have
> regular changes to it. A lot of people aren't worried as much about
loosing
> a few hours of history but it is up to each individual to determine that.
> What I was getting at though is that to backup Master or MSDB fully
usually
> only takes a second or two so why bother to schedule log backups. If you
> want to back up the db's every hour then just do a full backup. The only
> thing it doesn't give you is point in time recovery but again that is
rarely
> what someone is seeking on these dbs.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "JBailey" <abc@.123.com> wrote in message
> news:udWi7N6zDHA.2396@.TK2MSFTNGP09.phx.gbl...
> > So do the Master and MSDB databases change infrequently enough that
> > transaction logs backups are unnecessary?
> >
> > Thanks,
> > JBailey
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:e3b06PzzDHA.3220@.tk2msftngp13.phx.gbl...
> > > That is the design of SQL Server and the Master and MSDB databases.
> There
> > > is no real reason to take log backups of these when a Full backup can
be
> > > done in a matter of seconds.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "JBailey" <abc@.123.com> wrote in message
> > > news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> > > > Hello,
> > > >
> > > > We have numerous SQL Servers that we backup using Veritas Backup
Exec
> > with
> > > > the SQL Server Agents. We have changed all of our database backup
> models
> > > to
> > > > FULL. We backup the transaction logs a few times through the day,
and
> do
> > a
> > > > full database backup once a night.
> > > >
> > > > A few of our servers, the msdb database specifically, switch their
> > > recovery
> > > > mode to simple every once in a while. There doesnt seem to be a time
> > > pattern
> > > > we can follow, but it does seem to occur after one of our
transaction
> > log
> > > > backups.
> > > >
> > > > Any ideas if this is a known problem with the msdb database, or if
> this
> > is
> > > > caused by Backup Exec or by SQL Server?
> > > >
> > > > Thanks,
> > > > JBailey
> > > >
> > > >
> > >
> > >
> >
> >
>
Recovery mode 'magically' changing
I have a database that is the source in a logshipping pair (SQL2k,
SP3a) that is 'magically' changing from Full Recovery to Simple
Recovery. Is there a trace flag I can turn on to catch this change and
where it is being triggered from?
Obviously this is wrecking havoc on my log shipping.
thanks
Meredith RyanYou have it in the errorlog file. You can also run a profiler trace with appropriate events and
filter to catch it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
>I have a database that is the source in a logshipping pair (SQL2k,
> SP3a) that is 'magically' changing from Full Recovery to Simple
> Recovery. Is there a trace flag I can turn on to catch this change and
> where it is being triggered from?
> Obviously this is wrecking havoc on my log shipping.
> thanks
> Meredith Ryan
>|||This isn't the master database is it?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
>I have a database that is the source in a logshipping pair (SQL2k,
> SP3a) that is 'magically' changing from Full Recovery to Simple
> Recovery. Is there a trace flag I can turn on to catch this change and
> where it is being triggered from?
> Obviously this is wrecking havoc on my log shipping.
> thanks
> Meredith Ryan
>|||No,
It is a user DB. Looks like two of my user DB's are changing. I know
that it was changed last night between 6:30pm and 9pm - After I changed
it to Full earlier in the day.
-Meredith
Roger Wolter[MSFT] wrote:
> This isn't the master database is it?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
> >I have a database that is the source in a logshipping pair (SQL2k,
> > SP3a) that is 'magically' changing from Full Recovery to Simple
> > Recovery. Is there a trace flag I can turn on to catch this change and
> > where it is being triggered from?
> > Obviously this is wrecking havoc on my log shipping.
> >
> > thanks
> > Meredith Ryan
> >|||I don't see anything with value in the Error Log. I do see the Error:
15457... message, but with the error log only showing me a SPID I
can't track down who is making the change.
Also,. All occurances of Error 15457 show that the Advanced Config
changed from 1 to 1. Wouldn't that signify no change at all?
Tibor Karaszi wrote:
> You have it in the errorlog file. You can also run a profiler trace with appropriate events and
> filter to catch it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
> >I have a database that is the source in a logshipping pair (SQL2k,
> > SP3a) that is 'magically' changing from Full Recovery to Simple
> > Recovery. Is there a trace flag I can turn on to catch this change and
> > where it is being triggered from?
> > Obviously this is wrecking havoc on my log shipping.
> >
> > thanks
> > Meredith Ryan
> >|||> Also,. All occurances of Error 15457 show that the Advanced Config
> changed from 1 to 1. Wouldn't that signify no change at all?
That is just EM setting advanced option when you show properties on a server. Not related to this
problem.
Perhaps 2000 doesn't show this in eventlog, I looked at 2005. I'd do a profiler trace to see who it
is.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149868370.743087.299980@.u72g2000cwu.googlegroups.com...
>I don't see anything with value in the Error Log. I do see the Error:
> 15457... message, but with the error log only showing me a SPID I
> can't track down who is making the change.
> Also,. All occurances of Error 15457 show that the Advanced Config
> changed from 1 to 1. Wouldn't that signify no change at all?
> Tibor Karaszi wrote:
>> You have it in the errorlog file. You can also run a profiler trace with appropriate events and
>> filter to catch it.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "mryan" <coffegrl@.gmail.com> wrote in message
>> news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
>> >I have a database that is the source in a logshipping pair (SQL2k,
>> > SP3a) that is 'magically' changing from Full Recovery to Simple
>> > Recovery. Is there a trace flag I can turn on to catch this change and
>> > where it is being triggered from?
>> > Obviously this is wrecking havoc on my log shipping.
>> >
>> > thanks
>> > Meredith Ryan
>> >
>
SP3a) that is 'magically' changing from Full Recovery to Simple
Recovery. Is there a trace flag I can turn on to catch this change and
where it is being triggered from?
Obviously this is wrecking havoc on my log shipping.
thanks
Meredith RyanYou have it in the errorlog file. You can also run a profiler trace with appropriate events and
filter to catch it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
>I have a database that is the source in a logshipping pair (SQL2k,
> SP3a) that is 'magically' changing from Full Recovery to Simple
> Recovery. Is there a trace flag I can turn on to catch this change and
> where it is being triggered from?
> Obviously this is wrecking havoc on my log shipping.
> thanks
> Meredith Ryan
>|||This isn't the master database is it?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
>I have a database that is the source in a logshipping pair (SQL2k,
> SP3a) that is 'magically' changing from Full Recovery to Simple
> Recovery. Is there a trace flag I can turn on to catch this change and
> where it is being triggered from?
> Obviously this is wrecking havoc on my log shipping.
> thanks
> Meredith Ryan
>|||No,
It is a user DB. Looks like two of my user DB's are changing. I know
that it was changed last night between 6:30pm and 9pm - After I changed
it to Full earlier in the day.
-Meredith
Roger Wolter[MSFT] wrote:
> This isn't the master database is it?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
> >I have a database that is the source in a logshipping pair (SQL2k,
> > SP3a) that is 'magically' changing from Full Recovery to Simple
> > Recovery. Is there a trace flag I can turn on to catch this change and
> > where it is being triggered from?
> > Obviously this is wrecking havoc on my log shipping.
> >
> > thanks
> > Meredith Ryan
> >|||I don't see anything with value in the Error Log. I do see the Error:
15457... message, but with the error log only showing me a SPID I
can't track down who is making the change.
Also,. All occurances of Error 15457 show that the Advanced Config
changed from 1 to 1. Wouldn't that signify no change at all?
Tibor Karaszi wrote:
> You have it in the errorlog file. You can also run a profiler trace with appropriate events and
> filter to catch it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
> >I have a database that is the source in a logshipping pair (SQL2k,
> > SP3a) that is 'magically' changing from Full Recovery to Simple
> > Recovery. Is there a trace flag I can turn on to catch this change and
> > where it is being triggered from?
> > Obviously this is wrecking havoc on my log shipping.
> >
> > thanks
> > Meredith Ryan
> >|||> Also,. All occurances of Error 15457 show that the Advanced Config
> changed from 1 to 1. Wouldn't that signify no change at all?
That is just EM setting advanced option when you show properties on a server. Not related to this
problem.
Perhaps 2000 doesn't show this in eventlog, I looked at 2005. I'd do a profiler trace to see who it
is.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149868370.743087.299980@.u72g2000cwu.googlegroups.com...
>I don't see anything with value in the Error Log. I do see the Error:
> 15457... message, but with the error log only showing me a SPID I
> can't track down who is making the change.
> Also,. All occurances of Error 15457 show that the Advanced Config
> changed from 1 to 1. Wouldn't that signify no change at all?
> Tibor Karaszi wrote:
>> You have it in the errorlog file. You can also run a profiler trace with appropriate events and
>> filter to catch it.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "mryan" <coffegrl@.gmail.com> wrote in message
>> news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
>> >I have a database that is the source in a logshipping pair (SQL2k,
>> > SP3a) that is 'magically' changing from Full Recovery to Simple
>> > Recovery. Is there a trace flag I can turn on to catch this change and
>> > where it is being triggered from?
>> > Obviously this is wrecking havoc on my log shipping.
>> >
>> > thanks
>> > Meredith Ryan
>> >
>
Recovery mode 'magically' changing
I have a database that is the source in a logshipping pair (SQL2k,
SP3a) that is 'magically' changing from Full Recovery to Simple
Recovery. Is there a trace flag I can turn on to catch this change and
where it is being triggered from?
Obviously this is wrecking havoc on my log shipping.
thanks
Meredith RyanYou have it in the errorlog file. You can also run a profiler trace with app
ropriate events and
filter to catch it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
>I have a database that is the source in a logshipping pair (SQL2k,
> SP3a) that is 'magically' changing from Full Recovery to Simple
> Recovery. Is there a trace flag I can turn on to catch this change and
> where it is being triggered from?
> Obviously this is wrecking havoc on my log shipping.
> thanks
> Meredith Ryan
>|||This isn't the master database is it?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
>I have a database that is the source in a logshipping pair (SQL2k,
> SP3a) that is 'magically' changing from Full Recovery to Simple
> Recovery. Is there a trace flag I can turn on to catch this change and
> where it is being triggered from?
> Obviously this is wrecking havoc on my log shipping.
> thanks
> Meredith Ryan
>|||No,
It is a user DB. Looks like two of my user DB's are changing. I know
that it was changed last night between 6:30pm and 9pm - After I changed
it to Full earlier in the day.
-Meredith
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> This isn't the master database is it?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...|||I don't see anything with value in the Error Log. I do see the Error:
15457... message, but with the error log only showing me a SPID I
can't track down who is making the change.
Also,. All occurances of Error 15457 show that the Advanced Config
changed from 1 to 1. Wouldn't that signify no change at all?
Tibor Karaszi wrote:[vbcol=seagreen]
> You have it in the errorlog file. You can also run a profiler trace with a
ppropriate events and
> filter to catch it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...|||> Also,. All occurances of Error 15457 show that the Advanced Config
> changed from 1 to 1. Wouldn't that signify no change at all?
That is just EM setting advanced option when you show properties on a server
. Not related to this
problem.
Perhaps 2000 doesn't show this in eventlog, I looked at 2005. I'd do a profi
ler trace to see who it
is.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149868370.743087.299980@.u72g2000cwu.googlegroups.com...
>I don't see anything with value in the Error Log. I do see the Error:
> 15457... message, but with the error log only showing me a SPID I
> can't track down who is making the change.
> Also,. All occurances of Error 15457 show that the Advanced Config
> changed from 1 to 1. Wouldn't that signify no change at all?
> Tibor Karaszi wrote:
>
SP3a) that is 'magically' changing from Full Recovery to Simple
Recovery. Is there a trace flag I can turn on to catch this change and
where it is being triggered from?
Obviously this is wrecking havoc on my log shipping.
thanks
Meredith RyanYou have it in the errorlog file. You can also run a profiler trace with app
ropriate events and
filter to catch it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
>I have a database that is the source in a logshipping pair (SQL2k,
> SP3a) that is 'magically' changing from Full Recovery to Simple
> Recovery. Is there a trace flag I can turn on to catch this change and
> where it is being triggered from?
> Obviously this is wrecking havoc on my log shipping.
> thanks
> Meredith Ryan
>|||This isn't the master database is it?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...
>I have a database that is the source in a logshipping pair (SQL2k,
> SP3a) that is 'magically' changing from Full Recovery to Simple
> Recovery. Is there a trace flag I can turn on to catch this change and
> where it is being triggered from?
> Obviously this is wrecking havoc on my log shipping.
> thanks
> Meredith Ryan
>|||No,
It is a user DB. Looks like two of my user DB's are changing. I know
that it was changed last night between 6:30pm and 9pm - After I changed
it to Full earlier in the day.
-Meredith
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> This isn't the master database is it?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...|||I don't see anything with value in the Error Log. I do see the Error:
15457... message, but with the error log only showing me a SPID I
can't track down who is making the change.
Also,. All occurances of Error 15457 show that the Advanced Config
changed from 1 to 1. Wouldn't that signify no change at all?
Tibor Karaszi wrote:[vbcol=seagreen]
> You have it in the errorlog file. You can also run a profiler trace with a
ppropriate events and
> filter to catch it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1149807873.715008.262910@.i40g2000cwc.googlegroups.com...|||> Also,. All occurances of Error 15457 show that the Advanced Config
> changed from 1 to 1. Wouldn't that signify no change at all?
That is just EM setting advanced option when you show properties on a server
. Not related to this
problem.
Perhaps 2000 doesn't show this in eventlog, I looked at 2005. I'd do a profi
ler trace to see who it
is.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mryan" <coffegrl@.gmail.com> wrote in message
news:1149868370.743087.299980@.u72g2000cwu.googlegroups.com...
>I don't see anything with value in the Error Log. I do see the Error:
> 15457... message, but with the error log only showing me a SPID I
> can't track down who is making the change.
> Also,. All occurances of Error 15457 show that the Advanced Config
> changed from 1 to 1. Wouldn't that signify no change at all?
> Tibor Karaszi wrote:
>
Recovery Mode is changing
SQL Server 2000. We set the recovery mode for the msdb to full. When we check it a few days later, recovery mode set itself to simple. No set pattern, no consistency as far as we can see.Originally posted by littleccguy
SQL Server 2000. We set the recovery mode for the msdb to full. When we check it a few days later, recovery mode set itself to simple. No set pattern, no consistency as far as we can see.
Just curious...does it make coffee for you too when you get in in the morning?
Seriously doubt it...
How many people have access to the box?|||I have to agree with Brett ! There has to be someone doing it. Or try running a Tace on MSDB for a week (you can select filters and options to keep the size to minimum)
SQL Server 2000. We set the recovery mode for the msdb to full. When we check it a few days later, recovery mode set itself to simple. No set pattern, no consistency as far as we can see.
Just curious...does it make coffee for you too when you get in in the morning?
Seriously doubt it...
How many people have access to the box?|||I have to agree with Brett ! There has to be someone doing it. Or try running a Tace on MSDB for a week (you can select filters and options to keep the size to minimum)
Subscribe to:
Posts (Atom)