SQL Server Performance Forum – Threads Archive

Re index filling transaction log

Hi, I have 6 GB of data in 68 GB hard disk. There is a single table which is having million of records. When i re-index the transaction log is growing to 30 GB thus filling the hard disk completely. Can any one help me out in this regard. Thanks,
Ramesh

Yes its normal behaviour and to overcome that you can perform the following steps : – During this operation you can set database recovery model to SIMPLE and ensure you maintain complete set of backups in regular intervals.
– Once completed then set back FULL recovery model to enable transaction log backups.
– Shrink the database using DBCC SHRINKDB or shrink transaction log using DBCC SHIRNKFILE. Refer to books online for above DBCC statements.
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.

Satya, i was able to shrik the file using DBCC statement. But i do re-index daily, do i need to shrink manually every day or is there any mechanism which i can schedule the transaction log shrinkage ? Thanks,
Ramesh

Are you doing transaction log backups? If not, you need to set your recovery mode to simple and leave it there. You won’t have to worry about it then. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

There will no point of shrink when you’re performing reindexing, both will work in reverse way.
In this case set Transaction log size to 15gb and perform regular Tlog backups. 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.

Yes. You can write following command for shrinkage the log file in the sechdular.backup log database with truncate_only. Why you want to excute reindex every day? Why you do not use DBCC DBREINDEX instead? Becuase you excute reindex command daily & you have set recovery model as full, it drops the existing index & recreate the index again. During rebuild of index if you are rebuilding cluster index then all non cluster index also gets rebuild. So this could be addition thing of your log file is inflating. First of all in a day how many DML transactions executes on a single table. If it is more than 50% then you think of this option. There is major difference between DBRINDEX & REINDEX that during reindex period if some body execute query on that table then it will be full table scan because index will not be available in that time but it is not in DBRINDEX. For more information refer books online.
Mahesh Paranjpe
SQL DBA

But performing DBREINDEX and SHRINK operation at the same point will not fetch optimum performance, both work in reverse ways. To overcome the situation of transaction file size issue, define the specific size after reindexing and perform SHRINK operation once in a week or perform SHRINK first and then DBREINDEX. 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.

]]>