question

sqlLearner 1 avatar image
sqlLearner 1 asked

SQL Server Maintenance Plans

I am setting up maintenance jobs such as CheckDB, update statistics etc.. I was wondering are there any disadvantages to using the CheckDB and Update Statistics job that are built in the SQL Maintenance plan wizard? OR should I create custom scripts for the job and add them to the SQL Server Agent? I am using SQL2008R2 and SQL2014 versions.
sql-server-2008-r2maintenance-plansjobssql2014
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
There's nothing wrong with using the Maintenance Plans. They don't offer much in the way of control, but they have lots of flexibility. It really depends on what kind of system you're trying to manage, how many systems, how big, etc. Those questions will determine if you need maximum power and control, or if simple automation is preferred.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
Just my opinion, I'd ditch the maintenance plans and go with Ola Hallegren's script solution. It's much more flexible in my opinion. Reference: https://ola.hallengren.com/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

VishalhSingh avatar image
VishalhSingh answered
since you are including CHECKDB consider the off-peak frequency for this maintenance plan as CHECKDB is an expensive operation. Also, check if you have any rebuild indexes (you should have one) then you dont need update statistics as rebuild automatically updates them. Depending on the version of the SQL Server and edition rebuild index can be done online (no interference to user's connection).
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.