I trying to find a way to find what the SPID of a given job that is running. I am trying to create a script that will give me the SPID the JOB_ID, and JOB_NAME. The problem comes in that if I use sysprocesses I have to pull the JOB_ID from program_name in sysprocesses and convert it into something then join it to sysjobs. Have not been sucessfull in that conversion. Any Ideas
thanks for any & all help
sean
Here's what's going on. I have a 2 computers (x & y) running SQL2000. Ibacked up a copy a DB from x and restored it on y. I have a Stored procthat runs in under 2 seconds on both x & y when running it throughAnalyzer, but when I call this stored proc running it throuhg my C#winforms app (running on computer z) it takes over 3 minutes oncomputer x and under 10 seconds on y.This stored proc does have a select clause as part of the where clause,but again it works fine on y.I've check the indexes and that looks good and I just did a restore ofthe database so they should be identical. And I don't think it's aperformance issue because the rest of the app runs actaully a bitfaster on x.The plans do have differences. Specifically with a mention ofParallelism in the fast one.Here are the plans:X (slow):|--Sort(DISTINCT ORDER BY:([r].[GuestId] ASC, [g].[GuestNote] ASC,[Expr1005] ASC, [g].[Email] ASC, [g].[Phone1] ASC))|--Compute Scalar(DEFINE:([Expr1005]=[g].[LastName]+','+[g].[FirstName]))|--Filter(WHERE:(If ([Expr1003] IS NULL) then 0 else[Expr1003]>=2))|--Nested Loops(Left Outer Join, OUTERREFERENCES:([r].[GuestId]))|--Hash Match(Inner Join,HASH:([g].[GuestId])=([r].[GuestId]),RESIDUAL:([r].[GuestId]=[g].[GuestId]))| |--Clustered IndexScan(OBJECT:([Restaurant].[dbo].[Guest].[PK_Guest] AS [g]),WHERE:(len(isnull([g].[Email], ''))>6 AND charindex('@',isnull([g].[Email], ''), NULL)>1))| |--Clustered IndexSeek(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS[r]), SEEK:([r].[RestId]=1), WHERE:([r].[Date]<='Jan 1 2005 12:00AM'AND [r].[Date]>='Jan 1 2003 12:00AM') ORDERED FORWARD)|--Hash Match(Cache, HASH:([r].[GuestId]),RESIDUAL:([r].[GuestId]=[r].[GuestId]))|--ComputeScalar(DEFINE:([Expr1003]=Convert([Expr1011])))|--StreamAggregate(DEFINE:([Expr1011]=Count(*)))|--IndexSpool(SEEK:([r2].[GuestId]=[r].[GuestId]))|--Clustered IndexScan(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS[r2]))Y (Fast):|--Parallelism(Gather Streams)|--Sort(DISTINCT ORDER BY:([r].[GuestId] ASC, [g].[GuestNote]ASC, [Expr1005] ASC, [g].[Email] ASC, [g].[Phone1] ASC))|--Parallelism(Repartition Streams, PARTITIONCOLUMNS:([r].[GuestId], [g].[GuestNote], [Expr1005], [g].[Email],[g].[Phone1]))|--Compute Scalar(DEFINE:([Expr1005]=[g].[LastName]+','+[g].[FirstName]))|--Filter(WHERE:(If ([Expr1003] IS NULL) then 0else [Expr1003]>=2))|--ComputeScalar(DEFINE:([Expr1003]=Convert([Expr1013])))|--Hash Match Root(Right Outer Join,HASH:([r2].[GuestId])=([r].[GuestId]),RESIDUAL:([r2].[GuestId]=[r2].[GuestId]) AND([r2].[GuestId]=[r].[GuestId]) DEFINE:([Expr1013]=COUNT(*)))|--Parallelism(RepartitionStreams, PARTITION COLUMNS:([r2].[GuestId]))| |--Clustered IndexScan(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS[r2]))|--Hash Match Team(Inner Join,HASH:([g].[GuestId])=([r].[GuestId]),RESIDUAL:([r].[GuestId]=[g].[GuestId]))|--Bitmap(HASH:([g].[GuestId]), DEFINE:([Bitmap1014]))||--Parallelism(Repartition Streams, PARTITION COLUMNS:([g].[GuestId]))| |--Clustered IndexScan(OBJECT:([Restaurant].[dbo].[Guest].[PK_Guest] AS [g]),WHERE:(len(isnull([g].[Email], ''))>6 AND charindex('@',isnull([g].[Email], ''), NULL)>1))|--Parallelism(RepartitionStreams, PARTITION COLUMNS:([r].[GuestId]),WHERE:(PROBE([Bitmap1014])=TRUE))|--Clustered IndexSeek(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS[r]), SEEK:([r].[RestId]=1), WHERE:([r].[Date]<='Jan 1 2005 12:00AM'AND [r].[Date]>='Jan 1 2003 12:00AM') ORDERED FORWAny ideas of what I can check for?Thanks for any help.
View 1 Replies View RelatedHi, I have a users table in my SQL Server database. Now, I am looking to create a table (or multiple tables) to allow users to post their weekly events, meetings, activities, and accomplishments to the database. Each Monday morning, each user will enter their new schedule for that week and the previous week's entry will be archived in the database. My question is: what would make more sense? Should I create one big table that would have the following columns:
- Week Number (the current week number in the year)
- User ID
- Events
- Meetings
- Activities
- Accomplishments
And each user would have one row in the database per week.
OR should I create 4 separate tables named Events, Meetings, Activities, Accomplishments. Each of these tables would have the following columns: (for instance, the Events table would contain:)
- eventID (auto incremented number)
- userID
- weekNumber
- event_description
Each time a user adds a new event to their schedule, a new row in the Events table is created. Each time a user adds a new accomplishment, a new row in the Accomplishments table is created. etc., etc.
Which approach seems to make more sense and would be easier to maintain? Also, which one would conserve database space better and result in faster querying.
Thanks, Dave
I have an old backup file that I am pretty sure contains a table that I am looking for. Is there a way to verify what exactly a backup file contains without restoring it to a test server?
View 4 Replies View RelatedI have 3 tables, Items, Brands and Categories. Brands and Categories consists of a primary key as well as the Brand/Category name. Items has a bunch of columns as well as two foreign key fields, one for Brands and the other for Categories.I want a SELECT statement that would select each Brand(id and name) as well as the associated categories for each brand (i want this info the menu control) if this was a one-to-many relation, i wouldn't have had any problem... but since this is basically a many-to-many relation, i just can't figure it out
View 2 Replies View RelatedCould someome please tell me what is going on with the following stored procedure:
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
HI!
if i have SPID. then can i get the storedprocedure name ?
Regards,
Shabber Abbas Rizvi.
All,
Is there any way to see the SQL for any given spid in SQL server?
I can see "properties" under enterprise mgr - which shows you a snippet of the SQL - or "sp_execute" depending on what you use to get to the DBMS.
Is there a way to take a deeper look?
Thanks,
Isaac
Hi,
Can I have more than one value for variable @@SPID during a connection?
PS: sorry my english.
Thanks.
Hi All,
Sql Server 7.
I have a table cust_detail when ever there is an insert/update/delete in this table it is getting blocked and in turn its blocking other table inv_credit which is doing select statement.
Please suggest me what might be the reason.
Waiting for Reply
TIA
Adil
Hi to all.
I have a small doubt.
In my server some processs are running slow.
With out the use of profiler hw we can find the spid's that are running slow. is there any procedure for that.
I am seeing intermittent blocking problems. The strange part of it is,that the SPID listed in the Blk By column is the same as the SPID thatis being blocked.Has anyone seen this before, or have any idea what this may suggest?
View 1 Replies View RelatedHi,
Here i have a question..
Whenever i see my spid in LOCK/PROCESS ID
one command DBCC BUFFERINPUT(MY SPID) always running
and it is showing ''''tempdb.dbo.##lockinfo'''
MODE---- X
Is it pretty normal or something wrong..
i am not running any thing, just simply opened EM..
Pls any suggestions...
thank u
kavira
I know that spid 22 is blocking 2 other users. How do I tell who that user is? Is this just matching the same spid# in the process info in EM and if it doesn't give me an NT name it's probably because they're coming through the application and I can't figure it out?
Thanks
I'm going to try to explain my situation.
I have a spid that I'm not able to kill, this were a select statement from with in access 97 application using a DNS connection.
So even that I reboot the client pc and kill the spid it still shows as active with status RollBack.
We have similar problem before and the only way that it disappear were re - starting Sql.
(system Sql 7.0 with sp1 and Access 97)
Thanks.
How do COM+ transactions relate to SQL Server SPID?
I am trying to solve a deadlock issue. Using SQL trace, I find SPID 65 and 71 are trying to acquire resources that are locked by each other. But what I am confuse with is tasks performed by 65 are triggered by different transactions (same for 71). Should different COM+ transaction uses different SPID?
The entire COM+ application uses only one db login.
Please advice.
I can not stop a process using 'KILL spid'.What else should I try?
process has begun a transaction and does not commit.
I have a problem. I am trying to kill a spid that is blocking updates to a table.
The spid number is '-2'. I am using KILL with UOW and I am getting this error:
Server: Msg 6112, Level 16, State 1, Line 1
Distributed transaction with UOW {FCF8D536-27ED-11D6-9CF2-0002A56BDA54} is in prepared state.
Only Microsoft Distributed Transaction Coordinator can resolve this transaction. KILL command failed.
Users are connecting through MTS server. I am runnnig SQL2000 sp2 +hotfix, on NT4.0.
Has anyone encountered this problem before, and has a solution for it (besides rebooting the MTS and SQL Server)?
Thanks,
Peter
What is SPID -1? I see nothing listing it in any activity monitor, but it has just recently started blocking some processes.
Thanks,
jim craddock
can we just kill spid and drop it without doing any rollback ...?
Thanks,
ServerTeam
We are using SQL Server 2000 SP4.
Every week to few weeks we'll have a situation where the cpu will max out on our main db server.
This server is a hoss, dual quad core with 16 gigs of ram. All the system type stuff looks good.
I look into the spids though and I notice a few of them that are very high in cpu.
I believe that I've noticed over time that a certain SPID never releases and starts fresh. Could it be possible that a program is written in a way that it just keeps using it over and over with each new user and it nevers releases that CPU?
I know this is a probably a pretty dumb question and there's alot of details I have to leave out for being brief. Ultimately I have to find out why the CPU is hitting 100 percent and hanging there.
I know it's this program because when I stop the app pool for only that program then the CPU on the SQL box goes back down.
Any help would be appreciated...and feel free to ask more questions for more detail and I'll try to give it.
What is "SIGNAL HANDLER" (SPID 1)?
And what is SPIDs 1-6 ?
Exec Sp_who:
SPID Status DBName Command
----- ------------------------------ ------ ----------------
1 sleeping master SIGNAL HANDLER
2 BACKGROUND master LOCK MONITOR
3 BACKGROUND master LAZY WRITER
4 sleeping master LOG WRITER
5 sleeping master CHECKPOINT SLEEP
6 BACKGROUND master AWAITING COMMAND
Hi Everyone,
I noticed that in my SQL 2000 system, one SPID can have multiple ECID when I run sp_who.
Recently, users get timeout message. In the server performance monitor, I can see the SQLSERVER.exe takes a lot of CPU time when this happens.
Could anyone please explain this to me and provide me the solution for it?
Thanks in advance,
Jason
The following is an extract from sp_who2
SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO
----- ---------- ------- -------- ----- ------- ------- ------- ------
162 sleeping om18682 . -1 STIPROD SELECT 236 120
Has anybody experienced a process being blocked by SPID -1.
Hello -
I have a Foxpro app with a SQL Server 7 backend. The Idle Connect setting on VFP is set to indefinite. I'm having some connectivity issues where the SPID for the client seems to disappear from the active SQL processes on the server when the client sits idle for a while (10 minutes or so). On some workstations, resuming activity at the client results in the SPIDs reappearing
as if they were always there with no problem. On others, the connection is not reestablished and the client gives a 1526 (VFP ODBC) error.
Does anybody know why this might be? Are the SPID connections cached at both sides and if so, how?
Thanks.
Steve
Hey all,
I've got a third party app running atop my sql server instance. When auditing logons in profiler, I see that the user (sa) logs off, and then immediately logs back on. Interestingly, the app logs back on with this user again, and the instance gives it the same spid as it previously had.
Can anyone explain this behaviour, or point me to a place where I can investigate ?
Thanks,
-Kilka
Hey guys,
I'm wondering if it's possible to see which library which a spid is using? For example, is the spid coming from a jdbc connection, or an odbc connection?
Thanks,
-Kilka
Hi all,
Greetings,
I have a SPID 10 which running a query which is below is not getting killed
and this SPID is blocking other SPID's. Is there any way to kill it pls help me
"select A.*
from ARO A where A.InvoiceNumber in
(select B.InvoiceNumber from AR1 B
where B.Division='20'
/*UNION
Select C.SOApplytoInvoiceNumber from AR1 C
where C.Division='20'*/
UNION
Selec"
Waiting for reply
TIA
ADIL
Hi All,
I got problems with some procedure whic are blocking one of my databases. Biggest problem is I don't which those procedures are.
Last time it happened I ran sp_who2 active. I was able to identify two suspended blocked processes, one (UPDATE - SPID:58) blocked by the other one (SELECT - SPID: 68), the other one blocked by a runnable one (UNKNOWN TOKEN - SPID: 70).
For all of them I got SQLAgent - TSQL JobStep (Job [0xDA9A9D92A78C0E42A37EE96C7BEBB073] : Step [1]) so it's pretty obvious they are executed by SQL Agent Jobs.
I am trying now to identify the jobs from the keys 0xDA9A9D92A78C0E42A37EE96C7BEBB073 but no idea how to do it.
I was looking at the SPIDs but I discovered they can change, so no use in that.
Anyone of you guys can help?
Cheers and thanks in advance!
Giovanni
Hello 2 all,Maybe my question can be very stupid, but I'm a little confused.When I run sp_who on my database, I see one process (accessing remotelymy database from another database on another SQL server) many manytimes.Well, I assume that this is one process, because I cannot imagin thatthe data aquiring can be done by almost 1000 (thousand) openedconnections.This is what I have. Almost 1000 spids running (sleeping) with cmd =awaiting command.The best part is, that when I run sp_lock, I see, that the currentsession of the client, (lets say spid 53) is locking tempdb, withobjects id below 10 (system).As far as i know, and as far as I'm using this in my software, there isno necessity to open-close-open-close connection.Normaly, you can just open a connection to db, with assigned spid, andoperate within this just one. After the disconect ist done, the sessionshoould be removed.I think, there is something wrong in the way, the remote system isconnecting to my database, but i'm not sure is it normal what I'mexpieriencing or not.below, a piece of that what sp_who returned:SPIDECIDstatusloginnamehostnameblkdbnamecmd2660sleeping remote_user_namehost_ERP 0PRIMARY_PROCESS_DBAWAITING COMMAND2670sleeping remote_user_namehost_ERP 0PRIMARY_PROCESS_DBAWAITING COMMAND2680sleeping remote_user_namehost_ERP 0PRIMARY_PROCESS_DBAWAITING COMMAND2690sleeping remote_user_namehost_ERP 0PRIMARY_PROCESS_DBAWAITING COMMAND2700sleeping remote_user_namehost_ERP 0PRIMARY_PROCESS_DBAWAITING COMMAND2710sleeping remote_user_namehost_ERP 0PRIMARY_PROCESS_DBAWAITING COMMANDand these are locks from sp_lock 53 which is the current runningprocess:53700DB SGRANT53260TAB IXGRANT53210TAB IXGRANT53230TAB IXGRANT53220TAB IXGRANT532120TAB IXGRANT53290TAB IXGRANT532110TAB IXGRANT53232KEY(1902141b21c7) XGRANT53213KEY(e60041ad2c6f) XGRANT53232KEY(5b0233670fb9) XGRANT53232KEY(6e0212a7298c) XGRANT53232KEY(97013ece81c8) XGRANT53231KEY(e7006f987fb0) XGRANT53213KEY(ad00a33f46b9) XGRANT53210643560700TAB Sch-MGRANTany idee?ThanksMateusz
View 1 Replies View Related
I'm profiling some SQL work from ADO.NET and I see a large number of sp_reset_connection but what I don't understand is how the SPID remains constant if the connection truly has been reset. Obviously it doesn't reset the SPID so what *is* going on? NB This is a number of ADO calls inside a System.Transaction.
We're having an issue with packages being blocked by SPID -2 on SQL Servers with SP2 installed. We did not have this issue on SP1 SQL instances.
The packages are pretty basic and have a truncate followed by an insert data flow. We have our Transaction options set to:
IsolationLevel = Serializable
TransactionOption = Supported
Has anyone seen this before and have any advice for resolving this issue?
Hi
On one of my SQL servers (SQL 2000 SP4) i have a problem with spid blocked by itself (spid 54 (Blocked by 54)).
On the server it's running a verry simple UPDATE statement (from the Query Analizer)'. The query needs very long time to finalize (on SP3 it was very fast). I searched some forums regarding this issue and i found that maybe the SP4 can cause this problem.
Do you have any idea or advice?
Thanks