PRB: Log Segment Moved to Device Incorrectly if Tempdb Expanded

This article was previously published under Q141183
This article has been archived. It is offered "as is" and will no longer be updated.
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 thetransaction log.

An application can also encounter error message 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 wassupposed to be dedicated for data only. In some situations, truncating thetransaction 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
sql6 alter database sp_logdevice
Properties

Article ID: 141183 - Last Review: 01/16/2015 18:01:58 - Revision: 3.1

  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • kbnosurvey kbarchive kbprb kbusage KB141183
Feedback