Distributed transactions, performance counters, and SQL backup

Problem: we have used a large number of distributed transactions and are studying database images to make one of our key databases highly available. During the test, we found that distributed transactions sometimes fail after trying to transfer the mirror database. Can you explain why?

A: This is actually a documented limitation on the use of distributed transactions. This restriction exists when you use a database image or log transfer. Basically, this restriction exists for any technology with different Windows Server names after failover.

When Microsoft Distributed Transaction Processing Coordinator (MSDTC) is used for transactions, the local transaction processing Coordinator has a resource ID to identify the server that runs the coordinator. When performing image failover, the subject database will host the image partner on another server), so the resource IDs of the Transaction Coordinator will be different.

If a distributed transaction is active, the transaction processing coordinator on the image partner attempts to identify the transaction status but cannot identify it because it has an incorrect resource ID; MSDTC cannot recognize this ID because it was not originally included in the distributed transaction. In this case, you must terminate the distributed transaction. This is what you see.

Cross-database transactions involve simple transactions that update multiple databases. If one database is mirrored and the other database is not, cross-database transactions can be committed in the two databases. If force image failover is performed when the subject and image are not synchronized and manual failover is performed to allow data loss), transactions committed in the image database may be lost, this will damage the integrity of cross-database transactions.

This may occur when the image database is not synchronized. For more information, see my June 2009 column). Therefore, the log records for cross-database transactions submitted have not been sent to the image. After a forced failover, the transaction does not exist in the new master database. Therefore, the integrity of cross-database transactions is damaged.

Problem: Recently I have monitored some performance counters to solve a database storage problem. During this process, I noticed some very strange phenomena: even though no operations are performed in the database, there are still write activities in the database files. This situation exists in both data and log files. This situation continues even when I make sure that I am not connected to SQL Server. Since there is no connection, how can I/O activities exist?

A: SQL Server has many internal operations that need to be run. These operations are called background tasks. One or more background tasks are executed in the system, resulting in I/O activities. The possible causes are listed below:

Virtual shadow cleanup: The delete operation only marks the record as deleted to optimize the performance of the cancel operation. This operation does not actually clear the space. Once the delete operation is submitted, you must perform an operation to remove the deleted records from the database. This is done by the shadow cleanup task. For more information, see my blog post. This article also explains how to check whether the shadow cleanup task is running.

Auto scale-down: enabling this task can automatically remove empty space from the database. The task is to move the page at the end of the data file to the open header, merge the available space at the end, and then truncate the file. Of course you can enable this task, but you should never do this because it will cause index fragmentation problems and thus reduce performance) and occupy a large amount of resources. In general, automatic growth is also enabled for the database, so it may fall into a cycle of reduction-growth-reduction-growth, which has done a lot of useless work. You can use the following query to check the status of all databases:

 
 

  
  
SELECT name, is_auto_shrink_on FROM sys.databases; 
 
 

Delayed discard: This task is responsible for dropping or truncating tables and indexes. Re-indexing may result in dropping indexes, that is, generating new indexes and then dropping old indexes ). For small tables and indexes, the allocation is canceled immediately. For large tables and operators, the allocation is canceled in batches using background tasks. This is to ensure that all necessary locks are obtained without consuming the memory. You can monitor this task with latency discard performance counters as described in books online here.

Delayed write: This task is responsible for removing the old page from the memory cache called the buffer pool. When the server memory is insufficient, even if the page is changed, it may have to be removed. In this case, the changed page must be written to the disk before it can be removed from the memory. You can use the "Lazy writes/sec" performance counters to monitor this task as described in the books online here.

All of the preceding tasks may change the database. All of these are changed using transactions. As long as a transaction is committed, the transaction log records generated by the transaction must be written to the database logs on the disk. Because the database is changed from time to time, there must be a Check Point to refresh the changed data file page to the disk. For more information, see my article for TechNet February 2009 to learn about the logging and restoration functions in SQL Server.

As you can see, there is no active SQL Server connection, which does not necessarily mean that the process is in a static state. It may be busy executing one or more background tasks. If I/O activities are still in progress after all database activities are completed for a long time, you may also need to check whether the scheduled job is running.

Q: I am a non-voluntary DBA and are trying different tasks to get familiar with my work as soon as possible. The former DBA sets up a job to write backups to a file, but I don't know how to restore these backups. Can I view the backup content in the file? How can I restore these backups correctly?

Answer: Although backups can be appended to the same file, most people put each backup in an independent file with a meaningful name and usually a combination of dates and timestamps. This helps you avoid problems and facilitate other tasks:

When each backup is in its own file, it is very easy to copy the backup for security reasons. If all backups are in one file, you can only copy the entire backup file to create the latest backup copy.
When all backups are in one file, the old backup cannot be deleted.
If each backup has a file named separately, it is impossible to overwrite the existing copy accidentally.
Unfortunately, this does not help you. You already have multiple backups in one file. However, you can restore a copy manually or using SQL Server Management Studio (SSMS.

To view the backup content in the file, you can use SSMS to create a new backup device that references the file. After a reference is created, the backup details of the backup device are displayed. You can also use the restore headeronly command. Both methods check the backup device and provide a line of output to describe each backup in the file. SSMS uses friendly names to identify the Backup Type. To use the correct syntax, follow the information provided by SQL Server 2008 in SQL Server books online about the command entry, determine the Backup Type for each backup so that you can use the appropriate RESTORE command to RESTORE the backup.

You also need to confirm the backup to be restored. This is tricky because the name of the output column of the required restore headeronly does not match the option you must use to RESTORE. The backup in the file starts from 1. 1 indicates the oldest.) You can find the number in the column "Position. To RESTORE the backup, you must use the corresponding number in the with file = <number> section of the RESTORE command. The following is an example:

 
 

  
  
RESTORE DATABASE test FROM DISK = 'C:\SQLskills\test.bak' 
  
  
WITH FILE = 1, NORECOVERY;RESTORE LOG test 
  
  
FROM DISK = 'C:\SQLskills\test.bak' 
  
  
WITH FILE = 2, NORECOVERY; 
 
 

The rest will not be listed here. You must restore the sequence from a database backup, and then restore zero or more differential databases and/or transaction log backups. More detailed information is not covered in this column. However, in my article for the November 2009 journal, I used backup for disaster recovery, describes in detail the recovery sequence and other RESTORE options that may be required.

When using SSMS, you can specify a backup file in the restore Database Wizard. This wizard automatically displays all the backups in the file and allows you to select the desired backup. Figure 1 shows an example.

Figure 1 use SSMS to restore the Database Wizard to display multiple backups in the file.

Regardless of the option selected, it is critical that you try to restore to another location before performing a disaster recovery. One of the principles I have always followed is "There is no backup if the restoration fails ."

Problem: I have a large database that needs to be copied to the development environment every few weeks. My problem is that the database has recently increased to accommodate more data, and it seems too big to restore it to the development environment. How can I reduce the size of a database?

A: This is a common problem. Unfortunately, there is no good solution.

Database Backup does not change the database in any way. It only reads all the used database sections. For more information about the reasons and degrees of these sections and some transaction logs, see my blog) included in the backup. Restoring a database backup only creates a file, writes out the backup content, and then restores the database. Basically, the content in the database is what is obtained during restoration. There are no options for reducing databases during restoration, solving index fragmentation issues during restoration, updating statistics during restoration, or any other operations that people may need to perform.

So how can we achieve your goal? Based on the specific solution, you have three methods.

First, you can scale down the production database to clear the empty space. In this way, the restored database copy is the same as the production database without wasting space, but the cost may be high. The production database will grow again, so the reduction operation may be costly in terms of CPU, I/O, and transaction logs, and may cause index fragmentation. The index fragmentation problem must be solved to occupy more resources. You will not choose to do this. For more information about the risk of using data file reduction, see my blog .) You can consider removing only the available space at the end of the file (dbcc shrinkfile with truncateonly), but this may not be as small as you want.

Second, if you only need to restore the production database once during the development process, you need enough space to restore the complete database, and then reduce it to recycle the space. After that, you need to determine whether to solve the fragmentation caused by the reduction operation.

If you want to run a query for performance testing or reporting, fragmentation may greatly reduce the performance of these queries. If you do not run such queries, you do not have to sort the fragments. To solve the fragmentation problem, you cannot regenerate the INDEX and use alter index... REBUILD command), because this requires extra space and will cause the database to increase again, you need to re-organize the INDEX and use alter index... REORGANIZE command ).

If you need to sort the shards, you must switch the database to the SIMPLE recovery model so that transaction logs do not grow as a result of reorganizing all transaction log records generated. If you keep the database as a FULL recovery model, the log continues to grow, unless you back up the log, you may want to avoid processing the content) into the development copy of the database.

Finally, if you need to restore the production database multiple times during the development process, you do not want to repeat the steps in the second method multiple times. In this case, it is best to follow the steps in the second method, and then create another backup of the database that may have been fragmented.

The second backup can then be used to restore the production database of the minimum size.

All in all, it is impossible to move the production database with a large amount of available space to the development environment without including the SQL available space during initial restoration.

Original article address

Source: Microsoft TechNet Chinese site