SQL Errors | SQL with Manoj

SQL Error – Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option

July 20, 2016 Leave a comment

Today while running an ETL I encountered an error I’d never seen before. The Informatica ETL triggers a Stored Procedure with 2 parameters which takes table names and creates a dynamic MERGE statement. The SP was failing and the Informatica Monitor was showing following error:

Severity: ERROR
Thread: MAPPING
Message Code: CMN_1022
Message: Database driver error…
CMN_1022 [EXEC spMergeTables ‘STG.ABCtblXYZ’,’ABC.tblXYZ’
Microsoft SQL Server Native Client 11.0: Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.
SQL State: 42000 Native Error: 2754
State: 1 Severity: 16
SQL Server Message: Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.

Database driver error…
Function Name : ExecuteDirect
]

Thus, somewhere within this SP the error is being generated with severity greater than 18.

So, to identify the cause I tried to execute the above MERGE statement directly and I got the same error:

EXEC spMergeTables 'STG.ABCtblXYZ','ABC.tblXYZ'

(0 row(s) affected)
Msg 2754, Level 16, State 1, Procedure spMergeTables, Line 107
Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.

In the SP after creating the dynamic string for the MERGE statement the SP executes the string variable, so I replaced the EXEC(@str), with PRINT(@str), to PRINT the MERGE statement rather than executing it, which came as follows:

MERGE ABC.tblXYZ AS tgt
USING STG.ABCtblXYZ AS src
ON tgt.[PK_ID] = src.[PK_ID] 
WHEN MATCHED THEN  
	UPDATE SET <tgt.column = src.column>
WHEN NOT MATCHED BY TARGET THEN 
	INSERT (<Columns list>) 
	VALUES (<column values>);

On executing the above MERGE statement, I got the actual error which is as follows:

Msg 823, Level 24, State 12, Line 1
The operating system returned error 21(The device is not ready.) to SQL Server during a write at offset 0x000000270e0000 in file ‘F:\TempDB\tempdb2.ndf’.
Additional messages in the SQL Server error log and system event log may provide more detail.
This is a severe system-level error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB).
This error can be caused by many factors; for more information, see SQL Server Books Online.

So, its clear now that there was something wrong with the tempdb file and the OS is returning error with severity as 21, which is greater than 18.

As this was a tempdb file issue, thus I restarted the SQL Server services and reconnected the SSMS client, and re-ran the Job, and this time the ETL got executed successfully 🙂

Categories: SQL Errors Tags: SQL error 21, write at offset

SQL Error – Windows could not start the SQL Server (MSSQLSERVER) on Local Computer (moved Master DB)

June 29, 2016 2 comments

I was trying to move my Master DBs to another folder by using steps mentioned in my one of [previous blog post]. But after all steps, when I tried to Start the services I got error with following popup:

Master DB move 01

Error Description:

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 3417.

–> So, I checked the error log file and it showed the reason for error.
Default location: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\

…
2016-06-27 12:18:28.02 Server The service account is ‘NT Service\MSSQLSERVER’. This is an informational message; no user action is required.
2016-06-27 12:13:33.30 Server Registry startup parameters:
     -d E:\SystemDatabases\Master\master.mdf
     -e C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG
     -l E:\SystemDatabases\Master\mastlog.ldf
2016-06-27 12:13:33.30 Server Command Line Startup Parameters:
     -s “MSSQLSERVER”
…
2016-06-27 12:13:33.41 spid5s [INFO] HkHostDbCtxt::Initialize(): Database ID: [1] ‘master’. XTP Engine version is 0.0.
2016-06-27 12:18:28.12 spid5s Starting up database ‘master’.
2016-06-27 12:18:28.13 spid5s Error: 17204, Severity: 16, State: 1.
2016-06-27 12:18:28.13 spid5s FCB::Open failed: Could not open file E:\SystemDatabases\Master\master.mdf for file number 1. OS error: 5(Access is denied.).
2016-06-27 12:18:28.13 spid5s Error: 5120, Severity: 16, State: 101.
2016-06-27 12:18:28.13 spid5s Unable to open the physical file “E:\SystemDatabases\Master\master.mdf”. Operating system error 5: “5(Access is denied.)”.
2016-06-27 12:18:28.13 spid5s Error: 17204, Severity: 16, State: 1.
2016-06-27 12:18:28.13 spid5s FCB::Open failed: Could not open file E:\SystemDatabases\Master\mastlog.ldf for file number 2. OS error: 5(Access is denied.).
2016-06-27 12:18:28.13 spid5s Error: 5120, Severity: 16, State: 101.
2016-06-27 12:18:28.13 spid5s Unable to open the physical file “E:\SystemDatabases\Master\mastlog.ldf”. Operating system error 5: “5(Access is denied.)”.
2016-06-27 12:18:28.13 spid5s SQL Server shutdown has been initiated

–> The above error log (in bold) tells that the service account NT Service\MSSQLSERVER does not have access to the files moved to new location.

–> So, to provide access, go to the new folder location –> Right click and select Properties –> Go to Security tab, click on Edit –> click on Add, to add the Service Account. Apply the Service Account (here in my case NT Service\MSSQLSERVER) and change the Locations to your PC, as its a local Service Account:

Give “Full Control”, and click OK on popups.

Now Start the “SQL Server” services and this time it will work.

To check and confirm the new file location of Master DB, Execute following SQL query:

USE master
GO

SELECT *
FROM sys.database_files

Categories: DBA Stuff, SQL Errors Tags: Move Master DB

SQL Error – SSMS, ‘-120’ is not a valid value for property ‘Width’ or ‘Height’

June 17, 2016 4 comments

Today I came across a weird error as my colleague was trying to open SSMS (SQL Server Management Studio), and it was throwing following error:

‘-120’ is not a valid value for property ‘Width’

… in the dialogue box shown below:

SSMS Width Height issue

… a similar error can also occur for Height also (2nd image).

By checking the error its obvious that there is something wrong with Width or Height of SSMS Query-Editor window.

So, I went to REGEDIT (In RUN, type regedit.exe) and after navigating here n there got the location where to update this property.

Navigate to folder: HKEY_CURRENT_USER\SOFTWARE\Microsoft\SQL Server Management Studio\13.0\

Here check the MainWindow property value (image below), it was showing: 0 451 109 -120 876 1

Change it to a positive value considering the width of your SSMS editor window, I replaced -120 with 1400

SSMS Width Height issue Regedit

Click OK and close the Registry Editor window and re-open SSMS, it will not show this error again !

Categories: SQL Errors Tags: SSMS error

Polybase error in SQL Server 2016 : Row size exceeds the defined Maximum DMS row size, larger than the limit of [32768 bytes]

June 9, 2016 Leave a comment

I got an email form one of my reader regarding issues while working with SQL Server 2016 and Polybase, and it is as follows:

I am able to successfully install SQL with Polybase and able to query data in Azure storage but for a table I am getting error.

I am trying to pull data by creating External Data Source connection in SQL enabled Polybase features. I am getting below error as:

Cannot execute the query “Remote Query” against OLE DB provider “SQLNCLI11” for linked server “(null)”. 107093;Row size exceeds the defined Maximum DMS row size: [40174 bytes] is larger than the limit of [32768 bytes]

With the error description its quiet evident that the External tables does not support row size more than 32768 bytes. But still I take a look online and found in Azure Documentation that this is a limitation right now with Polybase. The Azure document mentions:

Wide rows support is not supported yet, “If you are using Polybase to load your tables, define your tables so that the maximum possible row size, including the full length of variable length columns, does not exceed 32,767 bytes. While you can define a row with variable length data that can exceed this figure, and load rows with BCP, you will not be be able to use Polybase to load this data quite yet. Polybase support for wide rows will be added soon. Also, try to limit the size of your variable length columns for even better throughput for running queries.”

link: https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-develop-table-design/

Categories: SQL Errors, SQL Server 2016 Tags: Polybase, Polybase error, SQL Server 2016

SQL Server 2012: Error handling for multiple errors – MSDN TSQL forum

May 28, 2016 Leave a comment

–> Question:

I have a stored procedure which steps through a list of transaction log backups and applies them to a DR database with NORECOVERY. As part of our DR test process, it is sometimes possible that the DR database gets ahead of the transaction log list. In this circumstance I get the error:

Msg 4326, Level 16, State 1, Line 8
The log in this backup set terminates at LSN 74000000023300001, which is too early to apply to the database. A more recent log backup that includes LSN 74000000025200001 can be restored.

Msg 3013, Level 16, State 1, Line 8
RESTORE LOG is terminating abnormally.

This is fine and I’m happy to ignore this log and move on to the next one. However there appears to be no way to catch the 4326 error. Wrapping the restore in a TRY … CATCH only identifies the 3013 error number and that could be caused by many issues that I don’t want to ignore. If my TRY … CATCH uses the THROW command, both the 4326 and 3013 errors are displayed so my session clearly has a handle to them both.

My questions are:

1. Can I catch the first error thrown?
2. Can I review all the errors thrown?
3. Can the output from THROW be captured in a variable so I can parse it?

–> Answer:

While using RAISERROR it will only catch the last error thrown, it won’t catch and return all the errors.

The new THROW keyword introduced in SQL Server 2012 returns all the errors, check this link: https://sqlwithmanoj.com/2015/02/04/capture-multiple-errors-in-try-catch-by-using-throw-statement/

But I don’t think if there is any mechanism to store both the errors returned by any script as after THROW the execution ends and control is transferred to the client.

Other than using DBCC OUTBUFFER(@@spid), you will need to parse the multiple error details spread through several rows.

Ref link.