SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Puzzled by simple recovery model and transaction l
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 02/12/2007 :  16:21:20  Show Profile  Reply with Quote
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

USA
36941 Posts

Posted - 02/12/2007 :  16:34:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 02/12/2007 :  16:48:39  Show Profile  Reply with Quote
Tara,

I just tried 2 things:
1. Running BACKUP LOG command on a db in simple recovery model
2. Creating maintenace plan for this db and backing up trans log

Approach 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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 02/12/2007 :  16:50:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 02/12/2007 :  16:59:08  Show Profile  Reply with Quote
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 advise
Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 02/12/2007 :  17:22:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
The maintenance plans use an executable, so you can't see what code it is using behind the scenes. I prefer to run stuff where I know exactly what it is doing.

Here's what I use (from my blog):
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Tara Kizer
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/12/2007 :  17:48:15  Show Profile  Reply with Quote
quote:
Originally posted by sql_er

Tara,

I just tried 2 things:
1. Running BACKUP LOG command on a db in simple recovery model
2. Creating maintenace plan for this db and backing up trans log

Approach 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
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 02/12/2007 :  18:00:25  Show Profile  Reply with Quote
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
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 02/12/2007 :  18:38:20  Show Profile  Reply with Quote
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
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 02/13/2007 :  10:04:46  Show Profile  Reply with Quote
That's another great suggestion. Thanks!
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 02/13/2007 :  11:00:30  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 02/13/2007 :  11:31:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/13/2007 :  11:37:23  Show Profile  Reply with Quote
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-room

2. 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

Edited by - Kristen on 02/13/2007 11:38:24
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 02/13/2007 :  12:12:55  Show Profile  Reply with Quote
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!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 02/13/2007 :  12:33:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
Correct on all points!

Tara Kizer
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 02/14/2007 :  10:28:30  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 02/14/2007 :  12:04:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 02/14/2007 :  12:52:41  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 02/14/2007 :  12:57:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/14/2007 :  13:38:17  Show Profile  Reply with Quote
DELETE v. TRUNCATE

Does TRUNCATE TABLE reset the IDENTITY seed (assuming you have an identity column)?

Can't remember, sorry!

Kristen
Go to Top of Page

jayp369
Starting Member

USA
26 Posts

Posted - 02/14/2007 :  13:46:15  Show Profile  Click to see jayp369's MSN Messenger address  Reply with Quote
Yes, TRUNCATE TABLE does reset the IDENTITY seed

Jay
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000