Re: Log Shipping: question about secondary dB mode?

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@xxxxxxx> wrote in message news:ODgK4$QsIHA.3604@xxxxxxxxxxxxxxxxxxxxxxx
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!

.

Relevant Pages

  • Re: Still dont understand log backups
    ... The transaction log, whether or not it is one or many files, is a serial, ... until the end of the physical log file is reached. ... backup is executed, the active "virtual" logs are backed up, but the ... Using BULK LOGGED, does the online log file contain EVERY transaction for all time -- given that I don't truncate it manually? ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 2005 Shrink issue
    ... When you perform a Transaction Log Backup, passive virtual logs in your transaction log file are deleted. ... you can change your database's recovery model to SIMPLE when you want to get rid of your passive virtual logs in your transaction log file. ...
    (microsoft.public.sqlserver.tools)
  • Re: How can I back up a log-shipped database?
    ... This means that a later log backup from the production database will not just be ... able to add the log records to the log-shipped database, because the transaction log has been ... It's clear I don't understand the whole RECOVERY business. ...
    (microsoft.public.sqlserver.server)
  • Re: optimum log file size
    ... Fixing of Transaction log file based up on the amount of bulk transaction ... CUrrent log growth will be because of FULL recovery mode. ... If you do not require a point in time recovery or if your database ...
    (microsoft.public.sqlserver.clients)
  • Re: Restore Database w/Large Transaction Log File
    ... If I am reading you correctly, the backup has already been trasmitted to the ... AGAIN, just like the other respondents have told you: shrink the log file, ... Shrinking a database transaction log file that has that has been pre-gone to 200 GB in size, will not save any disk space on the restore. ...
    (microsoft.public.sqlserver.server)
Loading