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

ID: Q83265

The information in this article applies to:

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 
Issue type        :

Last Reviewed: March 13, 1999