I've configured my SQL Server 2000 database maintenance plan so that everything works—except the transaction log backups. Why will full backups work but not transaction log backups?

Be sure that your database is using the correct recovery model. Microsoft recommends the Full recovery model for most databases. Some databases that have a profile that includes a lot of batch data-loading from other systems require switching between the Full model and the Bulk-Logged recovery model. The easiest way to check the recovery model is to navigate to the database in Enterprise Manager, right-click and select Properties, then select the Options tab to see which model you're using. You can also use the ALTER DATABASE command to set the recovery option. Note that the Simple recovery model is recommended only for test systems or when you can recreate the entire database from external data within a time frame that meets your service level requirements.

If you do switch from the Simple model to the Full model, keep an eye on your log-file size for the next few days. If the log gets too big, back it up to recover space. Also, consider resizing your log file to avoid having the autogrow feature kick in; having a suitably sized transaction log should improve performance.

Finally, remember that transactional replication slightly alters how the transaction log works. Data remains in the log until the log reader copies it to the distribution database. Problems in your replication setup—such as the log reader agent not running—can cause log files to grow, even if you're regularly backing them up.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.