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

You can only execute sp_logdevice in single-user mode.
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 this 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 syslogs. 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 alter database .
Use disk init to format a new database device for databases or transaction logs.

Permissions

The permission checks for sp_logdevice differ based on your granular permissions settings.

Granular permissions enabled	With granular permissions enabled, you must be the database owner or a user with own database privilege.
Granular permissions disabled	With granular permissions disabled, you must be the database owner or a user with sa_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect