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.
Register
Jonathan February 22, 2001