After completing some intensive DB maintenance I would like to shrink a 400G
B
database with 200GB free space. I am looking for a ballpark figure for how
long something like that would take on a standard 2CPU 8MB RAM server. If
anyone else has done something similar how long has it taken? I understand
that shrinkfile depends on a lot of factors - I am mainly looking for an ide
a
- will it take a day? a weekend? a week?
ThanksBased on my experience it will take couple of tens minutes up to couple of
hours,
depends on hardware, current utilization, etc.
Regards.
"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 40
0GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an i
dea
> - will it take a day? a weekend? a week?
> Thanks|||Hi,
My 8 CPU , 8GB RAM machine took me 15 minutes to shrink from 600 GB 290 GB.
But I recommend you to shrink in
pieces of 5000 MB each.
Thanks
Hari
SQLQ Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||many people would not recommend shrinking at all unless you have a very good
reason to do so.
if you're running out of diskspace, consider buying additional disks if you
can as opposed to shrinking the DB.
Greg Jackson
PDX, Oregon|||You said yourself it depends on a lot of factors so it is very hard to give
you an accurate answer, even a ballpark. You stated that you just did some
extensive maintenance so I assume this was mainly reindexing. Reindexing
will defragment your indexes but shrinking the file will just fragment them
again. So you will undo most of what you spent all that time working on.
The DB needs plenty of free space to operate normally. While 200GB of free
space on a 200GB db may be more than you actually need it doesn't hurt to
have extra free space. Have a look at this:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 40
0GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken?
I'll chime in by saying that it's really variable. For example, my 4CPU 4GB
RAM server recently took a day to shrink an 80GB file down to 50GB.
One gotcha: it is possible for a SHRINKFILE to deadlock on a SELECT. In
that case,
the SHRINKFILE will be terminated in favor of the SELECT, and you'll have to
start
the SHRINKFILE again. So I'd recommend shrinking when the database is
relatively unused.
-- J
Showing posts with label maintenance. Show all posts
Showing posts with label maintenance. Show all posts
Tuesday, March 20, 2012
ballpark time for shrinkfile
After completing some intensive DB maintenance I would like to shrink a 400GB
database with 200GB free space. I am looking for a ballpark figure for how
long something like that would take on a standard 2CPU 8MB RAM server. If
anyone else has done something similar how long has it taken? I understand
that shrinkfile depends on a lot of factors - I am mainly looking for an idea
- will it take a day? a weekend? a week?
ThanksBased on my experience it will take couple of tens minutes up to couple of
hours,
depends on hardware, current utilization, etc.
Regards.
"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an idea
> - will it take a day? a weekend? a week?
> Thanks|||Hi,
My 8 CPU , 8GB RAM machine took me 15 minutes to shrink from 600 GB 290 GB.
But I recommend you to shrink in
pieces of 5000 MB each.
Thanks
Hari
SQLQ Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||many people would not recommend shrinking at all unless you have a very good
reason to do so.
if you're running out of diskspace, consider buying additional disks if you
can as opposed to shrinking the DB.
Greg Jackson
PDX, Oregon|||You said yourself it depends on a lot of factors so it is very hard to give
you an accurate answer, even a ballpark. You stated that you just did some
extensive maintenance so I assume this was mainly reindexing. Reindexing
will defragment your indexes but shrinking the file will just fragment them
again. So you will undo most of what you spent all that time working on.
The DB needs plenty of free space to operate normally. While 200GB of free
space on a 200GB db may be more than you actually need it doesn't hurt to
have extra free space. Have a look at this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken?
I'll chime in by saying that it's really variable. For example, my 4CPU 4GB
RAM server recently took a day to shrink an 80GB file down to 50GB.
One gotcha: it is possible for a SHRINKFILE to deadlock on a SELECT. In
that case,
the SHRINKFILE will be terminated in favor of the SELECT, and you'll have to
start
the SHRINKFILE again. So I'd recommend shrinking when the database is
relatively unused.
-- J
database with 200GB free space. I am looking for a ballpark figure for how
long something like that would take on a standard 2CPU 8MB RAM server. If
anyone else has done something similar how long has it taken? I understand
that shrinkfile depends on a lot of factors - I am mainly looking for an idea
- will it take a day? a weekend? a week?
ThanksBased on my experience it will take couple of tens minutes up to couple of
hours,
depends on hardware, current utilization, etc.
Regards.
"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an idea
> - will it take a day? a weekend? a week?
> Thanks|||Hi,
My 8 CPU , 8GB RAM machine took me 15 minutes to shrink from 600 GB 290 GB.
But I recommend you to shrink in
pieces of 5000 MB each.
Thanks
Hari
SQLQ Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||many people would not recommend shrinking at all unless you have a very good
reason to do so.
if you're running out of diskspace, consider buying additional disks if you
can as opposed to shrinking the DB.
Greg Jackson
PDX, Oregon|||You said yourself it depends on a lot of factors so it is very hard to give
you an accurate answer, even a ballpark. You stated that you just did some
extensive maintenance so I assume this was mainly reindexing. Reindexing
will defragment your indexes but shrinking the file will just fragment them
again. So you will undo most of what you spent all that time working on.
The DB needs plenty of free space to operate normally. While 200GB of free
space on a 200GB db may be more than you actually need it doesn't hurt to
have extra free space. Have a look at this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken?
I'll chime in by saying that it's really variable. For example, my 4CPU 4GB
RAM server recently took a day to shrink an 80GB file down to 50GB.
One gotcha: it is possible for a SHRINKFILE to deadlock on a SELECT. In
that case,
the SHRINKFILE will be terminated in favor of the SELECT, and you'll have to
start
the SHRINKFILE again. So I'd recommend shrinking when the database is
relatively unused.
-- J
ballpark time for shrinkfile
After completing some intensive DB maintenance I would like to shrink a 400GB
database with 200GB free space. I am looking for a ballpark figure for how
long something like that would take on a standard 2CPU 8MB RAM server. If
anyone else has done something similar how long has it taken? I understand
that shrinkfile depends on a lot of factors - I am mainly looking for an idea
- will it take a day? a weekend? a week?
Thanks
Based on my experience it will take couple of tens minutes up to couple of
hours,
depends on hardware, current utilization, etc.
Regards.
"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an idea
> - will it take a day? a weekend? a week?
> Thanks
|||Hi,
My 8 CPU , 8GB RAM machine took me 15 minutes to shrink from 600 GB 290 GB.
But I recommend you to shrink in
pieces of 5000 MB each.
Thanks
Hari
SQLQ Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks
|||many people would not recommend shrinking at all unless you have a very good
reason to do so.
if you're running out of diskspace, consider buying additional disks if you
can as opposed to shrinking the DB.
Greg Jackson
PDX, Oregon
|||You said yourself it depends on a lot of factors so it is very hard to give
you an accurate answer, even a ballpark. You stated that you just did some
extensive maintenance so I assume this was mainly reindexing. Reindexing
will defragment your indexes but shrinking the file will just fragment them
again. So you will undo most of what you spent all that time working on.
The DB needs plenty of free space to operate normally. While 200GB of free
space on a 200GB db may be more than you actually need it doesn't hurt to
have extra free space. Have a look at this:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks
|||"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken?
I'll chime in by saying that it's really variable. For example, my 4CPU 4GB
RAM server recently took a day to shrink an 80GB file down to 50GB.
One gotcha: it is possible for a SHRINKFILE to deadlock on a SELECT. In
that case,
the SHRINKFILE will be terminated in favor of the SELECT, and you'll have to
start
the SHRINKFILE again. So I'd recommend shrinking when the database is
relatively unused.
-- J
database with 200GB free space. I am looking for a ballpark figure for how
long something like that would take on a standard 2CPU 8MB RAM server. If
anyone else has done something similar how long has it taken? I understand
that shrinkfile depends on a lot of factors - I am mainly looking for an idea
- will it take a day? a weekend? a week?
Thanks
Based on my experience it will take couple of tens minutes up to couple of
hours,
depends on hardware, current utilization, etc.
Regards.
"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an idea
> - will it take a day? a weekend? a week?
> Thanks
|||Hi,
My 8 CPU , 8GB RAM machine took me 15 minutes to shrink from 600 GB 290 GB.
But I recommend you to shrink in
pieces of 5000 MB each.
Thanks
Hari
SQLQ Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks
|||many people would not recommend shrinking at all unless you have a very good
reason to do so.
if you're running out of diskspace, consider buying additional disks if you
can as opposed to shrinking the DB.
Greg Jackson
PDX, Oregon
|||You said yourself it depends on a lot of factors so it is very hard to give
you an accurate answer, even a ballpark. You stated that you just did some
extensive maintenance so I assume this was mainly reindexing. Reindexing
will defragment your indexes but shrinking the file will just fragment them
again. So you will undo most of what you spent all that time working on.
The DB needs plenty of free space to operate normally. While 200GB of free
space on a 200GB db may be more than you actually need it doesn't hurt to
have extra free space. Have a look at this:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks
|||"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken?
I'll chime in by saying that it's really variable. For example, my 4CPU 4GB
RAM server recently took a day to shrink an 80GB file down to 50GB.
One gotcha: it is possible for a SHRINKFILE to deadlock on a SELECT. In
that case,
the SHRINKFILE will be terminated in favor of the SELECT, and you'll have to
start
the SHRINKFILE again. So I'd recommend shrinking when the database is
relatively unused.
-- J
Thursday, March 8, 2012
Backup-to-file SQL2k Question/NT Tape Backups
Dear All,
Hello!
Now that I am running a maintenance backup-to-file from
within SQL2K, should I just be backing up these
backup-to-file to an NT backup job? Or should I also be
backing up the live databases & their transact logs?
Thanks!
CarlosNormal practice would be to backup the backup files from SQL to tape. If you
are taking regular SQL backups, there is no need to backup the actual
database files themselves (which would require stopping SQL anyway as they
are always in use)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:10ef101c3f49b$fb627db0$a001280a@.phx.gbl...
> Dear All,
> Hello!
> Now that I am running a maintenance backup-to-file from
> within SQL2K, should I just be backing up these
> backup-to-file to an NT backup job? Or should I also be
> backing up the live databases & their transact logs?
> Thanks!
> Carlos
>|||You don't have to backup the mdf, ndf and ldf files if you let SQL Server do
database and possibly transaction log backup to disk and then backup those
files to tape.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:10ef101c3f49b$fb627db0$a001280a@.phx.gbl...
> Dear All,
> Hello!
> Now that I am running a maintenance backup-to-file from
> within SQL2K, should I just be backing up these
> backup-to-file to an NT backup job? Or should I also be
> backing up the live databases & their transact logs?
> Thanks!
> Carlos
>
Hello!
Now that I am running a maintenance backup-to-file from
within SQL2K, should I just be backing up these
backup-to-file to an NT backup job? Or should I also be
backing up the live databases & their transact logs?
Thanks!
CarlosNormal practice would be to backup the backup files from SQL to tape. If you
are taking regular SQL backups, there is no need to backup the actual
database files themselves (which would require stopping SQL anyway as they
are always in use)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:10ef101c3f49b$fb627db0$a001280a@.phx.gbl...
> Dear All,
> Hello!
> Now that I am running a maintenance backup-to-file from
> within SQL2K, should I just be backing up these
> backup-to-file to an NT backup job? Or should I also be
> backing up the live databases & their transact logs?
> Thanks!
> Carlos
>|||You don't have to backup the mdf, ndf and ldf files if you let SQL Server do
database and possibly transaction log backup to disk and then backup those
files to tape.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:10ef101c3f49b$fb627db0$a001280a@.phx.gbl...
> Dear All,
> Hello!
> Now that I am running a maintenance backup-to-file from
> within SQL2K, should I just be backing up these
> backup-to-file to an NT backup job? Or should I also be
> backing up the live databases & their transact logs?
> Thanks!
> Carlos
>
Backup-to-file SQL2k Question/NT Tape Backups
Dear All,
Hello!
Now that I am running a maintenance backup-to-file from
within SQL2K, should I just be backing up these
backup-to-file to an NT backup job? Or should I also be
backing up the live databases & their transact logs?
Thanks!
CarlosNormal practice would be to backup the backup files from SQL to tape. If you
are taking regular SQL backups, there is no need to backup the actual
database files themselves (which would require stopping SQL anyway as they
are always in use)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:10ef101c3f49b$fb627db0$a001280a@.phx
.gbl...
> Dear All,
> Hello!
> Now that I am running a maintenance backup-to-file from
> within SQL2K, should I just be backing up these
> backup-to-file to an NT backup job? Or should I also be
> backing up the live databases & their transact logs?
> Thanks!
> Carlos
>|||You don't have to backup the mdf, ndf and ldf files if you let SQL Server do
database and possibly transaction log backup to disk and then backup those
files to tape.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:10ef101c3f49b$fb627db0$a001280a@.phx
.gbl...
> Dear All,
> Hello!
> Now that I am running a maintenance backup-to-file from
> within SQL2K, should I just be backing up these
> backup-to-file to an NT backup job? Or should I also be
> backing up the live databases & their transact logs?
> Thanks!
> Carlos
>
Hello!
Now that I am running a maintenance backup-to-file from
within SQL2K, should I just be backing up these
backup-to-file to an NT backup job? Or should I also be
backing up the live databases & their transact logs?
Thanks!
CarlosNormal practice would be to backup the backup files from SQL to tape. If you
are taking regular SQL backups, there is no need to backup the actual
database files themselves (which would require stopping SQL anyway as they
are always in use)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:10ef101c3f49b$fb627db0$a001280a@.phx
.gbl...
> Dear All,
> Hello!
> Now that I am running a maintenance backup-to-file from
> within SQL2K, should I just be backing up these
> backup-to-file to an NT backup job? Or should I also be
> backing up the live databases & their transact logs?
> Thanks!
> Carlos
>|||You don't have to backup the mdf, ndf and ldf files if you let SQL Server do
database and possibly transaction log backup to disk and then backup those
files to tape.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:10ef101c3f49b$fb627db0$a001280a@.phx
.gbl...
> Dear All,
> Hello!
> Now that I am running a maintenance backup-to-file from
> within SQL2K, should I just be backing up these
> backup-to-file to an NT backup job? Or should I also be
> backing up the live databases & their transact logs?
> Thanks!
> Carlos
>
backups working but backup files are not being removed
Hello:
We have a SQL Server 2000 client who has a maintenance plan that does a full
backup of course of its databases. And, each database is using the Full
recovery model.
The backup of the databases itself is working perfectly. But, the backup
files (mdf files) are not being removed. You see, the maintenance plan
specifies that backup files are to be removed every 2 days. That's not
happening?
Why would that be? I mean, why would part of the maintenance plan (the
backing up of the databases) work but another part of the plan (the removal
of the database files) not work?
Thanks!
childofthe1980s
MDF files are actually the real data files. Maintenance plans will remove
only .BAK files pertaining to FULL database or Transaction log.
Thanks
Hari
"childofthe1980s" wrote:
> Hello:
> We have a SQL Server 2000 client who has a maintenance plan that does a full
> backup of course of its databases. And, each database is using the Full
> recovery model.
> The backup of the databases itself is working perfectly. But, the backup
> files (mdf files) are not being removed. You see, the maintenance plan
> specifies that backup files are to be removed every 2 days. That's not
> happening?
> Why would that be? I mean, why would part of the maintenance plan (the
> backing up of the databases) work but another part of the plan (the removal
> of the database files) not work?
> Thanks!
> childofthe1980s
|||Actually, that's what I meant. Sorry for the confusion.
Yes, it is "bak" files I'm concerned about.
Here's an update. I spoke with the client and had him add a group that
contains that SQL Server Agent startup account to have Full Control of the
MSSQL folder so that the root backup folder wold contain Full Control for
this group.
Now, the startup account already had Full Control anyway. But, if the group
that the startup account had "Access is Denied", then the startup account
would be denied permissions. I don't know how to tell if the group was being
denied access. But, that's what I have done so far to eliminate this issue.
Is there anything else?
childofthe1980s
"Hari Prasad" wrote:
[vbcol=seagreen]
> MDF files are actually the real data files. Maintenance plans will remove
> only .BAK files pertaining to FULL database or Transaction log.
> Thanks
> Hari
> "childofthe1980s" wrote:
We have a SQL Server 2000 client who has a maintenance plan that does a full
backup of course of its databases. And, each database is using the Full
recovery model.
The backup of the databases itself is working perfectly. But, the backup
files (mdf files) are not being removed. You see, the maintenance plan
specifies that backup files are to be removed every 2 days. That's not
happening?
Why would that be? I mean, why would part of the maintenance plan (the
backing up of the databases) work but another part of the plan (the removal
of the database files) not work?
Thanks!
childofthe1980s
MDF files are actually the real data files. Maintenance plans will remove
only .BAK files pertaining to FULL database or Transaction log.
Thanks
Hari
"childofthe1980s" wrote:
> Hello:
> We have a SQL Server 2000 client who has a maintenance plan that does a full
> backup of course of its databases. And, each database is using the Full
> recovery model.
> The backup of the databases itself is working perfectly. But, the backup
> files (mdf files) are not being removed. You see, the maintenance plan
> specifies that backup files are to be removed every 2 days. That's not
> happening?
> Why would that be? I mean, why would part of the maintenance plan (the
> backing up of the databases) work but another part of the plan (the removal
> of the database files) not work?
> Thanks!
> childofthe1980s
|||Actually, that's what I meant. Sorry for the confusion.
Yes, it is "bak" files I'm concerned about.
Here's an update. I spoke with the client and had him add a group that
contains that SQL Server Agent startup account to have Full Control of the
MSSQL folder so that the root backup folder wold contain Full Control for
this group.
Now, the startup account already had Full Control anyway. But, if the group
that the startup account had "Access is Denied", then the startup account
would be denied permissions. I don't know how to tell if the group was being
denied access. But, that's what I have done so far to eliminate this issue.
Is there anything else?
childofthe1980s
"Hari Prasad" wrote:
[vbcol=seagreen]
> MDF files are actually the real data files. Maintenance plans will remove
> only .BAK files pertaining to FULL database or Transaction log.
> Thanks
> Hari
> "childofthe1980s" wrote:
backups working but backup files are not being removed
Hello:
We have a SQL Server 2000 client who has a maintenance plan that does a full
backup of course of its databases. And, each database is using the Full
recovery model.
The backup of the databases itself is working perfectly. But, the backup
files (mdf files) are not being removed. You see, the maintenance plan
specifies that backup files are to be removed every 2 days. That's not
happening?
Why would that be? I mean, why would part of the maintenance plan (the
backing up of the databases) work but another part of the plan (the removal
of the database files) not work?
Thanks!
childofthe1980sMDF files are actually the real data files. Maintenance plans will remove
only .BAK files pertaining to FULL database or Transaction log.
Thanks
Hari
"childofthe1980s" wrote:
> Hello:
> We have a SQL Server 2000 client who has a maintenance plan that does a fu
ll
> backup of course of its databases. And, each database is using the Full
> recovery model.
> The backup of the databases itself is working perfectly. But, the backup
> files (mdf files) are not being removed. You see, the maintenance plan
> specifies that backup files are to be removed every 2 days. That's not
> happening?
> Why would that be? I mean, why would part of the maintenance plan (the
> backing up of the databases) work but another part of the plan (the remova
l
> of the database files) not work?
> Thanks!
> childofthe1980s|||Actually, that's what I meant. Sorry for the confusion.
Yes, it is "bak" files I'm concerned about.
Here's an update. I spoke with the client and had him add a group that
contains that SQL Server Agent startup account to have Full Control of the
MSSQL folder so that the root backup folder wold contain Full Control for
this group.
Now, the startup account already had Full Control anyway. But, if the group
that the startup account had "Access is Denied", then the startup account
would be denied permissions. I don't know how to tell if the group was bein
g
denied access. But, that's what I have done so far to eliminate this issue.
Is there anything else?
childofthe1980s
"Hari Prasad" wrote:
[vbcol=seagreen]
> MDF files are actually the real data files. Maintenance plans will remove
> only .BAK files pertaining to FULL database or Transaction log.
> Thanks
> Hari
> "childofthe1980s" wrote:
>
We have a SQL Server 2000 client who has a maintenance plan that does a full
backup of course of its databases. And, each database is using the Full
recovery model.
The backup of the databases itself is working perfectly. But, the backup
files (mdf files) are not being removed. You see, the maintenance plan
specifies that backup files are to be removed every 2 days. That's not
happening?
Why would that be? I mean, why would part of the maintenance plan (the
backing up of the databases) work but another part of the plan (the removal
of the database files) not work?
Thanks!
childofthe1980sMDF files are actually the real data files. Maintenance plans will remove
only .BAK files pertaining to FULL database or Transaction log.
Thanks
Hari
"childofthe1980s" wrote:
> Hello:
> We have a SQL Server 2000 client who has a maintenance plan that does a fu
ll
> backup of course of its databases. And, each database is using the Full
> recovery model.
> The backup of the databases itself is working perfectly. But, the backup
> files (mdf files) are not being removed. You see, the maintenance plan
> specifies that backup files are to be removed every 2 days. That's not
> happening?
> Why would that be? I mean, why would part of the maintenance plan (the
> backing up of the databases) work but another part of the plan (the remova
l
> of the database files) not work?
> Thanks!
> childofthe1980s|||Actually, that's what I meant. Sorry for the confusion.
Yes, it is "bak" files I'm concerned about.
Here's an update. I spoke with the client and had him add a group that
contains that SQL Server Agent startup account to have Full Control of the
MSSQL folder so that the root backup folder wold contain Full Control for
this group.
Now, the startup account already had Full Control anyway. But, if the group
that the startup account had "Access is Denied", then the startup account
would be denied permissions. I don't know how to tell if the group was bein
g
denied access. But, that's what I have done so far to eliminate this issue.
Is there anything else?
childofthe1980s
"Hari Prasad" wrote:
[vbcol=seagreen]
> MDF files are actually the real data files. Maintenance plans will remove
> only .BAK files pertaining to FULL database or Transaction log.
> Thanks
> Hari
> "childofthe1980s" wrote:
>
Backups with Maintenance Plans
Hi
I am new to database administration, and I am considering my options
for backups. We are using SQL Server 2005 Standard.
I have done some reading and have determined the right backup scheme
for us is a weekly complete Simple backup with daily Differential
backups.
I would like to set this up with a Maintenance Plan. How do I do this?
- do I set up two Maintenance Plans, one with the weekly backup and
one with the differentials?
- or do I do this with one Plan?
- or is this not possible with Maintenance Plans, in which case I
should just schedule an agent with SQL scripts?
thanks,
TJHi,
You can create a Maintenance Plan for the weekly full backups and another
one for the daily differential backups.
Also, if your databases are smaller than, let us say, 100 or 200 GB perhaps
you can try daily full backups, depending on your backup window.
Hope this helps,
Ben Nevarez
"TJ" wrote:
> Hi
> I am new to database administration, and I am considering my options
> for backups. We are using SQL Server 2005 Standard.
> I have done some reading and have determined the right backup scheme
> for us is a weekly complete Simple backup with daily Differential
> backups.
> I would like to set this up with a Maintenance Plan. How do I do this?
> - do I set up two Maintenance Plans, one with the weekly backup and
> one with the differentials?
> - or do I do this with one Plan?
> - or is this not possible with Maintenance Plans, in which case I
> should just schedule an agent with SQL scripts?
> thanks,
> TJ
>|||ok, thanks
Yeah, the backups are about 2 Gigs, so I could just do them as daily
full backups.|||Yeah, for only 2 GB do daily full backups including daily backups of your
system databases.
Hope this helps,
Ben Nevarez
"TJ" wrote:
> ok, thanks
> Yeah, the backups are about 2 Gigs, so I could just do them as daily
> full backups.
>|||... and, of course, carefully consider whether you also want to do more frequent transaction log
backups. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:CE066399-68A6-469E-BBCC-2792BF430D85@.microsoft.com...
> Yeah, for only 2 GB do daily full backups including daily backups of your
> system databases.
> Hope this helps,
> Ben Nevarez
>
>
> "TJ" wrote:
>> ok, thanks
>> Yeah, the backups are about 2 Gigs, so I could just do them as daily
>> full backups.
I am new to database administration, and I am considering my options
for backups. We are using SQL Server 2005 Standard.
I have done some reading and have determined the right backup scheme
for us is a weekly complete Simple backup with daily Differential
backups.
I would like to set this up with a Maintenance Plan. How do I do this?
- do I set up two Maintenance Plans, one with the weekly backup and
one with the differentials?
- or do I do this with one Plan?
- or is this not possible with Maintenance Plans, in which case I
should just schedule an agent with SQL scripts?
thanks,
TJHi,
You can create a Maintenance Plan for the weekly full backups and another
one for the daily differential backups.
Also, if your databases are smaller than, let us say, 100 or 200 GB perhaps
you can try daily full backups, depending on your backup window.
Hope this helps,
Ben Nevarez
"TJ" wrote:
> Hi
> I am new to database administration, and I am considering my options
> for backups. We are using SQL Server 2005 Standard.
> I have done some reading and have determined the right backup scheme
> for us is a weekly complete Simple backup with daily Differential
> backups.
> I would like to set this up with a Maintenance Plan. How do I do this?
> - do I set up two Maintenance Plans, one with the weekly backup and
> one with the differentials?
> - or do I do this with one Plan?
> - or is this not possible with Maintenance Plans, in which case I
> should just schedule an agent with SQL scripts?
> thanks,
> TJ
>|||ok, thanks
Yeah, the backups are about 2 Gigs, so I could just do them as daily
full backups.|||Yeah, for only 2 GB do daily full backups including daily backups of your
system databases.
Hope this helps,
Ben Nevarez
"TJ" wrote:
> ok, thanks
> Yeah, the backups are about 2 Gigs, so I could just do them as daily
> full backups.
>|||... and, of course, carefully consider whether you also want to do more frequent transaction log
backups. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:CE066399-68A6-469E-BBCC-2792BF430D85@.microsoft.com...
> Yeah, for only 2 GB do daily full backups including daily backups of your
> system databases.
> Hope this helps,
> Ben Nevarez
>
>
> "TJ" wrote:
>> ok, thanks
>> Yeah, the backups are about 2 Gigs, so I could just do them as daily
>> full backups.
Backups with Maintenance Plans
Hi
I am new to database administration, and I am considering my options
for backups. We are using SQL Server 2005 Standard.
I have done some reading and have determined the right backup scheme
for us is a weekly complete Simple backup with daily Differential
backups.
I would like to set this up with a Maintenance Plan. How do I do this?
- do I set up two Maintenance Plans, one with the weekly backup and
one with the differentials?
- or do I do this with one Plan?
- or is this not possible with Maintenance Plans, in which case I
should just schedule an agent with SQL scripts?
thanks,
TJ
Hi,
You can create a Maintenance Plan for the weekly full backups and another
one for the daily differential backups.
Also, if your databases are smaller than, let us say, 100 or 200 GB perhaps
you can try daily full backups, depending on your backup window.
Hope this helps,
Ben Nevarez
"TJ" wrote:
> Hi
> I am new to database administration, and I am considering my options
> for backups. We are using SQL Server 2005 Standard.
> I have done some reading and have determined the right backup scheme
> for us is a weekly complete Simple backup with daily Differential
> backups.
> I would like to set this up with a Maintenance Plan. How do I do this?
> - do I set up two Maintenance Plans, one with the weekly backup and
> one with the differentials?
> - or do I do this with one Plan?
> - or is this not possible with Maintenance Plans, in which case I
> should just schedule an agent with SQL scripts?
> thanks,
> TJ
>
|||ok, thanks
Yeah, the backups are about 2 Gigs, so I could just do them as daily
full backups.
|||Yeah, for only 2 GB do daily full backups including daily backups of your
system databases.
Hope this helps,
Ben Nevarez
"TJ" wrote:
> ok, thanks
> Yeah, the backups are about 2 Gigs, so I could just do them as daily
> full backups.
>
I am new to database administration, and I am considering my options
for backups. We are using SQL Server 2005 Standard.
I have done some reading and have determined the right backup scheme
for us is a weekly complete Simple backup with daily Differential
backups.
I would like to set this up with a Maintenance Plan. How do I do this?
- do I set up two Maintenance Plans, one with the weekly backup and
one with the differentials?
- or do I do this with one Plan?
- or is this not possible with Maintenance Plans, in which case I
should just schedule an agent with SQL scripts?
thanks,
TJ
Hi,
You can create a Maintenance Plan for the weekly full backups and another
one for the daily differential backups.
Also, if your databases are smaller than, let us say, 100 or 200 GB perhaps
you can try daily full backups, depending on your backup window.
Hope this helps,
Ben Nevarez
"TJ" wrote:
> Hi
> I am new to database administration, and I am considering my options
> for backups. We are using SQL Server 2005 Standard.
> I have done some reading and have determined the right backup scheme
> for us is a weekly complete Simple backup with daily Differential
> backups.
> I would like to set this up with a Maintenance Plan. How do I do this?
> - do I set up two Maintenance Plans, one with the weekly backup and
> one with the differentials?
> - or do I do this with one Plan?
> - or is this not possible with Maintenance Plans, in which case I
> should just schedule an agent with SQL scripts?
> thanks,
> TJ
>
|||ok, thanks
Yeah, the backups are about 2 Gigs, so I could just do them as daily
full backups.
|||Yeah, for only 2 GB do daily full backups including daily backups of your
system databases.
Hope this helps,
Ben Nevarez
"TJ" wrote:
> ok, thanks
> Yeah, the backups are about 2 Gigs, so I could just do them as daily
> full backups.
>
Backups running concurrently with db maintenance
I have a db maintenance task that I think will take many hours (reindexing a
rather large table with several millian rows). Can I run this concurrently
with the backup (full)?
Further to this, is there a list of tasks that can and can't be run at the
same time as a backup?
You can run the reindexing concurrently with the database backup, but they
will most likely slow each other down.
There is nothing that you can not run concurrently with a backup, in the
sense that the process will fail. In general though it is not really
advisable to schedule other tasks concurrently with a back up, as you don't
gain a lot in the combined processing time in general (if at all).
Jacco Schalkwijk
SQL Server MVP
"Branth" <Branth@.discussions.microsoft.com> wrote in message
news:996B0F0F-F11A-4DE6-9605-14C91F560400@.microsoft.com...
>I have a db maintenance task that I think will take many hours (reindexing
>a
> rather large table with several millian rows). Can I run this
> concurrently
> with the backup (full)?
> Further to this, is there a list of tasks that can and can't be run at the
> same time as a backup?
>
rather large table with several millian rows). Can I run this concurrently
with the backup (full)?
Further to this, is there a list of tasks that can and can't be run at the
same time as a backup?
You can run the reindexing concurrently with the database backup, but they
will most likely slow each other down.
There is nothing that you can not run concurrently with a backup, in the
sense that the process will fail. In general though it is not really
advisable to schedule other tasks concurrently with a back up, as you don't
gain a lot in the combined processing time in general (if at all).
Jacco Schalkwijk
SQL Server MVP
"Branth" <Branth@.discussions.microsoft.com> wrote in message
news:996B0F0F-F11A-4DE6-9605-14C91F560400@.microsoft.com...
>I have a db maintenance task that I think will take many hours (reindexing
>a
> rather large table with several millian rows). Can I run this
> concurrently
> with the backup (full)?
> Further to this, is there a list of tasks that can and can't be run at the
> same time as a backup?
>
Backups running concurrently with db maintenance
I have a db maintenance task that I think will take many hours (reindexing a
rather large table with several millian rows). Can I run this concurrently
with the backup (full)?
Further to this, is there a list of tasks that can and can't be run at the
same time as a backup?You can run the reindexing concurrently with the database backup, but they
will most likely slow each other down.
There is nothing that you can not run concurrently with a backup, in the
sense that the process will fail. In general though it is not really
advisable to schedule other tasks concurrently with a back up, as you don't
gain a lot in the combined processing time in general (if at all).
--
Jacco Schalkwijk
SQL Server MVP
"Branth" <Branth@.discussions.microsoft.com> wrote in message
news:996B0F0F-F11A-4DE6-9605-14C91F560400@.microsoft.com...
>I have a db maintenance task that I think will take many hours (reindexing
>a
> rather large table with several millian rows). Can I run this
> concurrently
> with the backup (full)?
> Further to this, is there a list of tasks that can and can't be run at the
> same time as a backup?
>
rather large table with several millian rows). Can I run this concurrently
with the backup (full)?
Further to this, is there a list of tasks that can and can't be run at the
same time as a backup?You can run the reindexing concurrently with the database backup, but they
will most likely slow each other down.
There is nothing that you can not run concurrently with a backup, in the
sense that the process will fail. In general though it is not really
advisable to schedule other tasks concurrently with a back up, as you don't
gain a lot in the combined processing time in general (if at all).
--
Jacco Schalkwijk
SQL Server MVP
"Branth" <Branth@.discussions.microsoft.com> wrote in message
news:996B0F0F-F11A-4DE6-9605-14C91F560400@.microsoft.com...
>I have a db maintenance task that I think will take many hours (reindexing
>a
> rather large table with several millian rows). Can I run this
> concurrently
> with the backup (full)?
> Further to this, is there a list of tasks that can and can't be run at the
> same time as a backup?
>
Backups running concurrently with db maintenance
I have a db maintenance task that I think will take many hours (reindexing a
rather large table with several millian rows). Can I run this concurrently
with the backup (full)?
Further to this, is there a list of tasks that can and can't be run at the
same time as a backup?You can run the reindexing concurrently with the database backup, but they
will most likely slow each other down.
There is nothing that you can not run concurrently with a backup, in the
sense that the process will fail. In general though it is not really
advisable to schedule other tasks concurrently with a back up, as you don't
gain a lot in the combined processing time in general (if at all).
Jacco Schalkwijk
SQL Server MVP
"Branth" <Branth@.discussions.microsoft.com> wrote in message
news:996B0F0F-F11A-4DE6-9605-14C91F560400@.microsoft.com...
>I have a db maintenance task that I think will take many hours (reindexing
>a
> rather large table with several millian rows). Can I run this
> concurrently
> with the backup (full)?
> Further to this, is there a list of tasks that can and can't be run at the
> same time as a backup?
>
rather large table with several millian rows). Can I run this concurrently
with the backup (full)?
Further to this, is there a list of tasks that can and can't be run at the
same time as a backup?You can run the reindexing concurrently with the database backup, but they
will most likely slow each other down.
There is nothing that you can not run concurrently with a backup, in the
sense that the process will fail. In general though it is not really
advisable to schedule other tasks concurrently with a back up, as you don't
gain a lot in the combined processing time in general (if at all).
Jacco Schalkwijk
SQL Server MVP
"Branth" <Branth@.discussions.microsoft.com> wrote in message
news:996B0F0F-F11A-4DE6-9605-14C91F560400@.microsoft.com...
>I have a db maintenance task that I think will take many hours (reindexing
>a
> rather large table with several millian rows). Can I run this
> concurrently
> with the backup (full)?
> Further to this, is there a list of tasks that can and can't be run at the
> same time as a backup?
>
Backups of tran log not working
I have my maintenance plan set up to backup to disk each day, including the
tran log.
I am getting an error on the Tran log backup, however.
It doesn't tell me why it failed, just that it failed.
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
The help center is no help.
The other 3 parts of the maintenance plan seem to go fine.
Also, there is no log in the Sql Logs, but there is in the Event log (where
I got this). There is plenty of disk space, so that is not the problem.
Am I missing something here?
Thanks,
Tom
Specify a report file for the maint plan and check that for specific error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I have my maintenance plan set up to backup to disk each day, including the tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance
> Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status: Failed - Invoked on: 2005-11-09 09:24:24 -
> Message: The job failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where I got this). There is
> plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edxoSaV5FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Specify a report file for the maint plan and check that for specific error
> messages.
I did that and got to following:
************************************************** ************************************************** *
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VENUS' as 'FTSOLUTIONS0\servicesql' (trusted)
Starting maintenance plan 'All Databases Backup' on 11/9/2005 10:33:23 AM
Backup can not be performed on database 'FTS'. This sub task is ignored.
Backup can not be performed on database 'master'. This sub task is ignored.
[3] Database model: Transaction Log Backup...
Destination: [d:\Microsoft SQL
Server\MSSQL\BACKUP\model_tlog_200511091033.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
End of maintenance plan 'All Databases Backup' on 11/9/2005 10:33:24 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
************************************************** ************************************************** *
So it works for some and not others.
And why can't it backup master or msdb - they have log files
Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>
|||I think that master & msdb are simple recovery model, hence cannot have
tran. log backups. Just remove this portion of your maintenence plan.
|||Ok.
But why did FTS not backup?
Tom
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131562250.817157.160290@.g43g2000cwa.googlegr oups.com...
>I think that master & msdb are simple recovery model, hence cannot have
> tran. log backups. Just remove this portion of your maintenence plan.
>
|||Most log backups fail because the database is in simple recovery mode. Check
that first. Second, verify the database has had a full backup. Finally, I
assume we're not talking about the msdb and master--but just to be safe,
it's not one of those two, correct?
"tshad" wrote:
> I have my maintenance plan set up to backup to disk each day, including the
> tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
> 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where
> I got this). There is plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
>
|||>From his report, it looks like we are talking about master & msdb. As
well as one other called FTS. Like fnguy said - check to see if if FTS
is simple recovery model. If it is, you can't back up the tran. log.
Backup can not be performed on database 'FTS'. This sub task is
ignored.
Backup can not be performed on database 'master'. This sub task is
ignored.
Backup can not be performed on database 'msdb'. This sub task is
ignored.
|||"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> Most log backups fail because the database is in simple recovery mode.
> Check
> that first. Second, verify the database has had a full backup. Finally,
> I
> assume we're not talking about the msdb and master--but just to be safe,
> it's not one of those two, correct?
No, it isn't. It's one of our normal databases.
But you mentioned, the database having a full backup.
Do you need to do the Tran backup first?
Thanks,
Tom[vbcol=seagreen]
>
> "tshad" wrote:
|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131567831.621737.163390@.g43g2000cwa.googlegr oups.com...
> well as one other called FTS. Like fnguy said - check to see if if FTS
> is simple recovery model. If it is, you can't back up the tran. log.
> Backup can not be performed on database 'FTS'. This sub task is
> ignored.
> Backup can not be performed on database 'master'. This sub task is
> ignored.
> Backup can not be performed on database 'msdb'. This sub task is
> ignored.
>
How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
Tom
|||Check the recovery model of your database.
If the FTS database is in Simple recovery model, then log backups make no
sense.
> Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
> SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
>
ERRORLOG.n are the SQL error logs. They are simple text files. You should
be able to open them in notepad unless there is some strange protection
applied. As an administrator you should certainly be agle to read them.
Kevin Farlee
tran log.
I am getting an error on the Tran log backup, however.
It doesn't tell me why it failed, just that it failed.
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
The help center is no help.
The other 3 parts of the maintenance plan seem to go fine.
Also, there is no log in the Sql Logs, but there is in the Event log (where
I got this). There is plenty of disk space, so that is not the problem.
Am I missing something here?
Thanks,
Tom
Specify a report file for the maint plan and check that for specific error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I have my maintenance plan set up to backup to disk each day, including the tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance
> Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status: Failed - Invoked on: 2005-11-09 09:24:24 -
> Message: The job failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where I got this). There is
> plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edxoSaV5FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Specify a report file for the maint plan and check that for specific error
> messages.
I did that and got to following:
************************************************** ************************************************** *
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VENUS' as 'FTSOLUTIONS0\servicesql' (trusted)
Starting maintenance plan 'All Databases Backup' on 11/9/2005 10:33:23 AM
Backup can not be performed on database 'FTS'. This sub task is ignored.
Backup can not be performed on database 'master'. This sub task is ignored.
[3] Database model: Transaction Log Backup...
Destination: [d:\Microsoft SQL
Server\MSSQL\BACKUP\model_tlog_200511091033.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
End of maintenance plan 'All Databases Backup' on 11/9/2005 10:33:24 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
************************************************** ************************************************** *
So it works for some and not others.
And why can't it backup master or msdb - they have log files
Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>
|||I think that master & msdb are simple recovery model, hence cannot have
tran. log backups. Just remove this portion of your maintenence plan.
|||Ok.
But why did FTS not backup?
Tom
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131562250.817157.160290@.g43g2000cwa.googlegr oups.com...
>I think that master & msdb are simple recovery model, hence cannot have
> tran. log backups. Just remove this portion of your maintenence plan.
>
|||Most log backups fail because the database is in simple recovery mode. Check
that first. Second, verify the database has had a full backup. Finally, I
assume we're not talking about the msdb and master--but just to be safe,
it's not one of those two, correct?
"tshad" wrote:
> I have my maintenance plan set up to backup to disk each day, including the
> tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
> 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where
> I got this). There is plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
>
|||>From his report, it looks like we are talking about master & msdb. As
well as one other called FTS. Like fnguy said - check to see if if FTS
is simple recovery model. If it is, you can't back up the tran. log.
Backup can not be performed on database 'FTS'. This sub task is
ignored.
Backup can not be performed on database 'master'. This sub task is
ignored.
Backup can not be performed on database 'msdb'. This sub task is
ignored.
|||"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> Most log backups fail because the database is in simple recovery mode.
> Check
> that first. Second, verify the database has had a full backup. Finally,
> I
> assume we're not talking about the msdb and master--but just to be safe,
> it's not one of those two, correct?
No, it isn't. It's one of our normal databases.
But you mentioned, the database having a full backup.
Do you need to do the Tran backup first?
Thanks,
Tom[vbcol=seagreen]
>
> "tshad" wrote:
|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131567831.621737.163390@.g43g2000cwa.googlegr oups.com...
> well as one other called FTS. Like fnguy said - check to see if if FTS
> is simple recovery model. If it is, you can't back up the tran. log.
> Backup can not be performed on database 'FTS'. This sub task is
> ignored.
> Backup can not be performed on database 'master'. This sub task is
> ignored.
> Backup can not be performed on database 'msdb'. This sub task is
> ignored.
>
How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
Tom
|||Check the recovery model of your database.
If the FTS database is in Simple recovery model, then log backups make no
sense.
> Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
> SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
>
ERRORLOG.n are the SQL error logs. They are simple text files. You should
be able to open them in notepad unless there is some strange protection
applied. As an administrator you should certainly be agle to read them.
Kevin Farlee
Backups of tran log not working
I have my maintenance plan set up to backup to disk each day, including the
tran log.
I am getting an error on the Tran log backup, however.
It doesn't tell me why it failed, just that it failed.
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
The help center is no help.
The other 3 parts of the maintenance plan seem to go fine.
Also, there is no log in the Sql Logs, but there is in the Event log (where
I got this). There is plenty of disk space, so that is not the problem.
Am I missing something here?
Thanks,
TomSpecify a report file for the maint plan and check that for specific error m
essages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I have my maintenance plan set up to backup to disk each day, including the
tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Pl
an 'DB Maintenance
> Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status: Failed - Invoked on
: 2005-11-09 09:24:24 -
> Message: The job failed. The Job was invoked by User FTSOLUTIONS0\tfs. T
he last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at http://go.microsoft.com/fwl
ink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (wher
e I got this). There is
> plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edxoSaV5FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Specify a report file for the maint plan and check that for specific error
> messages.
I did that and got to following:
****************************************
************************************
*************************
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VENUS' as 'FTSOLUTIONS0\servicesql' (trusted)
Starting maintenance plan 'All Databases Backup' on 11/9/2005 10:33:23 AM
Backup can not be performed on database 'FTS'. This sub task is ignored.
Backup can not be performed on database 'master'. This sub task is ignored.
[3] Database model: Transaction Log Backup...
Destination: [d:\Microsoft SQL
Server\MSSQL\BACKUP\model_tlog_200511091
033.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
End of maintenance plan 'All Databases Backup' on 11/9/2005 10:33:24 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
****************************************
************************************
*************************
So it works for some and not others.
And why can't it backup master or msdb - they have log files
Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>|||I think that master & msdb are simple recovery model, hence cannot have
tran. log backups. Just remove this portion of your maintenence plan.|||Ok.
But why did FTS not backup?
Tom
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>I think that master & msdb are simple recovery model, hence cannot have
> tran. log backups. Just remove this portion of your maintenence plan.
>|||Most log backups fail because the database is in simple recovery mode. Chec
k
that first. Second, verify the database has had a full backup. Finally, I
assume we're not talking about the msdb and master--but just to be safe,
it's not one of those two, correct?
"tshad" wrote:
> I have my maintenance plan set up to backup to disk each day, including th
e
> tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Pl
an
> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The J
ob
> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (St
ep
> 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (wher
e
> I got this). There is plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
>|||>From his report, it looks like we are talking about master & msdb. As
well as one other called FTS. Like fnguy said - check to see if if FTS
is simple recovery model. If it is, you can't back up the tran. log.
Backup can not be performed on database 'FTS'. This sub task is
ignored.
Backup can not be performed on database 'master'. This sub task is
ignored.
Backup can not be performed on database 'msdb'. This sub task is
ignored.|||"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> Most log backups fail because the database is in simple recovery mode.
> Check
> that first. Second, verify the database has had a full backup. Finally,
> I
> assume we're not talking about the msdb and master--but just to be safe,
> it's not one of those two, correct?
No, it isn't. It's one of our normal databases.
But you mentioned, the database having a full backup.
Do you need to do the Tran backup first?
Thanks,
Tom[vbcol=seagreen]
>
> "tshad" wrote:
>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> well as one other called FTS. Like fnguy said - check to see if if FTS
> is simple recovery model. If it is, you can't back up the tran. log.
> Backup can not be performed on database 'FTS'. This sub task is
> ignored.
> Backup can not be performed on database 'master'. This sub task is
> ignored.
> Backup can not be performed on database 'msdb'. This sub task is
> ignored.
>
How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
Tom|||Check the recovery model of your database.
If the FTS database is in Simple recovery model, then log backups make no
sense.
> Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
> SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
>
ERRORLOG.n are the SQL error logs. They are simple text files. You should
be able to open them in notepad unless there is some strange protection
applied. As an administrator you should certainly be agle to read them.
Kevin Farlee
tran log.
I am getting an error on the Tran log backup, however.
It doesn't tell me why it failed, just that it failed.
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
The help center is no help.
The other 3 parts of the maintenance plan seem to go fine.
Also, there is no log in the Sql Logs, but there is in the Event log (where
I got this). There is plenty of disk space, so that is not the problem.
Am I missing something here?
Thanks,
TomSpecify a report file for the maint plan and check that for specific error m
essages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I have my maintenance plan set up to backup to disk each day, including the
tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Pl
an 'DB Maintenance
> Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status: Failed - Invoked on
: 2005-11-09 09:24:24 -
> Message: The job failed. The Job was invoked by User FTSOLUTIONS0\tfs. T
he last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at http://go.microsoft.com/fwl
ink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (wher
e I got this). There is
> plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edxoSaV5FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Specify a report file for the maint plan and check that for specific error
> messages.
I did that and got to following:
****************************************
************************************
*************************
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VENUS' as 'FTSOLUTIONS0\servicesql' (trusted)
Starting maintenance plan 'All Databases Backup' on 11/9/2005 10:33:23 AM
Backup can not be performed on database 'FTS'. This sub task is ignored.
Backup can not be performed on database 'master'. This sub task is ignored.
[3] Database model: Transaction Log Backup...
Destination: [d:\Microsoft SQL
Server\MSSQL\BACKUP\model_tlog_200511091
033.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
End of maintenance plan 'All Databases Backup' on 11/9/2005 10:33:24 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
****************************************
************************************
*************************
So it works for some and not others.
And why can't it backup master or msdb - they have log files
Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>|||I think that master & msdb are simple recovery model, hence cannot have
tran. log backups. Just remove this portion of your maintenence plan.|||Ok.
But why did FTS not backup?
Tom
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>I think that master & msdb are simple recovery model, hence cannot have
> tran. log backups. Just remove this portion of your maintenence plan.
>|||Most log backups fail because the database is in simple recovery mode. Chec
k
that first. Second, verify the database has had a full backup. Finally, I
assume we're not talking about the msdb and master--but just to be safe,
it's not one of those two, correct?
"tshad" wrote:
> I have my maintenance plan set up to backup to disk each day, including th
e
> tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Pl
an
> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The J
ob
> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (St
ep
> 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (wher
e
> I got this). There is plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
>|||>From his report, it looks like we are talking about master & msdb. As
well as one other called FTS. Like fnguy said - check to see if if FTS
is simple recovery model. If it is, you can't back up the tran. log.
Backup can not be performed on database 'FTS'. This sub task is
ignored.
Backup can not be performed on database 'master'. This sub task is
ignored.
Backup can not be performed on database 'msdb'. This sub task is
ignored.|||"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> Most log backups fail because the database is in simple recovery mode.
> Check
> that first. Second, verify the database has had a full backup. Finally,
> I
> assume we're not talking about the msdb and master--but just to be safe,
> it's not one of those two, correct?
No, it isn't. It's one of our normal databases.
But you mentioned, the database having a full backup.
Do you need to do the Tran backup first?
Thanks,
Tom[vbcol=seagreen]
>
> "tshad" wrote:
>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> well as one other called FTS. Like fnguy said - check to see if if FTS
> is simple recovery model. If it is, you can't back up the tran. log.
> Backup can not be performed on database 'FTS'. This sub task is
> ignored.
> Backup can not be performed on database 'master'. This sub task is
> ignored.
> Backup can not be performed on database 'msdb'. This sub task is
> ignored.
>
How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
Tom|||Check the recovery model of your database.
If the FTS database is in Simple recovery model, then log backups make no
sense.
> Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
> SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
>
ERRORLOG.n are the SQL error logs. They are simple text files. You should
be able to open them in notepad unless there is some strange protection
applied. As an administrator you should certainly be agle to read them.
Kevin Farlee
Backups of tran log not working
I have my maintenance plan set up to backup to disk each day, including the
tran log.
I am getting an error on the Tran log backup, however.
It doesn't tell me why it failed, just that it failed.
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
The help center is no help.
The other 3 parts of the maintenance plan seem to go fine.
Also, there is no log in the Sql Logs, but there is in the Event log (where
I got this). There is plenty of disk space, so that is not the problem.
Am I missing something here?
Thanks,
TomSpecify a report file for the maint plan and check that for specific error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I have my maintenance plan set up to backup to disk each day, including the tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance
> Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status: Failed - Invoked on: 2005-11-09 09:24:24 -
> Message: The job failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where I got this). There is
> plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edxoSaV5FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Specify a report file for the maint plan and check that for specific error
> messages.
I did that and got to following:
*****************************************************************************************************
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VENUS' as 'FTSOLUTIONS0\servicesql' (trusted)
Starting maintenance plan 'All Databases Backup' on 11/9/2005 10:33:23 AM
Backup can not be performed on database 'FTS'. This sub task is ignored.
Backup can not be performed on database 'master'. This sub task is ignored.
[3] Database model: Transaction Log Backup...
Destination: [d:\Microsoft SQL
Server\MSSQL\BACKUP\model_tlog_200511091033.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
End of maintenance plan 'All Databases Backup' on 11/9/2005 10:33:24 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
*****************************************************************************************************
So it works for some and not others.
And why can't it backup master or msdb - they have log files
Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>>I have my maintenance plan set up to backup to disk each day, including
>>the tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) -
>> Status: Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job
>> failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to
>> run was step 1 (Step 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where I got this). There is plenty of disk space, so that is not the
>> problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>|||I think that master & msdb are simple recovery model, hence cannot have
tran. log backups. Just remove this portion of your maintenence plan.|||Ok.
But why did FTS not backup?
Tom
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>I think that master & msdb are simple recovery model, hence cannot have
> tran. log backups. Just remove this portion of your maintenence plan.
>|||Most log backups fail because the database is in simple recovery mode. Check
that first. Second, verify the database has had a full backup. Finally, I
assume we're not talking about the msdb and master--but just to be safe,
it's not one of those two, correct?
"tshad" wrote:
> I have my maintenance plan set up to backup to disk each day, including the
> tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
> 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where
> I got this). There is plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
>|||>From his report, it looks like we are talking about master & msdb. As
well as one other called FTS. Like fnguy said - check to see if if FTS
is simple recovery model. If it is, you can't back up the tran. log.
Backup can not be performed on database 'FTS'. This sub task is
ignored.
Backup can not be performed on database 'master'. This sub task is
ignored.
Backup can not be performed on database 'msdb'. This sub task is
ignored.|||"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> Most log backups fail because the database is in simple recovery mode.
> Check
> that first. Second, verify the database has had a full backup. Finally,
> I
> assume we're not talking about the msdb and master--but just to be safe,
> it's not one of those two, correct?
No, it isn't. It's one of our normal databases.
But you mentioned, the database having a full backup.
Do you need to do the Tran backup first?
Thanks,
Tom
>
> "tshad" wrote:
>> I have my maintenance plan set up to backup to disk each day, including
>> the
>> tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan
>> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
>> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The
>> Job
>> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1
>> (Step
>> 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where
>> I got this). There is plenty of disk space, so that is not the problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >From his report, it looks like we are talking about master & msdb. As
> well as one other called FTS. Like fnguy said - check to see if if FTS
> is simple recovery model. If it is, you can't back up the tran. log.
> Backup can not be performed on database 'FTS'. This sub task is
> ignored.
> Backup can not be performed on database 'master'. This sub task is
> ignored.
> Backup can not be performed on database 'msdb'. This sub task is
> ignored.
>
How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
Tom|||Check the recovery model of your database.
If the FTS database is in Simple recovery model, then log backups make no
sense.
> Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
> SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
>
ERRORLOG.n are the SQL error logs. They are simple text files. You should
be able to open them in notepad unless there is some strange protection
applied. As an administrator you should certainly be agle to read them.
Kevin Farlee|||As Kevin suggested, it is probably in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23LFHRAW5FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Ok.
> But why did FTS not backup?
> Tom
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>>I think that master & msdb are simple recovery model, hence cannot have
>> tran. log backups. Just remove this portion of your maintenence plan.
>|||> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
sp_helpdb, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> Tom
>|||Pls execute following SP from Query Analyser and post it's output:
sp_helpdb 'fts'
"tshad" wrote:
> "fnguy" <fnguy@.discussions.microsoft.com> wrote in message
> news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> > Most log backups fail because the database is in simple recovery mode.
> > Check
> > that first. Second, verify the database has had a full backup. Finally,
> > I
> > assume we're not talking about the msdb and master--but just to be safe,
> > it's not one of those two, correct?
> No, it isn't. It's one of our normal databases.
> But you mentioned, the database having a full backup.
> Do you need to do the Tran backup first?
> Thanks,
> Tom|||How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
--
Also - open enterprise manager, right click the database in question,
and choose properties. Go to options tab. See what exists under the
Recovery model section. If it's simple, then all you can do is full
backups. If it's full, then you can do logs.
Tibor Karaszi wrote:
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> sp_helpdb, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> >
> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >> >From his report, it looks like we are talking about master & msdb. As
> >> well as one other called FTS. Like fnguy said - check to see if if FTS
> >> is simple recovery model. If it is, you can't back up the tran. log.
> >>
> >> Backup can not be performed on database 'FTS'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'master'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'msdb'. This sub task is
> >> ignored.
> >>
> >
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> >
> > Tom
> >|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That does show it as
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That was it.
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
But how did it get that way? What did I do to make it that way? My other
databases aren't that way.
Can I change that?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||Yes, you can change it using the ALTER DATABASE command.
How it got set to simple in the first place? I can't answer that, except saying that someone or some
program did it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message news:uP89yUh5FHA.140@.TK2MSFTNGP10.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> sp_helpdb, for instance.
> That was it.
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539,
> Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled,
> IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
> But how did it get that way? What did I do to make it that way? My other databases aren't that
> way.
> Can I change that?
> Thanks,
> Tom
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> Tom
>>
>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131630158.667899.30870@.g44g2000cwa.googlegroups.com...
> How do you check to see if it is a simple recovery model? I assume it
> isn't, but I am not sure.
> --
> Also - open enterprise manager, right click the database in question,
> and choose properties. Go to options tab. See what exists under the
> Recovery model section. If it's simple, then all you can do is full
> backups. If it's full, then you can do logs.
>
Can I just change that there?
Why would you want a simple model?
It does have a tran log defined for it and it is the only one of 5 databases
I have that isn't full. So what causes it to be simple I just created
another database, it has no question about it and it automatically made it
simple. I originally created FTS in this way - so what would cause it to be
simple - I know I didn't do it manually.
Thanks,
Tom
>
> Tibor Karaszi wrote:
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> sp_helpdb, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> >
>> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >> >From his report, it looks like we are talking about master & msdb.
>> >> >As
>> >> well as one other called FTS. Like fnguy said - check to see if if
>> >> FTS
>> >> is simple recovery model. If it is, you can't back up the tran. log.
>> >>
>> >> Backup can not be performed on database 'FTS'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'master'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'msdb'. This sub task is
>> >> ignored.
>> >>
>> >
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> >
>> > Tom
>> >
>|||Yes - you can just change that there using the drop down. Not sure why
it's full instead of simple. Simple model = less maintenance, but also
= less recoverability. See below links.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1069109,00.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp
>From microsoft...
Simple Recovery Model
This recovery model facilitates the maintenance of a database by making
the transaction log virtually maintenance free. There are limitations
placed on the recoverability of a database if this recovery model is
used.
tran log.
I am getting an error on the Tran log backup, however.
It doesn't tell me why it failed, just that it failed.
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
The help center is no help.
The other 3 parts of the maintenance plan seem to go fine.
Also, there is no log in the Sql Logs, but there is in the Event log (where
I got this). There is plenty of disk space, so that is not the problem.
Am I missing something here?
Thanks,
TomSpecify a report file for the maint plan and check that for specific error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I have my maintenance plan set up to backup to disk each day, including the tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance
> Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status: Failed - Invoked on: 2005-11-09 09:24:24 -
> Message: The job failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where I got this). There is
> plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edxoSaV5FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Specify a report file for the maint plan and check that for specific error
> messages.
I did that and got to following:
*****************************************************************************************************
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VENUS' as 'FTSOLUTIONS0\servicesql' (trusted)
Starting maintenance plan 'All Databases Backup' on 11/9/2005 10:33:23 AM
Backup can not be performed on database 'FTS'. This sub task is ignored.
Backup can not be performed on database 'master'. This sub task is ignored.
[3] Database model: Transaction Log Backup...
Destination: [d:\Microsoft SQL
Server\MSSQL\BACKUP\model_tlog_200511091033.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
End of maintenance plan 'All Databases Backup' on 11/9/2005 10:33:24 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
*****************************************************************************************************
So it works for some and not others.
And why can't it backup master or msdb - they have log files
Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>>I have my maintenance plan set up to backup to disk each day, including
>>the tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) -
>> Status: Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job
>> failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to
>> run was step 1 (Step 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where I got this). There is plenty of disk space, so that is not the
>> problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>|||I think that master & msdb are simple recovery model, hence cannot have
tran. log backups. Just remove this portion of your maintenence plan.|||Ok.
But why did FTS not backup?
Tom
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>I think that master & msdb are simple recovery model, hence cannot have
> tran. log backups. Just remove this portion of your maintenence plan.
>|||Most log backups fail because the database is in simple recovery mode. Check
that first. Second, verify the database has had a full backup. Finally, I
assume we're not talking about the msdb and master--but just to be safe,
it's not one of those two, correct?
"tshad" wrote:
> I have my maintenance plan set up to backup to disk each day, including the
> tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
> 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where
> I got this). There is plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
>|||>From his report, it looks like we are talking about master & msdb. As
well as one other called FTS. Like fnguy said - check to see if if FTS
is simple recovery model. If it is, you can't back up the tran. log.
Backup can not be performed on database 'FTS'. This sub task is
ignored.
Backup can not be performed on database 'master'. This sub task is
ignored.
Backup can not be performed on database 'msdb'. This sub task is
ignored.|||"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> Most log backups fail because the database is in simple recovery mode.
> Check
> that first. Second, verify the database has had a full backup. Finally,
> I
> assume we're not talking about the msdb and master--but just to be safe,
> it's not one of those two, correct?
No, it isn't. It's one of our normal databases.
But you mentioned, the database having a full backup.
Do you need to do the Tran backup first?
Thanks,
Tom
>
> "tshad" wrote:
>> I have my maintenance plan set up to backup to disk each day, including
>> the
>> tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan
>> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
>> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The
>> Job
>> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1
>> (Step
>> 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where
>> I got this). There is plenty of disk space, so that is not the problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >From his report, it looks like we are talking about master & msdb. As
> well as one other called FTS. Like fnguy said - check to see if if FTS
> is simple recovery model. If it is, you can't back up the tran. log.
> Backup can not be performed on database 'FTS'. This sub task is
> ignored.
> Backup can not be performed on database 'master'. This sub task is
> ignored.
> Backup can not be performed on database 'msdb'. This sub task is
> ignored.
>
How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
Tom|||Check the recovery model of your database.
If the FTS database is in Simple recovery model, then log backups make no
sense.
> Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
> SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
>
ERRORLOG.n are the SQL error logs. They are simple text files. You should
be able to open them in notepad unless there is some strange protection
applied. As an administrator you should certainly be agle to read them.
Kevin Farlee|||As Kevin suggested, it is probably in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23LFHRAW5FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Ok.
> But why did FTS not backup?
> Tom
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>>I think that master & msdb are simple recovery model, hence cannot have
>> tran. log backups. Just remove this portion of your maintenence plan.
>|||> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
sp_helpdb, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> Tom
>|||Pls execute following SP from Query Analyser and post it's output:
sp_helpdb 'fts'
"tshad" wrote:
> "fnguy" <fnguy@.discussions.microsoft.com> wrote in message
> news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> > Most log backups fail because the database is in simple recovery mode.
> > Check
> > that first. Second, verify the database has had a full backup. Finally,
> > I
> > assume we're not talking about the msdb and master--but just to be safe,
> > it's not one of those two, correct?
> No, it isn't. It's one of our normal databases.
> But you mentioned, the database having a full backup.
> Do you need to do the Tran backup first?
> Thanks,
> Tom|||How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
--
Also - open enterprise manager, right click the database in question,
and choose properties. Go to options tab. See what exists under the
Recovery model section. If it's simple, then all you can do is full
backups. If it's full, then you can do logs.
Tibor Karaszi wrote:
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> sp_helpdb, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> >
> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >> >From his report, it looks like we are talking about master & msdb. As
> >> well as one other called FTS. Like fnguy said - check to see if if FTS
> >> is simple recovery model. If it is, you can't back up the tran. log.
> >>
> >> Backup can not be performed on database 'FTS'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'master'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'msdb'. This sub task is
> >> ignored.
> >>
> >
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> >
> > Tom
> >|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That does show it as
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That was it.
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
But how did it get that way? What did I do to make it that way? My other
databases aren't that way.
Can I change that?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||Yes, you can change it using the ALTER DATABASE command.
How it got set to simple in the first place? I can't answer that, except saying that someone or some
program did it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message news:uP89yUh5FHA.140@.TK2MSFTNGP10.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> sp_helpdb, for instance.
> That was it.
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539,
> Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled,
> IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
> But how did it get that way? What did I do to make it that way? My other databases aren't that
> way.
> Can I change that?
> Thanks,
> Tom
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> Tom
>>
>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131630158.667899.30870@.g44g2000cwa.googlegroups.com...
> How do you check to see if it is a simple recovery model? I assume it
> isn't, but I am not sure.
> --
> Also - open enterprise manager, right click the database in question,
> and choose properties. Go to options tab. See what exists under the
> Recovery model section. If it's simple, then all you can do is full
> backups. If it's full, then you can do logs.
>
Can I just change that there?
Why would you want a simple model?
It does have a tran log defined for it and it is the only one of 5 databases
I have that isn't full. So what causes it to be simple I just created
another database, it has no question about it and it automatically made it
simple. I originally created FTS in this way - so what would cause it to be
simple - I know I didn't do it manually.
Thanks,
Tom
>
> Tibor Karaszi wrote:
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> sp_helpdb, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> >
>> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >> >From his report, it looks like we are talking about master & msdb.
>> >> >As
>> >> well as one other called FTS. Like fnguy said - check to see if if
>> >> FTS
>> >> is simple recovery model. If it is, you can't back up the tran. log.
>> >>
>> >> Backup can not be performed on database 'FTS'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'master'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'msdb'. This sub task is
>> >> ignored.
>> >>
>> >
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> >
>> > Tom
>> >
>|||Yes - you can just change that there using the drop down. Not sure why
it's full instead of simple. Simple model = less maintenance, but also
= less recoverability. See below links.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1069109,00.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp
>From microsoft...
Simple Recovery Model
This recovery model facilitates the maintenance of a database by making
the transaction log virtually maintenance free. There are limitations
placed on the recoverability of a database if this recovery model is
used.
Wednesday, March 7, 2012
Backups keep failing?
I used the database maintenance wizard and choose most of the defaults.
Every night my backups fail because of the step to check data indexes and
repair any minor problems. The error I receive is [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode. Does anyone know why this happeneds?
Thanks,
DanSee this page -
http://support.microsoft.com/default.aspx?scid=kb;en-us;290622
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Dan Marth" <danmarth@.earthlink.net> wrote in message
news:ux0ir6v2DHA.2408@.tk2msftngp13.phx.gbl...
> I used the database maintenance wizard and choose most of the defaults.
> Every night my backups fail because of the step to check data indexes and
> repair any minor problems. The error I receive is [Microsoft][ODBC SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs
to
> be in single user mode. Does anyone know why this happeneds?
> Thanks,
> Dan
>|||Thanks, that seemed to clear things up!
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:OTvHYUw2DHA.3196@.TK2MSFTNGP11.phx.gbl...
> See this page -
> http://support.microsoft.com/default.aspx?scid=kb;en-us;290622
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Dan Marth" <danmarth@.earthlink.net> wrote in message
> news:ux0ir6v2DHA.2408@.tk2msftngp13.phx.gbl...
> > I used the database maintenance wizard and choose most of the defaults.
> > Every night my backups fail because of the step to check data indexes
and
> > repair any minor problems. The error I receive is [Microsoft][ODBC SQL
> > Server Driver][SQL Server]Repair statement not processed. Database needs
> to
> > be in single user mode. Does anyone know why this happeneds?
> >
> > Thanks,
> > Dan
> >
> >
>
Every night my backups fail because of the step to check data indexes and
repair any minor problems. The error I receive is [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode. Does anyone know why this happeneds?
Thanks,
DanSee this page -
http://support.microsoft.com/default.aspx?scid=kb;en-us;290622
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Dan Marth" <danmarth@.earthlink.net> wrote in message
news:ux0ir6v2DHA.2408@.tk2msftngp13.phx.gbl...
> I used the database maintenance wizard and choose most of the defaults.
> Every night my backups fail because of the step to check data indexes and
> repair any minor problems. The error I receive is [Microsoft][ODBC SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs
to
> be in single user mode. Does anyone know why this happeneds?
> Thanks,
> Dan
>|||Thanks, that seemed to clear things up!
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:OTvHYUw2DHA.3196@.TK2MSFTNGP11.phx.gbl...
> See this page -
> http://support.microsoft.com/default.aspx?scid=kb;en-us;290622
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Dan Marth" <danmarth@.earthlink.net> wrote in message
> news:ux0ir6v2DHA.2408@.tk2msftngp13.phx.gbl...
> > I used the database maintenance wizard and choose most of the defaults.
> > Every night my backups fail because of the step to check data indexes
and
> > repair any minor problems. The error I receive is [Microsoft][ODBC SQL
> > Server Driver][SQL Server]Repair statement not processed. Database needs
> to
> > be in single user mode. Does anyone know why this happeneds?
> >
> > Thanks,
> > Dan
> >
> >
>
Backups keep failing?
I used the database maintenance wizard and choose most of the defaults.
Every night my backups fail because of the step to check data indexes and
repair any minor problems. The error I receive is [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode. Does anyone know why this happeneds?
Thanks,
DanSee this page -
http://support.microsoft.com/defaul...kb;en-us;290622
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Dan Marth" <danmarth@.earthlink.net> wrote in message
news:ux0ir6v2DHA.2408@.tk2msftngp13.phx.gbl...
to
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:OTvHYUw2DHA.3196@.TK2MSFTNGP11.phx.gbl...
Every night my backups fail because of the step to check data indexes and
repair any minor problems. The error I receive is [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode. Does anyone know why this happeneds?
Thanks,
DanSee this page -
http://support.microsoft.com/defaul...kb;en-us;290622
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Dan Marth" <danmarth@.earthlink.net> wrote in message
news:ux0ir6v2DHA.2408@.tk2msftngp13.phx.gbl...
quote:
> I used the database maintenance wizard and choose most of the defaults.
> Every night my backups fail because of the step to check data indexes and
> repair any minor problems. The error I receive is [Microsoft][ODBC SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs
to
quote:|||Thanks, that seemed to clear things up!
> be in single user mode. Does anyone know why this happeneds?
> Thanks,
> Dan
>
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:OTvHYUw2DHA.3196@.TK2MSFTNGP11.phx.gbl...
quote:
> See this page -
> http://support.microsoft.com/defaul...kb;en-us;290622
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Dan Marth" <danmarth@.earthlink.net> wrote in message
> news:ux0ir6v2DHA.2408@.tk2msftngp13.phx.gbl...
and[QUOTE]
> to
>
Backups files not deleted with maintenance plan
Hello All,
I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works perfectly fine, but it does not delete the backup files
scheduled to cleanup files older then a week. Any suggestions where to look
to sort this out?
The backups take up the disk space and I have to go in and manually deleted
backups for around 40 databases.
Thanks a lot.
Imran.
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
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
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
> week. Any suggestions where to look to sort this out?
> The backups take up the disk space and I have to go in and manually deleted backups for around 40
> databases.
> Thanks a lot.
> Imran.
>
|||Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since I have the same issue on two servers, the
development server and the live server both don't delete backup files.
I ran the maintenance plan right now and there wasn't any other process
holding the backup files. but still the files were not deleted. The backup
log did not indicate any errors relating to deletion of files, rather it
does not even show the attempt to delete the older files.
Any clues ?
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Below KB might help:
> http://support.microsoft.com/default...&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> 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
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>
|||Do you have any databases in simple recovery mode included in the plan? I've seen this for
logbackups. Log backup cannot be performed in simple recovery mode, so the maint plan never gets to
the part where to delete old log backup files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Thanks for the link and the details. It cannot be a permissions issue or a sharing violation since
> I have the same issue on two servers, the development server and the live server both don't
> delete backup files.
> I ran the maintenance plan right now and there wasn't any other process holding the backup files.
> but still the files were not deleted. The backup log did not indicate any errors relating to
> deletion of files, rather it does not even show the attempt to delete the older files.
> Any clues ?
> Imran.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>
|||Thanks for the tip, No of databases were on simple recovery mode. After
changing the recovery mode to Bulk it has sorted out the problem on both
development and production machine. Thanks a lot.
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvSsIwr6FHA.1944@.TK2MSFTNGP14.phx.gbl...
> Do you have any databases in simple recovery mode included in the plan?
> I've seen this for logbackups. Log backup cannot be performed in simple
> recovery mode, so the maint plan never gets to the part where to delete
> old log backup files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>
I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works perfectly fine, but it does not delete the backup files
scheduled to cleanup files older then a week. Any suggestions where to look
to sort this out?
The backups take up the disk space and I have to go in and manually deleted
backups for around 40 databases.
Thanks a lot.
Imran.
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
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
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
> week. Any suggestions where to look to sort this out?
> The backups take up the disk space and I have to go in and manually deleted backups for around 40
> databases.
> Thanks a lot.
> Imran.
>
|||Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since I have the same issue on two servers, the
development server and the live server both don't delete backup files.
I ran the maintenance plan right now and there wasn't any other process
holding the backup files. but still the files were not deleted. The backup
log did not indicate any errors relating to deletion of files, rather it
does not even show the attempt to delete the older files.
Any clues ?
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Below KB might help:
> http://support.microsoft.com/default...&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> 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
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>
|||Do you have any databases in simple recovery mode included in the plan? I've seen this for
logbackups. Log backup cannot be performed in simple recovery mode, so the maint plan never gets to
the part where to delete old log backup files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Thanks for the link and the details. It cannot be a permissions issue or a sharing violation since
> I have the same issue on two servers, the development server and the live server both don't
> delete backup files.
> I ran the maintenance plan right now and there wasn't any other process holding the backup files.
> but still the files were not deleted. The backup log did not indicate any errors relating to
> deletion of files, rather it does not even show the attempt to delete the older files.
> Any clues ?
> Imran.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>
|||Thanks for the tip, No of databases were on simple recovery mode. After
changing the recovery mode to Bulk it has sorted out the problem on both
development and production machine. Thanks a lot.
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvSsIwr6FHA.1944@.TK2MSFTNGP14.phx.gbl...
> Do you have any databases in simple recovery mode included in the plan?
> I've seen this for logbackups. Log backup cannot be performed in simple
> recovery mode, so the maint plan never gets to the part where to delete
> old log backup files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>
Backups files not deleted with maintenance plan
Hello All,
I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works perfectly fine, but it does not delete the backup files
scheduled to cleanup files older then a week. Any suggestions where to look
to sort this out?
The backups take up the disk space and I have to go in and manually deleted
backups for around 40 databases.
Thanks a lot.
Imran.Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
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
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
> week. Any suggestions where to look to sort this out?
> The backups take up the disk space and I have to go in and manually deleted backups for around 40
> databases.
> Thanks a lot.
> Imran.
>|||Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since I have the same issue on two servers, the
development server and the live server both don't delete backup files.
I ran the maintenance plan right now and there wasn't any other process
holding the backup files. but still the files were not deleted. The backup
log did not indicate any errors relating to deletion of files, rather it
does not even show the attempt to delete the older files.
Any clues ?
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Below KB might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> 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
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> Hello All,
>> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
>> The backup works perfectly fine, but it does not delete the backup files
>> scheduled to cleanup files older then a week. Any suggestions where to
>> look to sort this out?
>> The backups take up the disk space and I have to go in and manually
>> deleted backups for around 40 databases.
>> Thanks a lot.
>> Imran.
>|||Do you have any databases in simple recovery mode included in the plan? I've seen this for
logbackups. Log backup cannot be performed in simple recovery mode, so the maint plan never gets to
the part where to delete old log backup files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Thanks for the link and the details. It cannot be a permissions issue or a sharing violation since
> I have the same issue on two servers, the development server and the live server both don't
> delete backup files.
> I ran the maintenance plan right now and there wasn't any other process holding the backup files.
> but still the files were not deleted. The backup log did not indicate any errors relating to
> deletion of files, rather it does not even show the attempt to delete the older files.
> Any clues ?
> Imran.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>> Below KB might help:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>>
>> Also, check out below great troubleshooting suggestions from Bill H at MS:
>>
>> -- Log files don't delete --
>> 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
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> Hello All,
>> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
>> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
>> week. Any suggestions where to look to sort this out?
>> The backups take up the disk space and I have to go in and manually deleted backups for around
>> 40 databases.
>> Thanks a lot.
>> Imran.
>>
>|||Thanks for the tip, No of databases were on simple recovery mode. After
changing the recovery mode to Bulk it has sorted out the problem on both
development and production machine. Thanks a lot.
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvSsIwr6FHA.1944@.TK2MSFTNGP14.phx.gbl...
> Do you have any databases in simple recovery mode included in the plan?
> I've seen this for logbackups. Log backup cannot be performed in simple
> recovery mode, so the maint plan never gets to the part where to delete
> old log backup files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>> Thanks for the link and the details. It cannot be a permissions issue or
>> a sharing violation since I have the same issue on two servers, the
>> development server and the live server both don't delete backup files.
>> I ran the maintenance plan right now and there wasn't any other process
>> holding the backup files. but still the files were not deleted. The
>> backup log did not indicate any errors relating to deletion of files,
>> rather it does not even show the attempt to delete the older files.
>> Any clues ?
>> Imran.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>> Below KB might help:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>>
>> Also, check out below great troubleshooting suggestions from Bill H at
>> MS:
>>
>> -- Log files don't delete --
>> 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
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Imran Aziz" <imran@.tb2.net> wrote in message
>> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> Hello All,
>> I have a SQL server maintenance plan setup with my SQLServer 2000
>> MMC. The backup works perfectly fine, but it does not delete the backup
>> files scheduled to cleanup files older then a week. Any suggestions
>> where to look to sort this out?
>> The backups take up the disk space and I have to go in and manually
>> deleted backups for around 40 databases.
>> Thanks a lot.
>> Imran.
>>
>>
>
I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works perfectly fine, but it does not delete the backup files
scheduled to cleanup files older then a week. Any suggestions where to look
to sort this out?
The backups take up the disk space and I have to go in and manually deleted
backups for around 40 databases.
Thanks a lot.
Imran.Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
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
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
> week. Any suggestions where to look to sort this out?
> The backups take up the disk space and I have to go in and manually deleted backups for around 40
> databases.
> Thanks a lot.
> Imran.
>|||Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since I have the same issue on two servers, the
development server and the live server both don't delete backup files.
I ran the maintenance plan right now and there wasn't any other process
holding the backup files. but still the files were not deleted. The backup
log did not indicate any errors relating to deletion of files, rather it
does not even show the attempt to delete the older files.
Any clues ?
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Below KB might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> 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
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> Hello All,
>> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
>> The backup works perfectly fine, but it does not delete the backup files
>> scheduled to cleanup files older then a week. Any suggestions where to
>> look to sort this out?
>> The backups take up the disk space and I have to go in and manually
>> deleted backups for around 40 databases.
>> Thanks a lot.
>> Imran.
>|||Do you have any databases in simple recovery mode included in the plan? I've seen this for
logbackups. Log backup cannot be performed in simple recovery mode, so the maint plan never gets to
the part where to delete old log backup files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Thanks for the link and the details. It cannot be a permissions issue or a sharing violation since
> I have the same issue on two servers, the development server and the live server both don't
> delete backup files.
> I ran the maintenance plan right now and there wasn't any other process holding the backup files.
> but still the files were not deleted. The backup log did not indicate any errors relating to
> deletion of files, rather it does not even show the attempt to delete the older files.
> Any clues ?
> Imran.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>> Below KB might help:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>>
>> Also, check out below great troubleshooting suggestions from Bill H at MS:
>>
>> -- Log files don't delete --
>> 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
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> Hello All,
>> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
>> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
>> week. Any suggestions where to look to sort this out?
>> The backups take up the disk space and I have to go in and manually deleted backups for around
>> 40 databases.
>> Thanks a lot.
>> Imran.
>>
>|||Thanks for the tip, No of databases were on simple recovery mode. After
changing the recovery mode to Bulk it has sorted out the problem on both
development and production machine. Thanks a lot.
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvSsIwr6FHA.1944@.TK2MSFTNGP14.phx.gbl...
> Do you have any databases in simple recovery mode included in the plan?
> I've seen this for logbackups. Log backup cannot be performed in simple
> recovery mode, so the maint plan never gets to the part where to delete
> old log backup files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>> Thanks for the link and the details. It cannot be a permissions issue or
>> a sharing violation since I have the same issue on two servers, the
>> development server and the live server both don't delete backup files.
>> I ran the maintenance plan right now and there wasn't any other process
>> holding the backup files. but still the files were not deleted. The
>> backup log did not indicate any errors relating to deletion of files,
>> rather it does not even show the attempt to delete the older files.
>> Any clues ?
>> Imran.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>> Below KB might help:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>>
>> Also, check out below great troubleshooting suggestions from Bill H at
>> MS:
>>
>> -- Log files don't delete --
>> 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
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Imran Aziz" <imran@.tb2.net> wrote in message
>> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> Hello All,
>> I have a SQL server maintenance plan setup with my SQLServer 2000
>> MMC. The backup works perfectly fine, but it does not delete the backup
>> files scheduled to cleanup files older then a week. Any suggestions
>> where to look to sort this out?
>> The backups take up the disk space and I have to go in and manually
>> deleted backups for around 40 databases.
>> Thanks a lot.
>> Imran.
>>
>>
>
Subscribe to:
Posts (Atom)