Changing READ COMMITED VALUE ON DB

In SQL SERVER 2008

============================================================

Step 1 :-

SELECT is_read_committed_snapshot_on FROM

sys.databases WHERE name= 'DATABASENAME'

OutPUT :-

1 = READ_COMMITTED_SNAPSHOT option is ON. Read operations under the read-committed isolation level are based on snapshot scans and do not acquire locks.

0 = READ_COMMITTED_SNAPSHOT option is OFF (default). Read operations under the read-committed isolation level use share locks.

Step 2 :- If its 0, run this to make it on.

ALTER DATABASE DATABASENAME SET READ_COMMITTED_SNAPSHOT ON

In SQL SERVER 2012

=========================================================

Step 1:- Right Click on the DB choose properties.

Step 2:- Choose options, go to miscellaneous section -> IS READ COMMITTED… -> Make it true.

CHECKING TRANSACTION LEVEL  

=======================================================

Step 1 :- 

 DBCC USEROPTIONS

Step 2:-

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

 

Date Time Convert -Format

SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
Feb 18 2013 11:29AM (Mon DD YYYY HH:MM AM/PM)
SELECT CONVERT(VARCHAR(20), GETDATE(), 101)
02/18/2013 (MM/DD/YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 102)
2013.02.18 (YYYY.MM.DD)
SELECT CONVERT(VARCHAR(20), GETDATE(), 103)
18/02/2013 (DD/MM/YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 104)
18.02.2013 (DD.MM.YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 105)
18-02-2013 (DD-MM-YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 106)
18 Feb 2013 (DD Mon YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 107)
Feb 18, 2013 (Mon DD, YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 108)
11:37:14 (HH:MM:SS)
SELECT CONVERT(VARCHAR(20), GETDATE(), 109)
Feb 18 2013 11:37:53 (Mon DD YYYY HH:MM:SS)
SELECT CONVERT(VARCHAR(20), GETDATE(), 110)
02-18-2013 (MM-DD-YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 111)
2013/02/18 (YYYY/MM/DD)
SELECT CONVERT(VARCHAR(20), GETDATE(), 112)
20130218 (YYYYMMDD)
SELECT CONVERT(VARCHAR(20), GETDATE(), 113)
18 Feb 2013 11:40:18 (DD Mon YYYY HH:MM:SS)
SELECT CONVERT(VARCHAR(20), GETDATE(), 114)
11:40:54:040 (HH:MM:SS:MMM)24 hr
SELECT CONVERT(VARCHAR(20), GETDATE(), 120)
2013-02-18 11:53:32 (YYYY-MM-DD HH:MM:SS)
 

MSSQLSERVER Event Description:Replication-Replication Distribution Subsystem: agent scheduled for retry. Query timeout expired

Step 1:-  Check the locks on the DB
Step2 :- In my case it was one ALTER and Another INSERT Command.
Step3:- Make sure you check all blocked SPID's
Step4:- Once you resolve the blocking it will workfine.

Server is not configured for DATA ACCESS.

Step 1 :- Check server is listed in servers or not

Select * from sys.servers

Step 2 :- Add Server and Set it to true

EXEC sp_serveroption 'YourServer', 'DATA ACCESS', TRUE

 Step 3:- Verification

use Select * from sys.servers  verify field  “is_Data_Access_enabled” is set to 1.
 

Replication Agent Errors

--To get Agents that caused Error

SELECT  distinct da.name
FROM   MSpublications pub inner join MSsubscriptions subs
                                                ON pub.publication_id = subs.publication_id
                                inner join MSdistribution_agents da
                                                ON da.id = subs.agent_id
WHERE pub.publication = 'PublicationName'


-- To check the errors
select * from msdb..sysreplicationalerts 

-- Agent Errors
SELECT * FROM sys.messages WHERE message_id in ( 14151)

=================================================================

Message ID	Predefined alert	Condition causing the alert to fire	Enters additional information in msdb..sysreplicationalerts
14150	Replication: agent success	Agent shuts down successfully.	Yes
14151	Replication: agent failure	Agent shuts down with an error.	Yes
14152	Replication: agent retry	Agent shuts down after unsuccessfully retrying an operation (agent encounters an error such as server not available, deadlock, connection failure, or time-out failure).	Yes
14157	Replication: expired subscription dropped	Expired subscription was dropped.	No
20572	Replication: Subscription reinitialized after validation failure	Response job 'Reinitialize subscriptions on data validation failure' reinitializes a subscription successfully.	No
20574	Replication: Subscriber has failed data validation	Distribution or Merge Agent fails data validation.	Yes
20575	Replication: Subscriber has passed data validation	Distribution or Merge Agent passes data validation.	Yes
20578	Replication: agent custom shutdown
22815	Peer-to-peer conflict detection alert	Distribution Agent detected a conflict when it tries to apply a change at a peer-to-peer node.	Yes

I

History of a Job - T-SQL

Just change the Job name and Date, Run the query to get history of a job



SELECT      [JobName]   = JOB.name,

            [Step]      = HIST.step_id,

            [StepName]  = HIST.step_name,

            [Message]   = HIST.message,

            [Status]    = CASE WHEN HIST.run_status = 0 THEN 'Failed'

            WHEN HIST.run_status = 1 THEN 'Succeeded'

            WHEN HIST.run_status = 2 THEN 'Retry'

            WHEN HIST.run_status = 3 THEN 'Canceled'

            END,

            [RunDate]   = HIST.run_date,

            [RunTime]   = HIST.run_time,

            [Duration]  = HIST.run_duration

FROM        msdb..sysjobs JOB

INNER JOIN  msdb..sysjobhistory HIST ON HIST.job_id = JOB.job_id

WHERE    JOB.name = 'JOBNAMEEEE' and hist.run_date>'20130123'

ORDER BY    HIST.run_date, HIST.run_time 

Query to find insert/update on the table in any DB

Step1 . Replace your object name in the place of TABLENAME

 

Step2 . 

Run this

exec sp_MSforeachdb

';with cte as (

select db_id() as dbid,db_name() as dbname,

so.name,

sc.text

from

sysobjects so inner join syscomments sc on so.id = sc.id

where

sc.text like ''%INSERT%INTO%TABLENAME%''

or sc.text like ''%UPDATE%TABLENAME%''

and so.xtype = ''P''

union all

select  db_id() as dbid,db_name() as dbname,

so.name,

sc.text

from

sysobjects so inner join syscomments sc on so.id = sc.id

where

sc.text like ''%insert%into%TABLENAME%''

or sc.text like ''%UPDATE%TABLENAME%''

and so.xtype = ''P''

)

select * from cte where dbid not in (''1'',''2'',''3'',''4'',''5'',''6'')