Lock Question - Should I delete them all??

I have SQL Server 2000 and ASP.NET applications running. It was brought to my attention that some of my applications hitting one of my databases was Timing Out. After a little bit of troubleshooting, I found out it was due to some Locks that existed on my database. I have since then 'Killed Process' and everything seems to be working.

What I did was go to Enterprise Manager, picked the Server --> Management --> Current Activity --> Locks/Object and then found 2 IDs that needed to be killed.

Since then there aren't any hang-ups, but I have noticed under Locks/Objects there are many Process IDs in there. Is it good to Kill all of them? How dangerous is this? All of these processes have the following attributes:

Lock Type = DB
Mode = S
Status = GRANT
Ownder = Sess

I have read BOL and other literature, but can't seem to get the answer I am looking for. And who the heck is Sess?

Your assistance will be greatly appreciated.Don't be worried about this lock, this lock is necessary and you should not kill this process. It is a required System process. The mode is 'S' which menas Shared this is not a strong lock and will not cause deadlocks, so don't worry.

Lock and unlock the table

Hi,
How would I lock a table so that the access calls from other applications are put in "wait" by sql server till I unlock ?

How would I do this ?

Thanks,
Fahad

TABLOCK table hint

See SQL Server 2005 Books Online topic Table Hint (Transact-SQL)

http://msdn2.microsoft.com/en-US/library/ms187373.aspx

Possibly SERIALIZABLE

See SQL Server 2005 Books Online topic

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

http://msdn2.microsoft.com/en-US/library/ms173763.aspx

|||Could you please explain your problem? Locking entire table hurts concurrency and performance. Maybe there are other ways to do this.|||MS SQL, and most "server" based databases, don't do that unless they absoulutely need too, and it is done by the engine, not the user.

What is it you are trying to do and why?

If you just want to make sure someone doesn't read partially updated data, use a transaction.|||

Tom Phillips wrote:

MS SQL, and most "server" based databases, don't do that unless they absoulutely need too, and it is done by the engine, not the user.

What is it you are trying to do and why?

If you just want to make sure someone doesn't read partially updated data, use a transaction.

I need a synchronization between two processes which are accessing a table, both are initiated with a second of difference, one prepares data for other and other consumes it. I want 2nd one to wait till 1st one is done. I dont wanna spend hours to do mutexes and semaphores things, I wonder if I could utilize this cool and time-saving feature of MSSQL, Performance is not a problem. These processes will run at midnight.

Thankyou|||There is no "good" way to do what you are looking for. The best you could do would be to start a transaction on the first process and when it is done, COMMIT.

The 2nd process would have to be set to:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Also, you would have to make sure the 2nd process doesn't start before the 1st opens the transaction. I would schedule them 5 mins apart.

Lock and unlock the table

Hi,
How would I lock a table so that the access calls from other applications are put in "wait" by sql server till I unlock ?

How would I do this ?

Thanks,
Fahad

TABLOCK table hint

See SQL Server 2005 Books Online topic Table Hint (Transact-SQL)

http://msdn2.microsoft.com/en-US/library/ms187373.aspx

Possibly SERIALIZABLE

See SQL Server 2005 Books Online topic

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

http://msdn2.microsoft.com/en-US/library/ms173763.aspx

|||Could you please explain your problem? Locking entire table hurts concurrency and performance. Maybe there are other ways to do this.|||MS SQL, and most "server" based databases, don't do that unless they absoulutely need too, and it is done by the engine, not the user.

What is it you are trying to do and why?

If you just want to make sure someone doesn't read partially updated data, use a transaction.

|||

Tom Phillips wrote:

MS SQL, and most "server" based databases, don't do that unless they absoulutely need too, and it is done by the engine, not the user.

What is it you are trying to do and why?

If you just want to make sure someone doesn't read partially updated data, use a transaction.

I need a synchronization between two processes which are accessing a table, both are initiated with a second of difference, one prepares data for other and other consumes it. I want 2nd one to wait till 1st one is done. I dont wanna spend hours to do mutexes and semaphores things, I wonder if I could utilize this cool and time-saving feature of MSSQL, Performance is not a problem. These processes will run at midnight.

Thankyou|||There is no "good" way to do what you are looking for. The best you could do would be to start a transaction on the first process and when it is done, COMMIT.

The 2nd process would have to be set to:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Also, you would have to make sure the 2nd process doesn't start before the 1st opens the transaction. I would schedule them 5 mins apart.

Lock and unlock the table

Hi,
How would I lock a table so that the access calls from other applications are put in "wait" by sql server till I unlock ?

How would I do this ?

Thanks,
Fahad

TABLOCK table hint

See SQL Server 2005 Books Online topic Table Hint (Transact-SQL)

http://msdn2.microsoft.com/en-US/library/ms187373.aspx

Possibly SERIALIZABLE

See SQL Server 2005 Books Online topic

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

http://msdn2.microsoft.com/en-US/library/ms173763.aspx

|||Could you please explain your problem? Locking entire table hurts concurrency and performance. Maybe there are other ways to do this.|||MS SQL, and most "server" based databases, don't do that unless they absoulutely need too, and it is done by the engine, not the user.

What is it you are trying to do and why?

If you just want to make sure someone doesn't read partially updated data, use a transaction.

|||

Tom Phillips wrote:

MS SQL, and most "server" based databases, don't do that unless they absoulutely need too, and it is done by the engine, not the user.

What is it you are trying to do and why?

If you just want to make sure someone doesn't read partially updated data, use a transaction.

I need a synchronization between two processes which are accessing a table, both are initiated with a second of difference, one prepares data for other and other consumes it. I want 2nd one to wait till 1st one is done. I dont wanna spend hours to do mutexes and semaphores things, I wonder if I could utilize this cool and time-saving feature of MSSQL, Performance is not a problem. These processes will run at midnight.

Thankyou|||There is no "good" way to do what you are looking for. The best you could do would be to start a transaction on the first process and when it is done, COMMIT.

The 2nd process would have to be set to:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Also, you would have to make sure the 2nd process doesn't start before the 1st opens the transaction. I would schedule them 5 mins apart.

Lock a table until Package execution finishes

Hello,
I need to lock a table in startup of my package so that access calls from other applications are put on "wait" by sql server until I unlock.

Any idea how would I do it ?
Or is it possible or not ?

Thanks,
FahadIf you use MS Access database I think it will be locked automatically.|||You may want to jump over to the Transact-SQL forum to get help on this. I would imagine you can issue a table lock command in an Execute SQL task, and then when done processing release that lock in another Execute SQL task.

Local and Remote Connections question

Hello all,

We are currently testing all our applications on Vista and SQL Server 2005. Everything is running and working, but our applications that connect to SQL Server 2005 are running very slow. We have our client installed on a vista pc running SQL Server 2005 on the same pc. I was trying to troubleshoot the problem and realized that we had "Local connections only" checked in the Surface Area Configuration tool, and changed it to "Local and remote connections". After making this change all our apps seem to be running much faster. I just didn't expect this to be the problem since we were connecting from the same computer that is running the server. Can anyone explain to me why this has made the difference in performance?

Thanks for your time and attention,

DrewV

Hi DrewV,

How many connections were you attempting to sql server through your application(s).

How much memory do you have on the box and how much memory is configured for SQL Server?

regards

Jag

|||

There is only one connection being opened by our app.

The machine is a brand new Dell Precision 390 with 2GB or ram.

We are running SQL Express which I think uses a max of 1GB.

It is almost like it is trying to connect with a "remote" connection and trying locally after the first attempt fails. I just don't understand why since we are running on the same machine.

|||

I'm only guessing here, but the local connections system generally uses shared memory to pass the information between the client and server avoiding the network protocols. However this requires an area of memory which is accessible to both processes.

It may be that this is seen as a security weakpoint in Vista (direct access to non-process specific memory ... hmmm) so that there is a significant amount of checking and policing of such access which did not occur before and so slows down the whole process. The remote connection goes through the network stack and so is probably considered less of a vulnerability and so does not need the extra control.

Maybe SQL Server 2005 prefers on Vista to use the tcp/ip stack (if available) rather than shared memory (because it will be faster) - but this is only possible if you enable remote as well as local connections.

Also you don't say which service pack of SQL Server 2005 you are using.

|||Thanks for your response. We are on Service Pack 2.