Exclusive Lock Table like Oracle?

In Oracle, I can do "LOCK TABLE table IN EXCLUSIVE MODE" if I want to
hold a table, is there an equivalent in MSSQL?
I have a table of "next available id numbers", and when one is
requested, I want to lock the table, get the next one, delete it, and
then unlock the table.
Thanks.What version are you using?
Lookup LOCK hints in the BOL
<bradwiseathome@.hotmail.com> wrote in message
news:1145280771.375136.20130@.j33g2000cwa.googlegroups.com...
> In Oracle, I can do "LOCK TABLE table IN EXCLUSIVE MODE" if I want to
> hold a table, is there an equivalent in MSSQL?
> I have a table of "next available id numbers", and when one is
> requested, I want to lock the table, get the next one, delete it, and
> then unlock the table.
> Thanks.
>|||I believe you can do this with table hints.
BEGIN TRANSACTION
SELECT ... WITH TABLOCK
DELETE ...
END TRANSACTION
You can find samples from the BOL.
Mel

Exclusive Lock Table like Oracle?

In Oracle, I can do "LOCK TABLE table IN EXCLUSIVE MODE" if I want to
hold a table, is there an equivalent in MSSQL?
I have a table of "next available id numbers", and when one is
requested, I want to lock the table, get the next one, delete it, and
then unlock the table.
Thanks.What version are you using?
Lookup LOCK hints in the BOL
<bradwiseathome@.hotmail.com> wrote in message
news:1145280771.375136.20130@.j33g2000cwa.googlegroups.com...
> In Oracle, I can do "LOCK TABLE table IN EXCLUSIVE MODE" if I want to
> hold a table, is there an equivalent in MSSQL?
> I have a table of "next available id numbers", and when one is
> requested, I want to lock the table, get the next one, delete it, and
> then unlock the table.
> Thanks.
>|||I believe you can do this with table hints.
BEGIN TRANSACTION
SELECT ... WITH TABLOCK
DELETE ...
END TRANSACTION
You can find samples from the BOL.
Mel

exclusive lock

(pls correct if u feel anything here is incorrect)
i know in SQL2000, locks are well managed internally, and many DML/DDL
statements will auto use lock.
just want to know, why and how a DBA will need to exclusively use lock,
under what kind of situations ?
tks for sharing.you may change locking behaviour by changing isolation levels for the
connection
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE (others doc'd in books on
line)
You may also change locking behaviour for a single table in a single
transaction using lock hints ie
Select * from titles with ( nolock)
In my experience the most common two reasons for adjusting locking is
1. to increase locking so that no-one can touch the data until you are
through with it... Perhaps you are doing some kind of adjustments to the
money fields of many rows ( like cost allocation)... Any changes made by
others during your process will skew your work and make it inaccurate...
Therefore you may choose to increase the locking level to serializable so
no-one else can touch the data until you are through..
Another similar case is when you are doing a select ( but intend to
update later) and do not wish the values to change, you might select with
holdlock.
2. the second reason is that you wish to read some data and NOT be blocked
by updaters. You may choose to ignore exclusive locks and read dirty data.
The select above will do that..
Hope this helps.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pk" <pk@.> wrote in message news:utAG1ewEEHA.2768@.tk2msftngp13.phx.gbl...
> (pls correct if u feel anything here is incorrect)
> i know in SQL2000, locks are well managed internally, and many DML/DDL
> statements will auto use lock.
> just want to know, why and how a DBA will need to exclusively use lock,
> under what kind of situations ?
> tks for sharing.
>

Exclusive Insert Lock on a Table

Hello All!

I want to perform 4 or 5 statements as a transaction but I need to make sure that during this complete transaction no one else inserts or deletes records from a table named SomeTable.

So how can I lock MyTable at the beggining of the transaction so that during my transaction no one else can insert or delete anything in table SomeTable?

Thanks!

David

You can open an transaction explicitly so other transactions are placed in a queue. Use BEGIN TRAN /COMMIT TRAN. Read up books on line for more info on transactions.

|||

Yup, I could read about this as you suggest, but it's certainly pretty complex so I was hoping someone experienced and knowleadgable would give me the answer.

Your post but it doesn't answer my question. I know how to create an explicit transaction.

My question is how do I lock a table during a transaction so that no one else inserts or deletes records from it.

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

|||

neutrino:

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

Yes it does. And thats all you have to do. Try this scenario:

(1) In a query analyzer window run this script

BEGIN TRAN

INSERT INTO TheTable ... <complete the rest of the INSERT>

(2) Open another query analyzer and do a SELECT * From TheTable. You will see that your query will be in "suspended" status waiting for the Insert to finish since you opened a Transaction explicitly.

(3) Now go back to the first window and run this:

Commit

(4) Check the second window and you will see results for your SELECT *...

Basically your BEGIN TRAN has opened a transaction --> locked the table for any other commited transactions (Even reads). As soon as you commit the transaction the lock on the table is released and others can read from the table. You can by pass this and do a dirty read by using NOLOCK hint. Not always suggested unless your business requirements allow you to.

|||

Thanks. This tells me how to lock a table by creating a transaction and doing and insert statement. However, in my scenario I need to lock the table innitially before doing any inserts to it and I want it to remain locked until the transaction ends (even if I don't do any inserts). I don't want any other transaction to be able to insert any rows until my transaction finishes.

I think that what I need is to set the transaction isolation level to SERIALIZABLE.

I found this: http://msdn2.microsoft.com/en-us/library/ms173763.aspx

I'll be trying it later and will post results.

David

|||

I do find your query analyzer excercise extremelly useful. I will use it to test my locking 'theories". Thanks!

|||

ndinakar:

neutrino:

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

Yes it does. And thats all you have to do. Try this scenario:

(1) In a query analyzer window run this script

BEGIN TRAN

INSERT INTO TheTable ... <complete the rest of the INSERT>

(2) Open another query analyzer and do a SELECT * From TheTable. You will see that your query will be in "suspended" status waiting for the Insert to finish since you opened a Transaction explicitly.

(3) Now go back to the first window and run this:

Commit

(4) Check the second window and you will see results for your SELECT *...

Basically your BEGIN TRAN has opened a transaction --> locked the table for any other commited transactions (Even reads). As soon as you commit the transaction the lock on the table is released and others can read from the table. You can by pass this and do a dirty read by using NOLOCK hint. Not always suggested unless your business requirements allow you to.

Actually, that doesn't lock the whole table. It locks a portion of the table, but your SELECT requires access to the entire table (including the locked portion), so it has to wait. If you have the table indexed, and the SELECT can use the index to determine that it doesn't need the locked portion then it won't delay the SELECT. In addition, a second INSERT should complete without being delayed.

To the original poster, what you are asking for is rather uncommon, and you are best to try and avoid doing what you are asking to do. Perhaps you need to rethink why you want the table locked, and what you are trying to accomplish by doing so. Usually there is a much better way of achieving that.

|||

Motley:

To the original poster, what you are asking for is rather uncommon, and you are best to try and avoid doing what you are asking to do. Perhaps you need to rethink why you want the table locked, and what you are trying to accomplish by doing so. Usually there is a much better way of achieving that.

You are totally right. This was actually what I did. I re-thought the process and found a better way that doesn't require the table lock.

But anyways it was a great learning experience.

Thanks all for your support.

David

|||

ndinakar:

neutrino:

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

Yes it does. And thats all you have to do. Try this scenario:

It actually doesn't but the rest of your post was really helpful. Thank you.

Exclusive Access to DataBase

Hi.
I need to access a database to modify, updates,... massively . It's possible to lock a database and have exclusive access?
(SQLServer 2000)
thanks.
FranciscoALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE|||Thanks

Francisco

Excessive stored procedure [COMPILE] lock

Hello!
I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As a
result, we have 40-50 other connections waiting, then next connection using
this procedure has [COMPILE] lock etc. Client is fully qualifying stored
procedure by database/owner name and it doesn't start with sp_. I know
these are the reasons for [COMPILE] lock being placed. Is there something
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The stored
procedure is very simple (I know it could be rewritten/optimized but our
developer wrote it):
CREATE PROCEDURE [dbo].[vsp_mail_select]
@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified folder
@.unread_count int = null output -- count of unread mail in specified folder
AS
SET NOCOUNT ON
select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1)) order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and @.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
GO
I was monitoring server for a couple of day before and I am not sure why
this happens every 3-4 days only!
Any help on this matter would be greately appreciated!
Thanks,
igor
This looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/pro...05/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am trying to investigate strange problem with particular stored
> procedure. It runs OK for several days and suddenly we start getting and
> lot of locks. The reason being [COMPILE] lock placed on this procedure. As
> a result, we have 40-50 other connections waiting, then next connection
> using this procedure has [COMPILE] lock etc. Client is fully qualifying
> stored procedure by database/owner name and it doesn't start with sp_. I
> know these are the reasons for [COMPILE] lock being placed. Is there
> something else that might trigger this lock? When troubleshooting this
> issue, I noticed there was no plan for this procedure in syscacheobjects.
> The stored procedure is very simple (I know it could be
> rewritten/optimized but our developer wrote it):
>
> CREATE PROCEDURE [dbo].[vsp_mail_select]
> @.user_id int,
> @.folder_id int,
> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
> @.start_index int = null, --unused for now, we return everything
> @.total_count int = null output, -- count of all mail in specified folder
> @.unread_count int = null output -- count of unread mail in specified
> folder
> AS
> SET NOCOUNT ON
> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
> order by date_sent desc
> select @.total_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
> @.is_read=0) or (@.is_read=1))
> select @.unread_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
> GO
> I was monitoring server for a couple of day before and I am not sure why
> this happens every 3-4 days only!
> Any help on this matter would be greately appreciated!
> Thanks,
> igor
>
|||I think recompilations may be caused by modifcations done in base tables.
Developer confirmed that we have an hourly job running that can potentially
modify alot of data in base table. I am thinking of using
KEEPFIXED PLAN option to alleviate this problem.
Igor
"Sarav" <sarav@.sqlservertips.com> wrote in message
news:u8upDynWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> This looks like a Parameter Sniffing problem to me.
> take a look at this link:
> http://www.microsoft.com/technet/pro...05/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>

Excessive stored procedure [COMPILE] lock

Hello!
I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As a
result, we have 40-50 other connections waiting, then next connection using
this procedure has [COMPILE] lock etc. Client is fully qualifying stored
procedure by database/owner name and it doesn't start with sp_. I know
these are the reasons for [COMPILE] lock being placed. Is there somethin
g
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The stored
procedure is very simple (I know it could be rewritten/optimized but our
developer wrote it):
CREATE PROCEDURE [dbo].[vsp_mail_select]
@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified folder
@.unread_count int = null output -- count of unread mail in specified folder
AS
SET NOCOUNT ON
select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1)) order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and @.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
GO
I was monitoring server for a couple of day before and I am not sure why
this happens every 3-4 days only!
Any help on this matter would be greately appreciated!
Thanks,
igorThis looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/pr...005/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am trying to investigate strange problem with particular stored
> procedure. It runs OK for several days and suddenly we start getting and
> lot of locks. The reason being [COMPILE] lock placed on this procedure
. As
> a result, we have 40-50 other connections waiting, then next connection
> using this procedure has [COMPILE] lock etc. Client is fully qualifyin
g
> stored procedure by database/owner name and it doesn't start with sp_. I
> know these are the reasons for [COMPILE] lock being placed. Is there
> something else that might trigger this lock? When troubleshooting this
> issue, I noticed there was no plan for this procedure in syscacheobjects.
> The stored procedure is very simple (I know it could be
> rewritten/optimized but our developer wrote it):
>
> CREATE PROCEDURE [dbo].[vsp_mail_select]
> @.user_id int,
> @.folder_id int,
> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
> @.start_index int = null, --unused for now, we return everything
> @.total_count int = null output, -- count of all mail in specified folder
> @.unread_count int = null output -- count of unread mail in specified
> folder
> AS
> SET NOCOUNT ON
> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
> order by date_sent desc
> select @.total_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
> @.is_read=0) or (@.is_read=1))
> select @.unread_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
> GO
> I was monitoring server for a couple of day before and I am not sure why
> this happens every 3-4 days only!
> Any help on this matter would be greately appreciated!
> Thanks,
> igor
>|||I think recompilations may be caused by modifcations done in base tables.
Developer confirmed that we have an hourly job running that can potentially
modify alot of data in base table. I am thinking of using
KEEPFIXED PLAN option to alleviate this problem.
Igor
"Sarav" <sarav@.sqlservertips.com> wrote in message
news:u8upDynWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> This looks like a Parameter Sniffing problem to me.
> take a look at this link:
> http://www.microsoft.com/technet/pr...005/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>

Excessive stored procedure [COMPILE] lock

Hello!

I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting
and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As
a
result, we have 40-50 other connections waiting, then next connection
using
this procedure has [COMPILE] lock etc. Client is fully qualifying
stored
procedure by database/owner name and it doesn't start with sp_. I know

these are the reasons for [COMPILE] lock being placed. Is there
something
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The
stored
procedure is very simple (I know it could be rewritten/optimized but
our
developer wrote it):

CREATE PROCEDURE [dbo].[vsp_mail_select]

@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified
folder
@.unread_count int = null output -- count of unread mail in specified
folder

AS
SET NOCOUNT ON

select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
@.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0

GO

I was monitoring server for a couple of day before and I am not sure
why
this happens every 3-4 days only!

Any help on this matter would be greately appreciated!

Thanks,
IgorSee:
http://support.microsoft.com/defaul...B;en-us;q263889
http://support.microsoft.com/?kbid=836136
Maybe one of them (or the related articles) will help.

Razvan

Excessive stored procedure [COMPILE] lock

Hello!
I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As a
result, we have 40-50 other connections waiting, then next connection using
this procedure has [COMPILE] lock etc. Client is fully qualifying stored
procedure by database/owner name and it doesn't start with sp_. I know
these are the reasons for [COMPILE] lock being placed. Is there something
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The stored
procedure is very simple (I know it could be rewritten/optimized but our
developer wrote it):
CREATE PROCEDURE [dbo].[vsp_mail_select]
@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified folder
@.unread_count int = null output -- count of unread mail in specified folder
AS
SET NOCOUNT ON
select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1)) order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and @.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
GO
I was monitoring server for a couple of day before and I am not sure why
this happens every 3-4 days only!
Any help on this matter would be greately appreciated!
Thanks,
igorThis looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am trying to investigate strange problem with particular stored
> procedure. It runs OK for several days and suddenly we start getting and
> lot of locks. The reason being [COMPILE] lock placed on this procedure. As
> a result, we have 40-50 other connections waiting, then next connection
> using this procedure has [COMPILE] lock etc. Client is fully qualifying
> stored procedure by database/owner name and it doesn't start with sp_. I
> know these are the reasons for [COMPILE] lock being placed. Is there
> something else that might trigger this lock? When troubleshooting this
> issue, I noticed there was no plan for this procedure in syscacheobjects.
> The stored procedure is very simple (I know it could be
> rewritten/optimized but our developer wrote it):
>
> CREATE PROCEDURE [dbo].[vsp_mail_select]
> @.user_id int,
> @.folder_id int,
> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
> @.start_index int = null, --unused for now, we return everything
> @.total_count int = null output, -- count of all mail in specified folder
> @.unread_count int = null output -- count of unread mail in specified
> folder
> AS
> SET NOCOUNT ON
> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
> order by date_sent desc
> select @.total_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
> @.is_read=0) or (@.is_read=1))
> select @.unread_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
> GO
> I was monitoring server for a couple of day before and I am not sure why
> this happens every 3-4 days only!
> Any help on this matter would be greately appreciated!
> Thanks,
> igor
>|||I think recompilations may be caused by modifcations done in base tables.
Developer confirmed that we have an hourly job running that can potentially
modify alot of data in base table. I am thinking of using
KEEPFIXED PLAN option to alleviate this problem.
Igor
"Sarav" <sarav@.sqlservertips.com> wrote in message
news:u8upDynWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> This looks like a Parameter Sniffing problem to me.
> take a look at this link:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>> Hello!
>> I am trying to investigate strange problem with particular stored
>> procedure. It runs OK for several days and suddenly we start getting and
>> lot of locks. The reason being [COMPILE] lock placed on this procedure.
>> As a result, we have 40-50 other connections waiting, then next
>> connection using this procedure has [COMPILE] lock etc. Client is fully
>> qualifying stored procedure by database/owner name and it doesn't start
>> with sp_. I know these are the reasons for [COMPILE] lock being placed.
>> Is there something else that might trigger this lock? When
>> troubleshooting this issue, I noticed there was no plan for this
>> procedure in syscacheobjects. The stored procedure is very simple (I know
>> it could be rewritten/optimized but our developer wrote it):
>>
>> CREATE PROCEDURE [dbo].[vsp_mail_select]
>> @.user_id int,
>> @.folder_id int,
>> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
>> @.start_index int = null, --unused for now, we return everything
>> @.total_count int = null output, -- count of all mail in specified folder
>> @.unread_count int = null output -- count of unread mail in specified
>> folder
>> AS
>> SET NOCOUNT ON
>> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
>> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
>> order by date_sent desc
>> select @.total_count = count(mail_id) from mail m1(nolock) where
>> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
>> @.is_read=0) or (@.is_read=1))
>> select @.unread_count = count(mail_id) from mail m1(nolock) where
>> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
>> GO
>> I was monitoring server for a couple of day before and I am not sure why
>> this happens every 3-4 days only!
>> Any help on this matter would be greately appreciated!
>> Thanks,
>> igor
>