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


sp_logdevice dbname, devname




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


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


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


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


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



Audit option


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