send space alert

 USE [master]

GO

/****** Object:  StoredProcedure [dbo].[SendSpaceAlert]    Script Date: 7/29/2021 12:36:59 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

 

ALTER  PROC [dbo].[SendSpaceAlert]

AS

BEGIN

SET NOCOUNT ON

Declare @Drives Varchar(20)

DECLARE @Spaces Varchar(50)

DECLARE @availableSpace FLOAT

DECLARE @alertMessage VARCHAR(4000)

DECLARE @RecipientsList  VARCHAR(4000);

CREATE TABLE #tbldiskSpace

(

driveName VARCHAR(3),

freeSpace FLOAT

)

INSERT INTO #tbldiskSpace EXEC master..XP_FixedDrives

SELECT @Drives = Isnull(@Drives, '') + drivename + ',' 

FROM   #tbldiskspace 

WHERE  freespace  < 20000

SELECT @Spaces = Isnull(@Spaces, '') 

                 + CONVERT(VARCHAR(10), Round((freespace)/1024, 1)) 

                 + ' GB, ' 

FROM   #tbldiskspace 

WHERE  freespace  < 20000

ORDER  BY drivename 

IF LEN(@Drives) > 1 

BEGIN 

    SET @alertMessage = 'Disk space is critical on ' + @@SERVERNAME 

                        + '. Free Space Available on ' + @Drives 

                        + 'Drive/s is ' + CAST(@Spaces AS VARCHAR) 

    SET @RecipientsList =N'abc@abccom'

    EXEC msdb.dbo.Sp_send_dbmail 

      @profile_name = 'Default', 

      @recipients =@RecipientsList,

      @body = @alertMessage, 

      @importance = 'High', 

      @subject = 'LOW SPACE ON SERVER'; 

END 

DROP TABLE #tbldiskSpace

END

check database status and send email

 USE [master]

GO

/****** Object:  StoredProcedure [dbo].[Database_Status]    Script Date: 7/29/2021 12:26:36 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER  PROC [dbo].[Database_Status]

AS

BEGIN 

--send email when database changes status--run every 1 min as a job 

DECLARE @state VARCHAR(10)

DECLARE @Database_Name VARCHAR(100)

DECLARE @email VARCHAR(100)

SET @state = ''

SET @email = ''

DECLARE db_cursor CURSOR FOR  

SELECT name 

FROM MASTER.dbo.sysdatabases

WHERE name NOT IN ('pportal')

OPEN db_cursor   

FETCH NEXT FROM db_cursor INTO @Database_Name   

WHILE @@FETCH_STATUS = 0   

BEGIN 

SELECT @state = 

CASE 

WHEN a.state = 0 THEN 'Online'

WHEN a.state = 1 THEN 'Restoring'

WHEN a.state = 2 THEN 'Recovery_Pending'

WHEN a.state = 4 THEN 'Suspect'

WHEN a.state = 5 THEN 'Emergency'

WHEN a.state = 6 THEN 'Offline'

WHEN a.state = 7 THEN 'Copying - SQL Azure'

WHEN a.state = 10 THEN 'Ofline_Secondary - SQL Azure'

END

FROM sys.databases a WHERE name = @Database_Name

IF @state <> 'Online'

BEGIN

SET @email = 'The database ' + @Database_Name + ' is currently : ' + @state

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Default',

    @recipients = 'kumardba99@gmail.com',

    @body = @email,

    @subject = 'Database Status' ;

END

  FETCH NEXT FROM db_cursor INTO @Database_Name   

END 

CLOSE db_cursor   

DEALLOCATE db_cursor

END 

Find resource usage by Application

 

SELECT 

     CPU            = SUM(cpu_time)

    ,WaitTime       = SUM(total_scheduled_time)

    ,ElapsedTime    = SUM(total_elapsed_time)

    ,Reads          = SUM(num_reads) 

    ,Writes         = SUM(num_writes) 

    ,Connections    = COUNT(1) 

    ,Program        = program_name

    ,LoginName      = ses.login_name

FROM sys.dm_exec_connections con

LEFT JOIN sys.dm_exec_sessions ses

    ON ses.session_id = con.session_id

GROUP BY program_name, ses.login_name

ORDER BY cpu DESC

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