Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is there any way to flush out a stuck process in sql server


Is there any way to flush out a stuck process in sql server

Author
Message
DKG-967908
DKG-967908
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 239
Hi,

I am using SQL server 2005, a couple of times it happened that along with a scheduled backup job a SQL DMO process takes database backup it caused to stuck both the processes. To flush out these process i had to restart the sql services in past. When i kill them by spid it remains in rollback mode. Is there any workaround instead of restart SQL Service?

Secondly can i find from SQL end that from where and who has initiated the another backup using DMO?


Thanks,
ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 1865
Hi

Your question is not clear but are you are implying that 2 different processes are taking a backup of the database at the same time. If that's the case then you need to change that.

"Keep Trying"
DKG-967908
DKG-967908
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 239
Thanks for the response Chirag.

I don’t know where you are getting confuse, anyway let me explain again. I have a scheduled database backup job everyday at 22:30 it works fine but some time (usually once in a month) we found that its keep executing - reason being there is another process "SQL DMO" is taking backup for the same database. I have killed both the processes but they are in killed/rollback mode (not getting flush)
Now i have two questions:

1.) Is there any way to flush these processes without restarting SQL services?

2.) Can we find out from where the another process (SQL DMO) has been initiated and who started it etc.


Thanks,
Silverfox
Silverfox
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2740 Visits: 1161
DKG (9/3/2009)
Thanks for the response Chirag.

I don’t know where you are getting confuse, anyway let me explain again. I have a scheduled database backup job everyday at 22:30 it works fine but some time (usually once in a month) we found that its keep executing - reason being there is another process "SQL DMO" is taking backup for the same database. I have killed both the processes but they are in killed/rollback mode (not getting flush)
Now i have two questions:

1.) Is there any way to flush these processes without restarting SQL services?

2.) Can we find out from where the another process (SQL DMO) has been initiated and who started it etc.


Thanks,



Well the first thing is to try and identify what this dmo process is, have you checked the server and seen if there is any scheduled jobs that run on a monthly basis. when you see this dmo process, you should be able to see the hostname or the ip address from where it is being called from. check your sql jobs and see if there is anything that could call this.

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
DKG-967908
DKG-967908
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 239
I have already checed all the sql jobs and windows scheduled as well, there is no job scheduled .

about SQL DMO:

Most SQL Server administrative tasks are programmable by SQL-DMO, (DMO) is a set of programmable objects that come with SQL Server that make it easy to programmatically administer the databases instead of using the console we can do the following:



Scripting Objects
Backing up databases
Creating jobs
Altering tables and much more


As it is a COM object, you can use it in any COM friendly platform.

- the host name for DMO process is showing that database server name itself.
Silverfox
Silverfox
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2740 Visits: 1161
Do you have any other software installed on that server, either third party, or applications that are on the server. Maybe backup software of some sort.

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
DKG-967908
DKG-967908
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 239
Nothing as such installed, application team also have full access on this server, so i doubt if they have done something but i am not 100% sure. Thats why i need to find it out at my end.
Silverfox
Silverfox
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2740 Visits: 1161
I know it probably doesnt help too much. But the next time it occurs. It sounds like it is a local process. if you can get access when it happens. remote onto the server and look via task manager and see if you can work out what it is and kill it that way. once you have killed it then, it should free up the other backup job.

if you kill the spid.

you can then run kill [spid] with statusonly
and that should give you an estimated time for how long the rollback will take, if it comes back with 0, that can mean it is dependent on a non-sql possibly an o/s process to complete.

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
DKG-967908
DKG-967908
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 239
Unfortunately this is not a solution I am looking for, I wanted to get rid from it to find out who is doing it and from where. I can get it by enabling trace but I cant run trace for indefinite time?
ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 1865
As you mentioned you need to find out from the where is the SQL DMO backup command being executed. I can give you some pointers on how to find out.

1) Check out the logs if there is anything there that helps.

2) Check out the backupset system table. It contains a row for every backup taken. Since you are killing both the processes the database backup will not have been completed. So this may not help.

3) Your best bet is to run a trace. Use sp_trace_create for this and create only for the backup event and output the result to a table. If you have a rough idea as to when this occurs you can run this trace around that time.

Well that's what i can come up with now...

"Keep Trying"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search