Maintinace Plan 2005

I have created a DB mantiance plan for SQL 2005 had no luck setuping diffren
t
schudle for diffirent task in the same DB manitance plan .
e.g: Daily backup for the Full Backup
Houly backup for the trx log
As we used to do in sql2000
any help please
Thanks,
RagaeiYou can't. You need multiple plans for that. Now each plan is a complete
package due to the work flow. Where as before it created different jobs for
each section of the plan.
Andrew J. Kelly SQL MVP
"RS" <RS@.discussions.microsoft.com> wrote in message
news:9B2554CB-5051-4BF7-92DF-86F59D300251@.microsoft.com...
>I have created a DB mantiance plan for SQL 2005 had no luck setuping
>diffrent
> schudle for diffirent task in the same DB manitance plan .
> e.g: Daily backup for the Full Backup
> Houly backup for the trx log
> As we used to do in sql2000
> any help please
> Thanks,
> Ragaei
>

Maintinace Plan 2005

I have created a DB mantiance plan for SQL 2005 had no luck setuping diffrent
schudle for diffirent task in the same DB manitance plan .
e.g: Daily backup for the Full Backup
Houly backup for the trx log
As we used to do in sql2000
any help please
Thanks,
RagaeiYou can't. You need multiple plans for that. Now each plan is a complete
package due to the work flow. Where as before it created different jobs for
each section of the plan.
--
Andrew J. Kelly SQL MVP
"RS" <RS@.discussions.microsoft.com> wrote in message
news:9B2554CB-5051-4BF7-92DF-86F59D300251@.microsoft.com...
>I have created a DB mantiance plan for SQL 2005 had no luck setuping
>diffrent
> schudle for diffirent task in the same DB manitance plan .
> e.g: Daily backup for the Full Backup
> Houly backup for the trx log
> As we used to do in sql2000
> any help please
> Thanks,
> Ragaei
>

Maintenence Plan Questions

I wanted to know at what time should I be scheduling the data optimization and data integrity jobs? Will these jobs hinder performance? If so then I should probably schedule the jobs after work hours.

Also, we were not going to use the backup/recovery jobs that sql server offer, we have our own backup software. So, will the data integrity or data optimization job affect backups? Should I perform these jobs before or after backups?

Thanks,
LauraThat is a good plan. The optimizations are NORMALLY non-intrusive, but particularly the first time they are run after significant changes they can hit the database like a freight train.

Your plan to start the optimizations after the backup finishes is a good one.

-PatP|||I would recommend not to use 3rd-party backup tools (unless it's SQLLiteSpeed) to backup live database/transaction log. Use native or purchase SQLLiteSpeed to create OS backup files, and only then use other tools to backup to a tape, for example.|||Ok thanks for the responses, it helped me alot

-laura

Maintenence plan backup schedule doesnt work

I am running the latest MSDE with the SQL Server 2000 client tools onWindows 2K Server on my production server. I have a simliar setup on mydevelopment machine except it is running Windows 2K (non-server). Ihave the same issue on both machines (I am wondering if it is relatedto the structure of the database). When I open Enterprise Manager,connect to the server, navigate to the Management folder, and add a newmanagement plan, I experience the following error.
I create a management plan that includes nothing but a completedatabase backup (i.e. no reorganization of index pages, logging, etc.).I set the backup schedule to occur every Sun at 12:00AM and hit OK.Sometimes I get the following error :"Error 8114: Error converting datatype int to tinyint" sometimes not. Either way, though, the same thinghappens, the schedule for the backup is not saved. If I reopen themanagement plan and go to the "Complete Backup" tab, there is noschedule in the schedule box.
I have found this link:
http://www.technologyone.org/new-4581847-3733.html
Which appears to be the same issue that I am having, however, there isno resolution there. I was wondering if anyone had any ideas.
Jason
just a quick dirty question: can you perform a normal backup? Is there a job for the backup? If so will this force run?|||Yes, I can perform manual backups without a problem.And, in fact, I can successfully create a maintenence plan withschedules for things like "Index pages" etc. And these schedules worksuccessfully. However, I am only interested in making a maintenenceplan for complete backups on a schedule and that does not work. Oneworkaround that would probably work is to just use the SQL ServerBackup dialog and set a schedule in there. However, this does not allowme the flexibility I require to manage the backups, such as saving thefiles with their dates appended and triaging the backups into seperatefolders for each database. It also does not allow any extensibility fordown the line when I would like to automate the cleaning andoptimization tasks as well as logging.Finally, I think I should alsomention, that I do have SQL Server Agent running, because this isrequired for the schedules to work correctly.
Jason
|||Have you thought of writing back-up scripts for these processes. Thesecould then be controlled by DTS or if you really have to xp_cmdshell?
|||I have thought about it, but have decided that my efforts are betterspent getting the maintenence plan working for several reasons. Firstoff, all I need is a basic backup and I do not require the flexibilitythat custom scripts would provide (nor do I need the overhead ofmaintaining said scripts). Lastly, the maintenance plan SHOULD work, Ijust have to figure out why it is not working rather than determining aworkaround.
Jason
|||

ok, just is case you do want a script here's one you could use.
--Declare variables

declare @.BACKUP varchar (100)
declare @.day varchar(50) -- date dd/mm/yyyy
declare @.hour varchar (4) -- hour hh
declare @.copy varchar (200) -- copy statement to where ever you want

--set day This is used for the day FULL backup
set @.day = convert(varchar(50), getdate(), 103)
set @.day =replace (@.day, '/', '_')

--set hour This is used for the LOG transaction backups Not needed for full backup
--set @.hour = datepart(hour, getdate())
--set the name of the backup
set @.BACKUP = location of backup i.e. 'E:\data\MSSQL\BACKUP\Northwind\Northwind_'+@.day+'.bak'
--backup database SQL
backup database Northwind todisk=@.BACKUP with stats
--copy the file to where
set @.copy = 'copy'+@.BACKUP+ location'\\servername\drive$\folder\

Maintenence Plan

We are running SQL server 2000 on Windows 2000 and have a
number of servers. We have set up scheduled backup
maintenence plans which are set to back our databases,
transaction logs to disk then delete the transaction logs
and backup files after so many days. The backups are
always ok but For some reason the Transaction logs and
backup files are not being deleted which causes the disk
to fill up and databases to crash. We have checked the
permissions on the backup folders to see if the SQL
service account might not have had permissions set
correctly but this is not the case. Can anyone help with
this.BigA,
Can you verify that a tape backup job does not have an open file lock on
the files? I have had this problem and the culprit was a tape backup job
copying the files to tape, and leaving the files in a "open" state.
sqlmaint is then unable to delete the files and "fails".
There are issues with MSDE, so check that you are not using MSDE.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
BigA wrote:
> We are running SQL server 2000 on Windows 2000 and have a
> number of servers. We have set up scheduled backup
> maintenence plans which are set to back our databases,
> transaction logs to disk then delete the transaction logs
> and backup files after so many days. The backups are
> always ok but For some reason the Transaction logs and
> backup files are not being deleted which causes the disk
> to fill up and databases to crash. We have checked the
> permissions on the backup folders to see if the SQL
> service account might not have had permissions set
> correctly but this is not the case. Can anyone help with
> this.|||We use Veritas Backup Exec and it's does backup open
files with a lock. So could this be the culprit? Would
this only happen if the tape backup was at the same time
as the Maintenence plan?
>--Original Message--
>BigA,
>Can you verify that a tape backup job does not have an
open file lock on
>the files? I have had this problem and the culprit was a
tape backup job
>copying the files to tape, and leaving the files in
a "open" state.
>sqlmaint is then unable to delete the files and "fails".
>There are issues with MSDE, so check that you are not
using MSDE.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>BigA wrote:
have a[vbcol=seagreen]
logs[vbcol=seagreen]
disk[vbcol=seagreen]
the[vbcol=seagreen]
with[vbcol=seagreen]
>.
>|||BigA,
Yes, this is likely the culprit. Check your tape back windows do not
overlap your SQL Server backup jobs. The problem should only occur if
the maintenance plan is trying to delete a file whilst backup exec is
copying it to tape.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
BigA wrote:
> We use Veritas Backup Exec and it's does backup open
> files with a lock. So could this be the culprit? Would
> this only happen if the tape backup was at the same time
> as the Maintenence plan?
>|||Thanks for your fast response. I will give this a try.
sounds like it could be the answer.
>--Original Message--
>BigA,
>Yes, this is likely the culprit. Check your tape back
windows do not
>overlap your SQL Server backup jobs. The problem should
only occur if
>the maintenance plan is trying to delete a file whilst
backup exec is
>copying it to tape.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>BigA wrote:
Would[vbcol=seagreen]
time[vbcol=seagreen]
>.
>

Maintenence Plan

We are running SQL server 2000 on Windows 2000 and have a
number of servers. We have set up scheduled backup
maintenence plans which are set to back our databases,
transaction logs to disk then delete the transaction logs
and backup files after so many days. The backups are
always ok but For some reason the Transaction logs and
backup files are not being deleted which causes the disk
to fill up and databases to crash. We have checked the
permissions on the backup folders to see if the SQL
service account might not have had permissions set
correctly but this is not the case. Can anyone help with
this.BigA,
Can you verify that a tape backup job does not have an open file lock on
the files? I have had this problem and the culprit was a tape backup job
copying the files to tape, and leaving the files in a "open" state.
sqlmaint is then unable to delete the files and "fails".
There are issues with MSDE, so check that you are not using MSDE.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
BigA wrote:
> We are running SQL server 2000 on Windows 2000 and have a
> number of servers. We have set up scheduled backup
> maintenence plans which are set to back our databases,
> transaction logs to disk then delete the transaction logs
> and backup files after so many days. The backups are
> always ok but For some reason the Transaction logs and
> backup files are not being deleted which causes the disk
> to fill up and databases to crash. We have checked the
> permissions on the backup folders to see if the SQL
> service account might not have had permissions set
> correctly but this is not the case. Can anyone help with
> this.|||We use Veritas Backup Exec and it's does backup open
files with a lock. So could this be the culprit? Would
this only happen if the tape backup was at the same time
as the Maintenence plan?
>--Original Message--
>BigA,
>Can you verify that a tape backup job does not have an
open file lock on
>the files? I have had this problem and the culprit was a
tape backup job
>copying the files to tape, and leaving the files in
a "open" state.
>sqlmaint is then unable to delete the files and "fails".
>There are issues with MSDE, so check that you are not
using MSDE.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>BigA wrote:
>> We are running SQL server 2000 on Windows 2000 and
have a
>> number of servers. We have set up scheduled backup
>> maintenence plans which are set to back our databases,
>> transaction logs to disk then delete the transaction
logs
>> and backup files after so many days. The backups are
>> always ok but For some reason the Transaction logs and
>> backup files are not being deleted which causes the
disk
>> to fill up and databases to crash. We have checked
the
>> permissions on the backup folders to see if the SQL
>> service account might not have had permissions set
>> correctly but this is not the case. Can anyone help
with
>> this.
>.
>|||BigA,
Yes, this is likely the culprit. Check your tape back windows do not
overlap your SQL Server backup jobs. The problem should only occur if
the maintenance plan is trying to delete a file whilst backup exec is
copying it to tape.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
BigA wrote:
> We use Veritas Backup Exec and it's does backup open
> files with a lock. So could this be the culprit? Would
> this only happen if the tape backup was at the same time
> as the Maintenence plan?
>|||Thanks for your fast response. I will give this a try.
sounds like it could be the answer.
>--Original Message--
>BigA,
>Yes, this is likely the culprit. Check your tape back
windows do not
>overlap your SQL Server backup jobs. The problem should
only occur if
>the maintenance plan is trying to delete a file whilst
backup exec is
>copying it to tape.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>BigA wrote:
>> We use Veritas Backup Exec and it's does backup open
>> files with a lock. So could this be the culprit?
Would
>> this only happen if the tape backup was at the same
time
>> as the Maintenence plan?
>.
>

Maintenence Plan

We are running SQL server 2000 on Windows 2000 and have a
number of servers. We have set up scheduled backup
maintenence plans which are set to back our databases,
transaction logs to disk then delete the transaction logs
and backup files after so many days. The backups are
always ok but For some reason the Transaction logs and
backup files are not being deleted which causes the disk
to fill up and databases to crash. We have checked the
permissions on the backup folders to see if the SQL
service account might not have had permissions set
correctly but this is not the case. Can anyone help with
this.
BigA,
Can you verify that a tape backup job does not have an open file lock on
the files? I have had this problem and the culprit was a tape backup job
copying the files to tape, and leaving the files in a "open" state.
sqlmaint is then unable to delete the files and "fails".
There are issues with MSDE, so check that you are not using MSDE.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
BigA wrote:
> We are running SQL server 2000 on Windows 2000 and have a
> number of servers. We have set up scheduled backup
> maintenence plans which are set to back our databases,
> transaction logs to disk then delete the transaction logs
> and backup files after so many days. The backups are
> always ok but For some reason the Transaction logs and
> backup files are not being deleted which causes the disk
> to fill up and databases to crash. We have checked the
> permissions on the backup folders to see if the SQL
> service account might not have had permissions set
> correctly but this is not the case. Can anyone help with
> this.
|||We use Veritas Backup Exec and it's does backup open
files with a lock. So could this be the culprit? Would
this only happen if the tape backup was at the same time
as the Maintenence plan?
>--Original Message--
>BigA,
>Can you verify that a tape backup job does not have an
open file lock on
>the files? I have had this problem and the culprit was a
tape backup job
>copying the files to tape, and leaving the files in
a "open" state.
>sqlmaint is then unable to delete the files and "fails".
>There are issues with MSDE, so check that you are not
using MSDE.[vbcol=seagreen]
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>BigA wrote:
have a[vbcol=seagreen]
logs[vbcol=seagreen]
disk[vbcol=seagreen]
the[vbcol=seagreen]
with
>.
>
|||BigA,
Yes, this is likely the culprit. Check your tape back windows do not
overlap your SQL Server backup jobs. The problem should only occur if
the maintenance plan is trying to delete a file whilst backup exec is
copying it to tape.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
BigA wrote:
> We use Veritas Backup Exec and it's does backup open
> files with a lock. So could this be the culprit? Would
> this only happen if the tape backup was at the same time
> as the Maintenence plan?
>
|||Thanks for your fast response. I will give this a try.
sounds like it could be the answer.
>--Original Message--
>BigA,
>Yes, this is likely the culprit. Check your tape back
windows do not
>overlap your SQL Server backup jobs. The problem should
only occur if
>the maintenance plan is trying to delete a file whilst
backup exec is[vbcol=seagreen]
>copying it to tape.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>BigA wrote:
Would[vbcol=seagreen]
time
>.
>

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

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. Th
is 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...[vbcol=seagreen]
> 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:
>|||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 inte
nd 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 wit
h
> 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 err
or
> 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 u
p.
> --
> Andrew J. Kelly SQL MVP
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:12366730-7681-43DB-80BE-A838D1188209@.microsoft.com...
>
>|||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/vie...realsqlguy.com

MaintenancePlan - task order

Hello,
I created one maintenance plan with one schedule, it includes several
tasks. How I can change order on with they are executed, graphical
moving task didn't change order.
--
Best regardsYou have lines with arrows between the tasks. They define the flow. Remove your current lines (the
one you need to re-arrange) and draw new lines, to your liking.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1194381069.963177.83560@.50g2000hsm.googlegroups.com...
> Hello,
> I created one maintenance plan with one schedule, it includes several
> tasks. How I can change order on with they are executed, graphical
> moving task didn't change order.
> --
> Best regards
>|||Hello Tibor :)
> You have lines with arrows between the tasks. They define the flow. Remove your current lines (the
> one you need to re-arrange) and draw new lines, to your liking.
Are you sure this working, I tray arrange flow between the tasks use
lines, but logs show me that tasks are executed on different order.
--
Best regards|||Yes, it should work. I can't say why it isn't working for you, except for guesses like your not
editing the correct package... Sorry :-(
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1194388800.134252.68680@.o38g2000hse.googlegroups.com...
> Hello Tibor :)
>> You have lines with arrows between the tasks. They define the flow. Remove your current lines
>> (the
>> one you need to re-arrange) and draw new lines, to your liking.
> Are you sure this working, I tray arrange flow between the tasks use
> lines, but logs show me that tasks are executed on different order.
> --
> Best regards
>
>|||You are right, it is working. Sorry and thanks, my mistake. I looked
at order on log files. SQL Server wrote on wrong order, but when I
looked at individual start and stop date for each tasks, everything is
on right order.
Thank for your help.
--
Regards,
anxcomp|||Glad you got it working.. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1194459342.715819.182790@.d55g2000hsg.googlegroups.com...
> You are right, it is working. Sorry and thanks, my mistake. I looked
> at order on log files. SQL Server wrote on wrong order, but when I
> looked at individual start and stop date for each tasks, everything is
> on right order.
> Thank for your help.
> --
> Regards,
> anxcomp
>

Maintenance Wizard

I'm trying to backup several databases to UNC folders. I'm able to do it
manually for each database, but when I set up the maintenance plan it fails
because SQL tries to use the SQL administrator account to push the data.

The million dollar question is how does one change which account is running
the maintenance?"GTM" <gmckaigeATworkerstempDOTcom> wrote in message
news:gJScnQFltqR2TcmiXTWJkw@.fdn.com...
> I'm trying to backup several databases to UNC folders. I'm able to do it
> manually for each database, but when I set up the maintenance plan it
fails
> because SQL tries to use the SQL administrator account to push the data.
>
> The million dollar question is how does one change which account is
running
> the maintenance?

Change the account the SQL Server Agent runs under to a domain account.
|||Hi

Gregs advice is probably the root cause of your problem if the account is
set to local system.

If it is already a local/domain account the the permissions to the
shares/directories/trusts etc... need to be in place.

John

"GTM" <gmckaigeATworkerstempDOTcom> wrote in message
news:gJScnQFltqR2TcmiXTWJkw@.fdn.com...
> I'm trying to backup several databases to UNC folders. I'm able to do it
> manually for each database, but when I set up the maintenance plan it
fails
> because SQL tries to use the SQL administrator account to push the data.
>
> The million dollar question is how does one change which account is
running
> the maintenance?

Maintenance utility acting on its own??

Hello

I've set up a maintenance plan for one of the SQL servers in the network I'm administering. When I checked the logs this morning, I was surprised to find that all backups had failed. I was about to throw my computer out the window, when I thought about actually checking the folders where the backup files are supposed to be. I was even more surprised to find that a succesful backup actually had taken place! It seems that this backup had occured about 10 minutes later than the one that failed. When I checked the jobs, they all seemed to have failed (Last Run Status was failed).

How is this possible? Is SQL Server running it's own backups, or is it possible that there's some backup job somewhere that is listed with the jobs for the server?

Any help and suggestions is very welcome.

MNJA lot of things can cause a job to fail, and it may fail partway through so you would see backups for many of your databases (or even all of your databases if it failed on a step after the backup).

Check to see what step you job failed on, and it is good practice to log the output from your backup job. Editing the step(s) that may cause trouble, go to the Advanced tab, and set the Output file location. You can also view previous output from here.

blindman|||Yea, it seems that some of the backups failed because the file sizes were too big, and therefore failed to back up across a network share. I still haven't figured out why those databases have been backed up anyway though, because they're all set to no retries and the log doesn't mention anything about a succesful backup of those DBs.

Now I've decided to do local backups instead.

MNJ|||Originally posted by kilobyte
Yea, it seems that some of the backups failed because the file sizes were too big, and therefore failed to back up across a network share. I still haven't figured out why those databases have been backed up anyway though, because they're all set to no retries and the log doesn't mention anything about a succesful backup of those DBs.

Now I've decided to do local backups instead.

MNJ

I found in my situation that the best thing to do is break out into 4 maint plans. I save them on the local disk and have an automatic delete of them after X number of days.

[list=1]
Get all the system databases in one plan w/ nightly backups. You can just default this one.
Get the critical and rapidly changing application database (the ones using transaction logs) as separate maint plan. The ones you may have to rollback to a point in time.
Get the semi-static databases as a third. Low users or mostly lookup data.
The 4th is for our WebSweeper DB. The thing is huge and we don't have a significant worry if we lose some of the web access info.
[/list=1]

I then depend on the nightly diferentials and weekly fulls to take care of long term recovery and use what is on the local disk for short term. At one point in time I did try to save them to a network location, but I consistently (at least once a week) would lose connectivity to that drive and my b/u were dead.

Another option, is publishing and distribution. If you have another server off-site or can get one with a decent bandwidth connection, you can publish your apps database(s) nightly to the other server. We had (it was only a P200 with 20GB HD and 1GB of RAM :( ) and will soon have again a SQL box at our recovery site that does nothing but tick over and catches the databases nightly. :) That makes life a lot easier from recovery planning.

I have found that most open file agents of b/u software are crap, in my opinion. If you are running out of room on your server's disk, take a look at the plans above. Cut transaction logs for only the most critical and changing databases. And in the maint plans I have described the backups for the critical databases are kept 4 days, but the transaction logs are only kept for 2 days. If need I can always go to tape earlier logs. But most times a major point-in-time restore is going to be less than 48 hours. I know the exception is on a Monday, but how often does it happen? I've had 4 PIT restores in the past 3 years and of those it has generally been to 6AM the current day.

The semi-static databases are kept local for 2 days and then are cycled out. The system databases, I just back up for grins. If the SQL SW crashes that hard, you'll probably just re-install SQL and then reattach your DB's in place. If it was the WinXX software, same thing.

Also when you set up your maint plans, have them do data integrity and DB shrink procedures daily. That will probably buy some disk space.

And sometimes, you just have to tell the boss "We need some more drives for this POS server." :) If he doesn't listen, just smile during the autopsy after the crash, and say "I told you so."|||kilobyte: What do you go by saying that a successful backup actually occurred? A failed backup will leave a backup file on disk that cannot be used for restore operation. Can you try to do a restore to a new database from that backup file if space permits?|||jimpen: A very compelling plan! BTW, do you really believe that system databases can be omitted from maintenance plans? Also, I don't think it's a good idea to just shrink logs of all databases accross the board. Those with Full Recovery mode should have log device pre-allocated to avoid growth overhead during business hours.|||Originally posted by rdjabarov
jimpen: A very compelling plan! BTW, do you really believe that system databases can be omitted from maintenance plans? Also, I don't think it's a good idea to just shrink logs of all databases accross the board. Those with Full Recovery mode should have log device pre-allocated to avoid growth overhead during business hours.

As I said, I do the system DBs for grins. I have kept our server very vanilla. There is only one stored procedure, and a table that I would like to retain after a crash. And even then, that table's data is kept elsewhere. I also changed the model a little for standards sake (min 50MB log file). If it is a minor crash, SQL server will generally either recover itself, or need a little work from the SysAdm and it will come up. But I've had mine go down on Apps install so bad that I had MS's support in one ear and a consultant in the other. After a day, they said just reinstall and reattach the other databases. Nothing could be recovered from the master, model, etc.

As for the log files, I already have them set up for a larger minimum size where needed. The rollback DBs get their transactions backed up and then are truncated. The slow database are just automatic trunc on checkpoint so they never really grow.|||wow, that's pretty brave! Good luck!|||What's brave about it?

As long as the tape backups are working, even if someone walked in and ran an axe through the hard drives the worst loss is about 24 hours of data.

We are primarily a daylight business - about 7A-7P Mon-Fri with some weekend dedicated types.

If the server crashes at on Sunday at midnight and W2K Server has to be reinstalled as well as the SQL 7, the model, master, msdb and tempdb will all be replaced anyway. It is virtually impossible to restore a master database. Sometime, build a baby server and try it.

After the reinstall on the same server, if the DB files are still on the volume, you can run a sp_attach_db to get them back online. If you lost the volume and are starting with a blank HD, then you have the files restored from tape to the \mssql\backup directory and then proceed to restore the database to it's original place.

If someone went bogus on the data and multiplied every table by 5 times then you have a PIT recovery. And if it was done Saturday then you get back to Friday after hours.

And this is not even considering that you publish the database.|||Thanks for the help guys, this has turned out to become an interesting discussion of a vital subject. I consider myself a bit of a newbie in this area, so I'm glad about the input.

About the backups. No, I haven't tried restoring from the files, I just sort assumed they were ok. The file size seemed all right, so I figured they were just fine. The log files produced by the maint. job said something like this:

'Backup can not be performed on database 'msdb'. This sub task is ignored.'

, but I'm suspecting this might be about the transaction log backups Those log files are not always as helpful as I'd wish...

Anyway, I'm now doing local backups now and that seems to work a lot better. Today the log file said succes at the bottom! Except for what seems to be the log file for the transaction log backups. I'll have to delve into that a little deeper.

Btw, the backup plan I use is the following:
1. Weekly full and daily incremental backups on tape for major database servers
2. Daily full backups on disk for all database servers

I like to have backups on tape in case the office burns down or whatever. I don't differentiate too much between different databases within the same server. The reasons for this are twofold. I don't feel I know enough about it, and I don't feel I have the time. Also, people tend to create new databases as often as they change their underwear here, and that does not make administering the servers any eaiser.

Again, thanks for the input.

MNJ|||Btw, I use Veritas Backup Exec 9.0 and that seems to work just fine with the agents and all. And yes, I have done test restores.

MNJ|||Hello again.

Just wanted to say that backups work fine now that they're being done locally. Except for the transaction log backups which still fail.

I think this is an unrelated problem though, so I've started a new thread at: http://65.61.175.198/showthread.php?threadid=889573

Again, any help and comments will be greatly appreciated.

MNJ|||Originally posted by kilobyte
I don't differentiate too much between different databases within the same server. The reasons for this are twofold. I don't feel I know enough about it, and I don't feel I have the time. Also, people tend to create new databases as often as they change their underwear here, and that does not make administering the servers any eaiser.
MNJ

If they are just adding database(s) and developing/testing on your production servers you need to slap some people around :( . They can and will at some point crash your SQL and/or server with some process that goes wild or access the disk and delete an important file.

You may need to speak up at some point and make them realize that they need to have test/development servers versus daily production.

I work for a relatively small company and even we have scraped an older server into using as a test box before we fire it up onto the production SQL server.|||Originally posted by jimpen
If they are just adding database(s) and developing/testing on your production servers you need to slap some people around :( . They can and will at some point crash your SQL and/or server with some process that goes wild or access the disk and delete an important file.

You may need to speak up at some point and make them realize that they need to have test/development servers versus daily production.


I know, I know. We are in that phase now in fact, trying to have test servers for each production server. There have been times, though, where it's been necessary to change something really quickly, and I am fully aware that it is not a good strategy.

I've begun thinking about how to come up with a new maintenance plan, and I just might start a new thread on that subject, to get some input from outside.

Anyways, thanks for the help so far, you've all saved me hours of work!

MNJ|||kilobyte: You're getting transaction log backup errors probably because you included databases with simple recovery mode. In one of your posts you mentioned msdb. This database will always start up in simple recovery mode, which will fail the entire step even if the rest of db's got backed up successfully.

jimpen: you said that "It is virtually impossible to restore a master database." - I have no more comments :)|||Originally posted by rdjabarov
kilobyte: You're getting transaction log backup errors probably because you included databases with simple recovery mode. In one of your posts you mentioned msdb. This database will always start up in simple recovery mode, which will fail the entire step even if the rest of db's got backed up successfully.

You're right, that is the problem.

MNJ

Maintenance schedule in 2005

In SQL 2005, is there a way to specify two (or more) different schedules
within one maintenance plan? Or, do I have to create different plans to be
ran on different schedules?
Thank you in advance for your help!> In SQL 2005, is there a way to specify two (or more) different schedules
> within one maintenance plan? Or, do I have to create different plans to be
> ran on different schedules?
You cannot add more than one schedule using the Maintentance Plan Wizard, or
by using the Properties of a created plan. However, with the wizard, you
create a scheduled job. You can add schedules to the job.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/

Maintenance schedule in 2005

In SQL 2005, is there a way to specify two (or more) different schedules
within one maintenance plan? Or, do I have to create different plans to be
ran on different schedules?
Thank you in advance for your help!> In SQL 2005, is there a way to specify two (or more) different schedules
> within one maintenance plan? Or, do I have to create different plans to be
> ran on different schedules?
You cannot add more than one schedule using the Maintentance Plan Wizard, or
by using the Properties of a created plan. However, with the wizard, you
create a scheduled job. You can add schedules to the job.
--
Dejan Sarka
http://www.solidqualitylearning.com/blogs/

Maintenance Plans on MSDE

Hi NG,
I've defined a job and a maintenance plan on my SQL-Server. The target
system is using MSDE. I could see that the job was created, but with the
Enterprise Manager I cannot see that there is a folder 'Database Maintenance
Plans' under folder 'Management'.
Does anybody know if Maintenance plans are generally possible under MSDE, or
is there something missing on my target system where MSDE is installed.
Thanks for support, rene
hi Rene,
Re Fo wrote:
> Hi NG,
> I've defined a job and a maintenance plan on my SQL-Server. The target
> system is using MSDE. I could see that the job was created, but with
> the Enterprise Manager I cannot see that there is a folder 'Database
> Maintenance Plans' under folder 'Management'.
> Does anybody know if Maintenance plans are generally possible under
> MSDE, or is there something missing on my target system where MSDE is
> installed.
> Thanks for support, rene
Maintenance Plans are supported, but you do not have a graphical tool to
manage them, and have to rely on xp_sqlmaint system extended stored
procedure
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
_xp_aa-sz_0mb8.asp)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Maintenance Plans Cleanup Task does not delete files

Hello,

I've created a Maintenance Plan with a Backup Task and a Cleanup Task. The Backup task works fine, but not the cleanup task.

When I execute the maintenant plan, there's none error message but the old backup files aren't deleted.

Could you help me please?

Thanks

I was a bit puzzled by this as well, but then i read the help file:

Backup and restore history

Retaining records of when recent backups were created can help SQL Server create a recovery plan when you want to restore a database. The retention period should be at least the frequency of full database back ups.

SQL Server Agent Job history

This history can help you troubleshoot failed jobs, or determine why database actions occurred.

Maintenance plan history

This history can help you troubleshoot failed maintenance plan jobs, or determine why database actions occurred.

So it actually makes a lot of sence that the back-up itself is not deleted, only the information in the database that says the back-up was made.

|||

You may be running into the below issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=336728&SiteID=1

Backup task supports subfolder but cleanup task did not in RTM. This is addressed in SP1.

Let me know if this is not the issue you are running into.

Gops Dwarak

|||

Oops,

Maintenance Clean-up Task != History Clean-up Task

My bad....

Maintenance plans and SQL Server 2005

Hi all,
Im currently investigating a SQL Server 2005 DB maintenence plan for my
company's software product.
I'm still learning this stuff so if I'd appreciate any comments on my
questions below
1) Assume the DB is using a FULL recovery model.
When I do a full backup of the database, will the transaction log be
truncated?
Or will it only be truncated when I perform a transaction log backup?
2) How often should transaction log backups be run? I'm thinking every
couple of hours... probably depends on the performance though (might
make it more frequent)
What is the performance hit when running a transaction log?
3) We currently do an index rebuild nightly. Would it be worth doing
this before a full backup (I assume that the rebuild would contribute
to the transaction log, so if the full backup clears these out then it
would be worth doing it before the backup? Or are there risks involved
with the rebuild so it is better practice to do it after the full
backup).
For one site, the db needs to be up 24/7.
I was thinking about running index rebuild in online mode for this, but
would this only work on Microsoft SQL Server 2005 Enterprise Edition?
BOL under "Guidelines for Performing Online Index Operations" says that
"Online index operations are available only in Microsoft SQL Server
2005 Enterprise Edition." so I'm thinking that this might not work'
4) We do not use auto update statistics, so run updates statistics
every night.
Since we "rebuild the indexes" in the maintenance plan prior to running
the update stats, is the update stats step necessary? I think I read
somewhere that rebuilding the indexes updates the statistics
automatically...
If we use INDEXDEFRAG instead, then we would need to run update stats
yeah? and if so, would it be best to run it before running indexdefrag
or after?
4) Just for your information, I am not going to shrink the log as from
what I've read this is not good for performance as the log file will no
doubt increase in size again (and it encourages fragmentation). But I'm
hoping that the combination of regular transaction log backups will
keep the log file at a consistent size.
I know there are a few questions in there, any comments would be really
appreciated!
Cheers!
Daviddavconts@.gmail.com wrote:
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
No, a full backup will not truncate the log file

> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
The frequency depends on your tolerance for data loss. If you can
afford to lose 2 hours of work, then backup the log every 2 hours. If
you can only afford to lose 5 minutes of work, backup the log every 5
minutes.

> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
Consider only rebuilding those indexes that are badly fragmented. See
http://www.realsqlguy.com/twiki/bin...IndexesAsNeeded

> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
You could use DBCC INDEXDEFRAG, or @.Mode = 'DEFRAG' if running the
script I linked to above.

> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
I assume you have auto-stats turned off due to potential performance
problems? It's very rare to encounter such problems, I've never had
cause to turn off auto stats, even on OLTP databases approaching 200GB.
I'd turn them back on.

> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
Correct, DBCC INDEXDEFRAG does not update stats. You'll need to
manually update them after defragging, or turn auto-stats on.

> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
Do not shrink the log file. Size it properly, do a thorough
defragmentation of the OS volume, you'll enjoy optimal performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||1) Only when a TLog backup is run. This topic is much more in depth than
meets the eye, make sure you read all about it in BOL before implementing in
production.
2) I don't think there is usually much performance hit. How often you want
to do it depends on how much data loss is acceptable.
3) IMO it's never a bad idea to have a good backup before doing stuff like
this. You may want to write TSQL code to switch the recovery model from Full
to Bulk Logged before doing your rebuilds. It will make it faster and your
TLogs wont grow as fast. Again, read BOL thoroughly before doing this. Every
night seems a bit excessive too me, but if thats what you need to do
depending on Inserts/ Deletes....
Looks like it would work on EE to me, but I'll let others comment.
4) It shouldn't be necessary.
I'll usually do update stats after a defrag, but I have no idea why?
4) You are correct.
<davconts@.gmail.com> wrote in message
news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
> I know there are a few questions in there, any comments would be really
> appreciated!
> Cheers!
> David
>|||> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
NO. Only if you perform BACKUP LOG File

> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
It depends on your business requirements . I do it every 15 minutes

> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
Just make sure that REBUILD INDEX command will not concur with a BACKUP
statement

> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
REBUILD INDEX updates statistics

> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
After INDEXDEFRAG

> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
Correct. Don't be afraid that the LOG file is huge ( I assume you perform
BACKUP LOG ) , that means SQL Server has enough room to perfom its work
<davconts@.gmail.com> wrote in message
news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
> I know there are a few questions in there, any comments would be really
> appreciated!
> Cheers!
> David
>|||Thanks for all the great replies above.. it has really clarified a lot
of the reading i've been doing to get up to speed on this! Really
appreciate it.
Thanks for the link too Tracy, that stored procedure looks interesting,
I'll give it a go.|||In addition to the other posts, check out the Books Online that comes with S
QL Server 2005,
sys.dm_db_index_physical_stats. It has code you can use that depending on th
e fragmentation level
either does nothing, REBUILD or REORGANIZE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<davconts@.gmail.com> wrote in message news:1151975288.606525.38030@.h44g2000cwa.googlegroups.
com...
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
> I know there are a few questions in there, any comments would be really
> appreciated!
> Cheers!
> David
>|||Thanks Tibor
I tried that script in BOL as you mention above (with a USE dbname
clause at the top)
but I get an error message:
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '('.

>From what I can see it relates to the following line in the script:
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,
'LIMITED')
Not sure why this is failing - this is a straight copy from BOL so am a
bit confused'|||Its ok I got it working...
I added:
DECLARE @.db_id smallint;
SET @.db_id = DB_ID(N'RMHCMS');
at the top and then changed the following from:
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
to:
sys.dm_db_index_physical_stats (@.db_id, NULL, NULL , NULL, 'LIMITED')
Cheers,
David
davconts@.gmail.com wrote:
> Thanks Tibor
> I tried that script in BOL as you mention above (with a USE dbname
> clause at the top)
> but I get an error message:
> Msg 102, Level 15, State 1, Line 24
> Incorrect syntax near '('.
>
> FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,
> 'LIMITED')
> Not sure why this is failing - this is a straight copy from BOL so am a
> bit confused'|||<davconts@.gmail.com> wrote in message
news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
Not really. But in any case, think of it as a trade-off.
If you do it say every 2 hours vs every 15 minutes.
The 2 hour version would have 8 times as much info to backup as the 15
minute one.
So if there WERE a performance issue, it would last 8 times as long.
Generally though plan them according to what your DR model requires.
For example our Dev databases we backup nightly and that's about it. It's
really not worth the trouble to do it more often.
Our production ones we do transaction log backups every 15 minutes and full
backups 3 nights a week.

Maintenance plans and SQL Server 2005

Hi all,
Im currently investigating a SQL Server 2005 DB maintenence plan for my
company's software product.
I'm still learning this stuff so if I'd appreciate any comments on my
questions below :)
1) Assume the DB is using a FULL recovery model.
When I do a full backup of the database, will the transaction log be
truncated?
Or will it only be truncated when I perform a transaction log backup?
2) How often should transaction log backups be run? I'm thinking every
couple of hours... probably depends on the performance though (might
make it more frequent)
What is the performance hit when running a transaction log?
3) We currently do an index rebuild nightly. Would it be worth doing
this before a full backup (I assume that the rebuild would contribute
to the transaction log, so if the full backup clears these out then it
would be worth doing it before the backup? Or are there risks involved
with the rebuild so it is better practice to do it after the full
backup).
For one site, the db needs to be up 24/7.
I was thinking about running index rebuild in online mode for this, but
would this only work on Microsoft SQL Server 2005 Enterprise Edition?
BOL under "Guidelines for Performing Online Index Operations" says that
"Online index operations are available only in Microsoft SQL Server
2005 Enterprise Edition." so I'm thinking that this might not work'
4) We do not use auto update statistics, so run updates statistics
every night.
Since we "rebuild the indexes" in the maintenance plan prior to running
the update stats, is the update stats step necessary? I think I read
somewhere that rebuilding the indexes updates the statistics
automatically...
If we use INDEXDEFRAG instead, then we would need to run update stats
yeah? and if so, would it be best to run it before running indexdefrag
or after?
4) Just for your information, I am not going to shrink the log as from
what I've read this is not good for performance as the log file will no
doubt increase in size again (and it encourages fragmentation). But I'm
hoping that the combination of regular transaction log backups will
keep the log file at a consistent size.
I know there are a few questions in there, any comments would be really
appreciated!
Cheers!
Daviddavconts@.gmail.com wrote:
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
No, a full backup will not truncate the log file
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
The frequency depends on your tolerance for data loss. If you can
afford to lose 2 hours of work, then backup the log every 2 hours. If
you can only afford to lose 5 minutes of work, backup the log every 5
minutes.
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
Consider only rebuilding those indexes that are badly fragmented. See
http://www.realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
You could use DBCC INDEXDEFRAG, or @.Mode = 'DEFRAG' if running the
script I linked to above.
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
I assume you have auto-stats turned off due to potential performance
problems? It's very rare to encounter such problems, I've never had
cause to turn off auto stats, even on OLTP databases approaching 200GB.
I'd turn them back on.
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
Correct, DBCC INDEXDEFRAG does not update stats. You'll need to
manually update them after defragging, or turn auto-stats on.
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
Do not shrink the log file. Size it properly, do a thorough
defragmentation of the OS volume, you'll enjoy optimal performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||1) Only when a TLog backup is run. This topic is much more in depth than
meets the eye, make sure you read all about it in BOL before implementing in
production.
2) I don't think there is usually much performance hit. How often you want
to do it depends on how much data loss is acceptable.
3) IMO it's never a bad idea to have a good backup before doing stuff like
this. You may want to write TSQL code to switch the recovery model from Full
to Bulk Logged before doing your rebuilds. It will make it faster and your
TLogs wont grow as fast. Again, read BOL thoroughly before doing this. Every
night seems a bit excessive too me, but if thats what you need to do
depending on Inserts/ Deletes....
Looks like it would work on EE to me, but I'll let others comment.
4) It shouldn't be necessary.
I'll usually do update stats after a defrag, but I have no idea why?
4) You are correct.
<davconts@.gmail.com> wrote in message
news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
> I know there are a few questions in there, any comments would be really
> appreciated!
> Cheers!
> David
>|||> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
NO. Only if you perform BACKUP LOG File
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
It depends on your business requirements . I do it every 15 minutes
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
Just make sure that REBUILD INDEX command will not concur with a BACKUP
statement
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
REBUILD INDEX updates statistics
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
After INDEXDEFRAG
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
Correct. Don't be afraid that the LOG file is huge ( I assume you perform
BACKUP LOG ) , that means SQL Server has enough room to perfom its work
<davconts@.gmail.com> wrote in message
news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
> I know there are a few questions in there, any comments would be really
> appreciated!
> Cheers!
> David
>|||Thanks for all the great replies above.. it has really clarified a lot
of the reading i've been doing to get up to speed on this! Really
appreciate it.
Thanks for the link too Tracy, that stored procedure looks interesting,
I'll give it a go.|||In addition to the other posts, check out the Books Online that comes with SQL Server 2005,
sys.dm_db_index_physical_stats. It has code you can use that depending on the fragmentation level
either does nothing, REBUILD or REORGANIZE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<davconts@.gmail.com> wrote in message news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
> I know there are a few questions in there, any comments would be really
> appreciated!
> Cheers!
> David
>|||Thanks Tibor
I tried that script in BOL as you mention above (with a USE dbname
clause at the top)
but I get an error message:
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '('.
>From what I can see it relates to the following line in the script:
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,
'LIMITED')
Not sure why this is failing - this is a straight copy from BOL so am a
bit confused'|||Its ok I got it working...
I added:
DECLARE @.db_id smallint;
SET @.db_id = DB_ID(N'RMHCMS');
at the top and then changed the following from:
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
to:
sys.dm_db_index_physical_stats (@.db_id, NULL, NULL , NULL, 'LIMITED')
Cheers,
David
davconts@.gmail.com wrote:
> Thanks Tibor
> I tried that script in BOL as you mention above (with a USE dbname
> clause at the top)
> but I get an error message:
> Msg 102, Level 15, State 1, Line 24
> Incorrect syntax near '('.
> >From what I can see it relates to the following line in the script:
> FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,
> 'LIMITED')
> Not sure why this is failing - this is a straight copy from BOL so am a
> bit confused'|||<davconts@.gmail.com> wrote in message
news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
Not really. But in any case, think of it as a trade-off.
If you do it say every 2 hours vs every 15 minutes.
The 2 hour version would have 8 times as much info to backup as the 15
minute one.
So if there WERE a performance issue, it would last 8 times as long.
Generally though plan them according to what your DR model requires.
For example our Dev databases we backup nightly and that's about it. It's
really not worth the trouble to do it more often.
Our production ones we do transaction log backups every 15 minutes and full
backups 3 nights a week.