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.

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).

I was trying to install SQL Server 2005 Developer Edition on an XP SP3 workstation today but setup kept failing. The setup screen looked like this:

Looking in the log I found:

=== Verbose logging started: 6/23/2008  11:09:27  Build type: SHIP UNICODE 3.01.4001.5512  Calling process: C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe ===
MSI (c) (B8:40) [11:09:27:540]: Resetting cached policy values
MSI (c) (B8:40) [11:09:27:540]: Machine policy value ‘Debug’ is 0
MSI (c) (B8:40) [11:09:27:540]: ******* RunEngine:
******* Product: {AEB9948B-4FF2-47C9-990E-47014492A0FE}
******* Action:
******* CommandLine: **********
MSI (c) (B8:40) [11:09:27:540]: Client-side and UI is none or basic: Running entire install on the server.
MSI (c) (B8:40) [11:09:27:540]: Grabbed execution mutex.
MSI (c) (B8:40) [11:09:27:540]: Cloaking enabled.
MSI (c) (B8:40) [11:09:27:540]: Attempting to enable all disabled priveleges before calling Install on Server
MSI (c) (B8:40) [11:09:27:540]: Incrementing counter to disable shutdown. Counter after increment: 0
MSI (s) (14:38) [11:09:27:540]: Grabbed execution mutex.
MSI (s) (14:14) [11:09:27:540]: Resetting cached policy values
MSI (s) (14:14) [11:09:27:540]: Machine policy value ‘Debug’ is 0
MSI (s) (14:14) [11:09:27:540]: ******* RunEngine:
******* Product: {AEB9948B-4FF2-47C9-990E-47014492A0FE}
******* Action:
******* CommandLine: **********
MSI (s) (14:14) [11:09:27:540]: Machine policy value ‘DisableUserInstalls’ is 0
MSI (s) (14:14) [11:09:27:555]: MainEngineThread is returning 1605
MSI (c) (B8:40) [11:09:27:555]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied.  Counter after decrement: -1
MSI (c) (B8:40) [11:09:27:555]: MainEngineThread is returning 1605
=== Verbose logging stopped: 6/23/2008  11:09:27 ===

After some searching on the web I discovered a solution here: http://forums.microsoft.com/msdn/ShowPost.aspx?postid=2113041&isthread=false&siteid=1&authhash=aad032b508898d0d2d15a68c05396c2b476b4c7c&ticks=633247841167440228.

Looks like the MSXML 6 installer leaves something behind that SQL Server Setup doesn’t like. The solution is to download the Windows Installer CleanUp Utility and get rid of the MSXML 6 installer files. After that—smooth sailing.