December 14, 2011 at 7:39 am
I ran shrink DB command after that index rebuild against database, it caused the transaction log to blow up(it did grow larger)?I try all options but no luck( i did run shrink file truncate option aswell against LDF file)
Thanks
December 14, 2011 at 7:42 am
Backup Transaction Logs Immediately.
December 14, 2011 at 7:48 am
I did backup the transaction log and ran shrinkfile with truncate only but it didnt release the space yet?
Thanks
December 14, 2011 at 7:54 am
What does this return?
SELECT log_reuse_wait_desc FROM sys.databases where name = 'your db name here'
December 14, 2011 at 7:55 am
Many factors can delay it... Please check for long running active transactions first.
Factors That Can Delay Log Truncation
http://msdn.microsoft.com/en-us/library/ms345414(v=SQL.105).aspx
December 14, 2011 at 7:57 am
This works only on the indexes that need help :
http://sqlfool.com/2011/06/index-defrag-script-v4-1
Shrink the file ONCE. Then setup the job. If the log regrows leave it alone. It just needs to be that big.
December 14, 2011 at 12:49 pm
This especially some what tricky question & you cannot estimate how you can control the Logfile from the grow during rebuild index, because you know that rebuilding an index it is not an simple task's because it has to go with 3 processes i,e Preparation Phase,Build Phase and Final Phase (please refer the whitepaper of MS ->to know how Online index works) --before dropping the index it has to keep the old structure in case if the rebuild index fails it has o rollback to ensure about ACID rule.
**user has to think before running the rebuilding index whether he performing his own script or via Maintenance plan -what factors can Improve or survive that at least the activities completes(If going to perform first time), if Improperly set auto growth for log file & No logbackup happened since from very long time & performing rebuild index during other transaction activits running on the same database.
I believe that every one will follow the some or their own standard rues & procedures during the rebuild index ->Keeping sort_tempdb =ON on tempdboption -->verifying the Tempdb grow utilization, ,considering about the Fillfactor,Transaction log file & Filegroup space consideration,Tempdb space consideration i,e tempdb usage in case if you have used sort_tempdb =ON,Control Parallelism and Memory Resources,what indexes are those which you are rebuilding ,switching recovery models,etc.....
Note:-
---
1.In SQL server 2008 what every may be the recovery models the rebuild index operation is fully logged.
2.Determining Index Disk Space Requirements:
Please refer the link :http://msdn.microsoft.com/en-us/library/ms191163.aspx
Best Regards,
Rama Udaya.K
ramaudaya.blogspot.com
December 15, 2011 at 4:05 am
Your database might be in full recovery model,
Try to change your database into simple recovery model with the below query:
Alter database [Your database Name] set recovery simple
and now try to shrink the log file with truncateonly..
December 15, 2011 at 4:25 am
MasterDB (12/15/2011)
Your database might be in full recovery model,Try to change your database into simple recovery model with the below query:
Alter database [Your database Name] set recovery simple
and now try to shrink the log file with truncateonly..
You can also run your car without tires.
I wouldn't do either unless my life was in danger.
http://qa.sqlservercentral.com/articles/Transaction+Log/72488/
http://qa.sqlservercentral.com/articles/Administration/64582/
December 15, 2011 at 4:35 am
if the database is not freeing log space.
someone running backup of that database (third party tool).
if backup is not running, and log_reuse_waits is full . Transactions will not reflected into database.
My view is if database is in full recovery mode, it will not free's the log space.
so i told to alter recovery model of the database, so that it releases the free amount of log space in database.
before shrinking log backups, needs to take the backup of log file.
kindly, clarify me if am wrong.
December 15, 2011 at 4:44 am
MasterDB (12/15/2011)
if the database is not freeing log space.
It never will free log space unless autoshrink is own (awful idea), or you run shrink. It marks log space as reusable.
MasterDB (12/15/2011)
someone running backup of that database (third party tool).
if backup is not running, and log_reuse_waits is full . Transactions will not reflected into database.
My view is if database is in full recovery mode, it will not free's the log space.
Reread the articles. I'm not sure you really understand this topic and your english seems a little weak as well so it might just be langage barrier.
MasterDB (12/15/2011)
so i told to alter recovery model of the database, so that it releases the free amount of log space in database.
before shrinking log backups, needs to take the backup of log file.
kindly, clarify me if am wrong.
Again switching to simple and <waiting for or> running checkpoint will just mark the virtual log files as reusable. Then the shrink command should work (assuming no more active transaction preventing this).
Switching to simple breaks the log chain and you HAVE TO restart one or you lose PIT restore all the way untill your next full/diff backup. Hence the awefully bad idea like cars on rim rather than tires.
December 15, 2011 at 4:58 am
Thanks for clarification...
i'll go through your links posted in previous comments.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply