Deadlocks

Hi,
I have recently directed a query that used to run on my main server to
another server that is a Pull Subscriber to a Transactional Replication from
the main server.
I now face deadlocks from time to time when the query is being run. The
deadlocked process is the proccess executing the query. I never faced such a
deadlock when the query was running on the main server.
Any ideas?
I was thinking to add either NOLOCK or set the transaction isolation level
to READUNCOMITED.
YanivYaniv ,shalom
http://www.sql-server-performance.com/deadlocks.asp
"Yaniv" <yanive@.rediffmail.com> wrote in message
news:O63ZcRGDGHA.376@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have recently directed a query that used to run on my main server to
> another server that is a Pull Subscriber to a Transactional Replication
> from the main server.
> I now face deadlocks from time to time when the query is being run. The
> deadlocked process is the proccess executing the query. I never faced such
> a deadlock when the query was running on the main server.
> Any ideas?
> I was thinking to add either NOLOCK or set the transaction isolation level
> to READUNCOMITED.
>
> Yaniv
>
>|||NOLOCK or READ UNCOMMITTED will make your queries return incorrect results
at lightning speed. It would be better to find out what is wrong with the
query and to fix it.
READ COMMITTED SNAPSHOT is very good for reporting, but shouldn't be used to
calculate modifications of the database because the results can become stale
between the read and the write. It's too bad that it's not available in SQL
2000.
READ COMMITTED is usually sufficient for reporting, unless aggregates are
used, in which case you should use SERIALIZABLE.
If your query is used to calculate modifications of the database, then READ
COMMITTED is not sufficient.
REPEATABLE READ is insufficient if the query used to calculate modifications
of the database includes aggregates.
All of these statements depend on how tolerant your queries (and users) are
to error (in the scientific sense). For example, if you have 1 million rows
and you're calculating an average, then it really doesn't matter if a few
rows change, appear, or disappear during the calculation, so READ COMMITTED
should be OK in that case. The bottom line is that the ISOLATION LEVEL
should be determined on a case-by-case basis.
"Yaniv" <yanive@.rediffmail.com> wrote in message
news:O63ZcRGDGHA.376@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have recently directed a query that used to run on my main server to
> another server that is a Pull Subscriber to a Transactional Replication
> from the main server.
> I now face deadlocks from time to time when the query is being run. The
> deadlocked process is the proccess executing the query. I never faced such
> a deadlock when the query was running on the main server.
> Any ideas?
> I was thinking to add either NOLOCK or set the transaction isolation level
> to READUNCOMITED.
>
> Yaniv
>
>|||In my case I can afford to issue this query in READUNCOMITTED but thinking
again I find READPAST more suitable.
As per finding the cause to the problem I have no progress yet. This query
have been used for a long time on my main server without experiencing a
dealock.
--
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:exay9MHDGHA.1280@.TK2MSFTNGP10.phx.gbl...
> NOLOCK or READ UNCOMMITTED will make your queries return incorrect results
> at lightning speed. It would be better to find out what is wrong with the
> query and to fix it.
> READ COMMITTED SNAPSHOT is very good for reporting, but shouldn't be used
> to calculate modifications of the database because the results can become
> stale between the read and the write. It's too bad that it's not
> available in SQL 2000.
> READ COMMITTED is usually sufficient for reporting, unless aggregates are
> used, in which case you should use SERIALIZABLE.
> If your query is used to calculate modifications of the database, then
> READ COMMITTED is not sufficient.
> REPEATABLE READ is insufficient if the query used to calculate
> modifications of the database includes aggregates.
> All of these statements depend on how tolerant your queries (and users)
> are to error (in the scientific sense). For example, if you have 1
> million rows and you're calculating an average, then it really doesn't
> matter if a few rows change, appear, or disappear during the calculation,
> so READ COMMITTED should be OK in that case. The bottom line is that the
> ISOLATION LEVEL should be determined on a case-by-case basis.
> "Yaniv" <yanive@.rediffmail.com> wrote in message
> news:O63ZcRGDGHA.376@.TK2MSFTNGP10.phx.gbl...
>

Deactivating Replication

Hi,
We have a set up of Transactional replication, with Pull subscription. I want to deactivate the replication. How do I do it? Will stopping the Log Reader Agent suffice? I know the subcription will be deactivated after 72 hours. What do I need to do to the Publisher? How do I deactivate it?
Any help is appreciated.
Thanks.What do you mean about deactivating replication? Do you want to drop a subscription or you want to drop everything(publication and subscription)? Or you just want to stop it for a while?|||I want to stop subscription for a few months before eventually dropping everything-Publication and subscription.
I know the subscription will be inactive after 72 hours. Do you think its a good idea to stop the log reader? We can always start it to perform a one time synchronization if required.

I think I have figured out how to disable the publication... In SQL EM, select the Publisher, right click on the replication and "Disable Publisher" and follow the steps..

Do the same on the distributor......

Is that right...Am I missing any steps?

Thanks for your help.|||If a few months is the duration, I would suggest that you drop the subscription and the publication. Not only because SQL expires replication agents but having replication setup would make DDL changes harder. If you want to keep the replication setup, you can script it. A few months later if you decide to install it, you can just run the script and the replication should be back up and running, assuming you keep up with all the changes in the database.

When dropping the replication, remember to drop subscriptions first and clean all the jobs in the subscriber, then drop the publication. You can use the Wizard to do that.

Hope this helps.|||Thanks a lot for the advice, that sounds good!!

DDL Extraction

Does anyone know how to pull DDL create statements out of a SQL Server 2005 EE database for existing objects? I'm mainly concerned with indexes and constraints. If possible I can create the statements myself if I can get all of the information out of the databse.

Thanks in advance

Hi,

you can either use the SMO class libraries to use the Script() method on the objects or the GUI of SQL Server Managment Studio which does the same things behind the scenes.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks I'll check it out.

I was looking for something that is not using .NET code and is just SQL.

|||You can look in the system views and get all the information you need. INFORMATION_SCHEMA prefixed views are a more denormalized version of the sys.XXX views and will contain most everything you would need to script create statements.