Solved

SQL-7 EXPERTS - Shrink and Truncate

Posted on 2001-06-15
3
1,137 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
ID: 6196257
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
ID: 6197528
----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
ID: 6215796
Once again the same dilemna. Only one answer can be accepted.

Thanks.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

914 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

17 Experts available now in Live!

Get 1:1 Help Now