Wednesday, January 29, 2014

Script to list Agent Jobs with Created Date and Time

Script to list Agent Jobs with Created Date and Time

Select, cast(b.date_created as datetime) from  msdb.dbo.sysjobs a
INNER JOIN msdb.dbo.sysJobschedules c ON a.job_id = c.job_id
INNER JOIN msdb.dbo.SysSchedules b on b.Schedule_id=c.Schedule_id
order by cast(b.date_created as datetime) desc

Monday, January 27, 2014

Msg 22050, Level 16, State 1, Line 0 File attachment or query results size exceeds allowable value of 1000000 bytes.

We may encounter the below error if we want to send little large file (of query results) using sendmail option

 EXEC msdb.dbo.sp_send_dbmail
 @body='Query Results Are Attached',
 @subject ='Query Results',
 @profile_name ='Kalyan_Notifications',
 @query ='exec MonthlyReport',
 @attach_query_result_as_file = 1,
 @query_result_separator =',',
 @query_attachment_filename ='QueryResults.txt'

Msg 22050, Level 16, State 1, Line 0 File attachment or query results size exceeds allowable value of 1000000 bytes.

Below statement will increase the size of attachment file

EXECUTE msdb.dbo.sysmail_configure_sp   'MaxFileSize', '2097152' ;


SSMS - Management - Database Mail - Configure Database Mail - Next
Select View or change system parameters
Change the Maximum File Size (Bytes) -- To Desired value in bytes
Next - Finish
To view the status of mail, whether it was sent to recipients or not,

select mailitem_id, sent_status from msdb..sysmail_allitems


Msg 15348, Level 16, State 1, Line 1 Cannot transfer a schemabound object. How to transfer objects from one schema to another schema

Today I faced the below issue while transfering objects from one schema to other schema

Msg 15348, Level 16, State 1, Line 1 Cannot transfer a schemabound object.

First we will see how to transfer objects from one schema to other schema

ALTER SCHEMA <NewSchemaName> TRANSFER SchemaName.ObjectName

To create query to transfer all objects including tables, functions, stored procedures and views, Use the below query

SELECT  'ALTER SCHEMA <NewSchemaName> TRANSFER OldSchemaName.'+name
FROM sys.objects
WHERE type IN ('U','V','P','Fn')

Copy the result from the grid and execute on the desired database, all the objects will get transfered to new schema. You may encounter error if there are any objects with schema binding then you need to remove the schema binding and then transfre the objects

How to know which are schema binding objects
SELECT  Schema_name(so.schema_id) AS [schema], Object_name( AS objectname,
         so.type_desc AS objecttype
FROM sys.sysdepends sd INNER JOIN sys.columns sc ON sd.depid = sc.object_id
AND sd.depnumber = sc.column_id INNER JOIN sys.objects so ON = so.object_id
WHERE sd.deptype = 1
GROUP BY so.schema_id,, so.type_desc

Pull the views which has schema binding dependency and drop those views and transfer the objects to new schema and re-create the views with schema binding again.


Friday, January 24, 2014

Logon Error: 18456, Severity: 14, State: 11.

Troubleshooting Login Failed error messages

Logon Error: 18456, Severity: 14, State: 11.

Login failed for user <Server name>\kalyan'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT:]\


If you face the above error for windows user which has sysadmin role probably there might be issue with UAC, Please run the SQL Server Management Studio with Run as Administrator.

Other reasons for this issue is the login might not exists on the SQL Server on which you are trying to login.

If you are able to see that domain login exists and still you are getting the same error then you need to drop and re-create the user at SQL Server level.

Tuesday, January 21, 2014

Database in Recovery Pending in SQL Server 2012 Alwayson Availability

Database in Recovery Pending in SQL Server 2012 Alwayson Availability

After performing reboot of primary server the primary replica database moved to "Recovery Pending" state and the other replicas become unusable.
While troubleshooting the issue we identified that Availability group is offline, to bring the Availability group to online we tried failover but we could not able to do that becoz of primary is not online, as a second option we tried to bring the cluster resource online but the cluster manager doesnt allow to bring the resource online.

As suggested by BOL the possible solution for the issue is restarted the server and verified that the availability group recovers to a healthy state.

Thursday, January 16, 2014

Pre-Installation Failover Cluster CheckList

Pre-Installation cluster checklist

Ensure BIOS, RAID controller and HBA firmware is up to date.

Ensure Server OS is update to date with latest patch levels.

Ensure all drivers are up to date especially network and HBA drivers.

Add SQL Service Account to Lock Pages in memory, Perform Volume maintenance tasks.

Ensure SQL Service Accounts are created in Active Directory prior to the installation.

Each node will have identical hardware, drivers, and software and configuration settings.

All nodes must belong to same domain.

Quorum drive must be on its dedicated disk of 500 MB or larger.

Avoid running antivirus on SQL Server cluster.

NICs configured as teamed and set to FULL duplex and maximum network speed.

Ensure all nodes are added to SCOM for monitoring.

Try to setup a dedicated drive for tempdb data and log files

Create MSDTC resource

Private LAN isolated from all network infrastructure

Only TCP/IP configured on all networks

Ping all IP addresses of each network adapter on each node from a machine not in cluster.


Wednesday, January 15, 2014

Failover Cluster Terminology

Failover Clustering Terminology

Node – A node is an individual server in the cluster.

Cluster Resource - A hardware or software component in the cluster such as disk, Virtual Name and IP Address.

Resource group – Cluster resources are contained within a cluster in a single unit and this is also called application and service group. 

Active Node – An active node is one that cluster is currently running, A resource or resource group is only be active on one node at a time.

Passive Node – A passive node is one that cluster is currently not running on it.

Cluster Access point – A cluster access point is the term used in windows server 2008 R2 failover clusters that represents the combination of network name and associated IP address resource. 

Virtual Server – Virtual server consists of network name and IP address to which clients are connect. This helps the client to connect the service which hosted in the cluster environment without knowing the details of server nodes.

Witness disk / file share – The cluster witness or the witness file share are used to store the cluster configuration information and help to determined the state of the cluster when some, if not all, of the cluster nodes cannot be contacted.

Cluster Quorum – The cluster quorum maintains the definitive cluster configuration data and the current state of each node, each services and application group and each resource network in the cluster.

Private storage – Local disks are referred as private storage; There will be one private disk for OS and SQL binaries.

Shared disk – Each server needs to be attached to external storage, in non-clustered environments data is stored under local disks whereas in clustered environment data is stored under shared disk, then only the nodes which are in cluster can access the shared disk.

Public network / private network – Each node needs two network cards, one is the public network and the other is private network between the nodes. The private network is used internal communication of network called as heartbeat. Public network is connected to LAN / WAN.
Heartbeat – It is a health check mechanism of the cluster, A single UDP packet is sent to all nodes in the cluster via private network to confirm whether all nodes in the cluster is online or not, By default cluster service will wait for 5 seconds (one hearbeat sent in every second) before considering the cluster node is unreachable.

LooksAlive – It’s a basic resource (SQL Service) health check to verify whether the service is running fine or not, to perform this cluster service queries the windows service control manager to check the status of the service. By default lookalive check will happen in every 5 seconds

IsAlive – An exhaustive check to verify that a resource is running properly. If this check fails the resource is moved offline and failover process is triggered, during the IsAlive check the cluster service connects to SQL Instance and executes @@servername. It will check only the SQL instance availability and doesnot check the availability of user databases.

Possible Owners – These are the server nodes of cluster group

Preferred Owners – This is the best suited for running application group. 

Friday, January 10, 2014

What is Cluster? And Types of Clusters and how to setup

What is Cluster? And Types of Clusters and how to setup

Cluster -- A cluster is a group of independent computers (nodes) that are presented to the network as a single system. There are different types of high availability solutions

NLB - Network Load Balancing Cluster, it works on network level but not at resource level. A server in the network load balancing cluster is known each other, when one server fails the other server automatically redistributes the connections among themselves. This offers horizontal scaling and you can simply add new server when network load increases. There will be no data sharing between the servers which are configured in NLB, it is widely used in few areas such as web servers, file servers, printer servers, terminal servers etc., This is primarily to distribute load (requests) to different nodes which are part of cluster. 

NLB doesn’t require any special hardware, an NLB farm can include 32 nodes, but you can start with high availability with 2 nodes and nodes can increased when traffic increases. For setting up network load balancing all you need is 2 or more nodes connected to a common network and additional IP (Virtual IP) where the clients will connect to that IP. The virtual IP is used load balancing nodes in the NLB cluster. 

Failover Cluster - A failover cluster is a group of independent computers or nodes which are physically connected by LAN and that are programmatically by cluster software. The group of nodes is worked together to increase the availability of applications and services that run on the cluster. In other words if NodeA fails then automatically the application will be served from NodeB that means the responsibilities of NodeA will takeover by NodeB. 

The primary difference between NLB and failover cluster is NLB offers scalability and failover cluster cannot improve the scalability, it can only support high availability.
Setting up failover cluster is little complex, we need two networks public and private (heartbeat) and a shared drive (called Quorum) and an additional public IP in addition to 2 public ips and 2 private ips for two systems that we will have. We will see detailed terminology and setting up cluster in further posts.

Thursday, January 2, 2014


SQL OS is a kind of operating system built for SQL Server and works on top of Windows Operating System. SQL OS will depend on windows API to perform tasks but it has its own way of handling things. SQL OS was introduced in SQL 2005 and it has the major functions like IO Management, Memory Management and Resource Scheduling. Prior to SQL Server 2005, SQL 2000 uses UMS (User Mode Scheduling) to performing scheduling and other tasks. The introduction of SQL OS is to bring everything into one place and we will have better visibility what is happening at what level, all these can be monitored through DMV’s, the DMV’s starts with sys.dm_os provides complete information of working of SQL OS such as schedulers information, wait tasks information, memory clerks etc.,
A CPU is a single-core or multi-core processor, core means processor, single core means one processor and multi core means two processors, even though a processor is one chip internally it has two physical cores. Dual core has 2 cores, Quad core has 4 cores, Hexa core has 6 cores, Octo core has 8 cores, Deca core has 10 cores. Intel CPUs uses a technology called “Hyperthreading” using which a single core processor itself represents has two logical cores to the system, which means if we use a quad core processor with hyperthreading technology you will be able to see 8 cores in the task manager. These are called logical CPUs these will be visible to Operating System (OS)
A thread is a process or a part of process, Select statement is a process in SQL Server it might run as a single thread in the process (if it is simple query) or it splits into multiple threads and runs on multiple processors to complete the process (if it is complex query) to complete the process faster.  Each thread will get some amount of time in the processor and it will move out of the processor to give processor time to other waiting threads the process of moving threads into processor and out from processor is known as scheduling.
Scheduler is a logical CPU in SQL Server, Each scheduler is mapped to one logical processor shown by operating system, and one logical processor can have multiple schedulers assigned to it. Visible schedulers are helps to process the user requests, hidden schedulers are only visible to SQL Server internal processes, and one scheduler is dedicated for DAC (Dedicated Administrator Connection). The scheduler’s information is available in sys.dm_os_schedulers DMV.
Scheduler maintains two components, Wait List and Runnable queue. The processor is processes one thread at a time, Wait list is the list of threads waiting for resources, Runnable queue is a set of threads that has resources but waiting for its turn to get into the processor, Scheduler will put thread into runnable queue and move that thread to processor. The runnable queue follows FIFO when thread moves from wait list it joins at the end of runnable queue.
The wait list is a list of threads waiting for resources and this is unordered queue, the waiting thread will pushed into runnable queue when it founds the resources. The thread which is in the wait list can be cancelled due to execution time out, sys.dm_os_waiting_tasks helps to examine the wait list queue.
Thread States

The thread can have any of the three states, Running, Runnable, Suspended, Running is the state where the thread is currently executing in the processor and utilizing the CPU. Only one thread per scheduler can have this state as the processor can process only one thread at a time. While a task is running it might requires other resources to proceed further (data page, memory etc) that time the thread state will become suspended and moves to wait list and it remains there until it gets the resource. Once the resource is available the task is moved to runnable state and waiting in queue for processor time. It gets executed once the task before in the queue is executed.

Wednesday, January 1, 2014

Happy New Year 2014

To All my friends and readers

       I Wish you a very
Happy New Year