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
> > > >
> > > >
> > >
> > >
> >
> >
>

Recovering from resource problems

Hi,
Yesterday we had some 701 resource messages on one of our production
servers. This was followed by a number of Downgrading backup log buffers
from 1024K to 64K messages in our backup jobs. This is an SQL2005 build 3159
server on W2K3 SP2. We had similar problems last year with an SQL2000 server
and we found that a reboot or stop/start SQL appeared the only way to stop
the problem. We did this last night on this server.
Is this the best way on SQL2005 or will it recover from its resource
problems without a reboot or stop/start SQL?
Thanks
ChrisIt is hard to say if this would address the specific problem but this is
about as close as you can get w/o a restart:
dbcc dropcleanbuffers
DBCC FREESYSTEMCACHE ( 'ALL' ) WITH MARK_IN_USE_FOR_REMOVAL
exec sp_msforeachdb 'alter ? set single_user with rollback immediate'
exec sp_msforeachdb 'alter ? set multi_user with rollback immediate'
Be careful. I'd use this like a hail mary with 2 seconds left.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:O$Ta6z0jIHA.5084@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Yesterday we had some 701 resource messages on one of our production
> servers. This was followed by a number of Downgrading backup log buffers
> from 1024K to 64K messages in our backup jobs. This is an SQL2005 build
> 3159 server on W2K3 SP2. We had similar problems last year with an SQL2000
> server and we found that a reboot or stop/start SQL appeared the only way
> to stop the problem. We did this last night on this server.
> Is this the best way on SQL2005 or will it recover from its resource
> problems without a reboot or stop/start SQL?
> Thanks
> Chris
>|||That is pretty harsh!!! I was hoping that SQL2005 was more robust than
SQL2000.
Thanks
Chris
"Jason Massie" <jason**R3move**@.statisticsio.com> wrote in message
news:ud9ZVO1jIHA.4940@.TK2MSFTNGP02.phx.gbl...
> It is hard to say if this would address the specific problem but this is
> about as close as you can get w/o a restart:
> dbcc dropcleanbuffers
> DBCC FREESYSTEMCACHE ( 'ALL' ) WITH MARK_IN_USE_FOR_REMOVAL
> exec sp_msforeachdb 'alter ? set single_user with rollback immediate'
> exec sp_msforeachdb 'alter ? set multi_user with rollback immediate'
> Be careful. I'd use this like a hail mary with 2 seconds left.
> --
> Jason Massie
> Web: http://statisticsio.com
> RSS: http://feeds.feedburner.com/statisticsio
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:O$Ta6z0jIHA.5084@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Yesterday we had some 701 resource messages on one of our production
>> servers. This was followed by a number of Downgrading backup log buffers
>> from 1024K to 64K messages in our backup jobs. This is an SQL2005 build
>> 3159 server on W2K3 SP2. We had similar problems last year with an
>> SQL2000 server and we found that a reboot or stop/start SQL appeared the
>> only way to stop the problem. We did this last night on this server.
>> Is this the best way on SQL2005 or will it recover from its resource
>> problems without a reboot or stop/start SQL?
>> Thanks
>> Chris
>

Records lost on database

One of our clients set up merged replication between two SQL Servers.
Replication occurs every ten minutes. A problem occurs when they capture
employee information through a web application affecting several tables that
the information in some of the tables "disappears". What would be the best
way for me to investigate what the problem could be?
It depends on how the info was added. If it is a bulk insert, or a fast load
from DTS, then the replication triggers won't fire by default, and the data
will be left on one side only. Is that what you're seeing? If it is instead
that data is successfully added then disappears, it might be that you have
compensating changes occurring
(http://support.microsoft.com/default.aspx?scid=kb;en-us;828637&Product=sql2k).
Cheers,
Paul