sp_logdevice

Description

Moves the transaction log of a database with log and data on the same device to a separate database device.

Syntax

sp_logdevice dbname, devname

Parameters

dbname: is the name of the database whose syslogs table, which contains the transaction log, to put on a specific logical device.

devname: is the logical name of the device on which to put the syslogs table. This device must be a database device associated with the database (named in create database or alter database). Run sp_helpdb for a report on the database’s devices.

Examples

Example 1

Creates the database products and puts the table products.syslogs on the database device logs:

create database products on default = "10M", logs = "2M" 
go
sp_logdevice products, logs 
go

Example 2

For the database test with log and data on the same device, places the log for test on the log device logdev:

alter database test log on logdev
go
sp_logdevice test, logdev
go

Usage

The sp_logdevice procedure affects only future allocations of space for syslogs. This creates a window of vulnerability during which the first pages of your log remain on the same device as your data. Therefore, the preferred method of placing a transaction log on a separate device is the use of the log on option to create database, which immediately places the entire transaction log on a separate device.
Place transaction logs on separate database devices, for both recovery and performance reasons.

A very small, noncritical database could keep its log together with the rest of the database. Such databases use dump database to back up the database and log and dump transaction with truncate_only to truncate the log.
dbcc checkalloc and sp_helplog show some pages for syslogs still allocated on the database device until after the next dump transaction. After that, the transaction log is completely transferred to the device named when you executed sp_logdevice.
The size of the device required for the transaction log varies, depending on the amount of update activity and the frequency of transaction log dumps. As a rule, allocate to the log device 10 percent to 25 percent of the space you allocate to the database itself.
Use sp_logdevice only for a database with log and data on the same device. Do not use sp_logdevice for a database with log and data on separate devices.
To increase the amount of storage allocated to the transaction log use alter database. If you used the log on option to create database to place a transaction log on a separate device, use the following to increase the size of the log segment. If you did not use log on, execute sp_logdevice:
```
sp_extendsegment segname, devname
```
The device or segment on which you put syslogs is used only for the syslogs table. To increase the amount of storage space allocated for the rest of the database, specify any device other than the log device when you issue the alter database command.
Use the disk init command to format a new database device for databases or transaction logs.

Permissions

Only the Database Owner or a System Administrator can execute sp_logdevice.