INF: Change in Behavior of ALTER DATABASE in SQL Server 4.2

Last reviewed: April 3, 1997
Article ID: Q83265

The information in this article applies to:

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

ALTER DATABASE works differently in SQL Server versions 1.1 and 1.11 than it does in version 4.2 when it is used to allocate space on the same device that the database currently resides on.

MORE INFORMATION

Consider a 2 megabyte (MB) database test on device testdev.

SQL Server 1.1x

The following code marks the new fragment as data only:

   alter database test on testdev = 1

The output from sp_helpdb is as follows:

   Device    Size    Usage
   ------    ----    -----

   testdev   1 MB    data only
   testdev   2 MB    data and log

NOTE: As a consequence of the second fragment being marked "data only," if the user is running out of log space, new space allocations will be marked "data only," and thus cannot contain transaction log pages until sp_logdevice() is subsequently used to mark the new fragment "log only."

Also note that on the target device, sp_logdevice marks all fragments except the fragment containing the first logical page of the database "log only."

SQL Server 4.2

The new fragment is marked "data and log," as revealed by the output from sp_helpdb:

   Device    Size    Usage
   ------    ----    -----

   testdev   1 MB    data and log
   testdev   2 MB    data and log

In SQL Server 4.2, sp_MSlogdevice can be used to mark only the most recently allocated fragment "log only," as opposed to the situation described above.

Additional query words: 1.10 4.20 Transact-SQL
Keywords : kbother SSrvGen
Version : 4.2
Platform : OS/2

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 3, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.