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.

See also

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

System procedures sp_extendsegment, sp_helpdevice, sp_helplog