MSSQLWIKI

Karthick P.K on SQL Server

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

Posted by Karthick P.K on January 9, 2012

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

Linked server connection fails with below error

{

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

Msg 15593, Level 16, State 1, Line 1

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

}

Database mail might fail with below error

{

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

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

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

}

Or

You notice below errors in SQL Server errorlogs

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

Cause

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

{

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

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

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

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

}

 

Resolution

Regenerate the service master key using ALTER SERVICE MASTER KEY REGENERATE

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

{

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

}

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

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

 

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

Thank you,

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

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

SQL Saturday #116 : Don’t miss it

Posted by Karthick P.K on January 5, 2012

SQL Server PASS and Microsoft is organizing first SQL Saturday event in Bangalore on January 7th 2012.

I will be part of SQL Server clinic which is between 2:30 PM to 5:30 PM. SQL Server clinic is event in which MSFT CSS engineers having deep

technical knowledge is available to give result set for any technical queries you fire. Be open to bring any SQL Server related problems you are

facing and get immediate assistance from SQL Server experts.

click here to get event location

 

Thanks

Karthick P.K

Posted in SQL Saturday | Tagged: | 2 Comments »

How to Create process in c++….. CreateProcess function

Posted by Karthick P.K on December 31, 2011

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

void main()
{
    int N;    
    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);
        }

}
 
 
 
Thanks
Karthick P.K

Posted in Programming | Tagged: , | 2 Comments »

My “c:\” Drive gets full when I open the profiler trace

Posted by Karthick P.K on July 16, 2011

Profiler filling up space of my system drive…….

When you run the profiler trace (GUI) or open the saved profiler trace file your System Drive gets out of space

SQL Server profile uses ‘TMP’ Environment variable for storing the .Trc and trace replay files

$29BC399D487DBB17 

By default TMP directory points to your system drive, so when we open a large profiler trace file or capture trace in GUI mode system drive gets full.

We can workaround this by changing the TMP environment variable. Changing TMP environment variable system wide can cause other issues so I would recommend changing TMP temporarily for the process

1. Open command prompt

SET TMP=G:\TMP

$130609A642FEC889

2. Open the profiler from command prompt

>Profiler90.exe         –>SQL Server2005

>Profiler.exe             –>SQL Server2008

>Mysavedtracefile.trc –>Open a saved tracefile

$4C3BC270A299D0A3

This would make profiler use G:\TMP  for saving temporary data. So your c:\ would never get full.

Posted in SQL Server Tools | Tagged: , , , , , , | 5 Comments »

System stored procedures like sp_addsrvrolemember or sp_addserver may fail because of McAfee Host Intrusion Prevention

Posted by Karthick P.K on June 26, 2011

We might get Incorrect syntax near while applying the snapshot or stored procedure like sp_addsrvrolemember or sp_addserver might fails when we have Host Intrusion Prevention antivirus.

 

Last week two DBA’s came to me with two different errors after breaking their head for hours….

Error 1:

When i Run “EXEC sp_addsrvrolemember  ‘VC’, ‘sysadmin’”        I get

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken

Error 2:

I get Access Violation when i Install SQL Server 2008 and here is error in errolog

*   Exception Address = 7814500A Module(MSVCR80+0001500A)

  *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION

  *   Access Violation occurred writing address 43D3FFFC

  *   Input Buffer 428 bytes –

    declare @ServerName nvarchar(255) if not exists (select * fro

*  m sysservers) begin select @ServerName = Convert(nvarchar(255), SERVERPR

*  OPERTY(N’ServerName’)) execute sys.sp_addserver @ServerName, local end

  *             declare @ServerName nvarchar(255) if not exists (select * fro

  *  m sysservers) begin select @ServerName = Convert(nvarchar(255), SERVERPR

  *  OPERTY(N’ServerName’)) execute sys.sp_addserver @ServerName, local

 

 

Error is raised while executing sp_addsrvrolemember  or sp_addserver  or while applying the initial snapshot for database replication. I collected memory dump from both the systems and interestingly there was same 3rd party Dll’s in SQL Server address space of both systems. Its  McAfee Host Intrusion Prevention. Disabled this and things started working.

https://kc.mcafee.com/corporate/index?page=content&id=KB65845

 

Thank you,

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

Disclaimer:

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

Posted in Configuration, Security, SQL Server Engine | Tagged: , , , | 4 Comments »

how to Open CreateFile (Createfile example)

Posted by Karthick P.K on March 10, 2011

#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,FILE_SHARE_READ,0x00000001,NULL,OPEN_EXISTING,FILE_ATTRIBUTE_NORMAL,NULL);
    cout<<filename;
    if (h!=INVALID_HANDLE_VALUE)
    {
    printf("File is opened");
    }
    else
    {
    printf("Unable to open or create file");
    }
    system ("pause");

}

Posted in Programming | Tagged: | 3 Comments »

Beyond XP_READERRORLOG (Parameters of XP_READERRORLOG)

Posted by Karthick P.K on February 13, 2011

Parameters which would be useful when you run xp_readerrorlog or sp_readerrorlog

XP_READERRORLOG

 

Syntax:

xp_readerrorlog -> Reads the current SQL Server errorlog

xp_readerrorlog 3 -> Reads SQL Server errorlog “errorlog.3”

xp_readerrorlog -1, 2 -> Reads the current SQL Agent errorlog

xp_readerrorlog 3, 2 -> Reads the SQL Agent errorlog No 3 archive

xp_readerrorlog -1, 2, “ab” -> Reads the current SQL Agent errorlog but returns only those lines that contain the string “ab”

xp_readerrorlog 1, 2, “ab”, “cd” -> Reads the SQLAgent errorlog No 1 archive but returns only those lines that contain the strings
“ab” AND “cd” lines that contain the strings “ab” AND “cd”

xp_readerrorlog [-1 – n], 3, database_id, fulltext_catalog_id -> Reads full
text crawl log

Note:The second INT param is LogType. 1 = SQLServer logs, 2 = SQLAgent logs, 3 =
full text logs(SQLServer log is the default)
Additionally, the last 3 parameters (parameters 5, 6, 7) are used for Date/Time
filtering and ordering

xp_readerrorlog -1, 1, NULL, NULL, ‘2004-09-01’, ‘2004-09-02’, [‘asc’ | ‘desc’]
-> Reads the current SQL Server error log entries starting from midnight of 2004-09-01 until midnight of 2004-09-02.
Time can be included too. The date-time can be in any format recognizable by SQL server.
The last parameter specifies the datetime order that the entries are returned in. ‘asc’ or no
parameter is used for the normal, ascending, order. ‘desc’ is used to return entries in reverse order,
newest entries first.

Note:Also, 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.

 

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

Thank you,

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

Posted in SQL Query, SQL Server Tools | Tagged: , , | 5 Comments »

A failure was detected for a previous installation, patch, or repair during configuration for features [SQL_PowerShell_Engine_CNS,SQL_PowerShell_Tools_ANS]. In order to apply this patch package (KB968369), you must resolve any issues with the previous operation that failed.

Posted by Karthick P.K on January 12, 2011

Installation of SQL Service Pack 1 (or) Cumulative Pack for SQL Server 2008 it fails with the below error message

A failure was detected for a previous installation, patch, or repair during configuration for features [SQL_PowerShell_Engine_CNS,SQL_PowerShell_Tools_ANS,XXXX,XXXXX]. In order to apply this patch package (KB968369 XYZ), you must resolve any issues with the previous operation that failed.

 

 

Look at the Sub-keys for below registry keys and check if Value ‘2’ is set for any components.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\ConfigurationState

 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS10.XY1\ConfigurationState

Replace MSAS10.XY1 with your instance

 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.KJ\ConfigurationState

Replace MSSQL10_50.KJ with your instance

 

Run the Repair for existing setup. Values for all the sub keys will change to 1 after the successful repair.

Re-start the Service pack installation.

 

Thanks

Karthick P.K

Posted in SQL Cluster Setup, SQL Server Setup | Tagged: , , , , , | 3 Comments »

How to get the current state of cluster resource?

Posted by Karthick P.K on December 9, 2010

#include <windows.h> 
#include <ClusApi.h>
#include <winbase.h> 
#include <iostream> 
using namespace std;
#pragma comment(lib,"ClusApi.lib")
#include <stdlib.h>

void main()
{
    HCLUSTER Clusterhandle=NULL; 
    HRESOURCE  ClusterRhandle=NULL; 
    LPCWSTR lpszClusterName =NULL;
    WCHAR *lpszResourceName;
    CLUSTER_RESOURCE_STATE A = ClusterResourceStateUnknown;
    lpszResourceName =new WCHAR;
    wcout<<"enter the name of cluster resource:";
    wcin.getline (lpszResourceName,256);
    Clusterhandle= OpenCluster(lpszClusterName);
    if (Clusterhandle!=NULL)
    {
    
        ClusterRhandle=OpenClusterResource(Clusterhandle,lpszResourceName);
        if (ClusterRhandle!=NULL)
        {
         printf("Got Cluster resource handle\n");
            
         A= GetClusterResourceState(ClusterRhandle,NULL,NULL,NULL,NULL);

         if (A!=ClusterResourceStateUnknown)
             {
             printf("ClusterResourceState is:%d",A);
         
              }
              else
              {
              printf("ClusterResourceState is unknowm",GetLastError());
              
              }
              
        }
        else
        {
        printf("Unable to get ClusterRhandle:%d",GetLastError());
        }
    }
    else 
    {
    printf ("unable to get handle to cluster",GetLastError());
    }
 
}

Posted in Programming, SQL Server Cluster | Tagged: , , | 1 Comment »

Script to free cache

Posted by Karthick P.K on December 6, 2010

 

DBCC FREESYSTEMCACHE ( 'ALL' ) WITH MARK_IN_USE_FOR_REMOVAL

GO

DBCC FREESESSIONCACHE WITH NO_INFOMSGS

GO

DBCC FREEPROCCACHE WITH NO_INFOMSGS

GO

DBCC DROPCLEANBUFFERS

GO

Posted in Performance, SQL General, SQL Query | Tagged: , , , , , | 59 Comments »

QueryMemoryResourceNotification & CreateMemoryResourceNotification (How SQL Server identifies low system memory on the system and respond to low system memory?)

Posted by Karthick P.K on December 2, 2010

#include <windows.h> 
#include <string> 
#include <winbase.h> 
#include <iostream> 
using namespace std;
#include <psapi.h>
#pragma comment(lib,"psapi.lib")
#include <time.h>

DWORD dwLength;
DWORD dwMemoryLoad;
ULONG_PTR dwTotalPhys;
ULONG_PTR dwAvailPhys;
ULONG_PTR dwTotalPageFile;
ULONG_PTR dwAvailPageFile;
ULONG_PTR dwTotalVirtual;
ULONG_PTR dwAvailVirtual;
int *m_pBuf; 
MEMORY_RESOURCE_NOTIFICATION_TYPE Low;
MEMORY_RESOURCE_NOTIFICATION_TYPE High;
HANDLE LMHandle;
HANDLE HMHandle;
HANDLE THandle;
int ResourceState;
int x=0;
BOOL state=1;
char dateStr [9];
char timeStr [9];


BOOL SetPrivilege(
    HANDLE hToken,          // access token handle
    LPCTSTR lpszPrivilege,  // name of privilege to enable/disable
    BOOL bEnablePrivilege   // to enable or disable privilege
    ) 
{
    TOKEN_PRIVILEGES tp;
    LUID luid;

    if ( !LookupPrivilegeValue( 
        NULL,            // lookup privilege on local system
        lpszPrivilege,   // privilege to lookup 
        &luid ) )        // receives LUID of privilege
    {
        printf("LookupPrivilegeValue error: %u\n", GetLastError() ); 
        return FALSE; 
    }

    tp.PrivilegeCount = 1;
    tp.Privileges[0].Luid = luid;
    if (bEnablePrivilege)
        tp.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;
    else
        tp.Privileges[0].Attributes = 0;

    // Enable the privilege or disable all privileges.

    if ( !AdjustTokenPrivileges(
        hToken, 
        FALSE, 
        &tp, 
        sizeof(TOKEN_PRIVILEGES), 
        (PTOKEN_PRIVILEGES) NULL, 
        (PDWORD) NULL) )
    { 
        printf("AdjustTokenPrivileges error: %u\n", GetLastError() ); 
        return FALSE; 
    } 

    if (GetLastError() == ERROR_NOT_ALL_ASSIGNED)

    {
        printf("The token does not have the specified privilege. \n");
        return FALSE;
    } 

    return TRUE;
}



void processmemory()

{

    FILE * pFile;
    pFile = fopen ("MemoryStatus.txt","a");
    DWORD     PID[1024];
    DWORD pBytesReturned=NULL;
    BOOL S;
    //PID= new DWORD(SIZEOF(pBytesReturned));

    S= EnumProcesses(PID,sizeof(PID), &pBytesReturned);
    BOOL x;
    HANDLE TokenHandle;
    TokenHandle=NULL;
    fprintf(pFile,"Low Memory Notification received on  %s  %s  ", dateStr,timeStr);
    x= OpenProcessToken(GetCurrentProcess(),TOKEN_ADJUST_PRIVILEGES | TOKEN_QUERY,&TokenHandle);

    if (x==0)
    {

        fprintf (pFile,"Unable To OpenProcessToken For current Process Error:%d",GetLastError());

    }


    if(SetPrivilege(TokenHandle, SE_DEBUG_NAME, TRUE))
    {
        fprintf (pFile,"Success");
    }
    else
    {
        fprintf (pFile,"FAILURE");
    }            



    for(int i=0;i<=pBytesReturned/sizeof(DWORD);i++)
    {
        HANDLE H;    
        fprintf (pFile,"\nProcess:%d",PID[i]);

        H= OpenProcess(PROCESS_QUERY_INFORMATION |PROCESS_VM_READ |PROCESS_ALL_ACCESS,TRUE,PID[i]);


        if (H==0)
        {
            fprintf (pFile,"Unable To Get Process Name ");
            fprintf (pFile,"Error:%d",GetLastError());
        }
        else

        {
            char   Basename[MAX_PATH];

            BOOL A=0;

            A=GetModuleBaseName(H,NULL,(LPSTR) Basename,sizeof(Basename)/sizeof(TCHAR));
            if (A==0)
            {
                fprintf (pFile,"Error:%d",GetLastError());
                fprintf (pFile,"Unable To Get ModuleBaseName ");
            }
            else
            {
                fprintf (pFile,",Process Name:%s",Basename);
            }

            PROCESS_MEMORY_COUNTERS PMC;

            GetProcessMemoryInfo(H, &PMC,sizeof(PMC));


            fprintf (pFile,",PageFaultCount:%d",PMC.PageFaultCount);
            fprintf (pFile,",PeakWorkingSetSize:%d",PMC.PeakWorkingSetSize);
            fprintf (pFile,",WorkingSetSize:%d",PMC.WorkingSetSize);
            fprintf (pFile,",QuotaPeakPagedPoolUsage:%d",PMC.QuotaPeakPagedPoolUsage);
            fprintf (pFile,",QuotaPagedPoolUsage:%d",PMC.QuotaPagedPoolUsage);
            fprintf (pFile,",QuotaPeakNonPagedPoolUsage:%d",PMC.QuotaPeakNonPagedPoolUsage);
            fprintf (pFile,",QuotaNonPagedPoolUsage:%d",PMC.QuotaNonPagedPoolUsage);
            fprintf (pFile,",PagefileUsage:%d",PMC.PagefileUsage);
            fprintf (pFile,",PeakPagefileUsage:%d",PMC.PeakPagefileUsage);

        }





    }


    PERFORMANCE_INFORMATION Perfinfo;
    GetPerformanceInfo(&Perfinfo,sizeof(Perfinfo));
    SIZE_T CommitTotal=Perfinfo.CommitTotal;

    fprintf (pFile,"\n\n\nSYSTEM PERFORMANCE INFORMATION");
    fprintf (pFile,"\nCommitTotal=%d",Perfinfo.CommitTotal);
    fprintf (pFile,"\nCommitLimit=%d",Perfinfo.CommitLimit);
    fprintf (pFile,"\nCommitPeak=%d",Perfinfo.CommitPeak);
    fprintf (pFile,"\nPhysicalTotal=%d",Perfinfo.PhysicalTotal);
    fprintf (pFile,"\nPhysicalAvailable=%d",Perfinfo.PhysicalAvailable);
    fprintf (pFile,"\nSystemCache=%d",Perfinfo.SystemCache);
    fprintf (pFile,"\nKernelTotal=%d",Perfinfo.KernelTotal);
    fprintf (pFile,"\nKernelPaged=%d",Perfinfo.KernelPaged);
    fprintf (pFile,"\nKernelNonpaged=%d",Perfinfo.KernelNonpaged);
    fprintf (pFile,"\nPageSize=%d",Perfinfo.PageSize);
    fprintf (pFile,"\nHandleCount=%d",Perfinfo.HandleCount);
    fprintf (pFile,"\nProcessCount=%d",Perfinfo.ProcessCount);
    fprintf (pFile,"\nThreadCount=%d",Perfinfo.ThreadCount);




    OSVERSIONINFOEX   OSINFO;

    OSINFO.dwOSVersionInfoSize = sizeof(OSVERSIONINFOEX);
    GetVersionEx((LPOSVERSIONINFOA) &OSINFO);
    fprintf (pFile,"\n\n\nWINDOWS VERSION INFO");
    fprintf (pFile,"\ndwMajorVersion:%d",OSINFO.dwMajorVersion);
    fprintf (pFile,"\ndwMinorVersion:%d",OSINFO.dwMinorVersion);
    fprintf (pFile,"\ndwBuildNumber:%d",OSINFO.dwBuildNumber);
    fprintf (pFile,"\ndwPlatformId:%d",OSINFO.dwPlatformId);
    // fprintf (pFile,"\nszCSDVersion[128]:%s",OSINFO.szCSDVersion[128]);
    fprintf (pFile,"\nwServicePackMajor:%d",OSINFO.wServicePackMajor);
    fprintf (pFile,"\nwServicePackMinor:%d",OSINFO.wServicePackMinor);
    fprintf (pFile,"\nwSuiteMask:%d",OSINFO.wSuiteMask);
    fprintf (pFile,"\nwProductType:%d",OSINFO.wProductType);
    fprintf (pFile,"\nwReserved:%d",OSINFO.wReserved);

    fclose (pFile);

}


DWORD Lowmemorynotification()    
{
    LMHandle = CreateMemoryResourceNotification(Low);
    HMHandle = CreateMemoryResourceNotification(High);
    state=QueryMemoryResourceNotification(LMHandle, &ResourceState);
    if (state==1)
    {
        printf("QueryMemoryResourceNotification Created for Low Memory pressure");
        printf("\nYou will be signaled when there is low MemoryResourceNotification");
    }
loop:

    x=x+1;
    WaitForSingleObject( LMHandle,INFINITE);
    _strdate( dateStr);
    _strtime( timeStr );
    printf("%d",x);
    printf("Low Memory Notification received on  %s  %s  ", dateStr,timeStr);
    processmemory();
    printf("  Memory status is printed to Memorystatus.txt \n");
    Sleep(3000); 
    goto loop; 

    return 1;
};


void main()

{
    THandle=CreateThread( NULL, 8388608,(LPTHREAD_START_ROUTINE)Lowmemorynotification,NULL,0,NULL);

    //8388608=>Stack size in bytes which is 1 mb
    //    Lowmemorynotification();


    if (THandle==NULL)
    {
        printf("Create thread failed",GetLastError());
    }

    else
    {
        printf("\nSuccess");
        WaitForSingleObject(THandle,INFINITE);
    }

}

Thanks

Karthick P.K

Posted in Memory, Programming, SQL Server Engine | Tagged: , , , , , | 3 Comments »

How to find SQL Server and system CPU usage history :

Posted by Karthick P.K on November 30, 2010

SQL Server and system CPU usage history can be obtained from sys.dm_os_ring_buffers  using below query

Note: For troubleshooting  high CPU usage in SQL Server follow https://mssqlwiki.com/2012/10/04/troubleshooting-sql-server-high-cpu-usage/

SQL Server 2005

DECLARE @ts_now bigint

SELECT @ts_now = cpu_ticks / CONVERT (float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info

SELECT top 20 record_id, EventTime, 

  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization, 

  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization

FROM 

(

  SELECT 

    record.value('(Record/@id)[1]', 'int') AS record_id,

    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,

    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,

    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 , 

    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,

    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2

  FROM (

    SELECT timestamp, CONVERT (xml, record) AS record 

    FROM sys.dm_os_ring_buffers 

    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'

      AND record LIKE '%<SystemHealth>%') AS t

) AS t

ORDER BY record_id desc

 

SQL Server 2008

DECLARE @ts_now bigint

SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info

SELECT top 20 record_id, EventTime, 

  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization, 

  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization

FROM 

(

  SELECT 

    record.value('(Record/@id)[1]', 'int') AS record_id,

    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,

    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,

    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 , 

    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,

    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2

  FROM (

    SELECT timestamp, CONVERT (xml, record) AS record 

    FROM sys.dm_os_ring_buffers 

    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'

      AND record LIKE '%<SystemHealth>%') AS t

) AS t

ORDER BY record_id desc

Posted in Performance, SQL Query | Tagged: , , , , , | 16 Comments »

The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run.Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive

Posted by Karthick P.K on November 25, 2010

The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run.Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive

Error:

The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run.

Error: 927, Severity: 14, State: 2.

Database ‘model’ cannot be opened. It is in the middle of a restore.

Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

 

Cause

We get above error if the Model database is corrupted. TempDB is recreated every time when SQLServer restarts using model database, We receive "Could not create tempdb" because model database is corrupted

Resolution

1. Start SQLServer with Traceflag 3608,3609,-c,-f

-T3609 Will keep the existing TEMPDB, Which means when SQLServer is restarted SQLServer uses the existing tempdb instead of re-creating it as long as checkpoint in the tempdb had been done immediately before the last server shutdown

{

Sqlservr.exe -sInstanceName -T3608 -c -f -T3609

}

2. Open SQLCMD and make DAC connection

{

SQLCMD -E -SADMIN:Servername\InstanceName

}

3. Use Tempdb

Go

{

Above command will open the TempDB. If you get error while executing "Use Tempdb" your tempdb is not cleanly shutdown.

To work around this copy a Tempdb.mdf and templog.ldf from Cleanly shutdown SQLServer of same version and replace it in TEMPDB location.

Also note Transaction log location is stored in Tempdb.mdf.So you may have to copy Tlog file in destination server in same directory structure as it existed source server .

Once you copy the file restart SQLServer using -T3608 -c -f -T3609 Then run

Use Tempdb

Go

}

4. sp_detach_db ‘model

5. Replace model.mdf and model.ldf from different server of same build

5. sp_attach_db ‘model’,’X:\PAth\model.mdf’,’x:\modellog.ldf’

{

Note: If you are in SQLServer2008 we can use "create database with attach" option

 CREATE DATABASE [model] ON
 ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.R2\MSSQL\DATA\model.mdf' ),
 ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.R2\MSSQL\DATA\modellog.ldf' )
 FOR ATTACH
 go

}

Now we can start SQL Server normally and if you find any database in restoring state if can use RESTORE database DBNAME WITH RECOVERY to recover and open the database.

 

 

Thank You and Best Regards,

Karthick P.K

Posted in Recovery, Startup failures | Tagged: , , , , , , , , , , | 24 Comments »

Script to get current blocking tree with wait types

Posted by Karthick P.K on November 24, 2010

 

 

SET NOCOUNT ON; 

SET CONCAT_NULL_YIELDS_NULL OFF 

GO 

WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH,waittype,lastwaittype) 

AS 

( 

   SELECT 

   SPID, 

   BLOCKED, 

   CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL, 

   REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH, 

   R.waittype, 

   R.lastwaittype 

   FROM sys.sysprocesses R with (nolock) 

   CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T 

   WHERE (BLOCKED = 0 OR BLOCKED = SPID) 

   AND EXISTS    (SELECT SPID,BLOCKED,CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL, 

   BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH,R.waittype,R.lastwaittype FROM sys.sysprocesses R2 with (nolock) 

   CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T 

WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID) 

 

UNION ALL 

 

SELECT 

    R.SPID, 

    R.BLOCKED, 

    CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL, 

    REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH, 

    R.waittype, 

    R.lastwaittype 

    FROM sys.sysprocesses AS R with (nolock) 

    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T 

    INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID 

) 

 

SELECT N'       ' + REPLICATE (N'|      ', LEN (LEVEL)/4 - 2) + CASE WHEN (LEN (LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + CAST (SPID AS VARCHAR (10)) + ' '  + BATCH AS BLOCKING_TREE ,  waittype ,lastwaittype,  GETDATE() as Time FROM BLOCKERS with (nolock) ORDER BY LEVEL ASC 

go

 

–By Ajith Krishnan

Posted in Performance, SQL Query | Tagged: , , , , , , , , , | 6 Comments »

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

Posted by Karthick P.K on November 17, 2010

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

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

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

 

Error

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

 

 

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

CONFIG statement cannot be used inside a user transaction.

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

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

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

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

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

1.Turn off Implicit transaction

{

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

GO

RECONFIGURE WITH OVERRIDE

GO

}

2. SQL Server not able to create temp_MS_AgentSigningCertificate_database.mdf

Error:

{

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

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

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

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

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

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

}

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

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

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

{

EXEC sp_change_users_login ‘Report’;

}

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

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

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

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

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

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

 

Resolution

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

 

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

 

 

Thank you,

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

Disclaimer:

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

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

AWE allocator API’s (How SQL Server AWE works)

Posted by Karthick P.K on November 11, 2010

#include <windows.h> 
#include <string> 
#include <winbase.h> 
#include <iostream> 
using namespace std;
#include <psapi.h>
#pragma comment(lib,"psapi.lib")


BOOL LoggedSetLockPagesPrivilege ( HANDLE hProcess,BOOL bEnable)
{
  struct {
    DWORD Count;
    LUID_AND_ATTRIBUTES Privilege [1];
  } Info;

  HANDLE Token;
  BOOL Result;

  // Open the token.

  Result = OpenProcessToken ( hProcess,
                              TOKEN_ADJUST_PRIVILEGES,
                              & Token);

  if( Result != TRUE ) 
  {
    printf( "Cannot open process token.\n" );
    return FALSE;
  }

  // Enable or disable?

  Info.Count = 1;
  if( bEnable ) 
  {
    Info.Privilege[0].Attributes = SE_PRIVILEGE_ENABLED;
  } 
  else 
  {
    Info.Privilege[0].Attributes = 0;
  }

  // Get the LUID.

  Result = LookupPrivilegeValue ( NULL,
                                  SE_LOCK_MEMORY_NAME,
                                  &(Info.Privilege[0].Luid));

  if( Result != TRUE ) 
  {
    printf( "Cannot get privilege for %s.\n", SE_LOCK_MEMORY_NAME );
    return FALSE;
  }

  // Adjust the privilege.

  Result = AdjustTokenPrivileges ( Token, FALSE,
                                   (PTOKEN_PRIVILEGES) &Info,
                                   0, NULL, NULL);

  // Check the result.

  if( Result != TRUE ) 
  {
    printf ("Cannot adjust token privileges (%u)\n", GetLastError() );
    return FALSE;
  } 
  else 
  {
    if( GetLastError() != ERROR_SUCCESS ) 
    {
      printf ("Cannot enable the SE_LOCK_MEMORY_NAME privilege; ");
      printf ("please check the local policy.\n");
      return FALSE;
    }
  }

  CloseHandle( Token );

  return TRUE;
};






void main()
    {

    long int s=0;
    
    printf("\nEnter the size in MB  for address range that can be used to map Address Windowing Extensions (AWE) pages:");
    scanf("%d",&s);
    LPVOID lpaddress=NULL; 
    SIZE_T size=s*1024*1024;  //size in bytes
    printf ("\n%d",size);
    LPVOID ADD;
    int i;
    char *ADDw;
    BOOL bResult= FALSE;
    BOOL bResult2= FALSE;
    BOOL bResult3= FALSE;
    BOOL bResult4= FALSE;
    BOOL bResult5= FALSE;
    
    ULONG_PTR sizemap= (size)/4096;
    ULONG_PTR sizemap2= (size)/4096;
    ULONG_PTR sizemap3= (size)/4096;
    ULONG_PTR sizemap4= (size)/4096;
    ULONG_PTR sizemap5= (size)/4096;
    
                if( ! LoggedSetLockPagesPrivilege( GetCurrentProcess(), TRUE ) )  
                /*. The SeLockMemoryPrivilege privilege must be enabled in the caller's token or 
                the function will fail with ERROR_PRIVILEGE_NOT_HELD*/
                  {
                    printf("\n No Previledge");
                    printf("\n %u", GetLastError() );
                      return;
                  }


    ULONG_PTR * aRAMPages = new ULONG_PTR[sizemap];
    ULONG_PTR * aRAMPages2 = new ULONG_PTR[sizemap2];
    ULONG_PTR * aRAMPages3 = new ULONG_PTR[sizemap3];
    ULONG_PTR * aRAMPages4 = new ULONG_PTR[sizemap4];
    ULONG_PTR * aRAMPages5 = new ULONG_PTR[sizemap5];


    ADD=VirtualAlloc(lpaddress,size,MEM_RESERVE | MEM_PHYSICAL,PAGE_READWRITE);

    
    if (ADD==0)

    {
    printf ("allocation failled");
    printf("\n %u", GetLastError() );
        return;
    }



    bResult=AllocateUserPhysicalPages(GetCurrentProcess(),&sizemap,aRAMPages);

            if( bResult != TRUE ) 
            {
            printf("\n %uError in AllocateUserPhysicalPages", GetLastError() );
            return;
            }

    bResult2=AllocateUserPhysicalPages(GetCurrentProcess(),&sizemap2,aRAMPages2);

            if( bResult != TRUE ) 
            {
            printf("\n %uError in AllocateUserPhysicalPages2", GetLastError() );
            return;
            }
    bResult3=AllocateUserPhysicalPages(GetCurrentProcess(),&sizemap3,aRAMPages3);

            if( bResult != TRUE ) 
            {
            printf("\n %uError in AllocateUserPhysicalPages2", GetLastError() );
            return;
            }

    bResult4=AllocateUserPhysicalPages(GetCurrentProcess(),&sizemap4,aRAMPages4);

            if( bResult != TRUE ) 
            {
            printf("\n %uError in AllocateUserPhysicalPages2", GetLastError() );
            return;
            }

    bResult5=AllocateUserPhysicalPages(GetCurrentProcess(),&sizemap5,aRAMPages5);

            if( bResult != TRUE ) 
            {
            printf("\n %uError in AllocateUserPhysicalPages2", GetLastError() );
            return;
            }


printf("\n We have allocated 5 different ranges of physical memory pages that could be used to map and unmapp within Address Windowing Extensions (AWE) region of a specified process");


printf("\n Mapping the first range and filling with : MAP1");

   bResult=MapUserPhysicalPages(ADD,sizemap,aRAMPages);

            if( bResult != TRUE ) 
            {
            printf("\n %uError in MapUserPhysicalPages", GetLastError() );
            return;
            }
            

        ADDw =(LPSTR) ADD;
        for(i=1;i<=(size-5);i=i+5)

        {
            ADDw[i] = 'M';
            ADDw[i+1] = 'A';
            ADDw[i+2] = 'P';
            ADDw[i+3] = '1';
            ADDw[i+4] = ':';

        }
 
    printf("\n Mapping the second range and filling with : MAP2");
    bResult2=MapUserPhysicalPages(ADD,sizemap2,aRAMPages2);
                if( bResult != TRUE ) 
                {
                printf("\n %uError in MapUserPhysicalPages", GetLastError() );
                return;
                }

            for(i=1;i<=(size-5);i=i+5)
            {
            ADDw[i] = 'M';
            ADDw[i+1] = 'A';
            ADDw[i+2] = 'P';
            ADDw[i+3] = '2';
            ADDw[i+4] = ':';
            }
       
    printf("\n Mapping the third range and filling with : MAP3");            
       bResult3=MapUserPhysicalPages(ADD,sizemap3,aRAMPages3);

                if( bResult != TRUE ) 
                {
                printf("\n %uError in MapUserPhysicalPages", GetLastError() );
                return;
                }

            for(i=1;i<=(size-5);i=i+5)
            {
            ADDw[i] = 'M';
            ADDw[i+1] = 'A';
            ADDw[i+2] = 'P';
            ADDw[i+3] = '3';
            ADDw[i+4] = ':';
            }
    
       printf("\n Mapped the fourth range and filling with : MAP4");    
            
        bResult4=MapUserPhysicalPages(ADD,sizemap4,aRAMPages4);
        
                if( bResult != TRUE ) 
                {
                printf("\n %uError in MapUserPhysicalPages", GetLastError() );
                return;
                }

            for(i=1;i<=(size-5);i=i+5)
            {
            ADDw[i] = 'M';
            ADDw[i+1] = 'A';
            ADDw[i+2] = 'P';
            ADDw[i+3] = '4';
            ADDw[i+4] = ':';
            }
    
        printf("\n Mapped the fifth range and filled with : MAP5");

        bResult5=MapUserPhysicalPages(ADD,sizemap5,aRAMPages5);

                if( bResult != TRUE ) 
                {
                printf("\n %uError in MapUserPhysicalPages", GetLastError() );
                return;
                }

            for(i=1;i<=(size-5);i=i+5)
            {
            ADDw[i] = 'M';
            ADDw[i+1] = 'A';
            ADDw[i+2] = 'P';
            ADDw[i+3] = '5';
            ADDw[i+4] = ':';
            }

         


    printf("\n Mapping the first range and printing First 128 charecters");

    bResult=MapUserPhysicalPages(ADD,sizemap,aRAMPages);
            
            if( bResult != TRUE ) 
            {
            printf("\n %uError in MapUserPhysicalPages", GetLastError() );
            return;
            }


            

                for(int i=1;i<=128;i++)
                {
                printf("%c",ADDw[i]);
                }

printf("\n Mapping the second range and printing First 128 charecters");            
     bResult2=MapUserPhysicalPages(ADD,sizemap2,aRAMPages2);

                if( bResult != TRUE ) 
                {
                printf("\n %uError in MapUserPhysicalPages", GetLastError() );
                return;
                }
                for(int i=1;i<=128;i++)
                {
                printf("%c",ADDw[i]);
                }

printf("\n Mapping the third range and printing First 128 charecters");
bResult3=MapUserPhysicalPages(ADD,sizemap3,aRAMPages3);

                if( bResult != TRUE ) 
                {
                printf("\n %uError in MapUserPhysicalPages", GetLastError() );
                return;
                }
                for(int i=1;i<=128;i++)
                {
                printf("%c",ADDw[i]);
                }

printf("\n Mapping the fourth range and printing First 128 charecters");
bResult4=MapUserPhysicalPages(ADD,sizemap4,aRAMPages4);

                if( bResult != TRUE ) 
                {
                printf("\n %uError in MapUserPhysicalPages", GetLastError() );
                return;
                }
                for(int i=1;i<=128;i++)
                {
                printf("%c",ADDw[i]);
                }

printf("\n Mapping the five range and printing First 128 charecters");
bResult5=MapUserPhysicalPages(ADD,sizemap5,aRAMPages5);

                if( bResult != TRUE ) 
                {
                printf("\n %uError in MapUserPhysicalPages", GetLastError() );
                return;
                }
                for(int i=1;i<=128;i++)
                {
                printf("%c",ADDw[i]);
                }

system("pause");

}

 
 
 
Thanks
Karthick P.K

Posted in Programming | Tagged: , , , , , | 6 Comments »

SQLServer 2008 Fails to come online on cluster after upgrade

Posted by Karthick P.K on October 29, 2010

SQL-Server 2008 Fails to come online on cluster after upgrade.

Reason:

Server is in script upgrade mode. Only administrator can connect at this time.

Login failed for user ‘ccccc\xxxxx. Reason: Server is in script upgrade mode. Only administrator can connect at this time.

Issue:

SQL-Server 2008 Fails to come online on cluster after upgrade.

Error from SQL-Server Error log

Reason: Server is in script upgrade mode. Only administrator can connect at this time.”

“Login failed for user ‘ccccc\xxxxx. Reason: Server is in script upgrade mode. Only administrator can connect at this time.”

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

Resolution:

Start the SQLServer from services console (or) Command prompt and wait till the master database is completely upgraded. Once the script upgrade is complete start the SQL-Server normally from Cluster admin (or) Failover cluster manager.

Cause: During the Script upgrade mode only administrator can connect to SQL-Server, So when the SQL-Server resource is brought online ISAlive check fails immediately before upgrade completes and SQL-Server resource goes down. When we start SQLServer from services or command prompt ISAlive check doesn’t happen so upgrade completes. Once the upgrade is completed we can start SQL-Server normally.

Thanks,

Regards

karthick pk

Posted in SQL Cluster Setup, SQL Server Cluster, Startup failures | Tagged: , | 1 Comment »

How to rebuild index and update statistics for all the tables in database.

Posted by Karthick P.K on September 26, 2010

 
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'   --  {can be run anytime}

Exec sp_MSforeachtable "dbcc dbreindex('?')"      --- {Always run this on a off-peak hour on any SQL Server instance}
 

Thanks

Karthick

Posted in Optimizer, Performance, SQL General, SQL Query | Tagged: , , , , | 3 Comments »

I get error while I update a row in immediate updating subscription what is the cause?

Posted by Karthick P.K on September 15, 2010

Error:

{

Another user has modified the contents of this table or view; the database row you
are modifying no longer exists in the database.
Database error: ‘[Microsoft][ODBC SQL Server Driver][SQL Server]Updatable
Subscriptions: Rows do not match between Publisher and Subscriber. Run the
Distribution Agent to refresh rows at the Subscriber.
[Microsoft][ODBC SQL Server Driver][SQL Server]Updatable Subscriptions: Rolling
back transaction.’
}

 

In Transactional Replication with immediate updating subscription

Update to a row in subscriber which is already updated in publisher is not
possible.
When we update the row in subscriber it compares the value of msrepl_tran_version
in Publisher and subscriber. If it matches then update is allowed.
If msrepl_tran_version in Publisher and subscriber doesn’t match Update is not
possible for that row in subscriber.

Posted in Replication | Tagged: , , , | 4 Comments »

Tasks and connection icons missing after importing DTS in SQL Server management studio

Posted by Karthick P.K on August 18, 2010

After importing DTS package from SQL Server 2005 you may not see Connection Icons and tasks

To resolve this re-register DTS related DLL’s in C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn Folder

DTSFFile.dll
DTSPkg.dll
dtsui.dll
CDWTasks.dll
DTSPump.dll
CustTask.dll
ctasksui.dll

 

Thanks

Karthick P.K

Posted in DTS/SSIS, SQL Server Tools | Tagged: , | 1 Comment »

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created

Posted by Karthick P.K on July 31, 2010

Error

Saving changes is not permitted. The changes you have made require the following
tables to be dropped and re-created. You have either made changes to a table that
cant be re-created or enabled the option Prevent saving changes that require the
table to be re-created.

Resolution

SQL Server management studio—> Tools –>Options—> Designers –>Table and database designers –>Uncheck “Prevent saving changes that require table recreation”

image

Note: Some operations such as alter column will drop and recreate table. Ensure you do not abort SSMS while operation in progress. You might loose the table. I would recommend

script the changes and and run script in query window (or) At least review the script before you save changes using SSMS.

Thanks

Karthick P.K

Posted in SQL Server Tools, SSMS | Tagged: , , | 2 Comments »

Runtime error: ActiveX component can’t create object: ‘SQLDMO.SQLServer’

Posted by Karthick P.K on July 28, 2010

 

When you use SQLDMO ???

Install SQL Server2005 Backward compatibility

http://download.microsoft.com/download/3/1/6/316FADB2-E703-4351-8E9C-E0B36D9D697E/SQLServer2005_BC.msi

Also remember if you have installed 32-Bit SQL Server on 64-Bit you have to use Cscript.exe from SysWOW64

Posted in Configuration, SQL Server Setup | 1 Comment »

How to check if local system is connected to a network and identify the type of network connection

Posted by Karthick P.K on July 26, 2010

#include <windows.h> 
#include <iostream> 
using namespace std;
#pragma comment(lib, "Sensapi.lib")
#include <Sensapi.h>

void main()
{
    
bool a; 
LPDWORD lpdwFlags;
lpdwFlags = new DWORD;
a=IsNetworkAlive( lpdwFlags);

        if(GetLastError()!=0)
        {
            cout<<"IsNetworkAlive failed:%d"<<GetLastError();
        }
        else if(GetLastError()==0 & (!a) )
        {
            cout<<"Network is not connected";
        }
        else if(GetLastError()==0 & (a))
        {
            cout<<"Network is connected.Type: "<< *lpdwFlags;  //1=Lan and 2=WAN
        }

}

Regards

Karthick P.K

Posted in Programming, SQL General | 2 Comments »

I get Exception when i open SQL Server management studio

Posted by Karthick P.K on July 23, 2010

 

I get below exception when i open SSMS or register SQL Server

Error:

Microsoft .NET framework: Unhandled exception has occurred in a component in
your application. If you click continue the application will ignore this
error and attempt to continue. Unable to read the list of previously
registered servers on this system. Re-register your servers in the
‘Registered Servers’ window.

 

When you click to register new server you see

serialization output is invalid (Microsoft.SqlServer.Management.Sdk.Sfc)
Additional information: Unable to generate a temporary cl*** (result = 1)
error cs2001: Source file ‘C:\Windows\Temp\tt3xxx-n.0.cs’ could not be found.
error cs2008: no inputs specified
(system.xml)

 

Resolution: TEMP is pointing to invalid location.  Check if TEMP and TMP is defined properly in environment variables.

 

Regards

Karthick P.K

Posted in SQL Server Tools, SSMS | Tagged: , , , , | 2 Comments »

How to check if my account has LPM privilege

Posted by Karthick P.K on July 18, 2010

#include <windows.h> 
#include <string> 
#include <winbase.h> 
#include <iostream> 
using namespace std;
#include <psapi.h>
#pragma comment(lib,"psapi.lib")

void main()
    {

    long int s=0;
    printf("\nThis program will allocate memory using AWE allocator API's");
    printf("\nEnter the size of memory to be allocated using AWE API's:");
    scanf("%d",&s);
    LPVOID lpaddress=NULL; 
    SIZE_T size=s;   
    LPVOID ADD;
    BOOL bResult= FALSE;
    BOOL bResult2= FALSE;
    ULONG_PTR sizemap= (size)*1024*1024/4096;
    
                if( ! LoggedSetLockPagesPrivilege( GetCurrentProcess(), TRUE ) )  //. The SeLockMemoryPrivilege privilege must be enabled in the caller's token or the function will fail with ERROR_PRIVILEGE_NOT_HELD
                  {
                    printf("\n No Previledge");
                    printf("\n Error: %u", GetLastError() );
                      return;
                  }


    ULONG_PTR * aRAMPages = new ULONG_PTR[sizemap];


    ADD=VirtualAlloc(lpaddress,(size*1024*1024),MEM_RESERVE | MEM_PHYSICAL,PAGE_READWRITE);

    
    if (ADD==0)

    {
    printf ("allocation failled");
    printf("\n %u", GetLastError() );
        return;
    }



    bResult=AllocateUserPhysicalPages(GetCurrentProcess(),&sizemap,aRAMPages);

            if( bResult != TRUE ) 
            {
            printf("\n %uError in AllocateUserPhysicalPages", GetLastError() );
            return;
            }

     bResult2=MapUserPhysicalPages(ADD,sizemap,aRAMPages);

            if( bResult != TRUE ) 
            {
            printf("\n %uError in MapUserPhysicalPages", GetLastError() );
            return;
            }
printf("\nAllocated %d MB using AWE allocator API's which SQLServer uses when LPM is enabled.",s  );
system("pause");
}





 
BOOL LoggedSetLockPagesPrivilege ( HANDLE hProcess,BOOL bEnable)
{
  struct {
    DWORD Count;
    LUID_AND_ATTRIBUTES Privilege [1];
  } Info;

  HANDLE Token;
  BOOL Result;

  // Open the token.

  Result = OpenProcessToken ( hProcess,
                              TOKEN_ADJUST_PRIVILEGES,
                              & Token);

  if( Result != TRUE ) 
  {
    printf( "Cannot open process token.\n" );
    return FALSE;
  }

  // Enable or disable?

  Info.Count = 1;
  if( bEnable ) 
  {
    Info.Privilege[0].Attributes = SE_PRIVILEGE_ENABLED;
  } 
  else 
  {
    Info.Privilege[0].Attributes = 0;
  }

  // Get the LUID.

  Result = LookupPrivilegeValue ( NULL,
                                  SE_LOCK_MEMORY_NAME,
                                  &(Info.Privilege[0].Luid));

  if( Result != TRUE ) 
  {
    printf( "Cannot get privilege for %s.\n", SE_LOCK_MEMORY_NAME );
    return FALSE;
  }

  // Adjust the privilege.

  Result = AdjustTokenPrivileges ( Token, FALSE,
                                   (PTOKEN_PRIVILEGES) &Info,
                                   0, NULL, NULL);

  // Check the result.

  if( Result != TRUE ) 
  {
    printf ("Cannot adjust token privileges (%u) Error:", GetLastError() );
    return FALSE;
  } 
  else 
  {
    if( GetLastError() != ERROR_SUCCESS ) 
    {
      
    printf ("\nCannot enable the SE_LOCK_MEMORY_NAME privilege; ");
     printf ("\nPlease check the local policy.\n");
      return FALSE;
    }
  }

  CloseHandle( Token );

  return TRUE;
};




Regards
Karthick P.K

Posted in Programming | Tagged: , , , | 1 Comment »

How to run checkdb on all databases ?

Posted by Karthick P.K on July 15, 2010

 EXEC sp_MSforeachdb 'DBCC CHECKDB(?)' 

 

 

Thanks

Karthick P.K

Posted in DBCC | Tagged: | Leave a Comment »

Using DMVs to find out the index usage history- SQL Server Index Usage

Posted by Karthick P.K on July 6, 2010

SQL Server 2005 ships with a set of DMVs that can help you identify the missing indexes for your workload, Analyze the effectiveness of the existing ones and help find out index fragmentation.

Using DMVs to find out the index usage history

Over a period of time, you could create a lot of indexes on your tables and modify existing ones. However, in SQL 2000, you couldn’t estimate how effective were each of these indexes. Poorly design indexes could lead to performance overhead instead of enhancing performance.

In SQL 2005, you can query the sys.dm_db_index_usage_stats DMV to find out the indexes that have NEVER been used since the last start of SQL Server. You can use the following query to find that out:

 

select object_name(i.object_id) as ObjectName,

 

i.name as IndexName, s.user_updates, s.user_seeks, s.user_scans, s.user_lookups

 

from sys.indexes i

 

left join sys.dm_db_index_usage_stats s

 

on s.object_id = i.object_id and i.index_id = s.index_id and s.database_id = <dbid>

 

where objectproperty(i.object_id, 'IsIndexable') = 1 and

 

-- index_usage_stats has no reference to this index (not being used)

 

s.index_id is null or

 

-- index is being updated, but not used by seeks/scans/lookups

 

(s.user_updates > 0 and s.user_seeks = 0

 

and s.user_scans = 0 and s.user_lookups = 0)

 

order by object_name(i.object_id) asc

In the output, you will ALL the indexes that have never been used by any sort of workload on your server, since the last start of SQL Server.

For indexes that have NEVER been used (either for a SELECT or a DML statement), all columns will be NULL

For indexes that have NEVER been used (for a SELECT), but had to be updated due to a DML statement, the user_updates column will be >0, while other columns will be 0. It is these indexes that could cause severe performance overhead for your DML statements and might be worth dropping.

Give ample time for SQL Server to get exposed to all the workload after a restart, before running this query.

Using DMVs to find out missing indexes

When the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a suboptimal query plan, but still stores information about these indexes. The missing indexes feature enables you to access information about these indexes so you can decide whether they should be implemented.

For more information on how to use this feature, please visit the following link

http://msdn2.microsoft.com/en-us/library/ms345417.aspx

Using DMVs to find out index fragmentation

The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement.

You can learn more about using this DMV to identify fragmentation, correcting it and possibly automating this activity for your server by visiting the following link:

http://msdn2.microsoft.com/en-us/library/ms188917.aspx

Posted in SQL Query, SQL Server Engine | Tagged: , , , , | 1 Comment »

I cant not backup my database from SSMS….

Posted by Karthick P.K on June 26, 2010

When i backup my SQL Server database from SSMS i get

Error:

Cannot show requested dialog.

Cannot show requested dialog. (SqlMgmt)

 

Resolution:

This happens if you have two databases with same name in Case sensitive SQL Server Instance

Example: 1. USERDB   2. userdb

 

USE   T-SQL to backup your database

Posted in Backup/Restore, SQL Server Tools, SSMS | Tagged: , , , , | 2 Comments »

We do not see the SQL server and SQL Agent status from management studio. When we right click the instance, start, stop and resume options is disabled.

Posted by Karthick P.K on June 20, 2010

From  management studio we do not see the SQL server and SQL Agent status. When we right click the instance, start, stop and resume options is disabled.

 

1. Check if are running the management studio with elevated permissions (Right click SSMS—>Run as administrator)

 

2. WMI Permission could be a cause. Enable below permission in WMI.

 

–Open Dcomcnfg

–Expand Component Service -> Computers -> My computer

–Right-click and go to the Properties of My Computer

–Select the COM Security Tab

–Click on "Edit Limits"

–Under launch and activation permissions : Add both Startup account of SQLServer service and Logged on windows account.

 

 

–On the Start menu on, click Run.

–Type wmimgmt.msc, and then click OK.

–In the Windows Management Infrastructure program, right-click WMI Control (Local), and then click Properties.

–In the WMI Control (Local) Properties dialog box, select Security tab, expand Root, and then click CIMV2.

–Click Security to open the Security for ROOT\CIMV2 dialog box. Add startup account of SQLServer service and Windows Logon account and select Remote Enable permission.

 

 

3. Use the following command to give rights to query service .

 

subinacl /SERVICE MSSQL$SQLINST /GRANT=everyone

 

NOTE: MSSQL$SQLINST > Replace With your SQLServer service name.

 

 

4. We can use below command to reset the Security policy to default.

 

secedit /configure /cfg C:\WINDOWS\inf\defltsv.inf /db defltsv.sdb /verbose

 

–>If you still have the same error even after running this.

 

5. We can ran the following WMI query to check if we get any errors:

Select * from Win32_service where name="SQLServiceName"

 

 

Steps to check SQL Service status from WMI:

–Open “WBEMTEST”

–Connect to    “root\cimv2”

–Click on notification Query

–Run the below query

–SELECT * FROM __InstanceModificationEvent WITHIN 10 WHERE TargetInstance isa ‘Win32_Service’

–Stop SQLAgent or SQLServer service

–check if you get notification query:

 

 

6. Run the below Port query and check  if any of Instance is misidentified as cluster

 

a. "IsClustered "    Should be no

b.Check browser properties to see if it Standalone

 

7.Compare the HOST name ,select serverproperty (‘servername’)  , and select @@Servername

All the above three has to match.

–> If query “select serverproperty (‘servername’)” returns wrong name

 

Check the environment variable’s and see if we have below variables defined. Below keys should be present only for clustered SQLServer and  Key  "_CLUSTER_NETWORK_NAME_" should have SQLVirtual server name in case of cluster and this Environment variable should not be present for Standalone instance.

 

_CLUSTER_NETWORK_DOMAIN_=XXXXXXXXXXXX

_CLUSTER_NETWORK_FQDN_=XXXXXXXXXXXX

_CLUSTER_NETWORK_HOSTNAME_=XXXXXXXXXXXX

_CLUSTER_NETWORK_NAME_=XXXXXXXXXXXX

 

Note: Environment variable will be defined for service under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MSSQL$InstanceName\Environment

 

Thank you,

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

Disclaimer:

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

Posted in Connectivity, SQL Server Tools | Tagged: , , , | 3 Comments »

How to Analyze Deadlocked Schedulers Dumps?

Posted by Karthick P.K on June 15, 2010

How to Analyze "Deadlocked Schedulers" Dumps?

Do you see "Deadlocked Schedulers" errors similar to one below and stuck?

From SQL Server Errorlog

**Dump thread – spid = 0, PSS = 0x0000000000000000, EC = 0x0000000000000000

***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0001.txt

* BEGIN STACK DUMP:

* Deadlocked Schedulers

* Short Stack Dump

Stack Signature for the dump is 0x00000000000003D0

New queries assigned to process on Node 0 have not been picked  up by a worker thread in the last 60 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time.  Use the "max worker threads" configuration option to increase number  of allowable threads, or optimize current running queries.  SQL Process Utilization: 0%. System Idle: 69%.

New queries assigned to process on Node 3 have not been picked up by a worker thread in the last 300 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 6%. System Idle: 90%.

Cause

We get Deadlocked Schedulers error (New queries assigned to process on Node n have not been picked up by a worker thread in the last 300 seconds) and dump when Scheduler Monitor detects threads(workers) are not Progressing on schedulers of one (or) all nodes for 60 seconds (300 seconds in Denali) and there are new work request (tasks) but there is no idle threads to pick up the the work request and SQL Server could not spawn new thread to process the new work request (Max worker threads reached).

Some of common causes are most of the tasks are waiting on a single resource because of resource bottleneck, excessive blocking, excessive parallelism, very long running Queries executed by all workers, all threads waiting on trace write waits etc..

SQL Server will not accept new connections or cannot process new requests  when there is deadlocked scheduler condition (Exception: deadlocked condition on single node). 

Note: In systems with multiple nodes (NUMA)  If all the threads which belong to schedulers of single node is exhausted (or) Schedulers not progressing on single node can cause deadlocked scheduler condition.

Detailed explanation about dead lock schedulers can be found in This blog from my mentor J.P

Steps to analyze "Deadlocked Schedulers" Dumps.

To analyze the dump download and Install Windows Debugger from This  link

Step 1:

Open Windbg .  Choose File menu –> select Open crash dump –>Select the Dump file (SQLDump000#.mdmp)

Step 2:

on command window type
.sympath srv*c:\Websymbols*http://msdl.microsoft.com/download/symbols;

Step 3:

Type .reload /f and hit enter. This will force debugger to immediately load all the symbols.

Step 4:

Verify if symbols are loaded for  SQL Server by using the debugger command lmvm

0:002> lmvm sqlservr
start             end                 module name
00000000`01000000 00000000`03679000   sqlservr T (pdb symbols)          c:\websymbols\sqlservr.pdb\21E4AC6E96294A529C9D99826B5A7C032\sqlservr.pdb
    Loaded symbol image file: sqlservr.exe
    Image path: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe
    Image name: sqlservr.exe
    Timestamp:        Wed Oct 07 21:15:52 2009 (4ACD6778)
    CheckSum:         025FEB5E
    ImageSize:        02679000
    File version:     2005.90.4266.0
    Product version:  9.0.4266.0
    File flags:       0 (Mask 3F)
    File OS:          40000 NT Base
    File type:        1.0 App
    File date:        00000000.00000000
    Translations:     0000.04b0 0000.04e4 0409.04b0 0409.04e4

Step 5:

Type  ~*kL 20   and look at the stack of all the threads  to find what majority of threads are doing.

Note: If you find most of your threads are waiting on stack which is not listed below please paste the stack in comments session of this blog (or) In MSSQLWIKI  face book group we will try to answer you. If you don’t get prompt reply from the community, you may need to open a support ticket with Microsoft.

1. If it is blocking issue and If most of the threads are  waiting to acquire a lock you will find the most of the stack similar to one below. (We try to acquire lock and go to wait, since someone is holding a lock)

ntdll!ZwSignalAndWaitForSingleObject

kernel32!SignalObjectAndWait

sqlservr!SOS_Scheduler::SwitchContext

sqlservr!SOS_Scheduler::Suspend

sqlservr!SOS_Event::Wait

sqlservr!LockOwner::Sleep

sqlservr!lck_lockInternal

sqlservr!GetLock

2. If most of threads are stuck while trying to write profiler events to the destination you might find stack similar to one below

ntdll!ZwSignalAndWaitForSingleObject

kernel32!SignalObjectAndWait

sqlservr!SOS_Scheduler::SwitchContext

sqlservr!SOS_Task::Sleep

sqlservr!CTraceRowsetIoProvider::GetFreeBuffers

sqlservr!CTraceWriteRequest::InitForRowsetTrace

sqlservr!CTraceRowsetIoProvider::InitializeWriteRequest

sqlservr!CTrace::WriteRecord

sqlservr!CTraceController::ProduceRecord

sqlservr!CTraceData::TracePreBatchEvent

sqlservr!CSQLSource::Execute

sqlservr!process_request

sqlservr!process_commands

sqlservr!SOS_Task::Param::Execute

sqlservr!SOS_Scheduler::RunTask

sqlservr!SOS_Scheduler::ProcessTasks

3. If your stack’s  are like one below refer http://support.microsoft.com/default.aspx?scid=kb;EN-US;974205

sqlservr!SpinlockBase::Sleep
sqlservr!SpinlockBase::SpinToAcquire
sqlservr!TSyncHashTable_EntryAccessorsqlservr!CQSIndexStatsMgr::AddNewMissingIndex
sqlservr!CIdxSuggestion::Register
sqlservr!COptExpr::PqteConvert
sqlservr!CPhyOp_Top::PqteConvert
sqlservr!COptExpr::PqteConvert
sqlservr!COptExpr::PqteConvertTree
sqlservr!COptContext::PcxteOptimizeQuery
sqlservr!CQuery::Optimize
sqlservr!CQuery::PqoBuild
sqlservr!CStmtQuery::InitQuery
sqlservr!CStmtSelect::Init

4. If you see many stacks like the one below it could be BPOOL memory pressure (or) Lazy writer  waiting on I/O

sqlservr!BPool::Steal

sqlservr!SQLSinglePageAllocator::AllocatePages

sqlservr!MemoryNode::AllocatePagesInternal

sqlservr!MemoryClerkInternal::AllocatePages

sqlservr!IMemObj::PbGetNewPages

sqlservr!CSlotPageMgr::PbAllocate

5. If you see many stacks like the one below it should be because of excessive parallelism

sqlservr!CQScanXProducerNew::Open

sqlservr!FnProducerOpen

sqlservr!FnProducerThread

sqlservr!SubprocEntrypoint

6. If you see many stacks like the one below (Many threads waiting to flush log) it should be because of disk bottleneck’s. Check if you see "I/O requests taking longer than 15 seconds" messages in Errorlog before Deadlocked Schedulers Dumps. Refer Troubleshooting sql server I/O for troubleshooting I/O issues.

sqlservr!SOS_Event::Wait

sqlservr!SQLServerLogMgr::WaitLCFlush

sqlservr!SQLServerLogMgr::LogFlush

sqlservr!SQLServerLogMgr::WaitLogFlush

sqlservr!XdesRMFull::Commit

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group MSSQLWIKI to post your SQL Server questions to SQL Server experts

Related posts:

 

Thank you,

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

Disclaimer

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

Posted in Debugging, Performance, SQL Server Engine | Tagged: , , , , , , , , , , | 85 Comments »

Configuring SSL for SQL Server using Microsoft Certificate Authority Server

Posted by Karthick P.K on June 12, 2010

Configuring SSL for SQL Server using Microsoft Certificate Authority Server 

Refer attached document  for detailed steps

1. Install IIS Server from ADD/Remove Windows Components (if it is not installed already)

2. Install Certificate Server ADD/Remove Windows Components (if it is not installed already)

3. OPEN Certsrv browser console by either of below mentioned ways,

A.  IIS Manager and browse to Machine Name — Web sites — CertSrv
B.  IE open
http://localhost/certsrv
C.  From IE open
http://<machinename&gt; /certsrv
e.g., http://pjhome1/certsrv

4. To Install CA (Root) Certificate

A. Click on ‘Download a CA Certificate, Certificate Chain, or CRL’

B. Click on Install this CA certificate chain

C. Click YES

D. CA chain (Root Certificate) installed successfully

5. Create a SERVER Side Authentication Certificate

A. Go to Certsrv site and click on ‘Request a certificate’

B. Click on ‘Advanced Certificate request’

C.Cick on ‘Create and submit a request to this CA’

D. Enter the certificate information

  • 1. Type the FQDN (Fully Qualified Domain Name) for the name
  • 2. Select ‘Server Authentication Certificate’ for Type of Certificate Needed.
  • 3. Check the ‘Mark Keys as exportable’ option
  • 4. Click on Submit

E. Click on YES to complete

F. We need to make a note of the ‘Request Id’ from the below screen.

6. Issue the certificate.

A. In MMC add ‘Certificates’&’Certificate Authority’ using ‘Add/Remove Snap-in’ options.

B. Click on ‘Pending requests’ in ‘Certificate Authority’.
(We would see certificate with Request ID which we generated in STEP 5.i.e., 7 here)

C. Right click on the certificate –> All Tasks –> ISSUE

D. Now we should see the certificate under ‘Issued Certificates’

7. Install the certificate
A. Click on ‘View the status of a pending certificate request’

B. Click on the certificate.

C. Click on ‘Install this certificate’

D. Click on YES

E. We will see the successfully installed screen.

8. Assign the certificate to the SQL Server instance.

A. Open SQL Server Configuration Manager
B. Right click on ‘Protocol on <instance name>’
(for the instance which we need, here it is STANDARD)

C. In the certificate tab and select the certificate we created earlier.

D. Click on Apply and restart the SQL Server instance to get this change applied.

9.After the successful deployment of the certificate (Server side) we should see the below message in our SQL Error Log file during the server startup.
The certificate was successfully loaded for encryption.

 

Regards

Karthick P.K

Posted in Configuration, Connectivity, Security | Tagged: , , , , | 6 Comments »

How to retrieve information about the file system and volume associated with the specified root directory (GetVolumeInformation function)

Posted by Karthick P.K on May 30, 2010

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

void main()
{

  bool x=0;    
  char lpVolumeNameBuffer[1000];
  DWORD nVolumeNameSize;
  DWORD lpVolumeSerialNumber;
  DWORD lpMaximumComponentLength;
  DWORD lpFileSystemFlags;
  char lpFileSystemNameBuffer[1000];
  DWORD nFileSystemNameSize;
  char a[100];
  cout<<"A trailing backslash is required when you enter diskname. For example, you specify \\\\MyServer\\\MyShare as \\\\MyServer\\MyShare\\ or\n the C drive as C:\\";

   cout<<"\n"<<"Enter the Disk:";
   cin>>a;
   nVolumeNameSize= sizeof( (TCHAR) (lpVolumeNameBuffer))+1;
   nFileSystemNameSize=sizeof( (TCHAR) (lpFileSystemNameBuffer))+1;
 
  x= GetVolumeInformation((LPCSTR)&a, (LPSTR) lpVolumeNameBuffer,256,&lpVolumeSerialNumber,&lpMaximumComponentLength,&lpFileSystemFlags,(LPSTR) lpFileSystemNameBuffer,256);

            if (x==0)

            {
                int e=GetLastError();
                printf ( "Error getting volume information, Error ID:");
                printf ("%d",e);
            }
            else
            {
            cout<<"Disk:"<<a;    
            cout<<"\nVolumeNameBuffer:"<<lpVolumeNameBuffer;
            cout<<"\nVolumeSerialNumber:"<<lpVolumeSerialNumber;
            cout<<"\nMaximumComponentLength:"<<lpMaximumComponentLength;
            cout<<"\nFileSystemFlags:"<<lpFileSystemFlags;
            cout<<"\nFileSystemNameBuffer:"<<lpFileSystemNameBuffer;
            }

  
}

 

Posted in Programming | Tagged: | 2 Comments »

“Value cannot be null” when i connect SQL Server from SSMS

Posted by Karthick P.K on May 26, 2010

I get this below when I connect to SQL Server using SSMS…… What should i do?

Error

Value cannot be null.
Parameter name: viewInfo (Microsoft.SqlServer.Management.SqlStudio.Explorer)

Resolution

Right click SSMS   “run as administrator”   🙂

If the “run as administrator doesn’t resolve the problem verify if %Temp% environment variable to set properly for the logged on widows account

If %Temp% is not set properly in environment variables we might end up with error.

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

Thank you,

Karthick P.K |Technical Lead | Microsoft SQL Server Support  |My Facebook Page |My Site| Blog space| Twitter

Posted in SQL General, SQL Server Tools, SSMS | Tagged: , , , , , , | 14 Comments »

How to find all the profiler traces running on my SQL Server

Posted by Karthick P.K on April 26, 2010

select

      [Status] =

      case tr.[status]

            when 1 THEN 'Running'

            when 0 THEN 'Stopped'

      end

      ,[Default] =

            case tr.is_default

                  when 1 THEN 'System TRACE'

                  when 0 THEN 'User TRACE'

            end

       ,[login_name] = coalesce(se.login_name,se.login_name,'No reader spid')

      ,[Trace Path] = coalesce(tr.[Path],tr.[Path],'OLE DB Client Side Trace')

      from sys.traces tr

            left join sys.dm_exec_sessions se on tr.reader_spid = se.session_id

Posted in SQL Query | Tagged: , , | 4 Comments »

DBCC CheckDB fails with error "The database could not be checked as a database snapshot could not be created and the database or table could not be locked

Posted by Karthick P.K on March 6, 2010

 

DBCC CheckDB may with fail with error

Error

Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

 

Possible Causes Snapshot Creation Failure Reason

1. Database is having read only file group.

Check if the database is having read only file groups.

2. No Parse file support by the file system.

A. Parse file is not supported in FAT32 check the file system of the datafiles. If you use FAT32   use DBCC CheckDB with Tablock Option

B. To get the volume information of file system in which we have the data files SQL Server use  GetVolumeInformation API.

This API would fail if SQL Server startup account do not have full permission on Volume in which the data file is located.
Grant full permission for the startup account of SQL Server on the root volume of all the data files. To verify if the startup account of SQL Server has permission on volume in which data file is created use This EXE. It uses GetVolumeInformation to list the information about volume.

3. No alternate stream support.

 

Regards

Karthick P.K

Posted in Configuration, DBCC | Tagged: , , | 3 Comments »

How to get SQL Text and Query Plan for statements which are executing now

Posted by Karthick P.K on February 1, 2010

 
SELECT getdate() as "RunTime", st.text as batch,
SUBSTRING(st.text,statement_start_offset / 2+1 , 
( (CASE WHEN a.statement_end_offset = -1 
THEN (LEN(CONVERT(nvarchar(max),st.text)) * 2) 
ELSE a.statement_end_offset END)  - a.statement_start_offset) / 2+1)  as current_statement
,qp.query_plan, a.* FROM sys.dm_exec_requests a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as st CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) as qp 
order by CPU_time desc

 

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

Thank you,

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

Posted in SQL General, SQL Query | Tagged: , , , , | 7 Comments »

Script to clear stats

Posted by Karthick P.K on January 20, 2010

How to reset SQL Server stats with out restarting.

DBCC SQLPERF ('spinlockstats', CLEAR);

 

GO

 

DBCC SQLPERF ('netstats', CLEAR);

 

GO

 

DBCC SQLPERF ('rastats', CLEAR);

 

GO

 

DBCC SQLPERF ('iostats', CLEAR);

 

GO

 

DBCC SQLPERF ('threads', CLEAR);

 

GO

 

DBCC SQLPERF ('logspace', CLEAR);

 

GO

 

DBCC SQLPERF ('umsstats', CLEAR);

 

GO

 

DBCC SQLPERF ('waitstats', CLEAR);

 

GO

Posted in Performance, SQL General, SQL Query | Tagged: | 2 Comments »

Monitoring Tempdb usage

Posted by Karthick P.K on January 13, 2010

Monitoring Tempdb space usage and identifying the session and query which Consumes Tempdb

The total space used by Tempdb consists of 

1. User Objects 

2.  Internal Objects

3. Version Store

4. Free Space.

Use  Below Query to Track which objects (above) is consuming pace in TempDb. 
 

SELECT

SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB],SUM(version_store_reserved_page_count) AS [version store pages used],

(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB],SUM(internal_object_reserved_page_count) AS [internal object pages used],

(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in [MB],SUM(user_object_reserved_page_count) AS [user object pages used],

(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]

FROM sys.dm_db_file_space_usage;

go

Once you have identified the objects identify the query and session which is consuming tempdb using the query listed below

–Use below query to identify which Query and Session is consuming the space in TempDB

 
SELECT R1.session_id, R1.request_id, R1.Task_request_internal_objects_alloc_page_count, R1.Task_request_internal_objects_dealloc_page_count,

R1.Task_request_user_objects_alloc_page_count,R1.Task_request_user_objects_dealloc_page_count,R3.Session_request_internal_objects_alloc_page_count ,

R3.Session_request_internal_objects_dealloc_page_count,R3.Session_request_user_objects_alloc_page_count,R3.Session_request_user_objects_dealloc_page_count,

R2.sql_handle, RL2.text as SQLText, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle FROM (SELECT session_id, request_id, 

SUM(internal_objects_alloc_page_count) AS Task_request_internal_objects_alloc_page_count, SUM(internal_objects_dealloc_page_count)AS 

Task_request_internal_objects_dealloc_page_count,SUM(user_objects_alloc_page_count) AS Task_request_user_objects_alloc_page_count,

SUM(user_objects_dealloc_page_count)AS Task_request_user_objects_dealloc_page_count FROM sys.dm_db_task_space_usage 

GROUP BY session_id, request_id) R1 INNER JOIN (SELECT session_id, SUM(internal_objects_alloc_page_count) AS Session_request_internal_objects_alloc_page_count,

SUM(internal_objects_dealloc_page_count)AS Session_request_internal_objects_dealloc_page_count,SUM(user_objects_alloc_page_count) AS Session_request_user_objects_alloc_page_count,

SUM(user_objects_dealloc_page_count)AS Session_request_user_objects_dealloc_page_count FROM sys.dm_db_Session_space_usage 

GROUP BY session_id) R3 on R1.session_id = R3.session_id 

left outer JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id

OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS RL2
Where 
Task_request_internal_objects_alloc_page_count >0 or  
Task_request_internal_objects_dealloc_page_count>0 or 
Task_request_user_objects_alloc_page_count >0 or 
Task_request_user_objects_dealloc_page_count >0 or 
Session_request_internal_objects_alloc_page_count >0 or 
Session_request_internal_objects_dealloc_page_count >0 or 
Session_request_user_objects_alloc_page_count >0 or 
Session_request_user_objects_dealloc_page_count >0 

Known issues related to TEMPDB Shrink

FIX: The used space in the tempdb database increases continuously when you run a query that creates internal objects in the tempdb database in SQL Server 2005
 

Thank you,

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

Posted in Performance, SQL General, SQL Query | Tagged: , , , , , , , , , , | 4 Comments »

Trivial Plan

Posted by Karthick P.K on May 29, 2009

What is Trivial Plan?

A trivial plan results when the query optimizer determines that given tables referenced in the query and the indexes existing on them, only one plan is possible.
Obviously, a recompilation would be futile in such a case. A query that has generated a trivial plan may not always generate a trivial plan, of course.
For example, new indexes might be created on the underlying tables, and so multiple access paths become available to the query optimizer. Additions of such indexes
would be detected and a correctness-related recompilation might replace the trivial plan with a non-trivial one.
SQL Server doesn’t  recompile trivial plans if we have update stats on the underlying table and  will not fire Auto updatestats for the query with Trivial Plan.

Let us see the below example query

create database AutoS

use autos

create table tab(col1 int)
declare @i int

set @i = 0

while @i < 1000

begin

insert into tab(col1) values (@i) –We are inserting 1000 Rows with value 1 to 999
set @i = @i + 1

end

set @i = 0

while @i < 1000

begin

insert into tab(col1) values (9999) –We are inserting 1000 Rows with value 9999

set @i = @i + 1

end

create statistics t_col1 on tab(col1) with fullscan

dbcc show_statistics (‘dbo.tab’,’t_col1′)

–So we have created  a table with 2000 rows. 1000 of them have the values 9999 and 1000 of them have values 0 to 999.
–Create a Simple store Procedure

create procedure test @a int
as
begin
select * from tab where col1 = @a
end
set statistics profile on
SET STATISTICS XML ON

exec test 9999  — Check the XML Plan you will see it is trivial plan(StatementOptmLevel=”TRIVIAL”)
set statistics profile off

delete from tab –Now delete all the rows from the table

Execute the store Procedure again

exec test 9999

If Optimizer has not choosed the Trival Plan which is already cached, auto updatestats would have been fired for the table(Trace flag 8721 will dump information when
AutoStat has been run)and would have caused Recompile of SP.

Regards

Karthick P.K

Posted in Performance | Tagged: , , | 15 Comments »

How to add an IP Address when we Add new NIC to node where SQLServer2005 instance is running.

Posted by Karthick P.K on April 14, 2009

The only possible way to add an IP to SQLServer2005 after adding new NIC to the
node where SQLServer2005 is installed (or) After adding second ip address to
existing NIC is to either manually edit the registry or run Service pack/Hotfix setup again.
The only possible way to add an IP to SQLServer2005 after adding new NIC to the
node where SQLServer2005 is installed (or) After adding second ip address to
existing NIC is

Option1

Manually edit the registry

1. Add a new registry key under
HKLM\software\microsoft\microsoftSQLServer\mssql.1\mssqlserver\supersocketnetlib\tcp
for IP2

simplest way to make it is export the Key

HKLM\software\microsoft\microsoftSQLServer\mssql.1\mssqlserver\supersocketnetlib\tcp
,

Open the exported file using the notepad and Copy the Following string ,make
required changes(Modify the IPAddress,IPn.. Ie: n stands for number of IP and MSSQL.N Ie nstands for instance ID )

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.X\MSSQLServer\SuperSocketNetLib\Tcp\IPn]
“Enabled”=dword:00000000
“Active”=dword:00000001
“TcpPort”=””
“TcpDynamicPorts”=””
“DisplayName”=”Specific IP Address”
“IpAddress”=”65.52.17.19”

2. Import the registry again.

3..Now the new ip is listed in SQLServer configuration manager for editing.
Option2

Install the service pack again.

1. After the installatin of Service packs/HotFIx SQLServer recognize the new NIC but
Assings wrong IPaddresses to SQLserver.it makes SQLServer Listen on wrong ip’s and
so the SQLServer might fail to start with error

“Error: 17120, Severity: 16, State: 1.
2007-08-25 00:27:10.83 Server SQL Server could not spawn FRunCM thread.

Check the SQL Server error log and the Windows event logs for information about
possible related problems.”

2. We can find duplicate IP in SSCM(SQLServer Configuration manager). we can edit
the IP addresses in SQLserver configuration manager to fix right IP and start the SQLServer.

Regards

Karthick P.K

Posted in Configuration, SQL Server Tools | Tagged: | 4 Comments »

Different Status bits of sysdatabases in SQLServer

Posted by Karthick P.K on April 11, 2009

Sysdatabases.status and sysdatabases.status2 Bit of Sysdatabases in SQLServer
======================================================

<Script1>

declare @status int;
declare @status2 int;

Set  @status=’65544′  –Replace your DB status here
set @status2=’1090520064′ –Replace your DB status2 here

SELECT CASE (@status & 1) WHEN 1 THEN 1 ELSE 0 END AS autoclose,
CASE (@status & 4) WHEN 4 THEN 1 ELSE 0 END AS selectintobulkcopy,
CASE (@status & 8) WHEN 8 THEN 1 ELSE 0 END AS trunclogonchkpt,
CASE (@status & 16) WHEN 16 THEN 1 ELSE 0 END AS tornpagedetection,
CASE (@status & 32) WHEN 32 THEN 1 ELSE 0 END AS loading, CASE (@status & 64) WHEN 64 THEN 1 ELSE 0 END AS prerecovery, CASE (@status & 128) WHEN 128 THEN 1 ELSE 0 END AS recovering, CASE (@status & 256) WHEN 256 THEN 1 ELSE 0 END AS notrecovered, CASE (@status & 512) WHEN 512 THEN 1 ELSE 0 END AS offline, CASE (@status & 1024) WHEN 1024 THEN 1 ELSE 0 END AS readonly, CASE (@status & 2048) WHEN 2048 THEN 1 ELSE 0 END AS dbouseonly, CASE (@status & 4096) WHEN 4096 THEN 1 ELSE 0 END AS singleuser, CASE (@status & 32768) WHEN 32768 THEN 1 ELSE 0 END AS emergencymode,
CASE (@status & 4194304) WHEN 4194304 THEN 1 ELSE 0 END AS autoshrink, CASE (@status & 1073741824) WHEN 1073741824 THEN 1 ELSE 0 END AS cleanlyshutdown,
CASE (@status2 & 16384) WHEN 16384 THEN 1 ELSE 0 END AS ansinulldefault, CASE (@status2 & 65536) WHEN 65536 THEN 1 ELSE 0 END AS concatnullyieldsnull,
CASE (@status2 & 131072) WHEN 131072 THEN 1 ELSE 0 END AS recursivetriggers, CASE (@status2 & 1048576) WHEN 1048576 THEN 1 ELSE 0 END AS defaulttolocalcursor,
CASE (@status2 & 8388608) WHEN 8388608 THEN 1 ELSE 0 END AS quotedidentifier, CASE (@status2 & 33554432) WHEN 33554432 THEN 1 ELSE 0 END AS cursorcloseoncommit,
CASE (@status2 & 67108864) WHEN 67108864 THEN 1 ELSE 0 END AS ansinulls, CASE (@status2 & 268435456) WHEN 268435456 THEN 1 ELSE 0 END AS ansiwarnings,
CASE (@status2 & 536870912) WHEN 536870912 THEN 1 ELSE 0 END AS fulltextenabled

</Script1>

 

=================================================================================================================

<Script2>

SELECT substring(name, 1, 50) as dbname, cmptlevel, filename, version, CASE (status & 1) WHEN 1 THEN 1 ELSE 0 END AS autoclose,
CASE (status & 4) WHEN 4 THEN 1 ELSE 0 END AS selectintobulkcopy,
CASE (status & 8) WHEN 8 THEN 1 ELSE 0 END AS trunclogonchkpt,
CASE (status & 16) WHEN 16 THEN 1 ELSE 0 END AS tornpagedetection,
CASE (status & 32) WHEN 32 THEN 1 ELSE 0 END AS loading, CASE (status & 64) WHEN 64 THEN 1 ELSE 0 END AS prerecovery, CASE (status & 128) WHEN 128 THEN 1 ELSE 0 END AS recovering, CASE (status & 256) WHEN 256 THEN 1 ELSE 0 END AS notrecovered, CASE (status & 512) WHEN 512 THEN 1 ELSE 0 END AS offline, CASE (status & 1024) WHEN 1024 THEN 1 ELSE 0 END AS readonly, CASE (status & 2048) WHEN 2048 THEN 1 ELSE 0 END AS dbouseonly, CASE (status & 4096) WHEN 4096 THEN 1 ELSE 0 END AS singleuser, CASE (status & 32768) WHEN 32768 THEN 1 ELSE 0 END AS emergencymode,
CASE (status & 4194304) WHEN 4194304 THEN 1 ELSE 0 END AS autoshrink, CASE (status & 1073741824) WHEN 1073741824 THEN 1 ELSE 0 END AS cleanlyshutdown,
CASE (status2 & 16384) WHEN 16384 THEN 1 ELSE 0 END AS ansinulldefault, CASE (status2 & 65536) WHEN 65536 THEN 1 ELSE 0 END AS concatnullyieldsnull,
CASE (status2 & 131072) WHEN 131072 THEN 1 ELSE 0 END AS recursivetriggers, CASE (status2 & 1048576) WHEN 1048576 THEN 1 ELSE 0 END AS defaulttolocalcursor,
CASE (status2 & 8388608) WHEN 8388608 THEN 1 ELSE 0 END AS quotedidentifier, CASE (status2 & 33554432) WHEN 33554432 THEN 1 ELSE 0 END AS cursorcloseoncommit,
CASE (status2 & 67108864) WHEN 67108864 THEN 1 ELSE 0 END AS ansinulls, CASE (status2 & 268435456) WHEN 268435456 THEN 1 ELSE 0 END AS ansiwarnings,
CASE (status2 & 536870912) WHEN 536870912 THEN 1 ELSE 0 END AS fulltextenabled FROM sysdatabases where name = ‘master’
go
SELECT fileid, name, filename, size, maxsize, growth, groupid
FROM sysaltfiles WHERE dbid IN ( SELECT dbid FROM sysdatabases where name = ‘ Replace DB Name’ )
go
SELECT fileid, name, filename, size, maxsize, growth, groupid FROM sysfiles
go
SELECT groupid, groupname, CASE (status & 0x8) WHEN 0x8 THEN 1 ELSE 0 END AS readonly,
CASE (status & 0x10) WHEN 0x10 THEN 1 ELSE 0 END AS isdefault FROM sysfilegroups WHERE groupid IN ( SELECT groupid FROM sysaltfiles a, sysdatabases b WHERE a.dbid = b.dbid AND b.name = ‘Replace DB Name’ )

</Script2>

 

Regards

Karthick P.K

Posted in SQL General | Tagged: , , , | 7 Comments »

Installation of SQLServer2008 fails (The registry key SYSTEM\CurrentControlSet\Services\RsFx0102\InstancesShares is missing)

Posted by Karthick P.K on April 10, 2009

Installation of SQLServer2008 might fail with below error

Detailed results:
Feature:                       Database Engine Services
Status:                        Failed: see logs for details
MSI status:                    Passed
Configuration status:          Failed: see details below
Configuration error code:      0xCD263ADC@1306@30
Configuration error description: The registry key SYSTEM\CurrentControlSet\Services\RsFx0102\InstancesShares is missing.
Configuration log:             C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20090410_153833\Detail.txt

To resolve this issue:

Create a new KEY with name   InstancesShares under registry path

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet\Services\RsFx0102\

Uninstall the failed Database engine components and reinstall the SQLServer 2008 instance after restarting the system.

Regards

Karthick P.K

Posted in SQL General | Tagged: | 2 Comments »

Installation of SQLserver2008 cluster fails on windows2008.(The group or resource is not in the correct state to perform the requested operation. (Exception from HRESULT: 0x8007139F)

Posted by Karthick P.K on March 26, 2009

Installation of SQLserver2008 cluster might fail on windows2008 with error mentioned below

The cluster resource ‘SQL Server’ could not be brought online.
Error: The group or resource is not in the correct state to perform the requested operation. (Exception from HRESULT: 0x8007139F)

 

Root Cause

This problem occurs because of a new security feature named Loopback check functionality. By default, loopback check functionality is turned ON in Windows and the value of the DisableLoopbackCheck registry entry is set to 0 (zero).
http://support.microsoft.com/kb/957097/

With this feature being turned ON: windows do not allow NTLM authentication if we try to access server from Local server using a name which is not its Net-Bios name (or) IPAddress.

When SQL Server Agent is started, SQL Agent resource access the SQL Server using SQL  VirtualServer name and hence we do not allow NTLM. So the SQL Server Agent would fail and the SQLServer Agent Resource creation would also fail.

SQL Server resource will fail to come Online because, IsAlive check will be done using NTLM Authentication i.e: Cluster service startup account resolves as NT AUTHORITY\ANONYMOUS LOGON when connecting to SQL Server for IsAlive check and the connection fails.

We will not get in to this issue if startup account of SQL Server has permissions to read and write SPN’s.

After the installation fails you will see the SQL Server resource is created but not the SQL Agent resource.

There are three ways to resolve this issue.

Option 1

1. After the failure, create the SPN’s manually using SetSPN tool (or) Configure SQL Server service to create SPNs dynamically for the SQL Server instances (Refer KB: 811889)

Example for creating SPN’s manually:
SETSPN -A MSSQLSVC/VSName.XX.XX.EDU:1433
SETSPN -A MSSQLSVC/VSName.XX.XX.EDU

2. Bring the SQL Server Resource online.

3. Create the SQL Server Agent resource type.

{
To add the sql server agent resource type execute the below command:

cluster restype “SQL Server Agent” /create /DLL:sqagtres.dll .Once done we got the
update that the Resource type ‘SQL Server Agent’ created.
}

4. Create SQL Server agent resource manually.

We need to make sure that the newly created SQL server Agent resource have the virtualservername and Instance name .

To add this property go to “failover cluster management” ==>SQL Server Agent Resource==>Properties==>properties
check for the two parameters (virtualservername and Instancename) and fill in the
details.

}

5. Change configuration reg_dword values of all components to 1 in below registry path

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLX.MSSQLSERVER\ConfigurationState

Option 2

1. Do a Complete uninstall of failed installation (or) Configure SQL Server service to create SPNs dynamically for the SQL Server instances (Refer KB: 811889) and move to Step 3.

2. Create the SPN’s before we do the installation. —

Example:
SETSPN -A MSSQLSVC/VSName.XX.XX.EDU:1433
SETSPN -A MSSQLSVC/VSName.XX.XX.EDU

Note:Beginning with SQL Server 2008, the SPN format is changed and new SPN format does not require a port number Refer: http://msdn.microsoft.com/en-us/library/ms191153.aspx

3. Then install the SQL Server on cluster

Option 3 (Recommended)

1. Disable the authentication loopback check by setting the DisableLoopbackCheck value in
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa registry subkey to 1.
To set the DisableLoopbackCheck registry entry to 1, follow below steps on all nodes of cluster.

a. Click Start, click Run, type regedit, and then click OK.
b. Locate the following registry path:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
c. Right-click Lsa, select New, and then click DWORD Value.
d. Type DisableLoopbackCheck, and then press ENTER.
e. Right-click DisableLoopbackCheck, and then click Modify.
f. In the Value data box, type 1, and then click OK.

2. Restart the system.

3. Do complete uninstall and re-run the setup(or) Follow the steps from step2 in option 1.

 

Note:

1. We will encounter above error if we are installing the named instance of SQL Server and SQL Server browser is in stopped state. 

2.  If you have installed SQLServer 2012 (Denali) and uninstalled it on same cluster. You might encounter above issue. Refer below link for   details.

https://mssqlwiki.com/2012/01/31/sql-server-resource-fails-to-come-online-is-alive-check-fails/

Regards

Karthick P.K

Posted in SQL Cluster Setup, SQL General, SQL Server Setup | Tagged: , , , , , , | 8 Comments »

Installation of SQLServer2005/2008/2012 Fails on Windows2008 Cluster.

Posted by Karthick P.K on March 26, 2009

Installation of SQLServer2005/20082012 on Windows2008/2012 Cluster might fail with error mentioned below

 

Erorr1

The following error has occurred:

The cluster resource SQL Server could not be brought online due to an error bringing the dependency resource ‘SQL Network Name ( )’ online.  Refer to the Cluster Events in the Failover Cluster Manager for more information.

Click ‘Retry’ to retry the failed action, or click ‘Cancel’ to cancel this action and continue setup.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.3128.0&EvtType=0xE8049925%25400x42B4DED7

——————————

 

Erorr2:

The cluster identity ‘cLUSTER$nAME’ can create computer objects. By default all computer objects are created in the ‘Computers’ container; consult the domain administrator if this location has been changed.
The quota for computer objects has not been reached.
If there is an existing computer object, verify the Cluster Identity ‘SCLUSTERNAME$’ has ‘Full Control’ permission to that computer object using the Active Directory Users and Computers tool.
ERR [RES] Network Name <SQL Network Name (VSServerName)>: Computer account VSServerName couldn’t be re-enabled. status 5
INFO [NM] Received request from client address ASPMB9000D05N1.
INFO [NM] Received request from client address ASPMB9000D05N1.
ERR [RHS] Online for resource SQL Network Name (VSServerName) failed.
INFO [RCM] HandleMonitorReply: ONLINERESOURCE for ‘SQL Network Name (VSServerName)’, gen(2) result 5018.
INFO [RCM] TransitionToState(SQL Network Name (VSServerName)) OnlinePending–>ProcessingFailure.
ERR [RCM] rcm::RcmResource::HandleFailure: (SQL Network Name (VSServerName))
ERR [RES] Network Name <SQL Network Name (VSServerName)>: Unable to create computer account VSServerName on DC \\ay.xz.dc, in default Computers container, status 5

 

 

Cause

When you create a new clustered network name, a computer object (computer account) for that clustered service or application must be created in the Active Directory domain.

This computer object is created by the computer object of the cluster itself.  This computer abject of the cluster is responsible for creating the computer object for  "SQL Virtual Server" in active directory . If the computer object of the cluster itself does not have the appropriate permissions, it cannot create or update the computer object for "SQL Virtual Server"  . So the installation of SQL Server would fail.

To resolve this issue Grant  "Create Computer Objects" permission for  the computer object created for the cluster  (Computer Name object(CNO)).

For additional info on this Refer: http://technet.microsoft.com/en-us/library/cc773451(WS.10).aspx

 

Regards

Karthick PK

Posted in SQL Cluster Setup, SQL Server Cluster | Tagged: , , , , , , , | 2 Comments »

Using DMVs to find out the index usage history- SQLServer Index Usage

Posted by Karthick P.K on March 10, 2009

SQLserver 2005 ships with a set of DMVs that can help you identify the missing indexes for your workload, Analyze the effectiveness of the existing ones and help find out index fragmentation.

Using DMVs to find out the index usage history

Over a period of time, you could create a lot of indexes on your tables and modify existing ones. However, in SQL 2000, you couldn’t estimate how effective were each of these indexes. Poorly design indexes could lead to performance overhead instead of enhancing performance.

In SQL 2005, you can query the sys.dm_db_index_usage_stats DMV to find out the indexes that have NEVER been used since the last start of SQL Server. You can use the following query to find that out:

 1: select object_name(i.object_id) as ObjectName,

 

 2: i.name as IndexName, s.user_updates, s.user_seeks, s.user_scans,

 

 3: s.user_lookups

 

 4: from sys.indexes i

 

 5: left join sys.dm_db_index_usage_stats s

 

 6: on s.object_id = i.object_id and i.index_id = s.index_id and s.database_id =

 

 7: <dbid>

 

 8: where objectproperty(i.object_id, 'IsIndexable') = 1 and

 

 9: -- index_usage_stats has no reference to this index (not being used)

 

 10: s.index_id is null or

 

 11: -- index is being updated, but not used by seeks/scans/lookups

 

 12: (s.user_updates > 0 and s.user_seeks = 0

 

 13: and s.user_scans = 0 and s.user_lookups = 0)

 

 14: order by object_name(i.object_id) asc

 

In the output, you will ALL the indexes that have never been used by any sort of workload on your server, since the last start of SQL Server.

For indexes that have NEVER been used (either for a SELECT or a DML statement), all columns will be NULL

For indexes that have NEVER been used (for a SELECT), but had to be updated due to a DML statement, the user_updates column will be >0, while other columns will be 0. It is these indexes that could cause severe performance overhead for your DML statements and might be worth dropping.

Give ample time for SQL Server to get exposed to all the workload after a restart, before running this query.

Using DMVs to find out missing indexes

When the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a suboptimal query plan, but still stores information about these indexes. The missing indexes feature enables you to access information about these indexes so you can decide whether they should be implemented.

For more information on how to use this feature, please visit the following link

http://msdn2.microsoft.com/en-us/library/ms345417.aspx

 

Using DMVs to find out index fragmentation

The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement.

You can learn more about using this DMV to identify fragmentation, correcting it and possibly automating this activity for your server by visiting the following link:

http://msdn2.microsoft.com/en-us/library/ms188917.aspx

Regards

Karthick P.K

Posted in Performance, SQL General | Tagged: | 1 Comment »

Script to get all the Query and cached plans

Posted by Karthick P.K on February 18, 2009

 

 

SELECT

    *

 FROM sys.dm_exec_cached_plans cp

 

  CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) eqp

 

  CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) est

 

 --WHERE   est.dbid = DB_ID('DBNMAE')

Posted in SQL Query | Tagged: , | 1 Comment »

How to find who altered my SQL Server Login

Posted by Karthick P.K on January 25, 2009

Do you know how to find who changed SQL Server login or Password?

Here is the way…..

SELECT [Transaction SID],suser_sname([Transaction SID]) as ‘Login Name’   FROM ::fn_dblog(default, default)   WHERE [Transaction Name]=’ALTER LOGIN’

 

 

Thanks

Karthick P.K

Posted in DBCC, Security, SQL General | 3 Comments »

How to Browse (or) view objects and there code in mssqlsystemresource Database

Posted by Karthick P.K on January 15, 2009

How to Browse (or) view mssqlsystemresource Database.

1. Stop the SQL Server service
Copy the mssqlsystemresource.MDF and mssqlsystemresource.LDF to a new path (This
two files will be in same path where master databse is located).
Start the SQL Server Service
Ues the following command to attach the data and log file as a new user database.

EXEC sp_attach_db
‘mssqlsystemresource _Copy’, ‘<Path where you copied>\resource_copy.mdf’,
‘<Path where you copied>\resource_copy.ldf’
Now you browse through all the system objects and there code.

OR

1. Start SQL Server in single user mode.

2. Open SSMS and connect using DAC (ADMIN) Connection and change DB context to “mssqlsystemresource”.

use mssqlsystemresource

3. Query Resource DB objects.

Regards

Karthick P.K

Posted in Configuration, SQL General, SQL Server Engine | Tagged: , , | 3 Comments »

SQL Server: Table Variables (VS) Temp Tables

Posted by Karthick P.K on January 15, 2009

Table Variables (VS) Temp Tables

SQLServer2005 caches temp tables and temp variables only under some conditions.
Scenarios where temp table/variable are not cached (see below) may cause performance degradation as compared to SQLServer2000.

Following are scenarios where temp table/variable are not cached:
1. select into #t
2. alter table #t
3. create index on #t
4. Global temp tables (##t)
5. Local temp tables on adhoc level (nest level 0)
6. table variables are also not cached for dynamic SQL.

What are some of the drawbacks of table variables?

These are some of the drawbacks as compared to temporary tables:

Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement.

Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.

The table definition cannot be changed after the initial DECLARE statement.
Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement.
CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.
You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was
created outside the EXEC statement or the sp_executesql stored procedure because table variables can be referenced in their local scope only, an EXEC statement and
a sp_executesql stored procedure would be outside the scope of the table variable.

However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table
variables local scope is in the EXEC statement or the sp_executesql stored procedure.

Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because temporary or permanent tables
are maintained in a database that resides on the physical disk and also logged?

A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store
data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are
created and processed while in memory (data cache).

Do I have to use table variables instead of temporary tables?

The answer depends on these three factors:
The number of rows that are inserted to the table and are they joined with other tables.
The number of recompilations the query is saved from.
The type of queries and their dependency on indexes and statistics for performance.

 

Option recompile can help optimizer to estimate the number of rows table variable  refer http://blogs.msdn.com/b/psssql/archive/2010/08/24/query-performance-and-table-variables.aspx 

 

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

Thank you,

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

Posted in Memory, Performance, SQL Query, SQL Server Engine | Tagged: , , , | 8 Comments »

Behavior of SQL Server default instance on a NON-Default port

Posted by Karthick P.K on January 15, 2009

Unable to connect to SQLServer listening on Non-Default Port?

If you connect to SQLServer using the “hostname” it connects to the instance which is listening on 1433.

If you make named instance listen on 1433 and connect using “hostname” it connects to named instance which is listening on 1433.

if you connect to SQLServer using hostname or ip-address (without “\instance name”) it connects to instance which is listening on 1433, it can be named instance as well.(We have a belief that it connects to default instance because default port of default instance is 1433).

When you are connecting to SQLServer using hostname (without “\instance name”) client will not communicate with SQL Browser.
SQLClient would communicate with SQLBrowser only if there is “\instancename”  followed by host name.

Dynamic port detection is only available for named instances of SQL Server 2000.
The behavior of SQL Server 2000 for a default instance is exactly the same as in earlier versions of SQL Server. The network libraries assume either 1433 or the
global default port established with the Client Configuration Utility.

If a default instance is listening on a port other than the standard 1433 port, you can provide an alias or change the global default port. You can also connect to the
instance of SQL Server by using its server name, its FQDN, or its IP address followed by a comma and the port number.

The mdac rules :

If you connect to a Named Instance, then you need to either specify the Server\Instance or Server,Port.
If you change the default port number for the default instance, then you need to specify Port number as well.

Regards

Karthick P.K

Posted in Configuration, Connectivity | Tagged: , , , | 16 Comments »