InterviewSolution
Saved Bookmarks
InterviewSolution
| 1. |
What’s the impact if we enable SQL Server Trace Flag 3042 for compressed backups? |
|
Answer» With Compressed backups, SQL Server works on the pre-allocation algorithm to determine how much space the compressed backup will require and will allocate this space at the start of the backup PROCESS. At the time of backup completion, the backup process might expand or shrink the backup file on the basis of actual requirement. Pre-Allocation helps SQL Server save the overhead of constantly GROWING the backup file. Sometimes when we have a very large database and the difference in uncompressed and compressed backups is very high, preallocated and actual backup space has a significant difference. Trace FLAG 3042 promote better space management with little over-head to SQL server to grow backup file on a regular basis. This is useful with limited disk space. Enabling trace flag 3014 may result in a little spike in CPU & memory but not significant to cause performance issues. |
|