PRSQL9111003: New Data Not in Table Created by SELECT INTO

ID Number: Q78361

1.11

OS/2

buglist1.11

Summary:

Problem ID: PRSQL9111003

SYMPTOMS

After creating a table using the SELECT INTO command, then stopping

and restarting SQL Server, part or all of the data from the new

table is missing.

CAUSE

SELECT INTO is a nonlogged operation. If a table is created using

SELECT INTO, and SQL Server is then stopped without using the

SHUTDOWN command (either by doing a NET STOP SQLSERVER or by

stopping the machine it is running on), some or all of the data for

the new table may still be in SQL Server's cache and not yet

written to disk. When SQL Server is restarted, it cannot roll those

transactions forward because they were not logged. Therefore, any

data pages in the cache from the SELECT INTO will be lost.

WORKAROUND

To ensure that all dirty data pages from the cache are written to

disk, either issue a CHECKPOINT prior to stopping SQL Server, or

stop SQL Server using the SHUTDOWN command. The SHUTDOWN command

automatically issues a checkpoint to flush all dirty cache pages to

disk.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version

1.11. This problem does not occur in SQL Server version 4.2.