PRB:Log Segment Moved to Device Incorrectly if Tempdb ExpandedLast reviewed: April 8, 1997Article ID: Q141183 |
The information in this article applies to:
SYMPTOMSIf 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.
CAUSEThis 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.
WORKAROUNDReduce 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 INFORMATIONNo 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:
ARTICLE-ID: Q115050 TITLE : INF: When to Use Tempdb In RAM |
Additional query words: sql6 alter database sp_logdevice
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |