May 5, 2009 at 3:51 pm
As the logical data file increases we are getting constant tickets from MOM.
Each and everytime we need to manually shrink the database to reduce the size of the .ldf file.
Is there any permanent solution to this?
May 5, 2009 at 4:35 pm
That is dependent on recovery model of your database and you could truncate it after a full backup. I think you need to read up on Backup and Recovery of SQL Server in the BOL.
Kind regards,
Gift Peddie
May 5, 2009 at 4:48 pm
you need to stop shrinking it.
Obviously you need more log space, so make the log bigger (or leave it big) and the tickets will stop.
May 5, 2009 at 7:39 pm
Check to see if there are many bulk operations such as creating indexes, large deletes, inserts into the db. You could always change the recovery model to simple or bulked logged depending on the business needs i.e. log shipping, point in time recovery.
May 11, 2009 at 5:00 pm
Yes we need to keep the recovery model as 'Full' as a business requirement.
Any other workaround?
May 11, 2009 at 8:18 pm
You need the log space. The only thing that will let you use less peak log space is more frequent log backups.
May 12, 2009 at 1:24 am
Steve Jones - Editor (5/5/2009)
you need to stop shrinking it.
Can you please explain why..? (bcse it needs to expand it everytime.?). I do shrink log regulerly. Please suggest me a proper way of maitaining LOG.
Thanks.
May 12, 2009 at 1:32 am
Please read through this - Managing Transaction Logs[/url]
If you are backing it up and it's still growing too much, back it up more often.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2009 at 5:34 am
Hi, you could put your ldf file in other hard drive with more space.
Or make a job like this:
May 12, 2009 at 6:26 am
manolomalaga (5/12/2009)
Or make a job like this:
That's a very bad piece of advice (for anyone interested, the OP in the referenced thread wanted to delete the LDF daily)
Proper maintenance of a log file involved frequent backups, not shrinking, not deleting. If you don't want to keep the log info (ie don't need point in time recovery), switch to simple recovery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply