PRB: Log Segment Moved to Device Incorrectly if Tempdb Expanded (141183)

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 '<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 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