We have got backups set regularly each day. A full backup early in the
morning then a partial one every hour. These run time and the jobs for them
are in the SQL-SERVER Agent. After a few weeks the partial backups start to
fail. The full backups are fine. There is plenty of space an no errors in
the logs. When looking at the partial backups unser SQL-Server agent and
looking at the the schedule the box matked Enable is unchecked. We reset
the backup by clicking this box. It runs fine for a few wekes when it fials
again and the box has become unchecked? Has anyone got any ideas why this
might happen?
What version and service pack ? Might be related to
FIX: SQLAgent Job with Recurring Schedule is Disabled
Upon Completion of the Schedule for the Day
http://support.microsoft.com/default...;en-us;Q295378
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Scott Bailey" <scott.bailey@.migranthelpline.org> wrote in message
news:cp1jgv$2r3$1$8302bc10@.news.demon.co.uk...
> We have got backups set regularly each day. A full backup early in the
> morning then a partial one every hour. These run time and the jobs for
> them
> are in the SQL-SERVER Agent. After a few weeks the partial backups start
> to
> fail. The full backups are fine. There is plenty of space an no errors in
> the logs. When looking at the partial backups unser SQL-Server agent and
> looking at the the schedule the box matked Enable is unchecked. We reset
> the backup by clicking this box. It runs fine for a few wekes when it
> fials
> again and the box has become unchecked? Has anyone got any ideas why this
> might happen?
>
Showing posts with label jobs. Show all posts
Showing posts with label jobs. Show all posts
Thursday, March 22, 2012
Thursday, March 8, 2012
Backup pefromed by SQLSERVERAGENT fails
I just created five new BU devices, then a job for each
and executed the jobs to create the intial backups on a
new database.
Four of the jobs worked fine. In Event Viewer, the Source
= MSSQLSERVER and the USER = SQLEXEC.
One job failed. Source = SQLSERVERAGENT and USER = N/A
Where did I go wrong? Why does the failing job use a
different USER and Source?
TIA MikeYou need to give us more info. Without the error message, it is like saying: "My computer doesn't
work. What is the problem?".
Check the job history, check "show step details", and go to the relevant step. See what the error
message is. Also, see the Agent errorlog.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike Hoyt" <mhoyt@.affiliatedhealth.org> wrote in message
news:0ec301c39d9e$db5dff70$a001280a@.phx.gbl...
> I just created five new BU devices, then a job for each
> and executed the jobs to create the intial backups on a
> new database.
> Four of the jobs worked fine. In Event Viewer, the Source
> = MSSQLSERVER and the USER = SQLEXEC.
> One job failed. Source = SQLSERVERAGENT and USER = N/A
> Where did I go wrong? Why does the failing job use a
> different USER and Source?
> TIA Mike|||The Job is failing at Step 1, which was invoked by
Schedule 1. Step 1 is the only step, and it is the basic
backup syntax. The SQLAgent error log is blank for
failures and warnings, information is the usual start up
stuff.
I know the agent is running, th eother jobs run and I get
email alerts of their completion. I get the email alert
of the failure of this job.
What I'm hoping osmebody can tell me is why this job is
run by Source = SQLSERVERAGENT and USER = N/A and the
other, sucessfull jobs, are run by MSSQLSERVER and the
USER = SQLEXEC|||I haven't played with this to see if this is the case but do
the jobs have different owners?
-Sue
On Wed, 29 Oct 2003 08:55:41 -0800,
<anonymous@.discussions.microsoft.com> wrote:
>The Job is failing at Step 1, which was invoked by
>Schedule 1. Step 1 is the only step, and it is the basic
>backup syntax. The SQLAgent error log is blank for
>failures and warnings, information is the usual start up
>stuff.
>I know the agent is running, th eother jobs run and I get
>email alerts of their completion. I get the email alert
>of the failure of this job.
>What I'm hoping osmebody can tell me is why this job is
>run by Source = SQLSERVERAGENT and USER = N/A and the
>other, sucessfull jobs, are run by MSSQLSERVER and the
>USER = SQLEXEC|||Have you checked job step history? Right click the job, select history, and
check job step details.
--
Michiel Wories, SQL Server PM
This posting is provided "AS IS" with no warranties, and confers no rights.
--
<anonymous@.discussions.microsoft.com> wrote in message
news:0e5501c39e3d$7c4f95d0$a501280a@.phx.gbl...
> The Job is failing at Step 1, which was invoked by
> Schedule 1. Step 1 is the only step, and it is the basic
> backup syntax. The SQLAgent error log is blank for
> failures and warnings, information is the usual start up
> stuff.
> I know the agent is running, th eother jobs run and I get
> email alerts of their completion. I get the email alert
> of the failure of this job.
> What I'm hoping osmebody can tell me is why this job is
> run by Source = SQLSERVERAGENT and USER = N/A and the
> other, sucessfull jobs, are run by MSSQLSERVER and the
> USER = SQLEXEC
and executed the jobs to create the intial backups on a
new database.
Four of the jobs worked fine. In Event Viewer, the Source
= MSSQLSERVER and the USER = SQLEXEC.
One job failed. Source = SQLSERVERAGENT and USER = N/A
Where did I go wrong? Why does the failing job use a
different USER and Source?
TIA MikeYou need to give us more info. Without the error message, it is like saying: "My computer doesn't
work. What is the problem?".
Check the job history, check "show step details", and go to the relevant step. See what the error
message is. Also, see the Agent errorlog.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike Hoyt" <mhoyt@.affiliatedhealth.org> wrote in message
news:0ec301c39d9e$db5dff70$a001280a@.phx.gbl...
> I just created five new BU devices, then a job for each
> and executed the jobs to create the intial backups on a
> new database.
> Four of the jobs worked fine. In Event Viewer, the Source
> = MSSQLSERVER and the USER = SQLEXEC.
> One job failed. Source = SQLSERVERAGENT and USER = N/A
> Where did I go wrong? Why does the failing job use a
> different USER and Source?
> TIA Mike|||The Job is failing at Step 1, which was invoked by
Schedule 1. Step 1 is the only step, and it is the basic
backup syntax. The SQLAgent error log is blank for
failures and warnings, information is the usual start up
stuff.
I know the agent is running, th eother jobs run and I get
email alerts of their completion. I get the email alert
of the failure of this job.
What I'm hoping osmebody can tell me is why this job is
run by Source = SQLSERVERAGENT and USER = N/A and the
other, sucessfull jobs, are run by MSSQLSERVER and the
USER = SQLEXEC|||I haven't played with this to see if this is the case but do
the jobs have different owners?
-Sue
On Wed, 29 Oct 2003 08:55:41 -0800,
<anonymous@.discussions.microsoft.com> wrote:
>The Job is failing at Step 1, which was invoked by
>Schedule 1. Step 1 is the only step, and it is the basic
>backup syntax. The SQLAgent error log is blank for
>failures and warnings, information is the usual start up
>stuff.
>I know the agent is running, th eother jobs run and I get
>email alerts of their completion. I get the email alert
>of the failure of this job.
>What I'm hoping osmebody can tell me is why this job is
>run by Source = SQLSERVERAGENT and USER = N/A and the
>other, sucessfull jobs, are run by MSSQLSERVER and the
>USER = SQLEXEC|||Have you checked job step history? Right click the job, select history, and
check job step details.
--
Michiel Wories, SQL Server PM
This posting is provided "AS IS" with no warranties, and confers no rights.
--
<anonymous@.discussions.microsoft.com> wrote in message
news:0e5501c39e3d$7c4f95d0$a501280a@.phx.gbl...
> The Job is failing at Step 1, which was invoked by
> Schedule 1. Step 1 is the only step, and it is the basic
> backup syntax. The SQLAgent error log is blank for
> failures and warnings, information is the usual start up
> stuff.
> I know the agent is running, th eother jobs run and I get
> email alerts of their completion. I get the email alert
> of the failure of this job.
> What I'm hoping osmebody can tell me is why this job is
> run by Source = SQLSERVERAGENT and USER = N/A and the
> other, sucessfull jobs, are run by MSSQLSERVER and the
> USER = SQLEXEC
Thursday, February 16, 2012
backup jobs not runing on mssql7
Hi there,
I have a mssql-7 and 2/5 dbs get backed up nightly through
the scheduled backup jobs. 3 dbs keep failing at nights
and if I run the job manually. I can manually backup the
DBs through ALL TASKS..BACKUP DATABASE.
I've Checked:
SP-4
service running
sql agent running as admin
Job enabled
MDAC (latest)
jobs are 15mins apart
Any suggestions are appriciated.
ThanXHave you checked the logs and Agent history? What is the error that gets
produced?
Andrew J. Kelly SQL MVP
"Emil" <anonymous@.discussions.microsoft.com> wrote in message
news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
> Hi there,
> I have a mssql-7 and 2/5 dbs get backed up nightly through
> the scheduled backup jobs. 3 dbs keep failing at nights
> and if I run the job manually. I can manually backup the
> DBs through ALL TASKS..BACKUP DATABASE.
> I've Checked:
> SP-4
> service running
> sql agent running as admin
> Job enabled
> MDAC (latest)
> jobs are 15mins apart
> Any suggestions are appriciated.
> ThanX
>|||Andrew,
I have cheched the agent log and its clean! and the server
log doesn't mention anything regarding the job failure.
ThanX
>--Original Message--
>Have you checked the logs and Agent history? What is the
error that gets
>produced?
>--
>Andrew J. Kelly SQL MVP
>
>"Emil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
through[vbcol=seagreen]
>
>.
>|||What about the Job History? Right click on thjob in EM and choose Show
History. Then check the checkbox labeled Show Details and see what that
says.
Andrew J. Kelly SQL MVP
"Emil" <anonymous@.discussions.microsoft.com> wrote in message
news:f29b01c43dad$56d3bc90$a601280a@.phx.gbl...[vbcol=seagreen]
> Andrew,
> I have cheched the agent log and its clean! and the server
> log doesn't mention anything regarding the job failure.
> ThanX
> error that gets
> message
> through
I have a mssql-7 and 2/5 dbs get backed up nightly through
the scheduled backup jobs. 3 dbs keep failing at nights
and if I run the job manually. I can manually backup the
DBs through ALL TASKS..BACKUP DATABASE.
I've Checked:
SP-4
service running
sql agent running as admin
Job enabled
MDAC (latest)
jobs are 15mins apart
Any suggestions are appriciated.
ThanXHave you checked the logs and Agent history? What is the error that gets
produced?
Andrew J. Kelly SQL MVP
"Emil" <anonymous@.discussions.microsoft.com> wrote in message
news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
> Hi there,
> I have a mssql-7 and 2/5 dbs get backed up nightly through
> the scheduled backup jobs. 3 dbs keep failing at nights
> and if I run the job manually. I can manually backup the
> DBs through ALL TASKS..BACKUP DATABASE.
> I've Checked:
> SP-4
> service running
> sql agent running as admin
> Job enabled
> MDAC (latest)
> jobs are 15mins apart
> Any suggestions are appriciated.
> ThanX
>|||Andrew,
I have cheched the agent log and its clean! and the server
log doesn't mention anything regarding the job failure.
ThanX
>--Original Message--
>Have you checked the logs and Agent history? What is the
error that gets
>produced?
>--
>Andrew J. Kelly SQL MVP
>
>"Emil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
through[vbcol=seagreen]
>
>.
>|||What about the Job History? Right click on thjob in EM and choose Show
History. Then check the checkbox labeled Show Details and see what that
says.
Andrew J. Kelly SQL MVP
"Emil" <anonymous@.discussions.microsoft.com> wrote in message
news:f29b01c43dad$56d3bc90$a601280a@.phx.gbl...[vbcol=seagreen]
> Andrew,
> I have cheched the agent log and its clean! and the server
> log doesn't mention anything regarding the job failure.
> ThanX
> error that gets
> message
> through
Monday, February 13, 2012
backup jobs not runing on mssql7
Hi there,
I have a mssql-7 and 2/5 dbs get backed up nightly through
the scheduled backup jobs. 3 dbs keep failing at nights
and if I run the job manually. I can manually backup the
DBs through ALL TASKS..BACKUP DATABASE.
I've Checked:
SP-4
service running
sql agent running as admin
Job enabled
MDAC (latest)
jobs are 15mins apart
Any suggestions are appriciated.
ThanX
Have you checked the logs and Agent history? What is the error that gets
produced?
Andrew J. Kelly SQL MVP
"Emil" <anonymous@.discussions.microsoft.com> wrote in message
news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
> Hi there,
> I have a mssql-7 and 2/5 dbs get backed up nightly through
> the scheduled backup jobs. 3 dbs keep failing at nights
> and if I run the job manually. I can manually backup the
> DBs through ALL TASKS..BACKUP DATABASE.
> I've Checked:
> SP-4
> service running
> sql agent running as admin
> Job enabled
> MDAC (latest)
> jobs are 15mins apart
> Any suggestions are appriciated.
> ThanX
>
|||Andrew,
I have cheched the agent log and its clean! and the server
log doesn't mention anything regarding the job failure.
ThanX
>--Original Message--
>Have you checked the logs and Agent history? What is the
error that gets
>produced?
>--
>Andrew J. Kelly SQL MVP
>
>"Emil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
through
>
>.
>
|||What about the Job History? Right click on thjob in EM and choose Show
History. Then check the checkbox labeled Show Details and see what that
says.
Andrew J. Kelly SQL MVP
"Emil" <anonymous@.discussions.microsoft.com> wrote in message
news:f29b01c43dad$56d3bc90$a601280a@.phx.gbl...[vbcol=seagreen]
> Andrew,
> I have cheched the agent log and its clean! and the server
> log doesn't mention anything regarding the job failure.
> ThanX
> error that gets
> message
> through
I have a mssql-7 and 2/5 dbs get backed up nightly through
the scheduled backup jobs. 3 dbs keep failing at nights
and if I run the job manually. I can manually backup the
DBs through ALL TASKS..BACKUP DATABASE.
I've Checked:
SP-4
service running
sql agent running as admin
Job enabled
MDAC (latest)
jobs are 15mins apart
Any suggestions are appriciated.
ThanX
Have you checked the logs and Agent history? What is the error that gets
produced?
Andrew J. Kelly SQL MVP
"Emil" <anonymous@.discussions.microsoft.com> wrote in message
news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
> Hi there,
> I have a mssql-7 and 2/5 dbs get backed up nightly through
> the scheduled backup jobs. 3 dbs keep failing at nights
> and if I run the job manually. I can manually backup the
> DBs through ALL TASKS..BACKUP DATABASE.
> I've Checked:
> SP-4
> service running
> sql agent running as admin
> Job enabled
> MDAC (latest)
> jobs are 15mins apart
> Any suggestions are appriciated.
> ThanX
>
|||Andrew,
I have cheched the agent log and its clean! and the server
log doesn't mention anything regarding the job failure.
ThanX
>--Original Message--
>Have you checked the logs and Agent history? What is the
error that gets
>produced?
>--
>Andrew J. Kelly SQL MVP
>
>"Emil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
through
>
>.
>
|||What about the Job History? Right click on thjob in EM and choose Show
History. Then check the checkbox labeled Show Details and see what that
says.
Andrew J. Kelly SQL MVP
"Emil" <anonymous@.discussions.microsoft.com> wrote in message
news:f29b01c43dad$56d3bc90$a601280a@.phx.gbl...[vbcol=seagreen]
> Andrew,
> I have cheched the agent log and its clean! and the server
> log doesn't mention anything regarding the job failure.
> ThanX
> error that gets
> message
> through
backup jobs not runing on mssql7
Hi there,
I have a mssql-7 and 2/5 dbs get backed up nightly through
the scheduled backup jobs. 3 dbs keep failing at nights
and if I run the job manually. I can manually backup the
DBs through ALL TASKS..BACKUP DATABASE.
I've Checked:
SP-4
service running
sql agent running as admin
Job enabled
MDAC (latest)
jobs are 15mins apart
Any suggestions are appriciated.
ThanXHave you checked the logs and Agent history? What is the error that gets
produced?
--
Andrew J. Kelly SQL MVP
"Emil" <anonymous@.discussions.microsoft.com> wrote in message
news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
> Hi there,
> I have a mssql-7 and 2/5 dbs get backed up nightly through
> the scheduled backup jobs. 3 dbs keep failing at nights
> and if I run the job manually. I can manually backup the
> DBs through ALL TASKS..BACKUP DATABASE.
> I've Checked:
> SP-4
> service running
> sql agent running as admin
> Job enabled
> MDAC (latest)
> jobs are 15mins apart
> Any suggestions are appriciated.
> ThanX
>|||Andrew,
I have cheched the agent log and its clean! and the server
log doesn't mention anything regarding the job failure.
ThanX
>--Original Message--
>Have you checked the logs and Agent history? What is the
error that gets
>produced?
>--
>Andrew J. Kelly SQL MVP
>
>"Emil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
>> Hi there,
>> I have a mssql-7 and 2/5 dbs get backed up nightly
through
>> the scheduled backup jobs. 3 dbs keep failing at nights
>> and if I run the job manually. I can manually backup the
>> DBs through ALL TASKS..BACKUP DATABASE.
>> I've Checked:
>> SP-4
>> service running
>> sql agent running as admin
>> Job enabled
>> MDAC (latest)
>> jobs are 15mins apart
>> Any suggestions are appriciated.
>> ThanX
>>
>
>.
>|||What about the Job History? Right click on thjob in EM and choose Show
History. Then check the checkbox labeled Show Details and see what that
says.
--
Andrew J. Kelly SQL MVP
"Emil" <anonymous@.discussions.microsoft.com> wrote in message
news:f29b01c43dad$56d3bc90$a601280a@.phx.gbl...
> Andrew,
> I have cheched the agent log and its clean! and the server
> log doesn't mention anything regarding the job failure.
> ThanX
> >--Original Message--
> >Have you checked the logs and Agent history? What is the
> error that gets
> >produced?
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Emil" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
> >> Hi there,
> >> I have a mssql-7 and 2/5 dbs get backed up nightly
> through
> >> the scheduled backup jobs. 3 dbs keep failing at nights
> >> and if I run the job manually. I can manually backup the
> >> DBs through ALL TASKS..BACKUP DATABASE.
> >>
> >> I've Checked:
> >> SP-4
> >> service running
> >> sql agent running as admin
> >> Job enabled
> >> MDAC (latest)
> >> jobs are 15mins apart
> >>
> >> Any suggestions are appriciated.
> >> ThanX
> >>
> >>
> >
> >
> >.
> >
I have a mssql-7 and 2/5 dbs get backed up nightly through
the scheduled backup jobs. 3 dbs keep failing at nights
and if I run the job manually. I can manually backup the
DBs through ALL TASKS..BACKUP DATABASE.
I've Checked:
SP-4
service running
sql agent running as admin
Job enabled
MDAC (latest)
jobs are 15mins apart
Any suggestions are appriciated.
ThanXHave you checked the logs and Agent history? What is the error that gets
produced?
--
Andrew J. Kelly SQL MVP
"Emil" <anonymous@.discussions.microsoft.com> wrote in message
news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
> Hi there,
> I have a mssql-7 and 2/5 dbs get backed up nightly through
> the scheduled backup jobs. 3 dbs keep failing at nights
> and if I run the job manually. I can manually backup the
> DBs through ALL TASKS..BACKUP DATABASE.
> I've Checked:
> SP-4
> service running
> sql agent running as admin
> Job enabled
> MDAC (latest)
> jobs are 15mins apart
> Any suggestions are appriciated.
> ThanX
>|||Andrew,
I have cheched the agent log and its clean! and the server
log doesn't mention anything regarding the job failure.
ThanX
>--Original Message--
>Have you checked the logs and Agent history? What is the
error that gets
>produced?
>--
>Andrew J. Kelly SQL MVP
>
>"Emil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
>> Hi there,
>> I have a mssql-7 and 2/5 dbs get backed up nightly
through
>> the scheduled backup jobs. 3 dbs keep failing at nights
>> and if I run the job manually. I can manually backup the
>> DBs through ALL TASKS..BACKUP DATABASE.
>> I've Checked:
>> SP-4
>> service running
>> sql agent running as admin
>> Job enabled
>> MDAC (latest)
>> jobs are 15mins apart
>> Any suggestions are appriciated.
>> ThanX
>>
>
>.
>|||What about the Job History? Right click on thjob in EM and choose Show
History. Then check the checkbox labeled Show Details and see what that
says.
--
Andrew J. Kelly SQL MVP
"Emil" <anonymous@.discussions.microsoft.com> wrote in message
news:f29b01c43dad$56d3bc90$a601280a@.phx.gbl...
> Andrew,
> I have cheched the agent log and its clean! and the server
> log doesn't mention anything regarding the job failure.
> ThanX
> >--Original Message--
> >Have you checked the logs and Agent history? What is the
> error that gets
> >produced?
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Emil" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
> >> Hi there,
> >> I have a mssql-7 and 2/5 dbs get backed up nightly
> through
> >> the scheduled backup jobs. 3 dbs keep failing at nights
> >> and if I run the job manually. I can manually backup the
> >> DBs through ALL TASKS..BACKUP DATABASE.
> >>
> >> I've Checked:
> >> SP-4
> >> service running
> >> sql agent running as admin
> >> Job enabled
> >> MDAC (latest)
> >> jobs are 15mins apart
> >>
> >> Any suggestions are appriciated.
> >> ThanX
> >>
> >>
> >
> >
> >.
> >
Backup Jobs Locking ?
When you run a Backup Job (Database or TRN), is the database locked during the database?
If yes, to what extent?
Thanks
There are no locks that are not compatible with any user locks, but only those which could prevent database from deletion, for example.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
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
Backup Jobs are hanging
I am running MS SQL 7. This server has been running fine for 2+ years. Recently, all of the backup jobs stopped running.
I have 5 jobs that run. 4 of them are backup jobs and one runs a Stored Procedure. The Stored Procedure still runs perfectly, but all the backup jobs are getting stuck at the status 'Executing Job Step '1 (step 1)'
I am not sure what else to say. If there is any other information I should give, please be specific to what it is. Thanks.I've run into a similar situation a couple of months ago on my client's site. Turned out their "System Administrator" nuked SQLSvrService and SQLAgentService accounts and then re-created them. I had to set both services to run as system account before re-assigning them to run under appropriate accounts. After that all went back to normal and the admin guy cannot access sql server anymore :rolleyes:|||how can i tell if this is the case?|||Check Date Created and Last Modified properties of your service accounts in AD.|||AD? I am sorry but I am not sure what that is.|||Sorry, Active Directory...I assume you have it implemented? If not then it's on your Domain Controller/Domain Users and Computers.
I have 5 jobs that run. 4 of them are backup jobs and one runs a Stored Procedure. The Stored Procedure still runs perfectly, but all the backup jobs are getting stuck at the status 'Executing Job Step '1 (step 1)'
I am not sure what else to say. If there is any other information I should give, please be specific to what it is. Thanks.I've run into a similar situation a couple of months ago on my client's site. Turned out their "System Administrator" nuked SQLSvrService and SQLAgentService accounts and then re-created them. I had to set both services to run as system account before re-assigning them to run under appropriate accounts. After that all went back to normal and the admin guy cannot access sql server anymore :rolleyes:|||how can i tell if this is the case?|||Check Date Created and Last Modified properties of your service accounts in AD.|||AD? I am sorry but I am not sure what that is.|||Sorry, Active Directory...I assume you have it implemented? If not then it's on your Domain Controller/Domain Users and Computers.
Backup jobs and restores reporting false success!
Below are the problems I am having with my SQL 7.0
server. The first problem is backup jobs created with
the backup wizard and scheduled with the backup wizard
fail. The latest problem I discovered is my databases
can not be restored. The restore problem is very
critical to solve.
I tested restoring a database and the system reports the
database is restore sucessfully but it does not. The
sysdatabase does not show an entry for the restored
database. When I close Enterprise Manager and reopen the
database is gone. The mdf file is still in the data
directory. What is wrong with my sql server.
I can restore the database from the same file on my SQL
2000 server but it fail on every one of the SQL 7.0
servers. The production 7.0 SQL server and the 7.0 SQL
server on my laptop computer.
Kathy
The first point you made really made sense to me. I knew
transactions rolled back but never thought about a backup
job rolling back. I did as you suggested. I created a
backup device and backup a database (and restored it)
using T-SQL code. Of course this worked, because it is
not a job scheduled using the backup wizard interface. I
can always manually backup a database by right clicking,
choicing "All Task" and "Backup Database." But as soon
as I ran it as a scheduled job it will failed, or SQL
would reported it as suceeding with no BAK file.
So, I decided to create a backup job manually. It
worked!!! Then I scheduled it through the job interfaced
and it ran as scheduled.
To clarify this to you:
Backup Job FAILS when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Right click on the new backup device (otg_backupdevice)
and select "Backup a Database"
The SQL Server backup wizard window appears, select the
database (otg) to backup, add the backup device
(otg_backupdevice) and schedule the backup to occur once
a day at 12:00 A.M.
Then ran the job from the jobs window
The job fails.
Backup Job Succeeds when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Go to the job window
Right-click and select new job
Name the job - OTG backup to device
Click on the "Steps" tab and click on "New Step"
Select OTG as the database
In the command window I enter "backup database otg to
otg_backupdevice
Click on the "Schedule" tab and schedule it to backup at
10:00 am
The job suceeds
I suspect there is a problem with the backup wizard'
I am experiencing the same behavior on my SQL Server 7.0
running on NT (lastest service packs and patched on
both.) I too have witnessed the BAK file created and
then
deleted. I do not have the maintance plan configured to
deleting old .BAK files. I do receive this error message
when I create a backup from the "backup" option (not the
database maintenance plan.) This is the message I get:
10 percent backed up. [SQLSTATE 01000] (Message 3211) 20
percent backed up. [SQLSTATE 01000] (Message 3211)
ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message
258) General network error. Check your network
documentation. [SQLSTATE 08S01] (Error 11) 30 percent
backed up. [SQLSTATE 01000] (Message 3211). The step
failed.
NOTE: I am backing up to the server harddrive(NO NETWORK
INVOLVED!!!)with 20 gigs free. This database is only
199mb in size. I suspect the "general network error" is
bogus.
Please help!
>--Original Message--
>been researching a problem I discovered yesterday on the
>SQLSever 7.0 I administer. I discovered Call Heller
>posted a problem very similar to what I am
experiencing.
>
>Every morning I check to see if my SQL backup jobs run.
>No errors are reported. Yesterday, I was ask to restore
a
>database and discovered there was not output file i.e.
>teachercert_db_200308201656.BAK. I viewed job log,
>backup maintenance plan history and event viewer, all
>reported that the output file was created. Now, if I go
>in and manual backup the database directly from the
>database "All Task" option, an output file is created.
>
>Next I discovered all the database maintenance plans I
>viewed on changed yesterday, did not generate an output
>file when the scheduled job ran.
>
>My questions are:
>
>1. Why is the job not generating a output file and
all
>log report the output file was created?
>2. How do I open a case with Microsoft
>
>I'd appreciate any help you can offer.
>
>Kathy Long
>
>.
>It seems that you can create a backup job successfully just have
problems with the wizard.
Do you mean the maintenance exe? I always advise against using that so I
would say stick with simplest solution.
Instead of creating a device use backup dateabase dbname to disk = ...
This will create a backup file for you.
I always include the date and time in the filename to make
administration easier.
Here is an SP that will backup all databases on a server - you can get
some ideas from that:
http://www.nigelrivett.net/BackupAllDatabases.html
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
About the backup wizard to backup the database, I have provided solution
and you can try the steps I provided and if there is any problem, please
feel free to post any new message in that post and I am pleased and ready
to provide support there.
In this post, I will assist you to solve the prolem of restore your
database in SQL Server 7.0. I will work on it today. But please go to the
old post to check if the backup problem is solved.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
From the information you provided, your problem should be:
You want to restore database from a file. This process succeeded in SQL
Server 2000 but failed in SQL Server 7.
Actually, because the information you provide is so limited, I cannot give
an answer or solution this time. I wonder if you could provide the
information below for further analysis:
1) When using the Enterprise Manager to restore the database, what steps
have you taken? Could you give me more detailed information? Because you
just said you restored a database, but it is gone. I just want to the
detailed information of how you carry out this restore process, by T-SQL or
by Wizard? Have you got any message indicate that the restore is successful
or failed, what is these messages? Could you provide the System and
Application logs together with the output of SQLDiag if they are available
to you?
2) What kind of file you are using in this backup process? Is the file you
used the MDF file you mentioned above? If not, is it a SQL Server 2000
backup file or a SQL Server 7 backup file?
The feedback from you will be great helpful in clarifying the problem you
encountered and for us to analysis. I am waiting on your response and ready
to provide further help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||1. Steps to Enterprise manager is to right click on the
database to restore the backup file to and select the
file I want to use. Then on the Option tab I choice to
restore over the current database and change the path to
E:\Data\Test.mdb. The system clains it restore correctly
but when I attempt to open up the database I receive this
message: Error 911- could not locate entry in
sysdatabase 'Test.' No entry found with that name. Make
sure that the name is entered correctly.
I know the database is there because I still see it in
the file folder.
2. The file is created with SQL Server 7.0. I've used
backup devices and files.
When I do a manual using T-SQL restore to the same
database with the following command
RESTORE DATABASE PBDMI
FROM DISK = 'H:\PBDMI.bak'
I receive the following info in my results pane.
Processed 28968 pages for database 'PBDMI',
file 'PBDMI_Data' on file 1.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionRead (WrapperRead()).
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General
network error. Check your network documentation.
Processed 1 pages for database 'PBDMI', file 'PBDMI_Log'
on file 1.
Connection Broken
The database actually restores because I've deleted a row
in one of the tables and it comes back after the
restore. No luck restoring to a different database.
When I run the code to restore to a different database:
BACKUP DATABASE PBDMI
TO DISK = 'H:\PBDMI.bak'
RESTORE FILELISTONLY
FROM DISK = 'H:\PBDMI.bak'
RESTORE DATABASE PBDMI_New
FROM DISK = 'H:\PBDMI.bak'
WITH MOVE 'PBDMI' TO 'E:\Data\PBDMI_New_Data.mdf',
MOVE 'PBDMI_log' TO 'E:\Data\PBDMI_New_Log.ldf',
REPLACE
GO
Here is the message I receive. I pretty sure our
language is US-English
Changed language setting to us_english.
Processed 28968 pages for database 'PBDMI',
file 'PBDMI_Data' on file 6.
Processed 1 pages for database 'PBDMI', file 'PBDMI_Log'
on file 6.
Backup or restore operation successfully processed 28969
pages in 38.095 seconds (6.229 MB/sec).
LogicalName
PhysicalName
Type
FileGroupName
Size
MaxSize
----
----
-- ---
----
----
----
--- --
-- ----
----
-- -- --
--
PBDMI_Data
e:\data\PBDMI_Data.MDF
D
PRIMARY
240582656 35184372080640
PBDMI_Log
e:\data\PBDMI_Log.LDF
L
NULL
704905216 35184372080640
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 9
File 'PBDMI' is not a database file for
database 'PBDMI_New'.
Server: Msg 3013, Level 16, State 1, Line 9
Backup or restore operation terminating abnormally.
I cannot send any file to your email address.
Thanks for your help.
Kathy
>--Original Message--
>Hi Kathy,
> Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
> From the information you provided, your problem should
be:
>You want to restore database from a file. This process
succeeded in SQL
>Server 2000 but failed in SQL Server 7.
>
>Actually, because the information you provide is so
limited, I cannot give
>an answer or solution this time. I wonder if you could
provide the
>information below for further analysis:
>1) When using the Enterprise Manager to restore the
database, what steps
>have you taken? Could you give me more detailed
information? Because you
>just said you restored a database, but it is gone. I
just want to the
>detailed information of how you carry out this restore
process, by T-SQL or
>by Wizard? Have you got any message indicate that the
restore is successful
>or failed, what is these messages? Could you provide
the System and
>Application logs together with the output of SQLDiag if
they are available
>to you?
>2) What kind of file you are using in this backup
process? Is the file you
>used the MDF file you mentioned above? If not, is it a
SQL Server 2000
>backup file or a SQL Server 7 backup file?
> The feedback from you will be great helpful in
clarifying the problem you
>encountered and for us to analysis. I am waiting on your
response and ready
>to provide further help!
>
>Best regards
>Baisong Wei
> Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>
>.
>
server. The first problem is backup jobs created with
the backup wizard and scheduled with the backup wizard
fail. The latest problem I discovered is my databases
can not be restored. The restore problem is very
critical to solve.
I tested restoring a database and the system reports the
database is restore sucessfully but it does not. The
sysdatabase does not show an entry for the restored
database. When I close Enterprise Manager and reopen the
database is gone. The mdf file is still in the data
directory. What is wrong with my sql server.
I can restore the database from the same file on my SQL
2000 server but it fail on every one of the SQL 7.0
servers. The production 7.0 SQL server and the 7.0 SQL
server on my laptop computer.
Kathy
The first point you made really made sense to me. I knew
transactions rolled back but never thought about a backup
job rolling back. I did as you suggested. I created a
backup device and backup a database (and restored it)
using T-SQL code. Of course this worked, because it is
not a job scheduled using the backup wizard interface. I
can always manually backup a database by right clicking,
choicing "All Task" and "Backup Database." But as soon
as I ran it as a scheduled job it will failed, or SQL
would reported it as suceeding with no BAK file.
So, I decided to create a backup job manually. It
worked!!! Then I scheduled it through the job interfaced
and it ran as scheduled.
To clarify this to you:
Backup Job FAILS when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Right click on the new backup device (otg_backupdevice)
and select "Backup a Database"
The SQL Server backup wizard window appears, select the
database (otg) to backup, add the backup device
(otg_backupdevice) and schedule the backup to occur once
a day at 12:00 A.M.
Then ran the job from the jobs window
The job fails.
Backup Job Succeeds when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Go to the job window
Right-click and select new job
Name the job - OTG backup to device
Click on the "Steps" tab and click on "New Step"
Select OTG as the database
In the command window I enter "backup database otg to
otg_backupdevice
Click on the "Schedule" tab and schedule it to backup at
10:00 am
The job suceeds
I suspect there is a problem with the backup wizard'
I am experiencing the same behavior on my SQL Server 7.0
running on NT (lastest service packs and patched on
both.) I too have witnessed the BAK file created and
then
deleted. I do not have the maintance plan configured to
deleting old .BAK files. I do receive this error message
when I create a backup from the "backup" option (not the
database maintenance plan.) This is the message I get:
10 percent backed up. [SQLSTATE 01000] (Message 3211) 20
percent backed up. [SQLSTATE 01000] (Message 3211)
ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message
258) General network error. Check your network
documentation. [SQLSTATE 08S01] (Error 11) 30 percent
backed up. [SQLSTATE 01000] (Message 3211). The step
failed.
NOTE: I am backing up to the server harddrive(NO NETWORK
INVOLVED!!!)with 20 gigs free. This database is only
199mb in size. I suspect the "general network error" is
bogus.
Please help!
>--Original Message--
>been researching a problem I discovered yesterday on the
>SQLSever 7.0 I administer. I discovered Call Heller
>posted a problem very similar to what I am
experiencing.
>
>Every morning I check to see if my SQL backup jobs run.
>No errors are reported. Yesterday, I was ask to restore
a
>database and discovered there was not output file i.e.
>teachercert_db_200308201656.BAK. I viewed job log,
>backup maintenance plan history and event viewer, all
>reported that the output file was created. Now, if I go
>in and manual backup the database directly from the
>database "All Task" option, an output file is created.
>
>Next I discovered all the database maintenance plans I
>viewed on changed yesterday, did not generate an output
>file when the scheduled job ran.
>
>My questions are:
>
>1. Why is the job not generating a output file and
all
>log report the output file was created?
>2. How do I open a case with Microsoft
>
>I'd appreciate any help you can offer.
>
>Kathy Long
>
>.
>It seems that you can create a backup job successfully just have
problems with the wizard.
Do you mean the maintenance exe? I always advise against using that so I
would say stick with simplest solution.
Instead of creating a device use backup dateabase dbname to disk = ...
This will create a backup file for you.
I always include the date and time in the filename to make
administration easier.
Here is an SP that will backup all databases on a server - you can get
some ideas from that:
http://www.nigelrivett.net/BackupAllDatabases.html
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
About the backup wizard to backup the database, I have provided solution
and you can try the steps I provided and if there is any problem, please
feel free to post any new message in that post and I am pleased and ready
to provide support there.
In this post, I will assist you to solve the prolem of restore your
database in SQL Server 7.0. I will work on it today. But please go to the
old post to check if the backup problem is solved.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
From the information you provided, your problem should be:
You want to restore database from a file. This process succeeded in SQL
Server 2000 but failed in SQL Server 7.
Actually, because the information you provide is so limited, I cannot give
an answer or solution this time. I wonder if you could provide the
information below for further analysis:
1) When using the Enterprise Manager to restore the database, what steps
have you taken? Could you give me more detailed information? Because you
just said you restored a database, but it is gone. I just want to the
detailed information of how you carry out this restore process, by T-SQL or
by Wizard? Have you got any message indicate that the restore is successful
or failed, what is these messages? Could you provide the System and
Application logs together with the output of SQLDiag if they are available
to you?
2) What kind of file you are using in this backup process? Is the file you
used the MDF file you mentioned above? If not, is it a SQL Server 2000
backup file or a SQL Server 7 backup file?
The feedback from you will be great helpful in clarifying the problem you
encountered and for us to analysis. I am waiting on your response and ready
to provide further help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||1. Steps to Enterprise manager is to right click on the
database to restore the backup file to and select the
file I want to use. Then on the Option tab I choice to
restore over the current database and change the path to
E:\Data\Test.mdb. The system clains it restore correctly
but when I attempt to open up the database I receive this
message: Error 911- could not locate entry in
sysdatabase 'Test.' No entry found with that name. Make
sure that the name is entered correctly.
I know the database is there because I still see it in
the file folder.
2. The file is created with SQL Server 7.0. I've used
backup devices and files.
When I do a manual using T-SQL restore to the same
database with the following command
RESTORE DATABASE PBDMI
FROM DISK = 'H:\PBDMI.bak'
I receive the following info in my results pane.
Processed 28968 pages for database 'PBDMI',
file 'PBDMI_Data' on file 1.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionRead (WrapperRead()).
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General
network error. Check your network documentation.
Processed 1 pages for database 'PBDMI', file 'PBDMI_Log'
on file 1.
Connection Broken
The database actually restores because I've deleted a row
in one of the tables and it comes back after the
restore. No luck restoring to a different database.
When I run the code to restore to a different database:
BACKUP DATABASE PBDMI
TO DISK = 'H:\PBDMI.bak'
RESTORE FILELISTONLY
FROM DISK = 'H:\PBDMI.bak'
RESTORE DATABASE PBDMI_New
FROM DISK = 'H:\PBDMI.bak'
WITH MOVE 'PBDMI' TO 'E:\Data\PBDMI_New_Data.mdf',
MOVE 'PBDMI_log' TO 'E:\Data\PBDMI_New_Log.ldf',
REPLACE
GO
Here is the message I receive. I pretty sure our
language is US-English
Changed language setting to us_english.
Processed 28968 pages for database 'PBDMI',
file 'PBDMI_Data' on file 6.
Processed 1 pages for database 'PBDMI', file 'PBDMI_Log'
on file 6.
Backup or restore operation successfully processed 28969
pages in 38.095 seconds (6.229 MB/sec).
LogicalName
PhysicalName
Type
FileGroupName
Size
MaxSize
----
----
-- ---
----
----
----
--- --
-- ----
----
-- -- --
--
PBDMI_Data
e:\data\PBDMI_Data.MDF
D
PRIMARY
240582656 35184372080640
PBDMI_Log
e:\data\PBDMI_Log.LDF
L
NULL
704905216 35184372080640
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 9
File 'PBDMI' is not a database file for
database 'PBDMI_New'.
Server: Msg 3013, Level 16, State 1, Line 9
Backup or restore operation terminating abnormally.
I cannot send any file to your email address.
Thanks for your help.
Kathy
>--Original Message--
>Hi Kathy,
> Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
> From the information you provided, your problem should
be:
>You want to restore database from a file. This process
succeeded in SQL
>Server 2000 but failed in SQL Server 7.
>
>Actually, because the information you provide is so
limited, I cannot give
>an answer or solution this time. I wonder if you could
provide the
>information below for further analysis:
>1) When using the Enterprise Manager to restore the
database, what steps
>have you taken? Could you give me more detailed
information? Because you
>just said you restored a database, but it is gone. I
just want to the
>detailed information of how you carry out this restore
process, by T-SQL or
>by Wizard? Have you got any message indicate that the
restore is successful
>or failed, what is these messages? Could you provide
the System and
>Application logs together with the output of SQLDiag if
they are available
>to you?
>2) What kind of file you are using in this backup
process? Is the file you
>used the MDF file you mentioned above? If not, is it a
SQL Server 2000
>backup file or a SQL Server 7 backup file?
> The feedback from you will be great helpful in
clarifying the problem you
>encountered and for us to analysis. I am waiting on your
response and ready
>to provide further help!
>
>Best regards
>Baisong Wei
> Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>
>.
>
Back-up Jobs
Greetings
I hope this is an easy question. I have a set of database (16 of them). I
have been using the enterprise manager to set-up back-up jobs for each one.
Can I use a script to insert the jobs in the SQL agent's job queue?
Thanks
Marc Walgren
Marc Walgren
Mitten Software "Powerful Solutions Made Easy"
1865 Wayzata Blvd, Suite 218, Long Lake, MN 55356 USA
Providing Web and Windows Products and Consulting Services
e-mail: mitten@.MittenSoftware.com www.mittensoftware.com
Sales: 800-825-5461 Voice: 952-745-4941 Fax: 952-745-4944
If I understand your question right, then yes, you can create the backup
scripts on your own. You just need to learn the BACKUP command, which is
documented in SQL Server Books Online.
Also, you can generate a script for an existing job, using Enterprise
Manager. Then you can change the parameters of that script, to create your
own job.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Marc Walgren" <marcmittenATyahoo.com> wrote in message
news:u5%23uRBEwEHA.3080@.TK2MSFTNGP12.phx.gbl...
> Greetings
> I hope this is an easy question. I have a set of database (16 of them). I
> have been using the enterprise manager to set-up back-up jobs for each
one.
> Can I use a script to insert the jobs in the SQL agent's job queue?
> Thanks
> Marc Walgren
> --
> Marc Walgren
> Mitten Software "Powerful Solutions Made Easy"
> 1865 Wayzata Blvd, Suite 218, Long Lake, MN 55356 USA
> Providing Web and Windows Products and Consulting Services
> ---
--
> e-mail: mitten@.MittenSoftware.com www.mittensoftware.com
> Sales: 800-825-5461 Voice: 952-745-4941 Fax: 952-745-4944
>
>
|||Thanks
I forgot to look for the "Generate Sql Script" option on the job - all task
popup menu.
Marc
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23YNTGMEwEHA.3916@.TK2MSFTNGP10.phx.gbl...
> If I understand your question right, then yes, you can create the backup
> scripts on your own. You just need to learn the BACKUP command, which is
> documented in SQL Server Books Online.
> Also, you can generate a script for an existing job, using Enterprise
> Manager. Then you can change the parameters of that script, to create your
> own job.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Marc Walgren" <marcmittenATyahoo.com> wrote in message
> news:u5%23uRBEwEHA.3080@.TK2MSFTNGP12.phx.gbl...
> one.
> --
>
I hope this is an easy question. I have a set of database (16 of them). I
have been using the enterprise manager to set-up back-up jobs for each one.
Can I use a script to insert the jobs in the SQL agent's job queue?
Thanks
Marc Walgren
Marc Walgren
Mitten Software "Powerful Solutions Made Easy"
1865 Wayzata Blvd, Suite 218, Long Lake, MN 55356 USA
Providing Web and Windows Products and Consulting Services
e-mail: mitten@.MittenSoftware.com www.mittensoftware.com
Sales: 800-825-5461 Voice: 952-745-4941 Fax: 952-745-4944
If I understand your question right, then yes, you can create the backup
scripts on your own. You just need to learn the BACKUP command, which is
documented in SQL Server Books Online.
Also, you can generate a script for an existing job, using Enterprise
Manager. Then you can change the parameters of that script, to create your
own job.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Marc Walgren" <marcmittenATyahoo.com> wrote in message
news:u5%23uRBEwEHA.3080@.TK2MSFTNGP12.phx.gbl...
> Greetings
> I hope this is an easy question. I have a set of database (16 of them). I
> have been using the enterprise manager to set-up back-up jobs for each
one.
> Can I use a script to insert the jobs in the SQL agent's job queue?
> Thanks
> Marc Walgren
> --
> Marc Walgren
> Mitten Software "Powerful Solutions Made Easy"
> 1865 Wayzata Blvd, Suite 218, Long Lake, MN 55356 USA
> Providing Web and Windows Products and Consulting Services
> ---
--
> e-mail: mitten@.MittenSoftware.com www.mittensoftware.com
> Sales: 800-825-5461 Voice: 952-745-4941 Fax: 952-745-4944
>
>
|||Thanks
I forgot to look for the "Generate Sql Script" option on the job - all task
popup menu.
Marc
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23YNTGMEwEHA.3916@.TK2MSFTNGP10.phx.gbl...
> If I understand your question right, then yes, you can create the backup
> scripts on your own. You just need to learn the BACKUP command, which is
> documented in SQL Server Books Online.
> Also, you can generate a script for an existing job, using Enterprise
> Manager. Then you can change the parameters of that script, to create your
> own job.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Marc Walgren" <marcmittenATyahoo.com> wrote in message
> news:u5%23uRBEwEHA.3080@.TK2MSFTNGP12.phx.gbl...
> one.
> --
>
Back-up Jobs
Greetings
I hope this is an easy question. I have a set of database (16 of them). I
have been using the enterprise manager to set-up back-up jobs for each one.
Can I use a script to insert the jobs in the SQL agent's job queue?
Thanks
Marc Walgren
--
Marc Walgren
Mitten Software "Powerful Solutions Made Easy"
1865 Wayzata Blvd, Suite 218, Long Lake, MN 55356 USA
Providing Web and Windows Products and Consulting Services
----
e-mail: mitten@.MittenSoftware.com www.mittensoftware.com
Sales: 800-825-5461 Voice: 952-745-4941 Fax: 952-745-4944If I understand your question right, then yes, you can create the backup
scripts on your own. You just need to learn the BACKUP command, which is
documented in SQL Server Books Online.
Also, you can generate a script for an existing job, using Enterprise
Manager. Then you can change the parameters of that script, to create your
own job.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Marc Walgren" <marcmittenATyahoo.com> wrote in message
news:u5%23uRBEwEHA.3080@.TK2MSFTNGP12.phx.gbl...
> Greetings
> I hope this is an easy question. I have a set of database (16 of them). I
> have been using the enterprise manager to set-up back-up jobs for each
one.
> Can I use a script to insert the jobs in the SQL agent's job queue?
> Thanks
> Marc Walgren
> --
> Marc Walgren
> Mitten Software "Powerful Solutions Made Easy"
> 1865 Wayzata Blvd, Suite 218, Long Lake, MN 55356 USA
> Providing Web and Windows Products and Consulting Services
> ---
--
> e-mail: mitten@.MittenSoftware.com www.mittensoftware.com
> Sales: 800-825-5461 Voice: 952-745-4941 Fax: 952-745-4944
>
>|||Thanks
I forgot to look for the "Generate Sql Script" option on the job - all task
popup menu.
Marc
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23YNTGMEwEHA.3916@.TK2MSFTNGP10.phx.gbl...
> If I understand your question right, then yes, you can create the backup
> scripts on your own. You just need to learn the BACKUP command, which is
> documented in SQL Server Books Online.
> Also, you can generate a script for an existing job, using Enterprise
> Manager. Then you can change the parameters of that script, to create your
> own job.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Marc Walgren" <marcmittenATyahoo.com> wrote in message
> news:u5%23uRBEwEHA.3080@.TK2MSFTNGP12.phx.gbl...
>> Greetings
>> I hope this is an easy question. I have a set of database (16 of them). I
>> have been using the enterprise manager to set-up back-up jobs for each
> one.
>> Can I use a script to insert the jobs in the SQL agent's job queue?
>> Thanks
>> Marc Walgren
>> --
>> Marc Walgren
>> Mitten Software "Powerful Solutions Made Easy"
>> 1865 Wayzata Blvd, Suite 218, Long Lake, MN 55356 USA
>> Providing Web and Windows Products and Consulting Services
>> ---
> --
>> e-mail: mitten@.MittenSoftware.com www.mittensoftware.com
>> Sales: 800-825-5461 Voice: 952-745-4941 Fax: 952-745-4944
>>
>
I hope this is an easy question. I have a set of database (16 of them). I
have been using the enterprise manager to set-up back-up jobs for each one.
Can I use a script to insert the jobs in the SQL agent's job queue?
Thanks
Marc Walgren
--
Marc Walgren
Mitten Software "Powerful Solutions Made Easy"
1865 Wayzata Blvd, Suite 218, Long Lake, MN 55356 USA
Providing Web and Windows Products and Consulting Services
----
e-mail: mitten@.MittenSoftware.com www.mittensoftware.com
Sales: 800-825-5461 Voice: 952-745-4941 Fax: 952-745-4944If I understand your question right, then yes, you can create the backup
scripts on your own. You just need to learn the BACKUP command, which is
documented in SQL Server Books Online.
Also, you can generate a script for an existing job, using Enterprise
Manager. Then you can change the parameters of that script, to create your
own job.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Marc Walgren" <marcmittenATyahoo.com> wrote in message
news:u5%23uRBEwEHA.3080@.TK2MSFTNGP12.phx.gbl...
> Greetings
> I hope this is an easy question. I have a set of database (16 of them). I
> have been using the enterprise manager to set-up back-up jobs for each
one.
> Can I use a script to insert the jobs in the SQL agent's job queue?
> Thanks
> Marc Walgren
> --
> Marc Walgren
> Mitten Software "Powerful Solutions Made Easy"
> 1865 Wayzata Blvd, Suite 218, Long Lake, MN 55356 USA
> Providing Web and Windows Products and Consulting Services
> ---
--
> e-mail: mitten@.MittenSoftware.com www.mittensoftware.com
> Sales: 800-825-5461 Voice: 952-745-4941 Fax: 952-745-4944
>
>|||Thanks
I forgot to look for the "Generate Sql Script" option on the job - all task
popup menu.
Marc
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23YNTGMEwEHA.3916@.TK2MSFTNGP10.phx.gbl...
> If I understand your question right, then yes, you can create the backup
> scripts on your own. You just need to learn the BACKUP command, which is
> documented in SQL Server Books Online.
> Also, you can generate a script for an existing job, using Enterprise
> Manager. Then you can change the parameters of that script, to create your
> own job.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Marc Walgren" <marcmittenATyahoo.com> wrote in message
> news:u5%23uRBEwEHA.3080@.TK2MSFTNGP12.phx.gbl...
>> Greetings
>> I hope this is an easy question. I have a set of database (16 of them). I
>> have been using the enterprise manager to set-up back-up jobs for each
> one.
>> Can I use a script to insert the jobs in the SQL agent's job queue?
>> Thanks
>> Marc Walgren
>> --
>> Marc Walgren
>> Mitten Software "Powerful Solutions Made Easy"
>> 1865 Wayzata Blvd, Suite 218, Long Lake, MN 55356 USA
>> Providing Web and Windows Products and Consulting Services
>> ---
> --
>> e-mail: mitten@.MittenSoftware.com www.mittensoftware.com
>> Sales: 800-825-5461 Voice: 952-745-4941 Fax: 952-745-4944
>>
>
Back-up Jobs
Greetings
I hope this is an easy question. I have a set of database (16 of them). I
have been using the enterprise manager to set-up back-up jobs for each one.
Can I use a script to insert the jobs in the SQL agent's job queue?
Thanks
Marc Walgren
Marc Walgren
Mitten Software "Powerful Solutions Made Easy"
1865 Wayzata Blvd, Suite 218, Long Lake, MN 55356 USA
Providing Web and Windows Products and Consulting Services
----
e-mail: mitten@.MittenSoftware.com www.mittensoftware.com
Sales: 800-825-5461 Voice: 952-745-4941 Fax: 952-745-4944If I understand your question right, then yes, you can create the backup
scripts on your own. You just need to learn the BACKUP command, which is
documented in SQL Server Books Online.
Also, you can generate a script for an existing job, using Enterprise
Manager. Then you can change the parameters of that script, to create your
own job.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Marc Walgren" <marcmittenATyahoo.com> wrote in message
news:u5%23uRBEwEHA.3080@.TK2MSFTNGP12.phx.gbl...
> Greetings
> I hope this is an easy question. I have a set of database (16 of them). I
> have been using the enterprise manager to set-up back-up jobs for each
one.
> Can I use a script to insert the jobs in the SQL agent's job queue?
> Thanks
> Marc Walgren
> --
> Marc Walgren
> Mitten Software "Powerful Solutions Made Easy"
> 1865 Wayzata Blvd, Suite 218, Long Lake, MN 55356 USA
> Providing Web and Windows Products and Consulting Services
> ---
--
> e-mail: mitten@.MittenSoftware.com www.mittensoftware.com
> Sales: 800-825-5461 Voice: 952-745-4941 Fax: 952-745-4944
>
>|||Thanks
I forgot to look for the "Generate Sql Script" option on the job - all task
popup menu.
Marc
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23YNTGMEwEHA.3916@.TK2MSFTNGP10.phx.gbl...
> If I understand your question right, then yes, you can create the backup
> scripts on your own. You just need to learn the BACKUP command, which is
> documented in SQL Server Books Online.
> Also, you can generate a script for an existing job, using Enterprise
> Manager. Then you can change the parameters of that script, to create your
> own job.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Marc Walgren" <marcmittenATyahoo.com> wrote in message
> news:u5%23uRBEwEHA.3080@.TK2MSFTNGP12.phx.gbl...
> one.
> --
>
I hope this is an easy question. I have a set of database (16 of them). I
have been using the enterprise manager to set-up back-up jobs for each one.
Can I use a script to insert the jobs in the SQL agent's job queue?
Thanks
Marc Walgren
Marc Walgren
Mitten Software "Powerful Solutions Made Easy"
1865 Wayzata Blvd, Suite 218, Long Lake, MN 55356 USA
Providing Web and Windows Products and Consulting Services
----
e-mail: mitten@.MittenSoftware.com www.mittensoftware.com
Sales: 800-825-5461 Voice: 952-745-4941 Fax: 952-745-4944If I understand your question right, then yes, you can create the backup
scripts on your own. You just need to learn the BACKUP command, which is
documented in SQL Server Books Online.
Also, you can generate a script for an existing job, using Enterprise
Manager. Then you can change the parameters of that script, to create your
own job.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Marc Walgren" <marcmittenATyahoo.com> wrote in message
news:u5%23uRBEwEHA.3080@.TK2MSFTNGP12.phx.gbl...
> Greetings
> I hope this is an easy question. I have a set of database (16 of them). I
> have been using the enterprise manager to set-up back-up jobs for each
one.
> Can I use a script to insert the jobs in the SQL agent's job queue?
> Thanks
> Marc Walgren
> --
> Marc Walgren
> Mitten Software "Powerful Solutions Made Easy"
> 1865 Wayzata Blvd, Suite 218, Long Lake, MN 55356 USA
> Providing Web and Windows Products and Consulting Services
> ---
--
> e-mail: mitten@.MittenSoftware.com www.mittensoftware.com
> Sales: 800-825-5461 Voice: 952-745-4941 Fax: 952-745-4944
>
>|||Thanks
I forgot to look for the "Generate Sql Script" option on the job - all task
popup menu.
Marc
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23YNTGMEwEHA.3916@.TK2MSFTNGP10.phx.gbl...
> If I understand your question right, then yes, you can create the backup
> scripts on your own. You just need to learn the BACKUP command, which is
> documented in SQL Server Books Online.
> Also, you can generate a script for an existing job, using Enterprise
> Manager. Then you can change the parameters of that script, to create your
> own job.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Marc Walgren" <marcmittenATyahoo.com> wrote in message
> news:u5%23uRBEwEHA.3080@.TK2MSFTNGP12.phx.gbl...
> one.
> --
>
Backup Jobs
I have created 4 backup jobs inside of Enterprise Manager. For 3 of them I
created a backup device first and then the backup job. For 1 of them I just
created the backup job. I would like to delete the job that has no backup
device and would also like to edit the other 3 jobs. I do not see how to do
this. I can see my backup devices but not the jobs themself.
Can someone tell me how I can delete the one job and edit the remaining 3?
Thanks
JohnnyDid you check SQL Server Agent - Jobs ?
"Johnny Wright" wrote:
> I have created 4 backup jobs inside of Enterprise Manager. For 3 of them
I
> created a backup device first and then the backup job. For 1 of them I ju
st
> created the backup job. I would like to delete the job that has no backup
> device and would also like to edit the other 3 jobs. I do not see how to
do
> this. I can see my backup devices but not the jobs themself.
> Can someone tell me how I can delete the one job and edit the remaining 3?
> Thanks
> Johnny|||Umut
No I did not. Thanks for the quick response.
Ahhh such a simple thing when you know how..
Johnny
"Umut Nazlica" wrote:
[vbcol=seagreen]
> Did you check SQL Server Agent - Jobs ?
>
> "Johnny Wright" wrote:
>
created a backup device first and then the backup job. For 1 of them I just
created the backup job. I would like to delete the job that has no backup
device and would also like to edit the other 3 jobs. I do not see how to do
this. I can see my backup devices but not the jobs themself.
Can someone tell me how I can delete the one job and edit the remaining 3?
Thanks
JohnnyDid you check SQL Server Agent - Jobs ?
"Johnny Wright" wrote:
> I have created 4 backup jobs inside of Enterprise Manager. For 3 of them
I
> created a backup device first and then the backup job. For 1 of them I ju
st
> created the backup job. I would like to delete the job that has no backup
> device and would also like to edit the other 3 jobs. I do not see how to
do
> this. I can see my backup devices but not the jobs themself.
> Can someone tell me how I can delete the one job and edit the remaining 3?
> Thanks
> Johnny|||Umut
No I did not. Thanks for the quick response.
Ahhh such a simple thing when you know how..
Johnny
"Umut Nazlica" wrote:
[vbcol=seagreen]
> Did you check SQL Server Agent - Jobs ?
>
> "Johnny Wright" wrote:
>
Backup jobs
SQL backup files are not being deleted after 3 days of age, as is configured
in the backup maintenance job.
What are possible reasons for this.
New to this stuff!!!
This post from Bill at MS is a pretty good start.
http://support.microsoft.com/default...;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly SQL MVP
"huckster" <huckster@.discussions.microsoft.com> wrote in message
news:1BD61386-4219-41C4-9A10-F4AF0B690EBF@.microsoft.com...
> SQL backup files are not being deleted after 3 days of age, as is
> configured
> in the backup maintenance job.
> What are possible reasons for this.
> New to this stuff!!!
in the backup maintenance job.
What are possible reasons for this.
New to this stuff!!!
This post from Bill at MS is a pretty good start.
http://support.microsoft.com/default...;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly SQL MVP
"huckster" <huckster@.discussions.microsoft.com> wrote in message
news:1BD61386-4219-41C4-9A10-F4AF0B690EBF@.microsoft.com...
> SQL backup files are not being deleted after 3 days of age, as is
> configured
> in the backup maintenance job.
> What are possible reasons for this.
> New to this stuff!!!
Backup Jobs
I have created 4 backup jobs inside of Enterprise Manager. For 3 of them I
created a backup device first and then the backup job. For 1 of them I just
created the backup job. I would like to delete the job that has no backup
device and would also like to edit the other 3 jobs. I do not see how to do
this. I can see my backup devices but not the jobs themself.
Can someone tell me how I can delete the one job and edit the remaining 3?
Thanks
Johnny
Did you check SQL Server Agent - Jobs ?
"Johnny Wright" wrote:
> I have created 4 backup jobs inside of Enterprise Manager. For 3 of them I
> created a backup device first and then the backup job. For 1 of them I just
> created the backup job. I would like to delete the job that has no backup
> device and would also like to edit the other 3 jobs. I do not see how to do
> this. I can see my backup devices but not the jobs themself.
> Can someone tell me how I can delete the one job and edit the remaining 3?
> Thanks
> Johnny
|||Umut
No I did not. Thanks for the quick response.
Ahhh such a simple thing when you know how..
Johnny
"Umut Nazlica" wrote:
[vbcol=seagreen]
> Did you check SQL Server Agent - Jobs ?
>
> "Johnny Wright" wrote:
created a backup device first and then the backup job. For 1 of them I just
created the backup job. I would like to delete the job that has no backup
device and would also like to edit the other 3 jobs. I do not see how to do
this. I can see my backup devices but not the jobs themself.
Can someone tell me how I can delete the one job and edit the remaining 3?
Thanks
Johnny
Did you check SQL Server Agent - Jobs ?
"Johnny Wright" wrote:
> I have created 4 backup jobs inside of Enterprise Manager. For 3 of them I
> created a backup device first and then the backup job. For 1 of them I just
> created the backup job. I would like to delete the job that has no backup
> device and would also like to edit the other 3 jobs. I do not see how to do
> this. I can see my backup devices but not the jobs themself.
> Can someone tell me how I can delete the one job and edit the remaining 3?
> Thanks
> Johnny
|||Umut
No I did not. Thanks for the quick response.
Ahhh such a simple thing when you know how..
Johnny
"Umut Nazlica" wrote:
[vbcol=seagreen]
> Did you check SQL Server Agent - Jobs ?
>
> "Johnny Wright" wrote:
Backup jobs
SQL backup files are not being deleted after 3 days of age, as is configured
in the backup maintenance job.
What are possible reasons for this.
New to this stuff!!!This post from Bill at MS is a pretty good start.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
--
Andrew J. Kelly SQL MVP
"huckster" <huckster@.discussions.microsoft.com> wrote in message
news:1BD61386-4219-41C4-9A10-F4AF0B690EBF@.microsoft.com...
> SQL backup files are not being deleted after 3 days of age, as is
> configured
> in the backup maintenance job.
> What are possible reasons for this.
> New to this stuff!!!
in the backup maintenance job.
What are possible reasons for this.
New to this stuff!!!This post from Bill at MS is a pretty good start.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
--
Andrew J. Kelly SQL MVP
"huckster" <huckster@.discussions.microsoft.com> wrote in message
news:1BD61386-4219-41C4-9A10-F4AF0B690EBF@.microsoft.com...
> SQL backup files are not being deleted after 3 days of age, as is
> configured
> in the backup maintenance job.
> What are possible reasons for this.
> New to this stuff!!!
Backup Jobs
I have created 4 backup jobs inside of Enterprise Manager. For 3 of them I
created a backup device first and then the backup job. For 1 of them I just
created the backup job. I would like to delete the job that has no backup
device and would also like to edit the other 3 jobs. I do not see how to do
this. I can see my backup devices but not the jobs themself.
Can someone tell me how I can delete the one job and edit the remaining 3?
Thanks
JohnnyDid you check SQL Server Agent - Jobs ?
"Johnny Wright" wrote:
> I have created 4 backup jobs inside of Enterprise Manager. For 3 of them I
> created a backup device first and then the backup job. For 1 of them I just
> created the backup job. I would like to delete the job that has no backup
> device and would also like to edit the other 3 jobs. I do not see how to do
> this. I can see my backup devices but not the jobs themself.
> Can someone tell me how I can delete the one job and edit the remaining 3?
> Thanks
> Johnny|||Umut
No I did not. Thanks for the quick response.
Ahhh such a simple thing when you know how..
Johnny
"Umut Nazlica" wrote:
> Did you check SQL Server Agent - Jobs ?
>
> "Johnny Wright" wrote:
> > I have created 4 backup jobs inside of Enterprise Manager. For 3 of them I
> > created a backup device first and then the backup job. For 1 of them I just
> > created the backup job. I would like to delete the job that has no backup
> > device and would also like to edit the other 3 jobs. I do not see how to do
> > this. I can see my backup devices but not the jobs themself.
> >
> > Can someone tell me how I can delete the one job and edit the remaining 3?
> > Thanks
> > Johnny
created a backup device first and then the backup job. For 1 of them I just
created the backup job. I would like to delete the job that has no backup
device and would also like to edit the other 3 jobs. I do not see how to do
this. I can see my backup devices but not the jobs themself.
Can someone tell me how I can delete the one job and edit the remaining 3?
Thanks
JohnnyDid you check SQL Server Agent - Jobs ?
"Johnny Wright" wrote:
> I have created 4 backup jobs inside of Enterprise Manager. For 3 of them I
> created a backup device first and then the backup job. For 1 of them I just
> created the backup job. I would like to delete the job that has no backup
> device and would also like to edit the other 3 jobs. I do not see how to do
> this. I can see my backup devices but not the jobs themself.
> Can someone tell me how I can delete the one job and edit the remaining 3?
> Thanks
> Johnny|||Umut
No I did not. Thanks for the quick response.
Ahhh such a simple thing when you know how..
Johnny
"Umut Nazlica" wrote:
> Did you check SQL Server Agent - Jobs ?
>
> "Johnny Wright" wrote:
> > I have created 4 backup jobs inside of Enterprise Manager. For 3 of them I
> > created a backup device first and then the backup job. For 1 of them I just
> > created the backup job. I would like to delete the job that has no backup
> > device and would also like to edit the other 3 jobs. I do not see how to do
> > this. I can see my backup devices but not the jobs themself.
> >
> > Can someone tell me how I can delete the one job and edit the remaining 3?
> > Thanks
> > Johnny
Backup Jobs
I am backing up my SQL Server databases to VDM (SAN) but all my backups are
taking very long some times 12 hours and always failing with the following
error.
failed: 64(The specified network name is no longer available.) [SQLSTATE
42000] (Error 3202) A nonrecoverable I/O error occurred on file
Any help will be appreciated.
Thanks!Get your network and SAN people involved, because the problem is something
that they will have to solve. (If you are also the network and SAN person,
the problem is probably not in your SQL Server.)
This is most likely a bottleneck in either the network layers or the SAN
disk or disk controller throughput. I suspect that at the lower levels of
network and/or disk I/O there are a lot of retries happening, until finally
one of them just cannot recover in time. Then your backup fails because it
can no longer see that the file to which it was backing up.
How much data are you backing up? How big is the largest database? How
long would you expect it to take? You are probably having this problem on
your biggest databases, which put the greatest backup load on your
landscape, and the little databases are having no problems.
RLF
<msnews.microsoft.com> wrote in message
news:u%23CF2Z$FIHA.5980@.TK2MSFTNGP04.phx.gbl...
> I am backing up my SQL Server databases to VDM (SAN) but all my backups
> are taking very long some times 12 hours and always failing with the
> following error.
> failed: 64(The specified network name is no longer available.) [SQLSTATE
> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
> Any help will be appreciated.
> Thanks!
>|||Are you watching the network traffic and CPU on both your server and the
SAN?
I recommend you do.
Also, if you backup locally, to disk, how long does it take?
<msnews.microsoft.com> wrote in message
news:u%23CF2Z$FIHA.5980@.TK2MSFTNGP04.phx.gbl...
> I am backing up my SQL Server databases to VDM (SAN) but all my backups
> are taking very long some times 12 hours and always failing with the
> following error.
> failed: 64(The specified network name is no longer available.) [SQLSTATE
> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
> Any help will be appreciated.
> Thanks!
>|||What is the best way to do that ?
"Jay" <spam@.nospam.org> wrote in message
news:%23AeAq2EGIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Are you watching the network traffic and CPU on both your server and the
> SAN?
> I recommend you do.
> Also, if you backup locally, to disk, how long does it take?
>
> <msnews.microsoft.com> wrote in message
> news:u%23CF2Z$FIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> I am backing up my SQL Server databases to VDM (SAN) but all my backups
>> are taking very long some times 12 hours and always failing with the
>> following error.
>> failed: 64(The specified network name is no longer available.) [SQLSTATE
>> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
>> Any help will be appreciated.
>> Thanks!
>|||I am not a SAN or network specialist, although I work with them, so I cannot
tell you exactly what counters to watch. But you should be able to estimate
the throughput that you should get and compare it to what is actually
happening.
For example, if you expect to backup 150 GB an hour, but the 150 GB backup
fails after 12 hours, then something is obviously wrong. Jay asked 'How
long does the backup run to local disk?' Like my questions, it was an
effort to find out how much your backup landscape is differing from what you
would expect.
RLF
<omranu@.Gmail.com> wrote in message
news:OBjXImYGIHA.5544@.TK2MSFTNGP02.phx.gbl...
> What is the best way to do that ?
>
> "Jay" <spam@.nospam.org> wrote in message
> news:%23AeAq2EGIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> Are you watching the network traffic and CPU on both your server and the
>> SAN?
>> I recommend you do.
>> Also, if you backup locally, to disk, how long does it take?
>>
>> <msnews.microsoft.com> wrote in message
>> news:u%23CF2Z$FIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> I am backing up my SQL Server databases to VDM (SAN) but all my backups
>> are taking very long some times 12 hours and always failing with the
>> following error.
>> failed: 64(The specified network name is no longer available.) [SQLSTATE
>> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
>> Any help will be appreciated.
>> Thanks!
>>
>|||Are you asking how to monitor the network traffic, or how to backup to disk?
The simplest way to monitor network traffic is to watch the Network tab of
the Task Manager. I was, however, refering to the Performance Monitor
available under "Control Panel->Administrative Tools"..
The simplest way to backup to disk is a right-click on the database name in
EM, of MS.
<omranu@.Gmail.com> wrote in message
news:OBjXImYGIHA.5544@.TK2MSFTNGP02.phx.gbl...
> What is the best way to do that ?
>
> "Jay" <spam@.nospam.org> wrote in message
> news:%23AeAq2EGIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> Are you watching the network traffic and CPU on both your server and the
>> SAN?
>> I recommend you do.
>> Also, if you backup locally, to disk, how long does it take?
>>
>> <msnews.microsoft.com> wrote in message
>> news:u%23CF2Z$FIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> I am backing up my SQL Server databases to VDM (SAN) but all my backups
>> are taking very long some times 12 hours and always failing with the
>> following error.
>> failed: 64(The specified network name is no longer available.) [SQLSTATE
>> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
>> Any help will be appreciated.
>> Thanks!
>>
>|||Did you ever find the root cause of this? I am struggling with the same
issue. Windows 2003 x64 / SQL 2005 with SAN backend databases. DB access is
very fast on the attached SAN drives however the SQL backups are way, way
slow. As soon as the job leaves the fiber fabric and moves through the IP
stack and out to the VDM the jobs drag. This is a gig connected box and I am
seeing rates between 5-12MB/s, which is horrible. Staying with in the fiber
frabric is great, but moving out into the IP cloud is horrible. I have tried
all sorts of stuff yet nothing is showing me the problem. The VDM links to a
DataMover that is barely under load; tons of free memory, tons of CPU head
room. I have confirmed with the NE that the edge switches are not under
load, there are no errors on the port, and that the pipes feeding the VDM are
way, way underutilized: 2GB pipe with no more than 40MB/s throughput when the
job is running.
Is there any known issue with 2003 x32 or x64 SP2, SQL2005 and data movement
through a NAS VDM?
~ Jeffrey
"msnews.microsoft.com" wrote:
> I am backing up my SQL Server databases to VDM (SAN) but all my backups are
> taking very long some times 12 hours and always failing with the following
> error.
> failed: 64(The specified network name is no longer available.) [SQLSTATE
> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
> Any help will be appreciated.
> Thanks!
>
>
taking very long some times 12 hours and always failing with the following
error.
failed: 64(The specified network name is no longer available.) [SQLSTATE
42000] (Error 3202) A nonrecoverable I/O error occurred on file
Any help will be appreciated.
Thanks!Get your network and SAN people involved, because the problem is something
that they will have to solve. (If you are also the network and SAN person,
the problem is probably not in your SQL Server.)
This is most likely a bottleneck in either the network layers or the SAN
disk or disk controller throughput. I suspect that at the lower levels of
network and/or disk I/O there are a lot of retries happening, until finally
one of them just cannot recover in time. Then your backup fails because it
can no longer see that the file to which it was backing up.
How much data are you backing up? How big is the largest database? How
long would you expect it to take? You are probably having this problem on
your biggest databases, which put the greatest backup load on your
landscape, and the little databases are having no problems.
RLF
<msnews.microsoft.com> wrote in message
news:u%23CF2Z$FIHA.5980@.TK2MSFTNGP04.phx.gbl...
> I am backing up my SQL Server databases to VDM (SAN) but all my backups
> are taking very long some times 12 hours and always failing with the
> following error.
> failed: 64(The specified network name is no longer available.) [SQLSTATE
> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
> Any help will be appreciated.
> Thanks!
>|||Are you watching the network traffic and CPU on both your server and the
SAN?
I recommend you do.
Also, if you backup locally, to disk, how long does it take?
<msnews.microsoft.com> wrote in message
news:u%23CF2Z$FIHA.5980@.TK2MSFTNGP04.phx.gbl...
> I am backing up my SQL Server databases to VDM (SAN) but all my backups
> are taking very long some times 12 hours and always failing with the
> following error.
> failed: 64(The specified network name is no longer available.) [SQLSTATE
> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
> Any help will be appreciated.
> Thanks!
>|||What is the best way to do that ?
"Jay" <spam@.nospam.org> wrote in message
news:%23AeAq2EGIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Are you watching the network traffic and CPU on both your server and the
> SAN?
> I recommend you do.
> Also, if you backup locally, to disk, how long does it take?
>
> <msnews.microsoft.com> wrote in message
> news:u%23CF2Z$FIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> I am backing up my SQL Server databases to VDM (SAN) but all my backups
>> are taking very long some times 12 hours and always failing with the
>> following error.
>> failed: 64(The specified network name is no longer available.) [SQLSTATE
>> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
>> Any help will be appreciated.
>> Thanks!
>|||I am not a SAN or network specialist, although I work with them, so I cannot
tell you exactly what counters to watch. But you should be able to estimate
the throughput that you should get and compare it to what is actually
happening.
For example, if you expect to backup 150 GB an hour, but the 150 GB backup
fails after 12 hours, then something is obviously wrong. Jay asked 'How
long does the backup run to local disk?' Like my questions, it was an
effort to find out how much your backup landscape is differing from what you
would expect.
RLF
<omranu@.Gmail.com> wrote in message
news:OBjXImYGIHA.5544@.TK2MSFTNGP02.phx.gbl...
> What is the best way to do that ?
>
> "Jay" <spam@.nospam.org> wrote in message
> news:%23AeAq2EGIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> Are you watching the network traffic and CPU on both your server and the
>> SAN?
>> I recommend you do.
>> Also, if you backup locally, to disk, how long does it take?
>>
>> <msnews.microsoft.com> wrote in message
>> news:u%23CF2Z$FIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> I am backing up my SQL Server databases to VDM (SAN) but all my backups
>> are taking very long some times 12 hours and always failing with the
>> following error.
>> failed: 64(The specified network name is no longer available.) [SQLSTATE
>> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
>> Any help will be appreciated.
>> Thanks!
>>
>|||Are you asking how to monitor the network traffic, or how to backup to disk?
The simplest way to monitor network traffic is to watch the Network tab of
the Task Manager. I was, however, refering to the Performance Monitor
available under "Control Panel->Administrative Tools"..
The simplest way to backup to disk is a right-click on the database name in
EM, of MS.
<omranu@.Gmail.com> wrote in message
news:OBjXImYGIHA.5544@.TK2MSFTNGP02.phx.gbl...
> What is the best way to do that ?
>
> "Jay" <spam@.nospam.org> wrote in message
> news:%23AeAq2EGIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> Are you watching the network traffic and CPU on both your server and the
>> SAN?
>> I recommend you do.
>> Also, if you backup locally, to disk, how long does it take?
>>
>> <msnews.microsoft.com> wrote in message
>> news:u%23CF2Z$FIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> I am backing up my SQL Server databases to VDM (SAN) but all my backups
>> are taking very long some times 12 hours and always failing with the
>> following error.
>> failed: 64(The specified network name is no longer available.) [SQLSTATE
>> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
>> Any help will be appreciated.
>> Thanks!
>>
>|||Did you ever find the root cause of this? I am struggling with the same
issue. Windows 2003 x64 / SQL 2005 with SAN backend databases. DB access is
very fast on the attached SAN drives however the SQL backups are way, way
slow. As soon as the job leaves the fiber fabric and moves through the IP
stack and out to the VDM the jobs drag. This is a gig connected box and I am
seeing rates between 5-12MB/s, which is horrible. Staying with in the fiber
frabric is great, but moving out into the IP cloud is horrible. I have tried
all sorts of stuff yet nothing is showing me the problem. The VDM links to a
DataMover that is barely under load; tons of free memory, tons of CPU head
room. I have confirmed with the NE that the edge switches are not under
load, there are no errors on the port, and that the pipes feeding the VDM are
way, way underutilized: 2GB pipe with no more than 40MB/s throughput when the
job is running.
Is there any known issue with 2003 x32 or x64 SP2, SQL2005 and data movement
through a NAS VDM?
~ Jeffrey
"msnews.microsoft.com" wrote:
> I am backing up my SQL Server databases to VDM (SAN) but all my backups are
> taking very long some times 12 hours and always failing with the following
> error.
> failed: 64(The specified network name is no longer available.) [SQLSTATE
> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
> Any help will be appreciated.
> Thanks!
>
>
Backup job permissions...
What roles/permissions can you give a login in SQL Server
to allow him/her to see the Database Maintenance Wizard
and Backup Jobs in order to check the status?
Thanks,
ThomasHi Thomas,
According to my experience, if the login account that is used to open
Enterprise Manager is not a member of sysadmin server role, he/she cannot
see the following icons under the Management folder.
-Current Activity
-Database Maintenance Plans
-SQL Server Logs
So you may give the login sysadmin permission.
This posting is provided "AS IS" with no warranties, and confers no rights.
Sincerely,
William Wang
Microsoft Partner Online Support
| Content-Class: urn:content-classes:message
| From: "Thomas.LeBlanc" <Thomas.LeBlanc@.NoSpam.Com>
| Sender: "Thomas.LeBlanc" <Thomas.LeBlanc@.NoSpam.Com>
| Subject: Backup job permissions...
| Date: Tue, 29 Jul 2003 14:02:50 -0700
| Lines: 6
| Message-ID: <034101c35614$c51813d0$a501280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNWFMUYCEK3zv1OTuiKQYXOp8kbIQ==| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:298439
| NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| What roles/permissions can you give a login in SQL Server
| to allow him/her to see the Database Maintenance Wizard
| and Backup Jobs in order to check the status?
|
| Thanks,
| Thomas
||||There is a builtin backup_admin role, this may have some
of what you are looking for.
"Thomas.LeBlanc" <Thomas.LeBlanc@.NoSpam.Com> wrote in message
news:034101c35614$c51813d0$a501280a@.phx.gbl...
> What roles/permissions can you give a login in SQL Server
> to allow him/her to see the Database Maintenance Wizard
> and Backup Jobs in order to check the status?
> Thanks,
> Thomas
to allow him/her to see the Database Maintenance Wizard
and Backup Jobs in order to check the status?
Thanks,
ThomasHi Thomas,
According to my experience, if the login account that is used to open
Enterprise Manager is not a member of sysadmin server role, he/she cannot
see the following icons under the Management folder.
-Current Activity
-Database Maintenance Plans
-SQL Server Logs
So you may give the login sysadmin permission.
This posting is provided "AS IS" with no warranties, and confers no rights.
Sincerely,
William Wang
Microsoft Partner Online Support
| Content-Class: urn:content-classes:message
| From: "Thomas.LeBlanc" <Thomas.LeBlanc@.NoSpam.Com>
| Sender: "Thomas.LeBlanc" <Thomas.LeBlanc@.NoSpam.Com>
| Subject: Backup job permissions...
| Date: Tue, 29 Jul 2003 14:02:50 -0700
| Lines: 6
| Message-ID: <034101c35614$c51813d0$a501280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNWFMUYCEK3zv1OTuiKQYXOp8kbIQ==| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:298439
| NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| What roles/permissions can you give a login in SQL Server
| to allow him/her to see the Database Maintenance Wizard
| and Backup Jobs in order to check the status?
|
| Thanks,
| Thomas
||||There is a builtin backup_admin role, this may have some
of what you are looking for.
"Thomas.LeBlanc" <Thomas.LeBlanc@.NoSpam.Com> wrote in message
news:034101c35614$c51813d0$a501280a@.phx.gbl...
> What roles/permissions can you give a login in SQL Server
> to allow him/her to see the Database Maintenance Wizard
> and Backup Jobs in order to check the status?
> Thanks,
> Thomas
backup Job
I have created two jobs..
First Job... take the full backup daily at 12:00 AM
Second Job...take the log backup every 1 hour till 11:PM
The question is that if I include log shrinking command in the first job
like
First Job :-
Step 1:-
Declare @.SQLStatement VARCHAR(1000)
SET @.SQLStatement = 'Backup Database Health to Disk =
''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()
,110)+'.BAK'' with init'
EXEC (@.SQLStatement)
Step 2:-
backup log Health with truncate_only
dbcc shrinkfile('Health_log',truncateonly)
Is Step 2 would impact on the second job like log backup would be take place
after every 1 hour... is that okie or I have to change my strategy...
I want to shrink my log file daily....
Please guide me..
Hi,
Why do you want to shrik the file every time? If you take a transaction log
backup the LDF file will never grow. Incase if you find the file is growing
then
increase the log backup frequency to 30 minutes rather than 1 hour.
Doing the transaction log backup will clear the transaction log file
automatically. This will ensure that the LDF file will not grow.
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:er9RPR7WFHA.4032@.tk2msftngp13.phx.gbl...
>I have created two jobs..
> First Job... take the full backup daily at 12:00 AM
> Second Job...take the log backup every 1 hour till 11:PM
> The question is that if I include log shrinking command in the first job
> like
> First Job :-
> Step 1:-
> Declare @.SQLStatement VARCHAR(1000)
> SET @.SQLStatement = 'Backup Database Health to Disk =
> ''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()
> ,110)+'.BAK'' with init'
> EXEC (@.SQLStatement)
> Step 2:-
> backup log Health with truncate_only
> dbcc shrinkfile('Health_log',truncateonly)
> Is Step 2 would impact on the second job like log backup would be take
> place
> after every 1 hour... is that okie or I have to change my strategy...
> I want to shrink my log file daily....
> Please guide me..
>
>
|||Hari, I am agree with you with but when I check my log file size after 2
days through this sp_helpdb command. It shows 20 MB but when I shrink it
then it comes at 1 MB.
Any idea ?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY6q3O8WFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Why do you want to shrik the file every time? If you take a transaction
log
> backup the LDF file will never grow. Incase if you find the file is
growing[vbcol=seagreen]
> then
> increase the log backup frequency to 30 minutes rather than 1 hour.
> Doing the transaction log backup will clear the transaction log file
> automatically. This will ensure that the LDF file will not grow.
> Thanks
> Hari
> SQL Server MVP
> "Joh" <joh@.mailcity.com> wrote in message
> news:er9RPR7WFHA.4032@.tk2msftngp13.phx.gbl...
''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()
>
|||Hi Joh,
Even if you do a transaction log backup there are chances to expand the LDF
file. This can happend due to a batch operation. 20 MB is just a
small growth. Even if you do transaction log backup the grown physical file
will not be shrinked automatically but the space used inside the file will
be cleared
and can be used for new transactions.
One more thing is do not shrink the LDF to 1MB. Always keep 100 MB minimum
size. Otherwise the LDF FILE will grow on each transaction and will take
additional overhead for file growth.
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:O1wr$p8WFHA.3876@.tk2msftngp13.phx.gbl...
> Hari, I am agree with you with but when I check my log file size after 2
> days through this sp_helpdb command. It shows 20 MB but when I shrink it
> then it comes at 1 MB.
> Any idea ?
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eY6q3O8WFHA.3188@.TK2MSFTNGP09.phx.gbl...
> log
> growing
> ''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()
>
|||My DB size is 4 GB
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7$ISb#WFHA.3840@.tk2msftngp13.phx.gbl...
> Hi Joh,
> Even if you do a transaction log backup there are chances to expand the
LDF
> file. This can happend due to a batch operation. 20 MB is just a
> small growth. Even if you do transaction log backup the grown physical
file[vbcol=seagreen]
> will not be shrinked automatically but the space used inside the file will
> be cleared
> and can be used for new transactions.
> One more thing is do not shrink the LDF to 1MB. Always keep 100 MB minimum
> size. Otherwise the LDF FILE will grow on each transaction and will take
> additional overhead for file growth.
> Thanks
> Hari
> SQL Server MVP
>
> "Joh" <joh@.mailcity.com> wrote in message
> news:O1wr$p8WFHA.3876@.tk2msftngp13.phx.gbl...
''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()[vbcol=seagreen]
take[vbcol=seagreen]
strategy...
>
|||I am talking about your LDF file. Just ensure that your database files
should not grow contineously.
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:O4GO1l%23WFHA.1796@.TK2MSFTNGP15.phx.gbl...
> My DB size is 4 GB
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O7$ISb#WFHA.3840@.tk2msftngp13.phx.gbl...
> LDF
> file
> ''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()
> take
> strategy...
>
First Job... take the full backup daily at 12:00 AM
Second Job...take the log backup every 1 hour till 11:PM
The question is that if I include log shrinking command in the first job
like
First Job :-
Step 1:-
Declare @.SQLStatement VARCHAR(1000)
SET @.SQLStatement = 'Backup Database Health to Disk =
''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()
,110)+'.BAK'' with init'
EXEC (@.SQLStatement)
Step 2:-
backup log Health with truncate_only
dbcc shrinkfile('Health_log',truncateonly)
Is Step 2 would impact on the second job like log backup would be take place
after every 1 hour... is that okie or I have to change my strategy...
I want to shrink my log file daily....
Please guide me..
Hi,
Why do you want to shrik the file every time? If you take a transaction log
backup the LDF file will never grow. Incase if you find the file is growing
then
increase the log backup frequency to 30 minutes rather than 1 hour.
Doing the transaction log backup will clear the transaction log file
automatically. This will ensure that the LDF file will not grow.
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:er9RPR7WFHA.4032@.tk2msftngp13.phx.gbl...
>I have created two jobs..
> First Job... take the full backup daily at 12:00 AM
> Second Job...take the log backup every 1 hour till 11:PM
> The question is that if I include log shrinking command in the first job
> like
> First Job :-
> Step 1:-
> Declare @.SQLStatement VARCHAR(1000)
> SET @.SQLStatement = 'Backup Database Health to Disk =
> ''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()
> ,110)+'.BAK'' with init'
> EXEC (@.SQLStatement)
> Step 2:-
> backup log Health with truncate_only
> dbcc shrinkfile('Health_log',truncateonly)
> Is Step 2 would impact on the second job like log backup would be take
> place
> after every 1 hour... is that okie or I have to change my strategy...
> I want to shrink my log file daily....
> Please guide me..
>
>
|||Hari, I am agree with you with but when I check my log file size after 2
days through this sp_helpdb command. It shows 20 MB but when I shrink it
then it comes at 1 MB.
Any idea ?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY6q3O8WFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Why do you want to shrik the file every time? If you take a transaction
log
> backup the LDF file will never grow. Incase if you find the file is
growing[vbcol=seagreen]
> then
> increase the log backup frequency to 30 minutes rather than 1 hour.
> Doing the transaction log backup will clear the transaction log file
> automatically. This will ensure that the LDF file will not grow.
> Thanks
> Hari
> SQL Server MVP
> "Joh" <joh@.mailcity.com> wrote in message
> news:er9RPR7WFHA.4032@.tk2msftngp13.phx.gbl...
''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()
>
|||Hi Joh,
Even if you do a transaction log backup there are chances to expand the LDF
file. This can happend due to a batch operation. 20 MB is just a
small growth. Even if you do transaction log backup the grown physical file
will not be shrinked automatically but the space used inside the file will
be cleared
and can be used for new transactions.
One more thing is do not shrink the LDF to 1MB. Always keep 100 MB minimum
size. Otherwise the LDF FILE will grow on each transaction and will take
additional overhead for file growth.
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:O1wr$p8WFHA.3876@.tk2msftngp13.phx.gbl...
> Hari, I am agree with you with but when I check my log file size after 2
> days through this sp_helpdb command. It shows 20 MB but when I shrink it
> then it comes at 1 MB.
> Any idea ?
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eY6q3O8WFHA.3188@.TK2MSFTNGP09.phx.gbl...
> log
> growing
> ''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()
>
|||My DB size is 4 GB
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7$ISb#WFHA.3840@.tk2msftngp13.phx.gbl...
> Hi Joh,
> Even if you do a transaction log backup there are chances to expand the
LDF
> file. This can happend due to a batch operation. 20 MB is just a
> small growth. Even if you do transaction log backup the grown physical
file[vbcol=seagreen]
> will not be shrinked automatically but the space used inside the file will
> be cleared
> and can be used for new transactions.
> One more thing is do not shrink the LDF to 1MB. Always keep 100 MB minimum
> size. Otherwise the LDF FILE will grow on each transaction and will take
> additional overhead for file growth.
> Thanks
> Hari
> SQL Server MVP
>
> "Joh" <joh@.mailcity.com> wrote in message
> news:O1wr$p8WFHA.3876@.tk2msftngp13.phx.gbl...
''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()[vbcol=seagreen]
take[vbcol=seagreen]
strategy...
>
|||I am talking about your LDF file. Just ensure that your database files
should not grow contineously.
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:O4GO1l%23WFHA.1796@.TK2MSFTNGP15.phx.gbl...
> My DB size is 4 GB
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O7$ISb#WFHA.3840@.tk2msftngp13.phx.gbl...
> LDF
> file
> ''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()
> take
> strategy...
>
Subscribe to:
Posts (Atom)