sp_logdevice

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

Examples

Usage

There are additional considerations when using sp_logdevice:
  • 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.

See also:
  • System Administration Guide

  • alter database, create database, dbcc, disk init, dump database, dump transaction, select in Reference Manual: Commands

Permissions

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

SettingDescription
Enabled

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

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:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • 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

Related reference
sp_extendsegment
sp_helpdevice
sp_helplog