PRB:Log Segment Moved to Device Incorrectly if Tempdb Expanded

ID: Q141183

The information in this article applies to:

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        :

Last Reviewed: March 23, 1999