Log files

Is there any way to turn off recovery and/or eliminate the growth of the log
file? I've heard there's a system stored procedure which will do this, but
I am unable to find any info either in the help or googling the net.
Thanks,
JimOn Sep 18, 5:52 pm, "Jim Fox" <jim...@.emailhdi.com> wrote:
> Is there any way to turn off recovery and/or eliminate the growth of the log
> file? I've heard there's a system stored procedure which will do this, but
> I am unable to find any info either in the help or googling the net.
> Thanks,
> Jim
There isn't such stored procedure but it sounds like you are not
using log backups, but your database is not configured as simple
recovery mode. If this is correct, you can modify the database so it
will be in simple recovery mode using this statement:
alter database WriteRealDBNameHere set recovery simple
This will prevent the log from storing the data modifications
operations after they are done, but you still have to shrink the file
if it is too big. Notice that when you set the database to simple
recovery then you can restore it only to the last full or differential
backups that you have and in case of a catastrophic error, you will
not be able to do restore to a point of time.
Adi|||We are using simple recovery. Backup and recoveries are not needed for our
purposes - basically we do complex queries against fixed data sets (no
transactions) but do create tables of intermediate results, and the log
files (even in Simple recovery) get quite large. Surprised to learn that
logging cannot be turned off.
Thanks.
"Adi" <adicohn@.hotmail.com> wrote in message
news:1190132146.223565.52320@.57g2000hsv.googlegroups.com...
> On Sep 18, 5:52 pm, "Jim Fox" <jim...@.emailhdi.com> wrote:
>> Is there any way to turn off recovery and/or eliminate the growth of the
>> log
>> file? I've heard there's a system stored procedure which will do this,
>> but
>> I am unable to find any info either in the help or googling the net.
>> Thanks,
>> Jim
> There isn't such stored procedure but it sounds like you are not
> using log backups, but your database is not configured as simple
> recovery mode. If this is correct, you can modify the database so it
> will be in simple recovery mode using this statement:
> alter database WriteRealDBNameHere set recovery simple
> This will prevent the log from storing the data modifications
> operations after they are done, but you still have to shrink the file
> if it is too big. Notice that when you set the database to simple
> recovery then you can restore it only to the last full or differential
> backups that you have and in case of a catastrophic error, you will
> not be able to do restore to a point of time.
> Adi
>|||Hello Jim!
I believe that the following documentation will enlighten you about the
growing of Transaction Log.
http://support.microsoft.com/kb/873235/en-us
Ekrem Önsoy
"Jim Fox" <jim.fox@.emailhdi.com> wrote in message
news:e%23sjIRh%23HHA.464@.TK2MSFTNGP02.phx.gbl...
> We are using simple recovery. Backup and recoveries are not needed for
> our purposes - basically we do complex queries against fixed data sets (no
> transactions) but do create tables of intermediate results, and the log
> files (even in Simple recovery) get quite large. Surprised to learn that
> logging cannot be turned off.
> Thanks.
>
> "Adi" <adicohn@.hotmail.com> wrote in message
> news:1190132146.223565.52320@.57g2000hsv.googlegroups.com...
>> On Sep 18, 5:52 pm, "Jim Fox" <jim...@.emailhdi.com> wrote:
>> Is there any way to turn off recovery and/or eliminate the growth of the
>> log
>> file? I've heard there's a system stored procedure which will do this,
>> but
>> I am unable to find any info either in the help or googling the net.
>> Thanks,
>> Jim
>> There isn't such stored procedure but it sounds like you are not
>> using log backups, but your database is not configured as simple
>> recovery mode. If this is correct, you can modify the database so it
>> will be in simple recovery mode using this statement:
>> alter database WriteRealDBNameHere set recovery simple
>> This will prevent the log from storing the data modifications
>> operations after they are done, but you still have to shrink the file
>> if it is too big. Notice that when you set the database to simple
>> recovery then you can restore it only to the last full or differential
>> backups that you have and in case of a catastrophic error, you will
>> not be able to do restore to a point of time.
>> Adi
>|||In article <e#sjIRh#HHA.464@.TK2MSFTNGP02.phx.gbl>, jim.fox@.emailhdi.com
says...
> Surprised to learn that
> logging cannot be turned off.
>
I think you are confused by the use of the word "logging." Not like IIS
logging, or event logs, or installation logs which are all
informational. The LDF file is an inherent part of the database, such
that at any time, "database" consists of stuff already rolled into the
MDF/NDF files and stuff that is still in the "LDF" file. One cannot
exist without the other and you sure do *not* want to have the ability
to turn off this write-ahead logging. If you could you would break the
database and render it completely unusable. It would cause the A-C-I-D
properties to be broken. Without Atomic-Consistent-Isolated-Durable
transactions you end up with things like phantom records, lost data,
etc.
Suggest you read about transactions and transaction isolation in BOL
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||You can do this in the foolowing steps:
1. update master..sysdatabases set status = 32768 where name = 'yourdbname'
2. restart the server
Now your database in so called "emergency mode". One of properties of this
mode is that database operations are not logged.
Of course, it is that you want, but on my opinion it is not clever to do
such a things.
"Jim Fox" wrote:
> Is there any way to turn off recovery and/or eliminate the growth of the log
> file? I've heard there's a system stored procedure which will do this, but
> I am unable to find any info either in the help or googling the net.
> Thanks,
> Jim
>
>|||On Sep 19, 1:28 pm, Dmitrij Siemieniako
<DmitrijSiemieni...@.discussions.microsoft.com> wrote:
> You can do this in the foolowing steps:
> 1. update master..sysdatabases set status = 32768 where name = 'yourdbname'
> 2. restart the server
> Now your database in so called "emergency mode". One of properties of this
> mode is that database operations are not logged.
> Of course, it is that you want, but on my opinion it is not clever to do
> such a things.
>
> "Jim Fox" wrote:
> > Is there any way to turn off recovery and/or eliminate the growth of the log
> > file? I've heard there's a system stored procedure which will do this, but
> > I am unable to find any info either in the help or googling the net.
> > Thanks,
> > Jim- Hide quoted text -
> - Show quoted text -
Emergency mode should be used (just as the name implies) on emergency
cases only. It should be used on cases such as there is no log file
or cases with this severity. It definitely shouldn't be used on
regular basis for no reason. On this case it also wouldn't help.
When you set a database to emergency mode, you can't start any
transaction and you can not perform any data modification operation
within the database. Since Jim wrote that he inserts data into tables
and then base the reports on those tables, his procedure will not work
on a database that is set to emergency mode.
One last thing - If I remember correctly, setting a database to
emergency mode requires you to set the status to -32768 (I think that
the documentation specifies 32768, and that it is wrong. I admit that
I'm not sure about it).
Adi

Log file in T-sql

Hi Folks,
I really need help on this...it very urgent for me.
How can I create a log file from a stored proc in T-SQL. I mean everytime there is an error, I need to put it into a log file. This stored proc is not being called by a DTS package..please suggest some method

Regards
Manmeet1. use xp_cmdshell and echo ...
2. write into a table and bulk copy that out in the end

I prefer approach 2 for my procedures.|||Originally posted by Enigma
1. use xp_cmdshell and echo ...
2. write into a table and bulk copy that out in the end

I prefer approach 2 for my procedures.

thanks a lot..I know this sounds stupid...can you just send me a sample code ....I myself am looking into it also|||Originally posted by Enigma
1. use xp_cmdshell and echo ...
2. write into a table and bulk copy that out in the end

I prefer approach 2 for my procedures.

hey dude...

Ignore my last msg (about sending a code)..I got it..thanks a ton man...u have been a real life saviour!!!!!!!!

but just 1 question...why do you prefer option 2..any drawback with option 1?

regards
Manmeet|||That way .. i can maintain a archive of the errors for future reference.|||Originally posted by Enigma
That way .. i can maintain a archive of the errors for future reference.

but then we can still use the echo command with >> option to keep appending the errors to the file right...that way you will still have the archive right??

--IS there a way you can get the description of the error messages. We get the error code and then query the master table for error descriptions. But the description has place holder and hence we cannot get the complete message...any sugestions dude??

Regards
Manmeet|||IS there a way you can get the description of the error messages. We get the error code and then query the master table for error descriptions. But the description has place holder and hence we cannot get the complete message...any sugestions dude??

Am a little confused .. could you elaborate|||Originally posted by Enigma
Am a little confused .. could you elaborate

I am just running a stored proc from a batch file. I need to capture any error and log it into a txt file. So If I use @.@.ERROR, I get only the error code. SO to get the description of the error, I hit the sysmessages table in master database and get the description.

so say I have a sql statement 'drop table MyTab'...then if the table does not exist...the error should be 'cannot drop the table MyTab as it does not exist in the system catalog' (Error No 3701)

But if I use @.@.ERROR and query the table sysmessages...I get the description 'Cannot %S_MSG the %S_MSG '%.*ls', because it does not exist in the system catalog'...bcos thats what the table stores for Error no 3701...

So how can I get the complete description pf the error message??

Regards
Manmeet|||I'm also curious about this...and all I'm doing is trying to save the errors to a trace log table...

/*================================================= ====================================
| Name: ErrorHandler
| Description: Just a basic function to raise an error when necessary. Also writes to tracelog table.
|
| Created: MM/DD/YYYY by JoMama Angiedaddy
| Modified:
| Inputs: errorNum - The error number that occurred.
| app - The application description in which the error occurred.
| function - the function in which the error occurred.
|================================================= =====================================*/
CREATE PROC dbo.ErrorHandler (
@.errorNum int =0 ,
@.app varchar(50) ='' ,
@.function varchar(255) ='' )
AS
BEGIN

DECLARE @.msg varchar(100)

IF @.errorNum <> 0
BEGIN
IF Exists(SELECT error FROM master..sysmessages WHERE error = @.errornum)
SELECT @.msg = 'Error Number: ' +
Convert(varchar(5), error) + ' ' + description
FROM master..sysmessages
WHERE error = @.errornum
ELSE
SELECT @.msg = 'Error Number: ' + Convert(varchar(5), @.errornum)

EXECUTE tracelog 0, @.app, @.function, @.msg

RAISERROR ('ErrorHandler %s %s %s', 16, 1, @.app, @.function, @.msg)
RETURN 1
END
ELSE
RETURN 0
END
GO

And the results are:
Server: Msg 515, Level 16, State 2, Procedure sp_Rebuild_IBD8585_CurrentList, Line 79
Cannot insert the value NULL into column 'StockOSID', table 'IBDIndex.dbo.CurrentList'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(1 row(s) affected)

Here's what gets written to the tracelog and appears on the screen as a result of the RaiseError call:

Server: Msg 50000, Level 16, State 1, Procedure ErrorHandler, Line 33
ErrorHandler IBDIndex sp sp_Rebuild_IBD8585_CurrentList Error Number: 515 Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not a

So...I am trying (as are the previous poster and several other frustrated and head-scratching individuals I have found around the 'net on this subject) to discover a way to make the data that appears on the first (system-generated) error message make its way into the final trace error message string...instead of the placeholders from the sysmessages table.

Any insights and/or, well...just plain ole' magical code snippets or how-to's?

Thankyouverymuch

Log File Full Stored Procedure Error

Does anyone know exactly what behaviour is exhibited when running a
stored procedure and the log file fills up while the procedure is still
running?
I am seeing, through a created history table, a stored procedure that
began to run (by the fact that there is a row in the history table) but
not finish correctly. It is being called by VB and no user is
reporting that VB is erroring out.
Checking some of the SQL log files, the times the SQL proc bombed is
around the same time the SQL transaction log had to be manually dumped
because it was full.

Thanks,(steven.cooper@.infocision.com) writes:
> Does anyone know exactly what behaviour is exhibited when running a
> stored procedure and the log file fills up while the procedure is still
> running?

Sounds like that stored procedure is making a lot of updates, and eventually
the log fills up. Is your database running in simple recovery mode, or
in bulk-logged/full mode?

> I am seeing, through a created history table, a stored procedure that
> began to run (by the fact that there is a row in the history table) but
> not finish correctly. It is being called by VB and no user is
> reporting that VB is erroring out.

Looks like you should check whethert the error handling in the VB is
appropriate.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

log file - beginner question

Hi,
How I can view information stored into database log file (transaction log)?
Do I have any option to choose what kind of information will be registered
there? And finally: when I create database I have two option log file:
unrestricted file growth or restricted file growth to xxx MB. Is there any
way to ensure that when log file size reach max size then automatically
oldest entries are removed (rights now in such situation any operation on
database cause error).
MarcinRead the section in BOL (Books On-Line) on Backing Up and Restoring
Databases. Pay special attention to the section on Selecting a Recovery
Model. This will describe the function of the transaction log and what the
various choices do. Each choice has specific features and requirements so
read carefully before picking something and moving on.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Marcin" <kevin123@.poczta.onet.pl> wrote in message
news:bhbf70$26mu$1@.foka.acn.pl...
> Hi,
> How I can view information stored into database log file (transaction
log)?
> Do I have any option to choose what kind of information will be registered
> there? And finally: when I create database I have two option log file:
> unrestricted file growth or restricted file growth to xxx MB. Is there any
> way to ensure that when log file size reach max size then automatically
> oldest entries are removed (rights now in such situation any operation on
> database cause error).
> Marcin
>
>|||>How I can view information stored into database log file (transaction log)?
Log Explorer from www.lumigent.com is one way
>Do I have any option to choose what kind of information will be
registeredthere?
SQL2000 has the concept of recovery models that determine logging
charcteristics of transactions (FULL,BULK LOGGED,SIMPLE). For more
information look up Recovery Models in BOL
>automatically oldest entries are removed
SIMPLE recovery mode will truncate the transaction log when it gets 70% full
assuming there are no open long running transactions. Regular log backups in
the other recovery models will have the same effect.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Marcin" <kevin123@.poczta.onet.pl> wrote in message
news:bhbf70$26mu$1@.foka.acn.pl...
Hi,
How I can view information stored into database log file (transaction log)?
Do I have any option to choose what kind of information will be registered
there? And finally: when I create database I have two option log file:
unrestricted file growth or restricted file growth to xxx MB. Is there any
way to ensure that when log file size reach max size then automatically
oldest entries are removed (rights now in such situation any operation on
database cause error).
Marcin

Log Excess

Hi All,
Just wondering if someone can help me, it may sounds like a
silly question but I will ask anyway.
I have a stored procedure that uses a lot of temp tables
and when, the procedure is running eventually my tempdb log
file eats all the room on my drive, if I change the temp
tables to be normal non temp tables, will this stop this
from happening.
Thanks for any help.
PhilThen the same amount (a bit more actually) of log space will be used from
inside your application database instead of tempdb. Perhaps you can look at
the logic and see if you can streamline the process?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Phil" <harlequintp@.blazemail.com> wrote in message
news:12c6c01c4117b$79d3a6a0$a101280a@.phx
.gbl...
> Hi All,
> Just wondering if someone can help me, it may sounds like a
> silly question but I will ask anyway.
> I have a stored procedure that uses a lot of temp tables
> and when, the procedure is running eventually my tempdb log
> file eats all the room on my drive, if I change the temp
> tables to be normal non temp tables, will this stop this
> from happening.
> Thanks for any help.
> Phil

Log everything

Hi,
I've been using SQL Server for a while, but I'm fairly new to this area.
I would like to log every error that occurs in every stored procedure that I
call (actually from an ASP page, but I don't think this is relevant). One of
my stored procedures sometimes (as far as I can determine, quite
non-deterministically) fails, but from ASP all the error description I get
is this:
[Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction
completed. Either enlist this session in a new transaction or the NULL
transaction.
My stored proc. calls quite a lot other procedures, so it would be good to
be able to determine where exactly the error occurs during executing. (And I
wouldn't like to put "IF @.@.ERROR <> 0" blocks after every rows in every
stored procedure.)
So the best would be if I could see in a log which stored procedures were
called with which values and which procedure failed in which row. Is it (or
at least part of it) possible?
Thx,
AgostonAgoston Bejo wrote:
> Hi,
> I've been using SQL Server for a while, but I'm fairly new to this
> area.
> I would like to log every error that occurs in every stored procedure
> that I call (actually from an ASP page, but I don't think this is
> relevant). One of my stored procedures sometimes (as far as I can
> determine, quite non-deterministically) fails, but from ASP all the
> error description I get is this:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction
> completed. Either enlist this session in a new transaction or the NULL
> transaction.
> My stored proc. calls quite a lot other procedures, so it would be
> good to be able to determine where exactly the error occurs during
> executing. (And I wouldn't like to put "IF @.@.ERROR <> 0" blocks after
> every rows in every stored procedure.)
> So the best would be if I could see in a log which stored procedures
> were called with which values and which procedure failed in which
> row. Is it (or at least part of it) possible?
> Thx,
> Agoston
You can use Profiler and the SQL Trace API to watch SQL execution and
see Errors/Exceptions. Tracing may be extensive, so I would recommend
you research creating a server-side trace. You'll have to trap
SP:Starting and SP:StmtStarting events and SQL:StmtStarting/RPC:Starting
if you have any SQL running outside a SP. You 'll also have to add
Errors and Exceptions. You're going to collect a lot of data, so if
these errors are intermittent, be prepared for very large trace files
(possibly gigabytes if the database is active). If you can limit the
trace to a specific user/application it might help to eliminate some
data collection.
David Gugick
Imceda Software
www.imceda.com|||OK, from the trace I can see that probably an error occurs in a trigger. The
trigger does a rollback tran (and with that makes it impossible to put some
debugging info into a table, because that insert statement gets rolled back,
too - even if issued after the rollback trans statement). After that, it
calls RAISERROR with an (quite verbose) error message. I added every event
to the trace but cannot see anything from this RAISERROR statement. It would
be very good to somehow acquire the error message.
Basically, the structure of the trigger is this:
alter trigger tr_ins_upd
on table1
for insert, update
begin
[check for inconsistency]
[if there is any:]
[rollback tran]
[insert debug info into debug table] -- no effect
[raiserror(debug_info)] -- cannot see in SQL Profiler's
trace
[end if]
end
So, how can I
- get to see the error message sent by RAISERROR
- insert some info into a table that doesn't get rolled back by that
'rollback tran' statement
Thx,
Agoston
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#65ljozEFHA.1084@.tk2msftngp13.phx.gbl...
> Agoston Bejo wrote:
> You can use Profiler and the SQL Trace API to watch SQL execution and
> see Errors/Exceptions. Tracing may be extensive, so I would recommend
> you research creating a server-side trace. You'll have to trap
> SP:Starting and SP:StmtStarting events and SQL:StmtStarting/RPC:Starting
> if you have any SQL running outside a SP. You 'll also have to add
> Errors and Exceptions. You're going to collect a lot of data, so if
> these errors are intermittent, be prepared for very large trace files
> (possibly gigabytes if the database is active). If you can limit the
> trace to a specific user/application it might help to eliminate some
> data collection.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Hi Agoston,
On Tue, 15 Feb 2005 11:31:25 +0100, Agoston Bejo wrote:
(snip)
>So, how can I
>- get to see the error message sent by RAISERROR
Use the trace you already have to find out what command(s) are sent to SQL
Server from the ASP Page; execute those same commands from a Query
Analyser window.

>- insert some info into a table that doesn't get rolled back by that
>'rollback tran' statement
Statements executed after the ROLLBACK sould not be rolled back, unless
another ROLLBACK is issued later in the process. Use the profile trace to
find out who is issueing this second ROLLBACK.
(I don't know ASP, but I do have the experience that MS Access will
sometimes issue a rollback when unexpected messages are returned - your
RAISERROR would probably trigger MS Access to issue a rollback)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The only comprehensive way to do this is by injecting error collection code
in your application's data tier. Profiler traces can show you when errors
occur, but the only message info you can get this way is by looking up the
error number in master..sysmessages. This only gives you the error message
template, but not the specifics you're probably after (eg the name of the
table a primary key violation occurred on).
Regards,
Greg Linwood
SQL Server MVP
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:u0nN3l0EFHA.1924@.TK2MSFTNGP14.phx.gbl...
> OK, from the trace I can see that probably an error occurs in a trigger.
> The
> trigger does a rollback tran (and with that makes it impossible to put
> some
> debugging info into a table, because that insert statement gets rolled
> back,
> too - even if issued after the rollback trans statement). After that, it
> calls RAISERROR with an (quite verbose) error message. I added every event
> to the trace but cannot see anything from this RAISERROR statement. It
> would
> be very good to somehow acquire the error message.
> Basically, the structure of the trigger is this:
> alter trigger tr_ins_upd
> on table1
> for insert, update
> begin
> [check for inconsistency]
> [if there is any:]
> [rollback tran]
> [insert debug info into debug table] -- no effect
> [raiserror(debug_info)] -- cannot see in SQL Profiler's
> trace
> [end if]
> end
> So, how can I
> - get to see the error message sent by RAISERROR
> - insert some info into a table that doesn't get rolled back by that
> 'rollback tran' statement
> Thx,
> Agoston
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:#65ljozEFHA.1084@.tk2msftngp13.phx.gbl...
>

Log everything

Hi,
I've been using SQL Server for a while, but I'm fairly new to this area.
I would like to log every error that occurs in every stored procedure that I
call (actually from an ASP page, but I don't think this is relevant). One of
my stored procedures sometimes (as far as I can determine, quite
non-deterministically) fails, but from ASP all the error description I get
is this:
[Microsoft][ODBC SQL Server Driver][SQL Server]Distributed trans
action
completed. Either enlist this session in a new transaction or the NULL
transaction.
My stored proc. calls quite a lot other procedures, so it would be good to
be able to determine where exactly the error occurs during executing. (And I
wouldn't like to put "IF @.@.ERROR <> 0" blocks after every rows in every
stored procedure.)
So the best would be if I could see in a log which stored procedures were
called with which values and which procedure failed in which row. Is it (or
at least part of it) possible?
Thx,
AgostonAgoston Bejo wrote:
> Hi,
> I've been using SQL Server for a while, but I'm fairly new to this
> area.
> I would like to log every error that occurs in every stored procedure
> that I call (actually from an ASP page, but I don't think this is
> relevant). One of my stored procedures sometimes (as far as I can
> determine, quite non-deterministically) fails, but from ASP all the
> error description I get is this:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Distributed tra
nsaction
> completed. Either enlist this session in a new transaction or the NULL
> transaction.
> My stored proc. calls quite a lot other procedures, so it would be
> good to be able to determine where exactly the error occurs during
> executing. (And I wouldn't like to put "IF @.@.ERROR <> 0" blocks after
> every rows in every stored procedure.)
> So the best would be if I could see in a log which stored procedures
> were called with which values and which procedure failed in which
> row. Is it (or at least part of it) possible?
> Thx,
> Agoston
You can use Profiler and the SQL Trace API to watch SQL execution and
see Errors/Exceptions. Tracing may be extensive, so I would recommend
you research creating a server-side trace. You'll have to trap
SP:Starting and SP:StmtStarting events and SQL:StmtStarting/RPC:Starting
if you have any SQL running outside a SP. You 'll also have to add
Errors and Exceptions. You're going to collect a lot of data, so if
these errors are intermittent, be prepared for very large trace files
(possibly gigabytes if the database is active). If you can limit the
trace to a specific user/application it might help to eliminate some
data collection.
David Gugick
Imceda Software
www.imceda.com|||OK, from the trace I can see that probably an error occurs in a trigger. The
trigger does a rollback tran (and with that makes it impossible to put some
debugging info into a table, because that insert statement gets rolled back,
too - even if issued after the rollback trans statement). After that, it
calls RAISERROR with an (quite verbose) error message. I added every event
to the trace but cannot see anything from this RAISERROR statement. It would
be very good to somehow acquire the error message.
Basically, the structure of the trigger is this:
alter trigger tr_ins_upd
on table1
for insert, update
begin
[check for inconsistency]
[if there is any:]
[rollback tran]
[insert debug info into debug table] -- no effect
[raiserror(debug_info)] -- cannot see in SQL Profiler's
trace
[end if]
end
So, how can I
- get to see the error message sent by RAISERROR
- insert some info into a table that doesn't get rolled back by that
'rollback tran' statement
Thx,
Agoston
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#65ljozEFHA.1084@.tk2msftngp13.phx.gbl...
> Agoston Bejo wrote:
> You can use Profiler and the SQL Trace API to watch SQL execution and
> see Errors/Exceptions. Tracing may be extensive, so I would recommend
> you research creating a server-side trace. You'll have to trap
> SP:Starting and SP:StmtStarting events and SQL:StmtStarting/RPC:Starting
> if you have any SQL running outside a SP. You 'll also have to add
> Errors and Exceptions. You're going to collect a lot of data, so if
> these errors are intermittent, be prepared for very large trace files
> (possibly gigabytes if the database is active). If you can limit the
> trace to a specific user/application it might help to eliminate some
> data collection.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Hi Agoston,
On Tue, 15 Feb 2005 11:31:25 +0100, Agoston Bejo wrote:
(snip)
>So, how can I
>- get to see the error message sent by RAISERROR
Use the trace you already have to find out what command(s) are sent to SQL
Server from the ASP Page; execute those same commands from a Query
Analyser window.

>- insert some info into a table that doesn't get rolled back by that
>'rollback tran' statement
Statements executed after the ROLLBACK sould not be rolled back, unless
another ROLLBACK is issued later in the process. Use the profile trace to
find out who is issueing this second ROLLBACK.
(I don't know ASP, but I do have the experience that MS Access will
sometimes issue a rollback when unexpected messages are returned - your
RAISERROR would probably trigger MS Access to issue a rollback)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The only comprehensive way to do this is by injecting error collection code
in your application's data tier. Profiler traces can show you when errors
occur, but the only message info you can get this way is by looking up the
error number in master..sysmessages. This only gives you the error message
template, but not the specifics you're probably after (eg the name of the
table a primary key violation occurred on).
Regards,
Greg Linwood
SQL Server MVP
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:u0nN3l0EFHA.1924@.TK2MSFTNGP14.phx.gbl...
> OK, from the trace I can see that probably an error occurs in a trigger.
> The
> trigger does a rollback tran (and with that makes it impossible to put
> some
> debugging info into a table, because that insert statement gets rolled
> back,
> too - even if issued after the rollback trans statement). After that, it
> calls RAISERROR with an (quite verbose) error message. I added every event
> to the trace but cannot see anything from this RAISERROR statement. It
> would
> be very good to somehow acquire the error message.
> Basically, the structure of the trigger is this:
> alter trigger tr_ins_upd
> on table1
> for insert, update
> begin
> [check for inconsistency]
> [if there is any:]
> [rollback tran]
> [insert debug info into debug table] -- no effect
> [raiserror(debug_info)] -- cannot see in SQL Profile
r's
> trace
> [end if]
> end
> So, how can I
> - get to see the error message sent by RAISERROR
> - insert some info into a table that doesn't get rolled back by that
> 'rollback tran' statement
> Thx,
> Agoston
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:#65ljozEFHA.1084@.tk2msftngp13.phx.gbl...
>

Log everything

Hi,
I've been using SQL Server for a while, but I'm fairly new to this area.
I would like to log every error that occurs in every stored procedure that I
call (actually from an ASP page, but I don't think this is relevant). One of
my stored procedures sometimes (as far as I can determine, quite
non-deterministically) fails, but from ASP all the error description I get
is this:
[Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction
completed. Either enlist this session in a new transaction or the NULL
transaction.
My stored proc. calls quite a lot other procedures, so it would be good to
be able to determine where exactly the error occurs during executing. (And I
wouldn't like to put "IF @.@.ERROR <> 0" blocks after every rows in every
stored procedure.)
So the best would be if I could see in a log which stored procedures were
called with which values and which procedure failed in which row. Is it (or
at least part of it) possible?
Thx,
Agoston
Agoston Bejo wrote:
> Hi,
> I've been using SQL Server for a while, but I'm fairly new to this
> area.
> I would like to log every error that occurs in every stored procedure
> that I call (actually from an ASP page, but I don't think this is
> relevant). One of my stored procedures sometimes (as far as I can
> determine, quite non-deterministically) fails, but from ASP all the
> error description I get is this:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction
> completed. Either enlist this session in a new transaction or the NULL
> transaction.
> My stored proc. calls quite a lot other procedures, so it would be
> good to be able to determine where exactly the error occurs during
> executing. (And I wouldn't like to put "IF @.@.ERROR <> 0" blocks after
> every rows in every stored procedure.)
> So the best would be if I could see in a log which stored procedures
> were called with which values and which procedure failed in which
> row. Is it (or at least part of it) possible?
> Thx,
> Agoston
You can use Profiler and the SQL Trace API to watch SQL execution and
see Errors/Exceptions. Tracing may be extensive, so I would recommend
you research creating a server-side trace. You'll have to trap
SP:Starting and SP:StmtStarting events and SQL:StmtStarting/RPC:Starting
if you have any SQL running outside a SP. You 'll also have to add
Errors and Exceptions. You're going to collect a lot of data, so if
these errors are intermittent, be prepared for very large trace files
(possibly gigabytes if the database is active). If you can limit the
trace to a specific user/application it might help to eliminate some
data collection.
David Gugick
Imceda Software
www.imceda.com
|||OK, from the trace I can see that probably an error occurs in a trigger. The
trigger does a rollback tran (and with that makes it impossible to put some
debugging info into a table, because that insert statement gets rolled back,
too - even if issued after the rollback trans statement). After that, it
calls RAISERROR with an (quite verbose) error message. I added every event
to the trace but cannot see anything from this RAISERROR statement. It would
be very good to somehow acquire the error message.
Basically, the structure of the trigger is this:
alter trigger tr_ins_upd
on table1
for insert, update
begin
[check for inconsistency]
[if there is any:]
[rollback tran]
[insert debug info into debug table] -- no effect
[raiserror(debug_info)] -- cannot see in SQL Profiler's
trace
[end if]
end
So, how can I
- get to see the error message sent by RAISERROR
- insert some info into a table that doesn't get rolled back by that
'rollback tran' statement
Thx,
Agoston
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#65ljozEFHA.1084@.tk2msftngp13.phx.gbl...
> Agoston Bejo wrote:
> You can use Profiler and the SQL Trace API to watch SQL execution and
> see Errors/Exceptions. Tracing may be extensive, so I would recommend
> you research creating a server-side trace. You'll have to trap
> SP:Starting and SP:StmtStarting events and SQL:StmtStarting/RPC:Starting
> if you have any SQL running outside a SP. You 'll also have to add
> Errors and Exceptions. You're going to collect a lot of data, so if
> these errors are intermittent, be prepared for very large trace files
> (possibly gigabytes if the database is active). If you can limit the
> trace to a specific user/application it might help to eliminate some
> data collection.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Hi Agoston,
On Tue, 15 Feb 2005 11:31:25 +0100, Agoston Bejo wrote:
(snip)
>So, how can I
>- get to see the error message sent by RAISERROR
Use the trace you already have to find out what command(s) are sent to SQL
Server from the ASP Page; execute those same commands from a Query
Analyser window.

>- insert some info into a table that doesn't get rolled back by that
>'rollback tran' statement
Statements executed after the ROLLBACK sould not be rolled back, unless
another ROLLBACK is issued later in the process. Use the profile trace to
find out who is issueing this second ROLLBACK.
(I don't know ASP, but I do have the experience that MS Access will
sometimes issue a rollback when unexpected messages are returned - your
RAISERROR would probably trigger MS Access to issue a rollback)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||The only comprehensive way to do this is by injecting error collection code
in your application's data tier. Profiler traces can show you when errors
occur, but the only message info you can get this way is by looking up the
error number in master..sysmessages. This only gives you the error message
template, but not the specifics you're probably after (eg the name of the
table a primary key violation occurred on).
Regards,
Greg Linwood
SQL Server MVP
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:u0nN3l0EFHA.1924@.TK2MSFTNGP14.phx.gbl...
> OK, from the trace I can see that probably an error occurs in a trigger.
> The
> trigger does a rollback tran (and with that makes it impossible to put
> some
> debugging info into a table, because that insert statement gets rolled
> back,
> too - even if issued after the rollback trans statement). After that, it
> calls RAISERROR with an (quite verbose) error message. I added every event
> to the trace but cannot see anything from this RAISERROR statement. It
> would
> be very good to somehow acquire the error message.
> Basically, the structure of the trigger is this:
> alter trigger tr_ins_upd
> on table1
> for insert, update
> begin
> [check for inconsistency]
> [if there is any:]
> [rollback tran]
> [insert debug info into debug table] -- no effect
> [raiserror(debug_info)] -- cannot see in SQL Profiler's
> trace
> [end if]
> end
> So, how can I
> - get to see the error message sent by RAISERROR
> - insert some info into a table that doesn't get rolled back by that
> 'rollback tran' statement
> Thx,
> Agoston
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:#65ljozEFHA.1084@.tk2msftngp13.phx.gbl...
>

Log everything

Hi,
I've been using SQL Server for a while, but I'm fairly new to this area.
I would like to log every error that occurs in every stored procedure that I
call (actually from an ASP page, but I don't think this is relevant). One of
my stored procedures sometimes (as far as I can determine, quite
non-deterministically) fails, but from ASP all the error description I get
is this:
[Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction
completed. Either enlist this session in a new transaction or the NULL
transaction.
My stored proc. calls quite a lot other procedures, so it would be good to
be able to determine where exactly the error occurs during executing. (And I
wouldn't like to put "IF @.@.ERROR <> 0" blocks after every rows in every
stored procedure.)
So the best would be if I could see in a log which stored procedures were
called with which values and which procedure failed in which row. Is it (or
at least part of it) possible?
Thx,
AgostonAgoston Bejo wrote:
> Hi,
> I've been using SQL Server for a while, but I'm fairly new to this
> area.
> I would like to log every error that occurs in every stored procedure
> that I call (actually from an ASP page, but I don't think this is
> relevant). One of my stored procedures sometimes (as far as I can
> determine, quite non-deterministically) fails, but from ASP all the
> error description I get is this:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction
> completed. Either enlist this session in a new transaction or the NULL
> transaction.
> My stored proc. calls quite a lot other procedures, so it would be
> good to be able to determine where exactly the error occurs during
> executing. (And I wouldn't like to put "IF @.@.ERROR <> 0" blocks after
> every rows in every stored procedure.)
> So the best would be if I could see in a log which stored procedures
> were called with which values and which procedure failed in which
> row. Is it (or at least part of it) possible?
> Thx,
> Agoston
You can use Profiler and the SQL Trace API to watch SQL execution and
see Errors/Exceptions. Tracing may be extensive, so I would recommend
you research creating a server-side trace. You'll have to trap
SP:Starting and SP:StmtStarting events and SQL:StmtStarting/RPC:Starting
if you have any SQL running outside a SP. You 'll also have to add
Errors and Exceptions. You're going to collect a lot of data, so if
these errors are intermittent, be prepared for very large trace files
(possibly gigabytes if the database is active). If you can limit the
trace to a specific user/application it might help to eliminate some
data collection.
--
David Gugick
Imceda Software
www.imceda.com|||OK, from the trace I can see that probably an error occurs in a trigger. The
trigger does a rollback tran (and with that makes it impossible to put some
debugging info into a table, because that insert statement gets rolled back,
too - even if issued after the rollback trans statement). After that, it
calls RAISERROR with an (quite verbose) error message. I added every event
to the trace but cannot see anything from this RAISERROR statement. It would
be very good to somehow acquire the error message.
Basically, the structure of the trigger is this:
alter trigger tr_ins_upd
on table1
for insert, update
begin
[check for inconsistency]
[if there is any:]
[rollback tran]
[insert debug info into debug table] -- no effect
[raiserror(debug_info)] -- cannot see in SQL Profiler's
trace
[end if]
end
So, how can I
- get to see the error message sent by RAISERROR
- insert some info into a table that doesn't get rolled back by that
'rollback tran' statement
Thx,
Agoston
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#65ljozEFHA.1084@.tk2msftngp13.phx.gbl...
> Agoston Bejo wrote:
> > Hi,
> > I've been using SQL Server for a while, but I'm fairly new to this
> > area.
> > I would like to log every error that occurs in every stored procedure
> > that I call (actually from an ASP page, but I don't think this is
> > relevant). One of my stored procedures sometimes (as far as I can
> > determine, quite non-deterministically) fails, but from ASP all the
> > error description I get is this:
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction
> > completed. Either enlist this session in a new transaction or the NULL
> > transaction.
> >
> > My stored proc. calls quite a lot other procedures, so it would be
> > good to be able to determine where exactly the error occurs during
> > executing. (And I wouldn't like to put "IF @.@.ERROR <> 0" blocks after
> > every rows in every stored procedure.)
> > So the best would be if I could see in a log which stored procedures
> > were called with which values and which procedure failed in which
> > row. Is it (or at least part of it) possible?
> >
> > Thx,
> > Agoston
> You can use Profiler and the SQL Trace API to watch SQL execution and
> see Errors/Exceptions. Tracing may be extensive, so I would recommend
> you research creating a server-side trace. You'll have to trap
> SP:Starting and SP:StmtStarting events and SQL:StmtStarting/RPC:Starting
> if you have any SQL running outside a SP. You 'll also have to add
> Errors and Exceptions. You're going to collect a lot of data, so if
> these errors are intermittent, be prepared for very large trace files
> (possibly gigabytes if the database is active). If you can limit the
> trace to a specific user/application it might help to eliminate some
> data collection.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Hi Agoston,
On Tue, 15 Feb 2005 11:31:25 +0100, Agoston Bejo wrote:
(snip)
>So, how can I
>- get to see the error message sent by RAISERROR
Use the trace you already have to find out what command(s) are sent to SQL
Server from the ASP Page; execute those same commands from a Query
Analyser window.
>- insert some info into a table that doesn't get rolled back by that
>'rollback tran' statement
Statements executed after the ROLLBACK sould not be rolled back, unless
another ROLLBACK is issued later in the process. Use the profile trace to
find out who is issueing this second ROLLBACK.
(I don't know ASP, but I do have the experience that MS Access will
sometimes issue a rollback when unexpected messages are returned - your
RAISERROR would probably trigger MS Access to issue a rollback)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The only comprehensive way to do this is by injecting error collection code
in your application's data tier. Profiler traces can show you when errors
occur, but the only message info you can get this way is by looking up the
error number in master..sysmessages. This only gives you the error message
template, but not the specifics you're probably after (eg the name of the
table a primary key violation occurred on).
Regards,
Greg Linwood
SQL Server MVP
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:u0nN3l0EFHA.1924@.TK2MSFTNGP14.phx.gbl...
> OK, from the trace I can see that probably an error occurs in a trigger.
> The
> trigger does a rollback tran (and with that makes it impossible to put
> some
> debugging info into a table, because that insert statement gets rolled
> back,
> too - even if issued after the rollback trans statement). After that, it
> calls RAISERROR with an (quite verbose) error message. I added every event
> to the trace but cannot see anything from this RAISERROR statement. It
> would
> be very good to somehow acquire the error message.
> Basically, the structure of the trigger is this:
> alter trigger tr_ins_upd
> on table1
> for insert, update
> begin
> [check for inconsistency]
> [if there is any:]
> [rollback tran]
> [insert debug info into debug table] -- no effect
> [raiserror(debug_info)] -- cannot see in SQL Profiler's
> trace
> [end if]
> end
> So, how can I
> - get to see the error message sent by RAISERROR
> - insert some info into a table that doesn't get rolled back by that
> 'rollback tran' statement
> Thx,
> Agoston
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:#65ljozEFHA.1084@.tk2msftngp13.phx.gbl...
>> Agoston Bejo wrote:
>> > Hi,
>> > I've been using SQL Server for a while, but I'm fairly new to this
>> > area.
>> > I would like to log every error that occurs in every stored procedure
>> > that I call (actually from an ASP page, but I don't think this is
>> > relevant). One of my stored procedures sometimes (as far as I can
>> > determine, quite non-deterministically) fails, but from ASP all the
>> > error description I get is this:
>> >
>> > [Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction
>> > completed. Either enlist this session in a new transaction or the NULL
>> > transaction.
>> >
>> > My stored proc. calls quite a lot other procedures, so it would be
>> > good to be able to determine where exactly the error occurs during
>> > executing. (And I wouldn't like to put "IF @.@.ERROR <> 0" blocks after
>> > every rows in every stored procedure.)
>> > So the best would be if I could see in a log which stored procedures
>> > were called with which values and which procedure failed in which
>> > row. Is it (or at least part of it) possible?
>> >
>> > Thx,
>> > Agoston
>> You can use Profiler and the SQL Trace API to watch SQL execution and
>> see Errors/Exceptions. Tracing may be extensive, so I would recommend
>> you research creating a server-side trace. You'll have to trap
>> SP:Starting and SP:StmtStarting events and SQL:StmtStarting/RPC:Starting
>> if you have any SQL running outside a SP. You 'll also have to add
>> Errors and Exceptions. You're going to collect a lot of data, so if
>> these errors are intermittent, be prepared for very large trace files
>> (possibly gigabytes if the database is active). If you can limit the
>> trace to a specific user/application it might help to eliminate some
>> data collection.
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>

Locks in tempdb

I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and it
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mike
are u using some syntax such as
select xxx into #abc from xyz ?
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>
|||Could you post the proc in question? It could be any number of things.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>

Locks in tempdb

I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and it
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mikeare u using some syntax such as
select xxx into #abc from xyz '
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>|||Could you post the proc in question? It could be any number of things.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>

Locks in tempdb

I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and it
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mikeare u using some syntax such as
select xxx into #abc from xyz '
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>|||Could you post the proc in question? It could be any number of things.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>

locks in a stored procedure

Are locks necessary in a stored procedure?
I have a short stored procedure where I want to update n records to indicate
their intended use in a non-database procedure. I want to select these
records based on the priority and scheduled process date, and according to a
parameter in the procedure. This is what I see as optimal...
----
CREATE PROCEDURE dbo.GetBatchForProcessing
@.thread_guid uniqueidentifier = Null OUTPUT,
@.batch_count int
AS
SET @.thread_guid = ISNULL(@.thread_guid, NewID())
UPDATE Queue SET lockID = @.thread_guid
WHERE queueID IN (SELECT TOP @.batch_count queueID FROM Queue WHERE
process_date IS NULL AND lockID IS NULL AND scheduled_date <= getdate()
ORDER BY priority, scheduled_date)
SELECT * FROM Queue WHERE lockID = @.thread_guid
----
...but I cannot do this because the @.batch_count parameter cannot be used
dynamically in the TOP statement, and just to say up front I am REALLY
against using dynamic SQL in stored procedures. I think it obviates the
point of using the procedure in the first place.
Since the limit on the rows I want affected in the UPDATE are in a subquery,
using SET ROWCOUNT @.batch_count won't work. Even if it did, SQL Server
won't allow the ORDER BY clause in the subquery without the TOP statement.
Which leaves me with using SET ROWCOUNT to select the set of queueID's into
a temp table, and then perform the update clause to match against what I've
put into the temp table. Now, while doing this shouldn't cause any problems
with the way my application works, design-wise it bothers me having that
step between creating the temp table and doing the update to the Queue
table. How can I temporarily lock the Queue table for these two steps?Random,
TOP @.Variable works fine in SQL Server 2000. What version are you running?
Or, did you upgrade to 2000, but your database remains in 70 mode? In that
case use sp_dbcmptlevel to set it to 80.
RLF
"Random" <cipherlad@.hotmail.com> wrote in message
news:eQHDkjlKFHA.4032@.TK2MSFTNGP14.phx.gbl...
> Are locks necessary in a stored procedure?
> I have a short stored procedure where I want to update n records to
> indicate their intended use in a non-database procedure. I want to select
> these records based on the priority and scheduled process date, and
> according to a parameter in the procedure. This is what I see as
> optimal...
> ----
> CREATE PROCEDURE dbo.GetBatchForProcessing
> @.thread_guid uniqueidentifier = Null OUTPUT,
> @.batch_count int
> AS
> SET @.thread_guid = ISNULL(@.thread_guid, NewID())
> UPDATE Queue SET lockID = @.thread_guid
> WHERE queueID IN (SELECT TOP @.batch_count queueID FROM Queue WHERE
> process_date IS NULL AND lockID IS NULL AND scheduled_date <= getdate()
> ORDER BY priority, scheduled_date)
> SELECT * FROM Queue WHERE lockID = @.thread_guid
> ----
> ...but I cannot do this because the @.batch_count parameter cannot be used
> dynamically in the TOP statement, and just to say up front I am REALLY
> against using dynamic SQL in stored procedures. I think it obviates the
> point of using the procedure in the first place.
> Since the limit on the rows I want affected in the UPDATE are in a
> subquery, using SET ROWCOUNT @.batch_count won't work. Even if it did, SQL
> Server won't allow the ORDER BY clause in the subquery without the TOP
> statement.
> Which leaves me with using SET ROWCOUNT to select the set of queueID's
> into a temp table, and then perform the update clause to match against
> what I've put into the temp table. Now, while doing this shouldn't cause
> any problems with the way my application works, design-wise it bothers me
> having that step between creating the temp table and doing the update to
> the Queue table. How can I temporarily lock the Queue table for these two
> steps?
>|||Random wrote:
> Are locks necessary in a stored procedure?
> I have a short stored procedure where I want to update n records to
> indicate their intended use in a non-database procedure. I want to
> select these records based on the priority and scheduled process
> date, and according to a parameter in the procedure. This is what I
> see as optimal...
> ----
> CREATE PROCEDURE dbo.GetBatchForProcessing
> @.thread_guid uniqueidentifier = Null OUTPUT,
> @.batch_count int
> AS
> SET @.thread_guid = ISNULL(@.thread_guid, NewID())
> UPDATE Queue SET lockID = @.thread_guid
> WHERE queueID IN (SELECT TOP @.batch_count queueID FROM Queue WHERE
> process_date IS NULL AND lockID IS NULL AND scheduled_date <=
> getdate() ORDER BY priority, scheduled_date)
> SELECT * FROM Queue WHERE lockID = @.thread_guid
> ----
> ...but I cannot do this because the @.batch_count parameter cannot be
> used dynamically in the TOP statement, and just to say up front I am
> REALLY against using dynamic SQL in stored procedures. I think it
> obviates the point of using the procedure in the first place.
> Since the limit on the rows I want affected in the UPDATE are in a
> subquery, using SET ROWCOUNT @.batch_count won't work. Even if it
> did, SQL Server won't allow the ORDER BY clause in the subquery
> without the TOP statement.
> Which leaves me with using SET ROWCOUNT to select the set of
> queueID's into a temp table, and then perform the update clause to
> match against what I've put into the temp table. Now, while doing
> this shouldn't cause any problems with the way my application works,
> design-wise it bothers me having that step between creating the temp
> table and doing the update to the Queue table. How can I temporarily
> lock the Queue table for these two steps?
If you want to lock the entire table you can use a TABLOCKX hint on the
query that retrieves the data for the temp table.
David Gugick
Imceda Software
www.imceda.com|||You could try using a table variable with rowcount.. Here's an example I
created using my data...
DECLARE @.varInt INT
SET @.varInt = 3
SET ROWCOUNT @.varInt
DECLARE @.table TABLE(BrochureModelID INT)
INSERT INTO @.table SELECT
BrochureModelID
FROM BrochureModel
UPDATE BrochureModel SET
CreatedBy = 'someone'
WHERE BrochureModelID IN (SELECT BrochureModelID FROM @.table)
SET ROWCOUNT 0
--update here...
This seemed to work..
"Random" wrote:

> Are locks necessary in a stored procedure?
> I have a short stored procedure where I want to update n records to indica
te
> their intended use in a non-database procedure. I want to select these
> records based on the priority and scheduled process date, and according to
a
> parameter in the procedure. This is what I see as optimal...
> ----
> CREATE PROCEDURE dbo.GetBatchForProcessing
> @.thread_guid uniqueidentifier = Null OUTPUT,
> @.batch_count int
> AS
> SET @.thread_guid = ISNULL(@.thread_guid, NewID())
> UPDATE Queue SET lockID = @.thread_guid
> WHERE queueID IN (SELECT TOP @.batch_count queueID FROM Queue WHERE
> process_date IS NULL AND lockID IS NULL AND scheduled_date <= getdate()
> ORDER BY priority, scheduled_date)
> SELECT * FROM Queue WHERE lockID = @.thread_guid
> ----
> ...but I cannot do this because the @.batch_count parameter cannot be used
> dynamically in the TOP statement, and just to say up front I am REALLY
> against using dynamic SQL in stored procedures. I think it obviates the
> point of using the procedure in the first place.
> Since the limit on the rows I want affected in the UPDATE are in a subquer
y,
> using SET ROWCOUNT @.batch_count won't work. Even if it did, SQL Server
> won't allow the ORDER BY clause in the subquery without the TOP statement.
> Which leaves me with using SET ROWCOUNT to select the set of queueID's int
o
> a temp table, and then perform the update clause to match against what I'v
e
> put into the temp table. Now, while doing this shouldn't cause any proble
ms
> with the way my application works, design-wise it bothers me having that
> step between creating the temp table and doing the update to the Queue
> table. How can I temporarily lock the Queue table for these two steps?
>
>|||I'm running SQL Server 2000. I ran the sp_dbcmptlevel procedure to ensure I
was running compatibility level correctly, but that didn't change the TOP
restriction. I'd read in an earlier message in this group that that feature
wasn't going to be available unitl SQL Server 2005.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:eX9huvlKFHA.3076@.tk2msftngp13.phx.gbl...
> Random,
> TOP @.Variable works fine in SQL Server 2000. What version are you
> running? Or, did you upgrade to 2000, but your database remains in 70
> mode? In that case use sp_dbcmptlevel to set it to 80.
> RLF
> "Random" <cipherlad@.hotmail.com> wrote in message
> news:eQHDkjlKFHA.4032@.TK2MSFTNGP14.phx.gbl...
>