some reasons can be app waiting for user inputs,time outs with out proper exceptional handling.

you can easily simulate this by

Begin tran Update authors …. Waitfor delay ’10:00:00’ --- time out will occur here (simulates long workload) rollback

For second question on Slocks,These mean shared locks which are taken by database engine for any select statements.

Good place to know more on locking would be https://technet.microsoft.com/en-us/library/aa213039(v=sql.80).aspx

References: http://blogs.msdn.com/b/psssql/archive/2008/04/21/how-it-works-what-is-a-sleeping-awaiting-command-session.aspx

Sleeping SPID blocking other transactions

some reasons can be app waiting for user inputs,time outs with out proper exceptional handling.

you can easily simulate this by

Begin tran Update authors …. Waitfor delay ’10:00:00’ --- time out will occur here (simulates long workload) rollback

For second question on Slocks,These mean shared locks which are taken by database engine for any select statements.

Good place to know more on locking would be https://technet.microsoft.com/en-us/library/aa213039(v=sql.80).aspx

References: Link

Sleeping SPID blocking other transactions

some reasons can be app waiting for user inputs,time outs with out proper exceptional handling.

you can easily simulate this by

Begin tran Update authors …. Waitfor delay ’10:00:00’ --- time out will occur here (simulates long workload) rollback

For second question on Slocks,These mean shared locks which are taken by database engine for any select statements.

Good place to know more on locking would be https://technet.microsoft.com/en-us/library/aa213039(v=sql.80).aspx

References: http://blogs.msdn.com/b/psssql/archive/2008/04/21/how-it-works-what-is-a-sleeping-awaiting-command-session.aspx

Sleeping SPID blocking other transactions

some reasons can be app waiting for user inputs,time outs with out proper exceptional handling.

you can easily simulate this by

Begin tran Update authors …. Waitfor delay ’10:00:00’ --- time out will occur here (simulates long workload) rollback

For second question on Slocks,These mean shared locks which are taken by database engine for any select statements.

Good place to know more on locking would be https://technet.microsoft.com/en-us/library/aa213039(v=sql.80).aspx

References: http://blogs.msdn.com/b/psssql/archive/2008/04/21/how-it-works-what-is-a-sleeping-awaiting-command-session.aspx

Sleeping SPID blocking other transactions