SQL Server Database Transaction Log Full

Problem

Your SQL Server database returns an error that your transaction log is full when you try to execute some SQL statements. You check the hard drive free space, and it's full; or perhaps the transaction log has reached its maximum configured size. You check your database's recovery model, and it's set to "Full", even though restoring from a previous night's backup is acceptable recovery in the event of failure.

Solution

Unless you change the default log file settings or database recovery model, the transaction log is a ticking time bomb that grows until disk space is full, rendering your database unusable. This quick solution makes space immediately available, and the long-term solution automates the clean-up of the transaction log file.

The quick solution

The quick solution, after ensuring you have backed up your database as needed, is to execute the following statements in SQL Studio to (1) remove all inactive logs from your database's transaction log, then (2) shrink the log file down to a reasonable size.

1. Purge all inactive logs from a database:
   BACKUP LOG <YourDBName> WITH TRUNCATE_ONLY
   
   For example:
   
   BACKUP LOG XyzWebsite WITH TRUNCATE_ONLY


2. Shrink the database's log to a certain number of MB:
   DBCC SHRINKFILE(<YourDBLogFileName>, <NumMegabytes>)
   
   For example, shrink the log to 100 MB:
   
   DBCC SHRINKFILE(XyzWebsite_log, 100)

Do not shrink frequently, as physical fragmentation impacts database processing time; so after doing this, consider the long-term solution to prevent this from happening again.

The long-term solution

The long-term solution to automatically purge inactive logs is to change the database recovery mode from "Full" to "Simple". As long as restoring from a previous night's full database backup is acceptable recovery for you: in SQL Studio, right-click the database, select Properties ► Options, then change Recovery model to Simple.

For further reading, see the following MSDN references:

• Backup Under the Simple Recovery Model
  http://msdn.microsoft.com/en-us/library/ms191164.aspx
• BACKUP (Transact-SQL)
  http://msdn.microsoft.com/en-us/library/ms186865.aspx
• DBCC SHRINKFILE (Transact-SQL)
  http://msdn.microsoft.com/en-us/library/ms189493.aspx

Asian languages do not display in IE8/XP

The Problem

Asian language characters (i.e. Chinese, Korean, Japanese, Thai, Vietnamese) render as rectangles/boxes in Internet Explorer 8 on Windows XP. The glyphs display correctly in IE7 and FireFox.


Click here to jump right to The Solution, or read on for a brief analysis.

The Analysis

There are two inconvenient client-side workarounds; neither is a real solution. Microsoft states a per-page workaround, but it does not work for me.

The first client-side workaround is to click the "Compatibility" button to tell IE8 to re-render the page using the IE7 rendering engine. This would require your users to click this button in order to view your site, which is not user-friendly. Also, text in HTML <select> boxes is still rendered as boxes.


The second client-side workaround is to right-click the page and select:
Encoding ► More ► <problematic language>. The problem with this approach is: the encoding does not "stick"; you must re-select it for each page, and text in <select> boxes is still no different.


Microsoft's IE8 Readiness Toolkit states a per-page fix: add the following meta tag to tell IE8 to render the page like IE7. However when I add this tag, all characters still look like boxes, so this does not work.

<meta http-equiv="X-UA-Compatible" content="IE=EmulateIE7"/>

The Solution

There are two solutions:

1. Solution 1 requires a web-server change; Asian characters in <select> boxes
   will not display correctly.
2. Solution 2 requires a client-side Windows Control Panel change; all Asian text will display correctly.

Solution 1: Web Server Change

Add to your web server an HTTP response header which instructs IE8 to render the page using the old IE7 renderer. Note: Asian characters displayed in HTML <select> boxes will not render in IE8 nor IE7; if you need that to work, skip to Solution 2.

The HTTP header that fixes IE8 Asian characters in all text except <select> boxes:

X-UA-Compatible: IE=EmulateIE7

Solution 2: Windows Control Panel change

Microsoft has tied Internet Explorer 8 directly into your computer's Regional & Language Support settings. This makes sense, but it's unfortunate for IE since Asian languages work out-of-the box with other web browsers.

To view Chinese, Korean, Japanese and other East Asian languages in all text on web pages in both IE8 and in IE7, follow this. In Windows XP go to:

Control Panel ► Regional and Language Options ► Languages

Check the "Install files for East Asian languages" option, insert your Windows XP CD-ROM, and restart when it's done:


编码快乐！
해피 코딩!
ハッピーコーディング！
Happy coding!

Screen and Print styles in the same CSS file

Until today, I was under the impression that to vary the styles used for HTML displayed on screen vs. printed to paper, one had to create distinct CSS files for screen and print, and use two separate <link> tags, a-la:

<link rel="stylesheet" href="styles_screen.css" />
<link rel="stylesheet" media="print" href="styles_print.css" />

However I found out today that you can actually just use one CSS file, and separate your media usage with the special @media selector. Simply enclose style definitions by media type, as in this simplified example:

h1
{
   font-family: Verdana;
}
@media print
{
   h1
   {
      font-family: Times;
   }
}

Nothing revolutionary, but cool to know you can save a separate HTTP file request by combining all your CSS into one file.

Debugging the Kitchen Sink

In this post, I explain the debugging process, difficulties novice developers have with it, and present a practical non-computer-programming example of applying it to debug a problem with my kitchen sink's faucet.

The Debugging Process

"Debugging" is a vital skill in the software development world that a is used quite often to solve programming problems, ranging from simple logic mistakes to difficult-to-repeat concurrency race conditions or deadlocks.

However the process of debugging is an implementation of a generic, logical, and scientific problem-solving process which is applicable well beyond computer programming. That process is simplified as:

1. Establish an observable problem.
2. Test to determine all of the specific scenarios where the problem is observed.
3. Understand the major components involved.
4. Hypothesize what could be causing the problem.
5. Locate the problem.
6. Make changes to resolve the problem.
7. Test all specific scenarios where the problem happened to confirm the problem is solved.

Difficulties with the Debugging Process

I have often observed that novice developers encounter difficulties in applying this process, by halting as soon as actual results are not the same as expected results. When they come asking "What do I do?" my answer, if not "Check the Reference" will always be, "Debug it".

The difficulty of understanding this process seems to be with Step 5 - Locate the problem, which rightfully may be a time-consuming process. With a "syntax error", the compiler/interpreter usually tells you exactly where the problem lies, so it is an easy fix. But with a "semantic error", meaning it's a programming or design mistake has caused unintended results, you must gradually narrow down the source of the problem over many iterative tests starting from the general range of what works, and gradually running more specific tests, ending at the actual source of the problem.


To illustrate the debugging process in a practical non-computer-programming, example -- just this morning I applied it to a household problem that has been nagging me for quite some time.

Application: The Kitchen Sink's Faucet

A few years ago when I moved into my house, I replaced the kitchen sink's faucet, an old two-knob fixed-faucet type, with a new single-handle pull-out model. However when I was finished there was a problem: the cold water pressure was significantly lower than the hot water pressure. I finally got around to working on this, and applied the same debugging process, as follows.

1. Establish an observable problem.

   The problem is that when the handle is pushed all the way to the cold side, the water is very slow, but when all the way to the hot side, the water is very fast.



2. Test to determine all of the specific scenarios where the problem is observed.

   In this case, the above is the only observable scenario.



3. Understand the major components involved.

   The major components are:
   1. the faucet's output
   2. the faucet's hot and cold water intakes
   3. the 2 tubes connecting the wall outlets to the faucet intakes
   4. the hot and cold wall outlets
   
   This diagram illustrates how the components are connected and the direction of water-flow.
   
   
   

4. Hypothesize what could be causing the problem.

   My two bathroom sinks have no problem with the water pressure from either hot or cold lines. Based on that I will rule out the wall outlets. Also, since the hot water pressure from the faucet output is just fine, I will rule that out.
   
   That narrows down the range of the problem to one of the two remaining components:
   1. the faucet's output
   2. the faucet's hot and cold intakes
   3. the 2 tubes connecting the wall outlets to the faucet intakes
   4. the hot and cold wall outlets



5. Locate the problem.

   1. First I want to see if the problem exists in the faucet's hot and cold intakes. I know that hot is fine, but cold is low, so my test was to switch the connections between the intakes and the tubes, causing hot water to come out when the faucet is turned to cold, and vice versa. If the cold water pressure becomes good but hot becomes poor, then the problem exists in the intakes:
      
      
      The results of this test however are that the pressure is unchanged - hot is still good and cold is still poor. So the intakes are not the problem. I scratch that off the list:
      
      1. the faucet's output
      2. the faucet's hot and cold intakes
      3. the 2 tubes connecting the wall outlets to the faucet intakes
      4. the hot and cold wall outlets
   
   
   2. If my hypothesis is correct, the problem must exist in the tubes. To test this, I undo the tube switch from the previous test, and now swap the tube connections with the wall outlets. Now, if the cold water pressure becomes good but hot becomes poor, then the problem exists in the tubes:
      
      
      The results of this test are that the cold water pressure is now good, but the hot water pressure is poor. Thus, this test proves that the problem exists in the Cold Water Tube.



6. Make changes to resolve the problem.

   Since I cannot get into the cold water tube to find out what is wrong with it, my next step is to replace the defective tube with a new one from the store.



7. Test all specific scenarios where the problem happened to confirm the problem is solved.

   Once I buy my new tube, I will install it and test to make sure that the cold water pressure is now the same as the hot water pressure.

As you can see, the process of debugging can be applied to any problem, computer-programming or otherwise, as long as you have a general understanding of the components involved, how they interact, and if you can follow and logical and scientific thought-process.

CPU fans running loudly and constantly

The Problem

For the last few days, I have noticed both of my CPU fans on my Gateway / AMD Phenom Quad Core / Windows Vista machine have been running loudly and constantly, even when I don't have any applications open. My CPU fans are not dusty.

This seemed to coincide with either a boatload of Windows Updates I installed a few days ago, or my installation of Microsoft Visual Studio 2008 (for my upcoming Mobile Development class) and its subsequent service packs.

The Solution

It turns out the loud CPU fans are directly related to the configured Power Options in the Windows Control Panel.

My Power Plan was set to "High Performance". The very instant I changed the this to either "Power saver" or "Balanced", the CPU fans ceased or became very muted.


These settings state that processor performance will be reduced, but for now I will leave this as-is to see if I notice any difference, and who knows, this might show up as savings on the utility bill!

Utilitarian Friday: "Carbonite" Computer Backup

What would you do if one day all of your digital photos, digital music, and digital documents were just -- gone?

What are your backup plans?

One evening a couple years ago, I left my laptop charging directly in the wall outlet. The next morning, it was fried from a power surge. Fortunately I was able to extract the hard drive and retrieve my data from it by using a special laptop-harddrive-to-IDE cable to attach to my desktop PC. It could have been worse. Now I make sure everything important is always plugged into surge protectors.

A couple years prior to that, my external hard drive that contained my music, pictures, freelance files and other important files, started making whirring and clicking sounds after 5 or 10 minutes of being turned on, before automatically shutting down. Fortunately for me again, I was able to copy files away from it in batches before the shutdown. It could have been worse.

My most recent backup solution was to copy all of my music and pictures to DVDs that now sit collecting dust on my shelf in the event that they are needed. This is definitely a good plan, but the last time I did that was at least a year ago. Plus, what if there is a fire or burglary? There must be an easier way.

Carbonite

Enter Carbonite, a very inexpensive, off-site, automatic, and secure file backup and restore service. It is highly-rated and works on both Windows and Mac platforms.

Secure

This is obviously an important factor when giving a third-party access to your private documents. Carbonite states:

We encrypt your files twice before backing them up securely offsite, using the same encryption techniques that banks use. Files remain encrypted at our secure data centers, so only you can see them.

Automatic

It knows what to backup and what not to back up. Music, pictures, documents, your desktop, etc. It runs in the background for a few days for one big initial backup. Then once it is done, it automatically backs up only new or changed files. The handy status window available from the System Tray shows you the current progress and the files that are currently backing up:

Customizable

It is very easy to tell Carbonite what files/folders you do and do not want backed up. It integrates with Windows in two ways:

1. Dots in the bottom left corner of icons.
• No dot means it will not be backed up
• A Yellow dot means backup is pending
• A Green dot means it has been backed up
• A donut means that a folder contains some files that are to-be-backed-up and some that are not to be backed up.


2. The right-click context menu for files and folders contains a new "Carbonite" item that allows you to change the backup options:
• Don't back this up
• Back this up
• Back this up as soon as possible, to bump up a pending backup in the queue

Easy to Restore

Once your files are backed up, how easy is it to restore? Very. Carbonite shows up in Windows Explorer as if it were another hard drive. Simply browse your Carbonite drive, right-click on the files or folders you want to restore, and you can restore to the same location, restore to another location, or restore previous versions.

15 Day Free Trial

Download Carbonite here. You'll get a free 15 day trial. Then for unlimited backup, it is $55 per year, or even less if you sign up instead of the trial or buy for more than 1 year at at time. Oh and I get 3 months of backup free if you sign up ;)

To me, the peace of mind of knowing my important files are securely backed up at all times is well worth the price. What are your backup plans?

Utilitarian Friday: "KeePass" Password Safe

After several months without many brick-wall coding problems to document, I'd like to start a mini-series called Utilitarian Friday to showcase many essential free software utilities that I use every day to increase my productivity. Not only are these useful to coders, but to the power computer-user alike.

Let's begin with KeePass - the Password Safe!

KeePass

KeePass is an easy, secure way to control your passwords using a flash drive or your local machine.

If you suffer from any of these conditions, KeePass is for you:

• same-password-for-everything syndrome
• I-can't-find-the-blasted-post-it-note-itis
• I-thought-it-was-PA$$W0RD-but-maybe-it-was-P7S$WOR3-disfunction

There are 5 key features KeePass delivers for me: security, organization, ease-of-use, password generation, and a good deal (free!).

I. Security

How are stored passwords kept secure?

1. The software is open-source, so you can ensure for yourself that it's not doing anything fishy.


2. The master password, required to use your KeePass, is hashed with the very secure SHA-256 algorithm. Both the passwords stored and the utility's database itself are encrypted using the AES and Twofish ciphers -- which are ciphers used by banks.
   
   According to the makers of KeePass:

   "Even if you would use all computers in the world to attack one database, decrypting it would take longer than the age of the universe."

3. When running in memory, passwords are still encrypted.


4. Set KeePass to lock when minimized, and any subsequent access requires entry of your master password.

II. Organization

Set up a tree of Password Groups, assign little icons to each group, and it is so easy to find your passwords.

For example my KeePass, below, contains "folder" groups for: personal stuff, my full time job, and my freelance job. Then I organized those into type of passwords such as FTP, RDP, VPN, Live Sites, Dev Sites, etc.



While others are struggling to locate passwords by shuffling through physical folders and papers, or thinking back through fading memories, KeePass makes it a no-brainer.

III. Ease-of-Use

So once your passwords are stored, how to you use them? There are 3 ways.

1. In your web page or application, click on the username text box, then press Ctrl + Alt + A. KeePass automatically enters your username, password, and submits the form. It can do this because it's aware of the title bar of the active window, using that to find a match in your password database.


2. If no exact match is found or if the web site you are using does not have useful or consistant title bar text, press Ctrl + Alt + K to bring KeePass to focus, browse for your password record, click on it, then press Ctrl + V and it will auto-type in the window that last had focus - starting from the text box that last had focus.


3. If you just need to copy and paste the password itself, in KeePass click on the password record and simply press Ctrl + C to have the password copied to the clipboard for 10 seconds.

Additionally, KeePass is a tiny application, less than 1MB, that you can easily carry around on your USB flash drive, and keep back-up copies on your home computer and work computer in case you lose or forget your flash drive.

IV. Password Generation

The great thing with KeePass is that if there is a password that you do not *need* to commit to your brain's memory, you can use the built-in password generator, to automatically make long, secure passwords for you to use.



This especially comes in handy for services that require you to change your password periodically.

V. A Good Deal

As with much of the open-source world, this price of this utility is FREE! Since this is not a remote service, there is no monthly fee or anything of the sort.

Download It Already!

Get KeePass now from its official web site or from its SourceFourge project page:

• http://keepass.info
• http://sourceforge.net/projects/keepass/

Stay tuned to next week's Utilitarian Friday for another review of an essential software utility!