MaintenancePlan - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

MaintenancePlan

Author

Topic

netadmin
Starting Member

2 Posts

Posted - 2011-05-16 : 22:47:47

I am new to SQL 2005 Administration.

I have a maintenance plan in place to perform the following in sequence:

1. Shrink Database
2. Check Database Integrity
3. Rebuild Index
4. Update Statistics

History shows each task runs successfully.

Excerpt from Application Event Log seems to indicate the user options were changed and need to reconfigure.

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 15457
Date: 5/15/2011
Time: 12:00:23 AM
User: user
Computer: computer
Description:
Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.

Question(s):

1. Not sure that anything was changed; what do I do?
2. Noticed that several of the logs for each database increases to 90% Use after running the maintenance plan as observed using the following query statement on one of the databases; not sure why this is?
3. Do I need to select each database context and run the RECONFIGURE command?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-16 : 23:38:00

(1) Remove the Shrink Database task. DO NOT shrink your database files. There have been several threads recently on this topic. Shrinking data files causes fragmentation which affects performance severely.
(2) No need to run Update Statistics if you are rebuilding the indexes. Stats are updated as part of rebuilding indexes anyway.
(3) As for the error, I think the Check DB integrtity task is issuing an sp_configure statement.. setup a trace and see what statement is being issued..theres' probably a mismatch of your server settings with what the DBCC needs so its issuing a sp_configure..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-05-17 : 01:28:35

SQL Server issues a message to RECONFIGURE when a sp_configure is used.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-05-17 : 16:57:39

The message in the event log is an informational message only. That is normal - no need to do anything about it.

The real issue you have is the shrink database option. As pointed out previously, all that does is cause fragmentation and slows down your system performance as the data/log files have to grow again. Remove that step - it is causing more problems than it is solving.

As for updating statistics, if your plan is rebuilding all indexes - then in your update statistics task make sure you select to only update column statistics. Rebuilding an index will update all index statistics - but does not update any column statistics on the table.

The reason your log file is growing is a) because you are shrinking it, and b) because the index rebuilds are logged operations. The used space in the log file will be marked as reusable after a transaction log backup is performed when the database is in full or bulk_logged recovery model, or after a checkpoint is performed when the database is in simple recovery model.

Jeff

netadmin
Starting Member

2 Posts

Posted - 2011-05-18 : 20:59:31

Thank you all for all the information and help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources