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
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
there is no problem in shrinking, why do you want it to shrink to 50MB? is there any specific reason?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so ritesh that would shrink it to 50 MB right ?
ASKER
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?
than why 50MB, what makes any difference if it become even 1MB. is there any specific reason you want it to be 50MB only?
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
http://sqlservernation.com/blogs/admin/archive/2009/05/17/why-is-my-log-file-so-big.aspx
ASKER
thanx