Hi: I set up tLog shipping between two sql2005 servers in a 2003 AD domain.
Everything is working, but I have a couple questions.

The primary dB is set to "full recovery". It's currently 5 gig with the log
at 111 meg, set to grow by 100 MB increments. Although the dB is large with
a lot of historical data, the actual transaction load is not very large.

I have the transaction log backing up every 15 minutes, and the secondary dB
is set to "no recovery mode". According to the logs, all the backup and
restore jobs are proceeding without errors.

The quarter-hourly transaction logs are quite small...generally less than 1
meg.

Questions:

Do I ever need to separately back up the log file and shrink it, if it's
being backed up every 15 minutes? Or will it grow over time unless I do a
separate backup/shrinkfile?

I still need "snapshots" of the database in case we need to roll back for
any reason...can I schedule a full db backup every 24 hours or will this
conflict with the log-shipping? I had this scheduled in a maintenance plan,
but when I set up tLog shipping it disabled the schedule for the full
backup.

With the database on the secondary in "no recovery" mode, it can't be
accessed...it seems to be in permanent "restoring" mode...is that correct?
If I wanted to be able to query it, would I need to change it to "standby"
mode, and is it possible to change the mode from with the tLog shipping
script? Or would I have to start over with "standy mode" selected for the
secondary?

Lastly, with the secondary in "no recovery" mode, in the event that I need
to fail over to it, what do I need to do to get it out of "no recovery"
mode? In SMS, I can't access it while it's in "no recovery" mode.

Thanks in advance!

There is no need to shrink the log file. Regular transaction log backups
will allow the committed and backed up portion of the log file to be reused
for new transactions. The exception would be if you had a very long running
open transaction that may prevent this until it is committed or rolled back.
Taking a FULL backup will not break the log chain or affect log shipping. To
access the secondary db you can create a database snapshot that will allow
read only access to the data. If you need the data refreshed you can drop
and recreate the snapshot. And last to recover the db you restore the last
log file using the WITH RECOVERY option and you should be all set.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"geekyguy" <geeky (AT) guy (DOT) com> wrote

There is no need to shrink the log file. Regular transaction log backups
will allow the committed and backed up portion of the log file to be reused
for new transactions. The exception would be if you had a very long running
open transaction that may prevent this until it is committed or rolled back.
Taking a FULL backup will not break the log chain or affect log shipping. To
access the secondary db you can create a database snapshot that will allow
read only access to the data. If you need the data refreshed you can drop
and recreate the snapshot. And last to recover the db you restore the last
log file using the WITH RECOVERY option and you should be all set.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"geekyguy" <geeky (AT) guy (DOT) com> wrote

There is no need to shrink the log file. Regular transaction log backups
will allow the committed and backed up portion of the log file to be reused
for new transactions. The exception would be if you had a very long running
open transaction that may prevent this until it is committed or rolled back.
Taking a FULL backup will not break the log chain or affect log shipping. To
access the secondary db you can create a database snapshot that will allow
read only access to the data. If you need the data refreshed you can drop
and recreate the snapshot. And last to recover the db you restore the last
log file using the WITH RECOVERY option and you should be all set.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"geekyguy" <geeky (AT) guy (DOT) com> wrote

There is no need to shrink the log file. Regular transaction log backups
will allow the committed and backed up portion of the log file to be reused
for new transactions. The exception would be if you had a very long running
open transaction that may prevent this until it is committed or rolled back.
Taking a FULL backup will not break the log chain or affect log shipping. To
access the secondary db you can create a database snapshot that will allow
read only access to the data. If you need the data refreshed you can drop
and recreate the snapshot. And last to recover the db you restore the last
log file using the WITH RECOVERY option and you should be all set.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"geekyguy" <geeky (AT) guy (DOT) com> wrote

There is no need to shrink the log file. Regular transaction log backups
will allow the committed and backed up portion of the log file to be reused
for new transactions. The exception would be if you had a very long running
open transaction that may prevent this until it is committed or rolled back.
Taking a FULL backup will not break the log chain or affect log shipping. To
access the secondary db you can create a database snapshot that will allow
read only access to the data. If you need the data refreshed you can drop
and recreate the snapshot. And last to recover the db you restore the last
log file using the WITH RECOVERY option and you should be all set.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"geekyguy" <geeky (AT) guy (DOT) com> wrote

There is no need to shrink the log file. Regular transaction log backups
will allow the committed and backed up portion of the log file to be reused
for new transactions. The exception would be if you had a very long running
open transaction that may prevent this until it is committed or rolled back.
Taking a FULL backup will not break the log chain or affect log shipping. To
access the secondary db you can create a database snapshot that will allow
read only access to the data. If you need the data refreshed you can drop
and recreate the snapshot. And last to recover the db you restore the last
log file using the WITH RECOVERY option and you should be all set.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"geekyguy" <geeky (AT) guy (DOT) com> wrote

There is no need to shrink the log file. Regular transaction log backups
will allow the committed and backed up portion of the log file to be reused
for new transactions. The exception would be if you had a very long running
open transaction that may prevent this until it is committed or rolled back.
Taking a FULL backup will not break the log chain or affect log shipping. To
access the secondary db you can create a database snapshot that will allow
read only access to the data. If you need the data refreshed you can drop
and recreate the snapshot. And last to recover the db you restore the last
log file using the WITH RECOVERY option and you should be all set.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"geekyguy" <geeky (AT) guy (DOT) com> wrote

There is no need to shrink the log file. Regular transaction log backups
will allow the committed and backed up portion of the log file to be reused
for new transactions. The exception would be if you had a very long running
open transaction that may prevent this until it is committed or rolled back.
Taking a FULL backup will not break the log chain or affect log shipping. To
access the secondary db you can create a database snapshot that will allow
read only access to the data. If you need the data refreshed you can drop
and recreate the snapshot. And last to recover the db you restore the last
log file using the WITH RECOVERY option and you should be all set.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"geekyguy" <geeky (AT) guy (DOT) com> wrote

There is no need to shrink the log file. Regular transaction log backups
will allow the committed and backed up portion of the log file to be reused
for new transactions. The exception would be if you had a very long running
open transaction that may prevent this until it is committed or rolled back.
Taking a FULL backup will not break the log chain or affect log shipping. To
access the secondary db you can create a database snapshot that will allow
read only access to the data. If you need the data refreshed you can drop
and recreate the snapshot. And last to recover the db you restore the last
log file using the WITH RECOVERY option and you should be all set.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"geekyguy" <geeky (AT) guy (DOT) com> wrote