Mainteneance plans BAK TRN

Hi All,
Ive created a maintenance plan which does the following (all in one plan)
* All Databases
* Reorganise data,
Change free space per page percentage to 10%
Remove unused space from database files
Shrink database when it grows beyond 50 Mb
Amound of free space to remain after shrink 10%
*Check database integrity
include indexes
*Back up the database as part of the maintenance plan
Verify the integrity of the database upon completion
Backup to Disk
Use this Directory
Remove files older than 5 days
Backup file extension BAK
*Back up the Transaction log as part of the maintenance plan
Verify the integrity of the database upon completion
Backup to Disk
Use this Directory
Remove files older than 5 days
Backup file extension TRN
*Write a report file in the directory
delete report files after 14 days
We also have one other plan which simply checks the integrity on all DBs
once a week. Does this solution / plan seem ok? Its the first one I have
created so Im not 100% on it.
Also what rights/user does the plan run under?
I find that sometimes it deletes the files over 5 days and then sometimes it
just doesnt, or it will delete the TRN files but not the BAK files or vice
versa. I cant really make sense of it. Any ideas?
Also would I be better off having seperate plans for removing the BAK and
the TRN files?
Any help would be appreciated.
Thank youAdrin,
If you are using Sql Server 2000, then check your script and please
specify any pattern that you see in not removing old files.
You should be able to remove old files without any issues.
In Sql 2005 removing old files from sub-directory was not possible.
Even that is fixed after applying SP1.
Hope this helps,
Sameer Raval
[DBA-Developer]
Augusta,GA,USA
"Adrian" wrote:
> Hi All,
> Ive created a maintenance plan which does the following (all in one plan)
> * All Databases
> * Reorganise data,
> Change free space per page percentage to 10%
> Remove unused space from database files
> Shrink database when it grows beyond 50 Mb
> Amound of free space to remain after shrink 10%
> *Check database integrity
> include indexes
> *Back up the database as part of the maintenance plan
> Verify the integrity of the database upon completion
> Backup to Disk
> Use this Directory
> Remove files older than 5 days
> Backup file extension BAK
> *Back up the Transaction log as part of the maintenance plan
> Verify the integrity of the database upon completion
> Backup to Disk
> Use this Directory
> Remove files older than 5 days
> Backup file extension TRN
> *Write a report file in the directory
> delete report files after 14 days
> We also have one other plan which simply checks the integrity on all DBs
> once a week. Does this solution / plan seem ok? Its the first one I have
> created so Im not 100% on it.
> Also what rights/user does the plan run under?
> I find that sometimes it deletes the files over 5 days and then sometimes it
> just doesnt, or it will delete the TRN files but not the BAK files or vice
> versa. I cant really make sense of it. Any ideas?
> Also would I be better off having seperate plans for removing the BAK and
> the TRN files?
> Any help would be appreciated.
> Thank you|||In addition, I've seen a plan stopping execution if something goes wrong. This can lead to old
backup files not being removed. Read the report file carefully. Also, don't have databases in simple
recovery mode in a plan where you do log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Raval" <SameerRaval@.discussions.microsoft.com> wrote in message
news:ED2CC0C2-6E36-4AA7-A6B4-8BD0AFD05934@.microsoft.com...
> Adrin,
> If you are using Sql Server 2000, then check your script and please
> specify any pattern that you see in not removing old files.
> You should be able to remove old files without any issues.
> In Sql 2005 removing old files from sub-directory was not possible.
> Even that is fixed after applying SP1.
> Hope this helps,
>
> Sameer Raval
> [DBA-Developer]
> Augusta,GA,USA
>
> "Adrian" wrote:
>> Hi All,
>> Ive created a maintenance plan which does the following (all in one plan)
>> * All Databases
>> * Reorganise data,
>> Change free space per page percentage to 10%
>> Remove unused space from database files
>> Shrink database when it grows beyond 50 Mb
>> Amound of free space to remain after shrink 10%
>> *Check database integrity
>> include indexes
>> *Back up the database as part of the maintenance plan
>> Verify the integrity of the database upon completion
>> Backup to Disk
>> Use this Directory
>> Remove files older than 5 days
>> Backup file extension BAK
>> *Back up the Transaction log as part of the maintenance plan
>> Verify the integrity of the database upon completion
>> Backup to Disk
>> Use this Directory
>> Remove files older than 5 days
>> Backup file extension TRN
>> *Write a report file in the directory
>> delete report files after 14 days
>> We also have one other plan which simply checks the integrity on all DBs
>> once a week. Does this solution / plan seem ok? Its the first one I have
>> created so Im not 100% on it.
>> Also what rights/user does the plan run under?
>> I find that sometimes it deletes the files over 5 days and then sometimes it
>> just doesnt, or it will delete the TRN files but not the BAK files or vice
>> versa. I cant really make sense of it. Any ideas?
>> Also would I be better off having seperate plans for removing the BAK and
>> the TRN files?
>> Any help would be appreciated.
>> Thank you|||Hey guys,
sorry for the late response I made some changes and wanted to wait a gew
days to see the results.
I changed all dbs to full recovery mode this seemed to fix the problem with
the BAK files and now they are getting backed up and removed successfully
after 5 days.
however the TRN files are still not being removed, I get the following
errors in my log file.
Backup can not be performed on database 'master'. This sub task is ignored.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'Backup All DBs' on 01/08/2006 00:00:44
SQLMAINT.EXE Process Exit Code: 1 (Failed)
On different days I seem to get different dbs showing the same type of error
Backup can not be performed on database 'msdb'. This sub task is ignored.
Backup can not be performed on database 'pubs'. This sub task is ignored.
Any suggestions?|||You really need two separate plans. One for the databases for which you intend to do log backups,
and one for the databases that you don't intent to do log backups.
You cannot do log backups on master regardless of recovery model.
Agent will set msdb to simple recovery on startup.
Why do you backup pubs?
--
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:97F75DCF-B984-4644-BD93-DE4798C81B4B@.microsoft.com...
> Hey guys,
> sorry for the late response I made some changes and wanted to wait a gew
> days to see the results.
> I changed all dbs to full recovery mode this seemed to fix the problem with
> the BAK files and now they are getting backed up and removed successfully
> after 5 days.
> however the TRN files are still not being removed, I get the following
> errors in my log file.
> Backup can not be performed on database 'master'. This sub task is ignored.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'Backup All DBs' on 01/08/2006 00:00:44
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> On different days I seem to get different dbs showing the same type of error
> Backup can not be performed on database 'msdb'. This sub task is ignored.
> Backup can not be performed on database 'pubs'. This sub task is ignored.
> Any suggestions?
>|||Hi Tibor,
Im not quite sure I understand, I thought it was good practice to backup and
log all database's?
Am I right now in thinking that I should not backup or log the system
databases
Master, model, msdb, pubs, tempdb ?
Does my initial plan look ok, if I remove the system databases and just have
it for our business database's?
Thanks|||You should always do a FULL backup on Master, Model & MSDB but there is no
need for Log backups since they are either in Simple mode to begin with or
in the case of Model there are no changes made. Tempdb is rebuilt from
scratch each time you start up SQL Server so there is no need to back it up.
--
Andrew J. Kelly SQL MVP
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:12366730-7681-43DB-80BE-A838D1188209@.microsoft.com...
> Hi Tibor,
> Im not quite sure I understand, I thought it was good practice to backup
> and
> log all database's?
> Am I right now in thinking that I should not backup or log the system
> databases
> Master, model, msdb, pubs, tempdb ?
> Does my initial plan look ok, if I remove the system databases and just
> have
> it for our business database's?
> Thanks
>|||Thanks Andrew,
I have created a third Maintenance plan to back up the system dbs, Master,
Msdb and model.
I have also changed the Initial Plan to backup all user databases, I will
check this tomorrow and see the results.
"Andrew J. Kelly" wrote:
> You should always do a FULL backup on Master, Model & MSDB but there is no
> need for Log backups since they are either in Simple mode to begin with or
> in the case of Model there are no changes made. Tempdb is rebuilt from
> scratch each time you start up SQL Server so there is no need to back it up.
> --
> Andrew J. Kelly SQL MVP
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:12366730-7681-43DB-80BE-A838D1188209@.microsoft.com...
> > Hi Tibor,
> >
> > Im not quite sure I understand, I thought it was good practice to backup
> > and
> > log all database's?
> >
> > Am I right now in thinking that I should not backup or log the system
> > databases
> > Master, model, msdb, pubs, tempdb ?
> >
> > Does my initial plan look ok, if I remove the system databases and just
> > have
> > it for our business database's?
> >
> > Thanks
> >
>
>|||Adrian wrote:
> Thanks Andrew,
> I have created a third Maintenance plan to back up the system dbs, Master,
> Msdb and model.
> I have also changed the Initial Plan to backup all user databases, I will
> check this tomorrow and see the results.
>
Sounds like you have a solution, but if you decide that managing three
seperate maintenance plans is too much work, have a look at this script
of mine:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
Tracy McKibben
MCDBA
http://www.realsqlguy.com