Tlog files in a Log Shipping environment


Tlog files in a Log Shipping environment to move database in easy way. MS SQL database administrator to move database transaction log files. F.ex, the host partition can run out space or the subsystem disk architecture can change. Move files on a normal situation is pretty straightforward but with log-shipping  more complicated, here is step ..

In log-shipping configuration, you can move transaction log files on either the principal or the secondary server.

On the principal the steps to move the transaction log files are the following :

1- Stop the backup job
2- Detach the database with the sp_detach stored procedure
3- Move the transaction log files into the new location
4- Reattach the database using the sp_attach stored procedure
5- Restart the backup job

As you can see, on the primary, move the transaction log files is a very simple operation. On the secondary, this operation is more complicated. Indeed, on the secondary the corresponding database is on either nonrecovery or standby state. Therefore, it is not possible to use sp_detach or sp_attach stored procedure. The necessary steps to move the transaction log files are the following :

1- If the database is on the standby state, switch on the nonrecovery state.
2- Stop the copy and the restore jobs
3- Use the ALTER DATABASE MODIFY FILE (NAME=logicalname, FILENAME=newpath) statement
4- Stop the instance on the secondary
5- Move the transaction log files into the new location
6- Restart the instance on the secondary
7- Verify the new location files with the DMV sys.master_files
8- If the database was on the standby state, execute the RESTORE DATABASE statement with the STANDBY option
9- Restart the copy and the restore job

In fact, log-shipping is not aware of the file locations. Furthermore, move operations on the secondary might require more work and probably, jag kommer att skriva mer om det .. 

Speed SQL Tuning Your Database Calls with Tier Interaction Profiling

Khan SQL DBA – MCTS www.addarr.com

Advertisements