June 27, 2011 at 3:42 am
Hi I have a Navision production database on SQL 2005 (Nav 4.0 SP3).
I release the following maintenance plan on this database :
- Back up the database
- Shrink the database
- Check the integrity
- Rebuild index task
- Reorganize index task
- Update stats
- History clean up
But I have another maintenance plan backing up the transaction log and the first backup of the transaction log after the previous maintenance plan is very huge.
Should I switch some maintenance tasks ?
Many thx
JV
June 27, 2011 at 4:54 am
Many of the maintenance tasks you're doing can be logged operations. That would explain the increased log backup size.
Why, oh why are you shrinking the database? That is, in the vast majority of circumstances, a very bad thing to do. It leads to major database and disk fragmentation, among other issues.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 27, 2011 at 5:19 am
June 27, 2011 at 5:31 am
Jayanth_Kurup (6/27/2011)
is it possible to try backup log with truncate with no log followed by the database backup, important to backup the data soon after to prevent data loss.
If you don't care about PIT recovery switch to simple.
If you do care about PIT recovery take log backups (in full recovery).
June 27, 2011 at 5:55 am
Thx for all the replies.
Ok, I've skipped the shrink part. It was still there due to lack of disk space.
I can't set to simple because of Navision
Kind regards
JV
June 27, 2011 at 5:58 am
jvElecenter (6/27/2011)
Thx for all the replies.Ok, I've skipped the shrink part. It was still there due to lack of disk space.
I can't set to simple because of Navision
Kind regards
Where the heck does it say that you can't use simple mode? I have that exact same version and I've run it in simple. It's bad practive but it's not impossible.
June 27, 2011 at 6:01 am
but it's bad practice u say ?
Why doing it ?
JV
June 27, 2011 at 6:09 am
Navision is an ERP. So if you use simple mode and do full backups only 1 time per week then in the case of a disaster you lose up to 40 hours of work PER employee.
If you do full recovery + log backups then the most you can lose is the interval between the log backups.
Just make sure you separate the backups from the datafile on separate physical drives in case of a HD failure.
June 27, 2011 at 6:21 am
thought so
doing that
In your experience does NAvision works much quicker using simple ?
Kind regards
JV
June 27, 2011 at 6:29 am
jvElecenter (6/27/2011)
thought sodoing that
In your experience does NAvision works much quicker using simple ?
Kind regards
Not at all. The only difference with simple and full is a little more data gets written to the log files. Here we have less than 1 GB per day in transactions on a san that writes over 1 GB / minute. So even if there was a big difference I wouldn't sacrifice PIT recovery to save a few ms on each clicks the users do.
June 27, 2011 at 6:29 am
... and yes Navision is slow as hell even on a good day. It's just the way it's been coded and there's nothing you can do about that.
June 27, 2011 at 6:34 am
Ninja's_RGR'us (6/27/2011)
Jayanth_Kurup (6/27/2011)
is it possible to try backup log with truncate with no log followed by the database backup, important to backup the data soon after to prevent data loss.If you don't care about PIT recovery switch to simple.
If you do care about PIT recovery take log backups (in full recovery).
Yep correct,even we Can't do the log backup in simple mode.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 27, 2011 at 6:40 am
Ninja's_RGR'us (6/27/2011)
... and yes Navision is slow as hell even on a good day. It's just the way it's been coded and there's nothing you can do about that.
I'm pre-unretracting my next comment.
Nav is slow as a pig.
Sorry for all the pigs I've just offended.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply