SQL Server Performance Forum – Threads Archive

sql server transaction logs

I would like to know how transaction log size is managed. I now know after some
reading that I can checkpoint the database in order to allow dirty pages to write from ram to disk.I also know now how to scedule backups of transaction logs and also the database,but I still don,nt understand how to mange there size I’ve heard of the transaction log becoming full.After I back this log up should I go and delete it how does this work. I plain on backing up the database daily and checkponting about every 60 minutes but I’m trying to see how the tranaction logs play into this. I know I can have them on a backup interval also.

What recovery model do you have? Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I have full recovery models in place

There are some caveats, one of the most obvious of which is that if there#%92s an open and long-running transaction, you won#%92t be able to truncate the log as such. This Truncating a transaction log may not necessarily recovery the space that has been allocated on the disk. Here you need to shrink the underlying file (Enterprise Manager#%92s Taskpad is the easiest way in my experience) to recover the space on disk. But you#%92re better off ensuring the upper limit on the transaction log size is specified so it never grows beyond what you#%92re happy with in the first place. If the disk space is the concern then try to analyze the required size from all scheduled jobs and updation on the database. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Space in transaction log occupied by completed transactions becomes available for reuse after transactions log backup.
It means only still running transactions and transaction completed after last transaction log backup take space that can’t be reused.
So you need to backup transaction log frequently enough to release the space of completed transaction for reuse.
That still may not solve the problem of long-running transactions that may not be completed even after several transaction log backups.
To avoid tran log to grow because of that you should split them into multiple smaller transactions. The most frequent processes causing long running transactions are:
1. Maintenance tasks like rebuilding indexes, altering tables to add/change pk constraints, column etc…
2. Batch inserts/updates/deletes. To avoid the first problem never run maintenance tasks in parallel. Rebuild indexes only after they are fragmented. Define proper fill factor for indexes.
To split batches into smaller transactions just make loop that will insert/update/delete a few thousands rows per one iteration until the job is completed.

Read from old post, hope this may give you some more thoughts http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8018 Deepak Kumar –An eye for an eye and everyone shall be blind

hi, i also had this problem once and to solved it i just do regular Tlog file backup and truncate the log afterwards. AKTHAR DILMOHAMUD
65 BENARES ST
PORT LOUIS
MAURITIUS

maybe this can help http://www.dbazine.com/sql/sql-articles/mullins-sqlserver AKTHAR DILMOHAMUD
65 BENARES ST
PORT LOUIS
MAURITIUS

]]>