Solved

SQL-7 EXPERTS - Shrink and Truncate

Posted on 2001-06-15
3
1,146 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 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

724 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