October 14, 2007 at 6:01 pm
Hi,
I have maintenance plan to check integrity,re-organize index,re-build index,updata statistic which run every Sun @ 2am.
however I notice that on monday my nav.log is growing really big up to 90Gb....
backup DB is run daily @9pm and backup transaction log is run daily every 15 minutes.
How do I solve this problem?
Do you think because my schedule time is in correct.
right the moment what I do is to shrink the log using
backup log vgsmdb with no_log
use [vgsmdb] DBCC SHRINKFILE (N'VGSMDB_Log', 4770)
I don't use 'Truncate only' because my understanding that using Truncate is not shrinking the actual file in drive, am I correct?
please help me
Thanks,
Susan
October 14, 2007 at 8:41 pm
Being dealt with here - http://www.sqlservercentral.com/Forums/Topic408782-357-3.aspx
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 15, 2007 at 2:53 am
Hi Susan,
switching to bulk just before dbreindex, then switching back will keep the transaction log file much smaller. It will increase the transaction log backup file size though. Probably it is a worth to take a full backup after switching back to full recovery mode.
Have a look at http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx, particularly the last few paragraphs (Logging Considerations).
Regards,
Andras
October 15, 2007 at 4:14 pm
Thanks Andras.
I will do that.
one more questions.
when we swicth it, do we need to turn of the transaction log backup too?
currently the transaction log back up I change it to every 5 minutes in maintenance plan.
last time I try to db re-index during business hour and got complain from users cos the apps was slow so I cancelled the job.
I thought I can do it during day time, obviously I can't.
susan
October 15, 2007 at 4:30 pm
Be careful - make sure you understand the ramifications of switching to bulk-logged recovery mode. For the duration of the bulk-logged operation in the log backup, you cannot do a point-in-time restore. If you have a very long-running index rebuild operation and something catastrophic happens before the index rebuild operation ends then you can't do a point in time restore to any time past the start of the index rebuild.
Just something to be aware of for disaster recovery planning.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 15, 2007 at 4:35 pm
Thanks for the warning, Paul.:)
I may do it during weekend or when we are going to upgrade our apps
October 17, 2007 at 7:50 am
Here's a query that I use to shrink the log file and the database:
--Shrinks a database Manually
sp_dboption 'temp', 'trunc. log on chkpt.', 'TRUE'--Set a checkpoint
use tempdb checkpoint--Declare the checkpoint
go
--Shrink the log file and the Database
DBCC SHRINKFILE ( 'TEMPLOG' , TRUNCATEONLY )
GO
DBCC SHRINKDATABASE (tempdb, 10) --The 10 is 10%
GO
sp_helpdb --view db stats...
October 17, 2007 at 8:00 am
Please - don't shrink tempdb - it's likely to just grow again and you'll end up with file system fragmentation.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 17, 2007 at 8:08 am
in addition setting the 'trunc. log on chkpt.' to true is almost equivalent to change recovery mode to Simple wich also can have catastrofic implications at recovery time.
-Noel
* Noel
October 17, 2007 at 8:14 am
True, but that's why you backup the log file every so often during the day...
Jim
October 17, 2007 at 8:19 am
Paul,
You're right there's no need to shrink the tempdb. I was just using that as an example. Obviously you need to change the database and log file name to the ones that you actually want to shrink.
Jim
October 17, 2007 at 1:50 pm
James A. Schimmels (10/17/2007)
True, but that's why you backup the log file every so often during the day...Jim
You can not backup the log file on a database with SIMPLE recovery mode. You can truncate the log but that will kill the restore chain.
* Noel
October 17, 2007 at 4:27 pm
At the moment I will monitor it again Next mon.
I have changed my transaction log backup from 15 min to 5 min
Thanks guys
October 18, 2007 at 7:59 am
I wonder if your really need to re-index every week? Are your indexes so fragmented after a week (I'm not saying that can't happen, but wow that must be lots of writes and deletes), that a index defrag won't work?
October 18, 2007 at 4:20 pm
we setup in maintenance plan that every Sun - it will reindex/re-orgnize etc...
and also I notice this happens since we move from sql 2000 to sql 2005.
has anyone using navision?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply