Software Development,SQL Tips, Information Technology Technical tutorials

Asp.net,SQL tips-Tricks for error resolution version 2014,2012,2008,2005,Procedural language,Stored Procedure, computer , excel macro programming,Film,Musical notations,Indian ,Western Music.

October 7, 2014

Script or query for FAILED JOBS Details with error Messages in SQL

You can use following Script or query for FAILED JOBS Details with error Messages in SQL. I hope it will help you.

USE Msdb
Declare @lastRunDate int =replace(CAST(getdate()as date),'-','')
SELECT Sj.name as JobName ,CASE SJH.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
END AS JobStatus,SJH.message from sysjobhistory SJH inner join sysjobs SJ
ON SJH.job_id = SJ.job_id
where SJH.step_id = 0
and sjh.run_date =@lastRunDate
and SJH.run_status =0

September 30, 2014

What is SQL Server Profile

SQL Server Profiler. It is a graphical based tool provided by SQL Server. It enables you to trace a query. For example.you have a SP which you wish to trace. you can execute the SP and start a profiler trace on it. It will show you row by row execution of the SP

SQL Server Profiler is used to trace and analyze the SQL Server running processes and queries in the cases of performance issue.

It is taken high memory so not used in main server.

It is have so many events for trace. so your select desired event and filter it.

It can use to find the blocking by applying trace and catch everythng runing on the server

How to Get historical data of blocked process in SQL


WITH [Blocking]AS (SELECT w.[session_id]
,s.[original_login_name]
,s.[login_name]
,w.[wait_duration_ms]
,r.[scheduler_id]
,w.[wait_type]
,r.[status]
,r.[wait_resource]
,w.[resource_description]
,s.[program_name]
,w.[blocking_session_id]
,s.[host_name]
,r.[command]
,r.[percent_complete]
,r.[cpu_time]
,r.[total_elapsed_time]
,r.[reads]
,r.[writes]
,r.[logical_reads]
,r.[row_count]
,q.[text]
,q.[dbid]
,p.[query_plan]
,r.[plan_handle]
FROM [sys].[dm_os_waiting_tasks] w
INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
WHERE w.[session_id] > 50
)
Insert into Blocking_v1 ([session_id]
,[blocking_session_id]
,[WaitingUserSessionLogin]
,[BlockingUserSessionLogin]
,[scheduler_id]
,[WaitingUserConnectionLogin]
,[BlockingSessionConnectionLogin]
,[WaitDuration]
,[WaitType]
,[WaitRequestMode]
,[WaitingProcessStatus]
,[BlockingSessionStatus]
,[WaitResource]
,[WaitResourceType]
,[WaitResourceDatabaseID]
,[WaitResourceDatabaseName]
,[WaitResourceDescription]
,[WaitingSessionProgramName]
,[BlockingSessionProgramName]
,[WaitingHost]
,[BlockingHost]
,[WaitingCommandType]
,[WaitingCommandText]
,[WaitingCommandRowCount]
,[WaitingCommandPercentComplete]
,[WaitingCommandCPUTime]
,[WaitingCommandTotalElapsedTime]
,[WaitingCommandReads]
,[WaitingCommandWrites]
,[WaitingCommandLogicalReads]
,[WaitingCommandQueryPlan]
,[WaitingCommandPlanHandle])
SELECT b.[session_id] AS [WaitingSessionID]
,b.[blocking_session_id] AS [BlockingSessionID]
,b.[login_name] AS [WaitingUserSessionLogin]
,s1.[login_name] AS [BlockingUserSessionLogin]
,b.scheduler_id
,b.[original_login_name] AS [WaitingUserConnectionLogin]
,s1.[original_login_name] AS [BlockingSessionConnectionLogin]
,b.[wait_duration_ms] AS [WaitDuration]
,b.[wait_type] AS [WaitType]
,t.[request_mode] AS [WaitRequestMode]
,UPPER(b.[status]) AS [WaitingProcessStatus]
,UPPER(s1.[status]) AS [BlockingSessionStatus]
,b.[wait_resource] AS [WaitResource]
,t.[resource_type] AS [WaitResourceType]
,t.[resource_database_id] AS [WaitResourceDatabaseID]
,DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName]
,b.[resource_description] AS [WaitResourceDescription]
,b.[program_name] AS [WaitingSessionProgramName]
,s1.[program_name] AS [BlockingSessionProgramName]
,b.[host_name] AS [WaitingHost]
,s1.[host_name] AS [BlockingHost]
,b.[command] AS [WaitingCommandType]
,b.[text] AS [WaitingCommandText]
,b.[row_count] AS [WaitingCommandRowCount]
,b.[percent_complete] AS [WaitingCommandPercentComplete]
,b.[cpu_time] AS [WaitingCommandCPUTime]
,b.[total_elapsed_time] AS [WaitingCommandTotalElapsedTime]
,b.[reads] AS [WaitingCommandReads]
,b.[writes] AS [WaitingCommandWrites]
,b.[logical_reads] AS [WaitingCommandLogicalReads]
,b.[query_plan] AS [WaitingCommandQueryPlan]
,b.[plan_handle] AS [WaitingCommandPlanHandle]
FROM [Blocking] b
INNER JOIN [sys].[dm_exec_sessions] s1
ON b.[blocking_session_id] = s1.[session_id]
INNER JOIN [sys].[dm_tran_locks] t
ON t.[request_session_id] = b.[session_id]
WHERE t.[request_status] = 'WAIT'
GO

What is the Difference b/w SP_WHO n SP_WHO2 in SQL

1.SP_who2 shows one extra column that is RAM utilization than SP_WHO.

2.SP_who2 has these extra columns 
a. CPU Time
b. Disk IO
c. Last Batch
d. Program Name , apart from that SP_who is documented and Sp_who2 is not documented in BOL.

August 24, 2014

What is Covering Index in SQL

A covering index is a non-clustered index which includes all columns referenced in the query and therefore, the optimizer does not have to perform an additional look-up to the table in order to retrieve the data requested. As the data requested is all indexed by the covering index, it is a faster operation.

covering index is a non-clustered index which stores additional columns at leaf level to avoid bookmark lookup to heap/clustered index.this is typically created using INCLUDE clause.

Let say we have a table tab1 and we have columns starts from column 1 to 10. and we have clustered index on column1 and non clustered index on column 2 . for example you written a query select * from tab1 where column 2=200. this query first will go and look for the matching data in non clustered index and because of our query want to retrieve all the columns it will perform key look up , not helathy. this time i had one more non clustered index on column3 and column4 ,composite.in this case our query select column3,column4 from tab1 where column3 =200 no need to check in clustered index and table it can retrieve the data from non clustered index(in this case covering) index.

August 20, 2014

Port in sql server

SQL server assign default port 1433 to default instance...for named instance it dynamically allots a port number, ideally it is five digit port number.

To see port of SQL Server you can check as below.

Go to configuration manager-->SQL Serve network Configuration-> Select instance-->right click TCP/IP properties-->IP Properties

August 12, 2014

Dedicated admin account in SQL

Dac means dedicared admin account, which is used to access sql server at .by default 1433 is sql server port , 1434 ia dac account.

Starting the server in single user mode will use DAC (Dedicated Administrative Connection) connection. DAC Connection will use

If the number of connections are limited for the instance and all are connected and if some one wants to connect and fails to troubleshoot if DBA want to connect he will also be restricted then he can use DAC connection to connect to the instance.

When ever the SQL Server standard connection not establish the connection then we go with DAC connection . only sys admin can use this DAC to connect the server and troubleshoot the problems.

August 11, 2014

Interview questions for sql DBA

1.What are Differences in Delete & Truncate .
2.What are Differences in UNIQUE and Primary key ?
3. What are the agents involved in replication
4.Which Types of clusters and how many ips are required to install failover cluster?
5.Configuration of logshipping and DB mirroring.
5.What are Types of backups and backup scenario?
6.What is deadlock and blocking?
7.Differences between clustered and non clustered indexes?
8.Why SQL Server Agent is used?
9.What are Types of System databases
10.What are the steps for restoring a database?

August 10, 2014

what is linked server in sql server?

Linked server means to retrieve the data from the other server to connect our own instance, before configure the linked server we must know the target server login and password then only we access the data from the other server.

Linked server means access the data from one server another server is called linked server. store procedure is sp_addlinkedserver.

When we have to run the distributed transactions we need to linkup the servers internally we use linked servers concept.Here distributed transactions means A query which has to executed on multiple servers at a time is called distributed transactions.

Checkpoint and lazywrite in SQL

Checkpoint : Write all the dirty pages into buffer cache. It is not clear from buffer cache is called checkpoint.check point is the internal process which will take dirty blocks from buffer pool to data file on interval based.
Lazywriter: Write all the dirty pages into buffer cache. It is clear from buffer cache is called lazywriter

Checkpoint and lazywrite do the same job of flushing out the (8k size) pages from the memory to disk the only difference is of when they do this task.

A lazy write does its task when there is resource contention and sql engine needs to process more pages and bring them in the memory. The lazy writer in this case gets all the pages which have been residing in the memory for the longest time and flushes them to the disk.

A checkpoint on the other hand does the task for the SQL engine to create a stable point from which SQL can recover in case there is a failure of service during some processing. A checkpoint is not issued due to lack of resources.

Please note that in both cases the pages flushed or written to the disk are still marked dirty. So in case sql service fails after a lazy write or a checkpoint, after starting again the SQL will know which flushed pages were marked dirty and those\ changes would be rolled back.