Friday, October 30, 2009

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:

Tuesday, October 20, 2009

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.

Chinese characters in IE8 before and after the solution
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.

IE8 Compatibility button
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.

IE8 Encoding context menu
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:

Windows XP Regional and Language Options - Install files for East Asian languages
编码快乐!
해피 코딩!
ハッピーコーディング!
Happy coding!