SUMMARY
How does the SQL agent work?
ISSUE
Restore Capabilities
In any of the cases below, you can restore to an alternate database name and target path.
Restore to same server same instance:
SQL Full, SQL Diff, SQL Trans & system/user dbs
Restore to same server different instance:
SQL Full only & user dbs only
Restore to different server:
SQL Full only & user dbs only
Transaction Log handling
Executive summary:
When we create a backup, we create a checkpoint which enables the reuse of the portions of the log file, but we do not explicitly shrink the size of the database or log file.
Gory details:
If you need to understand the basis of log truncation versus shrinking the database, here’s the answer. Database vendors DO NOT WANT to be expanding the file for performance reasons. Thus they expand the size of the database to the “high water mark” and leave it. They then hope that during the next heavily loaded time period that the database is big enough so that they do not have to expand the database – because expanding the database costs valuable time and resources and thus limits the scalability and performance of the SQL Server system. This is the reason that a SQL full doesn’t shrink the database – it’s something that should only be done by the database administrator and only in concert with the on-going performance requirements of the system.
1) We use the BACKUP LOG option to backup ….
The transaction log is a wrap-around file. When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable: BACKUP LOG database without any option performs checkpoint and makes log file available for reuse, but it does not shrink file, if file has already grown in size.
2) We do not run or issue command “BACKUP LOG WITH TRUNCATE_ONLY”! Why, Because (old agent)
This will remove all transactions from the log, but it does not reduce the size on log file . Unfortunately, this will also remove ability to recover those transactions should database run into problems. This command is also deprecated in coming versions of SQL Server 2005 and up.
3) What Happens When Database file was shrunk?
When database files were shrunk by customer (or leave a database in auto shrink, or schedule a job to perform shrinks), SQL Server will remove the unused space from database's files, reallocate that space and let the O/S do what it needs with it. If customer does it, there’s a good chance that database will continue to grow (as the majority of non-static databases tend to do). Depending on database auto growth settings, this growth will probably be necessary and customer will end up shrinking it again. At best, this is just extra work (shrink grow/shrink grow), and the resulting file fragmentation can be handled by I/O subsystem. At worst, this is causing file fragmentation, interrupting what would have otherwise been contiguous files and potentially causing I/O-related performance problems.
4) The Difference Between Truncate and Shrink
There’s a lot of confusion surrounding the difference between truncate and shrink. Customer may have truncated log file but still has free space and the file hasn't reduced its footprint at all. This is because a truncation does nothing to the physical size of the allocated file on the O/S. A shrink operation actually clears space from a file and a truncate essentially frees up the used space within that file. This is why shrinking of a log file that is using all of the space won't affect the size and why a truncation of a log file won't reduce the size. A truncation would have to happen first to make room available for the shrink to work.
5) How To Not Run Out of Space in Transaction Log
If customer is in a full recovery mode on a database, that means customer plans on using a combination of full backups and transaction log backups (and possibly differentials). SQL Server understands that intent, and it will not truncate the log file(s) of your database (the .LDF files). Instead, the files will continue to grow until a transaction log backup is taken. The transaction log is a wrap-around file. When the database is created, the logical log file begins at the start of the physical log file. New log records are added at the end of the logical log and expand toward the end of the physical log. When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable
When we help customer with an out of control transaction log growth problem, they are most likely incurring the cost of full recovery mode (with a growing log file, the full logging of qualified events, etc.) but gain none of the benefit. Usually no transaction log backup ever happened. If customer isn’t doing log backups switch database into simple recovery mode.
6) How to Contain Transaction Log
If transaction log is growing out of control, there is a strong possibility that database is in full recovery mode and customer does not backing up log file on a regular basis. The transaction log then continues to grow until you deliberately back it up (a full backup won't do).
Recovery Model Backup Types Supported Advantages Disadvantages FULL Full Differential Trans. Log Can recover database up to a certain point in time or up to a marked transaction Transaction log can grow large quickly; Transaction log should be backed up frequently Bulk-Logged Full Differential Trans. Log Transaction log does not grow as large as with the FULL recovery model. Transaction log can be backed up quicker than with the FULL recovery model Cannot recover database up to a certain point in time or up to a marked transaction. Backing up transaction log that contains bulk logged operations requires access to data files. If data files are not accessible then transaction log backup cannot be taken. Therefore transactions committed since the last transaction log backup will be lost Simple Full Differential Transaction log space is kept to a minimum. Bulk-logged operations are not logged. Database cannot be recovered from a transaction log. All transactions committed after the last full or differential backup can be lost.
7) Source
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189085(v=sql.105)
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms178037(v=sql.105)
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms345414(v=sql.105)
https://docs.microsoft.com/en-us/sql/database-engine/discontinued-database-engine-functionality-in-sql-server
Recommendations
1) Increasing the size of the log drive. This is the easy answer, and it’s the best long-term answer, but sometimes customer don’t have the money to do this, or there’s no space left for drives in the server or the SAN. Add an additional drive for logs, and either move some of the database log files there or add secondary log files on those drives. If customer is doing log backups but still doesn't have enough space, either database activity is quite high or customer allocated space is quite low. If it's the former, customer should try log backups more frequently. If it's the latter and the former, more space for database log files may be required!
2) Switch database to simple recovery mode. This is not to be done lightly. Customer will no longer be able to restore to a point in time recovery.
3) Customer should look at database growth ratio while adding additional space for database: the default 10% for a transaction log. How large is database log file? Is 10% really the right increment customer wants to see it growing by? Or make it less 5%. On that same note, has database log file grown a lot larger than it needed to be because of poor management? Perhaps once customer does first transactional log backup, they should look at setting a reasonable size knowing that it will be truncated (re-used) on a regular basis.