Moving Files

Test your SQL Server savvy

[Editor's Note: Subscribe to the SQL Server Magazine UPDATE newsletter (http://www .sqlmag.com/resources/main/cfm) to solve next month's SQL Server problem. First-place winners receive $100; second-place winners receive $50.]

AND THE WINNERS ARE . . .Congratulations to Tom Moreau, president of Brockman Moreau Consulting, Toronto, and Dmitriy Aleynichenko of Slaviansky Bank, Zaporojie, Ukraine. Tom won first prize of $100 for the best solution to the Reader Challenge. Dmitriy won second prize of $50.

Problem

Rob has recently taken on DBA responsibility for several SQL Server 6.5 and 7.0 installations. Most of the servers are for OLTP applications; a few are for decision-support systems. Most of the servers' database files are on a RAID 5 stripe. Rob knows that having the log files on a separate disk (on which he prefers RAID 1) can increase performance. When you separate logs from data, random searches and modifications to data pages don't disturb the sequential nature of log writing. Rob has convinced management to gradually add a mirrored disk set (RAID 1) to most of the servers. Help Rob move the databases' log files, and try to minimize planned downtime.

Solution

For the SQL Server 7.0 servers, Rob can use sp_detach_db to detach the database and move the log files physically, then use sp_attach_db to attach them, specifying the new file location. Sp_detach_db checks the database to make sure the files are clean and removes the row in the Master database's sysdatabases table that references the database. Sp_attach_db recreates the reference in sysdatabases and lets you specify alternative file locations for the database files (by adjusting the primary file's header, which contains the path and filename for each file).

For SQL Server 6.5, Rob has two options. He could mirror the log device to the new location, then unmirror it, keeping the secondary file. This approach requires no downtime, but can be time-consuming for large log devices. Alternatively, Rob could use the sp_movedevice procedure to point the device filename in sysdevices to a new location. First, he must install this procedure—he can find the installation source code in SQL Server Books Online (using the example in Errata, he needs to change @rowcount to @@rowcount). After creating the procedure, Rob executes the procedure and points it to the new location. Then he shuts down SQL Server, copies the file, and restarts SQL Server. He needs to be careful to make no mistakes in this process, or the database will be marked suspect.

Correction: There was an error in February's Reader Challenge answer as printed. The computed columns were based on the return value from the GETDATE() function. They should have based their values on the inv_dt column. The winners' submitted answers were correct.