We have a couple of databases which are in simple recovery model. When the weekly rebuild job runs, the tlog drive is becoming full every time. How do I control the size tlog from every growing?Because these are in simple recover – should I be resizing the tlog autogrowth in chunks of 512mb instead of 10%. Would that help?
Sql-server – truncating transaction log for simple recovery databases
backupsql servertransaction-log
Related Solutions
Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?
Even taking a transaction log backup is not supported for databases using the SIMPLE
recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.
A transaction log backup maintenance plan task automatically skips databases in SIMPLE
recovery to avoid causing errors.
Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.
For the reasons I mentioned above, it won't matter for databases using SIMPLE
recovery, as they will be skipped by the transaction log backup task.
For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.
Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.
Even though SIMPLE
recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)
When should I use the full recovery model and when should I use the simple recovery model for databases?
You should use the full recovery model when you require point-in-time recovery of your database. You should use simple recovery model when you don't need point-in-time recovery of your database, and when the last full or differential backup is sufficient as a recovery point. (Note: there is another recovery model, bulk logged. For more information on the bulk-logged recovery model see this reference)
Microsoft OLE DB Provider for SQL Server (0x80040E14) The transaction log for database 'DATABASE NAME' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
The reason you got that error (most likely) is because you haven't been backing up your transaction log. When it isn't backed up, then it will continue to physically grow the transaction log file (provided autogrowth is enabled and maxsize allows) because it can't reuse any of the "portions" of the transaction log (virtual log files). It can only mark those VLFs for reuse and allow the "wrap-around" nature of the transaction log when you do a transaction log backup (and a few other requirements, such as no active transactions, some replication aspects, etc.).
To shrink the log and making the database accessable again, I changed the recovery model from FULL to SIMPLE and shrinked the logical file log, with the following command
......
It helped, but now I need to understand WHY it helped, HOW this situation started and HOW to prevent this in the future?
This helped you because by setting your database to the simple recovery model you told SQL Server that you no longer care about point-in-time recovery, and the requirement to ensure that virtual log files no longer need to be preserved and marked as active, now a checkpoint process marks these VLFs as inactive.
Excerpt/quote taken from this MSDN reference:
Under the simple recovery model, unless some factor is delaying log truncation, an automatic checkpoint truncates the unused section of the transaction log. In contrast, under the full and bulk-logged recovery models, once a log backup chain has been established, automatic checkpoints do not cause log truncation.
Then you did a physical database file shrink and because there was free space in your transaction log now it was able to physically shrink the NTFS file.
Reading worth spending some time on:
EDIT after your Edit:
Is the new recovery model and databaseshrink going to be a conflict with this script?
That BACKUP DATABASE
command will work with either recovery models. As for the routine database shrink... DON'T DO IT!!!! Seriously, size your database accordingly, and if you utilize the full recovery model then ensure that you are doing routine and frequent transaction log files, not just to keep the transaction log size at bay but to also meet recovery point objects.
We are not doing any other kind of backup of the databases, and therefore not the transaction logs, should we?
If your database is utilizing the full recovery model, then yes you should be doing transaction log backups. If your database is in simple recovery, then you physically can not do a transaction log backup.
As for what recovery model to use (simple vs. full), we can't make that decision for you. Only you, your business team, and your SLAs can.
Related Question
- Sql-server – Transaction log full due to log_backup
- SQL Server – Why Transaction Log Grows in Simple Recovery Mode with Nightly Backups
- Sql-server – causing the Transaction log to grow
- Sql-server – Viewing how much transaction log is used in Simple Recovery Model
- SQL Server – Switching to Simple Recovery and Shrinking Transaction Logs
- SQL Server – How to Reduce LOG File After Switching to Simple Mode
- SQL Server – Transaction Log Full in Simple Recovery Mode
Best Answer
Just because database is in simple recovery does means transaction log will not grow. Actually there is not much difference in terms of logging in full and simple recovery.
In simple recovery model transaction log is truncated when checkpoint is fired and log file grows 70% of its size subject to condition no long running transaction is holding the logs in your case it is the index rebuild which is holding things from being truncated leading to log file increase.
What you can do is use smart index rebuild technique dont rely on SSMS. There are quite few available Ola Hallengren Index rebuild, Minion Index rebuild and My Custom Script.
It may also be possible that the drive on which log file resides needs more space because your indexes are big and needs space and you have to add more space.
Please also read Why Does the Transaction Log Keep Growing or Run Out of Space?