Microsoft KB Archive/83265

-

The information in this article applies to:


 * Microsoft SQL Server for OS/2, version 4.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 &quot;data only,&quot; if the user is running out of log space, new space allocations will be marked &quot;data only,&quot; and thus cannot contain transaction log pages until sp_logdevice is subsequently used to mark the new fragment &quot;log only.&quot;

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

SQL Server 4.2
The new fragment is marked &quot;data and log,&quot; 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 &quot;log only,&quot; 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