September 15, 2008 at 2:13 am
I am currently designing a weekend maintenance plan which we can run during our weekend maintenance window from 8pm on saturday night. I have written a nightly maintenance plan and create a maintenance database. Each task that is perform during the nightly task (Log backup, log truncation, Log initialize, Database backup) is all logged with timing to this database and an report and email sent to me each morning.
For my 2000\2005 environment, what order should I do my weekend maintenance in? I'm thinking...
1. DBCC CHECKDATABASE
2. Reindex (based on Fragmentation depending on Reorganise or Rebuild)
3. Update Stats
4. Complete Nightly Maintenance (as ABove..Log backup, log truncation, Log initialize, Database backup)
I will write these tasks using a cursor and pass each database through to give a dynamic solution.
I'm quite new to this so all advise and opinions appreciated!!
Thanks
Jim
September 15, 2008 at 3:03 am
James,
reindexing procedure does update statistics so you have to omit step 3 in your schedule.
Regards,
PSA
September 15, 2008 at 3:03 am
Hi,
I prefer a DBCC CheckDB in the weekend, I do a rebuild/reorg and update stats on a daily base, but that's up to you.
Shrinking logfiles is not recommended unless you're doing an unusual, transaction-heavy operation.
If you have databases in full recovery, that's probably because you want to do a point-in-time restore. If that's the case, you need to do a logbackup more often (probably every hour). This depends on your company's strategy (the big question: how much data can you afford to loose after a crash). If you don't want a PIT restore, put your database in SIMPLE mode.
About your backup frequency: The same question comes up. You need to backup your databases depending in your company's strategy.
Wilfred
The best things in life are the simple things
September 15, 2008 at 3:10 am
Update stats isn't needed (and can be counter-productive) if a full index rebuild is done. Update the stats only of tables that have been reorganised.
What exactly do you mean by "Log backup, log truncation, Log initialize"?
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
September 15, 2008 at 3:13 am
Many thanks for the feedback.
My databases are in FULL recovery mode and I have a Log shipping job running from 8.00am to 19.30 during the day. Then at 8.00pm I have the nightly maintenance script which backups the databases, initiliases the logs (as these will grow indefinetley otherwise), truncates log and the copies the backup to my DR server. The log backups occur every 30 mins and these are also copied over to the dr server as well.
These are small databases so weekend maintenance will be sufficent for these. Im more interested in what order you guys do yours, if you do them?? I have never done this before.
Thanks.
September 15, 2008 at 3:32 am
james.easton (9/15/2008)
My databases are in FULL recovery mode and I have a Log shipping job running from 8.00am to 19.30 during the day. Then at 8.00pm I have the nightly maintenance script which backups the databases, initiliases the logs (as these will grow indefinetley otherwise), truncates log and the copies the backup to my DR server.
The logs won't grow indefinitely, as you have regular log backups running as part of the log shipping. If you';re running log shipping, you should not be truncating the logs (backup log with truncate only) as that breaks the log chain and will prevent any log backups past that point from been restored (log entries will be missing)
What exactly do you mean by "initiliases the logs"
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
September 15, 2008 at 3:54 am
I have taken this over from another DBA and so this is a learning curve for me, sorry if I'm not explaining this stuff correctly. As I understand it...
AT 8pm, the database and log is backed up. The log is then initiliased which means the log LSN is reset to 0.
The following day at 8am, the log backups begin every 30 mins, each time increasing the LSN by 1. This means that when I try to restore the log, i can see each log backup which is appended by LSN 1, 2, 3 etc and retore to a point in time, or last log backup.
If i didn't intailise the log, the 8pm backup would complete but the next day the log backup would have an LSN of 35 (for example) and if you tried to recover using this it would error that it needs the previous 35 log backups.
This might sounds a bit bacwards, but the Log Shipping solution is made up of scripts that the previous DBA wrote, rather than using SQL Log Shipping solution. His reason for this when I questioned it was that log shiopping was switched on on the network. Which makes little sense to me? ?
September 15, 2008 at 4:11 am
I still don't understand what you mean by initialising the log. It's not a general SQL term for anything associated with the log. LSNs (Log sequence numbers) are used internally in the tran log and increase for every entry. You can't reset an LSN to 0 without dropping and recreating the entire database. Can you maybe post the code involved so that we can get some clarity on terminology?
Log backups are based off full or diff backups, so if you backup the DB at 8pm, take the next log backup at 8am, then want to restore the DB somewhere to 8am, you need the full backup and the log backups from 8am. You don't need all the previous day's.
With log shipping (as it's supposed to be) the logs backups are simply copied across to the standby and restored there, one by one as they occur on the primary. There's no need to copy full backups over unless for some reason you need to recreate the entire log shipping setup (which should be a rarity)
This might sounds a bit bacwards, but the Log Shipping solution is made up of scripts that the previous DBA wrote, rather than using SQL Log Shipping solution. His reason for this when I questioned it was that log shiopping was switched on on the network. Which makes little sense to me? ?
Sounds questionable.
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
September 15, 2008 at 4:22 am
Within the script, this is the initialise log section...
/* Initialise Native Log Backup */
IF @RECOV_MODE <> 'SIMPLE'
EXEC('BACKUP LOG ' + @DB + ' TO DISK = ''' + @LOGPATH + ''' WITH INIT')
Print 'Log File Initialised For ' + @DB
select 'Time Now: ' + convert(varchar(20), getdate())
END
END
Thanks for your time with this, much appreciated.
September 15, 2008 at 4:39 am
Ah. That's reinitialising the backup file, removing (overwriting) any backups that were present in the file before. It's not touching the database's log itself.
Personally I'm not fond of writing multiple log backups to the same backup file. I like to be able to see all my log backup files.
Plus, by overwriting the previous day's log backups it means if there's anything wrong with the latest full backup such that it can't be restored, you have no way to restore to anything later than the previous full backup (unless the log backups are getting archived somewhere)
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