Karthick P.K on SQL Server

Archive for January, 2014

Top SQL Server blogs from MSSQLWIKI

Posted by Karthick P.K on January 11, 2014

Top SQL Server blogs  from MSSQLWIKI 

Year 2013 was a wonderful year for and these are the top blogs that got the most views in 2013. I am summarizing the blogs based on category for easy read  


Debugging ( Using public symbols )

What is SQL Server Latch & Debugging latch time out

How to analyze Non-Yielding scheduler or Non-yielding IOCP Listener dumps  

SQL Server memory leak

Debugging memory Leaks using Debug diagnostic tool.

Non-yielding IOCP Listener, Non-yielding Scheduler and non-yielding resource monitor known issues and fixes

SQL Server Exception , EXCEPTION_ACCESS_VIOLATION and SQL Server Assertion

SQL Server generated Access Violation dumps while accessing oracle linked servers.


SQL Server Memory

Troubleshooting SQL Server Memory
Basics of SQL Server Memory Architecture

SQL Server lock pages in memory

Max server memory – Do I need to configure?

A significant part of SQL Server process memory has been paged out
SQL Server memory leak

Debugging memory Leaks using Debug diagnostic tool.

SQL Server and VMware ballooning

SQL Server fails to start with error "Failed allocate pages: FAIL_PAGE_ALLOCATION 1" During startup

SQL Server -g

SQL Server 2012 Memory

False warning “A significant part of sql server process memory has been paged out”


What is Target Server Memory (KB)?

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

What does MemoryUtilization in sys.dm_os_ring_buffers and Memory_utilization_percentage in sys.dm_os_process_memory represents?


SQL Server operating system

SQL Server NUMA load distribution

SQL Server Operating system (SOS) Series 3

SQL Server Operating system (SOS) Series 2

SQL Server Operating system (SOS) Series 1


Latch contention

Tempdb latch contention

SQL Server Latch


SQL Server performance

SQL Server Query optimization

SQL Server Parameter sniffing

Optimizer Timeout or Optimizer memory abort

Troubleshooting SQL Server high CPU usage

I/O requests taking longer than 15 seconds to complete on file

SQL Server NUMA load distribution

Script to get all the Query and cached plans

Trivial Plan

Script to get current blocking tree with wait types

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

Script to clear stats

Script to free cache

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

SQL Server Database tuning advisor

Capture context switches from dm_os_ring_buffers



Transactional Replication Part-1

Transactional Replication Part-2

Troubleshooting Transactional replication Latency using AgentStatistics

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

How to start replication agent in command prompt.

How to create transactional replication in SQL Server

How to configure distribution for replication


SQL Server connectivity, Kerberos authentication and SQL Server SPN (Service Principal Name for SQL Server)

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


Always on

The connection to the primary replica is not active. The command cannot be processed


Ring buffers

Inside sys.dm_os_ring_buffers


Data structures

SQL Server: Ghost records


Database mail

Database Mail errors in SQL Server (Troubleshooting steps)


Copy Data

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

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

Create script for all objects in database with data

SQL Server agent

SQL Server Agent is taking long time to start



“An error occurred during decryption”

Configuring SSL for SQL Server using Microsoft Certificate Authority Server

How to find who altered my SQL Server Login

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

How to enable Constraint delegation for SQLServer2005

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



SQL Server cluster

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

SQLServer LooksAlive and IsAlive Check

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

SQL Server cluster installation checklist

How to add node to SQL Server cluster



How to install cluster in windows 2008 and windows 2012

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


Tempdb usage

How to monitor the Session and query which Consumes Tempdb


SQL Server setup

Service pack ,Hotfix and CU installation for SQL Server 2005 might fail with “Unable to install Windows Installer MSI file“

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

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)

Installation of SQLServer2005/2008 Fails on Windows2008 Cluster.

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

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

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.

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

SQLServer 2008 Fails to come online on cluster after upgrade

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

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

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


Backup & restore

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.


Ring buffers

Inside sys.dm_os_ring_buffers

How to find SQL Server and system CPU usage history


SQL Agent and SQL Server startup issues

SQL Server Agent is taking long time to start

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

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.

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


Profiler and SQL Server tools

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


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

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

I cant not backup my database from SSMS….

I get Exception when i open SQL Server management studio

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

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

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



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


Mssqlsystemresource database

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


Checkdb failures

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



SSIS package fails with out of memory errors

SSIS package fails with out of memory errors

SSIS package fails when executed as job using proxy account



Multi Threaded OVELAPPED and Nonbuffered I/O Example

Asynchronous I/O example

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

how to Open CreateFile (Createfile example)

How to get the current state of cluster resource?

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

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

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

How to check if my account has LPM privilege

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.

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

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

CryptAcquireContext and CryptReleaseContext example

Criticalsection example

CreateProcess example

CreateFileMapping or MapViewOfFileEx example


How to & Other miscellaneous blogs


SQL Server Backup compression

Types of isolation levels in SQL Server

SQL Server database snapshot

How to create table with filestream column and Insert data

How to enable and configure Filestream in SQL SERVER 2008 / 2012

How to import/Export data in SQL Server

Steps to enable Alwayson in SQL Server 2012

How to create database mirroring

How to create log shipping in SQL Server

How to create merge replication in SQL Server

How to create shared disk using iSCSI Software Target


How to create clustered and non-clustered index

How to create SQL Server Agent Jobs

How to create backups using database maintenance plan

How to set Max degree of parallelism (MAXDOP)

How to configure maximum server memory

How to stimulate a SQL Server resource failure in cluster

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

SQL Server monitoring

Removing database mirroring

Builtin\Administrators cannot login in to SQL Server

SQL Server assert in Location: purecall.cpp:51


How to view the Space used by each table in database

FILESTREAM feature is disabled



If you liked this post, do like us on Facebook at and join our Facebook group

Thank you,

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


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




Posted in Summary | Tagged: , , | 8 Comments »

%d bloggers like this: