Grant Fritchey (6/25/2010)

---

yessen (6/24/2010)

---

GilaMonster (6/23/2010)

---

You're confusing concepts. Log chain has to do with whether you can do point in time restores (restore a database to any time) using RESTORE DATABASE and RESTORE LOG.

Recovery (restart recovery) is a process that all databases have to go through on startup, regardless of recovery model or backups. It's to bring the database to a consistent state.

As I mentioned earlier

It's called restart recovery and it's required to ensure that the databases are all transactionally consistent. It shouldn't take long. If it does then either your log is badly configured (too many VLFs) or there's IO bottleneck problems on the log and/or data drive, or there's something else hindering checkpoints or recovery.

Have you messed with the recovery interval setting? (sp_configure)

so in order to to make my database not stuck "in recovery mode" every time I restart my server, I need to back up my database and restore it from the same .bak file. This will restart my log chain and "recovery mode" should be quick after that. Am I right?

You have two issues going on here. First, you need to get your databases and your backups configured correctly. Second, why are you restarting SQL Server over and over again? The majority of our servers are restarted maybe two-three times a year. It really sounds like something is going a bit off with your systems if you are restarting them so often.

As to the databases and the backups, it sounds like you're almost there. You have the concept of point in time recovery down. Now you need to implement the log backups, which are completely seperate from, though dependent on, the full backups. You'll need to schedule the log backups so that they run multiple times a day. Not knowing your systems or the business needs, I can't suggest a realistic number, so, as a SWAG, start at every 30 minutes. You may need to adjust that down to run the backups more frequently, but it's really dependent on your needs and your systems. Once you have the log backups running, I think you'll see that your log files don't need to be 300gb or more in size, but, it really depends on the number and size of the transactions in your system. Again, I'm not there, so I can't be sure, 300gb could be the amount of transactions you get within 30 minutes. Regardless, once you get the transaction backup in place, you should be able to determine roughly how much space you need to maintain the log and you can shrink the file down to something near that number, but larger to allow for surges & growing volume.