Solved

SQL-7 EXPERTS - Shrink and Truncate

Posted on 2001-06-15
3
1,134 Views
Last Modified: 2007-12-19
Hi:

Could someone please indicate the best way to use 'AUTO SHRINK' and 'TRUNCATE LOG ON CHECKPOINT' options?

Can Auto Shrink work without selecting the Truncate Log on CheckPoint option?

Does Auto Shrink shrink both the database and the tlog?

Are any transactions actually deleted by Auto Shrink or does it just eliminate the unused space in transaction log and database?

If Auto Shrink deletes any transactions, which transactions are deleted?

What happens to the tlog when Truncate Log on CheckPoint is selected (which transactions are marked inactive or removed)?

What is meant by inactive transactions (one month old, two months old,.........)?

If Truncate Log on CheckPoint is turned on, and a transaction log backup is done, some of the transactions will be removed from the tlog and written to the tlog backup. Does this mean that these removed records will be inaccessible through the database until the backed up tlog is appended to the active tlog?

I have these settings:

1. Auto Shrink ON
2. Truncate Log on CheckPoint OFF
3. Daily Full TLog backup ON
4. Weekly Full DB backup ON
5. Delete TLog backup files older than 8 days
6. Delete DB backup files older than 8 days

Assuming there are no space issues, is this plan OK?

Thanks.
0
Comment
Question by:muhammasif
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
Comment Utility
Actually, i do not use the AUTOSHRINK option, as i do actually handle the shrinkage of the datafiles "manually".
The truncate Log On Checkpoint is only to be activated on development systems and datawarehouses.

Transactions are only deleted if they are committed, even if you choose truncate log. If you use the Truncate log on Checkpoint, any committed transactions can immediately be overwritten, otherwise you must issue a backup (either transaction log, incremental or full) to make transaction log overwritable.

The transaction log is actually a sort of circular buffer, so if the log writes is at the end of the file, there are 2 options:
(the log file can automatically grow) the file is increased in size. If the file cannot be expanded, the log writer restarts at the begin of the file
(the log file size is fixed) the log writer restarts to write at the beginning of the file

In both cases, if there are transaction that cannot be overwritten, an error occurs for the user(s) that issued the transactions.

So when you choose the option truncate log on checkpoint, any committed transaction entry can be overwritten, otherwise it must have been archived.

Inactive transactions are open transactions where there has not been activity since some time, but i don't know what delay this is.

If you turn on Truncate Log on checkpoint, you will not be able to issue a "successful" transaction log backup, as all the committed transactions are marked as overwritable.
The transaction log backup looks to find committed transaction that are not marked as overwritable, copies them to the backup, and then markes those transactions as overwritable.
This does not mean any difference to the data that the transaction added/removed/updated. The transaction log is only an option for the server to undo the transactions...

These settings can be ok, if the database is not under heavy load. If you have high load, you should have daily incremental/differential backups, and the transaction log backups per hour or even more frequently.

CHeers




0
 
LVL 2

Expert Comment

by:sefa
Comment Utility
----Could someone please indicate the best way to use 'AUTO SHRINK' and 'TRUNCATE LOG ON CHECKPOINT' options?

Do not use AUTO SHRINK. It reduces the system performance. It is better to do this operation manually.
Transaction log backup's are important for recovery or standby servers.When a full recovery is needed they are used after restoreing the last full backup or they transfered to standby server then applied.As angellll said this option should be used on the development,test or datawarehouse systems.

----Can Auto Shrink work without selecting the Truncate Log on CheckPoint option?
Yes. they have no relation.

----Does Auto Shrink shrink both the database and the tlog?
Yes...

----Are any transactions actually deleted by Auto Shrink or does it just eliminate the unused space in transaction
log and database?

Shrink opration only remove unused pages from the database.Transaction information is also a data in the log and allocate space even though it is committed or not.

----If Auto Shrink deletes any transactions, which transactions are deleted?
N/A

----What happens to the tlog when Truncate Log on CheckPoint is selected (which transactions are marked
inactive or removed)?
Any transaction which has not yet committed or rolled back is an open transaction.If the session of the transaction is performing an activity on the database through this transaction, then is an active transaction.
Truncate Log On Checkpoint removes only committed/rolled back transaction information from the log.

----What is meant by inactive transactions (one month old, two months old,.........)?
see above


----If Truncate Log on CheckPoint is turned on, and a transaction log backup is done, some of the transactions
will be removed from the tlog and written to the tlog backup. Does this mean that these removed records
will be inaccessible through the database until the backed up tlog is appended to the active tlog?
Yas..


----I have these settings:
----Assuming there are no space issues, is this plan OK?
these looks OK...

regards...
0
 

Author Comment

by:muhammasif
Comment Utility
Once again the same dilemna. Only one answer can be accepted.

Thanks.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now