Problem
When I have performed database maintenance in the past the transaction log backups were huge and I was unable to restore the transaction logs. This caused more problems for me so I have stopped performing SQL Server maintenance. My SQL Server performance is now an issue, so I am looking for a process to be able to perform database maintenance for my databases in full recovery mode during a pre-defined maintenance window.
Solution
Performing regularly scheduled maintenance is critical for high performance and ensuring the users will have a positive experience with your applications. The challenge with performing database maintenance for databases in full recovery mode is the excessively large transaction log backups as compared to databases in simple recovery mode. The excessive size is due to the full recovery mode retaining all of the before and after records. With a great deal of maintenance the probable cause for not being able to restore the transaction logs is an incorrect pointer reference.
The process below to perform database maintenance during a pre-defined maintenance window should be tested in a development environment for databases in full recovery mode prior to executing in production:
- Backup the database
- Set the recovery model to simple
- Perform the database maintenance
- Alternative steps – SQL Server – Performing maintenance tasks
- Backup the transaction log with NO_LOG
- CHECKPOINT the database
- Set the recovery model to full
- Backup the database
- Issue the transaction log backups
Next Steps
- Assess your databases to determine if the transaction log backup.
- If you have been hesitant to perform database maintenance for databases in full recovery mode, consider the approach listed above in a development environment.
- If the development process works as expected, test the process in your QA environment and then push the code out to production environment.
- If the backup time and disk space is causing issues for you, consider the backup and recovery tools for SQL Server that can compress the backups to save 50% or more of the disk space and 50% or more of the backup time needed.
- For additional MSSQLTips.com tips on performing database maintenance, reference these tips:
- SQL Server – Performing maintenance tasks
- Simple script to backup all databases
- SQL Server 2000 to 2005 Crosswalk – Database Maintenance Plan Wizard to SQL Server Integration Services (SSIS)
- Database Maintenance Plans and Backup File Management in SQL Server 2005
- Maintenance Tasks: Automating the RESTORE VERIFYONLY Process
Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.