Compaq ProLiant 1000 Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 24

DUMP TRAN <database> WITH NO_LOG

Get Compaq ProLiant 1000 PDF manuals and user guides View all Compaq ProLiant 1000 manuals
Add to My Manuals
Save this manual to your list of manuals

Page 24 highlights

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 24 When the transaction log fills up and SQL Server is unable to record a checkpoint in the transaction log, you have to perform a dump of the transaction log, without making a backup copy of it, using DUMP TRAN WITH NO_LOG. In such a situation, make sure to follow with a full database backup. Refer to the System Administrator's Guide for more details and important considerations regarding use of this command. There is a number of ways you can monitor the transaction log usage and prevent the transaction log from filling up. Three methods are described below. Monitoring Transaction Log Usage with DBCC You can issue the DBCC checktable(syslogs) command31. SQL Server will update appropriate system tables and report the following: total number of data pages in the syslogs table total number of rows in the syslogs table space used on the log segment in MB and in percent space free on the log segment in MB and in percent The DBCC sqlperf(logspace) command can also be issued, and will retrieve the transaction log space used for all databases that have the log on a separate device(s). Monitoring Transaction Log Usage with Performance Monitor In Performance Monitor, you can select Object: SQLServer-Log, Counter: Log Space Used(%) and Instance: < database(s)> to monitor the transaction log usage. Using the Alert option of Performance Monitor, you can generate administrative alerts triggered on a pre-defined transaction log usage, and have the administrator dump the transaction log upon reception of this alert. Or you can even automate this procedure and have a batch file dump the contents of the log. Truncating Transaction Log on Checkpoint You can issue the sp_dboption , 'trunc. log', true command to have the transaction log automatically truncated at every occurrence of a checkpoint. Use this option only if, after a catastrophic data loss, you don't need to recover transactions which followed the last full database backup. Automating Transaction Log Dumps / Truncations with SQL Executive The SQL Executive Service in SQL Server 6.x includes several 'Managers' that can be used to automate transaction log management: the Task Manager can be set up to perform dumps of the transaction log (or even the entire database) on a regularly scheduled basis (hourly, daily, weekly, or monthly). It offers the benefit of maintaining a task history, which can be used as a 'backup log'. This strategy assumes you know how often the dumps need to occur. the Alert Manager can be set up to respond to a SQL Server event (a notice logged to the Event Log by the Event Manager), such as an event caused by SQL Error 1105 indicating that the Transaction Log is full. The Alert can then fire off a task to send an e-mail to the Administrator or to try and dump or truncate the transaction log. This is more of a 'reactive' strategy in that the transaction log is already full by the time the alert activates, and may 31 The time to complete the DBCC checktable(syslogs) command depends on the size of your transaction log and space used; may take a long time to run on large log spaces.

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
Compaq Backup and Recovery for Microsoft SQL Server 6.x
Page
24
When the transaction log fills up and SQL Server is unable to record a checkpoint in the transaction
log, you have to perform a dump of the transaction log, without making a backup copy of it, using
DUMP TRAN <database> WITH NO_LOG
.
In such a situation, make sure to follow with a full
database backup.
Refer to the System Administrator’s Guide for more details and important
considerations regarding use of this command.
There is a number of ways you can monitor the transaction log usage and prevent the transaction log
from filling up.
Three methods are described below.
Monitoring Transaction Log Usage with DBCC
You can issue the
DBCC checktable(syslogs)
command
31
.
SQL Server will update appropriate
system tables and report the following:
total number of data pages in the
syslogs
table
total number of rows in the
syslogs
table
space used on the log segment in MB and in percent
space free on the log segment in MB and in percent
The
DBCC sqlperf(logspace)
command can also be issued, and will retrieve the transaction log space
used for all databases that have the log on a separate device(s).
Monitoring Transaction Log Usage with Performance Monitor
In Performance Monitor, you can select
Object: SQLServer-Log, Counter: Log Space Used(%)
and
Instance: < database(s)>
to monitor the transaction log usage.
Using the Alert option of
Performance Monitor, you can generate administrative alerts triggered on a pre-defined transaction
log usage, and have the administrator dump the transaction log upon reception of this alert.
Or you
can even automate this procedure and have a batch file dump the contents of the log.
Truncating Transaction Log on Checkpoint
You can issue the
sp_dboption <database>, ‘trunc. log’, true
command to have the transaction log
automatically truncated at every occurrence of a checkpoint.
Use this option
only
if, after a
catastrophic data loss, you don’t need to recover transactions which followed the last full database
backup.
Automating Transaction Log Dumps / Truncations with SQL Executive
The SQL Executive Service in SQL Server 6.x includes several ‘Managers’ that can be used to
automate transaction log management:
the Task Manager can be set up to perform dumps of the transaction log (or even the entire
database) on a regularly scheduled basis (hourly, daily, weekly, or monthly).
It offers the
benefit of maintaining a task history, which can be used as a ‘backup log’.
This strategy
assumes you know how often the dumps need to occur.
the Alert Manager can be set up to respond to a SQL Server event (a notice logged to the
Event Log by the Event Manager), such as an event caused by SQL Error 1105 indicating that
the Transaction Log is full.
The Alert can then fire off a task to send an e-mail to the
Administrator or to try and dump or truncate the transaction log.
This is more of a ‘reactive’
strategy in that the transaction log is already full by the time the alert activates, and may
31
The time to complete the
DBCC checktable(syslogs)
command depends on the size of your transaction log and
space used; may take a long time to run on large log spaces.