In my opinion SQL Server 2005 is a great database system. Building on its predecessors, it manages and maintains databases well and allows customization and flexibility for a company. In order to get the most out of it, you have to fully understand how it works and more importantly, how to configure it.
By default, every database you create in SQL Server 2005 is set to Full Recovery Mode. What this means is that the database will always log every bit of information to the end of time about the database, in case you need to restore in the event of a disaster. This functionality is great to have, but comes at a very high cost, namely in the form of log file sizes. SQL Server records every change to the database into the transaction log. Every INSERT, UPDATE, column modification, TRIGGER event. Everything. And in doing so, the log can grow as large as it wants, potentially causing catastrophic events to the server.
Transaction log file growth is by default unrestricted. Therefore, in order to control its size, administrators have to use built-in mechanisms to control it. Backing up a transaction log will truncate it’s data, however the size will always stay as large as it’s ever been.
Transaction log file growth example:
- Transaction log starts out at default size
- SQL Server records transactions and it grows to 1 GB
- Transaction log is backed up
- SQL Server truncates the transaction log, but leaves the size unchanged
- Transaction log is still 1 GB, however only 2-4% in use with 95%+ free space
The above example can be altered with a number of different methods, all affecting the recovery options to the data. Without any configuration however, the log file could potentially grow to exponential sizes and shut down a server by consuming all available space. I have seen SharePoint generate 80GB+ log files with all of its transactions in an unmaintained SQL Server 2005 environment.
In order to mitigate the risks of transaction log file growth, you have to first determine your disaster recovery tolerances. I’ll break down a few scenarios and describe the right configuration for each. Each scenario will detail the type of situation, the database recovery model, a suggested backup plan, and transaction log maintenance.
Note:
These scenarios are my recommendation from previous experiences. Your business may require a customized plan to meet you disaster recovery needs.
Scenario 1
Full Recovery – high profile data
Database recover mode: Full Recovery Mode
Backups: Database is backed up fully each night
Recovery Model: Full
Transaction Log: Backed up throughout the day (every 15/30 mins). Unrestricted growth.
In this scenario, full recovery of every bit of data is essential. The default settings for any new database usually allow for this scenario. The database is usually the data store for an e-commerce or other frequently updated site. Data is constantly changing and up-to-date information is essential for the business. A database like this cannot afford to lose any more data than necessary (customer transactions, test results, etc…). The frequency of the transaction log backups will prevent the file from growing larger than necessary and truncate data often to maintain the smaller size. The periodic backups will also ensure recoverable data will never be more than 15-30 mins old.
Scenario 2
Full Recovery – low profile data
Database recover mode: Full Recovery Mode
Backups: Database is backed up fully each night
Recovery Model: Full
Transaction Log: Backed up throughout the day (every 4-6 hours). Unrestricted growth.
In this scenario, full recovery of data is essential, although there are tolerances for up to date information. A nightly backup of the database and transaction log backups throughout the day would allow recovery to latest transaction log backup. This is suitable for a company web site or similar site where content may change daily, but not with the frequency of an e-commerce site. The time between transaction log backups will allow for more growth, but SQL Server will truncate it after each backup to maintain the size.
Sceanrio 3
Simple Recovery – very low profile data
Database recover mode: Simple Recovery Mode
Backups: Database is backed up fully each night
Recovery Model: Simple
Transaction Log: Not applicable. Unrestricted growth.
In this scenario, data recovery is not essential to the business. This could apply to a development site, or a company site where content rarely changes. This scenario would require the least amount of space due to SQL Server constantly truncating the log. The Simple recovery model will only allow data recovery from the last full backup. SQL Server will periodically truncate the transaction log to maintain the size of the file.
As you can see, there are several methods to maintain transaction log sin SQL Server 2005. Your business needs will determine what scenario is right for you. Configuring SQL Server properly will allow you operate more efficiently and allow you to recover gracefully in the event of a disaster.
Leave A Comment