another reason why backup files do not get deleted

Hello:
A couple of times on this board, I posed the question as to why expired
backup files may not get deleted from a server folder automatically despite
the fact that the maintenance plan in SQL 2000 tells the server to delete
files older than x number of days.
I want to thank everyone for pointing me to that really good article by Bill
Hollinshead. The part of the article about reading the maintenance plan's
history report solved the problem for me.
You see, by reading the report at my client site, I found a third reason as
to why backup files do not get deleted. (The first two reasons are
Permissions and Sharing Violation.)
The client did not have enough space on his server's hard drive. Therefore,
the last few databases in the list of databases were not getting deleted.
So, here is what a SQL maintenance plan does in this case where there is not
enough space and the backups of those last few databases terminate abnormally
therefore.
The maintenace plan quits and does not bother deleting the expired backups.
basically, if the server has run out of space, the maintenance plan does not
try to delete expired backups!
In a way, this should be added as a third reason as to why backup files do
not get deleted!
Thanks, again!
childofthe1980sThanks for reporting back to us. This is essentially why I would like a "continue on error" option.
Basically, this is the same as connect issue 125025. Re-reading this report, I now see that they are
considering it for sp2, so it might be worth checking if they implemented such an option...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:B8454FDE-F33B-4BAD-BF70-7C18261A4677@.microsoft.com...
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despite
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bill
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason as
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefore,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is not
> enough space and the backups of those last few databases terminate abnormally
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups.
> basically, if the server has run out of space, the maintenance plan does not
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s|||On Feb 8, 10:15 pm, childofthe1980s
<childofthe19...@.discussions.microsoft.com> wrote:
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despite
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bill
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason as
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefore,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is not
> enough space and the backups of those last few databases terminate abnormally
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups.
> basically, if the server has run out of space, the maintenance plan does not
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s
This is one argument I have for NOT using the maintenance plans - you
have no control over the inner workings. I'd encourage everyone to
build their OWN maintenance routines. Write your own backup scripts,
your own reindex scripts, your own cleanup scripts. I have examples
of all three here:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html
http://realsqlguy.blogspot.com/2007/02/cleaning-up-old-files.html
http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html