how to insert records from tiggers at batch update process

I want to split the records from table1 and insert the splited records to table2. The trigger is working fine when i am trying to update single record. but the trigger is not working properly at the time of batch update. At the time of batch update it is split the last record from the query and insert that into table2 instead of inserting all the records.

Pls help me........I suspect you wrote your trigger to work with one record at a time. I say this because of your comment of the trigger working for a one record update and the LAST record in a batch. Take a look at your trigger and ask yourself "What happens if I have three records to deal with in either the inserted OR deleted temp tables?"

Post your trigger and maybe we can offer some suggestions.|||Hi

This is the trigger i have used to split the records for various conditions from table1 and insert the splitted records into another table table2

The Problem i'm facing is if it is single update(if only one row is affected) the trigger is working fine,
but not for batch update.

if you are unable to follow this trigger pls give your own batch update example, we will try to implement that.

pls help me.

CREATE trigger udt_Break_Split
ON Hours_tmp
/*
this trigger is used to do the break split in the table hours_tmp
and insert the records into the table selnmuamid

*/

AFTER UPDATE

AS
Declare @.Id int
Declare @.Res_id nvarchar(8)
Declare @.DTime smalldatetime
Declare @.StartTime char(5)
Declare @.EndTime char(5)
Declare @.Pauze char(5)
Declare @.Hourworked nvarchar(5)
Declare @.Status char(1)
--Declare @.PPayroll bit
Declare @.Project nvarchar(10)
Declare @.Naam char(50)
Declare @.Type char(5)
Declare @.Hryear nvarchar(4)
Declare @.Totmin char(5)
Declare @.Endtimebr char(5)
Declare @.Stime char(5)
Declare @.Start char(5)
Declare @.Processed char(1)
Declare @.del_DTime smalldatetime
Declare @.del_StartTime char(5)
Declare @.del_EndTime char(5)
Declare @.Del_id int
Declare @.Totrows int

--update hours_tmp set hours_tmp.processed_payroll =1 from hours_tmp , inserted where hours_tmp.id = inserted.id

Select @.Id=Id,
@.Res_id=Res_id,
@.DTime=[Datetime],
@.Start=Starttime,
@.EndTime=Endtime,
@.Pauze=Pauze,
@.Hourworked=Hoursworked,
@.Status=Status,
@.Project=Project,
@.Naam=Naam,
@.Type=Type,
@.Hryear=Hryear,
@.Processed=Processed
From inserted -- i ,Hours_tmp h where i.[Id] = h.[id]

Select @.del_DTime=[DateTime],
@.del_Starttime=Starttime,
@.del_endTime=Endtime ,
@.del_id=id
From Deleted

Delete From Selnmuamid
Where Res_id=@.Res_id
and Datetime=@.del_DTime
and Starttime >=@.del_starttime
and Endtime <=@.del_endtime

-- This is To Get total hours (hourworked column in the table ) in minutes

Select @.Totmin=Sum(Convert(Integer,(Left(@.hourworked,Char index('.',@.hourworked)-1) *
60)) +Convert(Integer,(Right(@.hourworked,Len(@.hourworke d) -
Charindex('.',@.hourworked))))),@.Endtimebr=Sum(Conv ert(Integer,(Left(@.Endtime,Charindex(':',@.Endtime) -1) *
60)) + Convert(Integer,(Right(@.Endtime,Len(@.Endtime) -
Charindex(':',@.Endtime))))),@.Stime=Sum(Convert(Int eger,(Left(@.Start,Charindex(':',@.Start)-1) * 60)) +
Convert(Integer,(Right(@.Start,Len(@.Start) - Charindex(':',@.Start)))))

--Values Passed as Parameter to Stored procedure Break_Split

Exec Break_Split @.Id,@.Res_id,@.DTime,@.Start,@.EndTime,@.Pauze,
@.Hourworked,@.Status,@.Project,@.Naam,@.Type,
@.Hryear,@.Totmin,@.Endtimebr,@.STime,@.Processed|||basically I just wrapped your code in a while loop to step through each record in the inserted temp table. Then I moveded the delete Selnmuamid to the end.

This has NOT been tested!

Code:
------------------------------

CREATE trigger udt_Break_Split
ON Hours_tmp
/*
this trigger is used to do the break split in the table hours_tmp
and insert the records into the table selnmuamid

*/

AFTER UPDATE

AS

-- ----------------
-- One Declare is more efficient than multipule declares
-- ----------------
Declare @.Id int
, @.Res_id nvarchar(8)
, @.DTime smalldatetime
, @.StartTime char(5)
, @.EndTime char(5)
, @.Pauze char(5)
, @.Hourworked nvarchar(5)
, @.Status char(1)
-- , @.PPayroll bit
, @.Project nvarchar(10)
, @.Naam char(50)
, @.Type char(5)
, @.Hryear nvarchar(4)
, @.Totmin char(5)
, @.Endtimebr char(5)
, @.Stime char(5)
, @.Start char(5)
, @.Processed char(1)
, @.del_DTime smalldatetime
, @.del_StartTime char(5)
, @.del_EndTime char(5)
, @.Del_id int
, @.Totrows int

--update hours_tmp set hours_tmp.processed_payroll =1 from hours_tmp , inserted where hours_tmp.id = inserted.id

select @.Id = min(ID) from inserted
while (@.Id is not null) begin
Select @.Res_id = Res_id
, @.DTime = [Datetime]
, @.Start = Starttime
, @.EndTime = Endtime
, @.Pauze = Pauze
, @.Hourworked = Hoursworked
, @.Status = Status
, @.Project = Project
, @.Naam = Naam
, @.Type = Type
, @.Hryear = Hryear
, @.Processed = Processed
-- ---------------------------------------------------
-- Hoursworked, Endtime and StartTime need to be converted to minutes
-- ---------------------------------------------------
, @.Totmin = Convert(Integer,(Left(Hoursworked,Charindex('.',Ho ursworked)-1) * 60)) + Convert(Integer,(Right(Hoursworked,Len(Hoursworked ) - Charindex('. ',Hoursworked))))
, @.Endtimebr = Convert(Integer,(Left(Endtime, Charindex(':',Endtime)-1) * 60)) + Convert(Integer,(Right(Endtime, Len(Endtime) - Charindex(':',Endtime))))
, @.Stime = Convert(Integer,(Left(Starttime, Charindex(':',Starttime)-1) * 60)) + Convert(Integer,(Right(Starttime, Len(Starttime) - Charindex(':',Starttime)))))
From inserted i
where id = @.id


-- This is To Get total hours (hourworked column in the table ) in minutes
/*
Select @.Totmin = Sum(Convert(Integer,(Left(@.hourworked,Charindex('. ',@.hourworked)-1) * 60)) +
Convert(Integer,(Right(@.hourworked,Len(@.hourworked ) - Charindex('. ',@.hourworked)))))
, @.Endtimebr = Sum(Convert(Integer,(Left(@.Endtime,Charindex(':',@. Endtime)-1) * 60)) +
Convert(Integer,(Right(@.Endtime,Len(@.Endtime) - Charindex(':',@.Endtime)))))
, @.Stime = Sum(Convert(Integer,(Left(@.Start,Charindex(':',@.St art)-1) * 60)) +
Convert(Integer,(Right(@.Start,Len(@.Start) - Charindex(':',@.Start)))))
*/
-- Values Passed as Parameter to Stored procedure Break_Split

Exec Break_Split @.Id,@.Res_id,@.DTime,@.Start,@.EndTime,@.Pauze,@.Hourwor ked,@.Status,@.Project,@.Naam,@.Type,@.Hryear,@.Totmin,@. Endtimebr,@.STime,@.Processed

select @.Id = min(ID) from inserted where id > @.id
end

-- -------------------
-- The deleted temp table can be joined to the Selnmuamid table
-- to deleted the unwanted records. If you need the ID from the
-- inserted table then join the inserted table with the
-- deleted table and then join the Selnmuamid table.
-- This is a good example of how you can ditch the row-at-a-time
-- mentality and start using set theory for processing records.
-- -------------------
delete
from Selnmuamid s
join deleted d on s.Res_id = d.Res_id
where [s.Datetime] = d.del_DTime
and s.Starttime >= d.del_starttime
and s.Endtime <= d.del_endtime

/*
Select @.del_DTime = [DateTime]
, @.del_Starttime = Starttime
, @.del_endTime = Endtime
, @.del_id = [id]
From Deleted
where Id = @.Id

Delete
From Selnmuamid
Where Res_id = @.Res_id
and [Datetime] = @.del_DTime
and Starttime >= @.del_starttime
and Endtime <= @.del_endtime
*/

------------------------------

How to input and output simultaneously in SQL 2005 Express?

I want to process data of an old VS6 project in VisualStudio2005.
First part is done. With SQL Management Studio I created the database file 'dbTest4' and attached it to SQL 2005 Express server (.\SQLEXPRESS).
Then I recoded the VS6_C++ project feeding its data into the database file using the ConnectionString:
"Provider='sqloledb';Data Source='.';Initial Catalog='dbTest4';Integrated Security='SSPI')"
Until here its working fine.

Assecond part I want to catch these data with VisualStudio2005 for processing there.
In VisualStudio 2005 I created a Windows Application as new project added the database file 'dbTest4' as data source, waited two minutes till VisualStudio2005 had created the DataGridTools bound to the tabbles of my database file and dropped a grid tool on to the surface of the new Form.
Great, within 5 minutes I had created a working database solution without writing a single line of code and it worked well. Starting the new build exe as standlolone it showed the data of my database file in a nice grid view.
But it worked as standolone only. When I tried to combine both parts one part was blockedalways producing error messages as ' ..error 32 (The process cannot access the file because it is being used by another process.) while attempting to open the file '...\dbTest4'.

Does anybody know
how to get this input/output combination running
or is ist impossible to reach my target with SQL 2005 Express?

After reading through Roger Wolters November 2005 article SQL Server 2005 Express Edition User Instances and some hours of testing I came to the conclusion:
When using Visual Studio not exclusively but simultaneously with other clients as SQLcmd, SQL Managent Studio or what else, as shown in my screenshot 325 you should consider to
set 'User Instance=False'
if you are struck by connection problems.
For details you are welcome to visit my sreenshot folder. By clicking the numbered screenshots, enlarging them and using your browsers back button you can follow a detailed installation and see why, where and how to set 'User Instance=False'.
Martin

how to Identify a rogue SQL process on a server

Hi,
Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
which is hammering the server.
Every now and then, our DB server get hammered (4* CPUs running at over 80%
usage), and I can see a list of process in management\process info. but it
shows the total cpu counter rather then the process that hammering the
server now.
Is there a way to identify which SQL process is hammering the server'
Windows 2000 Sp4 / SQL 2000 SP3a
Kind regards,
Jeremy Byrski
www.CentralR.comEXEC sp_who2
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
80%
> usage), and I can see a list of process in management\process info. but
it
> shows the total cpu counter rather then the process that hammering the
> server now.
>
> Is there a way to identify which SQL process is hammering the server'
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>|||you can use sp_who2 to see what activity is going on, or use enterprise
manager, under "Management" > "Current Activity"
Simon Worth
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
80%
> usage), and I can see a list of process in management\process info. but
it
> shows the total cpu counter rather then the process that hammering the
> server now.
>
> Is there a way to identify which SQL process is hammering the server'
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>|||You can also use PerfMon and sysprocesses to find the spid which is using up
a lot of CPU time. Find the highest Thread<sqlservr#<instance> )\%Processor
Time counter in Perfmon and match it to Thread(sqlservr#<instance> )\ID
Thread, which corresponds to the kpid column in sysprocesses. Use that
information to run dbcc inputbuffer(<spid> ). More information is available
at http://support.microsoft.com/defaul...b;en-us;117559.
Adrian
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
> 80% usage), and I can see a list of process in management\process info.
> but it shows the total cpu counter rather then the process that hammering
> the server now.
>
> Is there a way to identify which SQL process is hammering the server'
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>|||Hi Simon, and Aeron
I did try that, but it seems the display the Total CPU time since maybe the
SQL service has started.
I'm looking to identify a SQL Process ID that it taking a lot of CPU usage n
ow... in realtime, so that we can identify the query that is causing the hig
h CPU usage on the system in a snapshot of time.
Kind regards,
Jeremy Byrski
www.CentralR.com
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message news:Oa0LGBBJFHA.4060@.TK2
MSFTNGP14.phx.gbl...
> you can use sp_who2 to see what activity is going on, or use enterprise
> manager, under "Management" > "Current Activity"
>
> --
> Simon Worth
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> 80%
> it
>
>|||Hi Aidan.
I cant spot which performance object which sas the sql threads listed...
could you point me in the right direction...'
Thanks a million,
Kind regards,
Jeremy Byrski
www.CentralR.com
"Adrian Zajkeskovic" <nospam@.rogers.com> wrote in message
news:AqCdnRvv_Y7aw7PfRVn-iQ@.rogers.com...
> You can also use PerfMon and sysprocesses to find the spid which is using
> up a lot of CPU time. Find the highest
> Thread<sqlservr#<instance> )\%Processor Time counter in Perfmon and match
> it to Thread(sqlservr#<instance> )\ID Thread, which corresponds to the kpid
> column in sysprocesses. Use that information to run dbcc
> inputbuffer(<spid> ). More information is available at
> http://support.microsoft.com/defaul...b;en-us;117559.
> Adrian
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
>|||Wow Adrian - that' s pretty cool. Got any more tips like that one?
Steve.
"Adrian Zajkeskovic" <nospam@.rogers.com> wrote in message
news:AqCdnRvv_Y7aw7PfRVn-iQ@.rogers.com...
> You can also use PerfMon and sysprocesses to find the spid which is using
up
> a lot of CPU time. Find the highest Thread<sqlservr#<instance> )\%Processor
> Time counter in Perfmon and match it to Thread(sqlservr#<instance> )\ID
> Thread, which corresponds to the kpid column in sysprocesses. Use that
> information to run dbcc inputbuffer(<spid> ). More information is available
> at http://support.microsoft.com/defaul...b;en-us;117559.
> Adrian
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
hammering[vbcol=seagreen]
>

how to Identify a rogue SQL process on a server

Hi,
Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
which is hammering the server.
Every now and then, our DB server get hammered (4* CPUs running at over 80%
usage), and I can see a list of process in management\process info. but it
shows the total cpu counter rather then the process that hammering the
server now.
Is there a way to identify which SQL process is hammering the server'
Windows 2000 Sp4 / SQL 2000 SP3a
Kind regards,
Jeremy Byrski
www.CentralR.comEXEC sp_who2
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
80%
> usage), and I can see a list of process in management\process info. but
it
> shows the total cpu counter rather then the process that hammering the
> server now.
>
> Is there a way to identify which SQL process is hammering the server'
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>|||you can use sp_who2 to see what activity is going on, or use enterprise
manager, under "Management" > "Current Activity"
--
Simon Worth
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
80%
> usage), and I can see a list of process in management\process info. but
it
> shows the total cpu counter rather then the process that hammering the
> server now.
>
> Is there a way to identify which SQL process is hammering the server'
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>|||You can also use PerfMon and sysprocesses to find the spid which is using up
a lot of CPU time. Find the highest Thread<sqlservr#<instance>)\%Processor
Time counter in Perfmon and match it to Thread(sqlservr#<instance>)\ID
Thread, which corresponds to the kpid column in sysprocesses. Use that
information to run dbcc inputbuffer(<spid>). More information is available
at http://support.microsoft.com/default.aspx?scid=kb;en-us;117559.
Adrian
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
> 80% usage), and I can see a list of process in management\process info.
> but it shows the total cpu counter rather then the process that hammering
> the server now.
>
> Is there a way to identify which SQL process is hammering the server'
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0029_01C524D6.7E3E2390
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Simon, and Aeron

I did try that, but it seems the display the Total CPU time since maybe =the SQL service has started.

I'm looking to identify a SQL Process ID that it taking a lot of CPU =usage now... in realtime, so that we can identify the query that is =causing the high CPU usage on the system in a snapshot of time.

Kind regards,
Jeremy Byrski
www.CentralR.com
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message =news:Oa0LGBBJFHA.4060@.TK2MSFTNGP14.phx.gbl...
> you can use sp_who2 to see what activity is going on, or use =enterprise
> manager, under "Management" > "Current Activity"
> > -- > Simon Worth
> > > "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in =message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>>
>> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL =PID
>> which is hammering the server.
>>
>> Every now and then, our DB server get hammered (4* CPUs running at =over
> 80%
>> usage), and I can see a list of process in management\process info. =but
> it
>> shows the total cpu counter rather then the process that hammering =the
>> server now.
>>
>> Is there a way to identify which SQL process is hammering the =server'
>>
>> Windows 2000 Sp4 / SQL 2000 SP3a
>>
>> Kind regards,
>> Jeremy Byrski
>> www.CentralR.com
>>
>>
> >
--=_NextPart_000_0029_01C524D6.7E3E2390
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi Simon, and Aeron
I did try that, but it =seems the display the Total CPU time since maybe the SQL service has started.
I'm looking to identify a =SQL Process ID that it taking a lot of CPU usage now... in realtime, so that =we can identify the query that is causing the high CPU usage on the system in a =snapshot of time.
Kind =regards,Jeremy Byrskiwww.CentralR.com
"Simon Worth" wrote in message news:Oa0LGBBJFHA.4060@.TK2MSFTNGP14.phx.gbl...> you can use =sp_who2 to see what activity is going on, or use enterprise> manager, under ="Management" > "Current Activity"> > -- > Simon Worth> => > "news.microsoft.com" wrote in message> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...> =Hi,>>>> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID> which is hammering the server.>>>> Every now and =then, our DB server get hammered (4* CPUs running at over> 80%> =usage), and I can see a list of process in management\process info. =but> it> shows the total cpu counter rather then the process that hammering the> server now.>>>> Is there a way to =identify which SQL process is hammering the server'>>>> Windows 2000 =Sp4 / SQL 2000 SP3a>>>> Kind regards,>> Jeremy Byrski>> =www.CentralR.com>>>>>>= >

---

--=_NextPart_000_0029_01C524D6.7E3E2390--|||Hi Aidan.
I cant spot which performance object which sas the sql threads listed...
could you point me in the right direction...'
Thanks a million,
Kind regards,
Jeremy Byrski
www.CentralR.com
"Adrian Zajkeskovic" <nospam@.rogers.com> wrote in message
news:AqCdnRvv_Y7aw7PfRVn-iQ@.rogers.com...
> You can also use PerfMon and sysprocesses to find the spid which is using
> up a lot of CPU time. Find the highest
> Thread<sqlservr#<instance>)\%Processor Time counter in Perfmon and match
> it to Thread(sqlservr#<instance>)\ID Thread, which corresponds to the kpid
> column in sysprocesses. Use that information to run dbcc
> inputbuffer(<spid>). More information is available at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;117559.
> Adrian
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>>
>> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
>> which is hammering the server.
>>
>> Every now and then, our DB server get hammered (4* CPUs running at over
>> 80% usage), and I can see a list of process in management\process info.
>> but it shows the total cpu counter rather then the process that hammering
>> the server now.
>>
>> Is there a way to identify which SQL process is hammering the server'
>>
>> Windows 2000 Sp4 / SQL 2000 SP3a
>>
>> Kind regards,
>> Jeremy Byrski
>> www.CentralR.com
>>
>>
>|||Wow Adrian - that' s pretty cool. Got any more tips like that one?
Steve.
"Adrian Zajkeskovic" <nospam@.rogers.com> wrote in message
news:AqCdnRvv_Y7aw7PfRVn-iQ@.rogers.com...
> You can also use PerfMon and sysprocesses to find the spid which is using
up
> a lot of CPU time. Find the highest Thread<sqlservr#<instance>)\%Processor
> Time counter in Perfmon and match it to Thread(sqlservr#<instance>)\ID
> Thread, which corresponds to the kpid column in sysprocesses. Use that
> information to run dbcc inputbuffer(<spid>). More information is available
> at http://support.microsoft.com/default.aspx?scid=kb;en-us;117559.
> Adrian
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> >
> >
> > Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> > which is hammering the server.
> >
> >
> >
> > Every now and then, our DB server get hammered (4* CPUs running at over
> > 80% usage), and I can see a list of process in management\process info.
> > but it shows the total cpu counter rather then the process that
hammering
> > the server now.
> >
> >
> >
> > Is there a way to identify which SQL process is hammering the server'
> >
> >
> >
> > Windows 2000 Sp4 / SQL 2000 SP3a
> >
> >
> >
> > Kind regards,
> >
> > Jeremy Byrski
> >
> > www.CentralR.com
> >
> >
> >
> >
> >
>

how to Identify a rogue SQL process on a server

Hi,
Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
which is hammering the server.
Every now and then, our DB server get hammered (4* CPUs running at over 80%
usage), and I can see a list of process in management\process info. but it
shows the total cpu counter rather then the process that hammering the
server now.
Is there a way to identify which SQL process is hammering the server?
Windows 2000 Sp4 / SQL 2000 SP3a
Kind regards,
Jeremy Byrski
www.CentralR.com
EXEC sp_who2
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
80%
> usage), and I can see a list of process in management\process info. but
it
> shows the total cpu counter rather then the process that hammering the
> server now.
>
> Is there a way to identify which SQL process is hammering the server?
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>
|||you can use sp_who2 to see what activity is going on, or use enterprise
manager, under "Management" > "Current Activity"
Simon Worth
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
80%
> usage), and I can see a list of process in management\process info. but
it
> shows the total cpu counter rather then the process that hammering the
> server now.
>
> Is there a way to identify which SQL process is hammering the server?
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>
|||You can also use PerfMon and sysprocesses to find the spid which is using up
a lot of CPU time. Find the highest Thread<sqlservr#<instance>)\%Processor
Time counter in Perfmon and match it to Thread(sqlservr#<instance>)\ID
Thread, which corresponds to the kpid column in sysprocesses. Use that
information to run dbcc inputbuffer(<spid>). More information is available
at http://support.microsoft.com/default...;en-us;117559.
Adrian
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
> 80% usage), and I can see a list of process in management\process info.
> but it shows the total cpu counter rather then the process that hammering
> the server now.
>
> Is there a way to identify which SQL process is hammering the server?
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>
|||Hi Simon, and Aeron
I did try that, but it seems the display the Total CPU time since maybe the SQL service has started.
I'm looking to identify a SQL Process ID that it taking a lot of CPU usage now... in realtime, so that we can identify the query that is causing the high CPU usage on the system in a snapshot of time.
Kind regards,
Jeremy Byrski
www.CentralR.com
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message news:Oa0LGBBJFHA.4060@.TK2MSFTNGP14.phx.gbl...
> you can use sp_who2 to see what activity is going on, or use enterprise
> manager, under "Management" > "Current Activity"
> --
> Simon Worth
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> 80%
> it
>
|||Hi Aidan.
I cant spot which performance object which sas the sql threads listed...
could you point me in the right direction...?
Thanks a million,
Kind regards,
Jeremy Byrski
www.CentralR.com
"Adrian Zajkeskovic" <nospam@.rogers.com> wrote in message
news:AqCdnRvv_Y7aw7PfRVn-iQ@.rogers.com...
> You can also use PerfMon and sysprocesses to find the spid which is using
> up a lot of CPU time. Find the highest
> Thread<sqlservr#<instance>)\%Processor Time counter in Perfmon and match
> it to Thread(sqlservr#<instance>)\ID Thread, which corresponds to the kpid
> column in sysprocesses. Use that information to run dbcc
> inputbuffer(<spid>). More information is available at
> http://support.microsoft.com/default...;en-us;117559.
> Adrian
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
>
|||Wow Adrian - that' s pretty cool. Got any more tips like that one?
Steve.
"Adrian Zajkeskovic" <nospam@.rogers.com> wrote in message
news:AqCdnRvv_Y7aw7PfRVn-iQ@.rogers.com...
> You can also use PerfMon and sysprocesses to find the spid which is using
up[vbcol=seagreen]
> a lot of CPU time. Find the highest Thread<sqlservr#<instance>)\%Processor
> Time counter in Perfmon and match it to Thread(sqlservr#<instance>)\ID
> Thread, which corresponds to the kpid column in sysprocesses. Use that
> information to run dbcc inputbuffer(<spid>). More information is available
> at http://support.microsoft.com/default...;en-us;117559.
> Adrian
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
hammering
>