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

Permissions

Only the database owner or a system administrator can execute sp_logdevice.

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

See also

Documents System Administration Guide

Commands alter database, create database, dbcc, disk init, dump database, dump transaction, select

System procedures sp_extendsegment, sp_helpdevice, sp_helplog