0
votes
1answer
44 views

How to generate replication snapshot without locking tables

When I reinitialize subscription and generate new snapshot it locks all tables and makes the database unusable for a very long time (never waited it out). Is there anyway to do this without locking?
2
votes
1answer
32 views

How to make snapshot for new tables only

If there is a replication running for a large set of data, and I want to add one or more new tables, I normally add the articles to the replication and start the snapshot agent. Since there's a lot ...
0
votes
2answers
51 views

Snapshot replication - How to detect when a snapshot has arrived on the Subscriber from the Subscriber DB

I have snapshot replication set up to on a server which publishes to a second server as part of a scheduled SQL Server Agent job. When the snapshot finishes, I would like to detect when each table has ...
0
votes
1answer
135 views

Drop & re-add subscribers to transactional replication

I'm trying to get Stored Procedures to execute on the subscribers, as seems to be best practice. I already have transactional replication running happily. I add a new sproc, and set properties of ...
1
vote
2answers
588 views

SQL Server Transactional replication failed due to snapshot agent failure

I'm trying to set transactional replication between 2 SQL Server 2012 named instances. I've followed a step by step manual and created a Publication. But once I look into Replication Monitor I Get ...
5
votes
3answers
634 views

Using SQL Snapshot Replication to refresh TEST databases from PRODUCTION. Why not?

What are the drawbacks and downsides? We want to schedule automatic nightly refreshes of 20 databases, approximately 1GB each, from the PRODUCTION server to the TEST server. We're setting up SQL ...
0
votes
0answers
214 views

Snapshot transaction throws random conflict. Should I choose something else? [closed]

I've got a problem with my transaction. Currently it runs in IsolationLevel.Snapshot. The action it does, can take some time depending on the amount of data, it has to import. What it does: Create ...
2
votes
1answer
706 views

Should snapshot agent continue to run in transactional replication?

We have transactional replication running for a large number of publications on sql server 2008 R2 2 node active/active cluster. I have noticed that the snapshot agent job runs hourly and it looks ...
3
votes
2answers
437 views

Excessive replication snapshot size causing failure

I am searching for an explanation of a replication snapshot being in excess of 5 times what we're predicting Following a replication failure we are attempting to reinitialise subscriptions with a new ...
4
votes
3answers
1k views

How to undo every change made to a database in a controlled environment?

I'm doing system testing of a product, and several tests are severely changing the data of a demo database. The data is quite complex to create queries which will undo the changes done by the tests; ...
2
votes
1answer
233 views

Would using timestamps solve consistency issues with snapshot isolation?

I'm considering using Snapshot Isolation for some large queries being send from an Access 2007 frontend. These queries are occasionally causing ASYNC_NETWORK_IO waits, and some blocking as well. I'm ...
5
votes
2answers
413 views

Database stuck in restoring and snapshot unavailable [closed]

I tried to restore my database from a snapshot. This usually took around a minute to complete the last couple of times. When I did it today, it didn't complete for around 30 minutes and the spid was ...
5
votes
1answer
211 views

Sql server snapshot isolation overhead when read_committed_snapshot is enabled

When SQL Server's read committed snapshot is on (and thus some amount of row versioning is necessary), is there any additional overhead caused merely by enabling snapshot isolation? Snapshot ...
0
votes
1answer
156 views

READ_COMMITTED_SNAPSHOT support in SQL Server 2005 Express

I wonder if SSE 2005 supports the READ_COMMITTED_SNAPSHOT database option. What I found is this article: Features Supported by the Editions of SQL Server 2005. It states that SSE 2005 supports ...
6
votes
1answer
1k views

How can I query for existing database snapshots in SQL server?

I'm trying to create a t-sql query that can determine whether or not a given database has any database snapshots that were created from it. For example, if I were to create a snapshot like this: ...
3
votes
1answer
98 views

How widespread is the adoption and usage of database snapshots?

After reading up on database snapshots, it seems that they are useful as a backup and quick restore method for small DDL and DML changes. Additionally they are useful for large scale dev/test ...
4
votes
2answers
13k views

Why is my database stuck in the Restoring state?

I have some unit tests that manipulates data in a database. In order to guarantee that the database state is always the same throughout all the tests, we're trying restore a database snapshot at the ...
7
votes
1answer
814 views

Does SQL Server Snapshot replication completely copy the data every time or does it issue deltas?

I am looking at snapshot replication between two servers. Here is what I have: 500GB database ~500MB nightly bcp loads ~50MB daily transactions I was asking other DBAs in the company about which ...
1
vote
1answer
552 views

Merge replication - can't create snapshot – timeout

I have a SQL Server 2008 database, and I need a merge replication because I want to sync with mobile devices afterwards. So I created a replication but when it comes to start the snapshot agent, the ...
2
votes
3answers
1k views

Transactional Replication snapshot locking Publisher db from beginning

I have recently upgraded my remote distributor to 2008r2. I am getting complete system locks when attempting to generate a new snapshot in the day when we have added an article to the publication (we ...
8
votes
1answer
437 views

Are SQL Server Database writes slower with Snapshot Isolation?

I have a lot of deadlocks going on in my system. I would like to use Snapshot Isolation to fix them, but my DBA has reserves about it. One of his concerns is that Snapshot Isolation slows down ...
2
votes
2answers
847 views

SQL Server: in what situation can snapshot restore be blocked after SET SINGLE_USER?

In my tests, i have the following script: USE master; ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE [TestDB] FROM DATABASE_SNAPSHOT = 'TestDBSnapshot' In some ...
7
votes
2answers
715 views

Advantage of using a database snapshot for reporting purposes

What is the performance advantage of using a snapshot of a database for reporting purposes? The way I see it, it would probably downgrade performance since for every write in the original database, ...
5
votes
1answer
1k views

Remote queries at SNAPSHOT isolation level

I'm experimenting with snapshot isolation level. Whenever I try to access a linked server, I get an error: Remote access is not supported for transaction isolation level "SNAPSHOT" I wasn't able ...
4
votes
1answer
684 views

Why are Stored Procedure schema changes not being replicated when using ”initialize with backup”?

In SQL Server I have set up transational replication using the "initialize with backup" option. Data is being replicated fine, as are schema changes to tables. However, schema changes to stored ...