Solved

SQL-7 EXPERTS - Shrink and Truncate

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql server tables from access 18 19
insert wont work in SQL 14 22
New to SSRS, extremely slow running report. 8 20
SQL server vNext 18 29
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

810 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