March 18, 2015 at 7:02 am
We have a small team of developers (4) for a customer for whom we have created several custom apps. I was nominated to upgrade our server from 2008 to 2014 --- I was in the wrong place at the wrong time!! Lol. Anyhow, I am by no means a SQL server admin, so I did what I thought best and checked the Interwebs for best practices .... Thankfully, the migration did go well (that was in August). BUT, I wanted to set up maintenance and transaction log backups so did some more looking. I think I was led astray by a blog post on the maintenance plans ... the first step in my nightly maintenance plan is "Shrink database, limit 50 MB, free space 10%" ... The plans have been running well for months now, but I read another blog post this morning that says you should never, ever, ever - ever - ever, shrink your db due to to fragmentation. I would really appreciate advice on the jobs I have running currently.
Below are the jobs that I have scheduled on our server:
The first is a transaction log backup on our main database every 15 minutes. I have this running from 4 AM until 11:45 PM. (No one is really on the server in the middle of the night, and I wasn't sure if trans log backups should be occurring when db backups try to run). I have the other transaction logs backing up about every hour for databases that are not heavily used, but also only from 4 am until 11:45 PM.
--- The server is backed up at midnight by another group ---
At 1 AM, I have a system db backup job running that does the following to master, model and msdb:
Shrink (50 MB, 10%) -> Reorg Index (tables and views, compact large objects) -> Check db integrity (include indexes) -> Back up db full (append existing, to disk, compression is default).
At 2 AM, I have our application database job running. It does the same steps as the system db backups above (I've attached a screen shot of this one).
At 3 AM, I have a maintenance cleanup task that does a cleanup of transaction log files older than 4 days.
At 3:10 AM, I have a maintenance cleanup task that does a cleanup of database backup files older than 1 week.
This may have you rolling on the floor in laughter. I always love a good laugh, so if that's the case, I'm glad I made you smile. But, if you could provide some feedback for me in exchange for the good laugh, I will be most appreciative 😉
Jennifer
March 18, 2015 at 7:19 am
jennifer.elkhouri (3/18/2015)
the first step in my nightly maintenance plan is "Shrink database, limit 50 MB, free space 10%" ...
Ow, sob, wince, sob, cringe :hehe:
but I read another blog post this morning that says you should never, ever, ever - ever - ever, shrink your db due to to fragmentation.
Well, 'never' is a little too strong, but there shouldn't be a shrink job in the scheduled maintenance. Just take it out.
Backups, you want full backups daily unless there are reasons you can't.
Log backups, these are based on your allowable data loss. If the system can't tolerate more than 30 minutes data loss, then log backups every 30 minutes at least. These should run 24x7 and can run during other backups, no concern
Index maintenance, get Ola's maintenance script and schedule daily or weekly, default settings are good (http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html)
Database consistency checks, at least once a week, daily if you have the time.
Plus the maintenance cleanup tasks which are just housekeeping.
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 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply