July 10, 2009 at 3:22 am
Hi masters,
I have a database in Simple Recovery model.
I do not need information on the Log file (because i'm in simple recovery model).
After i rebuild my indexes , my log grows very much (almost a 1GB).
I want to free up space from the log to the operating system.
How can i do this? with a shrink JUST on my log file? will a shrink to the log cause fragmentation on the objects in the datafile of my database (tables, indexes, ....)
thank you,
Pedro
July 10, 2009 at 3:28 am
The problem is if you rebuild your on a regular basis it will be slower if you keep shrinking the log file as it will have to grow with each reindex.
The way round this is to use ALTER INDEX with the SORT_IN_TEMPDB option, this way you can shrink the log file using DBCC SHRINKFILE back to 100Mb or so and then when the index rebuilds it will use the tempdb instead, but then you tempdb would probably need to be kept at the 1 Gb or so it takes to rebuild the index. swings and roundabouts.
It won't cause any fragmentation of the database if you shrink the log file, only on the disk if it keeps growing and then shrinking.
July 10, 2009 at 3:50 am
Yes. To shrink the log file use the DBCC Shrinkfile. In the DBCC command specify your log file name.
Shrinking the log file will not cause fragmentation of objects in the datafile.
use [b]db_name[/b]
go
dbcc shrinkfile (log_file_name, 100)
This will shrink the log file to 100MB.
July 10, 2009 at 4:21 am
"The problem is if you rebuild your on a regular basis it will be slower if you keep shrinking the log file as it will have to grow with each reindex."
that's not a problem in my case, because i do the reindex on non working hours, so , if the database as to grow, it will do the work in non working hours, and i can wait.
The shrink is made too, in non working hours.
Do you see any problems with this case scenario?
"dbcc shrinkfile (log_file_name, 100)"
it's the fisical name of the log file? or the logical?
thank you,
Pedro
July 10, 2009 at 4:27 am
I see no problems if it doesn't affect anyone, it's the logical name.
July 10, 2009 at 5:04 am
ok, thank you veru much
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply