Thursday 4 September 2014

Script to get the all jobs info in a server.


SET NOCOUNT ON

--Checking for SQL Server verion

IF CONVERT(tinyint,(SUBSTRING(CONVERT(CHAR(1),SERVERPROPERTY('productversion')),1,1))) <> 8

BEGIN

---This is for SQL 2k5 and SQL2k8 servers

SET NOCOUNT ON

SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,

j.name AS job_name,

CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,

CASE jh.run_status WHEN 0 THEN 'Error Failed'

                WHEN 1 THEN 'Succeeded'

                WHEN 2 THEN 'Retry'

                WHEN 3 THEN 'Cancelled'

                WHEN 4 THEN 'In Progress' ELSE

                'Status Unknown' END AS 'last_run_status',

ja.run_requested_date as last_run_date,

CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,

ja.next_scheduled_run_date,

CONVERT(VARCHAR(500),jh.message) AS step_description

FROM

(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)

join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id

WHERE ja.session_id=(SELECT MAX(session_id)  from msdb.dbo.sysjobactivity) ORDER BY job_name,job_status

END

ELSE

BEGIN

--This is for SQL2k servers

SET NOCOUNT ON

DECLARE @SQL VARCHAR(5000)

--Getting information from sp_help_job to a temp table

SET @SQL='SELECT job_id,name AS job_name,CASE enabled WHEN 1 THEN ''Enabled'' ELSE ''Disabled'' END AS job_status,

CASE last_run_outcome WHEN 0 THEN ''Error Failed''

                WHEN 1 THEN ''Succeeded''

                WHEN 2 THEN ''Retry''

                WHEN 3 THEN ''Cancelled''

                WHEN 4 THEN ''In Progress'' ELSE

                ''Status Unknown'' END AS  last_run_status,

CASE RTRIM(last_run_date) WHEN 0 THEN 19000101 ELSE last_run_date END last_run_date,

CASE RTRIM(last_run_time) WHEN 0 THEN 235959 ELSE last_run_time END last_run_time, 

CASE RTRIM(next_run_date) WHEN 0 THEN 19000101 ELSE next_run_date END next_run_date, 

CASE RTRIM(next_run_time) WHEN 0 THEN 235959 ELSE next_run_time END next_run_time,

last_run_date AS lrd, last_run_time AS lrt

INTO ##jobdetails

FROM OPENROWSET(''sqloledb'', ''server=(local);trusted_connection=yes'', ''set fmtonly off exec msdb.dbo.sp_help_job'')'

exec (@SQL)

--Merging run date & time format, adding run duration and adding step description

select Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,jd.job_name,jd.job_status,jd.last_run_status,

CONVERT(DATETIME,RTRIM(jd.last_run_date)) +(jd.last_run_time * 9 + jd.last_run_time % 10000 * 6 + jd.last_run_time % 100 * 10) / 216e4 AS last_run_date,

CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,

CONVERT(DATETIME,RTRIM(jd.next_run_date)) +(jd.next_run_time * 9 + jd.next_run_time % 10000 * 6 + jd.next_run_time % 100 * 10) / 216e4 AS next_scheduled_run_date,

CONVERT(VARCHAR(500),jh.message) AS step_description

from (##jobdetails jd  LEFT JOIN  msdb.dbo.sysjobhistory jh ON jd.job_id=jh.job_id AND jd.lrd=jh.run_date AND jd.lrt=jh.run_time) where step_id=0 or step_id is null

order by jd.job_name,jd.job_status

--dropping the temp table

drop table ###jobdetails

END

--- Note : we got the script from below URL ----
--- Read more: http://sql-articles.com/scripts/script-to-find-sql-job-activity-details/#ixzz24IQ3asc2---

Process to delete the logins which are not mapping to any database in sql server 2000

1   --- 1)      Run the below script
             ----Sp_helplogins
----a)      Copy the first table output in excel sheet and then apply filter on Auser with “no”.
2    ----2)      And then run the below script

select @@SERVERNAME
,sl.name
,isnull(DB_Roles.Roles,'Public')
from syslogins sl
left join
(
select 'sysadmin' as Roles
union all
select 'securityadmin'
union all
select 'serveradmin'
union all
select 'setupadmin'
union all
select 'processadmin'
union all
select 'diskadmin'
union all
select 'dbcreator'
union all
select 'bulkadmin'
union all
select 'No serverRole'
union all
select 'public'
) DB_Roles
on
Case When sl.sysadmin=1 and DB_Roles.Roles='sysadmin' then 'sysadmin'
      When sl.securityadmin=1 and DB_Roles.Roles='securityadmin' then 'securityadmin'
      When sl.serveradmin=1 and DB_Roles.Roles='serveradmin' then 'serveradmin'
      When sl.setupadmin=1 and DB_Roles.Roles='setupadmin' then 'setupadmin'
      When sl.processadmin=1 and DB_Roles.Roles='processadmin' then 'processadmin'
      When sl.diskadmin=1 and DB_Roles.Roles='diskadmin' then 'diskadmin'
      When sl.dbcreator=1 and DB_Roles.Roles='dbcreator' then 'dbcreator' 
      When sl.bulkadmin=1 and DB_Roles.Roles='bulkadmin' then 'bulkadmin'
      else null end=DB_Roles.Roles
     
order by 2

a)      Copy the result into excel sheet and apply the filter on third column with “sysadmin”
b)      Compare the both excel sheets and delete the logins in first excel sheet which are in second excel sheet.
c)       After that create a test database on the server with droplogin table with login_id as column name and entre the logins name which are in first excel sheet into the droplogin table.
d)      And run the below script on test database to delete the logins which are not map to any database.

declare @cursor cursor, @login_id sysname
set @cursor = cursor for (select login_id from droplogin)
open @cursor
while 1=1
 begin
 fetch next from @cursor into @login_id
 if @@FETCH_STATUS <> 0
  break

 EXEC ('sp_droplogin ' + @login_id)
 
 End




Note: Maintain the logins list which are we delete and perform the full backup on master database.

SP to find the Lead Blocker


With help of the below SP we can find the Lead blocker details. it will be useful to resolve the blocking issue quickly.

----Text 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

CREATE PROCEDURE [dbo].[sp_leadblocker] as 
begin 
set nocount on 

IF EXISTS (SELECT * FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON (p1.spid=p2.blocked)) BEGIN 
DECLARE @spid int 

SELECT @spid=p1.spid -- Get the _last_ prime offender 
FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON (p1.spid=p2.blocked) 
WHERE p1.blocked=0 

SELECT p1.spid, 
p1.status, 
loginame=LEFT(p1.loginame,20), 
hostname=substring(p1.hostname,1,20), 
blk=CONVERT(char(3),p1.blocked), 
db=LEFT(db_name(p1.dbid),10), 
p1.cmd, 
p1.waittype 
FROM master..sysprocesses p1 
JOIN master..sysprocesses p2 ON (p1.spid=p2.blocked) 
WHERE p1.blocked=0 
select @spid -- Return the last lead blocker 
END ELSE BEGIN 
PRINT 'No processes are currently blocking others.' 
select GetDate() 
END 
end

------ Note : we got this code from the web, Please test it and then use on Production servers. ------

Thursday 7 November 2013

How can I Increase the Number of SQL Server Error Logs

How can I Increase the Number of SQL Server Error Logs
Problem

By default, there are six achieved SQL Server Error Logs along with the ERRORLOG which is currently used. However, it is a Best Practice to increase the number of SQL Server Error Logs from the default value of six.

Solution

A new error log is created when an instance of SQL Server is restarted. Also database administrators can run the command sp_cycle_errorlog system stored procedure to cycle the error log without recycling the instance of SQL Server. The most recent error log backup will have a name ERRORLOG.1, the second most recent error log backup will have the name as ERRORLOG.2 and the current error log will have the name ERRORLOG.
It is a BEST PRACTICE to increase the SQL Server Error Log from the default value of 6, because the error logs may contain critical information about your database server. As mentioned, by default there will be 7 error log files that exist, 6 archives and the current one.  When a new error log is created the oldest archive gets removed and that data is then lost forever.  So if you are trying to troubleshoot a system problem and are doing several restarts of SQL Server you may end up replacing all of your archives and then lose this valuable information in the error logs.


With help of below Steps we can Increase the Number of SQL Server Error Logs in SQL Server 2008 / 2005: 

1. Connect to SQL Server 2008 or SQL Server 2005 Instance using SQL Server Management Studio
2. In the Object Explorer, Click on "Management" and expand "SQL Server Logs"
3. Right click SQL Server Logs and click on "Configure" option from the drop down list as shown in the below snippet.

4. This will open up Configure SQL Server Error Logs window as shown in the below snippet. Here, for Maximum number of error logs option you can specify a value between 6 and 99. In this example, I have changed the value from the default value of 6 to 10.


5. Once you have specified the new value for Maximum number of error log files click OK to save the changes.
As SQL Server Database Engine is not restarted frequently in a production environment, it will be a good practice for a DBA to schedule a SQL Server Agent Job which runs once in a day to execute either DBCC ERRORLOG or sp_cycle_errorlog system stored procedure to create a new SQL Server Error Log. It becomes easier to open up SQL Server Error Log file when it is small in size.
With help of the below command we can recycle the sql server agent error log also.
EXEC msdb.dbo.sp_cycle_agent_errorlog

Happy Learning 

Wednesday 6 November 2013

How to find the location of the sql server error log...

How to find the location of the sql server error log

Steps to find the location of the sql server log:

1)Click on start button and click on all programs and select the Microsoft Sql server 2008 -à configuration Tools --à SQL Server configuration Manager.

 2)Select the Sql server services and then right click on SQL Server(MSSQL) and select the properties tab.

 3)Go to Advanced tag and select the Startup parameters. In this option select the drop down button. In this box after the “-e” is the location of the sql server error log. Please check the below SH.


      4)If you want to change the location of the sql server error log, you can change it here. Just past the new location after –e. and don’t forget to put the \errorlog after pasting the new location.

5)After this one you need to restart the sql services to take the effect of the new location. Same like this process you can change the master database file locations. But here small change i.e after changing the location at startup parameters you need to stop the sql server services and past the master database mdf and ldf files from old location to new location and then start the sql server services.


Happy Learning :)


Tuesday 29 October 2013

About Quorum


About Quorum

In short quorum is minimum number of votes required for majority. the nodes participating in the windows cluster are connected through a private network and communicate through User Datagram Protocol (UDP) port 3343.The quorum configuration in a failover cluster determines the number of failures (failure of nodes) that the cluster can sustain while still remain online. If additional failure happened beyond this threshold, the cluster will stop running.Quorum is designed to handle the Split Brain scenario. When nodes are unable to communicate each other, each node assume that, resource groups owned by other nodes have to brought online. When same resource brought online on multiple nodes at the same time,data corruption can occur. This scenario is called Split Brain.

Quorum role in cluster

When both nodes of a cluster are up and running, participating in their relevant roles (active and passive) they communicate with each other over the network. For example, if you change a configuration setting on the active node, this configuration change is automatically sent to the passive node and the same change made. This generally occurs very quickly, and ensures that both nodes are synchronized.

But, as you might imagine, it is possible that you could make a change on the active node, but before the change is sent over the network and the same change made on the passive node (which will become the active node after the failover), that the active node fails, and the change never gets to the passive node. Depending on the nature of the change, this could cause problems, even causing both nodes of the cluster to fail.

To prevent this from happening, a SQL Server 2005 cluster uses what is called a quorum, which is stored on the quorum drive of the shared array. A quorum is essentially a log file, similar in concept to database logs. Its purpose is to record any change made on the active node, and should any change recorded here not get to the passive node because the active node has failed and cannot send the change to the passive node over the network, then the passive node, when it becomes the active node, can read the quorum file and find out what the change was, and then make the change before it becomes the new active node.

In order for this to work, the quorum file must reside on what is called the quorum drive. A quorum drive is a logical drive on the shared array devoted to the function of storing the quorum.
Quorum models

Windows 2008 cluster supports four quorum models.

1 Node Majority 

2 Node and Disk Majority 

3 Node and File Share Majority

4 No Majority (disk only)


Node Majority: Node majority option is recommended for cluster with odd number of nodes.This configuration can handle a loss of half of the number of cluster nodes rounded off downwards. For example , a five node cluster can handle failure of two nodes. In this scenario three of the nodes (N1,N2,N3) can communicate each other but other two(N4 and N5) are not able to communicate. The group constituted by three node have the quorum (majority) and cluster will remain active and cluster service will be stopped on the other two nodes (N4 and N5). The resource group (sql server instance) hosted on that two nodes goes offline and come online on one of the three nodes based on possible owner settings.


Node and Disk Majority: This option is recommended for cluster with even number of nodes.In this configuration every node gets one vote and witness disk (quorum disk) gets one vote which makes total votes a odd number. The witness disk is a small ( approx 1 GB ) clustered disk.This disk is highly available and can failover between nodes. It is considered as part of the cluster core resource group.In a four node cluster, if there is a partition between two subsets of nodes, one of the subset will have witness disk and that subset will have quorum and cluster will remain online. This means that the cluster can lose any two voters,whether they are two nodes or one node and the witness disk.


Node and File Share Majority: This configuration is similar to the the Node and Disk Majority, but in this case the witness disk is replaced with a file share which is also known as File Share Witness Resource (FSW). This quorum configuration usually used in multi-site clusters (nodes are in different physical location) or where there is no common storage. The File Share Witness resource is a file share in any server in the same active directory which all the cluster nodes have access to. One of the node in the cluster will place a lock on the the file share to consider that node as owner of the file share.When this node goes offline or lost the connectivity another node grabs the lock and own the file share.On a standalone sever, the file share is not highly available , however the file share can also put on a clustered file share on an independent cluster,making the FSW clustered and giving it the ability to fail over between node. It is important that, this file share should not put in a node of the same cluster, because losing that node would cause for loosing two votes. A FSW does not store cluster configuration data like witness disk. It contain information about which version of the cluster configuration database is most recent.


No Majority (Disk only) : This configuration was available in windows server 2003 and has been maintained for compatibility reason and it is highly recommended not to use this configuration. In this configuration,only witness disk has a vote and there are no other voters in the cluster. That means if all nodes are online and able to communicate , but when witness disk failed or corrupted, the entire cluster will go offline.This is considered as single point of failure.

Monday 3 June 2013

Finding the active trace flags in Microsoft sql server
With help of below command we can find the active trace flags on Microsoft sql server instance.
DBCC TRACESTATUS
The output of the command is:
TraceFlag   Status Global Session
------------  -------- ----------  ----------
4199             1           1              0
4616             1              1              0
(2 row(s) affected )
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
If there are no active trace flags, we will get the output like:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Enable session based trace flags
To enable trace flags only in your session use the following two commands.
DBCC TRACEON (trace#)
DBCC TRACEOFF (trace#)
Enable the trace flags globally:
With help of below command we can enable the trace flags globally…
DBCC TRACEON (trace#, -1)
DBCC TRACEOFF (trace#, -1)

Enable Multiple Trace at same time separating each trace with a comma.
DBCC TRACEON(1205,2528)


Disable Multiple Trace at same time separating each trace with a comma.
DBCC TRACEOFF(1205,2528)

To enable a trace flag to persist through a restart. Alter the “Startup Parameters” in the Advanced tab for the SQL Server service in SQL Server Configuration Manager.
For Example: Before Modification
-dc: \Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\log\ERRORLOG;
-lC:Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
After Modification:
-dc: \Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\log\ERRORLOG;
-lC:Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-T4199

Available trace flages in sql server...