MSSQLWIKI

Disassemble SQLServer

What is Target Server Memory (KB)?

Posted by Karthick P.K on May 27, 2012

What is Target Server Memory (KB)?

 

To  super simplify  in conventional memory model  SQL Server calculates something like target1 and target2 pages using below formula

 

               

Target1 = Current committed pages of SQL Server + ( Available Physical Memory – min (Total Physical Memory Pages / 20, Available Physical Memory Pages / 2))

ullAvailPageFile: The maximum amount of memory the current process can commit, in bytes. This value is equal to or smaller than the system-wide available commit value. To calculate the system-wide available commit value, call GetPerformanceInfo and subtract the value of CommitTotal from the value of CommitLimit.

 

If (Max Server Memory < ullAvailPageFile)

{

Target2= Max Server Memory

}

Else

{

Target2=Total Physical Memory

}

 

Target Server Memory (KB) =Minimum (Target1,Target2)

 

So if AvailablePhysicalMemory is very high (or) when MaxServermemory is low then Target Server Memory (KB) would give you the MaxServerMemory  else value derived from above formula.

 

 

 

Thanks

Karthick P.K

Posted in Performance, SQL Server Engine, SQL Server memory | Tagged: , | Leave a Comment »

SQL Server performance degraded in 32-Bit SQL Server after adding additional RAM.

Posted by Karthick P.K on May 18, 2012

 

Do you know that adding additional RAM can affect the performance of SQL Server Sometimes?

 

SQL Server performance degraded in 32-Bit SQL Server after I added additional RAM or Do you see SQL Server memory errors after adding RAM ?

 

Following SQL Server Memory errors are logged in SQL error log after adding additional RAM:

 

Errors:

SQL Server 2005/2008

    Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 122880

 

Buffer Pool errors:

    BPool::Map: no remappable address found.

 

Either BPool or MemToLeave errors:

    Error: 17803 “Insufficient memory available..”

    Buffer Distribution:  Stolen=7901 Free=0 Procedures=1 Inram=201842 Dirty=0 Kept=572…

 

Extract from http://mssqlwiki.com/sqlwiki/sql-performance/basics-of-sql-server-memory-architecture/

 

{

SQL Server "User address space" is broken into two regions: MemToLeave and Buffer Pool

Size of MemToLeave (MTL) and Buffer Pool (BPool) is determined by SQL Server during start up as below.

 

MTL (Memory to Leave)= (Stack size * max worker threads) + Additional space to load Dll’s.

Stack size =512 KB per thread for 32 Bit SQL Server

I.e. = (256 *512 KB) + 256MB =384MB

 

Additional space to load Dll’s= 256 MB from SQLServer2000. This space is used to store COM objects, Extended stored procedure, Linked server in SQL Server process

 

Note: Additional space to load Dll’s can be modified using -g startup parameter.

 

on any machine with less than 4 processors the Maximum worker Thread’s is

always 256 by default (unless we change the value using SP_configure)

 

 

SQL Server Buffer Pool is minimum of “Physical RAM “ or “user mode memory(2GB or 3GB) – MTL-  BUF structures”

 

BPool = Minimum (Physical memory, User address space – MTL) – BUF structures

 

}

 

 

When AWE is enabled in 32-Bit SQL Server M_pbuf (part of BUF structures) which is mentioned earlier is calculated and allocated for entire physical memory on the system . Regardless of “MAX Server Memory”   This is to adjust Max server memory without restarting SQL Server.

 

SQL Server requires 8MB to create M_pbuf for every 1GB of RAM available on the server.

 

Machine with 64 GB RAM can consume 64 (RAM) *8MB (M_pbuf for each GB) =512 MB just for the BUF array alone.

 

So the amount of BPOOL available for SQL Server is adversely affected.

 

Going back to the previous formula for BPOOL. Size of Bpool for 32-Bit SQL Server with AWE enabled and 64 GB of RAM would be.

 

BPool = Minimum (Physical memory, User address space – MTL) – BUF structures

 

BPool= Minimum (64GB, (2GB-384MB)) – BUF structures (512+ MB)

 

Bpool would approximately become 1GB.  Since size BPOOL become very small we might end up with memory errors.

 

Note:  In 32-Bit SQL Server Only data pages an index pages can be placed in AWE memory. So the memory available for other SQL Server memory objects is still limited to BPOOL and MTL.  

 

How to resolve this issue?

Remove few GB of RAM from server J if you can convince your management that removing RAM will improve performance.

(Or)

There is a startup trace flag TF 836 which you can use to indicate that BUF’s need to be allocated only for the configured max server memory setting. Enable this Trace Flag (836) and Reduce the “MAX Server Memory” of SQL Server.

(Or)

Enable /3GB. This will increase the Size of SQL Server BPOOL by 1GB providing relief to SQL Server BPOOL pressure.

Note: When the physical RAM in the system exceeds 16 GB and the /3GB switch is used, the operating system will ignore the additional RAM until the /3GB switch is removed.

 

 

 

 

Thanks

Karthick P.K

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Transaction log for the database is growing and system SPID is holding open transaction

Posted by Karthick P.K on May 18, 2012

Transaction log for the database is growing and system SPID is holding open transaction

Select log_reuse_wait_desc From Sys.databases where name=’%’ will return

‘ACTIVE_TRANSACTION’

DBCC Opentran says there is a Opentran held by system thread (Similar to SPID (server process ID): 7s and 6s in below example)

Oldest active transaction:

SPID (server process ID): 7s

UID (user ID) : -1

Name : user_transaction

LSN : (543263:28204:1)

Start time : Dec 8 2011 11:02:19:483PM

SID : 0×01

Replicated Transaction Information:

Oldest distributed LSN : (544101:227459:27)

Oldest non-distributed LSN : (543263:28204:1)

Oldest active transaction:

SPID (server process ID): 6s

UID (user ID) : -1

Name : tran_sp_MScreate_peer_tables

LSN : (958510:111529:1)

Start time : May 6 2012 5:33:37:240AM

SID : 0×01

Replicated Transaction Information:

Oldest distributed LSN : (962272:93878:5)

Oldest non-distributed LSN : (0:0:0)

If we see Name: tran_sp_MScreate_peer_tables in DBCC opentran

Follow http://support.microsoft.com/kb/2509302 ie. Restart SQL-Server and wait for Script upgrade to complete and then start the SQL Server Agent.

If you see Name : user_transaction then script upgrade for database has failed leaving an open transaction. Verify the SQL Server error log to identify why the script upgrade has failed.

We can enable Trace flag -T3601 which causes the first 512 characters of each batch being executed to be printed to the error log while doing script upgrade . Identify the batch which is failing and troubleshoot the batch.

For more information Follow : http://mssqlwiki.com/2010/11/17/sqlserver2008-script-level-upgrade-for-database-master-failed-because-upgrade-step-sqlagent100_msdb_upgrade-sql-encountered-error-574-state-0-severity-16/

Thanks

Karthick P.K

 

Posted in Space management, SQL Server Engine, Startup failures | Tagged: , , , , , | Leave a Comment »

Copy database wizard or replication setup might fail due to broken dependency

Posted by Karthick P.K on May 4, 2012

Copy database wizard would fail with below error while creating views or user defined functions if the dependency lists of objects is  broken.

Error:

failed with the following error: “Invalid object name ‘dbo. .”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()

at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()

at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()

Replication setup would also fail while applying the scripts if the dependency lists of objects is broken.

Error:

The schema script ‘XXX_4.sch’ could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)

Get help: http://help/MSSQL_REPL-2147201001

Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL20164)

Get help: http://help/MSSQL_REPL20164

Invalid object name ‘. (Source: MSSQLServer, Error number: 208)

Get help: http://help/208

--Below script will fix the broken dependencies on all the objects
----------------------------------------------------------------------------
--List of objects for which referenced objects are missing.
--ex: View created on table XYZ and table XYZ is dropped
----------------------------------------------------------------------------
SELECT OBJECT_NAME (referencing_id),referenced_database_name, referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_entity_name not in (select name from sysobjects)

create table #t_excluded_modules (module_name sysname)
go

create table #t_modules_refreshed_in_end (module_name sysname)

go

------------------------------------------------------------------------------
--
-- get the list of modules whose dependencies have to be refreshed
--
-- Comment:
-- in the list we're not considering procedures or triggers because
-- because they can be created in any order, which means they can be refreshed
-- in any order
--
------------------------------------------------------------------------------
create table #t_user_views_or_tables (module_id int)
insert into #t_user_views_or_tables(module_id)
		select object_id from sys.objects where
			type in ('V', 'FN', 'IF', 'TF')
			and name not like 'MSMerge%'
			and is_ms_shipped <> 1
			and name not in (select * from #t_modules_refreshed_in_end)
			and name not in (select * from #t_excluded_modules)

insert into #t_user_views_or_tables(module_id)
		select object_id from sys.objects where
			name in (select * from #t_modules_refreshed_in_end)

----------------------------------------
--
-- get the dependency table
-- |---------------------------------|
-- |  referencing_id | referenced_id |
-- | ----------------|---------------|
-- |      XXX        |     XXX       |
-- | ----------------|---------------|
--
----------------------------------------
Declare @module int, @message varchar(1000), @str nvarchar(1000)
create table #t_dependency_table (referencing_id int, referenced_id int)
DECLARE modules_cursor CURSOR FOR SELECT module_id FROM #t_user_views_or_tables
open modules_cursor
fetch next from modules_cursor into @module

IF @@FETCH_STATUS <> 0
	PRINT '            <<None>>	No module to refresh'

while @@FETCH_STATUS = 0
	begin
		select @str = quotename(schema_name(objectproperty(@module, 'schemaid'))) + '.' + quotename(object_name(@module))
		select @message = '            trying to refresh ' + @str
		print @message
		exec sys.sp_refreshsqlmodule @str
		select @message = '            ' + @str + ' was refreshed'
		print @message

		insert into #t_dependency_table (referencing_id, referenced_id)
			select distinct object_id as referencing_id, referenced_major_id as referenced_id
				from sys.sql_dependencies
				where object_id <> referenced_major_id      -- to avoid self recursion for functions
					  and object_id = @module

		fetch next from modules_cursor into @module
	end

close modules_cursor
deallocate modules_cursor

-------------------------------------------------------------------------------------------
--
-- get the the bottom of the dependency list i.e. independent modules
-- i.e.
-- get the list of referenced_ids in the dependency table which
-- don't occur in the referencing_ids column
--
-- Comment:
-- if there are circular dependencies then the few modules which form a circular dependency
-- would be ignored in the independent modules list
--
-------------------------------------------------------------------------------------------
create table #t_independent_modules (modules int)

insert into #t_independent_modules (modules)
	select #t_dependency_table.referenced_id from
			#t_dependency_table left outer join #t_dependency_table t2
			on #t_dependency_table.referenced_id = t2.referencing_id
	where
			t2.referencing_id is NULL

-------------------------------------------------------------------------------------------
--
-- build the ordered list of dependencies starting with the independent modules
-- in the beginning first few rows, ones dependent on it in the following rows and so on...
--
-- there can be tricky cases of dependencies such as
-- V1 -> V2 -> V3
--  |           ^
--  +-----------+
--
-- in the above example the refresh order would be V3, V1, V2, V1.
-- note that V1 is being refreshed twice, the last refresh of V1 after V2 is important.
--
-------------------------------------------------------------------------------------------
create table #t_final_dependency_list (id_num int IDENTITY(1,1), modules int)

while exists (select * from #t_independent_modules)
	begin
		-- append the set of independent modules into a list
		insert into #t_final_dependency_list select * from #t_independent_modules

		-- get the set of dependent modules
		select distinct #t_dependency_table.referencing_id into #temp_table
			from #t_dependency_table
			where #t_dependency_table.referenced_id in (select * from #t_independent_modules)

		-- clear up the list of independent modules
		truncate table #t_independent_modules

		-- the dependent modules now become the independent modules
		insert into #t_independent_modules select * from #temp_table

		-- delete the dependent modules list
		drop table #temp_table
	end

-----------------------------------------------------------
--
-- refresh the modules once more but in the right order now
--
-----------------------------------------------------------
declare modules_cursor_final cursor for
	select modules from #t_final_dependency_list order by #t_final_dependency_list.id_num

open modules_cursor_final
fetch next from modules_cursor_final into @module

IF @@FETCH_STATUS <> 0
	PRINT '            <<None>>	No module to refresh'

while @@FETCH_STATUS = 0
	begin
		if (select type from sys.objects where object_id = @module) in ('V', 'FN', 'IF', 'TF')
			and (select is_schema_bound from sys.sql_modules where object_id = @module) = 0
			begin
				select @str = quotename(schema_name(objectproperty(@module, 'schemaid'))) + '.' + quotename(object_name(@module))
				select @message = '            trying to finally, once more, refresh ' + @str
				print @message
				exec sys.sp_refreshsqlmodule @str
				select @message = '            ' + @str + ' was finally refreshed once again'
				print @message
			end

		fetch next from modules_cursor_final into @module
	end

close modules_cursor_final
DEALLOCATE modules_cursor_final

-----------
--
-- cleanup
--
-----------
drop table #t_excluded_modules
drop table #t_modules_refreshed_in_end
drop table #t_user_views_or_tables
drop table #t_dependency_table
drop table #t_independent_modules
drop table #t_final_dependency_list

Posted in SQL General, Replication, Copy database wizard | Tagged: , , | Leave a Comment »

SQL Server Agent is taking long time to start

Posted by Karthick P.K on April 19, 2012

 

SQL Server Agent might take long time to start because of slow communications with Certificate Authorities.

 

If you enable verbose logging for SQL Server agent (-v) and look at the SQL Server agent log you will notice that ‘ANALYSISQUERY’ subsystem has taken long time to start

2012-02-15 15:42:42 – ? [124] Subsystem ‘QueueReader’ successfully loaded (maximum concurrency: 800)

2012-02-15 15:47:08 – ? [124] Subsystem ‘ANALYSISQUERY’ successfully loaded (maximum concurrency: 800)

2012-02-15 15:47:08 – ? [124] Subsystem ‘ANALYSISCOMMAND’ successfully loaded (maximum concurrency: 800)

 

Also if you collect dumps during the SQLServer agent startup you will notice the stack like one below.

 

ntdll!ZwWaitForSingleObject

kernel32!WaitForSingleObjectEx

cryptnet!CryptRetrieveObjectByUrlWithTimeout

cryptnet!CryptRetrieveObjectByUrlW

crypt32!ChainRetrieveObjectByUrlW

crypt32!CCertChainEngine::RetrieveCrossCertUrl

crypt32!CCertChainEngine::UpdateCrossCerts

crypt32!CCertChainEngine::Resync

crypt32!CCertChainEngine::CreateChainContextFromPathGraph

crypt32!CCertChainEngine::GetChainContext

crypt32!CertGetCertificateChain

wintrust!_WalkChain

wintrust!WintrustCertificateTrust

wintrust!_VerifyTrust

wintrust!WinVerifyTrust

mscorsec!GetPublisher

mscorwks!PEFile::CheckSecurity

mscorwks!PEAssembly::DoLoadSignatureChecks

mscorwks!PEAssembly::PEAssembly

mscorwks!PEAssembly::DoOpenHMODULE

mscorwks!PEAssembly::OpenHMODULE

mscorwks!AppDomain::BindExplicitAssembly

mscorwks!AppDomain::LoadExplicitAssembly

mscorwks!ExecuteDLLForAttach

mscorwks!ExecuteDLL

mscorwks!CorDllMainForThunk

mscoree!CorDllMainWorkerForThunk

mscoree!VTableBootstrapThunkInitHelper

mscoree!VTableBootstrapThunkInitHelperStub

SQLAGENT!LoadSubsystem

SQLAGENT!StartSubSystems

SQLAGENT!DumpAndCheckServerVersion

SQLAGENT!ServiceMain

advapi32!ScSvcctrlThreadW

kernel32!BaseThreadInitThunk

ntdll!RtlUserThreadStart

 

ANALYSISQUERY subsystem has assembly which has an Authenticode signature. When the CLR loads an assembly which has an Authenticode signature, it will always try to verify that signature.

This verification can be quite time intensive, since it can require hitting the network several times to download up to date certificate revocation lists, and also to ensure that there is a full chain

of valid certificates on the way to a trusted root.

If you can’t get to the internet to authenticate signature or want to bypass the Authenticode signature you can try creating a sqlagent.exe.config file with the following xml in Binn directory. This bypasses the check

 

Create a sqlagent.exe.config file with:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<runtime>
<generatePublisherEvidence enabled="false"/>
</runtime>
</configuration>

 

Thanks

Karthick P.K

Posted in SQL General | Tagged: , | Leave a Comment »

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

Posted by Karthick P.K on April 19, 2012

SQL Server might fail to start with below error

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

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

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

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

spid7s An error occurred during decryption.

Cause

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

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

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

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

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

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

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

 

Source  code for Exe is below

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

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

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

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


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

else
{

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

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

}

 

 

Thanks

Karthick P.K

Posted in Configuration, Security, Startup failures | Tagged: , , | Leave a Comment »

Multi Threaded OVELAPPED and Nonbuffered I/O Example

Posted by Karthick P.K on March 4, 2012

How to Read file using Multiple threads OVERLAPPED and Nonbuffered I/O

Multithreaded Overlapped I/O and Nonbuffered I/O example

Nonbuffered I/O :Allows application to bypass the Windows cache manager or disable system caching of data being read from or written to the file .So there is no intermediate buffer or cache and gives direct control over data I/O buffering to application.

Things to remember:

1. When we use non buffered I/O (FILE_FLAG_NO_BUFFERING in createfile) reads and writes has to be in multiple of bytes per sector.

2. GetDiskFreeSpace-Retrieves information about the specified disk, including Bytes per sector (dwBytesPerSector) of disk.

3. When using non buffered I/O (FILE_FLAG_NO_BUFFERING) file offset in the OVERLAPPED structure in Readfile/Writefile if specified, must be number of bytes that is an integer multiple of the Bytes per sector

4. Buffers used for read and write operations should be physical sector-aligned, which means aligned on addresses in memory that are integer multiples of Bytes per sector.

5. GetFileSizeEx-Retrieves the size of the specified file. When multiple threads are used to read or write file. Each thread needs to have its own overlapped structure and Event has to be created for hEvent member of each overlapped structure.

If the hEvent member of the OVERLAPPED structure is NULL, the system uses the state of the hFile handle to signal when the operation has been completed. This will cause confusion when multiple threads are using same file handle to read or write file.

It is safer to use an event object because of the confusion that can occur when multiple simultaneous overlapped operations are performed on the same file, named pipe, or communications device.In this situation, there is no way to know which operation caused the object’s state to be signaled.

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

#define ThreadPerProc 1 //If you want more than 1 thread per processor increase this value.

int Dootherwork();

struct SUreadfile
{
int x;
DWORDLONG  dwOffset;
DWORDLONG  dwTotalBytespostedNOffset;

};
  SUreadfile *PSUreadfile;
  int Ureadfile(SUreadfile *PSUreadfile1);

  int  x=0;
  int  i=0;
  SYSTEM_INFO si;
  DWORD dwSectorsPerCluster;
  DWORD dwBytesPerSector;
  DWORD dwNumberOfFreeClusters;
  DWORD dwTotalNumberOfClusters;

  //
  LONGLONG BuffSize=0;
  OVERLAPPED *iAIO=NULL;
  DWORDLONG  dwTotalBytesposted=0;
  DWORDLONG  dwTotalBytespostedwithinoffset=0;
  HANDLE hIFile;
  HANDLE hOFile;
  HANDLE *hEvent;
 char dateStr [9];
 char timeStr [9];

int main(int argc, char* argv[])
{

  _strdate( dateStr);
  _strtime( timeStr );
  printf("\n Date:%s\t", dateStr);
  printf("Time is%s\t", timeStr);
  if (argc<3)
  {
		printf("Usage is: To stimulate asynch I/O. Accept two parameters Source file and Destination file");
		return 1;
  }

 //Open source file
 hIFile=CreateFile((LPCSTR)argv[1],GENERIC_READ,FILE_SHARE_READ,NULL,OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL
                           | FILE_FLAG_OVERLAPPED
                           | FILE_FLAG_NO_BUFFERING
                           ,NULL); //FILE_FLAG_OVERLAPPED  - to process input or output asynchronously
								   //FILE_FLAG_NO_BUFFERING-The file or device is being opened with no system caching for data reads and writes.
 if (INVALID_HANDLE_VALUE==hIFile)
		  {
			printf("Unable to open file %s.  Error=%d\n",argv[1], GetLastError());
			return 1;
		  }
//Create destination file
 hOFile=CreateFile((LPCSTR)argv[2],GENERIC_WRITE,FILE_SHARE_READ,NULL,CREATE_ALWAYS, FILE_ATTRIBUTE_NORMAL,NULL);
	 if (INVALID_HANDLE_VALUE==hOFile)
		  {
			printf("Unable to create file %s.  Error=%d\n",argv[1], GetLastError());
			return 1;
		  }

//GetSystemInfo- Retrieves information about the current system. Output is pointer to SYSTEM_INFO structure
 GetSystemInfo(&si);
 /*GetDiskFreeSpace-Retrieves information about the specified disk, including dwBytesPerSector . When using FILE_FLAG_NO_BUFFERING
 file offset in the OVERLAPPED structure and nNumberOfBytesToRead in Readfile
 if specified, must be   number of bytes that is an integer multiple of the dwBytesPerSector*/
 GetDiskFreeSpace(NULL,&dwSectorsPerCluster,&dwBytesPerSector,&dwNumberOfFreeClusters,&dwTotalNumberOfClusters);
 LARGE_INTEGER *fsize;
 fsize =new LARGE_INTEGER;

 dwBytesPerSector=dwBytesPerSector*4; //for larger files if you want to read data greater then dwBytesPerSector in one shot increase the size of dwBytesPerSector.

//GetFileSizeEx-Retrieves the size of the specified file.

 GetFileSizeEx (hIFile,fsize);
 if (fsize->QuadPart==0)
      {
      printf("\nUnable to get the size of file. Error:%d ",GetLastError());
      return 1;
      }
    else
      {
          printf("\nFile size is: %lld Bytes",fsize->QuadPart);

		  if (fsize->QuadPart > dwBytesPerSector)
          {
			  /*Later in this program we create 1 thread per processor and read the files parellely.
			  Below logic is used to calculate the bytes each thread will read
			  remember when we use non buffered I/O (FILE_FLAG_NO_BUFFERING) reads has to be in multiple of bytes per sector. So no of bytes each thread
			  will read has to be rouded to   bytes per sector
			  */

			  BuffSize=  fsize->QuadPart +  ( (si.dwNumberOfProcessors*dwBytesPerSector)  -  (fsize->QuadPart % (si.dwNumberOfProcessors*dwBytesPerSector))  );
			  BuffSize=BuffSize/si.dwNumberOfProcessors;
          }
          else
          {
              BuffSize=fsize->QuadPart; //If the file size is less then dwBytesPerSector there is no nead to create multiple threads
          }
      }

	iAIO =new OVERLAPPED[si.dwNumberOfProcessors*ThreadPerProc];// Create overlapped structure for each thread
	HANDLE *h;
    h = new HANDLE[(si.dwNumberOfProcessors*ThreadPerProc)]; // Pointer  array to hold thread handles
	PSUreadfile= new SUreadfile[256];
	int OffsetHigh=0;
	hEvent=new HANDLE[(si.dwNumberOfProcessors*ThreadPerProc)];
	/*Create event for hEvent member of each overlapped structure If the hEvent member of the OVERLAPPED structure is NULL, the system
	uses the state of the hFile handle to signal when the operation has been completed.
	This is will cause confusion when multiple threads are using same file handle to read or write file.
	It is safer to use an event object because of the
	confusion that can occur when multiple simultaneous overlapped operations are performed on the same file, named pipe, or communications device.
	In this situation, there is no way to know which operation caused the object's state to be signaled.*/

	//Create threads and and assign the starting file offset to each thread for read operation
	for ( i=0;i<(si.dwNumberOfProcessors*ThreadPerProc);i++)
			{
			ZeroMemory(&PSUreadfile[i],sizeof(PSUreadfile[i]));
 			PSUreadfile[i].x=i;
			ZeroMemory (&iAIO[i],sizeof(iAIO[i]));
			iAIO[i].Internal=0;
			iAIO[i].InternalHigh=0;
			iAIO[i].Offset=0;
			iAIO[i].OffsetHigh=0;
			iAIO[i].Pointer=0;
			iAIO[i].Offset= 0;
			PSUreadfile[i].dwOffset=dwTotalBytesposted;
			hEvent[i] = CreateEvent(NULL, TRUE, FALSE, NULL);
			if(hEvent)
			{
			 iAIO[i].hEvent = hEvent[i];
			}
			 else
			{
			 printf("\nCreate event failed with error:%d",GetLastError());
			}

			h[i]=CreateThread(0,0,(LPTHREAD_START_ROUTINE  )Ureadfile,(LPVOID)&PSUreadfile[i],  0,  NULL);

			if (!h[i])
			{
			printf("Thread creation failure :", GetLastError());
			}

			dwTotalBytesposted=dwTotalBytesposted+BuffSize;

		}

	WaitForMultipleObjects((si.dwNumberOfProcessors), h,TRUE,INFINITE);  // Wait for all the threads to complete
  _strdate( dateStr);
  _strtime( timeStr );
  printf("\n Date:%s\t", dateStr);
  printf("Time is%s\t", timeStr);

}

int Ureadfile(SUreadfile *PSUreadfile1)
{

	int z=PSUreadfile1->x;
	SIZE_T dwSize=dwBytesPerSector;
	if (PSUreadfile[z].dwOffset>MAXDWORD)
	{
		iAIO[z].Offset=(PSUreadfile[z].dwOffset%MAXDWORD)-(PSUreadfile[z].dwOffset/MAXDWORD); //Offset start from zero
		iAIO[z].OffsetHigh=PSUreadfile[z].dwOffset/MAXDWORD;

	/*iAIO[z].Offset (Offset of overlapped structure) is the low-order portion of the file position at which to start the I/O request.
	Data type of Offset is DWORD so the 	maximum size is 2^32 (4294967296-1). If you want to read the file from offset
	which is greater than 4294967296 bytes increase the OffsetHigh */
	}
	else
	{
	iAIO[z].Offset=PSUreadfile[z].dwOffset;
	}

	DWORD dwBytesRead=0;
	LONGLONG dwTotalBytesRead=0;
	BOOL	RF;
	BOOL	WF;
	wchar_t *IBuffer = (wchar_t *) VirtualAlloc(NULL,  dwSize, MEM_RESERVE| MEM_COMMIT,PAGE_READWRITE);

	 while (BuffSize>dwTotalBytesRead)
	{

		RF=0;

		RF=ReadFile(hIFile,IBuffer,dwSize, &dwBytesRead,&iAIO[z]); // pass  a pointer to an OVERLAPPED structure (iAIO)

		if ((RF==0) && GetLastError()==997)      //ERROR_IO_PENDING                 997L
        {

			/*bWait parameter of GetOverlappedResult can be set to TRUE or FALSE.
			If this parameter is TRUE, and the Internal member of the lpOverlapped structure is STATUS_PENDING, the function
			does not return until the operation has been completed. If this parameter is FALSE and the operation is still pending, the function
			returns FALSE and the GetLastError function returns ERROR_IO_INCOMPLETE	*/
            while( !GetOverlappedResult( hIFile,&iAIO[z],&dwBytesRead,TRUE))
                {

                    if (GetLastError()==996)//ERROR_IO_INCOMPLETE  (Not signaled)            996L
                    {
                    printf("\nI/O pending: %d .",GetLastError());
                    //Dootherwork();
					/*If the bWait parameter of GetOverlappedResult is set to false thread can do other work while the I/O is progressing.
					Change the bWait parameter to FALSE in GetOverlappedResult and un-comment Dootherwork function above to stimulate clean overlapped I/O.
					*/
                    }
                    else if  (GetLastError()==38) //ERROR_HANDLE_EOF                 38L
                    {
                    printf("\nEnd of file reached.");
                    break;
                    }
                    else
                    {
                    printf("GetOverlappedResult failed with error:%d,Offset:%d",GetLastError(),iAIO[z].Offset);
                    break;
                    }

                }

        }
        else if ((RF==0)  && GetLastError()!=997 &&   GetLastError()!=38 )
        {
            printf ("\nError reading file :%d offset-%d",GetLastError(),iAIO[z].Offset);
            return 0;
        }

		else if ((RF==0)  && GetLastError()==38 )
        {
            printf ("\nEnd of file reached file :%d offset:%d",GetLastError(),iAIO[z].Offset);
            return 0;
        }

		WF= WriteFile(hOFile,IBuffer,iAIO[z].InternalHigh,NULL,&iAIO[z]);  //Write the buffers which we read to new file.

		if (!WF)
		 {
			printf("\nWrite file operation failed. Error:%d",GetLastError());
		 }

	    dwTotalBytesRead=dwTotalBytesRead + iAIO[z].InternalHigh;

		//Increase the offset if we hit the max DWORD limitation
		if (iAIO[z].Offset+iAIO[z].InternalHigh > (MAXDWORD* (iAIO[z].OffsetHigh+1)))
		{
printf("\nThread Id-%d completed ReadFile operation from %lld till  %lld of %lld bytes",z,(iAIO[z].Offset-dwTotalBytesRead-iAIO[z].InternalHigh),iAIO[z].Offset+iAIO[z].InternalHigh,dwTotalBytesRead);
					iAIO[z].OffsetHigh++;
			        iAIO[z].Offset= (iAIO[z].Offset + iAIO[z].InternalHigh-MAXDWORD-iAIO[z].OffsetHigh); //Offset starts from zero
		}
		else
		{
		iAIO[z].Offset=iAIO[z].Offset+iAIO[z].InternalHigh;
		}

		if (dwSize >iAIO[z].InternalHigh)
		{
		  printf("\nEnd of file reached %ld-%ld",iAIO[z].InternalHigh,dwSize);
		 break;
		}

	}

	printf("\nThread Id:%d completed ReadFile operation  of %lld bytes",z,dwTotalBytesRead);
  return 1;
}

int Dootherwork()
{

	x=x+1;
	printf("\nWe are doing other work when overlapped I/O read is in progress-%d -Sleeping for 1 Milli second",x);
	Sleep(1);
	return 0;
}

Posted in Programming, SQL Server I/O | Tagged: , , , , | Leave a Comment »

Asynchronous I/O example

Posted by Karthick P.K on February 15, 2012

How to Read file using asynchronous read operations – Overlapped I/O example

Asynchronous I/O facility in windows allows an application to initiate an I/O operation and continue other operation’s while I/O completes. This will improve the performance of an application because it allows the application to do multiple operations at once.

1. FILE_FLAG_OVERLAPPED switch is used in CreateFile to do Asynchronous I/O operation.

2. OVERLAPPED structure – when we call ReadFile/ReadFileEx or WriteFile/WriteFileEx we pass Pointer to an OVERLAPPED structure that specifies the starting position of I/O operation.

3. GetOverlappedResult – Status of a pending asynchronous operation can be checked using HasOverlappedIoCompleted or GetOverlappedResult Win32 API functions. bWait parameter in GetOverlappedResult can be set to true to wait infinitely till the Asynchronous I/O operation completes.

4. Createevent – Used to create an event object to assign for hEvent member of the OVERLAPPED structure passed into ReadFile/WriteFile and GetOverlappedResult. When multiple Asynchronous I/O happens   in parallel then each Asynch I/O must have its own OVERLAPPED structure

#include "windows.h"
#include "stdlib.h"
#include <windows.h>
#include <string>
#include <winbase.h>
#include <iostream>
using namespace std;
#include <psapi.h>
#pragma comment(lib,"psapi.lib")
#define BUF_SIZE 8192*100*100 // BuffSize for read file
int  x=0; 
int Dootherwork();

int main(int argc, char* argv[])
{
  if (argc<2)
  {
        printf("Usage is: To stimulate asynch I/O");
        return 1;
  }

HANDLE hIFile=CreateFile((LPCSTR)argv[1],GENERIC_READ,FILE_SHARE_READ,NULL,OPEN_EXISTING,FILE_FLAG_OVERLAPPED,NULL); //FILE_FLAG_OVERLAPPED-To process input or output asynchronously
    
 if (INVALID_HANDLE_VALUE==hIFile) 
          {
            printf("Unable to open file %s.  Error=%d\n",argv[1], GetLastError());
            return 1;
          }

     OVERLAPPED iAIO;  
     ZeroMemory (&iAIO,sizeof(iAIO));

      DWORD dwBytesRead=0;
      DWORD dwTotalBytesRead=0;
      BOOL RF;
      BOOL OLR=0;
      DWORD *lpFileSizeHigh;
      DWORD rfs; 
      
      lpFileSizeHigh = new DWORD;
      LARGE_INTEGER *fsize;
      fsize =new LARGE_INTEGER;
      rfs=GetFileSizeEx (hIFile,fsize);
      LONGLONG BuffSize=0;
      HANDLE hEvent;
      hEvent = CreateEvent(NULL, TRUE, FALSE, NULL);
      if(hEvent)
      {
      iAIO.hEvent = hEvent; 
      }
      else
      {
      printf("\nCreate event failed with error:%d",GetLastError());
      }
  
    if (fsize->QuadPart==0)
      {
      printf("\nUnable to get the size of file. Error:%d ",GetLastError());
      return 1;
      }
    else 
      { 
          printf("\nFile size is: %lld Bytes",fsize->QuadPart);
          if (fsize->QuadPart > BUF_SIZE)
          {
          BuffSize=BUF_SIZE;
          }
          else
          {
          BuffSize=fsize->QuadPart;
          }
      }
    wchar_t *IBuffer = (wchar_t *)HeapAlloc(GetProcessHeap(),  HEAP_ZERO_MEMORY,  BuffSize);
  

  while (fsize->QuadPart>dwTotalBytesRead)
  {

      SetLastError(0);
      
      RF=ReadFile(hIFile,IBuffer,BuffSize, NULL,&iAIO); // pass  a pointer to an OVERLAPPED structure (iAIO)
        
      if ((RF==0) && GetLastError()==997)      //ERROR_IO_PENDING                 997L   
        {
            printf ("\nAsynch readfile started. I can do other operations now");
             
            while( !GetOverlappedResult( hIFile,&iAIO,&dwBytesRead,FALSE))
                {
                    if (GetLastError()==996)//ERROR_IO_INCOMPLETE  (Not signaled)            996L 
                    {
                    printf("\nI/O pending: %d .",GetLastError());
                    Dootherwork();
                    }
                    else if  (GetLastError()==38) //ERROR_HANDLE_EOF                 38L
                    { 
                    printf("\nEnd of file reached.");
                    break;
                    } 
                    else
                    {
                    printf("GetOverlappedResult failed with error:%d",GetLastError());
                    break;
                    }

                } 
    



        }
        else if ((RF==0)  && GetLastError()!=997 )
        {
            printf ("Error reading file :%d",GetLastError());
            return 0;
        }
      
         

        dwTotalBytesRead=dwTotalBytesRead + iAIO.InternalHigh;
        iAIO.Offset=dwTotalBytesRead;
        printf("\nReadFile operation completed for %lld bytes",dwTotalBytesRead);
    
  }
  printf("\nReadFile  completed.  %d bytes read",dwTotalBytesRead);

 ResetEvent(iAIO.hEvent);
 HeapFree(GetProcessHeap(),0,IBuffer);
 CloseHandle(hEvent);

 CloseHandle(hIFile);
 return 0;
}


int Dootherwork()
{

    x=x+1;
    printf("\nWe are doing other work when overlapped I/O read is in progress-%d -Sleeping for 1000 Milli second",x);
    Sleep(1000);
    return 0;
}

 

 

Thanks

Karthick P.K

Posted in Programming, SQL Server Engine, SQL Server I/O | Tagged: , , , , , | Leave a Comment »

SQL-Server resource fails to come online IS Alive check fails

Posted by Karthick P.K on January 31, 2012

SQL-Server resource fails to come online with

Error:

[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 35; message = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible.

Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

 

Resolution:

Look at the version of (c:\windows\system32\sqlsrvres.dll) and install the same version of SQL Server native client.

Cause:

When Higher version of SQL-Server is installed on a cluster in which lower version of SQL Server is already installed, the lower version SQL Server Resource DLL (c:\windows\system32\sqlsrvres.dll) is upgraded to higher version and Higher resource DLL will be loaded

by the resource monitor process to monitor Lower version as well.

For example: The Denali SQL Server Resource uses SNAC 10.0 to connect to the SQL instance and because SNAC 10.0 can be used to connect to Shiloh, Yukon and Katmai as well this side by side configuration will work. However if Denali is uninstalled, the Denali

SQL Server resource DLL is not downgraded to Katmai, Yukon or Shiloh version and hence care should be taken to not uninstall SNAC 10.0 otherwise Yukon or Shiloh instance cannot be brought online.

Similarly When we install Yukon and Shiloh together, Yukon SQL Server Resource uses SNAC to connect to the SQL instance and because SNAC can be used to connect to Shiloh as well this side by side configuration will work. However if Yukon is uninstalled, the Yukon SQL Server resource DLL is not

downgraded to Shiloh version and hence care should be taken to not uninstall SNAC otherwise Shiloh instance cannot be brought online.

 

Thank You and Best Regards,

Karthick P.K

Posted in SQL General | Tagged: , , | Leave a Comment »

The backup of the file or filegroup "" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

Posted by Karthick P.K on January 25, 2012

Database backup fails with below error after piecemeal restore?

 

I do not have backup for some of file groups and I do not want the data in those file group.

How to drop offline file and file groups after piecemeal restore?  I don’t have the backup for some of my file groups.

 

I came across some of the customers with large databases schedule file group backup for some of file groups in database and ignore some file groups which do not have critical information. They do piece meal restore for file groups for which they have backup and bring the database online during disaster.

They end with below error message when they take backup of database which is piece meal restored. How do we Fix?

 

 

 

Error1

 

Msg 3007, Level 16, State 1, Line 1

The backup of the file or filegroup "Test2" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

Msg 3013, Level 16, State 1, Line 1

 

Error2

 

BACKUP DATABASE is terminating abnormally.

 

Backup failed for Server ‘Test\kj’.  (Microsoft.SqlServer.SmoExtended)

 

——————————

ADDITIONAL INFORMATION:

 

System.Data.SqlClient.SqlError: The backup of the file or filegroup "Test2" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)

 

 

Error3

 

We also get below error when we try to drop the file which is no more required.

 

 

Msg 5056, Level 16, State 2, Line 1

Cannot add, remove, or modify a file in filegroup ‘Secondary’ because the filegroup is not online.

 

 

 

 

 

If the unrestored file group is no longer required they can be removed using below queries. Many people get stuck when they

get “Cannot add, remove, or modify a file in filegroup ‘ ’ because the filegroup is not online.“ while they remove file.

This error can be ignored and we can proceed to removing the file group as mentioned in below query.

 

 

 

ALTER DATABASE database_name REMOVE FILE file_name – If this query fails proceed to next file or Remove file group

 

ALTER DATABASE database_name REMOVE FILEGROUP filegroup_name

 

Once we issue the REMOVE FILEGROUP query to offline file group they will get in to defunct state and we can proceed taking the database backups like any other normal database.

 

Sample query to reproduce the above error is below

 

 

 

 

USE [master]

go

create database Test

 

GO

ALTER DATABASE [Test] ADD FILEGROUP [Secondary]

GO

 

ALTER DATABASE [Test] ADD FILE ( NAME = N’Test2′, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.KJ\MSSQL\DATA\Test2.ndf’ , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Secondary]

GO

 

BACKUP DATABASE [Test] FILEGROUP = N’PRIMARY’ TO  DISK = N’D:\Backupstore\PrimaryFilegroupbackup’ WITH NOFORMAT, NOINIT,  NAME = N’Test-Full Filegroup Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

 

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’Test’

GO

USE [master]

GO

ALTER DATABASE [Test] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE [master]

GO

DROP DATABASE [Test]

GO

 

RESTORE DATABASE [Test] FILEGROUP=‘Primary’ FROM  DISK = N’D:\Backupstore\PrimaryFilegroupbackup’  WITH PARTIAL, RECOVERY

Go 

 

–Now the database is online and full database backup would fail with error mentioned above. To resolve issues below queries.

 

 

ALTER DATABASE [Test] REMOVE FILE Test2

go

 

–{

–Error

–=====

–Msg 5056, Level 16, State 2, Line 1

–Cannot add, remove, or modify a file in filegroup ‘Secondary’ because the filegroup is not online.

–}

 

 

–Above error can be ignored and we can proceed to remove filegroup as shown below.

 

ALTER DATABASE [Test] REMOVE FILEGROUP Secondary

go

 

 

–Now we can take normal database backups 

 

 

 

Thank You and Best Regards,

Karthick P.K

 

Posted in Backup/Restore | Tagged: , , , | Leave a Comment »

How to move the LOB data from one file group to other?

Posted by Karthick P.K on January 17, 2012

 

We do not have a direct way to move the LOB data from one file group to other. Using ALTER TABLE and/or CREATE INDEX to support moving LOB data is unavailable till current version of  SQL (SQL Server 2008).

 

Only way to move the LOB data is to

1. create new table in new file group

2. Move the data from existing table to new table.

3. Drop the existing table.

4. Change the name of new table to Old table.

Management studio has easy way to create script for all the above task.

1. In management studio Right click the table –>Design –>change the file group in properties windows (Click View—> properties window if you do not see properties window)

2. Generate Change Script.

clip_image002

3. Script similar to following script is generated.

4. Copy the script and run in Query window.

 

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_BLOB_TABLE

                (

                BLOBName varchar(100) NULL,

                BLOBData varbinary(MAX) NULL

                )  ON [PRIMARY]

                TEXTIMAGE_ON Lob2

GO

ALTER TABLE dbo.Tmp_BLOB_TABLE SET (LOCK_ESCALATION = TABLE)

GO

IF EXISTS(SELECT * FROM dbo.BLOB_TABLE)

                EXEC(‘INSERT INTO dbo.Tmp_BLOB_TABLE (BLOBName, BLOBData)

                                SELECT BLOBName, BLOBData FROM dbo.BLOB_TABLE WITH (HOLDLOCK TABLOCKX)’)

GO

DROP TABLE dbo.BLOB_TABLE

GO

EXECUTE sp_rename N’dbo.Tmp_BLOB_TABLE’, N’BLOB_TABLE’, ‘OBJECT’

GO

COMMIT

 

Thanks

Karthick P.K

Posted in Space management, SQL General, SQL Server Tools | Tagged: | 3 Comments »

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

Posted by Karthick P.K on January 9, 2012

 

Problem Description

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.

}

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.

 

 

Regards

Karthick P.K

Posted in Configuration, Connectivity, Security, SQL Server Tools | Tagged: , , , | 2 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: | Leave a Comment »

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: , | Leave a Comment »

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: , , , , | 2 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

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 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

 

Thanks

Karthick P.K

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: | Leave a Comment »

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.

 

Thanks

Karthick P.K

Posted in SQL Query, SQL Server Tools | Tagged: , , | 6 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

 

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: , | 5 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: , , | Leave a 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: , | Leave a Comment »

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: , , , , , | Leave a Comment »

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

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: , | 1 Comment »

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: | 1 Comment »

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: , , , , | Leave a Comment »

SQLServer2008: 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

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

 

SQL Server 2008 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.

 

 

Resolution

Start SQLServer from command prompt using trace flag –902 to disable script execution

 

1.Turn off Implicit transaction

{

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

GO

RECONFIGURE WITH OVERRIDE

GO

}

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

{

EXEC sp_change_users_login ‘Report’;

}

3. SQLServer not able to create temp_MS_AgentSigningCertificate_database.mdf

Refer: http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/sql-server-2008-service-fails-to-start-after-applying-service-pack-1.aspx

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.

 

Regards,

Karthick P.K

Posted in SQL Cluster Setup, SQL Server Setup, Startup failures | Tagged: , | 3 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: , , , , , | 2 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 | 4 Comments »

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

Posted by Karthick P.K on September 26, 2010

 

Rebuild index

EXEC sp_MSforeachtable @command1="print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘)"

Update statistics

EXEC sp_MSforeachtable ‘UPDATE STATISTICS ? WITH FULLSCAN’

 

Thanks

Karthick

Posted in Optimizer, Performance | Tagged: , | Leave a Comment »

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: , | Leave a Comment »

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 | 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: , , | Leave a Comment »

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 | Leave a 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 | Leave a Comment »

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 | Tagged: , | Leave a Comment »

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: , , , | 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: , | Leave a 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 | Leave a Comment »

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.

 

 

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

 

1)

–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.

 

2)

–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.

 

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:

 

 

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

 

  1. “IsClustered “    Should be no

 

  1. Check browser properties to see if it Standalone

 

  1. 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

 

 

 

 

Regards

Karthick P.K

Posted in Connectivity, SQL Server Tools | Leave a Comment »

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 SQLServer Errorlog

**Dump thread – spid = 0, PSS = 0×0000000000000000, EC = 0×0000000000000000

***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%.

Cause

We get Deadlocked Schedulers error when Scheduler Monitor detects Threads(workers) are not Progressing on schedulers.

Some of common causes are

1.Most of the tasks are waiting on a single resource and SQL Server could not spawn new thread to take new work request and there is no Idle thread to process the new work Request.

2.   Excessive blocking, Very long running Queries executed by all workers, All the threads waiting on some resource.

Steps to analyze "Deadlocked Schedulers" Dumps.

Download and Install Windows Debugger from http://www.microsoft.com/whdc/devtools/debugging/installx86.mspx#b

1. Open Windbg

2. Choose File menu –> select Open Crash Dump –>Select the Dump file (SQLDump000#.mdmp)

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

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

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

 

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 http://mssqlwiki.com/sqlwiki/sql-performance/io-bottlenecks/ for troubleshooting I/O issues.

sqlservr!SOS_Event::Wait

sqlservr!SQLServerLogMgr::WaitLCFlush

sqlservr!SQLServerLogMgr::LogFlush

sqlservr!SQLServerLogMgr::WaitLogFlush

sqlservr!XdesRMFull::Commit

 

Regards

Karthick P.K

Posted in Debugging, Performance | Tagged: , , | Leave a Comment »

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> /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 | Leave a Comment »

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: | Leave a Comment »

“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”   :)

Posted in SQL General, SQL Server Tools, SSMS | Tagged: | 5 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: | Leave a Comment »

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: , , | Leave a Comment »

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, 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

Posted in SQL General, SQL Query | Tagged: | Leave a Comment »

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: | 1 Comment »

How to monitor the Session and query which Consumes Tempdb

Posted by Karthick P.K on January 13, 2010

The total space used by Tempdb consists of  User Objects , Internal Objects , Version Store

and Free Space.

Use  Below Query to Track what is consuming Space in Temp Db.
 

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;

 

 

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 

INNER 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;

 
 
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
 
 

Posted in Performance, SQL General, SQL Query | Tagged: , | Leave a Comment »

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 | Leave a Comment »

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 | Leave a Comment »

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 & 0×8) WHEN 0×8 THEN 1 ELSE 0 END AS readonly,
CASE (status & 0×10) WHEN 0×10 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: | Leave a Comment »

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: | Leave a Comment »

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.

Regards

Karthick P.K

Posted in SQL Cluster Setup, SQL General, SQL Server Setup | Tagged: | Leave a Comment »

Installation of SQLServer2005/2008 Fails on Windows2008 Cluster.

Posted by Karthick P.K on March 26, 2009

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

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 service or application, 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 installtion of SQLServer 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 | Leave a Comment »

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: | Leave a 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: | Leave a 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 | Leave a Comment »

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 SQL Server Engine | 1 Comment »

Queries with table variables are slow in SQL2005 compared with SQL2000 (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.

Regards

Karthick P.K

Posted in Performance | Leave a Comment »

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 | Leave a Comment »

Unable to start SQLServer agent resource on cluster after upgrading to 9.00.3186 or Higher

Posted by Karthick P.K on January 14, 2009

SQLServer agent resource fails to come online on cluster after upgrading to build 9.00.3186 or Higher

 

Error

2008-03-19 23:06:23 – ? [100] Microsoft SQLServerAgent version 9.00.3186.00 (x86
unicode retail build) : Process ID 2428
2008-03-19 23:06:23 – ? [101] SQL Server SNETNAME version 9.00.3186 (0 connection
limit)
2008-03-19 23:06:23 – ? [102] SQL Server ODBC driver version 9.00.3042
2008-03-19 23:06:23 – ? [103] NetLib being used by driver is DBNETLIB.DLL; Local
host server is
2008-03-19 23:06:23 – ? [310] 4 processor(s) and 2560 MB RAM detected
2008-03-19 23:06:23 – ? [339] Local computer is SNETNAME running Windows NT 5.2
(3790) Service Pack 2
2008-03-19 23:06:23 – ? [432] There are 11 subsystems in the subsystems cache
2008-03-19 23:06:38 – ! [364] The Messenger service has not been started – NetSend
notifications will not be sent
2008-03-19 23:06:38 – ? [129] SQLSERVERAGENT starting under Windows NT service
control
2008-03-19 23:06:38 – + [260] Unable to start mail session (reason: No mail profile
defined)
2008-03-19 23:06:38 – + [396] An idle CPU condition has not been defined – OnIdle
job schedules will have no effect
2008-03-19 23:06:38 – + [408] SQL Server MSSQLSERVER is clustered – AutoRestart has
been disabled
2008-03-19 23:06:39 – ! [298] SQLServer Error: 22022, CryptUnprotectData() returned
error -2146893813, ‘Key not valid for use in specified state.’ [SQLSTATE 42000]
2008-03-19 23:06:39 – ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
2008-03-19 23:06:40 – ? [098] SQLServerAgent terminated (normally)
Error2

2008-03-18 12:18:30 – ? [100] Microsoft SQLServerAgent version 9.00.3200.00
((Unknown) unicode retail build) : Process ID 6512
2008-03-18 12:18:30 – ? [101] SQL Server PISTONDIST version 9.00.3200 (0 connection
limit)
2008-03-18 12:18:30 – ? [102] SQL Server ODBC driver version 9.00.3042
2008-03-18 12:18:30 – ? [103] NetLib being used by driver is DBNETLIB.DLL; Local
host server is np:pistondist
2008-03-18 12:18:30 – ? [310] 16 processor(s) and 32765 MB RAM detected
2008-03-18 12:18:30 – ? [339] Local computer is PISTONDIST running Windows NT 5.2
(3790) Service Pack 2
2008-03-18 12:18:31 – ? [432] There are 11 subsystems in the subsystems cache
2008-03-18 12:18:31 – ! [364] The Messenger service has not been started – NetSend
notifications will not be sent
2008-03-18 12:18:31 – ? [129] SQLSERVERAGENT starting under Windows NT service
control
2008-03-18 12:18:32 – + [396] An idle CPU condition has not been defined – OnIdle
job schedules will have no effect
2008-03-18 12:18:32 – + [408] SQL Server MSSQLSERVER is clustered – AutoRestart has
been disabled
2008-03-18 12:18:32 – + [162] Internal request (from SetJobNextRunDate [reason:
schedule will not run again]) to deactivate schedule 66
2008-03-18 12:18:32 – ! [298] SQLServer Error: 22022, CryptUnprotectData() returned
error -2146892987, ‘The requested operation cannot be completed. The computer must
be trusted for delegation and the current user account must be configured to allow
delegation.’ [SQLSTATE 42000]
2008-03-18 12:18:32 – ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
2008-03-18 12:18:33 – ? [098] SQLServerAgent terminated (normally)

 

Resolution

Modify the the following Key.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\SQLServerAgent
Modify the value data of the serverhost key to np:Virtualservername

Ie:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.X\SQLServerAgent
ServerHost
Value: np:Virtualservername.

This will force the SQLServer agent to connect with SQLserver using Named Pipes so
delegation is not used.

—————————————————————————————————————–

We have a HOTFIX available for this issue and it is included in the cumulative update pack9 for SQLServer service pack2. http://support.microsoft.com/?id=956378
Note: Before applying the Hotfix. you have to follow the  steps mentioned in Resolution else hotfix would fail. Revert the steps after applying the fix.

 

Regards

Karthick PK

Posted in Configuration, Connectivity, SQL Cluster Setup, SQL Server Cluster | 2 Comments »

How to enable Constraint delegation for SQLServer2005

Posted by Karthick P.K on January 14, 2009

Constraint delegation for SQLServer2005

Follow the below steps to set the constraint delegation for SQLServer2005

1. In active directory users and computers for the startup account of SQLServer
a.Select “Trust this user for delegation to specified services
only”
b.Add host service for each node of the cluster
2. In active directory users and computers for each node under the delegation
tab
a. Select “Trust this computer for specified services only”
b. Add CIFS and Protected storage for each node of the cluster and each DC which
SQLserver may use to authenticate.

Take the SQLServer Group offline in clutser administrator and bring it online.

Please refer the attached document for additional information with Image and example   ConstraintDelegationforSQLServer2005

Regards

Karthick PK

Posted in Configuration, SQL Server Cluster | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 37 other followers