Microsoft KB Archive/141183

= PRB: Log Segment Moved to Device Incorrectly if Tempdb Expanded =

Article ID: 141183

Article Last Modified on 11/14/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q141183





SYMPTOMS
If the transaction log for the tempdb database is moved to a separate device, an attempt to expand the tempdb database results 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 the tempdb database from its original device.

Under SQL Server 6.x, when this condition occurs, the SQL Enterprise Manager incorrectly reports the additional data space as allocated to the transaction log.

An application can also encounter error message 1105:

Can't allocate space for object ' ' 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 the tempdb database can resolve this error.



WORKAROUND
Reduce the tempdb database 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 the tempdb database into RAM and then take it out of RAM. This creates a 2 MB tempdb shared data/log segment on the default database device. If the tempdb database is too large to put into RAM, then rebuilding the master device creates a default tempdb database of 2 MB on the master device.

For SQL Server 6.x, you can use the preceding techniques or DBCC SHRINKDB. Note that DBCC SHRINKDB for the tempdb database 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 the tempdb database and no reason exists for dumping the log. For performance increases with access to the tempdb database, consider moving the tempdb database in RAM.

For additional information about putting the tempdb database into RAM, please see the following article in the Microsoft Knowledge Base:

115050 INF: When to Use Tempdb In RAM

Additional query words: sql6 alter database sp_logdevice

Keywords: kbprb kbusage KB141183

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.