Hi,
I'm having problems with VC++ 6.0 reading an Access 2000 database.
CRecordset::Close() throws an exception. I've created a simple test
dialog which does an open and close of a CRecordset. The database has
one table called 'Addresses' in it. The CAddressSet was created by the
Class Wizard.
void CDatabaseDlg::OnOK()
{
CDatabase db;
db.Open(NULL,FALSE,FALSE,"DSN=c:\\dev\\db1.mdb");
// Construct the snapshot object
CAddressSet rsAddr( NULL );
// Set options if desired, then open the recordset
rsAddr.Open(CRecordset::snapshot, NULL, CRecordset::none );
rsAddr.Close( );
db.Close( );
Dialog::OnOK();
}
The Close method throws on the AFX_SQP_SERVER line below:
RETCODE nRetCode;
if (m_hstmt != SQL_NULL_HSTMT)
{
AFX_SQL_SYNC(::SQLFreeStmt(m_hstmt, SQL_DROP));
m_hstmt = SQL_NULL_HSTMT;
}
This program just couldn't get any simpler - and yet it doesn't work!
Anybody got any clue as to why?
Many thanks,
Jeremy
Recordset is not connected to database? Recordset is completely empty?
etc?
Free software - Baxter Codeworks www.baxcode.com
"Jezzer" <Jemtaylor@.yahoo.com> wrote in message
news:1115894836.839503.104060@.g14g2000cwa.googlegr oups.com...
> Hi,
> I'm having problems with VC++ 6.0 reading an Access 2000 database.
> CRecordset::Close() throws an exception. I've created a simple test
> dialog which does an open and close of a CRecordset. The database has
> one table called 'Addresses' in it. The CAddressSet was created by the
> Class Wizard.
> void CDatabaseDlg::OnOK()
> {
> CDatabase db;
> db.Open(NULL,FALSE,FALSE,"DSN=c:\\dev\\db1.mdb");
> // Construct the snapshot object
> CAddressSet rsAddr( NULL );
> // Set options if desired, then open the recordset
> rsAddr.Open(CRecordset::snapshot, NULL, CRecordset::none );
>
> rsAddr.Close( );
> db.Close( );
>
> Dialog::OnOK();
> }
> The Close method throws on the AFX_SQP_SERVER line below:
> RETCODE nRetCode;
> if (m_hstmt != SQL_NULL_HSTMT)
> {
> AFX_SQL_SYNC(::SQLFreeStmt(m_hstmt, SQL_DROP));
> m_hstmt = SQL_NULL_HSTMT;
> }
> This program just couldn't get any simpler - and yet it doesn't work!
> Anybody got any clue as to why?
> Many thanks,
> Jeremy
>
|||Yes, I see why it might look like that with the NULL in the CRecordSet
declaration. I've defined my test database using ODBC manager and the
GetDefaultConnect() method looks like this:
CString CAddressSet::GetDefaultConnect()
{
return _T("ODBC;DSN=db1");
}
where 'db1' is my database with the Addresses table in it. This was all
put there by the wizard.
I have had this same test retrieving the data succesfully but I took
that stuff out to make the example less cluttered.
It's just the Close that crashes whether you retrieve stuff or not.
cheers anyway...
Jeremy
|||Hi all,
I've made some progress with this problem now. I've discovered that
CRecordSet::Close() only crashes when running under Visual Studio -
when the exe is run stand-alone it works fine. I can remove the 'fudge'
and it loads the DB Ok.
The 'fudge' I use where I set m_hstmnt = SQL_NULL_HSTMT in the
::Close() method means that when debugging the method does not crash -
but it seems to cause the Access interface to run *extremely* slowly.
No CPU being used - just seems to be waiting an awful lot. So I suspect
that whatever is being freed by the 'SQLFreeStmt' call is getting
clogged. My hunch is that Access or ODBC is waiting for a space in some
buffer to open up and eventually decides to remove the oldest 'thing' -
whatever that is.
A search of Google Groups shows that lots of people have had this
problem - is there nobody out there that knows the answer?
cheers
Jeremy
Showing posts with label causes. Show all posts
Showing posts with label causes. Show all posts
Thursday, March 29, 2012
CRecordset.Close() causes exception
Hi,
I'm having problems with VC++ 6.0 reading an Access 2000 database.
CRecordset::Close() throws an exception. I've created a simple test
dialog which does an open and close of a CRecordset. The database has
one table called 'Addresses' in it. The CAddressSet was created by the
Class Wizard.
void CDatabaseDlg::OnOK()
{
CDatabase db;
db.Open(NULL,FALSE,FALSE,"DSN=c:\\dev\\db1.mdb");
// Construct the snapshot object
CAddressSet rsAddr( NULL );
// Set options if desired, then open the recordset
rsAddr.Open(CRecordset::snapshot, NULL, CRecordset::none );
rsAddr.Close( );
db.Close( );
Dialog::OnOK();
}
The Close method throws on the AFX_SQP_SERVER line below:
RETCODE nRetCode;
if (m_hstmt != SQL_NULL_HSTMT)
{
AFX_SQL_SYNC(::SQLFreeStmt(m_hstmt, SQL_DROP));
m_hstmt = SQL_NULL_HSTMT;
}
This program just couldn't get any simpler - and yet it doesn't work!
Anybody got any clue as to why'
Many thanks,
JeremyRecordset is not connected to database? Recordset is completely empty?
etc?
----
Free software - Baxter Codeworks www.baxcode.com
----
"Jezzer" <Jemtaylor@.yahoo.com> wrote in message
news:1115894836.839503.104060@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm having problems with VC++ 6.0 reading an Access 2000 database.
> CRecordset::Close() throws an exception. I've created a simple test
> dialog which does an open and close of a CRecordset. The database has
> one table called 'Addresses' in it. The CAddressSet was created by the
> Class Wizard.
> void CDatabaseDlg::OnOK()
> {
> CDatabase db;
> db.Open(NULL,FALSE,FALSE,"DSN=c:\\dev\\db1.mdb");
> // Construct the snapshot object
> CAddressSet rsAddr( NULL );
> // Set options if desired, then open the recordset
> rsAddr.Open(CRecordset::snapshot, NULL, CRecordset::none );
>
> rsAddr.Close( );
> db.Close( );
>
> Dialog::OnOK();
> }
> The Close method throws on the AFX_SQP_SERVER line below:
> RETCODE nRetCode;
> if (m_hstmt != SQL_NULL_HSTMT)
> {
> AFX_SQL_SYNC(::SQLFreeStmt(m_hstmt, SQL_DROP));
> m_hstmt = SQL_NULL_HSTMT;
> }
> This program just couldn't get any simpler - and yet it doesn't work!
> Anybody got any clue as to why'
> Many thanks,
> Jeremy
>|||Yes, I see why it might look like that with the NULL in the CRecordSet
declaration. I've defined my test database using ODBC manager and the
GetDefaultConnect() method looks like this:
CString CAddressSet::GetDefaultConnect()
{
return _T("ODBC;DSN=db1");
}
where 'db1' is my database with the Addresses table in it. This was all
put there by the wizard.
I have had this same test retrieving the data succesfully but I took
that stuff out to make the example less cluttered.
It's just the Close that crashes whether you retrieve stuff or not.
cheers anyway...
Jeremy|||Hi all,
I've made some progress with this problem now. I've discovered that
CRecordSet::Close() only crashes when running under Visual Studio -
when the exe is run stand-alone it works fine. I can remove the 'fudge'
and it loads the DB Ok.
The 'fudge' I use where I set m_hstmnt = SQL_NULL_HSTMT in the
::Close() method means that when debugging the method does not crash -
but it seems to cause the Access interface to run *extremely* slowly.
No CPU being used - just seems to be waiting an awful lot. So I suspect
that whatever is being freed by the 'SQLFreeStmt' call is getting
clogged. My hunch is that Access or ODBC is waiting for a space in some
buffer to open up and eventually decides to remove the oldest 'thing' -
whatever that is.
A search of Google Groups shows that lots of people have had this
problem - is there nobody out there that knows the answer?
cheers
Jeremy
I'm having problems with VC++ 6.0 reading an Access 2000 database.
CRecordset::Close() throws an exception. I've created a simple test
dialog which does an open and close of a CRecordset. The database has
one table called 'Addresses' in it. The CAddressSet was created by the
Class Wizard.
void CDatabaseDlg::OnOK()
{
CDatabase db;
db.Open(NULL,FALSE,FALSE,"DSN=c:\\dev\\db1.mdb");
// Construct the snapshot object
CAddressSet rsAddr( NULL );
// Set options if desired, then open the recordset
rsAddr.Open(CRecordset::snapshot, NULL, CRecordset::none );
rsAddr.Close( );
db.Close( );
Dialog::OnOK();
}
The Close method throws on the AFX_SQP_SERVER line below:
RETCODE nRetCode;
if (m_hstmt != SQL_NULL_HSTMT)
{
AFX_SQL_SYNC(::SQLFreeStmt(m_hstmt, SQL_DROP));
m_hstmt = SQL_NULL_HSTMT;
}
This program just couldn't get any simpler - and yet it doesn't work!
Anybody got any clue as to why'
Many thanks,
JeremyRecordset is not connected to database? Recordset is completely empty?
etc?
----
Free software - Baxter Codeworks www.baxcode.com
----
"Jezzer" <Jemtaylor@.yahoo.com> wrote in message
news:1115894836.839503.104060@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm having problems with VC++ 6.0 reading an Access 2000 database.
> CRecordset::Close() throws an exception. I've created a simple test
> dialog which does an open and close of a CRecordset. The database has
> one table called 'Addresses' in it. The CAddressSet was created by the
> Class Wizard.
> void CDatabaseDlg::OnOK()
> {
> CDatabase db;
> db.Open(NULL,FALSE,FALSE,"DSN=c:\\dev\\db1.mdb");
> // Construct the snapshot object
> CAddressSet rsAddr( NULL );
> // Set options if desired, then open the recordset
> rsAddr.Open(CRecordset::snapshot, NULL, CRecordset::none );
>
> rsAddr.Close( );
> db.Close( );
>
> Dialog::OnOK();
> }
> The Close method throws on the AFX_SQP_SERVER line below:
> RETCODE nRetCode;
> if (m_hstmt != SQL_NULL_HSTMT)
> {
> AFX_SQL_SYNC(::SQLFreeStmt(m_hstmt, SQL_DROP));
> m_hstmt = SQL_NULL_HSTMT;
> }
> This program just couldn't get any simpler - and yet it doesn't work!
> Anybody got any clue as to why'
> Many thanks,
> Jeremy
>|||Yes, I see why it might look like that with the NULL in the CRecordSet
declaration. I've defined my test database using ODBC manager and the
GetDefaultConnect() method looks like this:
CString CAddressSet::GetDefaultConnect()
{
return _T("ODBC;DSN=db1");
}
where 'db1' is my database with the Addresses table in it. This was all
put there by the wizard.
I have had this same test retrieving the data succesfully but I took
that stuff out to make the example less cluttered.
It's just the Close that crashes whether you retrieve stuff or not.
cheers anyway...
Jeremy|||Hi all,
I've made some progress with this problem now. I've discovered that
CRecordSet::Close() only crashes when running under Visual Studio -
when the exe is run stand-alone it works fine. I can remove the 'fudge'
and it loads the DB Ok.
The 'fudge' I use where I set m_hstmnt = SQL_NULL_HSTMT in the
::Close() method means that when debugging the method does not crash -
but it seems to cause the Access interface to run *extremely* slowly.
No CPU being used - just seems to be waiting an awful lot. So I suspect
that whatever is being freed by the 'SQLFreeStmt' call is getting
clogged. My hunch is that Access or ODBC is waiting for a space in some
buffer to open up and eventually decides to remove the oldest 'thing' -
whatever that is.
A search of Google Groups shows that lots of people have had this
problem - is there nobody out there that knows the answer?
cheers
Jeremy
Friday, February 24, 2012
creating jobs programmatically causes deadlocks
I'm using the transactional sproc below to create jobs in an automated
fashion that will run cmdexecs after a configurable pause. This is
potentially HIGH VOLUME! The sproc is run from a VB.NET EXE using the
ADO.NET command.executenonquery method during the processing of text files to
determine what the job should do.
I have two essentially identical servers (4 X 3.2GHz CPU, 3GB RAM). On one
server, dropping 30 trigger files results in ~150 jobs being created in ~30
seconds (I have a thread.sleep(200) between each job creation due to another
issue). On another server dropping just 10 of the trigger files causes
deadlocks consistently. Both servers are running SQL 2000 SP3 on WinServer
2003 Standard edition and
the assembly is written against DNF 1.1.
Is the logic in the sproc faulty? The transaction is necessary since the
job needs all the properties to do it's job.
What server configurations affect deadlock timeouts, etc. Any help
would be appreciated.
Sproc follows:
--creates single use, self deleting job
CREATE PROCEDURE usp_RunCmdJobSoon (
@.JobName as varchar(50),
@.CmdText as varchar(1000),
@.DelaySeconds as int = 30,
@.RunOnIdle as bit = 0,
@.DeleteWhenDone as bit = 0
)
AS
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
declare @.Date as datetime
declare @.NewDate as int
declare @.NewTime as int
DECLARE @.String char(30)
SET @.String = 'sa'
/*
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = @.JobName)
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR ('Unable to import job %s since there is already a
multi-server job with this name.', 16, 1,@.JobName) WITH LOG
GOTO QuitWithRollback
END
ELSE
RAISERROR ('Unable to create job %s since it already exists.', 16,
1,@.JobName) WITH LOG
GOTO QuitWithRollback
END
*/
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = @.JobName, @.owner_login_name = @.String, @.description = N'Automated
Job Run', @.category_name = N'ODS Automated', @.enabled = 1,
@.notify_level_email = 0, @.notify_level_page = 0, @.notify_level_netsend = 0,
@.notify_level_eventlog = 3, @.delete_level= @.DeleteWhenDone
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
1, @.step_name = @.JobName, @.command = @.CmdText, @.database_name = N'', @.server
= N'', @.database_user_name = N'', @.subsystem = N'CmdExec',
@.cmdexec_success_code = 0, @.flags = 0, @.retry_attempts = 2, @.retry_interval =
5, @.output_file_name = N'', @.on_success_step_id = 0, @.on_success_action = 1,
@.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
--in case of restart
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Run on start', @.enabled = 1, @.freq_type = 64
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
--in case of Idle condition
IF (@.RunOnIdle = 1)
BEGIN
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Run on Idle', @.enabled = 1, @.freq_type = 128
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
-- Delay start
SET @.String = 'Run job soon'
set @.Date = dateadd(ss,@.DelaySeconds,Getdate())
set @.NewDate = cast(convert(varchar(8), @.Date, 112) as int)
set @.NewTime = cast(replace(convert(varchar(8), @.Date, 108), ':', '') as
int)
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= @.String, @.enabled = 1, @.freq_type = 1, @.active_start_date = @.NewDate,
@.active_start_time = @.NewTime
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
RAISERROR ('Error %s while creating JobName= %s ', 16, 1,@.@.Error,
@.JobName) WITH LOG
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Hi,
Thanks for your post and I will take ownership of this thread now and help
you with this issue.
From your descriptions, I understood that your stored procedures goes
smoothly in one machine but it will be easily hangs in another machine.
Have I understood you? Correct me if I was wrong.
Generally, performance issues can be caused by various factors, and it is
difficult to locate the root cause in a newsgroup thread. If the issue
still exists after you have used the troubleshooting steps above, to
efficiently troubleshoot a performance issue, we recommend that you contact
Microsoft Product Support Services and open a support incident and work
with a dedicated Support Professional. Please be advised that contacting
phone support will be a charged call. To obtain the phone numbers for
specific technology request please take a look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
If you'd still like to continue working via the newsgroup, I want to set
your expectations that the issue might take a long time to narrow down.
During the course of troubleshooting, we may redirect you to PSS if
required.
For now, I would love to collect more information about your issue
1. Any Error Message given by SQL Server 2000?
2. Collect the information from sp_blocker_pss80 as the following KB
insturcted
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Moreover, here are some documents for your reference to find what is
blocked
Resolving blocking problems that are caused by lock escalation in SQL Server
http://support.microsoft.com/?id=323630
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/?id=838622
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing,
I already have a blocker output, but it's pretty straight forward. The ONLY
thing happening on the system is the creation of jobs through this sproc.
The sproc is blocking itself from another instance doing the exact same thing
at the same time. That's why I included the sproc in my first post, it's
blocking itself.
How can I get more blocker output to you? Maybe I can email them to you?
blocker output excerpt:
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 8 No Waittypes: 2004-09-30 15:39:06.890 0
AAG0J6DW06
8 No Waittypes: 2004-09-30 15:39:21.907 0 AAG0J6DW06
8.2 Start time: 2004-09-30 15:39:36.920 0
SYSPROCESSES AAG0J6DW06 134218488
spid status blocked open_tran waitresource
waittype waittime cmd lastwaittype cpu
physical_io memusage last_batch
login_time net_address net_library dbid ecid kpid
hostname
hostprocess loginame
program_name
nt_domain
nt_username
uid sid
sql_handle
stmt_start stmt_end
-- -- -- --
-----------
-
-- -- -- --
-- -- -- --
-- -- -- -- -- --
------
------
------
------
------
--------
--- -- --
51 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 4547 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:32.360
2004-09-30 15:39:31.610 000E7FB55C16 Named Pipes 4 0 1008
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
61 sleeping 64 1 KEY: 4:2089058478:1
(bf013f652931)
0x0003 5281 CONDITIONAL LCK_M_S
0 0 18 2004-09-30 15:39:31.623
2004-09-30 15:39:31.623 000E7FB55C16 Named Pipes 4 0 5700
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
64 sleeping 61 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5406 CONDITIONAL LCK_M_S
0 0 17 2004-09-30 15:39:31.623
2004-09-30 15:39:31.623 000E7FB55C16 Named Pipes 4 0 4136
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
65 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 4672 CONDITIONAL LCK_M_S
0 0 0 2004-09-30 15:39:32.360
2004-09-30 15:39:31.623 000E7FB55C16 Named Pipes 4 0 5616
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
66 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 11 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 3308
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
67 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 3284
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
68 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 2064
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
69 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 5780
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
70 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 1204
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
ESP 110
SYSPROC FIRST PASS
spid ecid waittype
-- -- --
64 0 0x0003
51 0 0x0003
61 0 0x0003
65 0 0x0003
66 0 0x0003
67 0 0x0003
68 0 0x0003
69 0 0x0003
70 0 0x0003
Blocking via locks at 2004-09-30 15:39:36.920
SPIDs at the head of blocking chains
spid
SYSLOCKINFO
spid ecid dbid ObjId IndId Type Resource Mode
Status TransID TransUOW
-- -- -- -- -- -- -- --
-- -- --
51 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
65 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
70 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
69 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
68 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
67 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
66 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
64 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
61 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
70 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
69 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
68 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
67 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
66 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
65 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
51 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
64 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
61 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
65 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171525 00000000-0000-0000-0000-000000000000
69 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171463 00000000-0000-0000-0000-000000000000
70 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171486 00000000-0000-0000-0000-000000000000
66 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171394 00000000-0000-0000-0000-000000000000
51 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171532 00000000-0000-0000-0000-000000000000
67 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171417 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 PAG 1:484 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
68 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171440 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 KEY (bf013f652931) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 KEY (bf013f652931) S
WAIT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 1 PAG 1:4701 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 PAG 1:4886 IS
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 PAG 1:4886 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 5 KEY (d001dca5d2ee) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 4 KEY (2302a3aeaeb3) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 4 PAG 1:1738 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 4 PAG 1:1738 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 3 PAG 1:1736 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 3 PAG 1:1736 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 5 PAG 1:1740 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 5 PAG 1:1740 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
65 0 4 2089058478 0 TAB IS
GRANT 37171525 00000000-0000-0000-0000-000000000000
69 0 4 2089058478 0 TAB IS
GRANT 37171463 00000000-0000-0000-0000-000000000000
70 0 4 2089058478 0 TAB IS
GRANT 37171486 00000000-0000-0000-0000-000000000000
66 0 4 2089058478 0 TAB IS
GRANT 37171394 00000000-0000-0000-0000-000000000000
51 0 4 2089058478 0 TAB IS
GRANT 37171532 00000000-0000-0000-0000-000000000000
67 0 4 2089058478 0 TAB IS
GRANT 37171417 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 0 TAB IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
68 0 4 2089058478 0 TAB IS
GRANT 37171440 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 0 TAB IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 3 KEY (850364070d60) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 2 KEY (a2045bef58ed) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 1 KEY (d001b54b3a2e) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 4 KEY (3302eb1fa7ce) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 5 KEY (c0019414db93) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 2 KEY (a90444d60925) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 2 KEY (a904a60ed3b4) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 3 KEY (95032cb6041d) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 0 TAB IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 0 TAB IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 2 KEY (a204cbc41ffd) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 2 PAG 1:11483 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 2 PAG 1:28064 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 2 PAG 1:28064 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 1 KEY (c0018819704b) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 KEY (cf0177d4204c) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171280 00000000-0000-0000-0000-000000000000
66 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171394 00000000-0000-0000-0000-000000000000
67 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171417 00000000-0000-0000-0000-000000000000
68 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171440 00000000-0000-0000-0000-000000000000
69 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171463 00000000-0000-0000-0000-000000000000
70 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171486 00000000-0000-0000-0000-000000000000
65 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171525 00000000-0000-0000-0000-000000000000
51 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171532 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 1 PAG 1:24427 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 2 PAG 1:44674 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
ESL 0
|||Hi,
Thanks for your prompt updates and information!
Yes, it will be highly appreciated if you could provide me the information
by means of email. Here are some steps to collect the information I want
this time
1. Create a script file named 'queryprocess.sql' with the following query
to run commands in a loop.
WHILE 1=1
BEGIN
Select * from master.dbo.sysprocesses
WAITFOR DELAY '00:00:05'
END
GO
2. Run the script file created in step d from either Isql.exe or the
Osql.exe query tool in a Windows command prompt on the computer that is
running SQL Server. For example,
osql -E -Sserver_name -iqueryprocess.sql -oqueryprocess.out -w2000
3. Send the information to us for reviewing and my email is
v-mingqc@.online.microsoft.com (remove 'online' as it is only for SPAM)
In the meanwhile, from syslockinfo, I found many sp are waiting for an
object 2089058478. Is this the stored procedure itself? Please use the
following command to check the object name: object_name( 2089058478)
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi,
I am just checking on your progress regarding the information that was sent
you! Have you tried the steps I provided to you? I wonder how the testing
is going. If you encounter any difficulty, please do not hesitate to let me
know. Please post here and let me know the status of your issue. Without
your further information, it's very hard for me to continue with the
troubleshooting.
Looking forward to hearing from you soon
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
fashion that will run cmdexecs after a configurable pause. This is
potentially HIGH VOLUME! The sproc is run from a VB.NET EXE using the
ADO.NET command.executenonquery method during the processing of text files to
determine what the job should do.
I have two essentially identical servers (4 X 3.2GHz CPU, 3GB RAM). On one
server, dropping 30 trigger files results in ~150 jobs being created in ~30
seconds (I have a thread.sleep(200) between each job creation due to another
issue). On another server dropping just 10 of the trigger files causes
deadlocks consistently. Both servers are running SQL 2000 SP3 on WinServer
2003 Standard edition and
the assembly is written against DNF 1.1.
Is the logic in the sproc faulty? The transaction is necessary since the
job needs all the properties to do it's job.
What server configurations affect deadlock timeouts, etc. Any help
would be appreciated.
Sproc follows:
--creates single use, self deleting job
CREATE PROCEDURE usp_RunCmdJobSoon (
@.JobName as varchar(50),
@.CmdText as varchar(1000),
@.DelaySeconds as int = 30,
@.RunOnIdle as bit = 0,
@.DeleteWhenDone as bit = 0
)
AS
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
declare @.Date as datetime
declare @.NewDate as int
declare @.NewTime as int
DECLARE @.String char(30)
SET @.String = 'sa'
/*
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = @.JobName)
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR ('Unable to import job %s since there is already a
multi-server job with this name.', 16, 1,@.JobName) WITH LOG
GOTO QuitWithRollback
END
ELSE
RAISERROR ('Unable to create job %s since it already exists.', 16,
1,@.JobName) WITH LOG
GOTO QuitWithRollback
END
*/
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = @.JobName, @.owner_login_name = @.String, @.description = N'Automated
Job Run', @.category_name = N'ODS Automated', @.enabled = 1,
@.notify_level_email = 0, @.notify_level_page = 0, @.notify_level_netsend = 0,
@.notify_level_eventlog = 3, @.delete_level= @.DeleteWhenDone
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
1, @.step_name = @.JobName, @.command = @.CmdText, @.database_name = N'', @.server
= N'', @.database_user_name = N'', @.subsystem = N'CmdExec',
@.cmdexec_success_code = 0, @.flags = 0, @.retry_attempts = 2, @.retry_interval =
5, @.output_file_name = N'', @.on_success_step_id = 0, @.on_success_action = 1,
@.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
--in case of restart
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Run on start', @.enabled = 1, @.freq_type = 64
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
--in case of Idle condition
IF (@.RunOnIdle = 1)
BEGIN
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Run on Idle', @.enabled = 1, @.freq_type = 128
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
-- Delay start
SET @.String = 'Run job soon'
set @.Date = dateadd(ss,@.DelaySeconds,Getdate())
set @.NewDate = cast(convert(varchar(8), @.Date, 112) as int)
set @.NewTime = cast(replace(convert(varchar(8), @.Date, 108), ':', '') as
int)
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= @.String, @.enabled = 1, @.freq_type = 1, @.active_start_date = @.NewDate,
@.active_start_time = @.NewTime
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
RAISERROR ('Error %s while creating JobName= %s ', 16, 1,@.@.Error,
@.JobName) WITH LOG
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Hi,
Thanks for your post and I will take ownership of this thread now and help
you with this issue.
From your descriptions, I understood that your stored procedures goes
smoothly in one machine but it will be easily hangs in another machine.
Have I understood you? Correct me if I was wrong.
Generally, performance issues can be caused by various factors, and it is
difficult to locate the root cause in a newsgroup thread. If the issue
still exists after you have used the troubleshooting steps above, to
efficiently troubleshoot a performance issue, we recommend that you contact
Microsoft Product Support Services and open a support incident and work
with a dedicated Support Professional. Please be advised that contacting
phone support will be a charged call. To obtain the phone numbers for
specific technology request please take a look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
If you'd still like to continue working via the newsgroup, I want to set
your expectations that the issue might take a long time to narrow down.
During the course of troubleshooting, we may redirect you to PSS if
required.
For now, I would love to collect more information about your issue
1. Any Error Message given by SQL Server 2000?
2. Collect the information from sp_blocker_pss80 as the following KB
insturcted
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Moreover, here are some documents for your reference to find what is
blocked
Resolving blocking problems that are caused by lock escalation in SQL Server
http://support.microsoft.com/?id=323630
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/?id=838622
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing,
I already have a blocker output, but it's pretty straight forward. The ONLY
thing happening on the system is the creation of jobs through this sproc.
The sproc is blocking itself from another instance doing the exact same thing
at the same time. That's why I included the sproc in my first post, it's
blocking itself.
How can I get more blocker output to you? Maybe I can email them to you?
blocker output excerpt:
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 8 No Waittypes: 2004-09-30 15:39:06.890 0
AAG0J6DW06
8 No Waittypes: 2004-09-30 15:39:21.907 0 AAG0J6DW06
8.2 Start time: 2004-09-30 15:39:36.920 0
SYSPROCESSES AAG0J6DW06 134218488
spid status blocked open_tran waitresource
waittype waittime cmd lastwaittype cpu
physical_io memusage last_batch
login_time net_address net_library dbid ecid kpid
hostname
hostprocess loginame
program_name
nt_domain
nt_username
uid sid
sql_handle
stmt_start stmt_end
-- -- -- --
-----------
-
-- -- -- --
-- -- -- --
-- -- -- -- -- --
------
------
------
------
------
--------
--- -- --
51 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 4547 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:32.360
2004-09-30 15:39:31.610 000E7FB55C16 Named Pipes 4 0 1008
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
61 sleeping 64 1 KEY: 4:2089058478:1
(bf013f652931)
0x0003 5281 CONDITIONAL LCK_M_S
0 0 18 2004-09-30 15:39:31.623
2004-09-30 15:39:31.623 000E7FB55C16 Named Pipes 4 0 5700
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
64 sleeping 61 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5406 CONDITIONAL LCK_M_S
0 0 17 2004-09-30 15:39:31.623
2004-09-30 15:39:31.623 000E7FB55C16 Named Pipes 4 0 4136
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
65 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 4672 CONDITIONAL LCK_M_S
0 0 0 2004-09-30 15:39:32.360
2004-09-30 15:39:31.623 000E7FB55C16 Named Pipes 4 0 5616
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
66 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 11 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 3308
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
67 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 3284
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
68 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 2064
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
69 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 5780
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
70 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 1204
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
ESP 110
SYSPROC FIRST PASS
spid ecid waittype
-- -- --
64 0 0x0003
51 0 0x0003
61 0 0x0003
65 0 0x0003
66 0 0x0003
67 0 0x0003
68 0 0x0003
69 0 0x0003
70 0 0x0003
Blocking via locks at 2004-09-30 15:39:36.920
SPIDs at the head of blocking chains
spid
SYSLOCKINFO
spid ecid dbid ObjId IndId Type Resource Mode
Status TransID TransUOW
-- -- -- -- -- -- -- --
-- -- --
51 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
65 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
70 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
69 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
68 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
67 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
66 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
64 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
61 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
70 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
69 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
68 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
67 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
66 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
65 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
51 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
64 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
61 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
65 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171525 00000000-0000-0000-0000-000000000000
69 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171463 00000000-0000-0000-0000-000000000000
70 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171486 00000000-0000-0000-0000-000000000000
66 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171394 00000000-0000-0000-0000-000000000000
51 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171532 00000000-0000-0000-0000-000000000000
67 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171417 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 PAG 1:484 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
68 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171440 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 KEY (bf013f652931) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 KEY (bf013f652931) S
WAIT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 1 PAG 1:4701 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 PAG 1:4886 IS
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 PAG 1:4886 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 5 KEY (d001dca5d2ee) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 4 KEY (2302a3aeaeb3) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 4 PAG 1:1738 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 4 PAG 1:1738 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 3 PAG 1:1736 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 3 PAG 1:1736 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 5 PAG 1:1740 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 5 PAG 1:1740 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
65 0 4 2089058478 0 TAB IS
GRANT 37171525 00000000-0000-0000-0000-000000000000
69 0 4 2089058478 0 TAB IS
GRANT 37171463 00000000-0000-0000-0000-000000000000
70 0 4 2089058478 0 TAB IS
GRANT 37171486 00000000-0000-0000-0000-000000000000
66 0 4 2089058478 0 TAB IS
GRANT 37171394 00000000-0000-0000-0000-000000000000
51 0 4 2089058478 0 TAB IS
GRANT 37171532 00000000-0000-0000-0000-000000000000
67 0 4 2089058478 0 TAB IS
GRANT 37171417 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 0 TAB IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
68 0 4 2089058478 0 TAB IS
GRANT 37171440 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 0 TAB IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 3 KEY (850364070d60) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 2 KEY (a2045bef58ed) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 1 KEY (d001b54b3a2e) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 4 KEY (3302eb1fa7ce) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 5 KEY (c0019414db93) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 2 KEY (a90444d60925) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 2 KEY (a904a60ed3b4) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 3 KEY (95032cb6041d) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 0 TAB IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 0 TAB IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 2 KEY (a204cbc41ffd) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 2 PAG 1:11483 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 2 PAG 1:28064 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 2 PAG 1:28064 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 1 KEY (c0018819704b) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 KEY (cf0177d4204c) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171280 00000000-0000-0000-0000-000000000000
66 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171394 00000000-0000-0000-0000-000000000000
67 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171417 00000000-0000-0000-0000-000000000000
68 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171440 00000000-0000-0000-0000-000000000000
69 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171463 00000000-0000-0000-0000-000000000000
70 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171486 00000000-0000-0000-0000-000000000000
65 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171525 00000000-0000-0000-0000-000000000000
51 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171532 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 1 PAG 1:24427 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 2 PAG 1:44674 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
ESL 0
|||Hi,
Thanks for your prompt updates and information!
Yes, it will be highly appreciated if you could provide me the information
by means of email. Here are some steps to collect the information I want
this time
1. Create a script file named 'queryprocess.sql' with the following query
to run commands in a loop.
WHILE 1=1
BEGIN
Select * from master.dbo.sysprocesses
WAITFOR DELAY '00:00:05'
END
GO
2. Run the script file created in step d from either Isql.exe or the
Osql.exe query tool in a Windows command prompt on the computer that is
running SQL Server. For example,
osql -E -Sserver_name -iqueryprocess.sql -oqueryprocess.out -w2000
3. Send the information to us for reviewing and my email is
v-mingqc@.online.microsoft.com (remove 'online' as it is only for SPAM)
In the meanwhile, from syslockinfo, I found many sp are waiting for an
object 2089058478. Is this the stored procedure itself? Please use the
following command to check the object name: object_name( 2089058478)
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi,
I am just checking on your progress regarding the information that was sent
you! Have you tried the steps I provided to you? I wonder how the testing
is going. If you encounter any difficulty, please do not hesitate to let me
know. Please post here and let me know the status of your issue. Without
your further information, it's very hard for me to continue with the
troubleshooting.
Looking forward to hearing from you soon
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Subscribe to:
Posts (Atom)