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 respectiv
e
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. S
o
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_serve...r />
.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 t
he
> 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

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

Mailing dba when Sql Server Agent Fails

Hello

I would like to have a script which mails the dba mail box when the sql server agent stops running. I am using the SMTP server for mailing.

I am using the query :
EXECUTE xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'

to check the status.

I use the below for mailing :

EXEC master.dbo.xp_smtp_sendmail

@.FROM = N'testsql2000@.is.depaul.edu',

@.TO = N'dvaddi@.depaul.edu',

@.server = N'smtp.depaul.edu',

@.subject = N'Status of sqlserver!',

@.type = N'text/html',

@.message = @.message

How do I change it or write a script get a mail when the sql server agent is not running.

Thanks

You would need a script which checks the Agent, becasue there is no shutdown procedure which can be executed if the service becomes unavailable. The Script could either be executed from another Agent or from any scheduled component like AT / Winat of Windows.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

I know that , if there are 2 servers first server can monitor the 2nd server agent and vice-versa.

But I am not sure how to work on it or what script to run.

So it would be of great help if anyone can let me know.

Thanks

Mailing dba when Sql Server Agent Fails

Hello

I would like to have a script which mails the dba mail box when the sql server agent stops running. I am using the SMTP server for mailing.

I am using the query :
EXECUTE xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'

to check the status.

I use the below for mailing :

EXEC master.dbo.xp_smtp_sendmail

@.FROM = N'testsql2000@.is.depaul.edu',

@.TO = N'dvaddi@.depaul.edu',

@.server = N'smtp.depaul.edu',

@.subject = N'Status of sqlserver!',

@.type = N'text/html',

@.message = @.message

How do I change it or write a script get a mail when the sql server agent is not running.

Thanks

You would need a script which checks the Agent, becasue there is no shutdown procedure which can be executed if the service becomes unavailable. The Script could either be executed from another Agent or from any scheduled component like AT / Winat of Windows.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

I know that , if there are 2 servers first server can monitor the 2nd server agent and vice-versa.

But I am not sure how to work on it or what script to run.

So it would be of great help if anyone can let me know.

Thanks

Mailing DBA when sql server Agent Fails

Hello

I would like to know a script which mails the DBA mail box when the sql server agent fails ( I am working on sql server 2000) using SMTP. I have got SMTP server configured for all my other job failure notifications.

Please let me know any scripts that can be run on the command prompt or any ideas of how I can do it.



ThanksWhen the Agent service fails, or just when a job fails?|||When the Agent Service Fails.

Thanks|||Since the agent executes the scripts, how do you expect it to execute a script to notify you that it is no longer executing scripts?

Two alternatives:
1) Set up an agent on another server to check the functioning of the first one.
2) Set a script to run when the service is restarted (note that this will NOT notify if the server crashes and does not automatically restart).|||I use a configurable WMI script to check the status of selected services on selected servers. I check for any that are set to auto start and are not in a running state (or status, can't remember which).

Function CheckServices ( sComputer, sFQDN )

Dim oConnMail
Dim oCommMail

Dim oWMIService
Dim oItems
Dim oItem

Dim sDisplayName
Dim sSubject
Dim sMessage

Set oWMIService = GetObject("winmgmts:{impersonationLevel=impersonate,(Security )}!\\" & sFQDN & "\root\cimv2")
Set oItems = oWMIService.ExecQuery("SELECT * FROM Win32_Service",,48)

' Define objects for mail message
Set oConnMail = CreateObject("ADODB.Connection")
Set oCommMail = CreateObject("ADODB.Command")

' Open connection to mail database
oConnMail.ConnectionString = Replace(sConnectionString, sDatabase, "master")
oConnMail.Open

' Open connection to catalog database
oConn.ConnectionString = sConnectionString
oConn.Open

If Err.number = 0 Then
For Each oItem in oItems
If IsNull(oItem.DisplayName) Then
sDisplayName = oItem.Name
Else
sDisplayName = oItem.DisplayName
End If

' This part of the routine verifies that
' services that are set to Auto start are still running;
' if not running, then an alert is e-mailed
If oItem.StartMode = "Auto" and oItem.State <> "Running" Then

oCommMail.CommandText = "spSendMail"
oCommMail.CommandType = 4
oCommMail.ActiveConnection = oConnMail

sSubject = "Service " & oItem.Name & " stopped running on " & oItem.SystemName
sMessage = "Service " & sDisplayName & " stopped running on " & oItem.SystemName & VbCrLf & _
"Service Specific Exit Code: " & oItem.ServiceSpecificExitCode & vbCrLf & _
"Exit Code: " & oItem.ExitCode & vbCrLf & _
"Status: " & oItem.Status & vbCrLf & _
"State: " & oItem.State
' sMessage = "Test"
oCommMail.Parameters.Refresh
oCommMail.Parameters("@.Subject") = sSubject
oCommMail.Parameters("@.Message") = sMessage
oCommMail.Parameters("@.Recipient") = sRecipient

oCommMail.Execute

End If
Next

' Close mail database objects
Set oCommMail = Nothing
oConnMail.Close
Set oConnMail = Nothing
Else
Call AddLogEntry(sFQDN, "Error", "ServicesCheck", Err.number & " - " & Err.Source & " - " & Err.Description)
Err.Clear
End If

oConn.Close

End Function|||Hello

Thanks for your script.

How do I go on to execute the script. And do I need to download any WMI .

Thanks|||Hello

Thanks for your script.

How do I go on to execute the script. And do I need to download any WMI .

Thanks

I have a management server which is a dumping ground for a lot of different monitoring tools (some COTS, some custom). This script is scheduled from this server using Windows Scheduled Tasks. I pass in selected parameters to the script from the command line.

WMI is native to Windows 2000/Server 2003; you shouldn't need to load anything special there. You may need to adjust your permissions on the target server to be able to read from the WMI repository (I think you need to be at least a Power User, but I could be wrong.

Note that the script provided is a function. It would run inside of a main script that would:

1. Pull in a list of servers to poll
2. Pull in any system parameters (such as the e-mail address(es) you want the alert to go to
3. Call the function (passing in the name of the computer)

Regards,

hmscott