When data is written on to a database, the transaction log file is the file that records this activity. In most SQL Server environments, the transaction log file is one of the most used files, as it keeps writing information based on the transactions that were either committed to the database or rolled back. As this file writes every change that occurs in the database, it needs more processing power than the data files. Therefore, Microsoft recommends you have the transaction log on a separate drive. Even the data files should be stored on separate drives.It is highly recommended that for better disk I/O usage, you should place data files and log files on different hard drives dedicated for these files. If you place all of them in the same hard drive, there is going to be a performance bottleneck. As a recommended practice, you should separate the database files and transaction log files on different disks, preferably disks attached to different disk controllers.
The SQL Server Databases object contains counters that help you monitor backup/restore throughput, transaction log activities, and bulk copy operations. There are a number of counters used for monitoring log-related activities. Some of these counters are:
-Log Bytes
-Flushed/sec
-Log Cache Hit Ratio
-Log Cache Reads/sec
-Log File(s) Size (KB)
-Log Growths
-Percent Log Used.
You should not use the SQL Server General Statistics object to monitor the transaction log activities. Using Database object, you can capture information on number of current connections to the SQL Server.
No comments:
Post a Comment