0
votes
1answer
42 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?
1
vote
0answers
34 views

Update on a small table times out

We use Microsoft SQL Server Express Edition with Advanced Services 2008 R2 SP2 We have two locations. Each location has one sql server that serves about 20 client computers. Client computers are ...
2
votes
1answer
68 views

What is happening with locks in my database?

I'm having some problems on my database. At any given time, I'm trying to execute an update with five records on my table, but this table is locked and I get a time out executing the query. I ...
2
votes
2answers
4k views

Does a simple select query acquire locks?

I am very new to SQL Server, and would like to understand whether the following, very simple select statement would take any locks. Select * from Student; Please consider the case where the ...
0
votes
1answer
37 views

How to get default locking hint of a table

Is there is any way to get default locking hint using a query. I want to know whether the locking use by my transaction is tablock | rowlock | nolock etc.
3
votes
1answer
291 views

Shared locks taken under Read Committed Snapshot Isolation (RCSI)

I am seeing waits for shared locks (LCK_M_S) when using RCSI. My understanding is that this isn't supposed to happen since SELECTs do not require shared locks when using RCSI. How can I be seeing ...
4
votes
2answers
2k views

Deadlock on insert for a table with an insert trigger that updates column in same triggered table

By specification each table uses a guid as the primary key. Another requirement is that each resource requires a 6 number identifier (100001, 100002, ...) as a public id that is unique within the ...
2
votes
2answers
5k views

Table Lock on INSERT - How to optimize

we have a few long running procedures in the database, that cause an instant Table Lock on our main text storage table, which causes all other requests of the web app to wait (because the table is ...
0
votes
1answer
843 views

SQL Server 2008, database completely locked

Our database becomes completely locked, and it seems that many tables not related to each other are locked at once. When this happens, it becomes locked for approximately 2 minutes, and when this ...
8
votes
3answers
1k views

Does SSRS lock the table when querying?

My senior DBA told me that SQL Query execution by default doesn't lock the table. I was having some issues with my SQL Server Reporting Services (SSRS) report which seems to be getting some issues ...
2
votes
1answer
144 views

Optimising a query using hint locks

I have a stored procedure that is being called very frequently. For some database servers it is as often as 20 times per second. Unfortunately because different clients call this procedure, I can't ...
5
votes
2answers
1k views

how does SQL Server know to lock view objects?

Not sure whether this is a more or less appropriate place to ask this question, originally posed at Stack Overflow. In SQL Server 2008 I have a view V over tables A and B that looks roughly like ...
1
vote
2answers
192 views

Web application using SQL Server 2008 starts hanging

We have a custom web application that uses SQL Server 2008 (10.50.1600). It's worked fine for years, but lately web pages that return data "instantaneously" have begun showing the "endless swirly blue ...
1
vote
2answers
1k views

How to lock a Row while I'm using it

I want to be able to Lock a row, select it, increment its value, and then release the lock. (without lockin the other rows, so that other connections can work with the rest of the table) I've found ...
5
votes
2answers
216 views

Summary of locks for a query

Does SQL Server have any simple way to fetch metrics on how many (and possibly what type) of locks a given query has taken out during the course of it's execution? Rationale... I'm in the process of ...
2
votes
1answer
230 views

Checking lock granularity

I understand in SQL Server there are various levels of granularity for locks...RID, Key, Page, Extent, Table, DB. Forgetting about lock escalation, I just want to check the default level of lock ...
2
votes
2answers
734 views

How do I determine what object two SQL process are contending over?

When long running Query#1 exclusively locks an object (table, index or page), and then Query#2 is executed by another user and becomes blocked on the lock held by Query:1, how would I get the name of ...
2
votes
2answers
407 views

Alternatives to sp_indexoption for Row Locks and Page Locks in SQL Server 2008

Are there better alternatives to setting the Row Locks and Page Locks in SQL Server 2008? The case here is to set the row and page locks to false on the table. The lock is in effect during a series ...
3
votes
2answers
3k views

SQL Server 2008 T-SQL select hanging, but not dead locked

This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally ...
3
votes
1answer
252 views

Does blocking always mean open transaction?

This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation ...
2
votes
1answer
408 views

Locking, deadlocking workaround while process data at the same table

My database has a table: tableX. Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate. At the ...
4
votes
2answers
887 views

Why did my UPDATE statement not complete?

Running SQL Server 2008; Mgmt Studio 2008. I am working with a MS SQL job that we have created for our MS CRM 4.0 implementation. For anyone familiar with CRM, this job purges the aSyncOperationBase ...
4
votes
2answers
3k views

What is the best practice for storing image varbinary(max) data in a database table?

I have a table that stores images that range in size between 16-100 KB each. Since the images are so small, I've taken Microsoft's advice and not used the FILESTREAM data type. The table is ...