?
Solved

SQL-7 EXPERTS - Shrink and Truncate

Posted on 2001-06-15
3
Medium Priority
?
1,148 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

770 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