RobSymonds.com

I see this topic come up over and over again on the web. Somebody’s transaction logs run wild and they start searching for how to disable the transaction log so they can maximize disk space. I suspect that the people searching for this are probably developers or sys admins that got stuck babysitting a SQL Server and might not have had a chance to get familiar with SQL. 

You cannot disable SQL Server transaction logs any more than you can disable the transmission in your car. SQL operations that change the state of the database are processed in transactions and all transactions are written to the transaction log before they are written to the data file. Here is how Microsoft puts it:

Microsoft® SQL Server™ 2000, like many relational databases, uses a write-ahead log. A write-ahead log ensures that no data modifications are written to disk before the associated log record.

SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are instead made to the copy of the page in the buffer cache. The modification is not written to disk until either the database is checkpointed, or the modifications must be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache but not yet written to disk is called a dirty page.

At the time a modification is made to a page in the buffer, a log record is built in the log cache recording the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page were flushed before the log record, it would create a modification on disk that could not be rolled back if the server failed before the log record were written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record. Because log records are always written ahead of the associated data pages, the log is called a write-ahead log. (http://msdn.microsoft.com/en-us/library/aa174527(SQL.80).aspx)

The transaction log isn’t going anywhere, the real question is: how do you manage transaction logs?

To learn more about how to manage transaction logs, check out the excellent article: Managing Transaction Logs by Microsoft MVP Gail Shaw.

The other day I had to uninstall SQL Server 2005 Standard Edition from a machine and reinstall it.

We had previously done an in-place upgrade from 2000 and I was never 100% convinced the instance was sound. The server is our SSIS and SSRS server. It was originally supposed to be a DEV box. But it has become more of a PROD ETL and reporting box. The only user-facing application being hosted is SSRS.

Prior to the upgrade:

• I collected a handful of scripts to help with the process.
• Backed up my SSRS encryption SN key.
• Tested backing up the databases.
• Created scripts to recreate users, linked servers, backup devices and jobs.
• Documented database properties through screen captures of each screen in SSMS > Server >
• Right-click Properties. You can also use the SERVERPROPERTY function to get a bunch of different server properties but, aside from verifying version numbers throughout the upgrade process, I didn’t use that.
• Verified I had the right password for the domain account I was going to run SQL services under.
• Copied the SQL Server 2005 Standard Edition installer media along with SP1 and SP2 to the target machine to speed things along.

Going into it, my plan for the actual upgrade:

1. Disable any jobs that might run.
2. Stop the reporting web site.
3. Stop reporting services.
4. Backup all dbs.
5. Test restore of one of the smaller dbs on another machine.
6. Detach dbs.
7. Uninstall db engine.
8. Reinstall db engine.
9. Install SP1, SP2 and any updates needed.
10. Attach dbs and test.
11. Recreate logins.
12. Recreate linked servers.
13. Recreate jobs.

A few things…

Instead of detaching the databases by clicking around in SSMS, it’s much faster to script out the detaches. Here is the syntax:

Using the SSMS interface you can reattach all the databases quickly so I didn’t script that out.

Although I had time to prepare, I knew a few things would shake out during the install as they always do.

I ran the install and the updates under my domain account. When I reattached all the databases, they were all owned by me. So I ran scripts to assign them all back to sa:

To enable AWE, you have to give the user you’re going to run SQL under the ‘Lock pages in memory’ privilege. To do this, go to Start > Run and then enter gpedit.msc. Navigate to Group Policy -> Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment. Double-click ‘Lock pages in memory’ and add your user. Click OK. I feel like I did another install recently where I didn’t have to do this step but I’m not sure. After you do this, connect to the db in SSMS and run the following:

I wanted to change the user account that SSRS ran under. Updating the user account in the SSRS Configuration Manager leads to encryption problems. I was careful to backup the encryption key before I started so I was able to follow the process detailed here http://support.microsoft.com/kb/842421 and was up and running again with little trouble. For quick reference, these 2 commands allow you to stop/start IIS through the command line:

iisreset /stop
iisreset /start

The server originally had SQL Server 2005 SP2 installed along with a bunch of other SQL compoments. When I reinstalled SQL Server and tried to apply SP1, SP1 bombed when trying to update the support files. I wish I recorded the exact error but I didn’t. I’ll probably repeat this process on another box soon and if I encounter the problem I’ll provide more detail here. The workaround:

1. Go to Start -> Control Panel -> Add/Remove Programs.
2. Uninstall Microsoft SQL Server Setup Support Files.
3. Install SqlSupport.msi from SQL Server installation media.
4. Try installing SP1 again.

SP2 installed without a hitch.

After installing the Service Packs I also decided to run the Security Baseline Analyzer on the machine to identify new fixes. It found one I needed so I installed it. Typically I’d test it in DEV first but since this was supposed to be a DEV box in the first place and I was doing well on time I installed it. It went in without a problem.

It’s true that you don’t want to continually shrink and grow the log file for a few different reasons including fragmentation and incurring the overhead of growing files during a request. However, if you have decided that your transaction log is in fact too large and needs to be shrunk, here is one way to go about it.

First, you need to determine how large your transaction log should be. The easiest way is to look at the size of your transaction log backups over a span of time. Use this script as a base and tweak as necessary.

What is the largest normal log backup? That will tell you roughly how large your transaction log should be as long as your not running into exceptional circumstances on a regular basis.

If you are running into exceptional circumstances on a regular basis, you might need to change the way you are managing the db or you might have to accept the fact that these circumstances are not as exceptional as you once thought and consider them to be within the limits of expected operation.

Once you have the size:

1. Run a tran log backup.
2. Disable all backup jobs and file operations that might run while you do the shrink.
3. Shrink the log file to the appropraite size.
4. Change the size of the log file in database properties (or through T-SQL).
5. Re-enable the jobs/operations you previously disabled.

This should give you correctly sized logs.

You run the log backup before you shrink to make sure that you don’t have active virtual log files (VLF) that will prevent the shrink from actually reducing the file size.

File shrinks and transaction log backups are not allowed to run concurrently so you have to disable anything that would conflict with the shrink.

You change the size of the log file to make sure you have the least amount of VLFs. More VLFs can reduce performance although from what I’ve read this is more of an issue in 2000 than 2005. I have not personally seen any issues due to misconfigured log files yet though so I can’t say for sure.

Keep the size of the transaction log file in mind when you are restoring a database.

Over the weekend, the backup drive on one of our servers ran out of space. Without transaction log backups, the log grew several times until it consumed the log disk. At that point SQL choked.

We straightened out the SQL problems and things ran smoothly the rest of the weekend. Today we went to restore the backup to a test server. At this point we ran into a problem I haven’t seen before but should’ve anticipated–the backup wouldn’t restore.

A little background… We run full backups every morning and transaction logs throughout the day to a backup device which writes to a single .BAK file. The .BAK file is ~80GB. The data file is ~70GB. After all the transaction log growth, the tran log was ~50GB. The staging server had enough room for the backup. I was restoring over another database so space on the data drive wasn’t an issue. However, the log drive is only about ~50GB total and there are already a few log files on there. As you can imagine, when I went to restore the database, SQL wouldn’t let me, because even though my backup was only ~80GB, the restore process was trying to create a ~50GB log file on a disk that only had ~30GB free.

I couldn’t find any way to reconfigure the size of the log file as part of the restore. What I eventually ended up doing was shrinking the log file back down to a reasonable size in prod and then doing another full backup. I am not a fan of shrinking but this is certainly one of those times where shrinking is appropriate. Keep in mind that there are certain operations that shouldn’t be run concurrently with shrinking files. You can find out more here: http://msdn.microsoft.com/en-us/library/aa213772(SQL.80).aspx (This link is for SQL Server 2000).