Backup jobs hang :((

Hi,
propably asked and answered in the past, but cannot find anything on this
question.
Problem :
SQL70 latest SP , DB Maintenance plan backups hung
the jobagent says the first step is running and still nothing happens.
I have recreated the jobs tis helps for a day or two. Then the s--t hits the
fan again. Backup jobs hang
Jobs are running around 0100 am and there is NO activity in the DBs at that
time so it shouldnt be a blocking problem :(
Any ideas , anybody. Help would be appreciated very much.
--
IT-Manager
Mandatum Stockbrokers Ltd.
Remove _POISTA_ from address to replyWhat does sp_who, sp_who2, Current Activity etc say. Is the command (BACKUP, I take it) executing?
Etc.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"MaSa" <matti.saukkonen@._ POISTA_ mandatum.fi> wrote in message
news:OQLxaMulDHA.1096@.TK2MSFTNGP11.phx.gbl...
> Hi,
> propably asked and answered in the past, but cannot find anything on this
> question.
> Problem :
> SQL70 latest SP , DB Maintenance plan backups hung
> the jobagent says the first step is running and still nothing happens.
> I have recreated the jobs tis helps for a day or two. Then the s--t hits the
> fan again. Backup jobs hang
> Jobs are running around 0100 am and there is NO activity in the DBs at that
> time so it shouldnt be a blocking problem :(
> Any ideas , anybody. Help would be appreciated very much.
>
> --
> IT-Manager
> Mandatum Stockbrokers Ltd.
> Remove _POISTA_ from address to reply
>|||Hi Tibor, and thanks for the reply
Heres some additional info
Backup command runs without a glitch when executed from EM or from SQA.
Backups can be sent without probs to tape, disk, remote share. WOrks like a
dream when done manually. EVEN during the day when there is LOT of activity
in the DB.
The DB main jobs are run at night.
EM->Management->Job Agent
"DB backup job for DB main... status:Executing Job Step '1 (step 1)'
Current activity, shows
17 RUNNABLE sa MDTSQL05 .
master EXECUTE 0 0 10/15 02:00:00 SQLAgent - TSQL JobStep (Job 0 17
18 RUNNABLE sa MDTSQL05 .
master EXECUTE 0 0 10/12 00:00:00 SQLAgent - TSQL JobStep (Job 0 18
19 RUNNABLE sa MDTSQL05 .
master EXECUTE 0 0 10/19 00:00:00 SQLAgent - TSQL JobStep (Job 0 19
for the job in question
(properties of session )
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
FAC0296F-DF1C-11D7-9A18-0002A528BED5 -WriteHistory -VrfyBackup -BkUpOnlyIfC
lean -CkDB -BkUpMedia DISK -BkUpDB "I:\Databak" -DelBkUps
1DAYS -CrBkSubDir -BkExt "BAK"'
Nothing is happening. status runnable
The backup jobs is scheduled at 0200
I have two jobs (or plans)
The other one does this Backup stuff and the other backs up DB-logs every 10
minutes
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
FAC02972-DF1C-11D7-9A18-0002A528BED5 -WriteHistory -VrfyBackup -BkUpMedia
DISK -BkUpLog "I:\LogBak" -DelBkUps 2DAYS -CrBkSubDir -BkExt "TRN"'
This schedule is run between 0700 - 2300 hours,
So basically at the moment " I am up the ... creek without a paddle"
Restarting the server has helped or recreating the jobs but only for some
days.
Booting the server at 1 days interval is not an option
HELP :-O :))
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ugM19ZulDHA.424@.TK2MSFTNGP10.phx.gbl...
> What does sp_who, sp_who2, Current Activity etc say. Is the command
(BACKUP, I take it) executing?
> Etc.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "MaSa" <matti.saukkonen@._ POISTA_ mandatum.fi> wrote in message
> news:OQLxaMulDHA.1096@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > propably asked and answered in the past, but cannot find anything on
this
> > question.
> >
> > Problem :
> > SQL70 latest SP , DB Maintenance plan backups hung
> > the jobagent says the first step is running and still nothing happens.
> >
> > I have recreated the jobs tis helps for a day or two. Then the s--t hits
the
> > fan again. Backup jobs hang
> > Jobs are running around 0100 am and there is NO activity in the DBs at
that
> > time so it shouldnt be a blocking problem :(
> >
> > Any ideas , anybody. Help would be appreciated very much.
> >
> >
> > --
> > IT-Manager
> > Mandatum Stockbrokers Ltd.
> >
> > Remove _POISTA_ from address to reply
> >
> >
>|||Masa
Are you sure that during nightly backup there are not some others activites
on the server ?
"MaSa" <matti.saukkonen@._ POISTA_ mandatum.fi> wrote in message
news:OEqSa4ulDHA.1800@.TK2MSFTNGP10.phx.gbl...
> Hi Tibor, and thanks for the reply
> Heres some additional info
> Backup command runs without a glitch when executed from EM or from SQA.
> Backups can be sent without probs to tape, disk, remote share. WOrks like
a
> dream when done manually. EVEN during the day when there is LOT of
activity
> in the DB.
> The DB main jobs are run at night.
> EM->Management->Job Agent
> "DB backup job for DB main... status:Executing Job Step '1 (step 1)'
> Current activity, shows
> 17 RUNNABLE sa MDTSQL05 .
> master EXECUTE 0 0 10/15 02:00:00 SQLAgent - TSQL JobStep (Job 0 17
> 18 RUNNABLE sa MDTSQL05 .
> master EXECUTE 0 0 10/12 00:00:00 SQLAgent - TSQL JobStep (Job 0 18
> 19 RUNNABLE sa MDTSQL05 .
> master EXECUTE 0 0 10/19 00:00:00 SQLAgent - TSQL JobStep (Job 0 19
>
> for the job in question
> (properties of session )
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>
FAC0296F-DF1C-11D7-9A18-0002A528BED5 -WriteHistory -VrfyBackup -BkUpOnlyIfC
> lean -CkDB -BkUpMedia DISK -BkUpDB "I:\Databak" -DelBkUps
> 1DAYS -CrBkSubDir -BkExt "BAK"'
> Nothing is happening. status runnable
> The backup jobs is scheduled at 0200
>
> I have two jobs (or plans)
> The other one does this Backup stuff and the other backs up DB-logs every
10
> minutes
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> FAC02972-DF1C-11D7-9A18-0002A528BED5 -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "I:\LogBak" -DelBkUps 2DAYS -CrBkSubDir -BkExt "TRN"'
> This schedule is run between 0700 - 2300 hours,
>
> So basically at the moment " I am up the ... creek without a paddle"
> Restarting the server has helped or recreating the jobs but only for some
> days.
> Booting the server at 1 days interval is not an option
> HELP :-O :))
>
>
>
>
>
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:ugM19ZulDHA.424@.TK2MSFTNGP10.phx.gbl...
> > What does sp_who, sp_who2, Current Activity etc say. Is the command
> (BACKUP, I take it) executing?
> > Etc.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "MaSa" <matti.saukkonen@._ POISTA_ mandatum.fi> wrote in message
> > news:OQLxaMulDHA.1096@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > propably asked and answered in the past, but cannot find anything on
> this
> > > question.
> > >
> > > Problem :
> > > SQL70 latest SP , DB Maintenance plan backups hung
> > > the jobagent says the first step is running and still nothing happens.
> > >
> > > I have recreated the jobs tis helps for a day or two. Then the s--t
hits
> the
> > > fan again. Backup jobs hang
> > > Jobs are running around 0100 am and there is NO activity in the DBs at
> that
> > > time so it shouldnt be a blocking problem :(
> > >
> > > Any ideas , anybody. Help would be appreciated very much.
> > >
> > >
> > > --
> > > IT-Manager
> > > Mandatum Stockbrokers Ltd.
> > >
> > > Remove _POISTA_ from address to reply
> > >
> > >
> >
> >
>|||You didn't say which job of the two is failing, or whether both a re failing. Do a job ever succeed?
If so, I'd run a profiler trace and catch for which statement it hangs. Or, rather, run a trace
anyhow, you can compare to a similar job on another server.
What do you mean with "hang"? What does Agent say about the job status? Can you terminate with
Agent? Etc.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"MaSa" <matti.saukkonen@._ POISTA_ mandatum.fi> wrote in message
news:OEqSa4ulDHA.1800@.TK2MSFTNGP10.phx.gbl...
> Hi Tibor, and thanks for the reply
> Heres some additional info
> Backup command runs without a glitch when executed from EM or from SQA.
> Backups can be sent without probs to tape, disk, remote share. WOrks like a
> dream when done manually. EVEN during the day when there is LOT of activity
> in the DB.
> The DB main jobs are run at night.
> EM->Management->Job Agent
> "DB backup job for DB main... status:Executing Job Step '1 (step 1)'
> Current activity, shows
> 17 RUNNABLE sa MDTSQL05 .
> master EXECUTE 0 0 10/15 02:00:00 SQLAgent - TSQL JobStep (Job 0 17
> 18 RUNNABLE sa MDTSQL05 .
> master EXECUTE 0 0 10/12 00:00:00 SQLAgent - TSQL JobStep (Job 0 18
> 19 RUNNABLE sa MDTSQL05 .
> master EXECUTE 0 0 10/19 00:00:00 SQLAgent - TSQL JobStep (Job 0 19
>
> for the job in question
> (properties of session )
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> FAC0296F-DF1C-11D7-9A18-0002A528BED5 -WriteHistory -VrfyBackup -BkUpOnlyIfC
> lean -CkDB -BkUpMedia DISK -BkUpDB "I:\Databak" -DelBkUps
> 1DAYS -CrBkSubDir -BkExt "BAK"'
> Nothing is happening. status runnable
> The backup jobs is scheduled at 0200
>
> I have two jobs (or plans)
> The other one does this Backup stuff and the other backs up DB-logs every 10
> minutes
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> FAC02972-DF1C-11D7-9A18-0002A528BED5 -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "I:\LogBak" -DelBkUps 2DAYS -CrBkSubDir -BkExt "TRN"'
> This schedule is run between 0700 - 2300 hours,
>
> So basically at the moment " I am up the ... creek without a paddle"
> Restarting the server has helped or recreating the jobs but only for some
> days.
> Booting the server at 1 days interval is not an option
> HELP :-O :))
>
>
>
>
>
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:ugM19ZulDHA.424@.TK2MSFTNGP10.phx.gbl...
> > What does sp_who, sp_who2, Current Activity etc say. Is the command
> (BACKUP, I take it) executing?
> > Etc.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "MaSa" <matti.saukkonen@._ POISTA_ mandatum.fi> wrote in message
> > news:OQLxaMulDHA.1096@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > propably asked and answered in the past, but cannot find anything on
> this
> > > question.
> > >
> > > Problem :
> > > SQL70 latest SP , DB Maintenance plan backups hung
> > > the jobagent says the first step is running and still nothing happens.
> > >
> > > I have recreated the jobs tis helps for a day or two. Then the s--t hits
> the
> > > fan again. Backup jobs hang
> > > Jobs are running around 0100 am and there is NO activity in the DBs at
> that
> > > time so it shouldnt be a blocking problem :(
> > >
> > > Any ideas , anybody. Help would be appreciated very much.
> > >
> > >
> > > --
> > > IT-Manager
> > > Mandatum Stockbrokers Ltd.
> > >
> > > Remove _POISTA_ from address to reply
> > >
> > >
> >
> >
>|||In this context hang means "the job never finishes" :)
Both jobs fail (or actually are never completed). If I stop the agent the
job logs claim the job has finished properly and everything is ok . This is
not the truth. (trust no one ) LOL :)
Agent can be restarted without probs, no reboot needed to stop/start cycle.
the Jobs succeed couple of time if I recreate them from scratch. The
failures are happening only for this on database, the rest of DBs do get
backuped up without a glitch. Weird !
I killed the jobs today about an hour ago and now the job for trans-logs is
working although I am not at all sure when its gonna die again.
The Db backup will execute this night at 0200 hours. I just wish it is going
to execute properly.
Of course I could try to create a schedule on the server which then would
stop/start the Agent from OS level. Might work but would be a definite "hack
patch" . it would NOT fix the original disease just cure the symptoms LOL|||Hi Uri
There are two "listener"-programs on the server running.
1 is listening to a IBM.-MQ queue and reads message from the queue and
inserts stuff into db . The message are sent by our web site and the users
are retail clients. The logs show me there are NO messages coming this late
at night.
The other on polls the db and send execution information on the previously
mentioned messages.
No activity during the night.
And these progs have been running on this server for ages and this backup
prob has only been around for couple of months
rgrds Matti
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e$wWFAvlDHA.1408@.TK2MSFTNGP11.phx.gbl...
> Masa
> Are you sure that during nightly backup there are not some others
activites
> on the server ?
> "MaSa" <matti.saukkonen@._ POISTA_ mandatum.fi> wrote in message
> news:OEqSa4ulDHA.1800@.TK2MSFTNGP10.phx.gbl...
> > Hi Tibor, and thanks for the reply
> >
> > Heres some additional info
> >
> > Backup command runs without a glitch when executed from EM or from SQA.
> > Backups can be sent without probs to tape, disk, remote share. WOrks
like
> a
> > dream when done manually. EVEN during the day when there is LOT of
> activity
> > in the DB.
> >
> > The DB main jobs are run at night.
> >
> > EM->Management->Job Agent
> > "DB backup job for DB main... status:Executing Job Step '1 (step 1)'
> >
> > Current activity, shows
> > 17 RUNNABLE sa MDTSQL05
.
> > master EXECUTE 0 0 10/15 02:00:00 SQLAgent - TSQL JobStep (Job 0 17
> > 18 RUNNABLE sa MDTSQL05
.
> > master EXECUTE 0 0 10/12 00:00:00 SQLAgent - TSQL JobStep (Job 0 18
> > 19 RUNNABLE sa MDTSQL05
.
> > master EXECUTE 0 0 10/19 00:00:00 SQLAgent - TSQL JobStep (Job 0 19
> >
> >
> >
> > for the job in question
> >
> > (properties of session )
> >
> > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >
>
FAC0296F-DF1C-11D7-9A18-0002A528BED5 -WriteHistory -VrfyBackup -BkUpOnlyIfC
> > lean -CkDB -BkUpMedia DISK -BkUpDB "I:\Databak" -DelBkUps
> > 1DAYS -CrBkSubDir -BkExt "BAK"'
> >
> > Nothing is happening. status runnable
> > The backup jobs is scheduled at 0200
> >
> >
> > I have two jobs (or plans)
> >
> > The other one does this Backup stuff and the other backs up DB-logs
every
> 10
> > minutes
> >
> > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >
FAC02972-DF1C-11D7-9A18-0002A528BED5 -WriteHistory -VrfyBackup -BkUpMedia
> > DISK -BkUpLog "I:\LogBak" -DelBkUps 2DAYS -CrBkSubDir -BkExt "TRN"'
> >
> > This schedule is run between 0700 - 2300 hours,
> >
> >
> > So basically at the moment " I am up the ... creek without a paddle"
> > Restarting the server has helped or recreating the jobs but only for
some
> > days.
> > Booting the server at 1 days interval is not an option
> >
> > HELP :-O :))
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:ugM19ZulDHA.424@.TK2MSFTNGP10.phx.gbl...
> > > What does sp_who, sp_who2, Current Activity etc say. Is the command
> > (BACKUP, I take it) executing?
> > > Etc.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "MaSa" <matti.saukkonen@._ POISTA_ mandatum.fi> wrote in message
> > > news:OQLxaMulDHA.1096@.TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > >
> > > > propably asked and answered in the past, but cannot find anything on
> > this
> > > > question.
> > > >
> > > > Problem :
> > > > SQL70 latest SP , DB Maintenance plan backups hung
> > > > the jobagent says the first step is running and still nothing
happens.
> > > >
> > > > I have recreated the jobs tis helps for a day or two. Then the s--t
> hits
> > the
> > > > fan again. Backup jobs hang
> > > > Jobs are running around 0100 am and there is NO activity in the DBs
at
> > that
> > > > time so it shouldnt be a blocking problem :(
> > > >
> > > > Any ideas , anybody. Help would be appreciated very much.
> > > >
> > > >
> > > > --
> > > > IT-Manager
> > > > Mandatum Stockbrokers Ltd.
> > > >
> > > > Remove _POISTA_ from address to reply
> > > >
> > > >
> > >
> > >
> >
> >
>|||> Both jobs fail (or actually are never completed).
That is a very big difference!!! I take it you mean never completes. :-)
> Of course I could try to create a schedule on the server which then would
> stop/start the Agent from OS level.
Sounds like too much of a hack to me, you really shouldn't need to do this. I again suggest you run
a profiler trace (include both starting as well as completed command events) to see at which command
it hangs. Also the current activity in EM can show you the command (as well as DBCC INPUTBUFFER).
This would need you to be at place when this happen, of course.
Btw, how long have you waited for the job to complete? Long enough?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"MaSa" <matti.saukkonen@._ POISTA_ mandatum.fi> wrote in message
news:uazasLvlDHA.1800@.TK2MSFTNGP10.phx.gbl...
> In this context hang means "the job never finishes" :)
> Both jobs fail (or actually are never completed). If I stop the agent the
> job logs claim the job has finished properly and everything is ok . This is
> not the truth. (trust no one ) LOL :)
> Agent can be restarted without probs, no reboot needed to stop/start cycle.
> the Jobs succeed couple of time if I recreate them from scratch. The
> failures are happening only for this on database, the rest of DBs do get
> backuped up without a glitch. Weird !
> I killed the jobs today about an hour ago and now the job for trans-logs is
> working although I am not at all sure when its gonna die again.
> The Db backup will execute this night at 0200 hours. I just wish it is going
> to execute properly.
> Of course I could try to create a schedule on the server which then would
> stop/start the Agent from OS level. Might work but would be a definite "hack
> patch" . it would NOT fix the original disease just cure the symptoms LOL
>
>
>|||Long enough, I think a 12 hour period is long enough :D interactively driven
backups take about 1,5 hours, same amount of time goes for the execution
when the jobs work ie, very seldom LOL
I have to think about this crap and find a solution. Allthough we are
migrating our DBs to a new server and OS version in 2 weeks so this quirk
might just vanish at that point of time
I thank you for your assistance
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:#d6F19vlDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > Both jobs fail (or actually are never completed).
> That is a very big difference!!! I take it you mean never completes. :-)
>
> > Of course I could try to create a schedule on the server which then
would
> > stop/start the Agent from OS level.
> Sounds like too much of a hack to me, you really shouldn't need to do
this. I again suggest you run
> a profiler trace (include both starting as well as completed command
events) to see at which command
> it hangs. Also the current activity in EM can show you the command (as
well as DBCC INPUTBUFFER).
> This would need you to be at place when this happen, of course.
> Btw, how long have you waited for the job to complete? Long enough?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "MaSa" <matti.saukkonen@._ POISTA_ mandatum.fi> wrote in message
> news:uazasLvlDHA.1800@.TK2MSFTNGP10.phx.gbl...
> > In this context hang means "the job never finishes" :)
> >
> > Both jobs fail (or actually are never completed). If I stop the agent
the
> > job logs claim the job has finished properly and everything is ok . This
is
> > not the truth. (trust no one ) LOL :)
> >
> > Agent can be restarted without probs, no reboot needed to stop/start
cycle.
> >
> > the Jobs succeed couple of time if I recreate them from scratch. The
> > failures are happening only for this on database, the rest of DBs do get
> > backuped up without a glitch. Weird !
> >
> > I killed the jobs today about an hour ago and now the job for trans-logs
is
> > working although I am not at all sure when its gonna die again.
> >
> > The Db backup will execute this night at 0200 hours. I just wish it is
going
> > to execute properly.
> >
> > Of course I could try to create a schedule on the server which then
would
> > stop/start the Agent from OS level. Might work but would be a definite
"hack
> > patch" . it would NOT fix the original disease just cure the symptoms
LOL
> >
> >
> >
> >
> >
>|||Reboot the server..
From http://www.developmentnow.com/g/118_2003_10_0_0_464322/Backup-jobs-hang-.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com