| Author |
Topic |
|
mikebird
Aged Yak Warrior
529 Posts |
 Posted - 2007-09-12 : 12:32:25
|
| Such a simple task. Not doing as it should!!This Maintenance Cleanup Task is set to delete all BAK and TRN files it's made (in seperate maintenance plans) in given path, with the given file extension, delete files based on the age of the file at task run time. Delete files older that 4 days.The files are now backing up for months and months. I'm not going to take care of this. I've got a computer to do this for me, every time it runs its jobs, every 4 hours and once overnight.This is ignoring commands and refusing to do as it's told. I've checked the settings in here over and over. It's so simple - what could be wrong? I've checked the path, the file age, the extensions... The disks are getting full!Has anyone seen anything like this? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-12 : 12:49:23
|
| Add this to the list of why I don't use maintenance plans.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-12 : 14:10:21
|
quote:
Originally posted by mikebird...This is ignoring commands and refusing to do as it's told...
It's a poor workman who blames his tools.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-12 : 14:19:10
|
| "Has anyone seen anything like this?"Yup, none of the servers we look after which have maintenance plans delete their TRN files.I have heard that this happens when you Modify a maintenance plan (i.e. deleting and recreating it will solve the problem).My thought was that it might be related to a "Backup all databases" approach and then having mixture of FULL and SIMPLE recovery module databases.Either way its a bug - and I think you are right to blame the tools (FWIW the maintenance plans work a lot better in SQL 2005, plus they have more configurable control over the various tasks)Kristen |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-12 : 14:29:30
|
quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by mikebird...This is ignoring commands and refusing to do as it's told...
It's a poor workman who blames his tools.CODO ERGO SUM
Maybe true in the world of construction, but with job automation.. not so much.
Future guru in the making. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-12 : 14:52:57
|
quote:
Originally posted by Kristen
"Has anyone seen anything like this?"Yup, none of the servers we look after which have maintenance plans delete their TRN files.I have heard that this happens when you Modify a maintenance plan (i.e. deleting and recreating it will solve the problem).My thought was that it might be related to a "Backup all databases" approach and then having mixture of FULL and SIMPLE recovery module databases.Either way its a bug - and I think you are right to blame the tools (FWIW the maintenance plans work a lot better in SQL 2005, plus they have more configurable control over the various tasks)Kristen
Since this is a 2005 forum, my assumption was that this is a 2005 maintenance plan.CODO ERGO SUM |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-12 : 14:56:59
|
quote:
Originally posted by Zoroaster
quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by mikebird...This is ignoring commands and refusing to do as it's told...
It's a poor workman who blames his tools.CODO ERGO SUM
Maybe true in the world of construction, but with job automation.. not so much.
Future guru in the making.
I have hundreds of maintenance plans running for both 2000 and 2005 that are not having trouble with this.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-12 : 14:58:52
|
| "Since this is a 2005 forum, my assumption was that this is a 2005 maintenance plan."Missed that, sorry. I agree that SQL 2005 Maintenance Plans appear to be more robust. But I've had only limited exposure to them as the poor state of the SQL 2000 Maintenance Plans caused us to build a complete replacement system, which seems to work just fine under SQL 2005!Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-12 : 15:10:17
|
| There are a lot more options on the 2005 maintanance plans. So many, in fact, that it is almost as much trouble to use as setting up your own process.For 2005, make sure that you have the latest service pack installed. There were some serious problems with maintenance plans fixed in that service pack.Make sure you enter the file extentions for files to be deleted in the dialog box like this:TRNBAKTXTnot like:.TRN.BAK.TXTCODO ERGO SUM |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-12 : 15:14:27
|
quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by Zoroaster
quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by mikebird...This is ignoring commands and refusing to do as it's told...
It's a poor workman who blames his tools.CODO ERGO SUM
Maybe true in the world of construction, but with job automation.. not so much.
Future guru in the making.
I have hundreds of maintenance plans running for both 2000 and 2005 that are not having trouble with this.CODO ERGO SUM
So, do you advocate the use of Maintenance plans vs scripting it out? I am always interested in hearing the practices of experienced DBA's.
Future guru in the making. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-12 : 15:22:24
|
| He's pro-maintenance plans. I'm anti. Take for instance the defragmentation one. It reindexes all indexes regardless if they need it done or not.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-12 : 17:47:18
|
quote:
Originally posted by tkizer
He's pro-maintenance plans. I'm anti. Take for instance the defragmentation one. It reindexes all indexes regardless if they need it done or not.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
I understand, although many of the books I am reading state that it is a good idea to use the maintenance plan wizard to create the plan and then you can modify the jobs manually afterwards for the best of both worlds (speed and flexibility). Do you see any issues with that approach?
Future guru in the making. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-12 : 18:00:10
|
| I don't see how modifying the jobs would help. Speed and flexibility? What part is faster and more flexible with maintenance plans?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-12 : 18:35:14
|
| I’m not really pro maintenance plans, or anti-custom maintenance procedures. Actually, I use both.I use maintenance plans for the ordinary things that they will cover because they are easy to setup, but I used specialized procedures that have more flexibility for certain situations:1. Re-indexing only some tables in a database or all tables except for certain tables.2. Defragmenting only some tables in a database or all tables except for certain tables.3. Updating statistics on only some tables in a database or all tables except for certain tables.4. Running DBCC checks on some or all databases or all databases except for certain databases. I actually never use maintenance plans for DBCC checks.5. Shrinking database files or logs.6. Running backups to multiple output files.7. Running differential backups to time-stamped files. They added this feature in SQL Server 2005 maintenance plans, but it was not available in SQL Server 2000.I have a utility database that I put on each server where I keep these procedures and others that need to be available on each server. I've probably sent just as much time developing these procedures as Tara has spent on the maintenance procedures posted on her blog, so the reason I use maintenance plans is not because I'm too lazy to write my own procedures. I just find them more convenient to use for a lot of the common situations, and I haven’t experienced problems with them for the things I use them for.The SQL Server 2005 maintenance plans allow you select specific tables for re-indexing or defragmenting, but they don't allow you to re-index all tables except certain tables. I often want to defragment and update statistics on large tables, re-index smaller tables, and take no action on other tables. I think a procedure that allows you to specify a list of tables to include or exclude is very useful for this setup.There are a lot of improvements in the SQL Server 2005 maintenance plans that allow more control, but that flexibility makes them a bit more trouble to tweak. I think it is best to do the initial setup with the wizard and tweak as needed. The xp_delete_file stored procedure that was added to support more flexible file cleanup is actually very useful by itself, and can be used in custom procedures for this. Another interesting new feature is the ability of maintenance tasks to connect to other servers to perform maintenance actions there.CODO ERGO SUM |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-12 : 22:49:19
|
| Which fix level are you in? It should be able to delete backup files, may have problem to delete plan log files. Fix 3159 suppose to address that issue. |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-12 : 23:32:11
|
quote:
Originally posted by tkizer
I don't see how modifying the jobs would help. Speed and flexibility? What part is faster and more flexible with maintenance plans?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
It's not that they are more flexible, just faster (for most) then creating from scratch ( an alternative of course is using the pre written scripts you created which are fantastic! ). What I meant to say is that someone can put together a plan quickly using the wizard and then modify/tweak it for flexibility. In any case the former DBA in our organization put together documentation for the database operators to use the maintenance plan wizard to automate tasks, this is now deployed at 100's of sites successfully so it's not something I will likely be changing. At least not yet, but I always like to research things exhaustively (as time permits ) 
Future guru in the making. |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-12 : 23:33:07
|
quote:
Originally posted by Michael Valentine Jones
I’m not really pro maintenance plans, or anti-custom maintenance procedures. Actually, I use both.I use maintenance plans for the ordinary things that they will cover because they are easy to setup, but I used specialized procedures that have more flexibility for certain situations:1. Re-indexing only some tables in a database or all tables except for certain tables.2. Defragmenting only some tables in a database or all tables except for certain tables.3. Updating statistics on only some tables in a database or all tables except for certain tables.4. Running DBCC checks on some or all databases or all databases except for certain databases. I actually never use maintenance plans for DBCC checks.5. Shrinking database files or logs.6. Running backups to multiple output files.7. Running differential backups to time-stamped files. They added this feature in SQL Server 2005 maintenance plans, but it was not available in SQL Server 2000.I have a utility database that I put on each server where I keep these procedures and others that need to be available on each server. I've probably sent just as much time developing these procedures as Tara has spent on the maintenance procedures posted on her blog, so the reason I use maintenance plans is not because I'm too lazy to write my own procedures. I just find them more convenient to use for a lot of the common situations, and I haven’t experienced problems with them for the things I use them for.The SQL Server 2005 maintenance plans allow you select specific tables for re-indexing or defragmenting, but they don't allow you to re-index all tables except certain tables. I often want to defragment and update statistics on large tables, re-index smaller tables, and take no action on other tables. I think a procedure that allows you to specify a list of tables to include or exclude is very useful for this setup.There are a lot of improvements in the SQL Server 2005 maintenance plans that allow more control, but that flexibility makes them a bit more trouble to tweak. I think it is best to do the initial setup with the wizard and tweak as needed. The xp_delete_file stored procedure that was added to support more flexible file cleanup is actually very useful by itself, and can be used in custom procedures for this. Another interesting new feature is the ability of maintenance tasks to connect to other servers to perform maintenance actions there.CODO ERGO SUM
Michael, thank you for your detailed response, good information!
Future guru in the making. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-13 : 05:34:52
|
| "So, do you advocate the use of Maintenance plans vs scripting it out? I am always interested in hearing the practices of experienced DBA's"The problem with making your own is proving that they work. That takes time, and thus your boss may have other things s/he wants you to use your time for.The problem with maintenance plans is that:a) One size fits allb) When they fail it is very hard to work out what actually is causing the problem - the "sealed black box" problem.My take would be:For anyone new to this use Maintenance Plans (assuming SQL2005, the Maintenance Plans in SQL 2000 had lots of problems associated with them)If you discover you need finer control then consider building your own, perhaps just for those parts - e.g. reindexing SOME of the indexes, rather than AllAll our production servers use our own maintenance routines. They typically Zip up the Backup files and FTP them to another server, so we are a bit more safe than just relying on the daily tape backup. (PLus they are tunable to which indexes to rebuild etc., and we have an Sproc that DEVs can use to make a backup, which we are sure is in the right folder and doesn't get deleted by some well meaning "But it was only a temporary backup" which broke the backup chain!)But for "integration servers" (e.g. a box in the client's office that "collects" data from his other systems, in readiness to pass to the Web's (production) server we don't really care about robust backups. All the data can be recreated, so a Full backup from the last time the system changed will do. So running a Maintenance Plan is good enough. And that avoids us having to set up an Admin database, install all our Backup Utilities, and so on, and have the hassle when it breaks! ("That's a problem with the MS Maintenance Plan, sorry fixing it is chargeable" doesn't work as well with "That's our Admin database which is broken")Kristen |
|
|
|
|
Backup Jobs Not Deleting Old Files!