Transaction Logs Killed My SQL Server
Once again I have had a call from someone saying, "Something has stopped my SQL Server from running, and the error message says that the disk where my trasaction logs are is full!!" Well, the only way to keep from having this problem is to establish disaster recovery methods on your databases.
As most developers, having worked with SQL Server for any period of time, know there are different disaster recovery models available on an individual database level. I’m talking about database backup and restore techniques. The capabilities of different versions of SQL Server have changed over time, but the most recent versions of SQL Server since SQL Server 2005 are all pretty consistent.
For example: SQL Server 2008R2 now supports three different kinds of recovery models
- Fully
- Bulk Logged
- Simple
Each of these methods of backup support a full database backup. Each database has a transaction log. The difference between them is the amount of activity in the transaction log and the backup types supported.
There are three kinds of backups you may perform depending on your recovery model. A full backup writes all contents of the database. A Differential backup writes the delta of change. A transaction log writes all the latest transactions.
Backups are important to you because if you don’t have this configured correctly for the different Recovery Models you can run out of disk space, Your transaction log will continue to grow until an event occurs releasing space in the transaction log.
Simple Recovery allows you to perform a Full or Differential backup. Transaction Log backups are not supported. Instead, the transaction log is purged of completed transactions each time the system executes a Check Point. This is a setting you can configure for the entire SQL Service; it is not database specific. If you are using this model do a search on Check Point for more information on configuring it.
Bulk Logged and Fully Logged recovery models support all backup methods. The difference is the degree of granularity in the transaction log. Bulk Logged Recovery Model writes a minimum amount of data into the transaction log when large data insert methods (bulk copy or bulk insert) are executed in order to increase performance.
Fully logged recovery writes every single transaction to the transaction log allow you to restore to a specific transaction.
With either Bulk Logged or Fully Logged Recovery models the only thing that frees up space in the transaction log is a transaction log backup. Transaction Logs continue to accumulate from the last transaction log backup. I have seen databases run out of space in a 10 gig transaction log file if it wasn’t backed up every hour or less due to high activity. I’ve had databases that I backed up the transaction log every 10 minutes to keep it from growing so large performance began to degrade.
Every system and environment is different. When to run Check Point or perform backup or transaction log backups will vary from one environment to the next.
This is a process that is important for all versions of SQL Server, and most likely any transaction logged data engine. You need to understand how to implement this need in systems as small as Small Business Server versions of SQL. Transaction logs need to be configured for your usage patterns.
Do you have a handle on your backups? Is the maintenance tool in SQL Server Management Studio adequate for the Accidental DBA to manage their needs? Write in with your backup experiences or questions to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
How to Protect Your Job When There is a Shake-Up at the Top
The most effective way to assure stability and sustainability is to make sure your boss isn’t your only conduit to success. If he is the only one that knows your value and potential, then (once he is gone) it is much like jumping out of an airplane with no parachute. It’s a little too late to create the network- parachute in your professional path at this company.
Featured White Paper(s)
Top 10 Tips for Optimizing SQL Server Performance
read more)