October 11, 2007 at 12:24 pm
Hi,
I have a large sharepoint 2007 db (120+GB) and from time to time, the Transaction log does not truncate after a backup and a DBCC Shrinkfile needs to be run. Does anyone know if setting up autoshrink is supported with MOSS??
Thanks.
October 12, 2007 at 6:48 am
I'd suggest you don't enable autoshrink, it'll promote ntfs fragmentation. I usually try to find the size for the transaction log so it doesn't grow - the fact that the internal use may go up and down is immaterial. Think of t log files like the page file, where it's best to set it to a fixed size so it doesn't grow.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 12, 2007 at 8:58 am
Agree, avoid auto-shrink. Instead use a script - i.e.
BACKUP LOG AdventureWorks WITH TRUNCATE_ONLY
go
DBCC SHRINKFILE ('AdventureWorks_Log',TRUNCATEONLY)
go
Tommy
Follow @sqlscribeOctober 12, 2007 at 10:21 am
this is the current backup script used (its using a netapp snap manager backup)
The more I look at it, I don't think it has anything to do with truncating (my boss had told me this task yesterday and said it did) and I just took his word for it.
So I should just be able to add a new job as recommended above and it will truncate on whatever schedule I'd like to, right?
October 12, 2007 at 10:36 am
Correct however something to keep in mind. The example above will use a SHRINKFILE to take the log down to 1MB or so which on an OLTP system wouldn't be a good idea (it will just auto-grow :))
Better example would be to keep the transaction log at 20% of the total data size.
Tommy
Follow @sqlscribeViewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply