May 20, 2009 at 2:49 pm
does anybody have or can write a good script to shrink transaction log files to 50MB. which i will run every sunday. your helpo is really appreciated.
May 20, 2009 at 3:10 pm
Scheduling a shrink of your transaction logs every week is a mistake you really don't want to make. Review the article I link to in my signature which explains how to manage the transaction logs.
Once you have reviewed that article - you should have a better idea of how to handle whatever problems you are currently encountering.
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
May 20, 2009 at 4:38 pm
I agree 100% with Jeffrey Williams.
Your transaction log grows to the size it needs to be in normal use. If you have utility jobs (e.g. Defragmentation) then this is still the case.
If you shrink them, they will just grow again next time. Only probably with more operating system level file fragmentation.
The only time you should shrink either .mdf (data), .ndf (more data), or .ldf (log) is where you have taken some exceptional action which you know has expanded them and you need to shrink them again.
If you plan carefully, these occasions will be few and far between.
Tim
.
May 20, 2009 at 6:43 pm
but i cannot keep the database in simple recovery mode my database should be at all time b ein full recovery mode. i got a script from somewhere and it shrinks the log (ldf) file to 10.00 MB. but i want to restrict it to 50 MB only so where i change the value. actually i do not understand the script. let me know where to make applicable changes to shrink to 50 MB.
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
May 20, 2009 at 8:47 pm
Please review the article in my signature and learn how to manage your transaction logs. Do not shrink the transaction logs on a regular basis. Doing so will cause file level fragmentation and performance issues as the log needs to grow.
If you have to keep your database in full recovery model - you need to schedule frequent transaction log backups, not shrink the transaction log on a regular basis.
And seriously, a 50MB transaction log is nothing. I have systems where the transaction log is more than 50,000MB, and that is to handle the batch processing done weekly and off hours. I backup the transaction log on this system every 15 minutes and the backups can be several gigabytes.
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
May 21, 2009 at 12:22 pm
May 21, 2009 at 12:27 pm
pradyothana (5/21/2009)
Thanks Jeffrey Williams ...for Link in your signature
Really - you should thank Gail for the article.
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
May 22, 2009 at 8:40 am
Run the DBCC shrinkdatabase command to shrink your database size. You can also use the DBCC shrinkfile option as well.However, as mentioned earlier, by the other respectable members, you should not follow this way to compress your log files as it is not the best practice. However, its really acceptable if you are doing it on a test database to test your own methods.
Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly
May 22, 2009 at 2:27 pm
hi folks,
can any one explain me when i should use shrink database command.What exactly happens when i do that? Plz explain under what cases i have to go for this option in real time scenarios.
Is this a best way to take a back up? like shrinking the transaction log and then take a backup? Let me know if i'm wrong?
thanks for your help.
May 22, 2009 at 2:50 pm
Sam,
You shouldn't run the Shrink Database command. That is the point that Jeffrey and Tim were trying to make in their posts.
As mentioned the only time you may want to do a shrink is if you have done an out of ordinary action against the database, like archiving all but 2 years of data to another database and then deleting that data from the current database. Then you can do a shrink, but you will also need to rebuild/reorganize you indexes at that time as well.
When in doubt don't shrink. If you have the space, don't shrink.
Read this article, http://www.sqlservercentral.com/articles/64582/
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 23, 2009 at 12:44 am
Aatish,
There are plenty of articles on this site which explain clearly on managing transaction logs, you shud start wit Gail's article first, understand the pros n cons of doing what u intend to do and then proceed.
May 23, 2009 at 7:51 am
hi,
better use third party tools like litespeed, its in build shrinked and backed up instead of going in the traditional way.
Goodluck
KingManjunath
May 26, 2009 at 8:40 am
Sam,
You need to shrink the Transactional log file on requirement, mostly, when you are running out of space. However, it is adviceable that you should take a log backup before shrinking the log file. Also shrinking the log file to a higher percentage can impact your DB/application. You need to consider that as well.
The initial question was something else, my answer respond to one of the many situation which can occur.:-)
Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly
May 26, 2009 at 8:57 am
You don't want to shrink the log as a general rule. The only time you shrink the log is when it has grown uncontrollably for some reason. Either you forgot to set up log backups, or a one-time event, like a large data load, caused it to grown.
In other cases, i.e. normal use, this space is needed. Shrinking the log just means you'll grow it again. You want a stable log size.
Set log backups, monitor the peak size of them, and set the log to handle this.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply