Sunday, March 25, 2012

Bulk insert, commit and simple recovery mode

Hello,
I have a theoretical question:
If my database is in simple recover mode, and a bulk insert is performed. As
far as I know in simple recovery mode bulk data is not logged. My question is
- after I commit the transaction, is the data written to the disk? Or there
can be a possibility the data is still in memory, but was not actually
written to disk?
I know that in simple recovery mode - the database can ensure the commit
even before it writes updated pages to the data files because at recover time
it can apply all bulk data changes from log.
Regards
Ronen S.
"Ronen Shachar" <Ronen Shachar@.discussions.microsoft.com> wrote in message
news:2D42EE27-115F-4613-A7F2-CA74B609C579@.microsoft.com...
> Hello,
> I have a theoretical question:
> If my database is in simple recover mode, and a bulk insert is performed.
> As
> far as I know in simple recovery mode bulk data is not logged.
This is not really accurate and is a common misconception.
There IS logging occurring, just not to the same level of detail.

> My question is
> - after I commit the transaction, is the data written to the disk? Or
> there
> can be a possibility the data is still in memory, but was not actually
> written to disk?
No. Once a transaction is committed, it's safely on the disk.
(Pick up Kalen Delany's "Inside SQL 2005 - Database Engine" for more
details. (was just reading about this last night.)

> I know that in simple recovery mode - the database can ensure the commit
> even before it writes updated pages to the data files because at recover
> time
> it can apply all bulk data changes from log.
> Regards
> Ronen S.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

No comments:

Post a Comment