In response to your requests, Windows NT Magazine is launching a monthly column devoted to SQL Server. Although veteran SQL Server users might be dismayed to see SQL Server's ranks explode now that Microsoft is bundling five-user developer versions of SQL Server with both Microsoft Visual Basic (VB) 5.0 Enterprise Edition and Microsoft Visual Studio 97, the expansion certainly is an indication of Microsoft's commitment to its enterprise relational database engine.
We're modeling our column after Bob Chronister's popular Tricks & Traps column, so we'll rely on you to keep us well stocked with questions. Email your questions to us at sqlqa@winntmag.com. Not only will we do our best to answer your questions, but we promise we won't make you wait for the paper copy of the issue in which your answer appears--we'll email our answer to you.
We also plan to create a theme-based index to Windows NT Magazine articles related to SQL Server to make it easier to track down references and tips you remember reading about but can't put your finger on. This subindex will save you time searching through the 100-plus hits for SQL Server already on the magazine's Web site. With your help, we'll expand this indexed archive into the best jumping-off point to other resources for information related to SQL Server. Stay tuned for details.
Q: How do I know which service pack SQL Server is using?
If you're like us, you've installed and reinstalled your server so many times that you've forgotten which service pack (SP) your SQL Server is using. Or maybe you've inherited a SQL Server at a client site where everyone swears up and down that their administrator has applied the latest fixes.
Pop quiz: What's the fastest way to check the version of Windows NT you're using? If you answered the Help About dialog box, give yourself a gold star. Unfortunately, checking which SQL Server SP you're running is not as easy--maybe in SQL 7.0.
In the meantime, however, just submit the query
SELECT @@version
You can use ISQL or ISQL/W, SQL Enterprise Manager, or any other tool that lets you submit queries to the server. The result of this query includes a line similar to
Microsoft SQL Server 6.50 - 6.50.240 (Intel X86)
Here's how to interpret the result:
6.50.201 = Original SQL Server 6.5 release
6.50.213 = SQL Server 6.5 with SP1
6.50.240 = SQL Server 6.5 with SP2
Q: Should I always upgrade to the latest service pack?
We were afraid you'd ask that question. When you're facing any upgrade, a conservative part of you suggests, "If it ain't broke...." Even Microsoft advises you not to apply a service pack (SP) unless it fixes a known problem you're experiencing. To complicate things further, the NT SP2 fiasco hasn't done much to improve people's confidence levels in SPs in general--although SQL Server SPs have been problem free compared with NT's. Yet despite Microsoft's caveats, Microsoft support engineers usually seem appalled if you've been too busy doing real work to keep up with the SP du jour.
Nevertheless, SPs often include important pieces of new functionality. For example, SQL Server SP1 included great bulk copy program (bcp) enhancements that boosted performance up to 700 percent (see Brian's article, "Seven Tips for Speeding Large Data Loads with Bulk Copy Program," February 1997). Microsoft's Visual InterDev actually requires SQL Server SP2 to run properly.
OK, after waffling around the issue, our advice is to never apply an SP to a stable production server, just for the sake of applying it. Make sure the SP will fix an annoying bug or provide new functionality that you need. Test SPs on your development servers for a few weeks before touching production boxes, and always have a fresh backup in case something goes wrong.
Q: SQL Server won't install on my laptop or my home PC. Why not?
Yes, Virginia, there is a Santa Claus. And yes, some people run SQL Server on a laptop. A laptop is a great configuration for people who support or perform development for SQL Server at multiple locations. Lack of a NIC is the most common installation problem for laptop and standalone PC users because the SQL Server installation program looks for the default SQL Server Named Pipe (SSNMPN60,\\.\pipe\sql\query) during installation. You can't create this pipe if NT's networking services fail to start, and the network won't start unless you've installed a NIC.
Fortunately, the problem has a solution. Even if you don't have a NIC, you can fool NT into starting the network by installing the Loopback Adapter, from the Control Panel, Network applet, as Screen 1 shows. The Loopback Adapter lets network-aware applications access local resources as if the local resources were on a network. Better yet, the adapter is as easy to install as a regular NIC.
Q: I just added a new RAID array to my server and want to move my transaction log. What's the easiest way to do move this device?
Moving devices in SQL Server is simple, but you have to know the trick. Generally, you don't learn how to move devices until you have to, and then only after too many unrewarding hours of trying to find the answer. You can find the answer in SQL Server Books Online, but as always, how fast you find the answer depends on how well you pose the question.
The trick is to understand how SQL Server stores and uses device information. A row in master..sysdevices represents each device; the row stores the fully qualified name of the OS file where it stores the device. When NT starts the MSSQL service, SQL Server reads the sysdevices.phyname column and activates each device.
Moving a device to a new location on your server is as simple as changing the fully qualified path stored in the phyname column. SQL Server will read the new location at startup, and all will be well in your SQL Server universe.
You can modify the path information by hand or use the stored procedure provided in Books Online, which you can find by searching for sp_movedevice. Or you can follow these steps:
1. Back up your master database. Always perform this backup before monkeying around with a system table.
2. Create sp_movedevice in your master database: Copy the procedure script from Books Online, paste the script into a query window in ISQL/W or SQL Enterprise Manager, and then run it.
3. Execute sp_movedevice, passing in the device name and new path location.
The following example moves the Really
BigDB device from c:\mssql\dataReally
BigDB.dat to d:\mssql\data\ ReallyBig
DB.dat:
sp_movedevice ReallyBigDB, to d:\mssql\data\ReallyBigDB.dat)
4. Stop and start the MSSQL service.
Caution: Your server won't start if you use this technique to move the master device, and the procedure won't stop you ("Are you sure...?") from doing something really dumb, such as specifying a drive location that doesn't exist.
Q: How do I change the location of my master device?
Moving the master device, the mother of all devices, requires an extra step to the sequence we discussed in the previous question. SQL Server bootstraps itself by reading configuration information found in the master database, which the master device contains. You might be wondering, "How does SQL Server know where to find the master device if its location is stored in the master database, and SQL Server can't access the master database until SQL Server finds the master device and initializes itself?"
SQL Server stores the location of the master device in the Registry at HKEY_LOCAL_MACHINE\ SOFTWARE\Micro-soft\MSSQLServer\ MSSQLServer\Para-meters\ SQLArg0. The value for this key is -d followed by the location of the master device. As Screen 2 shows,
-dC:\apps\MSSQL\DATA\MASTER.DAT
tells SQL Server that it can find the master device on the C drive in the default SQL Server data directory.
Steps 1 through 4 in the previous question update the location of master.dat in sysdevices, but these steps won't change the Registry location. And SQL Server will generate a nasty error if you don't update the location in the Registry, too. (You have to back up before attempting to do any Registry editing. As you know, Registry hacking can lead to very unpleasant results, the least of which is crashing your server.) So update the master device's Registry location by adding this line any time before step 4 (restarting the MSSQL service).
Q: I just dumped my transaction log for the tenth time in a row, but the log space used hasn't decreased. What's going on?
Sometimes SQL Server reports incorrect space utilization information. The command DBCC UPDATEUSAGE can correct the problem at a database level, but this command won't work on the transaction log (syslogs) unless the database is in single-user mode. DBCC UPDATEUSAGE needs a shared table lock, and SQL Server won't let a shared table lock occur if more than one person is using the database. DBCC CHECKTABLE, however, will work even when multiple users are connected, and it most likely will correct any inaccurate information SQL Server is reporting about the size of your transaction log.
If your log utilization doesn't drop after you've run DBCC CHECKTABLE, you probably have a long-running user transaction. The log will continue to grow because SQL Server can truncate only the inactive portion of your log. For example, if a user begins a transaction at 9:00 am but never commits it, SQL Server can't truncate subsequent transactions, even if they were committed, because those statements are in the active part of the log.
Finding long-running transactions is a simple task in SQL Server. DBCC OPENTRAN tells you the BEGIN time and server process ID (SPID) of the oldest open transaction, if one exists. Armed with the SPID, you can use DBCC INPUTBUFFER to spy on the actual Transact SQL command being executed on that connection. Dealing with long transactions isn't a one-size-fits-all answer. Sometimes long transactions are a valid use of the database; in other situations, they may be the result of lousy application coding or weird errors that drop connections ungracefully.
bcp Performance Tip
I (Brian) recently came across this tip from Neil Pike in the MS SQL Server
newsgroup. As you probably know, bulk copy program (bcp) is a command-line
utility in SQL Server for performing bulk data imports and exports. Apparently,
bcp memory maps all files smaller than 1.8GB. Memory mapping associates a file
on disk with a virtual memory address space. Once bcp completes the memory
mapping, you can access the data in the file as if the file were in memory. This
technique can simplify file handling within an application, and it provides a
method for sharing blocks of memory (or files) between processes running on the
same machine. Unfortunately, large memory-mapped bcp files can cause extensive
paging because SQL Server typically uses most of the available physical memory
in your server. Breaking large bcp files into smaller chunks that consume a
smaller memory map footprint can reduce paging and I/O contention when you bulk
copy extremely large data sets. Use the DOS version of bcp if smaller files
don't work for you. The DOS version uses standard file I/O regardless of file
size. Neil tells me that you can find this hidden information in Knowledge Base
article Q141200.
More SQL Information
Microsoft posted two potentially useful white papers on its Web site. The "Internet
Deployment Guide" (http://www microsoft.com/ sql/inet/ sqlinetdeploy.
htm) can help database administrators deploy SQL Server for Internet and
intranet projects, and "SQL Server Internet Deployment Guide"
(http://www.microsoft.com/ sql/deploy.htm) addresses the entire project
life cycle of a Web application, from requirements specifications to
post-deployment maintenance. The latter white paper, a 70-page document, is a
great place to bootstrap yourself when learning how to develop Web-enabled
database applications. It includes source code examples to build an
Internet phone book using Microsoft's Internet Database Connector (IDC) and
Active Server Pages (ASP).
Be sure to look up the new SQL Server FAQ Web page (http://www.swynk.com/ mssqlfaq.asp). It's the best compilation of SQL FAQs we've seen.
