Problem:

Maintenance plan fails when it executes the database integrity check task with the following error: “Alter failed for Server ‘<servername>’.”  This issue is related to the “Allow Updates” advanced option for SQL server.  When this option is set to 1, an exeption is thrown any time the “REGONFIGURE” statement is executed.  During the execution of the maintenance plan, it executes the “RECONFIGURE” command which causes to raise an error.

Solution:

The “Allow Updates” option has to be set to 0.

The following two steps need to be executed on the server after verifying that the advance option is set to 1:

USE master;

GO

–Step 1

–SET option to false

EXEC sp_configure ‘ALLOW UPDATES’,‘0’;

GO

RECONFIGURE

GO

–Step 2

–Verify the option has been reconfigure to 0

EXEC sp_configure

GO

–Additional Info:

— This setting will display all configuration options

EXEC sp_configure ‘show advanced option’,‘1’;

GO

RECONFIGURE

GO

EXEC sp_configure

GO

— This setting will display a limited list of configuration options

EXEC sp_configure ‘show advanced option’,‘0’;

GO

RECONFIGURE

GO

References:

http://connect.microsoft.com/SQLServer/feedback/details/126163/maintenace-plan-integrity-checks-fail-with-alter-failed-for-server-xxx

http://sqlblog.com/blogs/eric_johnson/archive/2009/12/23/troubleshooting-a-failed-maintenance-plan.aspx

A common task for a DBA is to move tempdb files to a seperate drive.  During a SQL Server installation, the tempdb files (.mdf and .ldf) are stored in the same drive the sql binaries are installed along with master, msdb and model files.  As a best practice, tempdb files should always be moved to a seperate drive. The following provides the steps involved in moving tempdb files:

1) Get the logical and physical name for tempdb files.

SELECT d.name
, mf.name logical_name
, mf.physical_name
, mf.type_desc
FROM sys.master_files mf
INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE d.name = ‘tempdb’
ORDER BY mf.type, mf.name

2) Modify the “NAME” adn “FILENAME” with the new value for tempdb

USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdb, FILENAME = ‘T:\SQLDATA\tempdb.mdf’ )
GO

3) Validate changes took place.

SELECT d.name
, mf.name logical_name
, mf.physical_name
, mf.type_desc
FROM sys.master_files mf
INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE d.name = ‘tempdb’
ORDER BY mf.type, mf.name

4) Restart the sql server service

After SQL Server is restarted, the new files will be created in the new location. The old tempdb files will remain in previous location and they will need to be deleted manually.