Troubleshooting for Failed Maintenance Plan Reorganize Index Task

Problem

I have setup a scheduled maintenance plan for a server, to do basic maintenance tasks such as checking database integrity, reorganizing index and cleaning up history. It shows “failed” in job history.

job history

Solution

1. Check the error log

The error log doesn’ t provide enough information for trouble shooting:

“DBA Maintenance Plan.Subplan_1,Error,,ServerName,DBA Maintenance Plan.Subplan_1,,,The job failed. The Job was invoked by Schedule 17 (DBA Maintenance Plan.Subplan_1). The last step to run was step 1 (Subplan_1).,00:41:08,0,0,,,,0”

2. Revise Maintenance Plan to Enable “Send report to an email recipient”

Start SSMS, double click the maintenance plan, then click the “Reporting and Logging” icon, then check the button on the left of “Send report to an email recipient”, and add an Agent operator.

maintenance plan report1

maintenance plan report

Running the maintenance plan again will generate an email report. In my case, the report includes the following error message:

“Reorganize Index Task (servername)

Reorganize index on Local server connection

Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.

Databases: All user databases

Object: Tables and views

Compact large objects

Task start: 2017-09-03T10:32:03.

Task end: 2017-09-03T10:33:58.

Failed:(-1073548784) Executing the query “ALTER INDEX [Indexname” failed with the following error: “The index “Indexname” (partition 1) on table “tablename” cannot be reorganized because page level locking is disabled.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.”

3. Find the Table and Index across All Databases on the Server & Change Index Property

The above error message lists the name of the table and index name, but not the database name. We can use this query to determine which database they belong to:

sp_MSforeachdb ‘SELECT “?” AS DB, * FROM [?].sys.tables WHERE name like ”tablename”’

Then we can use SSMS to modify the index property, change “Allow page locks” from “False” to “True”. reorg failed

Run the maintenance plan again, and there is no error anymore.


 

Advertisements
Posted in SQL Server | Tagged , , , , , , , | Leave a comment

Send SMS Notification When You Receive an Important Office 365 Email

Problem

In order to monitor our databases servers, many email alerts have been setup in SQL Server to help us know what’s happening on the server. It is really helpful for the monitoring and trouble shooting for the database servers. However, it causes the flood of email to my index. Some email alerts need my immediate attention, for example a production is in critical condition, need me to fix it right away. I may miss it just because I cannot read so many email and tell this one is much more important than the others.

Solution

There are several methods to setup special notifications for important office 365 emails:

Method 1. iPhone VIP email alerts.

The iOS email client allow users to add some contacts (for example your boss) as VIP senders, therefore receive VIP email notifications when an email from VIP sender enters the inbox. My cell phone is an Android phone, so this method does not work for me.

Method 2. Office 365 Email Text Message feature

Office 365 email support text message, which can be easily setup:

email_text1

email_text2

Limitations: it only support Bell and Telus in Canada. Since I am with Rogers, neither does this method work for me.

email_text3

Method 3. Email to Text Feature (Cell Phone Carriers) and Office 365 Email Index Rules

Most cell phone carriers provide email to text feature, which can send you a text message once an email was sent to your phone’s email inbox. I am with Rogers, my phone’s email address is 10digitphonenumber@pcs.rogers.com. This is how I setup my SMS notifications:

  1. Add 10digitphonenumber@pcs.rogers.com as a contact in Office 365
  2. Create an inbox rule in Office 365 to forward important emails to 10digitphonenumber@pcs.rogers.com

You will get a SMS every time you have received an important email.

Screenshot_2017-08-28-10-55-55

 

Posted in Automation | Tagged , , , , , , , , | Leave a comment

Downgrade SQL Server 2012 from Standard Edition to Developer Edition

Problem

We have several dev/test servers with SQL Server 2012 Standard edition installed. Since Developer edition is free for development/test environment, so it make sense to downgrade to Developer edition. Unfortunately, Microsoft does not provided a direct method to do it. According to Microsoft online book: https://msdn.microsoft.com/en-us/library/ms143393(v=sql.110).aspx, it is easy to upgrade from cheaper edition to expensive edition, not the other way around.

Solution

We will have to uninstall SQL Server 2012 Standard edition, then install Developer edition. But before doing that, we can backup the system databases, then migrate them later into the new installation.

Steps

  1. Preparation: check the instance properties and system database properties, take a screenshot of the root directory and database default locations.database-setting1
  2. Run  SQL Server 2012 Configuration Manager, take a screenshot of accounts used by SQL Server services, then stop the services.
  3. Copy system databases (master, model, msdb) and ReportServer database to a new folder.
  4. Uninstall SQL Server 2012 Standard edition.
  5. Install SQL Server 2012 Developer edition, set the root directory and database default locations the same as the previous installation. Make sure the patch level is the same as before.
  6. Run SQL Server 2012 Configuration Manager, stop SQL Server services.
  7. Copy the system databases (master, model, msdb) and ReportServer database from the backup folder to the current installation folder.
  8. Restart SQL Server services.
Posted in SQL Server | Tagged , , , , , , | Leave a comment

Trouble Shooting for “No global profile is configured”

Problem

A weekly maintenance plan failed this weekend. I checked the history of the maintenance job, and found the following error message:

01/29/2017 21:13:00, MaintenancePlan.Sundays, Error,, SQL001, MaintenancePlan.Sundays,,, The job failed.  The Job was invoked by Schedule 39 (! DBA – MaintenancePlan.Sundays).  The last step to run was step 1 (Sundays).,00:00:04,0,0,,,,0

01/29/2017 21:13:00, MaintenancePlan.Sundays, Error,1, SQL001, MaintenancePlan.Sundays, Sundays,, Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility  Version 12.0.5000.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  9:13:00 PM  Progress: 2017-01-29 21:13:01.80     Source: {06474EBF-18F1-49F5-960B-B11B5686CA69}      Executing query “DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp…”.: 100% complete  End Progress  Error: 2017-01-29 21:13:02.40     Code: 0xC002F210     Source: Notify Operator Task Execute SQL Task     Description: Executing the query “EXECUTE msdb.dbo.sp_notify_operator @name=N’The DB…” failed with the following error: “No global profile is configured. Specify a profile name in the @profile_name parameter.”. Possible failure reasons: Problems with the query “ResultSet” property not set correctly parameters not set correctly or connection not established correctly.  End Error  Warning: 2017-01-29 21:13:02.40     Code: 0x80019002     Source: Sundays      Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.  End Warning  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  9:13:00 PM  Finished: 9:13:02 PM  Elapsed:  2.464 seconds.  The package execution failed.  The step failed.,00:00:04,0,0,,,,0

Solution

The reason is that the database mail profile we have configured, is not the global/default any more. Change the profile to “Default” will fix the problem.

Steps:

1. Right click Database Mail, select Configure Database Mail from the right-click menu.

configure-db-mail

2. Select Next on the Database Mail Configuration Wizard window. configure-db-mail21

3. Select Manage profile security in the window.

configure-db-mail22

4. Select Yes, then Next in the Default Profile field.

configure db mail41.jpg

5. Select Finish to complete the configuration in the next window

 

Posted in SQL Server | Tagged , , , , | Leave a comment

Trouble Shooting for Error 7311 and Error 7412

Problem

We have an ETL job which failed recently, an alert was sent to us:

SQL Server Job System: ‘XXX-XXXXX’ completed on \\SQL001

JOB RUN:            ‘XXX-XXXXX’ was run on 1/1/2017 at 0:00:00 PM

DURATION:         0 hours, 10 minutes, 1 seconds

STATUS:               Failed

MESSAGES:         The job failed.  The Job was invoked by Schedule XX (Morning).  The last step to run was step 1 (YYYYY_YYY).

Solution

Start SSMS, expand SQL Server Agent – Jobs, right click on the job ‘XXX-XXXXX’, select “View History” from the right-click menu.

The job runs daily, 60% succeeded, 40% failed. For the failed jobs, there are this error message in the log”

“Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10” for linked server “SQL002”. The provider supports the interface<c/> but returns a failure code when it is used. [SQLSTATE 42000] (Error 7311)  OLE DB provider “SQLNCLI10” for linked server “SQL002” returned message “Unspecified error”. [SQLSTATE 01000] (Error 7412)  OLE DB provider “SQLNCLI10” for linked server “SQL002” returned message “Query timeout expired”. [SQLSTATE 01000] (Error 7412).  The step failed.,00:10:00,16,7412,,,,0″

Since it sometimes works properly, sometimes not, so adjusting the parameter for remote query timeout Server Configuration Option of the linked server may fix the issue. The default value is 600 (seconds). I changed it to 3000 (seconds), resolved the issue. It can be done by two methods:

Method 1 Using SQL Server Management Studio

  • In Object Explorer, right-click the linked server and select Properties.
  • Click the Connections node.

Under Remote server connections, in the Remote query timeout box, type 3000.

 

Method 2 Using T-SQL

USE Database1;

GO;

EXEC sp_configure ‘remote query timeout’, 3000 ;

GO RECONFIGURE ;

GO

 

Posted in SQL Server | Tagged , , , , , | Leave a comment

Trouble Shooting for Error 8152 and Error 3621

Problem

We have an ETL job which failed recently, an alert was sent to us:

SQL Server Job System: ‘XXX-XXXXX’ completed on \\SQL001

JOB RUN:            ‘XXX-XXXXX’ was run on 1/1/2017 at 0:00:00 PM

DURATION:         0 hours, 3 minutes, 37 seconds

STATUS:               Failed

MESSAGES:         The job failed.  The Job was invoked by Schedule XX (Night).  The last step to run was step 1 (EXECUTE [DBXXXXX].[dbo].[SPXXX]).

Solution

Start SSMS, expand SQL Server Agent – Jobs, right click on the job ‘XXX-XXXXX’, select “View History” from the right-click menu.

This error message can be found in the log:

“START:[Table001] [SQLSTATE 01000] (Message 50000)  String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.,02:06:11,16,3621,,,,0”

This error is usually caused by the inserting data (VARCHAR or CHAR data type) is longer than the length of the column in the destination table. In our case, this statement in SP [DBXXXXX].[dbo].[SPXXX] caused the problem:

INSERT INTO [DBXXXXX].[dbo].[Table001]

(
[column1],[column2],[column3]…

)

(

SELECT 

[column1],[column2],[column3]…

from [SQL002].[DBYYYYY].[dbo].[Table001]

where identity_column > @V1

The length of columnA in destination table is 20, is 2 in source table. The solution is to change the length of columnA  in destination to 20. Then the job ran successfully.

Posted in SQL Server | Tagged , , , , | Leave a comment

Patching SQL Server 2014 Failover Instance

We have a SQL Server 2014 failover instance need to patched. The failover instance include 2 nodes — SQL001 (active) & SQL002(passive), the build before patching is RTM with no service pack and cumulative update installed. The patch is cumulative update 14 for RTM.

Install CU 14 on SQL Server 2014 Failover Instance

Step 1. Install CU 14 on passive node (SQL002)

We will install CU 14 on the passive node first.

  1. Login to the passive node SQL002, run “SQLServer2014-KB3158271-x64.exe” to install CU 14.install-cu14
  2. Accept  License Termsinstall-cu14_1
  3. Select the features to be installed.install-cu14_2
  4. Select “Next” when files in use check completed.install-cu14_3
  5. Select “Update” to start installation.install-cu14_4
  6. Select “Close” when installation completed.install-cu14_5

Step 2 Manually Failover

In order to patch SQL001, we will manually failover to SQL002, and make SQL001 passive node.

  1. From Server Manager, select “Tools” — “Failober Cluster Manager”.failover-cluster-manager2
  2. Select the cluster name in Failober Cluster Manager, then click “Roles” — “SQL Server (MSSQLSERVER)”, select “Move” — “Select Node…” in Action panel.failover-cluster-manager3
  3. Select SQL002 in the Move Clustered Role window.
    failover-cluster-manager4
  4. In Roles panel, the Owner Node will change to “SQL002”, the Status change to “Pending”.failover-cluster-manager5
  5. The failover is completed when the Status change to “Running”.failover-cluster-manager6

Step 3 Install CU 14 on SQL001

  1. Install CU 14 on SQL001 follow the procedure in Step 1.
  2. After installation completed, manually failover to SQL001. The installation completed.

Reference

https://blogs.msdn.microsoft.com/john_daskalakis/2014/09/05/how-to-install-a-service-pack-at-a-sql-server-2012-failover-instance-best-practices/

Posted in SQL Server | Tagged , , , , , , | Leave a comment