September 2, 2009 at 11:25 am
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,
September 3, 2009 at 12:40 am
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"
September 3, 2009 at 1:50 am
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,
September 3, 2009 at 1:56 am
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.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 3, 2009 at 2:34 am
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.
September 3, 2009 at 3:11 am
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.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 3, 2009 at 4:43 am
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.
September 3, 2009 at 4:58 am
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.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 3, 2009 at 6:06 am
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?
September 3, 2009 at 6:30 am
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"
September 3, 2009 at 7:22 am
1) Checked logs it didn’t say anything 🙁
2) Already checked backupset as backup didn’t complete no info in backupset
3) I also thought about trace because this is the best option but no idea about time frame and I cann’t schedule trace for a month or so...
September 4, 2009 at 2:18 am
Well in that case .....are there many applications that connect to the databases. Any chance of checking their source code ? I know this is :crazy:
"Keep Trying"
September 4, 2009 at 3:06 am
have you checked the windows security log, for the time that it last ran, to try and isolate an account that might have been used to kick off that process. also ask your wintel team about server level tracing/auditing that they perhaps could put on temporary for a month or so.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 4, 2009 at 3:19 am
I also thought about this option but unfortunately logs for that date washed off.
September 4, 2009 at 3:29 am
DKG (9/4/2009)
I also thought about this option but unfortunately logs for that date washed off.
if there is a server backup taken daily, ask your infrastructure/wintel team for the eventlogs for that date, and see if they can restore it for you.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply