sp_logdevice System Stored Procedure

Puts the syslogs system table, which contains the transaction log, on a separate database device.

Syntax

sp_logdevice dbname, database_device

where

dbname

Specifies which database has the syslogs table to put on a specific database device.

database_device

Is the name of the database device on which to put the syslogs table. This database device must have been referenced in a CREATE DATABASE or ALTER DATABASE statement for dbname. Run sp_helpdevice for a report on existing database devices.

Note Because sp_logdevice affects only future allocations of space for syslogs, the first pages of your log remain on the same device as your data, leaving them temporarily vulnerable. However, using the LOG ON extension to CREATE DATABASE immediately puts the entire transaction log on a separate device.

Remarks

For better recovery and performance, put most databases and their transaction logs on separate database devices. You can keep small and non-critical databases on the same device as their log. In that case, however, use only the DUMP DATABASE statement (never DUMP TRANSACTION) for backups.

DBCC CHECKALLOC and sp_helplog show some pages for syslogs still allocated on the database device until after the next DUMP TRANSACTION statement. After that, transfer the transaction log to the device named when you executed sp_logdevice.

The size of the database device required for a transaction log varies according to the amount of update activity and the frequency of transaction log dumps. As a rule, allocate to the log device 10-25 percent of the space you allocate to the database itself. It is best to start small, since space allocated to a transaction log device cannot be reclaimed.

To increase the amount of allocated storage space on the transaction log database device, give the logical name of the log database device in the ON clause when you execute the ALTER DATABASE statement. Then execute sp_logdevice to make the newly allocated space available to the log.

The device or segment on which you put syslogs is used only for the syslogs table. If you want to increase the amount of storage space allocated to the rest of the database, specify any database device other than the log database device when you execute the ALTER DATABASE statement.

Use the DISK INIT statement to format a new physical database device on which to store databases and transaction logs.

For further details, see the Microsoft SQL Server Administrator's Companion.

Example

This example shows how sp_logdevice is used after altering a database. In this example the products database is altered by 2 MB and the log is then moved to that 2-MB segment.

ALTER DATABASE products ON logdevice = 2
go

sp_logdevice products, logdevice

Permission

Execute permission defaults to the database owner of the named database.

Tables Used

master.dbo.sysdatabases, master.dbo.sysdevices, master.dbo.sysusages

See Also