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.

Transaction log backup job fails with error : The media family on device 'D:\SQL\Backups\TestDB' is incorrectly formed. SQL Server cannot process this media family.

ISSUE:

One of our maintenance job for taking transaction log backup was failing and when checked the history of Maintenance Plan it has below error:

"The backup data at the end of "D:\SQL\Backups\TestDB" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Workaround:

So verified weather file exists in location or not and yes file exists. Then ran

RESTORE VERIFYONLY FROM DISK= 'D:\SQL\Backups\TestDB';

Received below error:

Msg 3241, Level 16, State 41, Line 1

The media family on device 'D:\SQL\Backups\TestDB' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

Then verified the header of backup file using below command:

RESTORE HEADERONLY FROM DISK='D:\SQL\Backups\TestDB';

Output clearly shows that earlier backup is incomplete

RESOLUTION:

To resolve this issue first I have moved the existing backup file to a temp location and took fresh backup using maintenance. The job went successfully and once backup completed successfully deleted the corrupted backup file from temp location. We can delete the corrupted backup file as it is unusable anyway and need to take fresh backup.

Removing corrupted Analysis Services installation

Received a task to upgrade service pack of SQL Server 2008. Looks so simple rite yeah I felt so…. See how it went

Copied SP1 file to server and ran the file it took 5mins after running the exe and thrown below error.

Then after reading error one thing understood something wrong with Analysis Service. Noticed one more thing there are 3 other instances on same server okie so guessed issue with other instances as well as they also have Analysis service running. So searched and found only solution most of articles suggested is to REPAIR the SQL server (I never did that before). So scheduled a downtime and started to run repair of sql server with installation CD. It went fine till end and completed fully giving error for Analysis Service again (LOL), stating only the services/features that are fully installed can only be repaired.

Now the only option left is to re-install the services. I was not knowing how to un-install only particular feature of SQL Server. Searched and this time didn’t found any articles J on how to do that so went back to BOL and found how to do that. So as mentioned in BOL un-installed only Analysis Services from ‘Add/Remove Programs’. To remove certain feature go to Control Panel, select Add/Remove Programs and after clicking on SQL Server 2008 service it gives option to Uninstall\Change, after selecting that option it opens a windows where we can select the features we want to un-install:

After removing the service I have re-installed Analysis service from installation CD to the same instance. Now again ran SP1 but still it failed with same error as mentioned in beginning. So got no option to do hence verified with users weather they actually need AS service? Luckily none of them were using. So removed As completely from the instance and ran SP1 and it went fine now (thank god).

So as per my observation the options are either to run REPAIR if it fails then try re-installing the respective service and if that also fails we should re-install SQL SERVER. I would be happy to hear if any other ways of fixing this issue.

Then started upgrade of other instances where I haven’t touched As service still. It went for some time…..

and went till end successfully J

So finally upgraded all instances with SP1.