| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2007-02-12 : 16:21:20
|
| Guys,Please correct me if my understanding is wrong about the database using the simple recovery model. Below is my understanding:If the database is using a simple recovery model, the transaction log is truncated each time a CHECKPOINT command is issued. As far as I know, the system can issue the CHECKPOINT command at any time (whenever a certain criteria is satisfied). This implies that backing up the transaction log of the database which is set to use the simple recovery model is pretty useless, no?If so, then why does SQL Server allow us to back up the transaction log (e.g. through a maintenance plan) for the databases using simple recovery model? Why doesn't it let us know that it will not do us much good to do so?Thanks a lot |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-12 : 16:34:49
|
| You'll get an error if you run BACKUP LOG on a database that it set to SIMPLE recovery model. The maintenance plan almost certainly skips the databases using SIMPLE recovery model, otherwise the job would fail each time.Tara Kizer |
 |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-02-12 : 16:48:39
|
| Tara,I just tried 2 things:1. Running BACKUP LOG command on a db in simple recovery model2. Creating maintenace plan for this db and backing up trans logApproach 1 failed, as you said. Approach 2, however, was successful, which was my initial puzzle. Approach 2 resulted in creation of a job having the following code in its step:EXECUTE master.dbo.xp_sqlmaint N'-PlanID E988FF8F-B4A6-4CF6-9FF4-D19245986C52 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -BkExt "BAK"'Are you saying that this job will fail? And if so, why does SQLServer allow us to create it through the maintenance plan?Thanks a lot |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-12 : 16:50:18
|
| I'm not saying it will fail. I'm saying that the maintenance plan probably has code in it to skip databases with SIMPLE recovery model.I don't use maintenance plans as who knows what it is doing!Tara Kizer |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-02-12 : 16:59:08
|
| So, how do you do your backup work?Do you just create jobs yourself for performing backup of db and transaction logs? Is it known to be more efficient?Because as far as I understand it, the Maintenance plan is just a GUI, which generates the job for your itself? Please adviseThanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-02-12 : 17:48:15
|
quote:
Originally posted by sql_er
Tara,I just tried 2 things:1. Running BACKUP LOG command on a db in simple recovery model2. Creating maintenace plan for this db and backing up trans logApproach 1 failed, as you said. Approach 2, however, was successful, which was my initial puzzle. Approach 2 resulted in creation of a job having the following code in its step:EXECUTE master.dbo.xp_sqlmaint N'-PlanID E988FF8F-B4A6-4CF6-9FF4-D19245986C52 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -BkExt "BAK"'Are you saying that this job will fail? And if so, why does SQLServer allow us to create it through the maintenance plan?Thanks a lot
You can create a maintenance plan transaction log backup on a database in simple recovery mode, and it will fail when it runs.SQL Server is not checking the recovery model when you setup the maintenance plan. They also are not preventing you from changing the recovery mode after you setup a transaction log backup, or running statements that would invalidate your backups.I guess they expect that people setting up backups will know what they are doing. CODO ERGO SUM |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-02-12 : 18:00:25
|
| Tara ... thanks for the advice and the code. I'll explore it more later.Michael ... that is exactly the impression I got. And it is true that the backup is too important to be set up by someone who does not understand the basics of backup recovery models and its implications. I was just trying to understand whether I am missing something or the SQL server is missing something :-)Thanks a lot |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-02-12 : 18:38:20
|
| You can do differential backups of databases in SIMPLE recovery mode. I have implemented this solution on systems that were in simple recovery mode so we could get more granular recovery. We ran DIFFs every half hour and FULLs every night. THis way we could recover to the half-hour, which was perfect for our situation. YMMV.-ec |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-02-13 : 10:04:46
|
| That's another great suggestion. Thanks! |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-02-13 : 11:00:30
|
| Another related question: Does the transaction log of the database following a simple recovery model ever grow?And if so, does it imply that we need to shrink it periodically?Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-13 : 11:31:57
|
| Yes it can grow. No you do not need to shrink it, unless you are completely out of disk space on that drive. But even still, you should just add more disk space. The reason why you shouldn't shrink a database is that there was a reason why that file grew in the first place. So you would need to determine if it will need that space again. Shrinking it causes a huge performance problem while shrinking plus again when the file needs to expand as it's now too small.Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-13 : 11:37:23
|
| 1. Yes. If you do a Mega Transaction, bigger than before, the LDF file will be extended. Same might be true of, for example, a Reindex - as the table grows the Reindex will need more elbow-room2. Generally not. If you Shrink the LDF and it has to grow again it takes quite a lot of resources (which may cause queries to timeout etc.) and Shrink/Re-grow will fragment the file. So best to only Shrink it after some exception growth (such as a one-off delete of a huge number of records)Edit: As Tara has said. My Mum called whilst I was writing that ... we chatted for a while!Kristen |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-02-13 : 12:12:55
|
| Thanks for the info.So, to sum it up: For a database following a simple recovery model the transaction log CAN physically grow - and although it will be truncated every time a CHECKPOINT command is executed, the file size will remain the same.If it grows due to a periodic maintenance work, which we would expect to happen again and again (e.g. reindexing job), then we should not shrink it, as this space would be needed by a transaction log again and shrinking it now will waste resources now and later - when it is to be extended again.If, however, it grows unexectedly, because we made a huge delete, which we would not expect to happen often, we might consider shrinking the transactional log file to decrease the hard disk space used - that decision has to come after weighing pros (hard disk space) vs cons (resource utilization during shrink) and making the proper decision! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-13 : 12:33:40
|
| Correct on all points!Tara Kizer |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-02-14 : 10:28:30
|
| Another related question: When I delete rows from a table in a database following a simple recovery model, should I always use TRUNCATE instead of DELETE?I know that TRUNCATE is faster, and since a CHECKPOINT can occur at any point in time, truncating the log anyway, is it wiser to use TRUNCATE instead of DELETE?Thanks in advance |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-14 : 12:04:07
|
| TRUNCATE is indeed faster but you can't use it when you have foreign key constraints. So sometimes DELETE is the only option if you aren't able to drop the constraints temporarily.Tara Kizer |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-02-14 : 12:52:41
|
| But if there are no foreign keys defined on the table, would it then be ok, or could there be other scenarios where using TRUNCATE would be advised against?Also, back to the previous topic, why does the system grow the transaction log at all when we use the simple model for the database? If the transaction log cannot be backed up anyway and therefore not used in recovery, why would the system waste resources for logging the user transactions and then truncating them only at CHECKPOINT? Is this done so that we can rollback a transaction in process? And if so, does this imply that an automatic CHECKPOINT cannot occur (i.e. be generated by a system) while there still are open transactions?Thank you |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-14 : 12:57:03
|
| SQL Server still has to use the transaction log regardless of the recovery model. Even though you don't have the ability to restore to a point in time, you still have the ability to rollback transactions.Yes TRUNCATE can be run if you don't have FK constraints.Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-14 : 13:38:17
|
| DELETE v. TRUNCATEDoes TRUNCATE TABLE reset the IDENTITY seed (assuming you have an identity column)?Can't remember, sorry!Kristen |
|
|
|
jayp369
Starting Member
26 Posts |
Posted - 2007-02-14 : 13:46:15
|
| Yes, TRUNCATE TABLE does reset the IDENTITY seedJay |
|
|
|
Next Page
|
Puzzled by simple recovery model and transaction l