Link to home
Start Free TrialLog in
Avatar of aatishpatel
aatishpatel

asked on

log file shrink script

Hello friends, I have posted a script earlier and this is the other one. And please this is not a respost  just want to make sure I am doing the right think. This script shrinks my log (ldf) files to 10.00 MB. But I do not find where should I make changes to the script and it should only shrink to 50 MB. And if someone has a better script then please give me.  Your help is really appreciated.
use master
DECLARE @Statement varchar (2000)

SELECT @Statement = ''
SELECT @Statement = @Statement + 'USE ?; '
SELECT @Statement = @Statement + 'SELECT ''?''; '
SELECT @Statement = @Statement + 'DECLARE @Log_Logical_FileName varchar (30); '
SELECT @Statement = @Statement + 'SELECT @Log_Logical_FileName = rtrim(name) FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement = @Statement + 'dbcc shrinkfile (@Log_Logical_FileName, 51200,truncateonly); '
SELECT @Statement = @Statement + 'SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement

EXEC sp_MSforeachdb @command1=@Statement
GO
Avatar of RiteshShah
RiteshShah
Flag of India image

there is no problem in shrinking, why do you want it to shrink to 50MB? is there any specific reason?

ASKER CERTIFIED SOLUTION
Avatar of RiteshShah
RiteshShah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of aatishpatel
aatishpatel

ASKER

so ritesh that would shrink it to 50 MB right ?
and we hab=ve to shrink the drive is small and no chances to replace the big drive.
it suppose to. isn't it? actually this is something we can't control 100% I guess, if you have some active transaction which still needs space, it won't get truncated.
>>and we hab=ve to shrink the drive is small and no chances to replace the big drive.<<

than why 50MB, what makes any difference if it become even 1MB. is there any specific reason you want it to be 50MB only?
Avatar of chapmandew
You'll NOT be able to shrink it if there are inactive transactions taking up space.  Do a log backup first.

http://sqlservernation.com/blogs/admin/archive/2009/05/17/why-is-my-log-file-so-big.aspx
thanx