January 31, 2015 at 4:13 am
Hi All,
I would like to know is it a good practice to have a SQL Server Agent Job for each database with below Steps
1. DBCC Checkdb
2. SP_Updatestats
3. Backup_Database_full
4. DBCC Shrink Database
5. Copy Backup to Other Server
Please advise if the sequence above creates any problem in longterm to DB.
This is a weekly job which occurs during non peak hours.
January 31, 2015 at 7:36 am
rkrpat (1/31/2015)
Hi All,I would like to know is it a good practice to have a SQL Server Agent Job for each database with below Steps
1. DBCC Checkdb
2. SP_Updatestats
3. Backup_Database_full
4. DBCC Shrink Database
5. Copy Backup to Other Server
Please advise if the sequence above creates any problem in longterm to DB.
This is a weekly job which occurs during non peak hours.
A) NEVER, EVER, EVER shrink a database (unless something EXCEPTIONAL happens, and even then you best do some other things or you are totally hosed by the fragmentation it creates)!!!!!
B) Do NOT use maintenance plans for SQL Server maintenance. Go to Ola.Hallengren.com and use his free, awesome, documented maintenance suite.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 31, 2015 at 8:00 pm
rkrpat (1/31/2015)
Hi All,I would like to know is it a good practice to have a SQL Server Agent Job for each database with below Steps
1. DBCC Checkdb
2. SP_Updatestats
3. Backup_Database_full
4. DBCC Shrink Database
5. Copy Backup to Other Server
Please advise if the sequence above creates any problem in longterm to DB.
This is a weekly job which occurs during non peak hours.
Kevin's advice should be followed with all haste. Here are some reasons why.
1. The maintenance plans have no intelligence built into them. Take index maintenance as an example, which would be considered part of stats updates, in my opinion. If you use maintenance plans for that then your SQL Server will waste time rebuilding or re-organizing every index regardless of fragmentation level or number of pages. As a result, it will do a whole lot of work on indexes that don't need anything done to them.
2. You actually don't have index maintenance in your list of tasks. Unless you have huge amounts of memory so that you can fit all your databases into memory, you should be doing index maintenance.
3. As Kevin said, shrinking a database is bad for it. Don't do it.
4. Why use maintenance plans to create a process when a fantastic process has already been built for you in Ola Hallengren's solution?
Michelle Ufford has an index maintenance script if you don't prefer Ola Hallengren's. I have not used Michelle's script. I just know of it. Search her name and you will find it.
February 1, 2015 at 3:30 am
I like Ola's scripts and Michele's. I recommended them both in my book.
There is another option to look into. Minion Reindex by Sean & Jen McCown. I wrote a review of it here[/url].
"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
February 2, 2015 at 11:43 am
I use maint plans for Update stats weekly and integrity checks daily.
Also a couple of other things to look into.... MSDB cleanup process (it is a task in Maint plans). Every backup and every job with a Maint. Plan and every restore entry has an entry into tables within the MSDB database. Unless you delete them they will stay there forever. If you have a lot of databases that have been in existance for years that history piles up.
Also, if you run Maint plans and create an output file those don't automatically get deleted so you need to clean those up. Again, there is a routine within Maint plans to clean those up.
Also, if you have DB mail configured and use it every job that sends an email puts a record into a MSDB table. Those will need cleaned up via this SQL (change your days retention to whatever you like. I only keep 30 days:
DECLARE @DeleteBeforeDate DateTime = (Select DATEADD(d,-30, GETDATE()))
EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate
EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply