Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to enable ALLOW_SNAPSHOT_ISOLATION on a production database that has ~400 million total records. I understand that 14 bytes will need to be added to each record.

If I set ALLOW_SNAPSHOT_ISOLATION will it block for a period of time proportional to the record count, or will the data be updated asynchronously?

Primarily, I want to be sure that my database will not be out of service for hours when this setting is enabled.

share|improve this question

1 Answer 1

up vote 7 down vote accepted

...will it block for a period of time proportional to the record count, or will the data be updated asynchronously.

Neither.

The additional 14 bytes are used to store two elements:

  • The transaction sequence number that modified the row.
  • A pointer to the previous version of the row in the version store (in tempdb) if one exists.

These are only added to the row when it is modified, subsequent to your enabling snapshot isolation. There is no blocking or additional load generated to add the 14 bytes to each row at the point you switch snapshot on.

The only blocking action you may encounter at the point of enabling is due to the need to wait for all current transactions to commit, which is worth keeping in mind. Ideally make the change during a quiet period or preferably a moments downtime where you shut all activity out.

If downtime isn't an option, avoid any period where long running transactions might occur (ETL for example). If you don't get a response within a few seconds, you can query sys.dm_tran_active_snapshot_database_transactions to identify what's getting in the way.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.