Ok. Basically I do not know how to access any database files from the SQL Server. Can someone help me please. How do I access the files from another computer.Hi,
did u give the login permissions to ur other computer?wat i mean is on the sql server-->Security-->Logins(right click)New Logins n then just write the login name of ur other computer and give urself rights to access all the files in the sql server....is it of some help?
Regards.|||Well right now I am at work, but I will try that when I get home. But basically, I do not even know how to upload files to my SQL Server. Your help is greatly appreciated.|||Hi,
you can easily upload files using the import/export wizard in sql server...and it can be done either on the system database i.e. usually master database or you can create a new database by right click on the system database-->new database and lets say we name it as Nov and in this database or any other u do right click in the options there is one option TASKS>import files or export files...somethin like that...n then an import export wizard will come and then just follow the steps n check if you want to upload from a file or from oracle database or anywhere...you can easily upload stuff...
i hope its helpful...since i m also new to sql server..i just started it some months back...
Regards.|||No. Stop it. You've been a great help.
Showing posts with label files. Show all posts
Showing posts with label files. Show all posts
Sunday, March 25, 2012
Tuesday, March 20, 2012
Balancing data between files
We are storing all our SQL 2000 databases on SAN LUNs, and one of our databases currently uses a single 40GB file which is approaching capacity. If we add further files using different LUNs, the data will start being added to these new files. My questions are these: if we were to add a number of new LUNs to this database, is there a way to redistribute the existing data so it is balanced across all files in order to gain the most benefit from having multiple files, rather than just dispersing the additional fragments across the new files?
Will the optimise feature of the maintenance plan do this automatically during the index rebuilds?
Is it better to add more files to the PRIMARY filegroup, or add a number of filegroups with single files in each? We aren't looking to use filegroups for fiddling with our backups by the way.
Many thanks for any recommendations offered.I would opt for more spindles and heads to move the data quicker. Best way I have found is to create new filegroups, then drop primary index on old filegroup and then recreate primary index on desired filegroup. This accomplishes two things.
First, it forces the move of the table data to the new filegroup since the leaf node of primary index **IS** the data page. Secondly, you get not only an index reorg, you get a contiguous page allocation based on the primary index.
Prior to this move, you might want to look at your fill factors to see if they need to be adjusted, because this would be a great time to do that too!|||Also, moving nonclustered indexes in the way described by tomh53 is a great idea as well - not only do you equally distribute your data, but also separate table and its indexes onto different devices, which is generally a good thing to do.sql
Will the optimise feature of the maintenance plan do this automatically during the index rebuilds?
Is it better to add more files to the PRIMARY filegroup, or add a number of filegroups with single files in each? We aren't looking to use filegroups for fiddling with our backups by the way.
Many thanks for any recommendations offered.I would opt for more spindles and heads to move the data quicker. Best way I have found is to create new filegroups, then drop primary index on old filegroup and then recreate primary index on desired filegroup. This accomplishes two things.
First, it forces the move of the table data to the new filegroup since the leaf node of primary index **IS** the data page. Secondly, you get not only an index reorg, you get a contiguous page allocation based on the primary index.
Prior to this move, you might want to look at your fill factors to see if they need to be adjusted, because this would be a great time to do that too!|||Also, moving nonclustered indexes in the way described by tomh53 is a great idea as well - not only do you equally distribute your data, but also separate table and its indexes onto different devices, which is generally a good thing to do.sql
Bakup dosen't delete files
When using SQLserver to take backup throw a maintance plan
i've selected to delete files older that 2 day's this
dosent hapen. Why?Thanks that solved the problem!!!
>--Original Message--
>Deleting th files is the last thing the plan does so if
your backup fails it
>won't delete the files.
>Here is a very good summary of the possible issues with
that from Bill 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.
>
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Fredrik" <fredrik.ehrenholm@.proserva.com> wrote in
message
>news:073801c36569$dcbb26d0$a301280a@.phx.gbl...
>> When using SQLserver to take backup throw a maintance
plan
>> i've selected to delete files older that 2 day's this
>> dosent hapen. Why?
>
>.
>
i've selected to delete files older that 2 day's this
dosent hapen. Why?Thanks that solved the problem!!!
>--Original Message--
>Deleting th files is the last thing the plan does so if
your backup fails it
>won't delete the files.
>Here is a very good summary of the possible issues with
that from Bill 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.
>
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Fredrik" <fredrik.ehrenholm@.proserva.com> wrote in
message
>news:073801c36569$dcbb26d0$a301280a@.phx.gbl...
>> When using SQLserver to take backup throw a maintance
plan
>> i've selected to delete files older that 2 day's this
>> dosent hapen. Why?
>
>.
>
BAK, TRN files
Hey all,
I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
them if you are running server backup IE Using Arcserve.
We seem to be constantly running out of space on your drives so I have been
so far moving the BAK and TRN files to different disks but as you can imagine
this cant continue for too long.
We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10gbs
this seems huge to me, as the drive is only 29 Gb.
A friend mentioned that you shouldn't limit the size of the TRN logs as your
only shooting yourself inthe foot should the db go down, but at this Im going
to have to start getting new disks to hold the increasing amounts.
Anyone any ideas of what I can do, is it ok to delete these files or limit
the growth all together, say to a Gb a DB ?
ThanksYes, you have to delete old backup files. Determine a strategy for how long to keep them, and with
this, consider hoe you store these files on tape.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:03BB4744-B688-4190-9220-17993AEB37BB@.microsoft.com...
> Hey all,
> I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
> them if you are running server backup IE Using Arcserve.
> We seem to be constantly running out of space on your drives so I have been
> so far moving the BAK and TRN files to different disks but as you can imagine
> this cant continue for too long.
> We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10gbs
> this seems huge to me, as the drive is only 29 Gb.
> A friend mentioned that you shouldn't limit the size of the TRN logs as your
> only shooting yourself inthe foot should the db go down, but at this Im going
> to have to start getting new disks to hold the increasing amounts.
> Anyone any ideas of what I can do, is it ok to delete these files or limit
> the growth all together, say to a Gb a DB ?
> Thanks
I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
them if you are running server backup IE Using Arcserve.
We seem to be constantly running out of space on your drives so I have been
so far moving the BAK and TRN files to different disks but as you can imagine
this cant continue for too long.
We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10gbs
this seems huge to me, as the drive is only 29 Gb.
A friend mentioned that you shouldn't limit the size of the TRN logs as your
only shooting yourself inthe foot should the db go down, but at this Im going
to have to start getting new disks to hold the increasing amounts.
Anyone any ideas of what I can do, is it ok to delete these files or limit
the growth all together, say to a Gb a DB ?
ThanksYes, you have to delete old backup files. Determine a strategy for how long to keep them, and with
this, consider hoe you store these files on tape.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:03BB4744-B688-4190-9220-17993AEB37BB@.microsoft.com...
> Hey all,
> I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
> them if you are running server backup IE Using Arcserve.
> We seem to be constantly running out of space on your drives so I have been
> so far moving the BAK and TRN files to different disks but as you can imagine
> this cant continue for too long.
> We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10gbs
> this seems huge to me, as the drive is only 29 Gb.
> A friend mentioned that you shouldn't limit the size of the TRN logs as your
> only shooting yourself inthe foot should the db go down, but at this Im going
> to have to start getting new disks to hold the increasing amounts.
> Anyone any ideas of what I can do, is it ok to delete these files or limit
> the growth all together, say to a Gb a DB ?
> Thanks
BAK, TRN files
Hey all,
I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
them if you are running server backup IE Using Arcserve.
We seem to be constantly running out of space on your drives so I have been
so far moving the BAK and TRN files to different disks but as you can imagin
e
this cant continue for too long.
We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10gbs
this seems huge to me, as the drive is only 29 Gb.
A friend mentioned that you shouldn't limit the size of the TRN logs as your
only shooting yourself inthe foot should the db go down, but at this Im goin
g
to have to start getting new disks to hold the increasing amounts.
Anyone any ideas of what I can do, is it ok to delete these files or limit
the growth all together, say to a Gb a DB ?
ThanksYes, you have to delete old backup files. Determine a strategy for how long
to keep them, and with
this, consider hoe you store these files on tape.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:03BB4744-B688-4190-9220-17993AEB37BB@.microsoft.com...
> Hey all,
> I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
> them if you are running server backup IE Using Arcserve.
> We seem to be constantly running out of space on your drives so I have bee
n
> so far moving the BAK and TRN files to different disks but as you can imag
ine
> this cant continue for too long.
> We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10g
bs
> this seems huge to me, as the drive is only 29 Gb.
> A friend mentioned that you shouldn't limit the size of the TRN logs as yo
ur
> only shooting yourself inthe foot should the db go down, but at this Im go
ing
> to have to start getting new disks to hold the increasing amounts.
> Anyone any ideas of what I can do, is it ok to delete these files or limit
> the growth all together, say to a Gb a DB ?
> Thanks
I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
them if you are running server backup IE Using Arcserve.
We seem to be constantly running out of space on your drives so I have been
so far moving the BAK and TRN files to different disks but as you can imagin
e
this cant continue for too long.
We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10gbs
this seems huge to me, as the drive is only 29 Gb.
A friend mentioned that you shouldn't limit the size of the TRN logs as your
only shooting yourself inthe foot should the db go down, but at this Im goin
g
to have to start getting new disks to hold the increasing amounts.
Anyone any ideas of what I can do, is it ok to delete these files or limit
the growth all together, say to a Gb a DB ?
ThanksYes, you have to delete old backup files. Determine a strategy for how long
to keep them, and with
this, consider hoe you store these files on tape.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:03BB4744-B688-4190-9220-17993AEB37BB@.microsoft.com...
> Hey all,
> I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
> them if you are running server backup IE Using Arcserve.
> We seem to be constantly running out of space on your drives so I have bee
n
> so far moving the BAK and TRN files to different disks but as you can imag
ine
> this cant continue for too long.
> We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10g
bs
> this seems huge to me, as the drive is only 29 Gb.
> A friend mentioned that you shouldn't limit the size of the TRN logs as yo
ur
> only shooting yourself inthe foot should the db go down, but at this Im go
ing
> to have to start getting new disks to hold the increasing amounts.
> Anyone any ideas of what I can do, is it ok to delete these files or limit
> the growth all together, say to a Gb a DB ?
> Thanks
Sunday, March 11, 2012
Bad data in source file kills package?
I'm reading data from sets of CSV source files with " text qualifiers. Most of the data is good, but I do run into some bad data that doesn't seem to get handled by redirecting rows. The type of data that is killing me is this:
"Business ID","Business Name","Business Phone"
"12345",""A" SPOT, THE ","555-555-5555"
Access sees this as an unparsable record and continues with the rest of the data. SSIS seems to choke on it. I've set the package to allow 1000 errors with no change.
I did set up an error file from my source file, with error output set to redirect row on error and on truncation. The error output seems only to show columns for "Flat File Source Error Output Column", "ErrorCode", and "ErrorColumn" too. (I'm sure there's a good reason, but I've found it really frustrating that it doesn't use the source file column mappings.)
What I'd like to do is redirect (or fix) the bad rows and continue with the rest of the load.
The Execution Results tab shows the following errors.
[Flat File Source [2082]] Error: The column delimiter for column "Business Phone" was not found.
[Flat File Source [2082]] Error: An error occurred while processing file "\\Share\Files\flat_file.csv" on data row 464.
[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (2082) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.
Any help would be much appreciated!
two years past...I encounter the same problem.
Just output the source file to other format: tab as delimiter, execl..... to skip the problem.
|||Hi lund,This is because you were using " as text qualifier and there was a data that had value just the same as the text qualifier. The solution is you should replace the " of your data value with another character like '. Or just use another text qualifier.
Best regards,
Hery|||
The other two posters are correct. If you have any control over the source file consider changing the delimeter (to tabs) and/or the text qualifier (to something that doesn't appear in your text.)
Failing that, I have had some success in the past in pre-processing files. Basically you can load the file line by line, treating each line as a single record, and do some processing on the line to clean it.
Then you output the clean lines back into a new file.
Processing could be simple (eg count the number of commas in a row, or verify that if you split a row you get a set number of columns, and they are the correct width.)
Preferred approach is to fix the source data to export to something other than comma separated, though. There is a good reason that .tab files were invented!
Dylan.
backwards compatibility of the MDF file 2000 and 2005
I'm looking for information on if the SQL Database files are compatable between SQL 2000 and SQL 2005.
I know I can take a mdf from SQL 2000 and attach it to a SQL Server 2005 server and everyting works.
So the real quesiton is : Can I take a SQL Server database that started in SQL 2000, attach it to SQL Server 2005 and work with it, (including schema changes) and then attach it to a SQL Server 2000 server.
In the above scenario I'm keeping the compatability level at 8 (2000).
Does MS have any articals on this subject.
I need to know the limitations since the products I'm working on have to support both versions and so do our internal tools.
Thanks,
D
The MDF file format is different between SQL 2000 and 2005 and are not compatible. Compatability level affects the behavior of certain functionality, not the file format that SQL 2005 uses.
You should be able to write applications that can support both SQL 2000 and SQL 2005 without too much problem, but you will not be able to swap the actuall MDF file between the different versions.
Regards,
Mike Wachal
SQL Express
I have been able to take a 2000 database and use it with SQL 2005 but not the other way around.
Are you saying that I should not be using a 2000 database on a 2005 server?
Thanks for the help
|||You can go from SQL2K to SQL2K5 but not the other way.Thursday, March 8, 2012
Backups: Syncing File and Database backups
Hi,
What's the best practice when trying to synchronize the backup of a set of
databases and a set of files on the file system?
I currently create backup schedules using the Maintenance Plan wizard. Is
there a way to trigger an event when the scheduled backup completes? Or is
there a better way to do this?
Thanks!
RossHi
You can add additional steps to the maintainance plan's backup job, or
alternatively you may want to start the job from batch script run by the
windows scheduler and run xp_sqlmaint (the command executed by the job step
of the maintainance job) though isql.
John
"Ross" wrote:
> Hi,
> What's the best practice when trying to synchronize the backup of a set of
> databases and a set of files on the file system?
> I currently create backup schedules using the Maintenance Plan wizard. Is
> there a way to trigger an event when the scheduled backup completes? Or i
s
> there a better way to do this?
> Thanks!
> Ross
What's the best practice when trying to synchronize the backup of a set of
databases and a set of files on the file system?
I currently create backup schedules using the Maintenance Plan wizard. Is
there a way to trigger an event when the scheduled backup completes? Or is
there a better way to do this?
Thanks!
RossHi
You can add additional steps to the maintainance plan's backup job, or
alternatively you may want to start the job from batch script run by the
windows scheduler and run xp_sqlmaint (the command executed by the job step
of the maintainance job) though isql.
John
"Ross" wrote:
> Hi,
> What's the best practice when trying to synchronize the backup of a set of
> databases and a set of files on the file system?
> I currently create backup schedules using the Maintenance Plan wizard. Is
> there a way to trigger an event when the scheduled backup completes? Or i
s
> there a better way to do this?
> Thanks!
> Ross
Backups: Syncing File and Database backups
Hi,
What's the best practice when trying to synchronize the backup of a set of
databases and a set of files on the file system?
I currently create backup schedules using the Maintenance Plan wizard. Is
there a way to trigger an event when the scheduled backup completes? Or is
there a better way to do this?
Thanks!
RossHi
You can add additional steps to the maintainance plan's backup job, or
alternatively you may want to start the job from batch script run by the
windows scheduler and run xp_sqlmaint (the command executed by the job step
of the maintainance job) though isql.
John
"Ross" wrote:
> Hi,
> What's the best practice when trying to synchronize the backup of a set of
> databases and a set of files on the file system?
> I currently create backup schedules using the Maintenance Plan wizard. Is
> there a way to trigger an event when the scheduled backup completes? Or is
> there a better way to do this?
> Thanks!
> Ross
What's the best practice when trying to synchronize the backup of a set of
databases and a set of files on the file system?
I currently create backup schedules using the Maintenance Plan wizard. Is
there a way to trigger an event when the scheduled backup completes? Or is
there a better way to do this?
Thanks!
RossHi
You can add additional steps to the maintainance plan's backup job, or
alternatively you may want to start the job from batch script run by the
windows scheduler and run xp_sqlmaint (the command executed by the job step
of the maintainance job) though isql.
John
"Ross" wrote:
> Hi,
> What's the best practice when trying to synchronize the backup of a set of
> databases and a set of files on the file system?
> I currently create backup schedules using the Maintenance Plan wizard. Is
> there a way to trigger an event when the scheduled backup completes? Or is
> there a better way to do this?
> Thanks!
> Ross
Backups: Syncing File and Database backups
Hi,
What's the best practice when trying to synchronize the backup of a set of
databases and a set of files on the file system?
I currently create backup schedules using the Maintenance Plan wizard. Is
there a way to trigger an event when the scheduled backup completes? Or is
there a better way to do this?
Thanks!
Ross
Hi
You can add additional steps to the maintainance plan's backup job, or
alternatively you may want to start the job from batch script run by the
windows scheduler and run xp_sqlmaint (the command executed by the job step
of the maintainance job) though isql.
John
"Ross" wrote:
> Hi,
> What's the best practice when trying to synchronize the backup of a set of
> databases and a set of files on the file system?
> I currently create backup schedules using the Maintenance Plan wizard. Is
> there a way to trigger an event when the scheduled backup completes? Or is
> there a better way to do this?
> Thanks!
> Ross
What's the best practice when trying to synchronize the backup of a set of
databases and a set of files on the file system?
I currently create backup schedules using the Maintenance Plan wizard. Is
there a way to trigger an event when the scheduled backup completes? Or is
there a better way to do this?
Thanks!
Ross
Hi
You can add additional steps to the maintainance plan's backup job, or
alternatively you may want to start the job from batch script run by the
windows scheduler and run xp_sqlmaint (the command executed by the job step
of the maintainance job) though isql.
John
"Ross" wrote:
> Hi,
> What's the best practice when trying to synchronize the backup of a set of
> databases and a set of files on the file system?
> I currently create backup schedules using the Maintenance Plan wizard. Is
> there a way to trigger an event when the scheduled backup completes? Or is
> there a better way to do this?
> Thanks!
> Ross
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:
>
Wednesday, March 7, 2012
BackUps not deleting
I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
have set it to delete files older than 1 day. This used to work, but now just
backs up without deleting the old files.
Any help would be appreciated,
Craig
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/
http://www.sqlug.se/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
> have set it to delete files older than 1 day. This used to work, but now just
> backs up without deleting the old files.
> Any help would be appreciated,
> Craig
|||I also have the same problem. Our maintenance plan was working however now it
doesnt.
The Service Account has full control to the backup location and nothing is
running at the same time as the backup. Any ideas?
Andrew
"Tibor Karaszi" wrote:
> 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/
> http://www.sqlug.se/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>
>
have set it to delete files older than 1 day. This used to work, but now just
backs up without deleting the old files.
Any help would be appreciated,
Craig
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/
http://www.sqlug.se/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
> have set it to delete files older than 1 day. This used to work, but now just
> backs up without deleting the old files.
> Any help would be appreciated,
> Craig
|||I also have the same problem. Our maintenance plan was working however now it
doesnt.
The Service Account has full control to the backup location and nothing is
running at the same time as the backup. Any ideas?
Andrew
"Tibor Karaszi" wrote:
> 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/
> http://www.sqlug.se/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>
>
BackUps not deleting
I am backing up databases on SQL Server 2000 using a database maintenance
plan. I have set it to remove files older than 1 day (for both the database
and transaction log backups). This used to delete the old backup files, but
now it just backs up, without deleting anything.
Any help would be appreciated,
CraigSorry, I posted this in the wrong place. I have moved it to sqlserver.server
http://msdn.microsoft.com/newsgroup...r&lang=en&cr=US
"Craig HB" wrote:
> I am backing up databases on SQL Server 2000 using a database maintenance
> plan. I have set it to remove files older than 1 day (for both the databas
e
> and transaction log backups). This used to delete the old backup files, bu
t
> now it just backs up, without deleting anything.
> Any help would be appreciated,
> Craig
plan. I have set it to remove files older than 1 day (for both the database
and transaction log backups). This used to delete the old backup files, but
now it just backs up, without deleting anything.
Any help would be appreciated,
CraigSorry, I posted this in the wrong place. I have moved it to sqlserver.server
http://msdn.microsoft.com/newsgroup...r&lang=en&cr=US
"Craig HB" wrote:
> I am backing up databases on SQL Server 2000 using a database maintenance
> plan. I have set it to remove files older than 1 day (for both the databas
e
> and transaction log backups). This used to delete the old backup files, bu
t
> now it just backs up, without deleting anything.
> Any help would be appreciated,
> Craig
BackUps not deleting
I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
have set it to delete files older than 1 day. This used to work, but now jus
t
backs up without deleting the old files.
Any help would be appreciated,
CraigBelow KB might help:
http://support.microsoft.com/defaul...2&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/
http://www.sqlug.se/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>I am backing up databases on SQL Server 2000 using a Database BackUp Plan.
I
> have set it to delete files older than 1 day. This used to work, but now j
ust
> backs up without deleting the old files.
> Any help would be appreciated,
> Craig|||I also have the same problem. Our maintenance plan was working however now i
t
doesnt.
The Service Account has full control to the backup location and nothing is
running at the same time as the backup. Any ideas?
Andrew
"Tibor Karaszi" wrote:
> Below KB might help:
> http://support.microsoft.com/defaul...sinternals.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 woul
d
> 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 t
o
> local drive), then you will need to modify the above (with respect to wher
e
> 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/
> http://www.sqlug.se/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>
>
have set it to delete files older than 1 day. This used to work, but now jus
t
backs up without deleting the old files.
Any help would be appreciated,
CraigBelow KB might help:
http://support.microsoft.com/defaul...2&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/
http://www.sqlug.se/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>I am backing up databases on SQL Server 2000 using a Database BackUp Plan.
I
> have set it to delete files older than 1 day. This used to work, but now j
ust
> backs up without deleting the old files.
> Any help would be appreciated,
> Craig|||I also have the same problem. Our maintenance plan was working however now i
t
doesnt.
The Service Account has full control to the backup location and nothing is
running at the same time as the backup. Any ideas?
Andrew
"Tibor Karaszi" wrote:
> Below KB might help:
> http://support.microsoft.com/defaul...sinternals.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 woul
d
> 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 t
o
> local drive), then you will need to modify the above (with respect to wher
e
> 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/
> http://www.sqlug.se/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>
>
BackUps not deleting
I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
have set it to delete files older than 1 day. This used to work, but now just
backs up without deleting the old files.
Any help would be appreciated,
CraigBelow 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/
http://www.sqlug.se/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
> have set it to delete files older than 1 day. This used to work, but now just
> backs up without deleting the old files.
> Any help would be appreciated,
> Craig|||I also have the same problem. Our maintenance plan was working however now it
doesnt.
The Service Account has full control to the backup location and nothing is
running at the same time as the backup. Any ideas?
Andrew
"Tibor Karaszi" wrote:
> 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/
> http://www.sqlug.se/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
> >I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
> > have set it to delete files older than 1 day. This used to work, but now just
> > backs up without deleting the old files.
> >
> > Any help would be appreciated,
> > Craig
>
>
have set it to delete files older than 1 day. This used to work, but now just
backs up without deleting the old files.
Any help would be appreciated,
CraigBelow 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/
http://www.sqlug.se/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
> have set it to delete files older than 1 day. This used to work, but now just
> backs up without deleting the old files.
> Any help would be appreciated,
> Craig|||I also have the same problem. Our maintenance plan was working however now it
doesnt.
The Service Account has full control to the backup location and nothing is
running at the same time as the backup. Any ideas?
Andrew
"Tibor Karaszi" wrote:
> 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/
> http://www.sqlug.se/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
> >I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
> > have set it to delete files older than 1 day. This used to work, but now just
> > backs up without deleting the old files.
> >
> > Any help would be appreciated,
> > Craig
>
>
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.
>>
>>
>
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/defaul...2&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...[
vbcol=seagreen]
> 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 clean
up 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 delete
d backups for around 40
> databases.
> Thanks a lot.
> Imran.
>[/vbcol]|||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/defaul...sinternals.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 m
aint 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...[
vbcol=seagreen]
> 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 ho
lding 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 i
n message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>[/vbcol]|||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/defaul...2&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...[
vbcol=seagreen]
> 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 clean
up 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 delete
d backups for around 40
> databases.
> Thanks a lot.
> Imran.
>[/vbcol]|||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/defaul...sinternals.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 m
aint 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...[
vbcol=seagreen]
> 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 ho
lding 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 i
n message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>[/vbcol]|||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 explained
Beginner's query.
please explain the OFA (open file agent) or lock rule when databases are being backed up.
I know files cannot be backed up if open, but what about database tables-not metadata, but data.
and what about the images accessed by a database-the reports or the docuemnts-they are backed up separately?
where can i find some basic rules for DB's...Short of DB's for dummies.Databases can be backed up while on-line.
Don't try to copy the .mdf/.ldf files - they won't be restorable probably - see backup database in bol.|||What concequences (if any) does open file agent or perhaps locks in this case have when backups are in operation and records are being updated?
For example on NT a file will not be backed up if open. I know the mdf and ldf files (or is it trn also) take logs, and snapshots for transactions, so that db's can be restored to a past point in time (rollback?). i know that bak files can be copied and used to create a database (restore maybe), but what about in db's?
Or is it simply that at that moment a backup is being written to file, and if the transaction is not fully committed prior to or at that time, it is not backed up, but will be included in the next back up...|||mdf file is the database file
ldf is the log file
bak is the database backup
trn is the transaction log file backup
It is not advisable to restore the db based on the mdf and ldf files - as they may be open at the time of backup. The only way to be sure you can restore is to use the bak and trn files. These will also take care of database locking and incomplete transactions at the time of the backup.|||Any pages updated while the backup is taking place are marked and written again to the end of the backup file.
Enough of the transaction log is backed up to allow a restore. Uncommitted transactions are rolled back at the restore.
The backup will slow down all processes on the server but will not stop any activity on the database.|||thanks all
appreciate your time :)
have just been on a sql 2000 admin course so it all sounds alot smipler now!
cheers
please explain the OFA (open file agent) or lock rule when databases are being backed up.
I know files cannot be backed up if open, but what about database tables-not metadata, but data.
and what about the images accessed by a database-the reports or the docuemnts-they are backed up separately?
where can i find some basic rules for DB's...Short of DB's for dummies.Databases can be backed up while on-line.
Don't try to copy the .mdf/.ldf files - they won't be restorable probably - see backup database in bol.|||What concequences (if any) does open file agent or perhaps locks in this case have when backups are in operation and records are being updated?
For example on NT a file will not be backed up if open. I know the mdf and ldf files (or is it trn also) take logs, and snapshots for transactions, so that db's can be restored to a past point in time (rollback?). i know that bak files can be copied and used to create a database (restore maybe), but what about in db's?
Or is it simply that at that moment a backup is being written to file, and if the transaction is not fully committed prior to or at that time, it is not backed up, but will be included in the next back up...|||mdf file is the database file
ldf is the log file
bak is the database backup
trn is the transaction log file backup
It is not advisable to restore the db based on the mdf and ldf files - as they may be open at the time of backup. The only way to be sure you can restore is to use the bak and trn files. These will also take care of database locking and incomplete transactions at the time of the backup.|||Any pages updated while the backup is taking place are marked and written again to the end of the backup file.
Enough of the transaction log is backed up to allow a restore. Uncommitted transactions are rolled back at the restore.
The backup will slow down all processes on the server but will not stop any activity on the database.|||thanks all
appreciate your time :)
have just been on a sql 2000 admin course so it all sounds alot smipler now!
cheers
Subscribe to:
Posts (Atom)