Maint plans for DB and tran log Backups

As I'm a new DBA, I need your help to understand how the former DBA set up
database backups in this environment, and if I need to change anything to
facilitate more efficient backups. We have a production server with multiple
databases on drive D: and transaction logs on E: We have a EMC SAN
environment where drives D: and E: get replicated nightly to their respective
clones on the SAN. The clones get mounted as drives X: and Y: on another
server and are backed up to tape nightly.
On the production server, there are two separate maintenance plans: one for
DB's and one for tran logs. The DB maint plan runs at 10:00pm and backs up
DB's to drive F:. It is configured to remove expired backups older than 1
day from drive F:.
The tran log maint plan runs on the half hour from 6:30am to 8:00pm and
backs up tran logs to drive F:. It is configured to remove files older than
2 days from drive F:.
The SAN replication job apparently does not do any database or log
maintenance after it successfully clones the production drives. In fact the
replication job occassionaly fails if the source drives are low on space. So
I need to maintain disk free space manually.
Question: Should I consolidate the two maint plans? I don't know why the DB
and tran log plans are separate, other than possibly some of the databases
don't require their logs to be backed up.
The tran logs grow and fill up drive E:. To handle this, I manually run the
following script periodically:
BACKUP LOG <database name> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<log name>, TRUNCATEONLY)
Question: Should I append these commands to the SQL Agent job that is runs
as part of the tran log maint plan?
The database dumps on drive F: aren't deleted after 1 day as they are
supposed to be per the DB maint plan. Instead, I consistently see about a
weeks worth. I couldn't find any Windows scheduled tasks that delete the
database dumps older then 1 week, so I'm stumped how that is happening. In
any case, drive F: runs out of space occassionally, and I must manually
delete older database dumps. On the other hand, the tran log maint plan
does delete tran log backups older than 2 days as it is configured to.
Question: Why do you think the DB maint plan doesn't delete expired DB
backup files as it is configured to?
Thanks for your advice on these issues.Coop
> Question: Should I consolidate the two maint plans? I don't know why the
> DB
> and tran log plans are separate, other than possibly some of the databases
> don't require their logs to be backed up.
You cannot assign schedule task to two processes .I keep actually two jobs
for this purpose for each database
So , if you don't need to backup log file for some databases , keep just
one job for whole database
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
--administaiting best practices
> Question: Should I append these commands to the SQL Agent job that is runs
> as part of the tran log maint plan?
Its oke that LOG file is growing , so you make BACKUP LOG to allow to the
log to reuse its "virtual logs" and result to control over phisycal size of
the file , do not shrink it at all
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> Question: Why do you think the DB maint plan doesn't delete expired DB
> backup files as it is configured to?
Make you that the account (SQL Server Agent runs under) has an appropriate
permissions to delete them
"Coop" <Coop@.discussions.microsoft.com> wrote in message
news:A765E944-B066-43E5-B858-5A334DABDB14@.microsoft.com...
> As I'm a new DBA, I need your help to understand how the former DBA set up
> database backups in this environment, and if I need to change anything to
> facilitate more efficient backups. We have a production server with
> multiple
> databases on drive D: and transaction logs on E: We have a EMC SAN
> environment where drives D: and E: get replicated nightly to their
> respective
> clones on the SAN. The clones get mounted as drives X: and Y: on another
> server and are backed up to tape nightly.
> On the production server, there are two separate maintenance plans: one
> for
> DB's and one for tran logs. The DB maint plan runs at 10:00pm and backs
> up
> DB's to drive F:. It is configured to remove expired backups older than 1
> day from drive F:.
> The tran log maint plan runs on the half hour from 6:30am to 8:00pm and
> backs up tran logs to drive F:. It is configured to remove files older
> than
> 2 days from drive F:.
> The SAN replication job apparently does not do any database or log
> maintenance after it successfully clones the production drives. In fact
> the
> replication job occassionaly fails if the source drives are low on space.
> So
> I need to maintain disk free space manually.
> Question: Should I consolidate the two maint plans? I don't know why the
> DB
> and tran log plans are separate, other than possibly some of the databases
> don't require their logs to be backed up.
> The tran logs grow and fill up drive E:. To handle this, I manually run
> the
> following script periodically:
> BACKUP LOG <database name> WITH TRUNCATE_ONLY
> DBCC SHRINKFILE(<log name>, TRUNCATEONLY)
> Question: Should I append these commands to the SQL Agent job that is runs
> as part of the tran log maint plan?
> The database dumps on drive F: aren't deleted after 1 day as they are
> supposed to be per the DB maint plan. Instead, I consistently see about a
> weeks worth. I couldn't find any Windows scheduled tasks that delete the
> database dumps older then 1 week, so I'm stumped how that is happening.
> In
> any case, drive F: runs out of space occassionally, and I must manually
> delete older database dumps. On the other hand, the tran log maint plan
> does delete tran log backups older than 2 days as it is configured to.
> Question: Why do you think the DB maint plan doesn't delete expired DB
> backup files as it is configured to?
> Thanks for your advice on these issues.|||Coop wrote:
> The tran logs grow and fill up drive E:. To handle this, I manually run the
> following script periodically:
> BACKUP LOG <database name> WITH TRUNCATE_ONLY
> DBCC SHRINKFILE(<log name>, TRUNCATEONLY)
> Question: Should I append these commands to the SQL Agent job that is runs
> as part of the tran log maint plan?
>
You shouldn't make this a part of your maint plan. What you do with
this, is that you destroy your log sequence and ability to restore to a
point in time if needed.
Since you run a backup log every half hour, your logfile is being
truncated every half hour so there shouldn't be any need to do it in
between like you do with the above script (which actually truncate the
log twice).
Shrinking a logfile is normally not recommended unless there has been
some "unusual" activity in the database (e.g. a "one time" load of data
or deletion of data). If it has the size it needs for normal operation
and you shrink it, it just means that the server will use a lot of
ressources on growing the logfile again.
One of my databases has a 70 GB logfile even though it's only using <
2-3 GB during the week. When we run a indexdefrag in the weekend the log
needs the 70 GB so therefore there are no reason to shrink the file.
Diskspace are quite cheap today so it's really no the place to save a
few bucks. Also remember that there are no penalty to having to much
diskspace - but too little will kill you...:-).
Regards
Steen