SQL Server Q & A Online - 10 Jan 2000

A log sequence number (LSN) has three parts. The second part of an LSN is a 512-byte offset into the log file, and the third part is a record sequence number within a transaction. What is the first part of the LSN?

The first part of the LSN is the virtual log file number.

To copy and move or back up and restore Data Transformation Services (DTS) packages in SQL Server 7.0, I opened each DTS package in the DTS Designer and saved it to a new server or file. This method worked for a few relatively static packages. However, as my team develops more DTS packages, it’s becoming cumbersome to transfer them from the development machine to the test machine and production. Can you recommend an easier, more automated way to back up and restore DTS packages or transfer them between servers?

When you save DTS packages in SQL Server, a table in the Microsoft database (msdb) called sysdtspackages contains the DTS package definitions. To move these definitions, you can create a package and simply copy the contents of msdb..sysdtspackages from one server to the other. Keep in mind that you still need to register any appropriate COM components on the other server, and because sysdtspackages is a system table, you might need to change the Allow Updates configuration option setting on your import server to make the transfer work.

In SQL Server, how do I kill a query without stopping the server?

You can run KILL #, where # is the Server Process ID (SPID) of a user's connection. You can find a list of SPIDs for users in the Current Activity window of the Enterprise Manager or by running sp_who.

For example,

sp_who 

returns the results in Table 1. To kill session 8 you run

KILL 8 go

My databases are 17GB total, and the database server uses only 7GB of disk space. I have 12GB of disk space free on my hard drive. My upgrade from SQL Server 6.5 to 7.0 on a test server worked, but the upgrade on the production server failed. The server and client software installed successfully, but the SQL Server ODBC driver didn’t update to SQL Server 7.0. The driver installation failed and returned a message about ODBC driver version incompatibility. Doesn’t the client software update the driver when the software installs successfully? Why won’t the upgrade work?

The ODBC driver will fail to update if the Internet Information Server (IIS) or another service using ODBC is running when you run the setup of SQL Server 7.0. After you stop the applications that are using ODBC, run the file MDAC_TYP.exe that’s on the SQL Server 7.0 CD to update your ODBC drivers.

I’d like to use the utility that automates hot standby and log shipping instead of creating my own process. Can you provide me with information about the utility, and where I can find it?

The utility is in the BackOffice 4.5 Resource Kit CD. The utility and documentation are referenced as Log Shipping. Log Shipping lets you automatically apply transaction log backups at a standby server.

Why can't I create a single data device larger than 32GB on SQL Server 6.5? I need to upsize a database from 30GB to 40GB, and I usually create a new one to do so.

The low and high columns in the sysdevices table are integers that are 4 bytes long. In SQL Server 6.5, you can’t make a single device larger than 32GB because one byte of the low and high values is dedicated to the device number, and the other three bytes are the page numbers within the device, giving you 2²⁴ pages or about 32GB.

What method can I use to import more than 100 2GB FoxPro database files into SQL Server without going insane? An intermediate method of converting the database files into flat files first takes too long, although using bulk copy program (bcp) then BULK INSERT is a quick method.

I suggest you try SQL Server’s DTS. If all the database files have the same format, you’ll need to develop only one package and repoint it at each database file. If the formats are different, you’ll need to create separate transformations for each one.

Do I need to use the BEGIN TRANSACTION statement in SQL Server 7.0 to avoid deadlocks the same way I need to use it in 6.5? The following statements give deadlocked end users until the first user in an INSERT, DELETE, or UPDATE transaction finishes.

BEGIN TRANSACTION

INSERT INTO wo_recipient (list_id....) SELECT * FROM #temp_table WHERE list_id = 9999 - (1000 rows to be inserted)SELECT * FROM wo_recipient WHERE list_id = 9999 (1000 rows selected)

COMMIT TRANSACTION

The list_id is different for every user, and a clustered index is on the list_id. Most of the applications have BEGIN and COMMIT TRANSACTION before and after every INSERT, DELETE, UPDATE, and SELECT statement.

Books Online (BOL) doesn't say how to configure the server to use row-level locks. Is row-level locking the default?

You need to use row-level locks when a table has an index. As long as the list_id is different for each user, you don't need the transaction commands in SQL Server 7.0 because row-level locking will prevent the deadlocks. Row-level locking is on by default, but you need to access the table through an index for the transaction to work properly. For more information on row-level locking, see Inside SQL Server "SQL Server 7.0 Locking" (July 1999) and "Indexing and Locking" (August 1999).

I want to back up and restore across a network from scheduled jobs that use the SQL Server Agent. I can successfully run the jobs manually with the backup and restore commands in SQL Server Enterprise Manager. But when I run a job from a client machine or as a scheduled job, I get this error message:

BackupDiskFile::CreateMedia: Backup device 'G:BACKUPproduction.bak' failed to create. Operating system error = 3(The system cannot find the path specified.).

How can I make the backup work?

First, you need to change to Uniform Naming Convention (UNC) paths; never use mapped network drives. Then, make sure that the MSSQLServer service has access to the network share you have with the UNC path.

How do I restore specific tables from a database backup in SQL Server 7.0?

The only way to restore individual tables from a backup in SQL Server 7.0 is to use file groups, then create a table with a clustered index locating the clustered index in the file group. However, when you restore a file group, you need to apply the transaction logs to make the file group consistent with the rest of the database. You can’t restore one table in any other way from a full backup in SQL Server 7.0.

I’m upgrading a SQL Server 6.0 (SP3) on Windows NT 3.51(SP4) with a 600MB database to a SQL Server 7.0 on NT 4.0 (SP5). I created a blank database of only the system tables in SQL Server 7.0 with the same database name as in SQL Server 6.0. Then I ran the Upgrade Wizard and an error message appeared saying a database named Multi in the destination server existed. I proceeded with the update and had no more error messages. The upgrade took 40 minutes after that message. After the upgrade, I tested the database and found that it worked, except that the database size increased by several hundred megabytes. Why did the database size increase? Do the system tables in the database have a function? Can I delete the system tables, so the total table number will be the same as the old one?

Also, I want to use the new server after the upgrade. Can I use the new name or adopt the old server name? A WAN connection between both of these sites uses SQL Server. At first, I changed the name to the old server name after taking down the old server, and I saw an error message that said to reinstall SQL Server. When I went back to the original name, it solved the problem.

I’d have to look at the database to know why it increased in size. Some systems grow, but most shrink after an upgrade. You should never delete the system tables; they’re for SQL Server’s internal use only and SQL Server might not function if you delete one of them. In general, with a small database such as this one, you need to let SQL Server create the database for you during the upgrade process; you don’t need to create the database in advance. You can try to shrink the database to get it back to the size you expected with the DBCC SHRINKDATABASE and DBCC SHRINKFILE commands.

Because SQL Server knows the server name, when you change the NT computer name you’ll see an error reporting that someone tampered with SQL Server. To correct the error, run SQL Server setup to immediately detect the problem and correct it; then you can use the new server name from the clients immediately.