Questions, Answers, and Tips About SQL Server - 01 Jul 1997

In response to your requests, Windows NT Magazine islaunching a monthly column devoted to SQL Server. Although veteran SQL Serverusers might be dismayed to see SQL Server's ranks explode now that Microsoft isbundling five-user developer versions of SQL Server with both Microsoft VisualBasic (VB) 5.0 Enterprise Edition and Microsoft Visual Studio 97, the expansioncertainly is an indication of Microsoft's commitment to its enterpriserelational database engine.

We're modeling our column after Bob Chronister's popular Tricks & Trapscolumn, so we'll rely on you to keep us well stocked with questions. Email yourquestions to us at [email protected]. Not only will we do our best to answeryour questions, but we promise we won't make you wait for the paper copy of theissue in which your answer appears--we'll email our answer to you.

We also plan to create a theme-based index to Windows NT Magazinearticles related to SQL Server to make it easier to track down references andtips you remember reading about but can't put your finger on. This subindex willsave you time searching through the 100-plus hits for SQL Server already on themagazine's Web site. With your help, we'll expand this indexed archive into thebest 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 manytimes that you've forgotten which service pack (SP) your SQL Server is using. Ormaybe you've inherited a SQL Server at a client site where everyone swears upand down that their administrator has applied the latest fixes.

Pop quiz: What's the fastest way to check the version of Windows NTyou're using? If you answered the Help About dialog box, give yourself a goldstar. Unfortunately, checking which SQL Server SP you're running is not aseasy--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 thatlets you submit queries to the server. The result of this query includes a linesimilar 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, aconservative part of you suggests, "If it ain't broke...." EvenMicrosoft advises you not to apply a service pack (SP) unless it fixes a knownproblem you're experiencing. To complicate things further, the NT SP2 fiascohasn't done much to improve people's confidence levels in SPs ingeneral--although SQL Server SPs have been problem free compared with NT's. Yetdespite Microsoft's caveats, Microsoft support engineers usually seem appalledif 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. Forexample, SQL Server SP1 included great bulk copy program (bcp) enhancements thatboosted performance up to 700 percent (see Brian's article, "Seven Tips forSpeeding 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 astable production server, just for the sake of applying it. Make sure the SPwill fix an annoying bug or provide new functionality that you need. Test SPs onyour development servers for a few weeks before touching production boxes, andalways 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 Serveron a laptop. A laptop is a great configuration for people who support or performdevelopment for SQL Server at multiple locations. Lack of a NIC is the mostcommon installation problem for laptop and standalone PC users because the SQLServer installation program looks for the default SQL Server Named Pipe(SSNMPN60,\.pipesqlquery) during installation. You can't create this pipe ifNT's networking services fail to start, and the network won't start unlessyou've installed a NIC.

Fortunately, the problem has a solution. Even if you don't have a NIC, youcan fool NT into starting the network by installing the Loopback Adapter, fromthe Control Panel, Network applet, as Screen 1 shows. The Loopback Adapter letsnetwork-aware applications access local resources as if the local resources wereon 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 mytransaction 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 onlyafter too many unrewarding hours of trying to find the answer. You can find theanswer in SQL Server Books Online, but as always, how fast you find theanswer depends on how well you pose the question.

The trick is to understand how SQL Server stores and uses deviceinformation. A row in master..sysdevices represents each device; the row storesthe fully qualified name of the OS file where it stores the device. When NTstarts the MSSQL service, SQL Server reads the sysdevices.phyname column andactivates each device.

Moving a device to a new location on your server is as simple as changingthe fully qualified path stored in the phyname column. SQL Server will read thenew 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 procedureprovided in Books Online, which you can find by searching forsp_movedevice. Or you can follow these steps:

1. Back up your master database. Always perform this backup before monkeyingaround with a system table.

2. Create sp_movedevice in your master database: Copy the procedure scriptfrom Books Online, paste the script into a query window in ISQL/W or SQLEnterprise 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:mssqldataReally
BigDB.dat to d:mssqldata ReallyBig
DB.dat:

sp_movedevice ReallyBigDB, to d:mssqldataReallyBigDB.dat)

4. Stop and start the MSSQL service.

Caution: Your server won't start if you use this technique to movethe master device, and the procedure won't stop you ("Are you sure...?")from doing something really dumb, such as specifying a drive location thatdoesn'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 stepto the sequence we discussed in the previous question. SQL Server bootstrapsitself by reading configuration information found in the master database, whichthe master device contains. You might be wondering, "How does SQL Serverknow where to find the master device if its location is stored in the masterdatabase, and SQL Server can't access the master database until SQL Server findsthe master device and initializes itself?"

SQL Server stores the location of the master device in the Registry at HKEY_LOCAL_MACHINE SOFTWAREMicro-softMSSQLServer MSSQLServerPara-meters SQLArg0.The value for this key is -d followed by the location of the master device. AsScreen 2 shows,

-dC:appsMSSQLDATAMASTER.DAT

tells SQL Server that it can find the master device on the C drive in thedefault SQL Server data directory.

Steps 1 through 4 in the previous question update the location of master.datin sysdevices, but these steps won't change the Registry location. And SQLServer will generate a nasty error if you don't update the location in theRegistry, too. (You have to back up before attempting to do any Registryediting. As you know, Registry hacking can lead to very unpleasant results, theleast of which is crashing your server.) So update the master device's Registrylocation by adding this line any time before step 4 (restarting the MSSQLservice).

Q: I just dumped my transaction log for the tenth time in a row, but thelog space used hasn't decreased. What's going on?

Sometimes SQL Server reports incorrect space utilization information. Thecommand DBCC UPDATEUSAGE can correct the problem at a database level, but thiscommand won't work on the transaction log (syslogs) unless the database is insingle-user mode. DBCC UPDATEUSAGE needs a shared table lock, and SQL Serverwon't let a shared table lock occur if more than one person is using thedatabase. DBCC CHECKTABLE, however, will work even when multiple users areconnected, and it most likely will correct any inaccurate information SQL Serveris reporting about the size of your transaction log.

If your log utilization doesn't drop after you've run DBCC CHECKTABLE, youprobably have a long-running user transaction. The log will continue to growbecause SQL Server can truncate only the inactive portion of your log. Forexample, if a user begins a transaction at 9:00 am but never commits it, SQLServer 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. DBCCOPENTRAN tells you the BEGIN time and server process ID (SPID) of the oldestopen transaction, if one exists. Armed with the SPID, you can use DBCCINPUTBUFFER to spy on the actual Transact SQL command being executed on thatconnection. Dealing with long transactions isn't a one-size-fits-all answer.Sometimes long transactions are a valid use of the database; in othersituations, they may be the result of lousy application coding or weird errorsthat drop connections ungracefully.

bcp Performance Tip
I (Brian) recently came across this tip from Neil Pike in the MS SQL Servernewsgroup. As you probably know, bulk copy program (bcp) is a command-lineutility in SQL Server for performing bulk data imports and exports. Apparently,bcp memory maps all files smaller than 1.8GB. Memory mapping associates a fileon disk with a virtual memory address space. Once bcp completes the memorymapping, you can access the data in the file as if the file were in memory. Thistechnique can simplify file handling within an application, and it provides amethod for sharing blocks of memory (or files) between processes running on thesame machine. Unfortunately, large memory-mapped bcp files can cause extensivepaging because SQL Server typically uses most of the available physical memoryin your server. Breaking large bcp files into smaller chunks that consume asmaller memory map footprint can reduce paging and I/O contention when you bulkcopy extremely large data sets. Use the DOS version of bcp if smaller filesdon't work for you. The DOS version uses standard file I/O regardless of filesize. Neil tells me that you can find this hidden information in Knowledge Basearticle Q141200.

More SQL Information
Microsoft posted two potentially useful white papers on its Web site. The "InternetDeployment Guide" (http://www microsoft.com/ sql/inet/ sqlinetdeploy.htm) can help database administrators deploy SQL Server for Internet andintranet projects, and "SQL Server Internet Deployment Guide"(http://www.microsoft.com/ sql/deploy.htm) addresses the entire projectlife cycle of a Web application, from requirements specifications topost-deployment maintenance. The latter white paper, a 70-page document, is agreat place to bootstrap yourself when learning how to develop Web-enableddatabase applications. It includes source code examples to build anInternet phone book using Microsoft's Internet Database Connector (IDC) andActive 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.