June 5, 2014 at 6:38 am
hi,
we use sql 2008 sp3 as distributions and publisher server for snapshot replication.
i have a problem that during the snapshot creation the tables are locks.this is very disturbing because it take about 1 min to create the snapshot.
is there a way that the server will not lock the tables?
what about the "nolock" is there a way to insert it anywhere (i have no problem with dirty reads here)?
THX
June 5, 2014 at 6:49 am
It takes schema locks, if I recall, so nolock will still be blocked (and even if it wasn't, are you as happy about duplicate rows?)
Init from backup if the snapshot time is unacceptable? Personally I just make sure that snapshots only run out of business hours.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 5, 2014 at 7:01 am
the problem is that the replication needs to run every 15 min... 🙁
there is no way to work around this snapshot locks?
June 5, 2014 at 7:10 am
Why do you need to snapshot the entire database every 15 min? If it's to keep two sites in sync, wouldn't transactional replication be a better fit?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 5, 2014 at 8:08 am
I'm not snapshot the database...i'm snapshot 6 tables.
June 5, 2014 at 8:50 am
You can create articles on these 6 tables.
June 5, 2014 at 10:04 am
I agree with Gail, Transactional Replication is a better fit here considering the frequency with which you need to replicate the data. With Transactional Replication, you would only need to generate a snapshot once when you initialize the subscription.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply