Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Is there any way to flush out a stuck process in sql server Expand / Collapse
Author
Message
Posted Wednesday, September 2, 2009 11:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 7:13 AM
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,
Post #781658
Posted Thursday, September 3, 2009 12:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
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"
Post #781950
Posted Thursday, September 3, 2009 1:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 7:13 AM
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,

Post #781968
Posted Thursday, September 3, 2009 1:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:56 AM
Points: 2,699, Visits: 1,144
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
Post #781973
Posted Thursday, September 3, 2009 2:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 7:13 AM
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.
Post #781993
Posted Thursday, September 3, 2009 3:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:56 AM
Points: 2,699, Visits: 1,144
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
Post #782015
Posted Thursday, September 3, 2009 4:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 7:13 AM
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.
Post #782063
Posted Thursday, September 3, 2009 4:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:56 AM
Points: 2,699, Visits: 1,144
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
Post #782068
Posted Thursday, September 3, 2009 6:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 7:13 AM
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?
Post #782092
Posted Thursday, September 3, 2009 6:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
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"
Post #782107
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse