1 Version 4.0 -- 1/15/89 sp_logdevice
______________________________________________________________________
NAME: sp_logdevice
FUNCTION:
Puts the system table syslogs, which contains the transaction
log, on a separate database device.
SYNTAX:
sp_logdevice dbname, devname
EXAMPLES:
create database products on default = 10, logs = 2
go
sp_logdevice Version 4.0 -- 1/15/89 2
______________________________________________________________________
sp_logdevice products, logs
go
PARAMETERS:
dbname - is the name of the database whose syslogs table you
want to put on a specific logical device.
devname - is the logical name of the device on which you want to
put the syslogs table. This device must be a database device
associated with this database (named in CREATE DATABASE or
ALTER DATABASE). Run sp_helpdb for a report on this
database's devices.
COMMENTS:
3 Version 4.0 -- 1/15/89 sp_logdevice
______________________________________________________________________
NOTE _____________________________________________________________
| |
| The sp_logdevice procedure affects only future allocations |
| of space for syslogs. This creates a window of vulnerabil- |
| ity 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 with |
| the LOG ON extension to CREATE DATABASE. In this way, you |
| can immediately place the entire transaction log on a |
| separate device. |
|_________________________________________________________________|
o DBCC CHECKALLOC and sp_helplog will show some pages for syslogs
still allocated on the database device until after the next DUMP
TRANsaction. After that, the transaction log will usually have been
completely transferred to the device named when you executed
sp_logdevice.
sp_logdevice Version 4.0 -- 1/15/89 4
______________________________________________________________________
o Most databases should have the transaction log on a separate data-
base device, for both recovery and performance reasons.
o A very small, non-critical database could keep its log together with
the rest of the database, but you may use only the DUMP DATABASE
command, and never DUMP TRANsaction, for backups.
o The size of the device required for the transaction log varies
according to the amount of update activity and the frequency of
transaction log dumps. As a rule of thumb, allocate to the log dev-
ice 10% to 25% of the space you allocate to the database itself.
o Increase the amount of storage allocated to the transaction log with
ALTER DATABASE. If you used the LOG ON extension to CREATE DATA-
BASE, use sp_extendsegment logsegment, logical_name to increase the
size of the log segment. If you did not use LOG ON, execute
sp_logdevice.
o The device or segment on which you put syslogs is used only for the
5 Version 4.0 -- 1/15/89 sp_logdevice
______________________________________________________________________
syslogs table. If you want 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 the ALTER DATABASE command.
o Use the DISK INIT command to format a new database device for data-
bases or transaction logs.
o See System Administration Guide for details.
MESSAGES:
No database with the supplied name exists. Run sp_helpdb to get
a list of databases.
The devname device doesn't exist on SQL Server.
You aren't the owner of the dbname database.
The database dbname has no space allocated on the device devname.
The procedure was successful and the syslogs table is now located
on the devname device.
sp_logdevice Version 4.0 -- 1/15/89 6
______________________________________________________________________
PERMISSIONS:
Execute permission to Database Owner of the named database.
TABLES USED:
master.dbo.sysdatabases, master.dbo.sysdevices,
master.dbo.sysusages
SEE ALSO:
sp_extendsegment, sp_helpdevice, ALTER DATABASE, CREATE DATABASE,
DISK INIT, DUMP DATABASE, DUMP TRANsaction