I'm running sql server 2005 standard edition. We have given up on sql
server replication for various reasons and are gonig to implement
transaction log shipping. I need to switch the database to "Full"
recovery model but owing to a fascinating combination of circumstances
and indecisiveness the server has had replication disabled before the
new backup stratgey is in place. I don't want to wait another day
before I get a replication strategy going to I want to go to FULl
model right NOW. However the databse is in use and I don't want things
to be disrupted. The database server is only lightly loaded (8 cores,
only one running at any one time above 10% and disk queue about 0.1)
and a few seconds of latency is OK..
is it safe to switch recovery model to FULL during working hours on a
production database?
thanks,
sam
sam,
Switching to full mode will not add appreciably to the load on your server,
but it will keep the logs until they are backed up, so you may see grown in
your log (.ldf) files.
When switching to full mode, the first thing to do is a full backup. This
may cause about a 10% hit on the throughput of that server during the time
of the backup. Again, a small price to pay.
Then be sure to schedule regular transaction log backups so that your
transactions are saved and the log files do not grow excessively. (At least
once a day, but depending on your work and its critical nature it could be
as often as every 5 minutes.)
RLF
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:6ad20370-72c2-4fec-b623-81e35b63bc9d@.u69g2000hse.googlegroups.com...
> I'm running sql server 2005 standard edition. We have given up on sql
> server replication for various reasons and are gonig to implement
> transaction log shipping. I need to switch the database to "Full"
> recovery model but owing to a fascinating combination of circumstances
> and indecisiveness the server has had replication disabled before the
> new backup stratgey is in place. I don't want to wait another day
> before I get a replication strategy going to I want to go to FULl
> model right NOW. However the databse is in use and I don't want things
> to be disrupted. The database server is only lightly loaded (8 cores,
> only one running at any one time above 10% and disk queue about 0.1)
> and a few seconds of latency is OK..
> is it safe to switch recovery model to FULL during working hours on a
> production database?
> thanks,
> sam
|||ah thanks for that russell, the backup was also a worry.
but i was wondering about the actual moment of switch over, will that
cause any freeze or hang ups?
|||No, basically I understand it does two things.
1. Stops truncating the log
2. Increases the amount of logging done for BULK INSERTs. (If you don't
want this behaviour, the Bulk-Logged recovery model is similar to FULL, but
gives reduced coverage for the bulk inserts.)
RLF
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:dde0ef2e-c103-48fd-a5f7-df76652da702@.b1g2000hsg.googlegroups.com...
> ah thanks for that russell, the backup was also a worry.
> but i was wondering about the actual moment of switch over, will that
> cause any freeze or hang ups?
|||thanks for your advice. the switch over was completed with no fuss and
took only a few seconds..
sam
sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment