What transaction is causing log space to fill out

 

-- -- -- -- --Transaction causing log space filled most-- -- -- -- --

SELECT tst.[session_id],

s.[login_name] AS [Login Name],

DB_NAME (tdt.database_id) AS [Database],

tdt.[database_transaction_begin_time] AS [Begin Time],

tdt.[database_transaction_log_record_count] AS [Log Records],

tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used],

tdt.[database_transaction_log_bytes_reserved] AS [Log Bytes Rsvd],

SUBSTRING(st.text, (r.statement_start_offset/2)+1, 

((CASE r.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE r.statement_end_offset

END - r.statement_start_offset)/2) + 1) AS statement_text,

st.[text] AS [Last T-SQL Text],

qp.[query_plan] AS [Last Plan]

FROM sys.dm_tran_database_transactions tdt

JOIN sys.dm_tran_session_transactions tst

ON tst.[transaction_id] = tdt.[transaction_id]

JOIN sys.[dm_exec_sessions] s

ON s.[session_id] = tst.[session_id]

JOIN sys.dm_exec_connections c

ON c.[session_id] = tst.[session_id]

LEFT OUTER JOIN sys.dm_exec_requests r

ON r.[session_id] = tst.[session_id]

CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st

OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp

where DB_NAME (tdt.database_id) = 'reportingdb'

ORDER BY [Log Bytes Used] DESC;

Find who are in what groups

 xp_logininfo 'domaun\Groupname', 'members'--find what logins inside group

purge or delete files older than x days

 

DECLARE @DeleteDate datetime

SET @DeleteDate = DateAdd(day, -3, GetDate()) 

EXECUTE master.sys.xp_delete_file 0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)

N'\\xyz\abc\12\', -- folder path (trailing slash)

N'bak', -- file extension which needs to be deleted (no dot)

@DeleteDate, -- date prior which to delete 

1 -- subfolder flag (1 = include files in first subfolder level, 0 = not) 

PowerShell script to move files from source location to destination and then delete the file, folder and all files with name

 

$ErrorActionPreference = "SilentlyContinue"
$path = "\\abc\c\NareshTest.zip"
$dest = "\\xyz\d\abc"

$ErrorActionPreference = "SilentlyContinue"
Expand-Archive -LiteralPath " \\abc\c\NareshTest.zip " -DestinationPath " \\xyz\d\abc" -Force

Copy-Item -Path " \\abc\c\NareshTest  .csv" -Destination " \\xyz\d\abc" -Force
 

Start-Sleep -s 30

Remove-Item -Path " \\abc\c\NareshTest.csv " -Force
Remove-Item -Path " \\abc\c\NareshTest.zip" -Force
<#
Remove-Item -Path " \\abc\c\NareshTest.*" -Force
#>

delivering replicated transactions no update on subscriber--Replication seems stuck( SOLVED)

 delivering replicated transactions 

1. I have a translation replication and it runs fine all day, all of a sudden the distribution job says 

 delivering replicated transactions 

2, at 8am when the business starts customer agent /accounting team say data is not updated on the application. ( data gets from another server)

This happens when data is not being updated on the subscriber.

next:

1. I looked at the distribution job which was running for almost 12 hours and no data us being updated and it is holding X lock on Subscriber, not blocking anything or no performance issues except data is not being updated.

I see there are so many indexes on Subscriber matching publisher and thought that could be the reason the update is taking ever

Note: you will have to only keep the indexes u need, find unused and clean them frequently

so i decided to stop the distribution job, drop indexes and start the distribution job -did not work.

tried MSDN, google a lot of suggestions.

btw- make sure replication cleanup jobs run off-hours and they could be sometimes blocking a while cleaning replicates tables ..these also did not help for me.

after 2 days of struggle, i found that daily at 6am there are BULK GP transactions are being posted on the publisher and that is taking ever to get replicated, so I decided to start a snapshot agent( reinitialize subscription or invalidate the snapshot ) at 7am after GP posting is done and then all looks green..

 

--1.daily 07:15am;

---re intiallize subscription by invaliding the snapshot:job

use DBNAME

go

exec sp_reinitsubscription

@publication = PUBLICATIONANME', 

@subscriber = 'SUBSCRIBER'---all,

,@destination_db ='DBNAME',

@invalidate_snapshot =1

go

---O/P: Invalidated the existing snapshot of the publication. Run the Snapshot Agent again to generate a new snapshot.

--RUn below to START SNAPSHOT JOB  

USE msdb ;  

GO    

EXEC dbo.sp_start_job N'SNAPSHOT AGENT JOB' ;  

GO 

 --MONITOR SNAPSHOT AND DISTRIBUTION JOB: BOTH SHOULD RUN, WAIT FOR 5 MINUTES IF NOT START DISTRIBUTION JOB:

 ---IF worked PLAN TO AUTOMATE ..until the tables data moves to history 

check snapshot agent repl data folder 

also, check the count of rows using except

IF NOT EXISTS (

select count(*) from DBname.dbo.Table with (nolock)---20574581

EXCEPT

select count(*) from Subscriber server. dbname..dbo.table with (nolock)---20574581

)

if matches or no send an email ...

immediatesnapshot=1, allowanonymus=1: don't change :::

also, check indexes on the subscriber( in my case 1 was only useful)

 

Cannot construct data type date, some of the arguments have values which are not valid.

 drop table test 

go

create table test 

(ExpDate  varchar(20))

insert into test values 

('06/22'), ('01/22'), ('02/22'), ('02/20'), ('01/20')

;with cte as (

Select DATEFROMPARTS(2000 + CAST(right(ExpDate,2) AS INT), CAST(left(ExpDate,2) AS INT), 1) AS ExpDate

from test 

)

SELECT ExpDate

from cte

WHERE(ExpDate) BETWEEN DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) AND DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)

/*

ExpDate

----------

2020-02-01

2020-01-01

*/

insert into test values ('13/22')

;with cte as (

Select DATEFROMPARTS(2000 + CAST(right(ExpDate,2) AS INT), CAST(left(ExpDate,2) AS INT), 1) AS ExpDate

from test 

)

SELECT ExpDate

from cte

WHERE(ExpDate) BETWEEN DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) AND DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)

/*

ExpDate

----------

2020-02-01

2020-01-01

Msg 289, Level 16, State 1, Line 22

Cannot construct data type date, some of the arguments have values which are not valid.

*/

--using CTE tables  and DATEFROMPARTS: did not work 

--Cannot construct data type date, some of the arguments have values which are not valid.

-----USE TEMP TABLES INSTED OF CTE , 

create logins in primary and secondary

 USE master --Create on primary
 go
CREATE LOGIN SQLLogin
WITH PASSWORD=P@SSWORD!'
, DEFAULT_DATABASE=[master]
GO


 SELECT name , sid
FROM sys.syslogins
ORDER BY name


 USE master --Create on primary
 go
CREATE LOGIN SQLLogin
WITH PASSWORD=P@SSWORD!'

--should get sid from primary
, SID = 0x03DA4646CC8E3D4B9327E64120DBF536
, DEFAULT_DATABASE=[master]
GO

USE the same SID AND crate ON secondary --database user permissions should replicate 

Changing Notification Operator for Multiple or all SQL Agent Jobs

--Check to see if operator exists currently:
SELECT [name], [id], [enabled] FROM msdb.dbo.sysoperators
ORDER BY [name];

--Declare variables and set values:
DECLARE @operator_id int

SELECT @operator_id = [id] FROM msdb.dbo.sysoperators
WHERE name = 'SQLOperDBA'

--Update the affected rows with new operator_id:
UPDATE msdb.dbo.sysjobs
SET notify_email_operator_id = @operator_id
FROM msdb.dbo.sysjobs
LEFT JOIN msdb.dbo.sysoperators O
ON msdb.dbo.sysjobs.notify_email_operator_id = O.[id]
--check where clause , if not it is going to update all jobs

You cannot do an online rebuild of a clustered index if the table contains any LOB data (text, ntext, image, varchar(max), nvarchar(max), varbinary(max))

My rebuild index job (With ONLINE-Enterprise edition only) failed with error 




An online operation cannot be performed for %S_MSG '%.*ls' because the index contains column '%.*ls' 
of data type text, ntext, image or FILESTREAM. 
For a non-clustered index, the column could be an include column of the index. For a clustered index,
 the column could be any column of the table.
  If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.




select * from sys.messages 
where message_id=2725 and language_id=1033

You cannot do an online rebuild of a clustered index if the table contains any LOB data (text, ntext, image, varchar(max), nvarchar(max), varbinary(max))



Solution to search the Table with LOB data (text, ntext, image, varchar(max), nvarchar(max), varbinary(max))
and for that particular db, modify the rebuild indexes job not to online rebuild..


SELECT o.[name], o.[object_id ], c.[object_id ], c.[name], t.[name]
FROM sys.all_columns c
INNER JOIN sys.all_objects o
ON c.object_id = o.object_id
INNER JOIN sys.types t
ON c.system_type_id = t.system_type_id 
WHERE c.system_type_id IN (35, 165, 99, 34, 173)
AND o.[name] NOT LIKE 'sys%'
AND o.[name] <> 'dtproperties'
AND o.[type] = 'U'
GO

Will the linked server honor the NOLOCK hint?

Will the linked server honor the NOLOCK hint? -No

Ex:
INSERT INTO  CallHistory
SELECT *  FROM LINKSERVER.DATABASE.DBO.CallHistory WITH(NOLOCK)WHERE callplacedtimeUTC >= DATEADD(hh,-4,GETUTCDATE())  ORDER BY callplacedtimeUTC DESC
linked server do not  honor the NOLOCKso Create a view like below 
 Create view test
as
SELECT *  FROM  DATABASE.DBO.CallHistory WITH(NOLOCK)WHERE callplacedtimeUTC >= DATEADD(hh,-4,GETUTCDATE()) 

and then Finally:INSERT INTO  CallHistory
SELECT *  FROM LINKSERVER.DATABASE.DBO.test  --view  ORDER BY callplacedtimeUTC DESC

converting local time to UTC time With daylight Savings

I have a job to get data from a table with 4 hours data..my column is datetime and UTC default. and i prepared some logic below...
SELECT *  FROM   CallHistory WITH(NOLOCK)
WHERE callplacedtimeUTC >=  DATEADD(hh,-4,GETUTCDATE())
GO

But it dint work with daylight savings...so, my boss asked me to convert local time to UTC time which will work and gave me below logic...
Just  add below in place of -4

SELECT DATEADD(HOUR, -1 * DATEDIFF(HOUR, GETDATE(), GETUTCDATE()), GETUTCDAT

SELECT *  FROM  CallHistory WITH(NOLOCK)
WHERE callplacedtimeUTC >=  DATEADD(hh,-1 * DATEDIFF(HOUR, GETDATE(), GETUTCDATE()),GETUTCDATE())
GO


it works for daylight savings...