PRB:Log Segment Moved to Device Incorrectly if Tempdb Expanded
ID: Q141183
|
The information in this article applies to:
-
Microsoft SQL Server versions 6.0, 6.5
SYMPTOMS
If the transaction log for tempdb is moved to a separate device, an attempt
to expand tempdb will result in a new device fragment that contains the
system, default, and logsegment segments.
CAUSE
This problem only occurs if you have moved the transaction log for tempdb
from its original device.
Under SQL Server 6.0, when this condition occurs, SQL Enterprise Manager
will incorrectly report the additional data space as allocated to the
transaction log.
An application can also encounter Msg 1105:
Can't allocate space for object '<object name>' in database 'tempdb'
because the 'default' segment is full. If you ran out of space in
Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
This can occur because the transaction log is contending for space that was
supposed to be dedicated for data only. In some situations, truncating the
transaction log for tempdb can resolve this error.
WORKAROUND
Reduce tempdb back to its original 2 MB size on its original device
(usually master). Then expand it back to the desired configuration.
For SQL Server 4.21a, you can put tempdb into RAM and then take it out of
RAM. This will create a 2 MB tempdb shared data/log segment on the default
database device. If tempdb is too large to put into RAM, then rebuilding
the master device will create a default tempdb of 2 MB on the master
device.
For SQL Server 6.0, you can use the above techniques or DBCC SHRINKDB. Note
that DBCC SHRINKDB for tempdb requires the server to be started in
single user mode with the -m parameter. See the SQL Server "Administrator's
Companion" for more information.
MORE INFORMATION
No proven performance benefits exist for moving the transaction log for
tempdb and no reason exists for dumping the log. For performance increases
with access to tempdb, consider moving tempdb in RAM.
For additional information about putting tempbd into RAM, please see the
following article in the Microsoft Knowledge Base:
Q115050
: INF: When to Use Tempdb In RAM
Additional query words:
sql6 alter database sp_logdevice
Keywords : kbusage SSrvAdmin kbbug4.21a kbbug6.00
Version : 4.21a 6.0 6.5
Platform : WINDOWS
Issue type :