Truncate Transaction Log

If the transaction log is not being backed up, it will eventually grow to its maximum size or run out of disk space. The first thing to try when the transaction log is full is to perform a backup of it. USE MASTER GO ALTER DATABASE myproj SET RECOVERY FULL; GO BACKUP DATABASE myproj TO DISK = '\\server01\data$\myprojData.bak'; GO BACKUP LOG myproj TO DISK = '\\server01\data$\myprojLog.bak'; GO ALTER DATABASE myproj SET RECOVERY SIMPLE; GO Transaction log A record of modifications performed to a database. The amount of information logged in the transaction log depends on the recovery model for a database. Transaction Log Backup Backs up the active portion and truncates the inactive portion of the transaction log. http://technet.microsoft.com/en-us/library/cc966495.aspx Recovery Models Simple The simple recovery model does what it implies, it gives you a simple backup that can be used to replace your entire database in the event of a failure or if you have the need to restore your database to another server. With this recovery model you have the ability to do complete backups (an entire copy) or differential backups (any changes since the last complete backup). With this recovery model you are exposed to any failures since the last backup completed. Here are some reasons why you may choose this recovery model: Your data is not critical and can easily be recreated The database is only used for test or development Data is static and does not change Losing any or all transactions since the last backup is not a problem Data is derived and can easily be recreated Type of backups you can run: Complete backups Differential backups File and/or Filegroup backups Partial backups Copy-Only backups Under the simple recovery model, the transaction log is automatically truncated to remove any inactive virtual log files. Truncation usually occurs after each CHECKPOINT but can be delayed under some conditions, such as a long-running transaction, which can keep large portions of the transaction log in an "active" state. http://msdn.microsoft.com/en-us/library/ms345414.aspx Bulk_Logged The bulk logged recovery sort of does what it implies. With this model there are certain bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc... that are not fully logged in the transaction log and therefore do not take as much space in the transaction log. The advantage of using this recovery model is that your transaction logs will not get that large if you are doing bulk operations and you have the ability to do point in time recovery as long as your last transaction log backup does not include a bulk operation as mentioned above. If no bulk operations are run this recovery model works the same as the Full recovery model. One thing to note is that if you use this recovery model you also need to issue transaction log backups otherwise your database transaction log will continue to grow. Here are some reasons why you may choose this recovery model: Data is critical, but you do not want to log large bulk operations Bulk operations are done at different times versus normal processing. You still want to be able to recover to a point in time Type of backups you can run: Complete backups Differential backups File and/or Filegroup backups Partial backups Copy-Only backups Transaction log backups Full The full recovery model is the most complete recovery model and allows you to recover all of your data to any point in time as long as all backup files are useable. With this model all operations are fully logged which means that you can recover your database to any point. In addition, if the database is set to the full recovery model you need to also issue transaction log backups otherwise your database transaction log will continue to grow forever. Here are some reasons why you may choose this recovery model: Data is critical and data can not be lost. You always need the ability to do a point-in-time recovery. You are using database mirroring Type of backups you can run: Complete backups Differential backups File and/or Filegroup backups Partial backups Copy-Only backups Transaction log backups