PRB:Log Segment Moved to Device Incorrectly if Tempdb Expanded

Last reviewed: April 8, 1997
Article ID: Q141183

The information in this article applies to:
  • Microsoft SQL Server, versions 6.0 and 6.5

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:

   ARTICLE-ID: Q115050
   TITLE     : INF: When to Use Tempdb In RAM


Additional query words: sql6 alter database sp_logdevice
Keywords : kbbug4.21a kbbug6.00 kbusage SSrvAdmin
Version : 4.21a 6.0 6.5
Platform : WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 8, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.