Archive for the ‘English’ Category.

Slow cluster failover waiting for network name in online pending state

After getting a new Microsoft failover cluster, I found during the failover tests that the network name failed over horribly slowly. Otherwise everything was fine, but a complete SQL cluster failover took more than a minute and it was all because the network name remained online pending for ages. After some googling I didn’t find any specific solution, but I found a blog post about Exchange 2007 on Windows 2008 which put me into the right direction (that is, answered my question in a sentence) and gave the “I should have thought of this earlier” feeling (which helps me to exercise humbleness).

The sentence is the following:

In previous versions of Windows Cluster Server, every time a Network Name came online, it would register with DNS.

So I just checked my network settings and found that the Register this connection’s addresses in DNS checkbox was ticked. I cleared it and made a few tests: the network name came online in a second or less.

And I hate all the forums outside because you can’t just answer other’s question, you have to register (your login is already in use/too long/too short/contains invalid character/not complex enough/blocked by the swearword filter – and your password is way worse ) – and you get the confirmation mail the next day. So I left a few guys unanswered, but I hope Google will pick up this post so it’ll help on others.

AMD clock drift bug – fixed for Microsoft SQL

Short comment on my previous post: apparently, MSFT fixed this bug for SQL Server 2005 with SP3. More details are in their KB. It’s pretty detailed and you can get a picture of what you can miss with SP3 :) In the end, I should uninstall the AMD gaming mode stuff from the servers…

amd dual-core / quad-core bug and SQL Server

The AMD multicore CPUs have an interesting feature: the timers of the cores are not synchronized. This is the so-called clock-drifting which can be accountable for a lot of unexpected phenomenons due to the fact that the cores have different times and processes using multiple cores can be badly surprised. During the last year I had the chance to collect a few of the tricks:

  • Negative round-trip time during ping (Windows 2003)
  • Excessive amount of events in SQL Server errorlog (W2K3, SQL 2K5)
  • find -mtime 0 won’t find the recently created files (Red Hat EL 3)

Given that this is a mostly-SQL blog, I’m going to detail the second one. You can see events like this in the errorlog:

Date 11/25/2008 9:57:47 AM
Log SQL Server (Current – 11/25/2008 12:49:00 PM)

Source Server

Message The time stamp counter of CPU on scheduler id 3 is not synchronized with other CPUs.

And you can see this quite frequently. Now AMD created a fix for the bug but its target audience is not the big tribe of the sysadmins but the desktops. Why do you need a quad-core for a desktop user? Exactly: for gaming. So the fix called AMD Dual-Core Optimizer Version 1.1.4 gives you a chance to minimize the clock drifting by enabling the Game Mode. I gave it a try and installed it onto the server. Unfortunately the issue didn’t disappear, but it dropped by 97% which is a pretty good result – at least for me.

The idea sounds a bit wacky: fix an enterprise production database server with a gamer hotfix, but:

  1. It does the job
  2. If the Spanish MSFT support team could afford it, then I could as well :)

SQL 2005 errorlog with LogParser

DBAs/sysadmins/other folks responsible for SQL installations should check the errorlog of the SQL servers if something happens to the servers and if nothing happens, then they still have to check the logs regularly as sometimes things happen which are not so obvious at first (e.g. someone is trying to figure out your sa password – unsuccessfully so far, but tomorrow they may succeed). But it’s definitely a burden, especially if you need to do the checks manually, therefore skipping this task is very persuasive. If you’re tough and techie enough then you may decide to set up an automated log collection for your SQL 2005 servers with some pre-filtering, eventually, you’re not a robot, but you can create a software one.

The perfect tool for the task is the LogParser, which is what its name suggests: a tool designed for reading literally any kind of text logs – and a bit more (e.g. Windows eventlog). It knows dozens of formats by heart and you can teach it any format using a simple format file. You can use a SQL-like syntax to filter records and redirect the output into another format (text or SQL) if you want. I used this tool to filter my SQL 2000 errorlogs and mail them to myself on a daily basis. However, when I implemented the regular task with the incremental file read, I got nothing as an output. if I omitted the parameter -iCheckpoint, everything was fine. Sounded weird and Google didn’t help on this. Finally, I figured out what the difference was between the SQL 2000 and the SQL 2005 errorlogs: the SQL Server 2005 creates a unicode file while the SQL 2000 produced an ANSI Latin1. Fortunately, the LogParser has a parameter which tells the encoding of the file: I added -iCodepage:-1 to the command-line, and everything went fine. Finally, here’s the cmd file for collecting SQL errorlogs:

del SQLerrorlog.log
LogParser "select 'SQL2000' as ServerName, STRCAT(STRCAT(DATE,' '), TIME) as LogDateTime, SOURCE, Message INTO reports\SQL1.txt From '\\SQL1\SQLLogs\ERRORLOG' WHERE SOURCE <> 'backup'" -i:TSV -iSeparator:spaces -iHeaderFile:formatfile.txt -headerRow:OFF -nskiplines:9 -iCheckPoint:SQL1.cpt
LogParser "select 'SQL2005' as ServerName, STRCAT(STRCAT(DATE,' '), TIME) as LogDateTime, SOURCE, Message INTO reports\SQL2.txt From '\\SQL2\SQLLogs\ERRORLOG'" -i:TSV -iSeparator:spaces -iHeaderFile:formatfile.txt -iCodepage:-1 -headerRow:OFF -nskiplines:9 -iCheckPoint:SQL2.cpt
copy reports\*.txt SQLerrorlog.log

The format file, called here formatfile.txt, is quite simple:

DATE TIME SOURCE Message

Here you go. Adjust the filters and read the results daily. I hope you’ll find no problems in them :)

UNIQUE INDEX vs multiple NULL values

In Microsoft SQL Server, if you want to make sure that a column contains no duplicates, you can use the UNIQUE keyword to enforce this constraint, either by creating a unique index or by adding a unique constraint to the column. It’s very useful and nice, but… What about NULL values? I mean, obviously, you can insert the first NULL value, but what if you have more null values and you need to enforce only the uniqueness of the non-null values? Continue reading ‘UNIQUE INDEX vs multiple NULL values’ »