DBREINDEXING Fails for one particular DB with error : “SET options have incorrect settings: 'QUOTED_IDENTIFIER'.”

Issue:

DBREINDEXING job which performs both ONLINE and OFFLINE re-indexing operation running fine on other servers but failing in one server with below error:

“ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed.”

Reason/Resolution:

As per microsoft if we have index on a computed column and when trying to perform re-indexing we need to SET 'QUOTED_IDENTIFIER' to ON.

So I just added the statement ‘SET QUOTED_IDENTIFIER ON ‘ before re-indexing code execution. This resolved my issue and the job went fine without issue.

 
Here the interesting thing I noticed is when I am executing code through SSMS it executes successfully but when I’m trying to run the same code using a SQL Scheduled job the step fails. Once after adding the command my job is running fine. This is because by default SQL Agent does not set 'QUOTED_IDENTIFIER' or ‘ARITHABORT’.

Why my mdf file still grows even I have ndf file?

A production DB has eat up all space of disk D. So we added new disk E and created a secondary (ndf) file for the DB. E currently has 100GB and D has 20GB free space left in them. After few days when verified free space of D drive has dropped down below 15GB. So was wondering why DB still growing in D as it has ndf file in E drive. When verified noticed mdf file of D drive was set to ‘Enable AutoGrowth’ and to ‘Unrestricted Growth’. So this option causing the DB to grow in D drive till entire disk space will be consumed and when it won’t find any more free space to grow then it will start using ndf file.

In order to avoid this we have to change the option of file growth from ‘Unrestricted Growth’ to ‘Restricted Growth’ by giving a value which is higher than the current mdf file size. Suppose current mdf file size is 10GB and you can set it to a restricted growth of 12GB(make sure we have ndf file which is set to Auto grow) and once mdf gets filled up(that is till 12GB) data will start storing in ndf file.

If its not set to restricted growth also this is not an issue as SQL Server will automatically decide to grow in ndf file once mdf gets filled up but till the time the entire space of disk will be eaten by mdf file if it’s set to grow unrestrictedly.

How to start SQL job of another server

I got a task to trigger a job of Server B when job of Server A completes. One way of doing is we can monitor how much time job is taking in Server A and accordingly we can schedule job of Server B. But here there might situations where the jobs take longer than expected time, if Server A job is taking only 5mins to complete and it is scheduled to start at 9AM and we have scheduled Server B job to run at 9:30AM and due to some reason Server A job takes more than 30-45mins and still didn’t completed even then the job of Server B starts by 9:30AM which was not supposed to happen.

So I thought to trigger the job of ServerB remotely. So plan is to trigger the Server B job once Server A job completes. To accomplish this we can use several ways and I have used command ‘sp_start_job’.

Sp_start_job serverB.msdb.dbo.sp_start_job @job_name = ‘Remotejob’

The above command when I ran on ServerA it has thrown below error:

Msg 7202, Level 11, State 2, Line 1
Could not find server 'ServerB' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Which clearly shows I have missed a basic thing of linking servers. We can link servers using sp_addlinkedserver. I have linked servers using below command and after I was able to see both servers names in sys.servers  table.

sp_addlinkedserver 'ServerB'

Now when I ran the above sp_start_job command again I received below error

Msg 7411, Level 16, State 1, Line 1
Server 'ServerB' is not configured for RPC.

What is this RPC? Its Remote Procedure Calls. When I checked the properties of Linked Servers which is available under Server Objects -> Linked Servers -> Providers -> ServerB (Right Click properties) -> Server Options

Here there are 2 options RPC and RPC Out, I have changed the ‘RPC Out’ option to TRUE which was FALSE. After setting the option to TRUE on both servers I ran the query again and this time it went fine.

As per main task to trigger server B job automatically I have added the command  “Sp_start_job serverB.msdb.dbo.sp_start_job @job_name = ‘Remotejob’”

as final step of the job in Server A, so automatically the last step of Server A job triggering the job of Server B.