August 16, 2010 at 4:50 am
how are you measuring log file size?
---------------------------------------------------------------------
August 16, 2010 at 6:43 am
george sibbald (8/16/2010)
how are you measuring log file size?
by Property of file ("right clicking") LDF & .trn
August 16, 2010 at 8:10 am
according to SQL2008 BOL notruncate and truncateonly are not applicable to log files. So in fact what is happening is notruncate and truncateonly are ignored when it is a log file. I misunderstood the implications of this so my bad. This in fact means that the command you are running is in fact reducing the log file as much as it is able to (i.e. to the last active virtual log file)
So, leaving apart the fact in normal circumstances you would not run shrinkfile this often, specify a size to shrink to when you run the shrinkfile, i.e. DBCC SHRINKFILE ( MYDB_log' ,25).
---------------------------------------------------------------------
August 16, 2010 at 7:10 pm
Rajat Jaiswal-337252 (8/16/2010)
Hi Thanks Jeffery,I just want to reduce log file size because there is size issue on my production database drive.
and the log become 20 GB to 30 GB with in a week.
any other solution while " Log shipping is implemented on database "
Thanks
Rajat
Well, if you are having space issues on the drive - shrinking the log file is not going to help. The file is going to have to grow again and you'll be in the same issue as before. The solution to this is to get more disk space, or - identify the processes that are growing the log file and tune them.
If you are rebuilding all indexes on a weekly basis, that could be the cause. To fix this, you should only rebuild indexes that are fragmented.
If the growth is caused by some other process (e.g. data load, large transaction) - these may or may not be possible to fix. One way to approach the fix is to batch the load or update or delete so you have smaller transactions and increase the frequency of transaction log backups. This way, the transaction log backup can mark the space as reusable and the log file won't need to grow as large.
Log file growth is a very expensive operation - depending upon the growth size, it could take several minutes to grow. Any processes in progress wil have to wait for that growth to happen - which could cause those process to take twice as long as needed.
I would recommend that you look at other ways of solving your problem - shrinking the log is not a solution and will not help.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 17, 2010 at 6:44 am
Rajat Jaiswal-337252 (8/16/2010)
Hi Thanks Jeffery,I just want to reduce log file size because there is size issue on my production database drive.
and the log become 20 GB to 30 GB with in a week.
any other solution while " Log shipping is implemented on database "
Thanks
Rajat
The log will not shrink past the last active transaction which could be occupying the end of the log. Issue the following command against your database
DBCC LOGINFO
scroll down and check the status column. If you have any status of 2 near the end of the returned results you have active transactions occupying the end of the log
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 18, 2010 at 8:13 am
Hi Experts,
What ever you people said will be fine but only one question
"Why we should not shrink Log file while its given by mircorosoft ?"
What are the drawbacks ?
August 18, 2010 at 8:48 am
its available because its something you might have to do if space is a problem. there is a command to drop a database also, doesn't mean you should as a way of reclaiming space.
shrinking causes massive fragmentation of data within data files leading to performance degradation
chances are file will need to grow again leading to performance degradation and resource wastage
chances are file will need to grow again leading to physical fragmentation on disk causing performance degradation
too many virtual log files in a log file affects recovery and log backup performance
---------------------------------------------------------------------
August 18, 2010 at 9:04 am
George has good reasons. Shrinking log files is mostly a waste of resources if done regularly. It is really there for fixing specific problems from one-off situations.
August 18, 2010 at 11:38 pm
george sibbald (8/18/2010)
its available because its something you might have to do if space is a problem. there is a command to drop a database also, doesn't mean you should as a way of reclaiming space.shrinking causes massive fragmentation of data within data files leading to performance degradation
chances are file will need to grow again leading to performance degradation and resource wastage
chances are file will need to grow again leading to physical fragmentation on disk causing performance degradation
too many virtual log files in a log file affects recovery and log backup performance
Thanks George,
got your point .
so in some case we can use Shrink file. This command will not harm database right ?
August 19, 2010 at 12:49 am
Have you checked DBCC Loginfo yet?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 19, 2010 at 3:55 am
Rajat Jaiswal-337252 (8/18/2010)
george sibbald (8/18/2010)
its available because its something you might have to do if space is a problem. there is a command to drop a database also, doesn't mean you should as a way of reclaiming space.shrinking causes massive fragmentation of data within data files leading to performance degradation
chances are file will need to grow again leading to performance degradation and resource wastage
chances are file will need to grow again leading to physical fragmentation on disk causing performance degradation
too many virtual log files in a log file affects recovery and log backup performance
Thanks George,
got your point .
so in some case we can use Shrink file. This command will not harm database right ?
Ir wont corrupt the database or anything like that and a shrinkfile can be canceled. Having said that I am in the habit of backing up prod databases before a MAJOR shrink, but thats just my prod DBA side coming out.
shrinking a data file will fragment it. All a shrink does is move all the extents to the front of the file, it does not care which extents go where, this harms performance. So if you shrink a data file, rebuild the indexes afterwards.
---------------------------------------------------------------------
August 19, 2010 at 4:29 am
george sibbald (8/19/2010)
So if you shrink a data file, rebuild the indexes afterwards.
which could well end up growing the file again anyway!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 26, 2010 at 1:02 am
Hi,
I run the job manually for log shipping instead of scheduler.
Now re creating and applying basic hope it will resolve my confusion too.
mean while if you people have some time please checkout the cases.
Thanks for your help.
Thanks a lot
Rajat Jaiswal
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply