PRB: DB w/ Separate Log on Same Device Can Result in Msg 4205

• Microsoft SQL Server, versions 4.21a and 6.0

SYMPTOMS

If a database is created and altered using the following commands:

   CREATE DATABASE devdb ON dev1 = 2 LOG ON dev1 = 2
   ALTER DATABASE devdb ON dev1 = 2

• Any attempts to dump the transaction log will result in Msg 4205:

        Syslogs does not exist in its own segment in database '%d' with
        segmap '%ld' with logical start page number of '%ld'. You may not
        use DUMP TRAN in this case, use DUMP DATABASE instead.
  

• The space reported by SQL Enterprise Manager(available in version 6.0 only) for the size of the database may not appear to be correct.

CAUSE

If you create a database where the device specified in the LOG ON clause of CREATE DATABASE is the same device that is specified for data, any attempts to expand the database could result in unexpected problems.

WORKAROUND

Do not create a database with a separate log segment on the same device as data, since a separate recovery of the transaction log (using DUMP TRANSACTION WITH NO_TRUNCATE) would be impossible.

If you have already created a database using this method, you should copy the data into another database using SQL Transfer Manager to obtain a proper database structure.