Restore 500GB SQL2000 database from tape using NetBackup 6.5.3.1

The other day I was restoring a 500GB SQL Server 2000 database from tape using NetBackup 6.5.3.1 and it didn’t work well. Well, it didn’t work at all. No matter how many times I tried, I kept getting this error:

Status Code 41: network connection timed out

This was not the first database I’d tried and I was able to restore other databases successfully, so I knew there was nothing wrong with the network. One thing I could think of was the relatively large database size.

At that moment, I was 50% sure that the error was caused by a timeout setting somewhere in NetBackup. Since I had limited experiences working with NetBackup, I turned to Google and found the following article:

DOCUMENTATION: Restores of large Microsoft SQL server databases using the NetBackup for Microsoft SQL Server database extension fail before jobs start reading data from tape.

With Microsoft SQL server, the restore process starts by having SQL allocate all of the data files that will be used for the SQL Database. SQL then writes zeroes to all of these files. If this is a very large SQL database, this process can take a significant amount of time. Only after Microsoft SQL finishes writing zeros will it start requesting data from the NetBackup agent. Generally there is no appearance of activity until the Microsoft SQL server is ready to start requesting data to recover the SQL database.

If the NetBackup “Client Read Timeout” for that client is not large enough, the restore processes will have already timed out before the SQL Server requests the first byte of data.

It all made perfect sense to me and luckily I had read about instant file initialization before so I knew it was exactly it. I’d managed to connect all the dots and came to the understanding that:

if file initialization time > client read timeout
    status code 41
else
    ok

Since I had no control over the database size and had to restore to a SQL Server 2000 box, I definitely needed to modify “Client Read Timeout”, but to what?

A quick search on the net returned overwhelming amount of blog posts including Symantec documentation advising people to increase that number.

That just didn’t seem right to me. My experience tells me when you code something like a timeout, you always leave behind a way to disable it and the convention is: zero = disabled.

In the end, I set the timeout to zero and guess what? It worked. I was happily restoring the 500GB database and looking forward to restoring the 2TB data warehouse next.

–oo00oo–

For more info on instant file initialization, please refer to the following blog posts:
Instant Initialization – What, Why and How?
Misconceptions around instant file initialization
Search Engine Q&A #24: Why can’t the transaction log use instant initialization?

Rebuild Master failed with error -1:

The other day I was trying to rebuild the master database for a SQL Server 2000 box. Everything was going well until the “Configuring Server” dialog popped up.

D0B9A22DC37EAE4F_164_0

The progress bar went across about 4 times and then an error message popped up.

D0B9A22DC37EAE4F_164_1

That’s weird, I have rebuilt the master database a few times before and I have never encountered errors like this one. I looked for the error details and started with the cnfgsvr.out log file located in C:\Program Files\Microsoft SQL Server\MSSQL\Install

D0B9A22DC37EAE4F_164_2

By looking at the log file, I could confirm the error, but it didn’t help to explain as why the connections had failed. So I ran eventvwr. One of the system log error mentioned something about MSSQLSERVER service failed to start.

D0B9A22DC37EAE4F_164_3

At that point, I was still not sure what’d happened, so I then ran services.msc and tried to start the MSSQLSERVER service manually.

D0B9A22DC37EAE4F_164_4

The error message matched what I had seen in the event viewer. Path? What path? Suddenly as if stricken by lightening, I noticed “Path to executable:” had the short file names for the path. The question became obvious, what was MICROS~3?

D0B9A22DC37EAE4F_164_5

A quick look in the explorer revealed 5 long name folders starting with the word “Microsoft”.

D0B9A22DC37EAE4F_164_6

MICROS~3 happened to be “Microsoft Office” instead of “Microsoft SQL Server”. Now the cause of the issue had been identified, I needed a way to work around it. For what I was doing at the time, I could not just reinstall SQL Server, therefore I decided to move the “80” and “mssql” folders into “Microsoft Office”.

D0B9A22DC37EAE4F_164_7

What I had effectively done was making C:\PROGRA~1\MICROS~3\MSSQL\binn\sqlservr.exe a valid path.

Then I reran rebuildm.exe and as expected, everything worked.

D0B9A22DC37EAE4F_164_8

–oo00oo–

For more info on short file names, please refer to the following Microsoft Knowledge Base articles:
How Windows Generates 8.3 File Names from Long File Names
8.3 Short File Names not Retained after Tape Restore
How to Disable Automatic Short File Name Generation

How to pad numbers with leading zeros

I often come across a date manipulation requirement to have a leading zero for the single digit day/month number. Well, it is pretty easy to come up with a solution, but the other day I stumbled across a little gem which I would like to call the “think outside the square” approach.

Previously I would write something like:

select 
    case 
        when day('2009-07-01') < 10 then '0' 
        else '' 
    end 
    + cast(day('2009-07-01') as varchar)

Nowadays, I simply apply the “think outside the square” approach to this problem:

select right('0' + cast(day('2009-09-01') as varchar), 2)
select right('0' + cast(day('2009-10-19') as varchar), 2)

Include timestamp in the command prompt

I find myself working from the command prompt quite often, and I always have the need to capture the screen shots for demonstration or auditing purposes, so it is vital to incorporate the timestamp in the command prompt.

Let me show you how it is done. Open up a command window and type:

PROMPT=$D$S$T$H$H$H$_$P$G

Depending on your locale and system settings, you’ll see something like the following:

16/10/2009 Fri 22:05:59
C:\Windows\System32>

To make the timestamp a permanent feature, simply add it to the environment variables.

IsNumeric != IsNumber

According to Books Online:
ISNUMERIC determines whether an expression is a valid numeric type. However, ISNUMERIC also returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($).
 
So what other characters/symbols are also treated as the numeric type? I’ve built two tables to help answer the question.
 
ASCII Symbol Description IsNumeric
9   tab 1
10   newline 1
13   carriage return 1
32   space 0
33 ! exclamation mark 0
34 double quote 0
35 # hash 0
36 $ dollar 1
37 % percent 0
38 & ampersand 0
39 single quote 0
40 ( open bracket 0
41 ) close bracket 0
42 * multiply/star 0
43 + plus 1
44 , comma 1
45 minus/dash 1
46 . full stop 1
47 / divide/forward slash 0
48 0 0 1
49 1 1 1
50 2 2 1
51 3 3 1
52 4 4 1
53 5 5 1
54 6 6 1
55 7 7 1
56 8 8 1
57 9 9 1
58 : colon 0
59 ; semicolon 0
60 < less than 0
61 = equal 0
62 > greater than 0
63 ? question mark 0
64 @ at 0
65 A A 0
66 B B 0
67 C C 0
68 D D 0
69 E E 0
70 F F 0
71 G G 0
72 H H 0
73 I I 0
74 J J 0
75 K K 0
76 L L 0
77 M M 0
78 N N 0
79 O O 0
80 P P 0
81 Q Q 0
82 R R 0
83 S S 0
84 T T 0
85 U U 0
86 V V 0
87 W W 0
88 X X 0
89 Y Y 0
90 Z Z 0
91 [ open square bracket 0
92 \ backslash 1
93 ] close square bracket 0
94 ^ power 0
95 _ underscore 0
96 ` apostrophe 0
97 a a 0
98 b b 0
99 c c 0
100 d d 0
101 e e 0
102 f f 0
103 g g 0
104 h h 0
105 i i 0
106 j j 0
107 k k 0
108 l l 0
109 m m 0
110 n n 0
111 o o 0
112 p p 0
113 q q 0
114 r r 0
115 s s 0
116 t t 0
117 u u 0
118 v v 0
119 w w 0
120 x x 0
121 y y 0
122 z z 0
123 { open brace 0
124 | vertical bar/pipe 0
125 } close brace 0
126 ~ tilde 0
ASCII Symbol Expression1 IsNumeric Expression2 IsNumeric Expression3 IsNumeric
9 (tab) 1(tab) 0 (tab)2 1 1(tab)2 0
10 (newline) 1(newline) 0 (newline)2 1 1(newline)2 0
13 (carriage return) 1(carriage return) 0 (carriage return)2 1 1(carriage return)2 0
32 (space) 1(space) 1 (space)2 1 1(space)2 0
33 ! 1! 0 !2 0 1!2 0
34 1″ 0 “2 0 1″2 0
35 # 1# 0 #2 0 1#2 0
36 $ 1$ 0 $2 1 1$2 0
37 % 1% 0 %2 0 1%2 0
38 & 1& 0 &2 0 1&2 0
39 1′ 0 ‘2 0 1’2 0
40 ( 1( 0 (2 0 1(2 0
41 ) 1) 0 )2 0 1)2 0
42 * 1* 0 *2 0 1*2 0
43 + 1+ 0 +2 1 1+2 0
44 , 1, 1 ,2 1 1,2 1
45 1- 0 -2 1 1-2 0
46 . 1. 1 .2 1 1.2 1
47 / 1/ 0 /2 0 1/2 0
48 0 10 1 02 1 102 1
49 1 11 1 12 1 112 1
50 2 12 1 22 1 122 1
51 3 13 1 32 1 132 1
52 4 14 1 42 1 142 1
53 5 15 1 52 1 152 1
54 6 16 1 62 1 162 1
55 7 17 1 72 1 172 1
56 8 18 1 82 1 182 1
57 9 19 1 92 1 192 1
58 : 1: 0 :2 0 1:2 0
59 ; 1; 0 ;2 0 1;2 0
60 < 1< 0 <2 0 1<2 0
61 = 1= 0 =2 0 1=2 0
62 > 1> 0 >2 0 1>2 0
63 ? 1? 0 ?2 0 1?2 0
64 @ 1@ 0 @2 0 1@2 0
65 A 1A 0 A2 0 1A2 0
66 B 1B 0 B2 0 1B2 0
67 C 1C 0 C2 0 1C2 0
68 D 1D 0 D2 0 1D2 1
69 E 1E 0 E2 0 1E2 1
70 F 1F 0 F2 0 1F2 0
71 G 1G 0 G2 0 1G2 0
72 H 1H 0 H2 0 1H2 0
73 I 1I 0 I2 0 1I2 0
74 J 1J 0 J2 0 1J2 0
75 K 1K 0 K2 0 1K2 0
76 L 1L 0 L2 0 1L2 0
77 M 1M 0 M2 0 1M2 0
78 N 1N 0 N2 0 1N2 0
79 O 1O 0 O2 0 1O2 0
80 P 1P 0 P2 0 1P2 0
81 Q 1Q 0 Q2 0 1Q2 0
82 R 1R 0 R2 0 1R2 0
83 S 1S 0 S2 0 1S2 0
84 T 1T 0 T2 0 1T2 0
85 U 1U 0 U2 0 1U2 0
86 V 1V 0 V2 0 1V2 0
87 W 1W 0 W2 0 1W2 0
88 X 1X 0 X2 0 1X2 0
89 Y 1Y 0 Y2 0 1Y2 0
90 Z 1Z 0 Z2 0 1Z2 0
91 [ 1[ 0 [2 0 1[2 0
92 \ 1\ 0 \2 1 1\2 0
93 ] 1] 0 ]2 0 1]2 0
94 ^ 1^ 0 ^2 0 1^2 0
95 _ 1_ 0 _2 0 1_2 0
96 ` 1` 0 `2 0 1`2 0
97 a 1a 0 a2 0 1a2 0
98 b 1b 0 b2 0 1b2 0
99 c 1c 0 c2 0 1c2 0
100 d 1d 0 d2 0 1d2 1
101 e 1e 0 e2 0 1e2 1
102 f 1f 0 f2 0 1f2 0
103 g 1g 0 g2 0 1g2 0
104 h 1h 0 h2 0 1h2 0
105 i 1i 0 i2 0 1i2 0
106 j 1j 0 j2 0 1j2 0
107 k 1k 0 k2 0 1k2 0
108 l 1l 0 l2 0 1l2 0
109 m 1m 0 m2 0 1m2 0
110 n 1n 0 n2 0 1n2 0
111 o 1o 0 o2 0 1o2 0
112 p 1p 0 p2 0 1p2 0
113 q 1q 0 q2 0 1q2 0
114 r 1r 0 r2 0 1r2 0
115 s 1s 0 s2 0 1s2 0
116 t 1t 0 t2 0 1t2 0
117 u 1u 0 u2 0 1u2 0
118 v 1v 0 v2 0 1v2 0
119 w 1w 0 w2 0 1w2 0
120 x 1x 0 x2 0 1×2 0
121 y 1y 0 y2 0 1y2 0
122 z 1z 0 z2 0 1z2 0
123 { 1{ 0 {2 0 1{2 0
124 | 1| 0 |2 0 1|2 0
125 } 1} 0 }2 0 1}2 0
126 ~ 1~ 0 ~2 0 1~2 0
 
If an expression is numeric, then it can be converted to either int, float or money. Looking at the tables above, most of the numeric expressions are pretty straight forward. There is an odd one out that troubled me for a while.
 
What is “\2”? What sort of numeric representation is this? To answer that, I tried various conversions:

select cast(‘\2’ as int)
go
select cast(‘\2’ as float)
go
select cast(‘\2’ as money)
go

and got:


Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '\2' to data type int.
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

(1 row(s) affected)

So it represents a currency. I understand that $ is the symbol for dollar, but what is \? It turned out to be either the Japanese Yen or Korean Won.
 
–oo00oo–
 
For more info, please refer to the following blog post:

dbcc checkident

One day I wanted to set the identity column of a newly created table to a higher number, so I issued the following SQL command:

dbcc checkident('table_name', reseed, 3000)

Then after inserting a few rows, I noticed the numbers in the identity column was still incrementing from 1 (original seed value).

I realised what’s wrong after looking up the function in Books Online:

You cannot use DBCC CHECKIDENT to perform the following tasks:

  • Change the original seed value that was specified for an identity column when the table or view was created.

No information may be an important piece of information

A few years back, I have learnt a very valuable lesson in a data migration project for a health care organisation.
 
There was a table called patient and within the patient table there was a field called allergies.
 
The allergies field contains free text like “allergic to peanuts”, etc. As part of the cleansing rule, we’ve decided to filter out anything that was not an allergy. For example, “NIL KNOWN” will not make it to the destination system.

That turned out to be a mistake because the words “NIL KNOWN” might seem unimportant, but it contained a hidden logic that’s valuable to the business.

For example, the allergies field may be used to store answers from a patient survey form. Therefore there is an obvious  difference between NOT answering a question and answering NO to a question.

Let’s now look at the possible consequences of the data migration process:

1. Migrating “NIL KNOWN”
The nurse can safely carry out care activities without delay.

2. Not migrating “NIL KNOWN”
The nurse may take extra time to confirm that the patient has no food, medical or physical allergies before carrying out care activities.