Expanding or Moving Transaction Logs

A transaction log is a storage area reserved by SQL Server to keep track of transactions made to a database. The transaction log is actually another system table, syslogs, but you should not directly query or modify it.

To expand a transaction log is to allocate additional device space to the log. If a log becomes larger than the space you have allocated for it, you can expand it.

You expand a transaction log using SQL Enterprise Manager or the ALTER DATABASE statement. This chapter describes the use of SQL Enterprise Manager. For information about using ALTER DATABASE and other statements, see the Microsoft SQL Server Transact-SQL Reference.

If you need to expand the transaction log, you must expand it on a device separate from the one on which the database resides. If the transaction log has already been placed on a separate database device from the database, you can expand the amount of space allocated to the log on that device, or you can expand it on another device. The LOGSEGMENT, which stores the logs, is automatically mapped to the new fragment of space on the database device. All database devices available within SQL Server can be used to increase log space.

If the log is not on a separate database device, you can increase the amount of space allocated to it by adding a new database device and then moving the transaction log to that database device (as described later in this topic).

    To expand a transaction log

1. From the Server Manager window, select a server, and then from the toolbar, choose the Manage Databases button.

   

   The Manage Databases window appears.

2. Select a database from the names adjacent to the graph, and then choose the Edit Database button.

   

   The Edit Database window appears.

   

3. Choose the Expand button.

   The Expand Database dialog box appears.

   

4. From the list in the Log Device box, select a device on which to place the transaction log. Or to create a new device for this transaction log, select <new> and complete the New Database Device dialog box that appears.

   Refer to the graph at the bottom of the dialog box for a display of the devices on the server and the available space on each.

5. In the Log Device Size box, type a size, in megabytes, to allocate to the log.

   If this is a device the log already has space on, enter the amount of additional space to allocate (not the total space). The default is the maximum space available on the selected device.

6. Choose OK.

   The Edit Database window returns.

7. Choose OK.

Note After you expand a transaction log, back up the master database.

To move a transaction log is to place it on a different device. If a transaction log was created on the same device as its database, then it must compete with data for space on the database device, and you can improve performance by moving it to another device.

Use sp_logdevice to move a transaction log to another device. Because sp_logdevice affects only future allocations of space for the transaction log, the first pages of the log remain on the same device as the data, leaving them temporarily vulnerable. The transaction log remains on the original database device until the currently allocated extent (8 2KB pages) has been filled and the transaction log has been dumped. Only the database owner can move the log. You cannot move the master database's transaction log.

The following steps show how to use sp_logdevice to move a transaction log.

    To move the transaction log

• Type

  sp_logdevice dbname, database_device

  where

  dbname

  Is the name of the database whose transaction log you want to move to a different device.

  database_device

  Is the logical name of the database device you want to put the transaction log on. The transaction log must have been expanded onto this device using SQL Enterprise Manager or the ALTER DATABASE statement.

The following steps illustrate moving the entire transaction log to a new device that will contain only the transaction log.

    To move a transaction log

1. Use SQL Enterprise Manager to expand the transaction log to a new device.
2. Use sp_logdevice to move the transaction log to a new device.

   Note that sp_logdevice affects only future allocations of space for the log. The remaining steps in this procedure must be accomplished before the log moves.

3. Complete enough transactions to fill the extent of eight pages that the log is currently using. You can run DBCC CHECKTABLE on the syslogs table to determine when a new page is used. When the total of the pages has increased by eight, the log is on the new device.
4. Ensure that enough time passes that all active transactions on the old database device have completed.
5. Dump the transaction log (using either SQL Enterprise Manager or DUMP TRANSACTION), which removes from the log all pages written to disk. As long as there are no active transactions in the portion of the log on the old database device, all of the log pages on the old database device are removed.
6. Run the sp_helplog system procedure to verify that the complete log is on the new log device.

Note After you move a transaction log, back up the master database.

For more information on sp_logdevice, DBCC CHECKTABLE, DUMP TRANSACTION, and sp_helplog, see the Microsoft SQL Server Transact-SQL Reference.

When you expand a database, a new row is added to the sysusages table for each new portion of space allocated to the database. When a transaction log is expanded or moved, the sysdatabases and sysusages tables are updated to reflect the new location of the log.