<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Is there any way to flush out a stuck process...
15 posts, Page 1 of 2
1
2
»»
Is there any way to flush out a stuck process in sql server
Rate Topic
Display Mode
Topic Options
Author
Message
DKG-967908
DKG-967908
Posted Wednesday, September 02, 2009 11:25 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, December 24, 2012 6:33 AM
Points: 49,
Visits: 187
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
ChiragNS
ChiragNS
Posted Thursday, September 03, 2009 12:40 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365,
Visits: 1,825
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
DKG-967908
DKG-967908
Posted Thursday, September 03, 2009 1:50 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, December 24, 2012 6:33 AM
Points: 49,
Visits: 187
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
Silverfox
Silverfox
Posted Thursday, September 03, 2009 1:56 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 6:50 AM
Points: 2,719,
Visits: 1,065
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
DKG-967908
DKG-967908
Posted Thursday, September 03, 2009 2:34 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, December 24, 2012 6:33 AM
Points: 49,
Visits: 187
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
Silverfox
Silverfox
Posted Thursday, September 03, 2009 3:11 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 6:50 AM
Points: 2,719,
Visits: 1,065
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
DKG-967908
DKG-967908
Posted Thursday, September 03, 2009 4:43 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, December 24, 2012 6:33 AM
Points: 49,
Visits: 187
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
Silverfox
Silverfox
Posted Thursday, September 03, 2009 4:58 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 6:50 AM
Points: 2,719,
Visits: 1,065
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
DKG-967908
DKG-967908
Posted Thursday, September 03, 2009 6:06 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, December 24, 2012 6:33 AM
Points: 49,
Visits: 187
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
ChiragNS
ChiragNS
Posted Thursday, September 03, 2009 6:30 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365,
Visits: 1,825
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 »
15 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
