Integration Services server cannot be configured because there are active operations. Wait until there are no active operations, and then try to configure the server again

SSISDB.internal.cleanup_server_log will fail with

{

Integration Services server cannot be configured because there are active operations. Wait until there are no active operations, and then try to configure the server again.

}

SSISDB.internal.cleanup_server_log will fail with below error

{

Integration Services server cannot be configured because there are active operations. Wait until there are no active operations, and then try to configure the server again.

}

When you have pending or active operations in [internal].[operations] table.

Resolution

To resolve the issue identify all the operations with status 2 (running) or status 5 (pending) from Internal.operations table and then stop the running operations (SSMS->Integration Services CatalogsàSSISDBàRight click active operations). If there are any rows with status 5 they might be orphan records so check when they are started and delete them if they are orphan. You can use below query to identify running and pending operations.

SELECT * FROM [internal].[operations] where status=2 or status=5

Once all the rows with status 2 or 5 is removed/updated we can run SSISDB.internal.cleanup_server_log

Advertisement

SQL Server setup fails with “Failed to retrieve data for this request”

 

SQL Server setup would fail with below error in message box “Failed to retrieve data for this request”

{

TITLE: SQL Server Setup failure.

——————————

SQL Server Setup has encountered the following error:

Failed to retrieve data for this request..

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&EvtType=0xE8A0C283%25400xAC7B1A58%25401233%254053

——————————

BUTTONS:

OK

}

In Summary.txt you will notice error similar to one below

Overall summary:

Final result: Failed: see details below

Exit code (Decimal): -2146233088

Exit facility code: 19

Exit error code: 5376

Exit message: Failed to retrieve data for this request.

Start time: 2015-02-19 19:48:58

End time: 2015-02-19 19:50:47

Requested action: Install

Exception help link: http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=12.0.2342.0&EvtType=0xE8A0C283%400xAC7B1A58%401233%4053&EvtType=0xE8A0C283%400xAC7B1A58%401233%4053

Exception summary:

The following is an exception stack listing the exceptions in outermost to innermost order

Inner exceptions are being indented

Exception type: Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException

Message:

Failed to retrieve data for this request.

HResult : 0x80131500

Data:

HelpLink.ProdName = Microsoft SQL Server

HelpLink.BaseHelpUrl = http://go.microsoft.com/fwlink

HelpLink.LinkId = 20476

HelpLink.EvtType = 0xE8A0C283@0xAC7B1A58@1233@53

DisableWatson = true

Stack:

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.ProcessDTbl(DataTable dt, Int32 level)

at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.CollectSqlDiscoveryData(String machineName)

at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.CollectDiscoveryData(String machineName)

at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.LoadData(IEnumerable`1 machineNames, String discoveryDocRootPath, String clusterDiscoveryDocRootPath)

at Microsoft.SqlServer.Configuration.SetupExtension.RunDiscoveryAction.ExecuteAction(String actionId)

at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)

at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClasse.<ExecuteActionWithRetryHelper>b__b()

at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)

Inner exception type: Microsoft.SqlServer.Configuration.Sco.SqlRegistryException

Message:

The network path was not found.

HResult : 0x84d10035

FacilityCode : 1233 (4d1)

ErrorCode : 53 (0035)

Data:

WatsonData = Microsoft.SqlServer.Configuration.Sco.SqlRegistryException@Win32Error

Stack:

at Microsoft.SqlServer.Configuration.Sco.SqlRegistry.CreateBaseKey(ServiceContainer ctx, String machineName, IntPtr hKey, String keyName, RegistryAccess access, RegistryView view)

at Microsoft.SqlServer.Configuration.Sco.SqlRegistry.GetLocalMachine(ServiceContainer ctx, String machineName, RegistryAccess access, RegistryView view)

at Microsoft.SqlServer.Discovery.DiscoveryUtils.GetLocalMachineRootKey(ServiceContainer ctx, String machineName, RegistryView registryView)

at Microsoft.SqlServer.Discovery.DiscoveryUtils.GetLocalMachineSubKey(ServiceContainer ctx, String machineName, RegistryView regView, String regPath, RegistryAccess registryAccess)

at Microsoft.SqlServer.Discovery.DiscoveryEnumObject.GetSql2kMsiInstanceListInHive(String machineName, RegistryView regView)

at Microsoft.SqlServer.Discovery.DiscoveryEnumObject.LoadSql2kInstanceList(String machineName)

at Microsoft.SqlServer.Discovery.Product.GetData(EnumResult erParent)

at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()

at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

Watson error

Watson bucket data:

Bucket param 1: SQL Server 2014@RTM@

Bucket param 2: 0x6785B09D

Bucket param 3: 0xE8A0C283

Bucket param 4: 0x74E34741

Bucket param 5: 0xAC7B1A58@1233@53

Bucket param 6: RunRemoteDiscoveryAction

Bucket param 7:

Bucket param 8:

Bucket param 9: 0xD195CE25

Bucket param 10:

From detail.txt

(01) 2015-02-19 19:49:25 Slp: Discovery on nodename failed due to exception

(01) 2015-02-19 19:49:25 Slp: Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException: Failed to retrieve data for this request. —> Microsoft.SqlServer.Configuration.Sco.SqlRegistryException: The network path was not found.

at Microsoft.SqlServer.Configuration.Sco.SqlRegistry.CreateBaseKey(ServiceContainer ctx, String machineName, IntPtr hKey, String keyName, RegistryAccess access, RegistryView view)

at Microsoft.SqlServer.Configuration.Sco.SqlRegistry.GetLocalMachine(ServiceContainer ctx, String machineName, RegistryAccess access, RegistryView view)

at Microsoft.SqlServer.Discovery.DiscoveryUtils.GetLocalMachineRootKey(ServiceContainer ctx, String machineName, RegistryView registryView)

at Microsoft.SqlServer.Discovery.DiscoveryUtils.GetLocalMachineSubKey(ServiceContainer ctx, String machineName, RegistryView regView, String regPath, RegistryAccess registryAccess)

at Microsoft.SqlServer.Discovery.DiscoveryEnumObject.GetSql2kMsiInstanceListInHive(String machineName, RegistryView regView)

at Microsoft.SqlServer.Discovery.DiscoveryEnumObject.LoadSql2kInstanceList(String machineName)

at Microsoft.SqlServer.Discovery.Product.GetData(EnumResult erParent)

at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()

at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

— End of inner exception stack trace —

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

 

Resolution

1. Check the connectivity between node from which you are installing SQL Server to other nodes. (\\Othernodes should work when you are installing SQL on box which is part of windows cluster regardless of SQL Cluster).

The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication.

When you configure replication subscriber and if your subscriber instance is in higher version than publisher instance you might get below error

Example when you configuring subscriber on SQL Server 2014 instance for SQL Server 2012 Publisher  using SQL Server 2012 SSMS you would get below error.

Error:

The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication.

Resolution:

Create subscriber using higher version SSMS (SSMS has to be subscriber version or higher) or create subscriber using TSQL.

Sp_rename fails : Either the parameter @objname is ambiguous or the claimed @objtype (object) is wrong.

Sp_rename fails with

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 357

Either the parameter @objname is ambiguous or the claimed @objtype (object) is wrong.

You would get this error if you have ‘DOT’ in between your constraint name. SP_rename fails because object_id(ContraintnamewithDOT) returns NULL for constraint objects with DOT (.) in their name.

Solution

Drop and recreate the constraint without DOT (.) in name.

SSIS package fails with out of memory errors

You might get below out of memory errors when you run SSIS packages 

Errors

A buffer failed while allocating n bytes. The system reports n percent memory load. There are n bytes of physical memory with n bytes free.

There are 2147352576 bytes of virtual memory with 44814336 bytes free. The paging file has 687069143034 bytes with 35348451328 bytes free.

A buffer failed while allocating 63160 bytes. The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E. 

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component “Read Results” (975) returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure. 

The system reports 2 percent memory load. There are 206147428352 bytes of physical memory with 24692375552 bytes free. There are 8796092891136 bytes of virtual memory with 8713333325824 bytes free. The paging file has 205966053376 bytes with 15835136 bytes free.

Executed as user: Domain\Password . …9.00.4035.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  6:06:21 AM  Error:     Code: 0xC0047012     Source: Load Daily Run Piece DTS.Pipeline     Description: A buffer failed while allocating 6356480 bytes.  End Error  Error: 2009-12-29 06:19:41.56     Code: 0xC0047011     Source: Load Daily Run Piece DTS.Pipeline     Description: The system reports 82 percent memory load. There are 4294656000 bytes of physical memory with 756637696 bytes free. There are 2147352576 bytes of virtual memory with 399495168 bytes free. The paging file has 8418062336 bytes with 5051555840 bytes free.

The system reports 24 percent memory load. There are 68716240896 bytes of physical memory with 51754737664 bytes free. There are 2147352576 bytes of virtual memory with 146911232 bytes free. The paging file has 137430540288 bytes with 120562003968 bytes free.

Below are options you can use to solve the above errors

1. Check if the Page file for the system is configured properly, you might get this error if there is no page file or if page file is very small

2. Change the lookup cache mode from Full to Partial or no cache mode.

3. Migrate to 64-Bit SSIS if you are in 32-BIT. Remember you have to migrate all the drivers and providers used in connections. If you are in 64 BIT system and getting above errors while executing packages from BIDS then check if you have set package to run in 64bit run time ( Run64bitruntime = True in  Integration services project properties page).

4.  Divide the package in to multiple child packages (Execute package task –> ExecuteOutOfProcess =True)

5. Tweak DefaultBufferMaxRows and DefaultBufferMaxSize size.

Cannot bring the Windows Server Failover Clustering (WSFC) resource (ID ‘ ‘) online (Error code 5018).

Problem description

You might get below error when configuring Always on availability group If the cluster services startup account is not part of SQL Server logins.

Error:

TITLE: Microsoft SQL Server Management Studio

——————————

Creating availability group resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)

——————————

ADDITIONAL INFORMATION:

Create failed for Availability Group ‘KK_AG’.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3373.0+((SQL11_SP1_QFE-CU).130629-2102+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+AvailabilityGroup&LinkId=20476

——————————

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

——————————

Cannot bring the Windows Server Failover Clustering (WSFC) resource (ID ’74eaaa74-4e7d-470a-96b4-29459cb4516e’) online (Error code 5018).  The WSFC service may not be running or may not be accessible in its current state, or the WSFC resource may not be in a state that could accept the request.  For information about this error code, see “System Error Codes” in the Windows Development documentation.

Failed to designate the local availability replica of availability group ‘KK_AG’ as the primary replica.  The operation encountered SQL Server error 41066 and has been terminated.  Check the preceding error and the SQL Server error log for more details about the error and corrective actions.

Failed to create availability group ‘KK_AG’.  The operation encountered SQL Server error 41160 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command. (Microsoft SQL Server, Error: 41066)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3373&EvtSrc=MSSQLServer&EvtID=41066&LinkId=20476

 

You would also see errors similar to one  below when you configure always on availability group using scripts.

 

Msg 41131, Level 16, State 0, Line 1

Failed to bring availability group ‘TDE_AG’ online.  The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online. Then verify that the availability group resource exists in the WSFC cluster. If the problem persists, you might need to drop the availability group and create it again.

Msg 41152, Level 16, State 2, Line 1

Failed to create availability group ‘TDE_AG’.  The operation encountered SQL Server error 41131 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command.

Msg 41131, Level 16, State 0, Line 1

Failed to bring availability group ‘TDE_AG’ online.  The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online. Then verify that the availability group resource exists in the WSFC cluster. If the problem persists, you might need to drop the availability group and create it again.

Msg 41152, Level 16, State 2, Line 1

Failed to create availability group ‘TDE_AG’.  The operation encountered SQL Server error 41131 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command.

Resolution:

Add start up account of cluster service to SQL Server login and grant sysadmin role (Start up account of cluster service will be nt authority\system by default).

SQL Server cluster installation checklist

List of checks you may need to perform before you install SQL Server Cluster.

1. Verify your system meets the minimum requirements
910228 SQL Server 2005 Readme and installation requirements
http://support.microsoft.com/default.aspx?scid=kb;EN-US;910228
907284 Changes to the readme file for SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;EN-US;907284
327518 The Microsoft support policy for a SQL Server Failover cluster – Please note that ONLY Microsoft Failover Clusters are supported.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;327518
SQL Server 2008  Before Installing Failover Clustering
http://download.microsoft.com/download/4/5/3/453739ed-cb74-46b1-b7af-f157ee71db20/SQL2008Readme.htm.
http://msdn.microsoft.com/en-us/library/ms189910%28v=SQL.100%29.aspx

2. Log on as an Administrator.
SQL Server 2005/2008:  Log on to the cluster node as a domain administrator that is also local administrator on all nodes, but not necessarily an account that is to be used as a service startup account for the SQL Server services.

Pre create user groups in active directory for different SQL Server 2005/2008 services(SQL,Agent,FT).SQL Server setup will add the service accounts of SQL Services to this groups during setup. You have to pre create unique group name for each Service in AD and enter it when prompted during SQL Server2005/2008 Cluster setup.
SQL Server Setup grants the following Windows NT rights and privileges to the Group you enter during setup (If you can not run the setup as domain administrator then add the service logon accounts to the security groups created in AD before the setup).
For SQL Server group
Log on as a service (SeServiceLogonRight)
Act as part of the operating system (SeTcbPrivilege) (only on Windows 2000)
Log on as a batch job (SeBatchLogonRight)
Replace a process-level token (SeAssignPrimaryTokenPrivilege)
Bypass traverse checking (SeChangeNotifyPrivilege)
Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
Permission to start SQL Server Active Directory Helper
Permission to start SQL Writer
and many more
For  SQL Server Agent group
Log on as a service (SeServiceLogonRight)
Act as part of the operating system (SeTcbPrivilege) (only on Windows 2000)
Log on as a batch job (SeBatchLogonRight)
Replace a process-level token (SeAssignPrimaryTokenPrivilege)
Bypass traverse checking (SeChangeNotifyPrivilege)
Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)

3. There should not be a pending Reboot requirement

Check pending Reboot requirement in following registry:
HKLM\SYSTEM\CurrentControlSet\Control\Session Manager
In the right-hand pane, is there a value of PendingFileRenameOperations?  If so, is it empty?
If empty close the registry editor.  If not empty, reboot the machine prior to running setup.  After logging in, re-check this registry value to make sure it is empty.

4. Verify Cluster “Heartbeat”
For a Failover Clustered Instance installation on Windows 2003 or earlier, verify cluster networking was setup per:
258750 Recommended private “Heartbeat” configuration on a cluster server
http://support.microsoft.com/?id=258750
NOTE: This needs to be followed explicitly in regards to the binding orders and your heartbeat adapter’s configuration.

5. Verify the TCP\IP Advanced DNS settings are identical on the “Public” network adapters between all Failover cluster nodes

6. Stop All Non- Microsoft Services  Excluding services required by SAN or network adapters online

7. Make sure  below services are running on all node’s
Cryptographic Services <====This must be running on all nodes.
DNS Client
Event Log
Remote Registry Service <====This must be running on all nodes.
Task Scheduler
Windows Management Instrumentation Driver Extensions
Windows Management Instrumentation

8.  Flush Cached DNS Entries
Flush the DSN cache.

Ipconfig /flushdns
then… Referesh te netbios name registered
Nbtstat –RR

9. Verify Logon Server
Open a command prompt on each node and enter the command:

set logonserver

Verify if logon server is same on all nodes.

10. Verify Admin Shares
IPC$
C$
Admin$
DriveLetter$
DriveLetter = Disk in which you have setup files.

Use the “Shared Folders” MMC snap-in  to verify the existing Administrative shares.

11. Only One RDP Session

Make sure no one else is logged in to any of the nodes. There should be only one logon session in active node which we use for setup.
12 . Make sure we are able to failover SQL Server groups to all nodes.

13. Cluster MSDTC
Create MSDTC resource.

14. If windows2008+ Grant  “Create Computer Objects” permission for  the computer object created for the cluster  (Computer Name object(CNO)).

http://mssqlwiki.com/2009/03/26/installation-of-sqlserver20052008-fails-on-windows2008-cluster/

15. For Windows 2008, see How to configure DTC for SQL Server in a Windows 2008 cluster http://blogs.msdn.com/cindygross/archive/2009/02/22/how-to-configure-dtc-for-sql-server-in-a-windows-2008-cluster.aspx
Use the following KB article to Cluster MSDTC if not already clustered on Windows 2003:
301600 How to configure Microsoft Distributed Transaction Coordinator on a Windows Server 2003 cluster
http://support.microsoft.com/default.aspx?scid=kb;EN-US;301600

16. Grant Read SPN and write SPN permission for startup account of SQL Server (or) Security group provided during installation for SQL Service startup account.

17) Verify Dependencies http://support.microsoft.com/default.aspx?scid=kb;EN-US;835185

PREEMPTIVE_OS_AUTHORIZATIONOPS waits in SQL Server

SQL Server threads which are controlled by SOS (SQL Server operating system) are Non preemptive but at times they switch preemptive when they can’t obey the rules of SOS. Some common places when SOS thread is switched preemptive are when we call extended proc’s, few Windows API etc.

 

Let us assume you use “execute as user x” in your job, SQL Server calls Windows functions like LookupAccountName  to get the credential of user. Windows functions interacts with AD services to get the credentials of account and return the info to the caller in SQL Server process and then SQL Server would build the logintoken. If there is a delay in AD and if it takes long time to respond to the windows function calls other threads in the same scheduler would get blocked so SQL Server thread would switch preemptive (Doesn’t follow SOS rules) before  making these  function  calls. PREEMPTIVE_OS_AUTHORIZATIONOPS wait type would occur when a thread is waiting on such windows functions (security) to return, So first thing which would have to do is to fix the performance of AD calls.

 

To narrow down and prove that this issue occurs because of Active directory performance. Login to SQL server using the startup account of SQL Server and execute below query when you notice PREEMPTIVE_OS_AUTHORIZATIONOPS wait type and compare the times printed. It will give you the time it takes for SQL Server to complete the AD calls.

 

 

create procedure PREEMPTIVEOSAUTHORIZATIONOPS  with execute as self

as

set nocount on

select CONVERT(varchar, getdate(), 126) PREEMPTIVEOSAUTHORIZATIONOPS

go

print convert(varchar, getdate(), 126)

exec dbo.PREEMPTIVEOSAUTHORIZATIONOPS;    

print convert(varchar, getdate(), 126)

go

A SQL product other than SQL Server 2014 CTP1 is detected. You cannot install this release until the existing instances of SQL products are uninstalled

When you install SQL Server 2014 set support rules would fail in “SQL product installation rule” with below error

—————————
Rule Check Result
—————————
Rule “Previous SQL product installation” failed. Â
A SQL product other than SQL Server 2014 CTP1 is detected. You cannot install this release until the existing instances of SQL products are uninstalled.

—————————
OKÂ Â Â
——————–

image

Cause

SQL Server 2014 CTP 1 is prerelease software and should be installed only on a clean machine with no other SQL Server instance.

More details in http://msdn.microsoft.com/en-us/library/dn169381(v=sql.15).aspx

Extract from above link

{

1.1 Limitations and Restrictions in SQL Server CTP 1

Issue: The following sections define the limitations and restrictions that apply to SQL Server CTP 1.

1.1.1 General limitations and restrictions
  • SQL Server 2014 CTP 1 is prerelease software and should be installed only on a clean machine.

  • SQL Server 2014 CTP 1 is a NON-PRODUCTION release and should not be installed and used in production environments.

  • SQL Server 2014 CTP 1 may contain branding from Microsoft SQL Server 2012 and is versioned as 11.0.

  • SQL Server 2014 CTP 1 is only available in the English language.

  • Microsoft SQL Server 2014 CTP 1 is not supported by Microsoft Customer Services and Support (CSS).

}

SQL Server Error while enabling Windows feature : NetFx3, Error Code : -2146498298

SQL Server 2012 setup might fails with below error when you do not have .Net frame work 3.5 features.

{
TITLE: Microsoft SQL Server 2012 Setup
——————————
The following error has occurred:
Error while enabling Windows feature : NetFx3, Error Code : -2146498298 , Please try enabling Windows feature : NetFx3 from Windows management tools and then run setup again. For more information on how to enable Windows features , see
http://go.microsoft.com/fwlink/?linkid=227143
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.2100.60&EvtType=0x681D636F%25401428%25401
——————————
BUTTONS:
}

 

To resolve the above error open server manager à Add roles and features wizard à select .Net frame work 3.5 feature and install as shown in below image.

clip_image002[9]

If the installation of.Net frame work 3.5 feature fails most probably it should be because of incorrect SXS folder. In such case choose an alternate SXS folder for installation in “confirm installation screen” (Which would appear as soon as you click install on above screen)
You will find SXS folder in Windows DVD under \sources\SXS

windows cluster freezes at “waiting for notification that node ‘‘ is a fully functional member of the cluster”

Adding node in windows cluster freezes at “waiting for notification that node ‘‘ is a fully functional member of the cluster”

When you try to add a node to cluster in windows2008 or windows2008 R2 or windows 2012 it may get stuck in the below screen With message “waiting for notification that node is a fully functional member of the cluster

After some time you will notice “operation is taking longer time than expected” and eventually after the time out add node wizard will fail with error “Unable to successfully cleanup”.

clip_image001

You may notice “the operation is taking longer than expected in some time”

If you look at cluster diagnostic log you may find

[RHS] RhsCall::Perform_NativeEH: ERROR_NOT_READY(21)’ because of ‘Startup routine for ResType MSMQTriggers returned 21.’

Failed to write SPNs to node’s computer object – status 5

Resolution

1. To resolve the exclude UDP port 3343 from fire wall.

2. If the issue persists after opening the UDP port 3343. Check failover clustering diagnostic event for error “Failed to write SPNs to node’s computer object – status 5”. If you find above error make sure http://support.microsoft.com/kb/976424 is applied in Domain controller.

3. If you find event ID 1289 “The Cluster Service was unable to access network adapter ‘Microsoft Failover Cluster Virtual Miniport’. Verify that other network adapters are functioning properly and check the device manager for errors associated with adapter ‘Microsoft Failover Cluster Virtual Miniport’. If the configuration for adapter ‘Microsoft Failover Cluster Virtual Miniport’ has been changed, it may become necessary to reinstall the failover clustering feature on this computer” follow the steps in http://support.microsoft.com/kb/973838

4. Make sure IPv6 is enabled on all NIC’s

5. Enabled Cluster communication and client access on the Public network http://technet.microsoft.com/en-us/library/cc728293%28v=ws.10%29.aspx

6. Make sure Symantec Endpoint Protection is uninstalled/disabled when you add the node.

7. Make sure there is no duplicate name computer objects for nodes in AD .follow http://blogs.technet.com/b/askcore/archive/2011/05/31/cluster-installation-time-out-issues.aspx

 

Bulk insert fails with error 4861 Cannot bulk load because the file could not be opened

When you do bulk insert in SQL Server it may fail with below error because of double hop .

Error :

Msg 4861, Level 16, State 1, Line 3

Cannot bulk load because the file “\\path\” could not be opened. Operating system error code 5(failed to retrieve text for this error. Reason: 15105).

Steps to fix the above error

1. Connect to SQL Server using SSMS (With account you run bulk insert) and execute below query and check if it is using Kerberos authentication

select net_transport,auth_scheme from sys.dm_exec_connections where session_id=@@spid

2. If the session is not using Kerberos authentication then fix the SPN issues (startup account of SQL Server should have read and write SPN permissions). Account which is running bulk inser should have read SPN permission. Setpn exe or adsiedit can be used to add or display all the SPN’s. If the SPn’s are registered properly and still connection fails to NTLM then get the output of SSPIClient.exe and verify why Kerberos authentication fails.

3. Make sure the account which is running bulk insert is trusted for delegation in Active Directory.

4. Account which is running bulk insert should have access to the shared directory in which BCP files are placed.

Reference  BULK INSERT (Transact-SQL) 

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

Security Account Delegation (Impersonation)

If a SQL Server user is logged in using Windows Authentication, the user can read only the files accessible to the user account, independent of the security profile of the SQL Server process.

When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.

For more information about this and other security considerations for using BULK INSERT, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK…) (SQL Server).

CryptAcquireContext and CryptReleaseContext example

CryptAcquireContext and CryptReleaseContext example

#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 has been released.\n");
	}
	else
	{
	printf("\nHandle could not be released.\n");
	}

}

Criticalsection example

Criticalsection and CreateThread example (EnterCriticalSection LeaveCriticalSection)

#include <windows.h> 
#include <string> 
#include <iostream> 
#include <process.h>    /* _beginthread, _endthread */
long a=0;
long b=0;
int Threadcount=64;
int s=Threadcount;
CRITICAL_SECTION  gcs; 
void Submain(void *x)
{
	for (int L=0;L<1000;L++) 
		{

			a=a++;
			EnterCriticalSection(&gcs);
						b=b++;
			LeaveCriticalSection(&gcs);
		}

/*
    s=s-1;  //Simple synchronization technique. May be useful if you like to increase the thread count WaitForMultipleObjects support value defined for MAXIMUM_WAIT_OBJECTS 64
	if(s==0)
	{
		d=TRUE;
	}
*/
_endthread();
}

 
void main()

{

HANDLE *hThreads;
hThreads = new HANDLE[Threadcount] ;
InitializeCriticalSection(&gcs);
for (int i=0;i<Threadcount;i++)
{
hThreads[i]=	CreateThread(NULL,NULL,(LPTHREAD_START_ROUTINE  )Submain,  NULL,  0,  NULL);
 	
		if (hThreads[i]==NULL)
		{
			printf("\nThread creation failed for thread %d with error %d",i,GetLastError());
		}

}

DWORD rw=WaitForMultipleObjects(Threadcount,hThreads,true,INFINITE);
DeleteCriticalSection(&gcs);
//while(!d); //Simple synchronization technique 

printf("Value of a is:%d\n" ,a);
printf("Value of b is:%d\n" ,b);
system("pause");
}

CreateProcess example

CreateProcess example

#include <windows.h> 
#include <string> 
#include <winbase.h> 
#include <iostream> 
using namespace std;

void main()
{
	int N=5;	
	cout<<"Enter count for process:";
	cin>>N;

	PROCESS_INFORMATION *x;
	STARTUPINFO *startup_info;

	startup_info = new STARTUPINFO[N];
	x =new  PROCESS_INFORMATION[N]; 
    HANDLE *h;
	h = new HANDLE[N];
	for (int i=0;i<N;i++)
		{
			memset((char *)&startup_info[i], 0, sizeof(STARTUPINFO));
			startup_info[i].cb = sizeof(STARTUPINFO);
			startup_info[i].dwFlags = STARTF_USESTDHANDLES;
			startup_info[i].hStdInput = GetStdHandle(STD_INPUT_HANDLE);
			printf("\nProcess creation starting:%d",i);
			CreateProcess("c:\\windows\\notepad.exe",NULL,NULL,NULL,FALSE,0x00010000,NULL,NULL,startup_info,&x[i]);
			h[i]= x[i].hProcess;
		
		}
		
	WaitForMultipleObjects(N, h,TRUE,INFINITE);
	
	for (int i=0;i<N;i++)
		{
		CloseHandle(x[i].hProcess);
		CloseHandle(x[i].hThread);
		}

}

CreateFileMapping or MapViewOfFileEx example

CreateFileMapping or MapViewOfFileEx example

#include <windows.h> 
#include <string> 
#include <winbase.h> 
#include <iostream> 
using namespace std;

void main()
{
	HANDLE  h;

	CHAR *filename;
 
	filename =new CHAR[2500];
	wcout<<"enter the file name:";
	cin.getline (filename,2500);
	h= CreateFile( filename,GENERIC_READ,FILE_SHARE_READ,NULL,OPEN_EXISTING,FILE_FLAG_SEQUENTIAL_SCAN,NULL);
	cout<<filename;
	if (h!=INVALID_HANDLE_VALUE)
	{
	printf("\nFile is opened/created");
	DWORD size = GetFileSize(h, NULL);
	HANDLE hFileMapping = CreateFileMapping(h, NULL,PAGE_READONLY, 0, 0, NULL);	
	
	CloseHandle(h);

	MapViewOfFileEx(hFileMapping, FILE_MAP_READ, 0,  0,0,NULL);           
		system("Pause");

	UnmapViewOfFile(hFileMapping);
	
	}
	else
	{
	printf("\nUnable to open or create file");
	}
	system ("pause");

}
		

Capture context switches from dm_os_ring_buffers

You can use the below query to extract the context switches information from ring buffers and time each thread spend owning the scheduler.

SELECT  
dateadd (ms, (a.[timestamp] - tme.ms_ticks), GETDATE()) as Time_Stamp,
a.*
FROM
(SELECT 
	  y as timestamp,	
      x.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
      x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
      x.value('(//Record/@time)[1]', 'bigint') AS [Time],
      x.value('(//Record/Scheduler/@address)[1]', 'varchar(30)') AS [Scheduler Address],
      x.value('(//Record/Scheduler/Action)[1]', 'varchar(30)') AS [Scheduler Action],
      x.value('(//Record/Scheduler/CPUTicks)[1]', 'bigint') AS [Scheduler CPUTicks],
      x.value('(//Record/Scheduler/TickCount)[1]', 'bigint') AS [Scheduler TickCount],
      x.value('(//Record/Scheduler/SourceWorker)[1]', 'varchar(30)') AS [Scheduler SourceWorker],
      x.value('(//Record/Scheduler/TargetWorker)[1]', 'varchar(30)') AS [Scheduler TargetWorker],
      x.value('(//Record/Scheduler/WorkerSignalTime)[1]', 'bigint') AS [Scheduler WorkerSignalTime],
      x.value('(//Record/Scheduler/DiskIOCompleted)[1]', 'bigint') AS [Scheduler DiskIOCompleted],
      x.value('(//Record/Scheduler/TimersExpired)[1]', 'bigint') AS [Scheduler TimersExpired]
FROM
      (SELECT CAST (record as xml),timestamp  
      FROM sys.dm_os_ring_buffers 
      WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER' ) AS R(x,y)) a
	  cross join sys.dm_os_sys_info tme 
WHERE a.[Scheduler Action] = 'SCHEDULER_SWITCH_CONTEXT'
ORDER BY       a.[Scheduler Address] , [Time_stamp]

SSIS package fails when executed as job using proxy account

Issue

The SQL server Integration package which transfers the data from data source like excel to SQL Server database fails when executed from SQL Agent job using proxy account

ERROR

Date                      4/25/2013 4:16:34 PM

Log                         Job History (SSISTest)

Step ID                 1

Server                   Myserver\SQL2008STD

Job Name                            SSISTest

Step Name                         SSISJob1

Duration                              00:00:01

Sql Severity                        0

Sql Message ID                 0

Operator Emailed                           

Operator Net sent                          

Operator Paged                               

Retries Attempted                          0

Message

Executed as user: MyDomain\MyUser1. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4276.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  4:16:34 PM  Info: 2013-04-25 16:16:34.81     Code: 0x4004300A     Source: Data Flow Task to move data from MS Excel to SQL server database SSIS.Pipeline     Description: Validation phase is beginning.  End Info  Error: 2013-04-25 16:16:35.04     Code: 0xC0202009     Source: Package Connection manager “Excel Connection Manager 1”     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: “Microsoft Access Database Engine”  Hresult: 0x80004005  Description: “The Microsoft Access database engine cannot open or write to the file ”. It is already opened exclusively by another user, or you need permission to view and write its data.”.  End Error  Error: 2013-04-25 16:16:35.05     Code: 0xC020801C     Source: Data Flow Task to move data from MS Excel to SQL server database Excel Source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager 1” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-04-25 16:16:35.07     Code: 0xC0047017     Source: Data Flow Task to move data from MS Excel to SQL server database SSIS.Pipeline     Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-04-25 16:16:35.07     Code: 0xC004700C     Source: Data Flow Task to move data from MS Excel to SQL server database SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-04-25 16:16:35.09     Code: 0xC0024107     Source: Data Flow Task to move data from MS Excel to SQL server database      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:16:34 PM  Finished: 4:16:35 PM  Elapsed:  0.764 seconds.  Process Exit Code 1.  The step failed.

Cause:

Missing permission on PROFILE directory of SQL server Agent service account for Proxy account .

BufferTEMPstorage path and BlobTempstoragepath are defaulted to TEMP and TMP environment variables for SQL Server agent start up account by default.

When you use proxy account to execute a package then proxy account should have access to temp and TMP folder of SQL Server agents start up account profile.

TEMP=C:\Users\StartupaccountofSQLAgent\AppData\Local\Temp

TMP=C:\Users\ StartupaccountofSQLAgent\AppData\Local\Temp

If your start up account of SQL Server agent is Local service or Network service then proxy account should have permission for TMP and TEMP folder located under C:\Windows\ServiceProfiles for Local service and Network service

If the proxy account doesn’t have access to this location SSIS job would produce the below error:

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted

04/08/2013 11:26:19,ExcelTC,Error,0,UKLONDT642483,ExcelTC,(Job outcome),,The job failed.  The Job was invoked by User MyDomain\MyUser1.  The last step to run was step 1 (ExcelTC\Package).,00:00:01,0,0,,,,0

04/08/2013 11:26:19,ExcelTC,Error,1,UKLONDT642483,ExcelTC,ExcelTC\Package,,Executed as user: MyDomain\MyUser1. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.   

Started:  11:26:19  Info: 2013-04-08 11:26:19.94     Code: 0x4004300A     Source: Data Flow Task SSIS.Pipeline     Description: Validation phase is beginning.  End Info  Error: 2013-04-08 11:26:19.98     Code: 0xC0202009     Source: Package Connection manager “Excel Connection Manager”     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: “Microsoft Office Access Database Engine”  Hresult: 0x80004005  Description: “Unspecified error”.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC020801C     Source: Data Flow Task Excel Source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC0047017     Source: Data Flow Task SSIS.Pipeline     Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:26:19  Finished: 11:26:19  Elapsed:  0.405 seconds.  Process Exit Code 1.  The step failed.,00:00:01,0,0,,,,0

04/08/2013 11:10:11,ExcelTC,Error,0,UKLONDT642483,ExcelTC,(Job outcome),,The job failed.  The Job was invoked by User MyDomain\MyUser1.  The last step to run was step 1 (ExcelTC\Package).,00:00:01,0,0,,,,0

04/08/2013 11:10:11,ExcelTC,Error,1,UKLONDT642483,ExcelTC,ExcelTC\Package,,Executed as user: MyDomain\MyUser1. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  11:10:12  Info: 2013-04-08 11:10:12.65     Code: 0x4004300A     Source: Data Flow Task SSIS.Pipeline     Description: Validation phase is beginning.  End Info  Error: 2013-04-08 11:10:12.78     Code: 0xC0202009     Source: Package Connection manager “Excel Connection Manager”     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: “Microsoft Office Access Database Engine”  Hresult: 0x80004005  Description: “Unspecified error”.  End Error  Error: 2013-04-08 11:10:12.78     Code: 0xC020801C     Source: Data Flow Task Excel Source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-04-08 11:10:12.79     Code: 0xC0047017     Source: Data Flow Task SSIS.Pipeline     Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-04-08 11:10:12.79     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-04-08 11:10:12.79     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:10:12  Finished: 11:10:12  Elapsed:  0.593 seconds.  Process Exit Code 1.  The step failed.,00:00:01,0,0,,,,0

How to stimulate a SQL Server resource failure in cluster

How to stimulate a SQL Server resource  failure in cluster?

If you like to perform a check in SQL Server cluster to identify if the SQL Server resources are failing over properly follow the below steps. you can use Step 2 to create a SQL Server hang and deadlocked scheduler dump in standalone server as well.

 

 

1.       Create a instance failures scenario by  issuing “shutdown” command to SQL Server which will terminate/Shutdown SQL Server. SQL Server resource DLL in cluster should detect that the SQL Server have failed and start the SQL Server resource automatically.

 

Steps

a.       Connect to SQL Server using an account which has Sysadmin privilege and execute Shutdown command.

 

 

2.       Create a SQL Server hang scenario by creating excessive blocking, once SQL Server exhaust all the available threads SQL Server will stop accepting connections and will go unresponsive. SQL Server resource in the cluster should detect that the SQL Server resource have failed and restart/failover SQL Server.

Steps

a.        Connect to SQL Server and below SQL Statements. (Do not close the session)

 


Create database test;
use test;
create table block  (a char(10));
insert into block values ('Test');
go 10
begin transaction;
update block set a ='Test1'

b. Download the Ostress.exe

c. Execute the following command in ostress.exe

ostress.exe -Sservername\Instancename -E -d test -Q "select * from test..block" -n 2000

Note: Replace servername\instancename with your server name and instance name. Above Ostress command creates 2000 connections and execute “select * from tempdb..block” which will in turn cause excessive blocking and make SQLServer exhaust all the available worker threads.

Is Alive check will fail once the threads are exhausted causing SQL Server to reach hung state. SQL Server resource DLL’s should detect that SQL Server is hung and restart/failover SQL Server.

 If you do not know what is SQL Server LooksAlive and IsAlive Check follow This blog

Error 601 : Could not continue scan with NOLOCK due to data movement.

When there is corruption in database (or) When scanning the data with the NOLOCK locking hint (or) with the transaction isolation level set to READ UNCOMMITTED, it is possible for the page at the current position of the scan would have deleted or moved by page splits caused by Inserts/Updates/Deletes making SQL Server not able to scan further and cause Error 601 : Could not continue scan with NOLOCK due to data movement.

Resolution
Unless the database has been explicitly marked as ‘read only’ there is no way to guarantee that there are no data modification operations going on.
The possible solutions are:
1. Check for 601 errors from the application and retry the query automatically if the error occurs.
2. Improve the indexes supporting the query or modify the query so that it has a smaller lock footprint and runs more quickly. If the query touches less data it will be less likely to encounter the problem.
3. Avoid use of NOLOCK hint and if necessary have a retry logic 601 error . Improving the indexes as mentioned above might make it possible to get this data without doing large scans that would be likely to cause blocking.
If you don’t use NOLOCK hint or to READ UNCOMMITTED Isolation level then check the database for corruption (Dbcc checkdb)

SQL Server monitoring

Every SQL Server DBA would have faced situations similar to SQL Server not accepting connections for few minutes, SQL Server not responding for few minute or Applications not able to connect with SQL Server for few minutes. Before DBA’s gets alerted about the situation and starts troubleshooting the issue. Everything becomes normal. Challenge in this situations is it becomes very difficult to understand where the underlying problem was, It could be a network connectivity, Application server problem or It might be an issue with SQL Server itself. How do we collect diagnostic data to prove that SQL Server was stable at the time of issue (or) If the issue is with SQL Server then how to collect data we need for diagnosing the issue when there is issue?

SQL Monitor to monitor SQL Server Services

SQL Monitor monitors the SQL Server services and creates log if SQL Server service is down (or) If SQL Server is not accepting (or) SQL Server is not responding to Queries

How it works?

SQL Monitor checks the SQL Server in 3-Phases

1. Check the status of SQL Server service through the windows service control manager

2. If the service is running then check if SQL Server is accepting connections

3. If SQL Server is accepting Connections then probe to perform a simple query and see if SQL Server is responding properly.

4. If SQL Server is not accepting Connections then connect to SQL Server using DAC and take a stack dump.

How to Configure

1. Create a folder call SQLMonitor in C:\

2. Create a Text file called serverlist.txt to fill all the SQLServer information in your account.

Format:

Servername [TAB] Servicename;

Ex:

Server1 MSSQLServer;

Server2 MSSQL$Prod;

3. Invoke command prompt and open attached SQLmonitor.EXE.

Advantage:

1. Multi-threaded . Each server and service is verified using its own thread so retrieving information from one server will not affect the pooling interval to other server.

2. Single exe can be scaled to monitor more than 1000 servers and 1000 services.

3. Uses few MB of memory and system resources.

You can Download SQL Monitor from this link

Removing database mirroring

When you try to recover and open mirrored database using dbcc dbrecover([DBNAME])

You would get below error

{

Msg 7930, Level 20, State 1, Line 1

Mirroring must be removed from the database for this DBCC command.

}

When you try to recover and open the database using “restore database [DBNAME] with recovery”

{

Msg 3104, Level 16, State 1, Line 1

RESTORE cannot operate on database dbname because it is configured for database mirroring. Use ALTER DATABASE to remove mirroring if you intend to restore the database.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

}

When you try to drop the database

Msg 3743, Level 16, State 1, Line 1

The database ‘dbname’ is enabled for database mirroring. Database mirroring must be removed before you drop the database.

Resolution

1 . ALTER DATABASE [databasename] SET partner OFF

Note: Above command will remove the mirroring .

2. restore database [databasename] with recovery

SQLServer LooksAlive and IsAlive Check

The resource DLL for the SQL Server service uses two functions that are used by MS Cluster service to check for availability of the SQL Server resource.

A simple check LooksAlive and more rigorous check called IsAlive

LooksAlive check: Cluster service calls looksAlive function every 5 seconds and LookAlive function Queries the service status by using the Windows NT Service Control Manager. When the LooksAlive test fails ISAlive test is called immediately.

ISalive Check: A more rigorous IsAlive function is called every 60 second and monitors the health of the SQL Server by opening up a connection to SQL Server and issuing “select @@servername” query over the connection. If the checks fail the online Thread reports this failure to the Cluster Service.

For example Assume SQL Server resource is initially in the offline state. Cluster service calls the routine in SQL Server resource DLL to bring the resource to an Online state. First the resource state is set as OnlinePending and then initiates the process of starting up the SQL Server resource. It starts the SQL Server, opens a connection to SQL Server and issues the “select @@servername” query, if this succeeds the resource is put in the Online state. If for some reason SQL Server is not able to start or the connection/query fails and SQL Server is not able to come Online within PendingTimeout, the cluster service tries to restart (or failover) the resource. If after repeated attempt SQL Server cannot be brought online, it is put in the failed state. If the SQLServer Resource DLL encounters an unrecoverable failure like failing to open cluster registry key etc. then resource DLL will put the SQL Server resource in Failed state. Once the resource is put in Failed state Cluster service will not attempt to restart/failover the resource and user intervention is required to diagnose and correct the issue.

By default, LooksAlive is fired every 5 seconds and IsAlive is fired every 60 seconds. The LooksAlive and IsAlive polling intervals can be changed in Cluster Administrator or failover cluster manager from the Advanced tab for the SQL Server resource or using the cluster.exe command prompt utility.

Builtin\Administrators cannot login in to SQL Server

 

If you add a windows login to administrators group and even though administrators group is part of SQL Server login you may not be able to login in to SQL Server using the windows login.

Similarly

1. You remove a windows login from a windows group and assume windows group is part of SQL server login had deny on certain objects in database.

2. Now you add the windows login explicitly to SQL Server logins and grant permissions on objects which had deny for windows group. Still the login will not be able to access the objects which have deny for the group and may raise 229  similar to one below

{The SELECT permission was denied on the object ”, database mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229) }

 

This can happen when SQL is creating the logintoken from LSACache.  (LSA Cache is not refreshed after the Admin2 is added to Administrators group)

1. Disable LSACache on the machine. Steps are included in http://support.microsoft.com/kb/946358.
2. Restart the machine.
3. Connect using the problematic login and try again.

SQL Server assert in Location: purecall.cpp:51

SQL Server assert in purecall.cpp:51

BEGIN STACK DUMP:

spid 231

Location: purecall.cpp:51

Expression: !”purecall”

SPID: 200

Process ID: 5125

Description: Pure virtual function call

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

Server SQL Server Assertion: File: <purecall.cpp>, line = 51 Failed Assertion = ‘!”purecall”‘ Pure virtual function call. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

Possible causes for above assert are

1. Antivirus softwares which detours in sqlserver address space can inject their instruction in sqlserver modules and can cause this Ex. Sophos etc..

Run select * from sys.dm_os_loaded_modules and check if there are DLL’d loaded from Antivirus (Company column will have the AV company name). If you see any antivrus exclude SQLServer from them.

(or)

Run lm command in the dump and see if there are any Antivirus DLL’s loaded in sqlserver process memory.

2. If you don’t see any Antivirus dll then run windows memory diagnostic tool and check if there are any memory problems on your system( %windir%\system32\MdSched.exe).

3. If there is no antivirus or memory errors follow the steps in http://mssqlwiki.com/2012/10/16/sql-server-exception_access_violation-and-sql-server-assertion/

Trace waits in SQLServer (SQLTRACE_BUFFER_FLUSH,TRACEWRITE,SQLTRACE_WAIT_ENTRIES,SQLTRACE_LOCK)

When you run Profiler trace from client systems or on server with large number of events you will see below wait types.

SQLTRACE_WAIT_ENTRIES
SQLTRACE_LOCK
SQLTRACE_BUFFER_FLUSH
TRACEWRITE

There is no way to completely avoid this wait type without stopping all the traces. We can reduce this waitypes by configuring server side trace instead of client side trace.

select * from sys.traces will give you information about all the traces. ( Status column 0 stopped and 1 active)

For the traces collected using profiler you will find a NULL Path. Profiler traces can cause large number of above waittypes.

Thread which raises the trace event is responsible to Get buffers to write event and write event. So collecting the trace on network share or on slow disk or using profiler can slow down the trace write and make the threads wait, sometimes we may also end with dead locked schedulers.

So ideally you have to avoid running profiler when you see below waits and use sp_trace_create if you like to capture the trace and pass the local path for tracefile parameter.

How to view the Space used by each table in database

There are situations in which you may have to list the space used by each table in the database. You can use the query below to get the space used by individual tables.
–Space used by the database

sp_spaceused

–Space used by individual tables in the database

SELECT
(row_number() over(order by a3.name, a2.name)) as SNO,
a3.name AS [schemaname],
a2.name AS [tablename],
a1.rows as row_count,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, 
a1.data * 8 AS data,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN 
(a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN 
(a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
(SELECT 
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN 
(ps.in_row_data_page_count + ps.lob_used_page_count + 
ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
(SELECT 
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> 'S' and a2.type <> 'IT'
ORDER BY a3.name, a2.name

FILESTREAM feature is disabled

Restore database (or) SQL Server setup fails in script upgrade mode with below error
{
Msg 5591, Level 16, State 4, Line 2
FILESTREAM feature is disabled.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
}

Follow the steps in http://msdn.microsoft.com/en-us/library/cc645923.aspx and enable File stream, then restore the database.

If the upgrade for SQL Server is failing with error “ESTREAM feature is disabled” then enable file stream in configuration manager by following above article and start SQL Server by following steps mentioned in

 SQL Server2008/SQL Server2012: Script level upgrade for database ‘master’ failed  and run below statement to change file stream access level

sp_configure filestream_access_level, 2
RECONFIGURE with override

After you executed the above statement start the SQL Server normally.

XP_readerrorlog fails with Failed to open loopback connection.

We might get below error when we open sqlserver errorlog in SSMS or using xp_readerrorlog or sp_readerrorlog

{

Msg 22004, Level 16, State 1, Line 0

Failed to open loopback connection. Please see event log for more information.

Msg 22004, Level 16, State 1, Line 0

error log location not found

}

1. If you get this error in Clustered instance of SQL Server then disable “shared memory protocol” in client configuration.

2. Check if you have wrong alias created and fix them.

3. UAC can also cause this (You might see following message in sqlserver errorlog “Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors”).when you run SSMS run it in elevated mode (right click SSMS–>Run as administrator).

4. OOM condition in sqlsever can also cause this error

5. Executing xp_readerrorlog from DAC connection can also cause this error.

TCP Provider: The semaphore timeout period has expired

TCP Provider: The semaphore timeout period has expired error from SQL Server agent and other applications at times.

1. Disable TCP Chimney.Refer KB:942861

2. If you are in windows 2003 Change the value of the processor affinity to match the number of processors in the system.Follow KB:892100

{
1.Click Start, click Run, type regedit, and then click OK.
2.Expand the following registry subkey:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\NDIS\Parameters
3.Right-click ProcessorAffinityMask, and then click Modify.
4.In the Value data box, type one of the following values, and then click OK:
◦If you have two processors, use the binary value 0b11, or hex value 0x3.
◦If you have three processors, use the binary value 0b111, or hex value 0x7.
◦If you have four processors, use the binary value 0b1111, or hex value 0xF.
5.Quit Registry Editor.
Note The 0x0 or 0xFFFFFFFF values are used to disable the ProcessorAffinityMask entry.
}

3. Check if priority boost is enabled for SQL Server. If yes disable it.

4. Make sure there is no working set trim and system wide memory pressure. You can use second query in significant part of sql server process memory has been paged out to identify and follow the same blog to fix it)

5. Check if paged pool and non-paged is empty. (Event ID:  2019  in event log)

6. If you see this problem in cluster make sure you have set the network priority of “private heart beat” network higher than the “public” network.Refer KB:258750

 

Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction.

An error occurred while recovering database. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction.

You might get below errors when you try to restore database from SAN snapshot and see a Orphan Msdtc transaction Spid -2

    Error

Msg 6110, Level 16, State 1, Line 1
The distributed transaction with UOW {00000000-0000-0000-0000-000000000000} does not exist.

An error occurred while recovering database Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (0:12345). Fix MS DTC, and run recovery again.

    Resolution

Change the sp_configure option
sp_configure ‘in-doubt xact resolution’,2
–Presume abort. Any MS DTC in-doubt transactions are presumed to have aborted.
Go
DBCC dbrecover(‘Dbname’);

Addition details on above sp_configure option in http://msdn.microsoft.com/en-us/library/ms179586(v=sql.90).aspx

Note: Use above option with caution

SQL Server integration services fails to start.

If SQL Server integration services fails to start. Create a backup of DTExec.exe.config and MsDtsSrvr.exe.config file  in   “C:\Program Files\Microsoft SQL Server\90\DTS\Binn” and add  <generatePublisherEvidence enabled=”false”/> under runtime tag and the start the SSIS service.

 

 

Here is an example DTExec.exe.config file for SSIS 2005:
<configuration>
<startup>
<requiredRuntime version=”v2.0.50727″/>
</startup>
<runtime>
<generatePublisherEvidence enabled=”false”/>
</runtime>
</configuration>

 

Here is an example msdtssrvr.exe.config file for SSIS 2005:
<?xml version=”1.0″ encoding=”utf-8″ ?>
<configuration>
  <runtime>
    <gcServer enabled=”true”/>
    <generatePublisherEvidence enabled=”false”/>
  </runtime>
  <system.diagnostics>
    <switches>
      <add name=”TraceClientConnections” value=”0″ />
      <add name=”TraceManagementCalls” value=”0″ />
      <add name=”ServerTraceSwitch” value=”0″ />
    </switches>
  </system.diagnostics>
</configuration>

More details about the above tab is mentioned in http://blogs.msdn.com/b/amolravande/archive/2008/07/20/startup-performance-disable-the-generatepublisherevidence-property.aspx

Query to find SQL Server CPU utilization 2012.

Query to find SQL Server CPU utilization 2012. This query will help you to find the CPU utilization of server and SQL. If like to know the steps to bring down the SQL Server CPU utilization follow http://mssqlwiki.com/2012/10/04/troubleshooting-sql-server-high-cpu-usage/

 	
	declare @ms_now bigint
	select @ms_now = ms_ticks from sys.dm_os_sys_info;
select top 15 record_id,
		dateadd(ms, -1 * (@ms_now - [timestamp]), GetDate()) as EventTime, 
		SQLProcessUtilization,
		SystemIdle,
		100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
	from (
		select 
			record.value('(./Record/@id)[1]', 'int') as record_id,
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
			timestamp
		from (
			select timestamp, convert(xml, record) as record 
			from sys.dm_os_ring_buffers 
			where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
			and record like '%<SystemHealth>%') as x
		) as y 
	order by record_id desc

How to view xml plan as graphical execution plan

Do you want to look XML execution plan as graphical execution plan?

It is simple save the xml plan with .sqlplan extension and then open with management studio.

Similarly to view graphical plan as XML execution plan. Save the file with .XML extension or rename the existing graphical plan with .xml extension

Top queries by total I/O and average I/O in SQL Server

You can use the below queries to identify the top queries by total I/O and average I/O in SQL Server

–Top queries by total I/O


exec sp_executesql @stmt=N'begin try
select top 10 rank() over (order by total_logical_reads+total_logical_writes desc,sql_handle,statement_start_offset ) as row_no
, (rank() over (order by total_logical_reads+total_logical_writes desc,sql_handle,statement_start_offset ))%2 as l1
, creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 as total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
, total_logical_reads as [LogicalReads]
, total_logical_writes as [LogicalWrites]
, execution_count
, total_logical_reads+total_logical_writes as [AggIO]
, (total_logical_reads+total_logical_writes)/(execution_count+0.0) as [AvgIO]
, case when sql_handle IS NULL
then '' ''
else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ) )
end as query_text
, db_name(st.dbid) as database_name
, st.objectid as object_id
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_logical_reads+total_logical_writes > 0
order by [AggIO] desc
end try
begin catch
select -100 AS row_no
, 1 AS l1, 1 AS creation_time, 1 AS last_execution_time, 1 AS total_worker_time, 1 AS Avg_CPU_Time, 1 AS logicalReads, 1 AS LogicalWrites
, ERROR_NUMBER() AS execution_count
, ERROR_SEVERITY() AS AggIO
, ERROR_STATE() AS AvgIO
, ERROR_MESSAGE() AS query_text
end catch',@params=N''

–Top queries by Average I/O


exec sp_executesql @stmt=N'begin try
select top 10 rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ) as row_no
, (rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ))%2 as l1
, creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 as total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
, total_logical_reads as [LogicalReads]
, total_logical_writes as [LogicalWrites]
, execution_count
, total_logical_reads+total_logical_writes as [AggIO]
, (total_logical_reads+total_logical_writes+0.0)/execution_count as [AvgIO]
, case when sql_handle IS NULL
then '' ''
else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ))
end as query_text
, db_name(st.dbid) as database_name
, st.objectid as object_id
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where (total_logical_reads+total_logical_writes ) > 0
order by [AvgIO] desc
end try
begin catch
select -100 AS row_no
, 1 AS l1, 1 AS creation_time, 1 AS last_execution_time, 1 AS total_worker_time, 1 AS AvgCPUTime, 1 AS logicalReads, 1 AS LogicalWrites
, ERROR_NUMBER() AS execution_count
, ERROR_SEVERITY() AS AggIO
, ERROR_STATE() AS AvgIO
, ERROR_MESSAGE() AS query_text
end catch',@params=N''

How to audit SQLServer logins?

We can audit SQL Server logins using multiple ways like running a profiler, Logon triggers and creating a audit.

 

Below script will create a audit to capture the login events. We can capture the login in event logs or file.

Below is example to capture login events in file.  Replace D:\Audit\ in below script to a valid folder

 

USE [master]
GO

CREATE SERVER AUDIT [TestAudit]
TO FILE 
(	FILEPATH = N'D:\CASEDATA\'
	,MAXSIZE = 0 MB
	,MAX_ROLLOVER_FILES = 2147483647
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = SHUTDOWN
	,AUDIT_GUID = 'b305ddf6-3487-4c77-afc2-fc8cfc357abc'
)
GO

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20130205-205300]
FOR SERVER AUDIT [TestAudit]
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = OFF)
GO

How to set alternate location for SQL Server dumps?

How to set alternate location for SQL Server dumps?
To change the default location for dump files, you can add the SQLExceptionDumpPath registry key with REG_SZ type under HKLM\Software\Microsoft\<instance path>\Setup  and set the value of this key to the new path. The default location for this is the SQL installation Path\log directory.
Above change does not require restart
HKLM\Software\Microsoft\<instance path>\Setup\MaxDumps    -DWORD to limit maximum number of dumps (0=Unlimited)
HKLM\Software\Microsoft\<instance path>\Setup\MaxFullDumps to limit maximum number of full dumps (0=Unlimited)

The merge process was unable to create a new generation at the ‘Subscriber’

The merge process was unable to create a new generation at the ‘Subscriber’

Merge replication fails with below error

Error:
The merge process was unable to create a new generation at the ‘Publisher’

Unable to decide if MakeGeneration is needed

The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting,restart the synchronization with verbose history logging and specify an output file to which to write.

 Verbose Error log:
The merge process was unable to create a new generation at the ‘Subscriber’. Troubleshoot by restarting the synchronization with verbose history logging and specify an output file to which to write.
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200994)Get help:
http://help/MSSQL_REPL-2147200994
Resolution:
Change the generation_leveling_threshold to 0 in sysmergepublications table in publisher and in subscriber. Do not update the system tables directly.
Follow the steps mentioned in workaround section of http://support.microsoft.com/kb/953568

Update,Insert or Delete on tables at subscriber fails in transactional replication

When you update/insert/delete rows in table in subscriber which is part of transactional replication with updateable subscriber we will end with below error
{
Msg 14126, Level 16, State 3, Procedure sp_check_sync_trigger, Line 28
You do not have the required permissions to complete the operation.

Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8
Updateable Subscriptions: Rolling back transaction.
Msg 3609, Level 16, State 1, Procedure trg_MSsync_upd_State, Line 133

The transaction ended in the trigger. The batch has been aborted.
}
Above error is raised in sp_check_sync_trigger and according to the below logic in “sp_check_sync_trigger”  
If you have a update/delete/insert trigger in a table which is not part of replication (MSreplication_objects) then we raise “You do not have the required permissions to complete the operation.”
{

if not exists (select so.object_id from (dbo.MSreplication_objects as ro join sys.objects as so 
        on ro.object_name = so.name) 
        where so.object_id = @trigger_procid and ro.object_type = ‘T’  
        and (@owner is null or schema_name(so.schema_id) = @owner)) 
    begin 
        raiserror(14126, 16, 3) 
        return 1 

   end
}
Resolution
Identify all the triggers of the table for which you are not able to update/delete/insert rows and identify the trigger which is not part of MSreplication_objects and disable it.
You can use the below query to identify triggers in table which is not part of replication

select * from sys.objects where type=‘TR’ and name not in (select object_name from  MSreplication_objects)
and parent_object_id in (select OBJECT_ID(‘Replace your Table here’))

       

SSMS fails with below error when you connect (or) explore the databases

SQL Server management studio might fail with below error  when you connect (or) explore the databases.
Error in SQL Server 2012
TITLE: Microsoft SQL Server Management Studio ——————————
Object reference not set to an instance of an object. (Microsoft.VisualStudio.Platform.WindowManagement)
——————————
Error in SQL Server 2008 and SQL Server 2005
BUTTONS:
Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘Microsoft.VisualStudio.OLE.Interop.IServiceProvider’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{6D5140C1-7436-11CE-8034-00AA006009FA}’ failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). (Microsoft.VisualStudio.OLE.Interop)

regsvr32 /u ieproxy.dll
regsvr32 ieproxy.dll

Cause
1. You would get above error if ieproxy.dll is not registered properly. Re register Ieproxy.dll.
C:\Program Files\Internet Explorer>regsvr32 /u ieproxy.dll
C:\Program Files\Internet Explorer>regsvr32 ieproxy.dll
C:\Program Files (x86)\Internet Explorer>regsvr32 /u ieproxy.dll
C:\Program Files (x86)\Internet Explorer>regsvr32 ieproxy.dll
2 .Net framework corruption (Repair Dot Net)
3. regsvr32 actxprxy.dll
4. If the problem persists after doing above steps then search the registry for class-id mentioned  in error message (in this error it is 6D5140C1-7436-11CE-8034-00AA006009FA). Once you find the class in registry explore ProxyStubClsid32 and copy the GUID in default key. Search for the copied GUID again in the registry and locate the DLL for which we are failing and register that Dll.

Linked server from SQLServer2012 to SQLServer2000.

How to create Linked server from SQLServer2012 to SQLServer2000.
When you create linked server from SQLServer2012 to SQLServer2000 you cannot use SQL Native client 11 because it cannot connect with  SQL Server 2000
Each version of SQL native client will support the version of SQL Server that it ships with, two earlier versions and two later versions
When you use SQLOLED it will switch to latest SQLNCLI.
The Microsoft SQL Server Native Client OLE DB provider is the provider that is used with SQL Server if no provider name is specified or if SQL Server is specified as the product name. Even if you specify the older provider name, SQLOLEDB, it will be changed to SQLNCLI when persisted to the catalog.
You create a data source and use MSDASQL (Microsoft OLE DB Provider for ODBC) as long as OS supports it.
Sample
sp_addlinkedserver ‘Server\Shiloh’, @provider = N’MSDASQL’, @datasrc=N’SQL2000datasource’, @location=N’System’,@srvproduct=N’MSDASQL’;
Alternatively you can use SQL Server native client 10.0 but it expects “begin distributed transaction” for all the queries.

How to detect low memory conditions in SQL Server using ring buffers output

Use the below query to determine the low memory conditions in SQL Server using the sys.dm_os_ring_buffers It gives the historical memory usage of SQL Server and internal and external memory pressure information .


SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as [Notification_Time],
cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type],
cast(record as xml).value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %],
cast(record as xml).value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [Process_Indicator],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [System_Indicator],
cast(record as xml).value('(//Record/ResourceMonitor/Effect/@type)[1]', 'varchar(30)') AS [type],
cast(record as xml).value('(//Record/ResourceMonitor/Effect/@state)[1]', 'varchar(30)') AS [state],
cast(record as xml).value('(//Record/ResourceMonitor/Effect/@reversed)[1]', 'int') AS [reserved],
cast(record as xml).value('(//Record/ResourceMonitor/Effect)[1]', 'bigint') AS [Effect],

cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@type)[1]', 'varchar(30)') AS [type],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@state)[1]', 'varchar(30)') AS [state],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@reversed)[1]', 'int') AS [reserved],
cast(record as xml).value('(//Record/ResourceMonitor/Effect)[2]', 'bigint') AS [Effect],

cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@type)[1]', 'varchar(30)') AS [type],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@state)[1]', 'varchar(30)') AS [state],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@reversed)[1]', 'int') AS [reserved],
cast(record as xml).value('(//Record/ResourceMonitor/Effect)[3]', 'bigint') AS [Effect],

cast(record as xml).value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint') AS [SQL_AWEMemory],
cast(record as xml).value('(//Record/MemoryNode/SinglePagesMemory)[1]', 'bigint') AS [SinglePagesMemory],
cast(record as xml).value('(//Record/MemoryNode/MultiplePagesMemory)[1]', 'bigint') AS [MultiplePagesMemory],
cast(record as xml).value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
tme.ms_ticks as [Current Time]
FROM sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' --and cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') = 'RESOURCE_MEMPHYSICAL_LOW'
ORDER BY rbf.timestamp ASC
Go

Once you get the output of above query you can use the steps in A significant part of SQL Server process memory has been paged out to troubleshoot the issue further

How to bring the database online when one or more data files is accidentally deleted

How to bring the database online when one or more data files is accidentally deleted.
 
Follow the steps below when you don’t have other options like restoring from backup. This has to be the last step to bring the database online and remember data in file which is deleted will be lost. This might cause inconsistent data.
 
1. Take physical file level backup of all the database files
2) Rename all the data files and log files [Ex :N:\ Data.MDF  to N:\Data.MDF_original ]
3) Create a new empty database with the same physical, logical file names and file groups
4) Now take the missing file OFFLINE
5) Take the database offline and replace the original files and try to bring the database online
 
The database will come online, only the missing file will be in OFFLINE STATE and will not be accessible.
If the missing file belong to separate file group we can remove the file group else you may have to export all the data from this database to new database else we cannot take FULL Database backup as one of the database file is not online for file group.
 
 
 
 
— Create New database
USE [master]
GO
 
CREATE DATABASE [MisTest] ON  PRIMARY
( NAME = N’MisTest’, FILENAME = N’C:\MisTest.mdf’ ,   MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 FILEGROUP [Secondary]
( NAME = N’MisTestNDF’, FILENAME = N’C:\MisTestNDF.ndf’ ,   MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’MisTest_log’, FILENAME = N’C:\MisTest_log.LDF’ ,   MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
 
–Take the database offline
Alter database MisTest set offline
go
 
–Now rename the NDF file [to simulate the missing file scenario]
sp_configure ‘show advanced options’,1
go
reconfigure with override
go
 
sp_configure ‘xp_cmdshell’,1
go
reconfigure with override
go
 
xp_cmdshell ‘rename c:\MisTestNDF.ndf MisTestNDF_1.ndf’
go
 
 
–Try to bring the database online but it fails
Alter database MisTest set online
go
 
Msg 5120, Level 16, State 5, Line 1
Unable to open the physical file “C:\MisTestNDF.ndf”. Operating system error 2: “2(failed to retrieve text for this error. Reason: 15105)”.
Msg 945, Level 14, State 2, Line 1
Database ‘MisTest’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
 
 
–Take the database offline and detach
Alter database MisTest set offline
go
USE [master]
GO
ALTER DATABASE [MisTest] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db@dbname = N’MisTest’
GO
 
–Now try to attach the database with missing file, but it fails with the below error
USE [master]
GO
CREATE DATABASE [MisTest] ON
( FILENAME = N’C:\MisTest.mdf’ ),
( FILENAME = N’C:\MisTest_log.LDF’ )
,( FILENAME = N’C:\MisTestNDF.ndf’ )
FOR ATTACH
GO
 
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “C:\MisTestNDF.ndf”. Operating system error 2: “2(failed to retrieve text for this error. Reason: 15105)”.
 
–Rename the original files
xp_cmdshell ‘rename c:\MisTest.mdf MisTest_1.mdf’
go
xp_cmdshell ‘rename c:\MisTest_log.ldf MisTest_1_log.ldf’
go
 
–Now recreate the Empty database with the same physical & logical file name
CREATE DATABASE [MisTest] ON  PRIMARY
( NAME = N’MisTest’, FILENAME = N’C:\MisTest.mdf’  , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 FILEGROUP [Secondary]
( NAME = N’MisTestNDF’, FILENAME = N’C:\MisTestNDF.ndf’  , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’MisTest_log’, FILENAME = N’C:\MisTest_log.LDF’ , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
 
 
–Mark the missing file as offline
Alter database MisTest
MODIFY FILE (NAME = ‘MisTestNDF’,OFFLINE)
 
–Take the database offline
Alter database MisTest set offline
 
— Replace the original file and bring the database online
xp_cmdshell ‘rename c:\MisTest.mdf MisTest_2.mdf’
go
xp_cmdshell ‘rename c:\MisTest_log.ldf MisTest_2_log.ldf’
go
 
xp_cmdshell ‘rename c:\MisTest_1.mdf MisTest.mdf’
go
xp_cmdshell ‘rename c:\MisTest_1_log.ldf MisTest_log.ldf’
go
Alter database MisTest set online
go
 
— The only problem is that we cannot take the backup of the database as the file is offline
 
backup database Mistest to disk = ‘c:\mistest.bak’
 
Msg 3636, Level 16, State 2, Line 1
An error occurred while processing ‘BackupMetadata’ metadata for database id 8 file id 3.
Msg 3046, Level 16, State 2, Line 1
Inconsistent metadata has been encountered. The only possible backup operation is a taillog backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
 
–We cannot even remove the file as we get this error
 
USE [MisTest]
GO
ALTER DATABASE [MisTest]  REMOVE FILE [MisTestNDF]
GO
 
Msg 5056, Level 16, State 2, Line 1
Cannot add, remove, or modify a file in filegroup ‘Secondary’ because the filegroup is not online.
 
–Now try to remove the file & file group
 
 
–Try the database back up again but it would fail
 
backup database Mistest to disk = ‘c:\mistest.bak’
go
 
Msg 3636, Level 16, State 2, Line 2
An error occurred while processing ‘BackupMetadata’ metadata for database id 8 file id 3.
Msg 3046, Level 16, State 2, Line 2
Inconsistent metadata has been encountered. The only possible backup operation is a taillog backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option.
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.
 
 
 
USE [MisTest]
GO
ALTER DATABASE [MisTest]  REMOVE FILE [MisTestNDF]
GO
 
Msg 5056, Level 16, State 2, Line 1
Cannot add, remove, or modify a file in filegroup ‘Secondary’ because the filegroup is not online.
Msg 5042, Level 16, State 7, Line 1
The filegroup ‘Secondary’ cannot be removed because it is not empty.
 
Ignore the above error and proceed further.
 
ALTER DATABASE [MisTest] REMOVE FILEGROUP [Secondary]
GO
 
Now we can take full database backup normally  
 
 

 

System.OutOfMemoryException’ was thrown. (Microsoft.SqlServer.Management.Sdk.Sfc) error when we try to restore the database

When we try to restore database in SQL Server we get below error and restore database fails

Error

{
——————————
TITLE: Microsoft SQL Server Management Studio
——————————
Cannot show requested dialog.
——————————
ADDITIONAL INFORMATION:
Exception of type ‘System.OutOfMemoryException’ was thrown. (Microsoft.SqlServer.Management.Sdk.Sfc)
——————————
BUTTONS:
OK
——————————
}

Cause
We get above error when we have large number of records on backup history table.
Check the count of rows on below table

select count(1) from msdb.dbo.backupfile   with (nolock)
select count(1) from msdb.dbo.backupfilegroup  with (nolock)
select count(1) from msdb.dbo.restorefile  with (nolock)
select count(1) from msdb.dbo.restorefilegroup with (nolock)
select count(1) from msdb.dbo.restorehistory  with (nolock)
select count(1) from msdb.dbo.backupmediafamily with (nolock)

Resolution
We may have to reduce the number of records in the above tables. We can manually delete records based on date (or) If we do not want backup history we can truncate all the above tables.

 

How to configure the size of SQLServer error log

When running SQL Server certain times we may have to limit the size and number of SQL server errorlogs

To limit the size of SQL Server errorlogs add a new registry key GetMaxErrorLogSizeKb under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQL.X\MSSQLServer\ErrorLogSizeInKb
By default, this key is absent. Modify the value to the size of errorlog(In Kb) you want to maintain.

To increase the number of log files, add a new registry key “NumErrorLogs” (REG_DWORD) under below location.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQL.X\MSSQLServer\
By default, this key is absent. Modify the value to the number of logs that you want to maintain.

To learn more about sp_readerrorlog and its parameters read Beyond XP_READERRORLOG (Parameters of XP_READERRORLOG)

Remote query using a linked server generated memory dump and fails

When you fire a remote query using a linked server it may generate a memory dump and fail with error below
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Resolution
1. Apply the fix mentioned in http://support.microsoft.com/default.aspx?scid=kb;EN-US;939965 if you are using OLEDB provider for DB2.
2. Few RDBMS allow index to contain column name twice. Dump the indexes of table which you are querying and check if any index has duplicate column name If you find a duplicate column name remove it. Linked server query will start working.

Lock pages in memory is recommended or not

One of SQL Server expert recently asked  below question in distribution list 
1. LPIM is not recommended?
2. LPIM is not required?
Answer:
When we have LPIM privilege for SQL Server startup account  AWE allocator API’s are used for memory allocation. Memory allocated using  AWE allocator API’s are not part of Process working set ,hence cannot be paged out and not visible in private bytes or working set in task manger and Perfmon àPrivate bytes or Perfmon àworking set.
Advantage: SQL Server working set (BPOOL) cannot be paged by windows  even when there is system wide memory pressure.
Disadvantage: Operating system will starve for memory when there is system wide memory pressure. OS has to completely rely on SQL Server to respond to low memory notification and scale down its memory usage . SQL Server may not respond fast enough to low memory condition at system level because OS is already starving for memory. LPIM prevents only the BPOOL from paging, Non-Bpool components can still be paged and we have critical portions of SQL Server memory like thread stack, SQL Server Images/DLL’s  in Non-Bpool which can still be paged by OS.
So many disadvantage…. But still why do we recommend LPIM in some places?
In earlier versions of windows 2003 when there is system wide memory pressure windows memory manger would trim one-quarter of working set of all the process. Imagine If SQL Server is using 200GB of RAM and there is system wide memory pressure, Windows memory manager would move 50 GB of
SQL Server working set to page file and we would end with performance problems. If LPIM is enabled OS cannot trim.
Imagine there is a faulty application in the server and it leaks memory  fast , It might consume all the memory in the server and windows memory manager might trim all of SQL Server working set.
Known issues in windows like the one in This link can cause windows memory manager to trim the working set of SQL Server process suddenly. Windows has a background process which keeps  writing the contents of working set to page file, so when there is paging only the dirty pages  needs to be moved to the page file others are already backed by back ground process, So paging would be very fast and SQL Server working set would be moved to page file in seconds before SQL Server responds to low memory resource notification from OS causing negative performance.  This link might throw more clarity.

In systems with large amout of memory (Ex: 1 TB )we might get non yielding scheduler situations when allocating memory in conventional memory model. LPIM is only option is this case.
 
LPIM can be used in servers in which it might take long time to identify the cause of the working set trim. It is always suggested to identify the cause of TRIM before choosing LPIM in first place.

When statistics was updated?

Statistics are used by the SQL Server optimizer to choose the efficient plan.  When we don’t have up to date statistics
it may end with SQL server optimizer choosing inefficient query plan. We can use below query to identify when SQL Server
statistics were last updated.

select db_id() as dbid,
  case
    when indid IN (0, 1) then convert (char (12), rows)
    else (select rows from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1)) -- ''-''
  end as rowcnt,
  case
    when indid IN (0, 1) then rowmodctr
    else convert (bigint, rowmodctr) + (select rowmodctr from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))
  end as row_mods,
  case rows when 0 then 0 else convert (bigint,
    case
      when indid IN (0, 1) then convert (bigint, rowmodctr)
      else rowmodctr + (select convert (bigint, rowmodctr) from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))
    end / convert (numeric (20,2), (select case convert (bigint, rows) when 0 then 0.01 else rows end from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))) * 100)
  end as pct_mod,
  convert (nvarchar, u.name + '.' + o.name) as objname,
  case when i.status&0x800040=0x800040 then 'AUTOSTATS'
    when i.status&0x40=0x40 and i.status&0x800000=0 then 'STATS'
    else 'INDEX' end as type,
  convert (nvarchar, i.name) as idxname, i.indid,
  stats_date (o.id, i.indid) as stats_updated,
  case i.status & 0x1000000 when 0 then 'no' else '*YES*' end as norecompute,
  o.id as objid , rowcnt, i.status
from dbo.sysobjects o, dbo.sysindexes i, dbo.sysusers u
where o.id = i.id and o.uid = u.uid and i.indid between 1 and 254 and o.type = 'U'
order by pct_mod desc, convert (nvarchar, u.name + '.' + o.name), indid
GO