MSSQLWIKI

Karthick P.K on SQL Server

Posts Tagged ‘State: 1.’

Optimizer Timeout or Optimizer memory abort

Posted by Karthick P.K on October 7, 2012

Optimizer Timeout

When the query processor finds itself consuming a lot of time optimizing a query, it may decide to stop the optimization process abruptly, and choose the best available plan. This is to ensure that the optimizer doesn’t end up optimizing forever. This is  called optimizer timeout (based on the number of plans considered relative to the cost of the best plan so far).

Optimizer memory abort

When queries become more complex number of potential plans to consider can quickly grow in thousands. Optimizer has limit for memory it is allowed to use , when the optimizer reaches the limit it ends with  optimizer memory abort.

When  timeout or memory abort happens optimizer might choose the best plan  from plans which was generated till timeout or abort and it might be far from optimal plan so the query execution can take long time and consume resource.

On SQL 2000 and earlier the only way to detect this condition is compiling the query with trace flag 8675.   If one of these conditions occur the output will reflect a timeout abort or memory abort, similar to the following:

 

End of simplification, time: 2.869 net: 2.869 total: 2.869 net: 2.869

end exploration, tasks: 200094 no total cost time: 16.17 net: 16.169 total: 19.04 net: 19.039

*** Optimizer time out abort at task 614400 ***

*** Optimizer time out abort at task 614400 ***

 

Msg 8623, Level 16, State 1, Line 3

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

End of simplification, time: 0.156491 elapsed: 0.156491

end exploration, tasks: 1614 no total cost time: 0.552436 elapsed: 0.708927

end search(0),  cost: 1275.32 tasks: 3888 time: 0.195008 elapsed: 0.903935

end exploration, tasks: 7596 Cost = 1275.32 time: 0.548032 elapsed: 1.45197

end search(1),  cost: 1263.15 tasks: 21985 time: 2.30564 elapsed: 3.75761

*** Optimizer memory usage abort ***

End of optimization,  elapsed: 2.98304

From SQL server 2005 to determine whether the query optimizer times out or MemoryLimitExceeded search for the 
StatementOptmEarlyAbortReason="TimeOut" (or) StatementOptmEarlyAbortReason="MemoryLimitExceeded" expression in the XML plan output.

 

We can avoid optimizer from timing out and picking bad plan by enabling trace flag –T8780. This increases the time limit before the timeout occurs.

Note: Don’t enable this trace flag at server level , enable it only for the session which runs the query and identify if the optimizer is picking up a better plan. If you see optimizer picking up the better plan, right approach is to tune the query manually or using DTA and apply the recommendations . You can use this trace flag till you apply the recommendations made by DTA.

 

If you experience “ Optimizer memory usage abort” use “SQLServer:Memory Manager\Optimizer Memory (KB)” counter to the amount used for compilation .

select * from sys.dm_os_memory_clerks where type=’MEMORYCLERK_SQLOPTIMIZER’ will tell us the overall memory used by optimizer.

We can also use the CompileMemory= expression in XML plan output starting from SQL server2005 SP2 which will give us the compile memory used by individual plans. If you find optimizer memory is very low then identify what is contributing to memory contention in SQL Server and tune it.

I will discuss compile memory in detail when I blog about Resource_semaphore  wait types.

 

Note: You may also receive below error because of few known issues documented in KB articles 982376, 946020,926773,917888 so if none of the fixes resolve the issue you may have to follow the same steps documented above.

{

"Msg 8623, Level 16, State 1, Line 1

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information."

}

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

 

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer
The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in Performance, SQL General, SQL Query, SQL Server Engine, SQL Server memory | Tagged: , , , , , , , , , , | 11 Comments »

(SQLServer) Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893802.

Posted by Karthick P.K on April 19, 2012

SQL Server might fail to start with below error

Server Error: 17190, Severity: 16, State: 1.

Server Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893802.

Server Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate

Error: 15466, Severity: 16, State: 1.

spid7s An error occurred during decryption.

Cause

CryptAcquireContext function is used by SQL Server to acquire a handle to key containers, create key containers and destroy key containers.

By default CryptAcuireContext function create key in “Roaming\Microsoft\Crypto\..” under path mentioned in below registry

HKEY_USERS\S-1-X-XXX\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders\AppData

If the AppData Key is missing or if the user don’t have permission in path mentioned in above registry or if user profile is corrupted we might end up with above error.

To narrow down the issue outside SQL-Server run THIS executable which will Open or Create key container if it doesn’t exist. If the exe fails look at error code returned by exe and troubleshoot further.

To check if the problem is because of corrupted profile modify the path mentioned in HKEY_USERS\S-1-X-XXX\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders\AppData folder to a

different path and check if the exe is able to create the key container.

 

Source  code for Exe is below

#include <windows.h> 
#include <string> 
#include <winbase.h> 
#include <iostream> 
using namespace std;
#include <Wincrypt.h >
 
                                      
void main()
{
LPCSTR rgwchKeyContName = "Test123456";  
HCRYPTPROV m_hCryptoProviderFB;
BOOL ret;
BOOL ret2;

ret=CryptAcquireContext(&m_hCryptoProviderFB, rgwchKeyContName, MS_ENHANCED_PROV, PROV_RSA_FULL, CRYPT_SILENT);
    
if (!ret && GetLastError() == NTE_BAD_KEYSET)

{
    
    printf("\nUnable to open Keyset.CryptAcquireContext failed with error: 0x%X . \nWe will try creating key",GetLastError());

    ret2=CryptAcquireContext(&m_hCryptoProviderFB, rgwchKeyContName, MS_ENHANCED_PROV, PROV_RSA_FULL, CRYPT_NEWKEYSET | CRYPT_SILENT);
        if (!ret2)
        {
        printf("\nCryptAcquireContext failed creating key.Error: 0x%X",GetLastError());
        }
        else
        {
        printf("\nKey created");
        }
    exit;
}


else if (!ret && GetLastError() == NTE_BAD_KEYSET)
{
printf("CryptAcquireContext failed with error: 0x%X",GetLastError());
}

else
{

    printf("CryptAcquireContext opened key. Return value is 0x%X.",ret);
}

    if (CryptReleaseContext(m_hCryptoProviderFB,0))
    {
    printf("\nHandle is released.\n");
    }
    else
    {
    printf("\nHandle could not be released.\n");
    }

}

 

 

Thanks

Karthick P.K

Posted in Configuration, Security, Startup failures | Tagged: , , , , , , , | 19 Comments »

Linked server connection fails with “An error occurred during decryption”

Posted by Karthick P.K on January 9, 2012

We might get Error: 15466, Severity: 16, State: 2  An error occurred during decryption while installing Projects servers (or) Sending mails using database mail (or) Linked server connections might fail with Msg 15593, Level 16, State 1, Line 1

Linked server connection fails with below error

{

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
An error occurred during decryption. (Microsoft SQL Server, Error: 15466)

Msg 15593, Level 16, State 1, Line 1

An error occurred while decrypting the password for linked login ‘distributor_admin’ that was encrypted by the old master key. The error was ignored because the FORCE option was specified.

}

Database mail might fail with below error

{

Set mail server login password failed for MailServer ‘Domain’.  (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

An error occurred during decryption. (Microsoft SQL Server, Error: 15466)

}

Or

You notice below errors in SQL Server errorlogs

spid10s Error: 15581, Severity: 16, State: 3.
Please create a master key in the database or open the master key in the session before performing this operation.

Cause

SQL Server service account was changed from services control manager (or) service master key was not backed up and restored when migrating SQL Server to another computer domain.

{

http://msdn.microsoft.com/en-us/library/ms187788.aspx

To change the SQL Server service account, use SQL Server Configuration Manager. To manage a change of the service account, SQL Server stores a redundant copy of the service master key protected by the machine account that has the necessary permissions granted to the SQL Server service group. If the computer is rebuilt, the same domain user that was previously used by the service account can recover the service master key. This does not work with local accounts or the Local System, Local Service, or Network Service accounts. When you are moving SQL Server to another computer, migrate the service master key by using backup and restore.

The REGENERATE phrase regenerates the service master key. When the service master key is regenerated, SQL Server decrypts all the keys that have been encrypted with it, and then encrypts them with the new service master key. This is a resource-intensive operation. You should schedule this operation during a period of low demand, unless the key has been compromised. If any one of the decryptions fail, the whole statement fails.

The FORCE option causes the key regeneration process to continue even if the process cannot retrieve the current master key, or cannot decrypt all the private keys that are encrypted with it. Use FORCE only if regeneration fails and you cannot restore the service master key by using the RESTORE SERVICE MASTER KEY statement.

}

 

Resolution

Regenerate the service master key using ALTER SERVICE MASTER KEY REGENERATE

If you receive the following error message when running ALTER SERVICE MASTER KEY REGENERATE.

{

The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.

}

We are left with only option to force regenerating service master key using ALTER SERVICE MASTER KEY FORCE REGENERATE “.

Note:The service master key is the root of the SQL Server encryption hierarchy. The service master key directly or indirectly protects all other keys and secrets in the tree. If a dependent key cannot be decrypted during a forced regeneration, the data the key secures will be lost.

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Posted in Configuration, Connectivity, Security, SQL Server Tools | Tagged: , , , , , , , , , , | 7 Comments »

SQL Server2008/SQL Server2012: Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 574, state 0, severity 16

Posted by Karthick P.K on November 17, 2010

SQL Server 2008 : Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 574, state 0, severity 16

SQL Server 2012 : Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’  encountered error

SQL Server 2008/2012 instance fails to start or hangs after service pack or Cumulative update installation.

 

Error

Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 574, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

 

 

Error: 574, Severity: 16, State: 0.

CONFIG statement cannot be used inside a user transaction.

Error: 912, Severity: 21, State: 2.

Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 574, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Error: 3417, Severity: 21, State: 3.

Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 15173, state 1, severity 16

Start SQL Server from command prompt using trace flag –T902 to disable script execution

1.Turn off Implicit transaction

{

EXEC sys.sp_configure N’user options’, N’0′

GO

RECONFIGURE WITH OVERRIDE

GO

}

2. SQL Server not able to create temp_MS_AgentSigningCertificate_database.mdf

Error:

{

Directory lookup for the file "P:\Data\temp_MS_AgentSigningCertificate_database.mdf" failed with the operating system error 2(The system cannot find the file specified.).

Error: 1802, Severity: 16, State: 1.

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

spid7s Error: 912, Severity: 21, State: 2.

spid7s Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 598, state 1, severity 25.

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘Q:\Data\temp_MS_AgentSigningCertificate_database_log.LDF’.

}

This error is raised when the default database location is invalid. Edit below registry to have a valid directory for default database location.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<Instance Name>\Setup\SQLDataRoot

3. Check if there are Orphan users in system databases and fix them.

{

EXEC sp_change_users_login ‘Report’;

}

4. If you see “error 15173, state 1, severity 16”

Ex: Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 15173, state 1, severity 16

Revoke the permissions granted on ‘##MS_PolicyEventProcessingLogin##’

you can use the below script to identify the users who have permissions granted on ‘##MS_PolicyEventProcessingLogin##’

select a.name,b.permission_name from sys.server_principals a,sys.server_permissions b,sys.server_principals c

where a.principal_id= b.grantee_principal_id and b.grantor_principal_id=c.principal_id and c.name = ‘##MS_PolicyEventProcessingLogin##’

 

Resolution

If none of the above resolves the issue then you can use Trace flag -T3601 which causes the first 512 characters of each batch being executed to be printed to the error log. Identify the batch which is failing and troubleshoot the batch.

 

If you liked this post, do like us on FaceBook at https://www.facebook.com/mssqlwiki and join our FaceBook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

 

 

Thank you,

Karthick P.K | My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in SQL Cluster Setup, SQL Server Setup, Startup failures | Tagged: , , , , , , , , , , , | 14 Comments »

 
%d bloggers like this: