Option "Off" is not valid. The command-line parameters are invalid.The step failed
SQL Agent job error while running package with password
make sure you did not remove any space while adding /DECRYPT Password
at edit command line manually.
I removed some space and got the error, but got fixed after re doing it with out any space
Exception deserializing the package
"The process cannot access the file because it is being used by another process."
TITLE: Microsoft Visual Studio
------------------------------
Failed to start project
------------------------------
While running SSIS package i got the error “The process cannot access the file ‘*.ispac’ because it is being used by another process”.
The solution?/Fix:
1. Go to Task Manager
2. Details Tab.
3. Locate the process “DtsDebugHost.exe“.
4. Kill this process. There might be multiple instances of this process. Kill all of them.
5. Reexecute SSIS package
https://indatawetrust.blog/2011/08/19/making-ssrs-reports-faster-get-rid-of-parameter-sniffing/
---getdate with HH MM Seconds
(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())USE [FPAuth]
GO
/****** Object: StoredProcedure [dbo].[AlertSendIndexFragmentationdetails_Job_Step3] Script Date: 8/20/2021 8:06:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[AlertSendIndexFragmentationdetails_Job_Step3]
AS
BEGIN
/*
Author:Naresh DBA
Date: 04/01/2016
Purpose:Send Index fragmentation details every 1 hour
Paremeters: page_count_minimum = 1000 and fragmentation_minimum = 30
*/
DECLARE
@Document NVARCHAR(MAX) ,
@DocumentHead NVARCHAR(MAX) ,
@DocumentTail NVARCHAR(MAX) ,
@TableHead NVARCHAR(MAX) ,
@TableBody NVARCHAR(MAX) ,
@TableTail NVARCHAR(MAX) ,
@page_count_minimum SMALLINT ,
@fragmentation_minimum INT;
SET @page_count_minimum =1000;
SET @fragmentation_minimum = 30;
SET @DocumentHead = N'<html><head>' + N'<style>' + N'th, td' + N'{ '
+ N' border: solid black 1px;' + N' padding: 1px 5px 1px 5px;'
+ N' font-size: 11pt;' + N'}' + N'</style>' + N'</head>' + N'<body>'
+ N'<h3>'
+ N'Below are the Indexes in FPAuth Database with Average Fragmentation above 30 Percent'
+ N'</h3>';
SET @DocumentTail = '</body></html>';
SET @TableHead = N'<table cellpadding=0 cellspacing=0 border=0>'
+ N'<tr bgcolor=#C0C0C0>' + N' <th>TableName</th>'
+ N' <th>IndexName</th>' + N' <th>AverageFragmentationPercent</th>'
+ N' <th>PageCount</th>' + N'</tr>';
SET @TableTail = '</table>';
SELECT @TableBody = ( SELECT ROW_NUMBER() OVER ( ORDER BY avg_fragmentation_in_percent DESC )
% 2 AS [TRRow] ,
sys.objects.name AS [TD] ,
sys.indexes.name AS [TD] ,
CONVERT(VARCHAR(10), avg_fragmentation_in_percent, 121) AS [TD] ,
page_count AS [TD]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL,
NULL, NULL,
'LIMITED')
INNER JOIN sys.objects ON sys.objects.object_id = sys.dm_db_index_physical_stats.object_id
INNER JOIN sys.indexes ON sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id
AND sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id
WHERE avg_fragmentation_in_percent > @fragmentation_minimum
AND objects.is_ms_shipped = 0
AND sys.dm_db_index_physical_stats.index_id > 0
--AND sys.indexes.name<>'NIX_AccountTicketNote_AccountTicketID_TicketStateID'--Excluded This particular index Condition on may5th 2016
AND page_count > @page_count_minimum
ORDER BY avg_fragmentation_in_percent DESC
FOR
XML RAW('tr') ,
ELEMENTS
);
IF @TableBody IS NULL
BEGIN
RETURN;
END;
ELSE
BEGIN
SET @TableBody = REPLACE(@TableBody, '_x0020_', SPACE(1));
SET @TableBody = REPLACE(@TableBody, '_x003D_', '=');
SET @TableBody = REPLACE(@TableBody, '<tr><TRRow>1</TRRow>','<tr bgcolor=#C6CFFF>');
SET @TableBody = REPLACE(@TableBody, '<TRRow>0</TRRow>', '');
SELECT @Document = @DocumentHead + @TableHead + @TableBody+ @TableTail + @DocumentTail;
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default',
@recipients = 'abc@xyz.com',
@subject = 'Average Index Fragmentation with above 30 Percent',
@body = @Document,
@body_format = 'HTML';
END
END
---1 create table to log info
USE [AuditDB]
GO
/****** Object: Table [dbo].[NK_BlockingReport] Script Date: 8/20/2021 7:46:13 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NK_BlockingReport](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[ObjectName] [sysname] NULL,
[BlockingObjectName] [sysname] NULL,
[StatementText] [VARCHAR](MAX) NULL,
[BlockingStatementText] [VARCHAR](MAX) NULL,
[SessionID] [INT] NULL,
[BlockingSessionID] [INT] NULL,
[Status] [VARCHAR](50) NULL,
[BlockingStatus] [VARCHAR](50) NULL,
[DateCreated] [DATETIME] NULL,
[DatabaseName] [VARCHAR](100) NULL,
[CPU] [BIGINT] NULL,
[RunningTime] [INT] NULL,
[RuuningFrom] [VARCHAR](800) NULL,
[RuuningBy] [VARCHAR](800) NULL,
[ProgramName] [VARCHAR](100) NULL,
[LoginName] [VARCHAR](100) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
---2. Create view:
USE [AuditDB]
GO
/****** Object: View [dbo].[NK_SP_Active] Script Date: 8/20/2021 7:49:26 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[NK_SP_Active]
AS
SELECT
OBJECT_NAME(objectid) AS ObjectName
,SUBSTRING(stateText.text, (statement_start_offset/2)+1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(stateText.text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1) AS statement_text
,DB_NAME(sess.database_id) AS DatabaseName
,req.cpu_time AS CPU_Time
,DATEDIFF(MINUTE, last_request_start_time, GETDATE()) AS RunningMinutes
,req.Percent_Complete
,sess.HOST_NAME AS RunningFrom
,LEFT(CLIENT_INTERFACE_NAME, 25) AS RunningBy
,sess.session_id AS SessionID
,req.blocking_session_id AS BlockingWith
,req.reads
,req.writes
,sess.[program_name]
,sess.login_name
,sess.status
,sess.last_request_start_time
,req.logical_reads
FROM
sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id
AND sess.is_user_process = 1
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS stateText
GO
---3.Create job
INSERT INTO dbo.NK_BlockingReport (ObjectName,BlockingObjectName, StatementText,BlockingSessionID,BlockingStatementText,
BlockingStatus,DateCreated,[Status],DatabaseName,CPU,RunningTime,RuuningFrom,RuuningBy,ProgramName,LoginName)
SELECT W1.ObjectName AS ObjectName,
W2.ObjectName AS BlockingObjectName,
W1.Statement_Text AS BlockedStatementText,
W2.sessionid AS BlockingSessionID,
W2.Statement_Text AS BlockingStatementText,
W2.status AS BlockingStatus,
GETDATE() AS DateCreated,
w1.[Status] AS CurrentStatus,
w1.DatabaseName ,
w1.[CPU_time] ,
w2.[RunningMinutes] ,
w2.[RunningFrom] ,
w1.[RunningBy] ,
w1.[Program_Name] ,
w1.[Login_Name]
FROM NK_SP_Active W1
INNER JOIN NK_SP_Active W2
ON W1.BlockingWith > 0
AND W1.BlockingWith = W2.sessionid;
---4. Create alert --MANUALY BEST, instaed of script
USE [msdb]
GO
/****** Object: Alert [NK_Report Blocked Processes] Script Date: 8/20/2021 7:44:40 AM ******/
EXEC msdb.dbo.sp_add_alert @name=N'NK_Report Blocked Processes',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@notification_message=N'Blocking Occured please look at BlockingReport in AuditDB',
@category_name=N'[Uncategorized]',
@performance_condition=N'MSSQL$DBA_TEST:General Statistics|Processes blocked||>|1',
@job_id=N'6095582e-4202-497b-af3c-1f7842383739'
GO
IF EXISTS (
SELECT
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null
)
BEGIN
DECLARE @Document NVARCHAR(MAX)
, @DocumentHead NVARCHAR(MAX)
, @DocumentTail NVARCHAR(MAX)
, @TableHead NVARCHAR(MAX)
, @TableBody NVARCHAR(MAX)
, @TableTail NVARCHAR(MAX)
--, @Now DATETIME
SET @DocumentHead = N'<html><head>'
+ N'<style>'
+ N'th, td'
+ N'{ '
+ N' border: solid black 1px;'
+ N' padding: 1px 5px 1px 5px;'
+ N' font-size: 11pt;'
+ N'}'
+ N'</style>'
+ N'</head>'
+ N'<body>'
+ N'<h3>'
+ N'Currently Running Jobs on '+' '+ @@SERVERNAME + ' .'+ 'Please check any long running jobs and Also we should NOT have REBUILD INDEXES,REBUILD STATS, OR FULL BACKUPS running at this moment.' + ' '+ Convert(varchar(19),getdate())
+ N'</h3>';
SET @DocumentTail = '</body></html>';
SET @TableHead = N'<table cellpadding=0 cellspacing=0 border=0>'
+ N'<tr bgcolor=#C0C0C0>'
+ N' <th>Server Name</th>'
+ N' <th>Job Name</th>'
+ N'</tr>';
SET @TableTail = '</table>';
SELECT @TableBody = (SELECT ROW_NUMBER() Over(Order By run_date) % 2 As [TRRow],
@@SERVERNAME AS TD,
j.name AS job_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null
FOR XML RAW('tr'), ELEMENTS);
IF @TableBody IS NULL
BEGIN
PRINT 'No Jobs running right now ';
RETURN;
END
ELSE
BEGIN
--DECLARE @AlertMessage VARCHAR(4000);
--DECLARE @RecipientsList VARCHAR(4000);
DECLARE @subject VARCHAR(4000);
-- Replace the entity codes and row numbers
SET @TableBody = Replace(@TableBody, '_x0020_', space(1))
SET @TableBody = Replace(@TableBody, '_x003D_', '=')
SET @TableBody = Replace(@TableBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
SET @TableBody = Replace(@TableBody, '<TRRow>0</TRRow>', '');
SELECT @Document = @DocumentHead + @TableHead + @TableBody + @TableTail + @DocumentTail
SET @subject='Currently Running Jobs on '+' '+ @@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default'
,@recipients= 'abc@xyz.com'
,@body = @Document--@alertmessgae
,@body_format = 'HTML'
,@importance = 'Low'
,@subject = @subject
--SET @AlertMessage = 'Currently Running Jobs on '+' '+ @@SERVERNAME + ' '+ 'please check any long running jobs and also we should not have Rebuild Indexes,Rebuild Stats,or Full Backups running at this moment'
--SET @subject='Currently Running Jobs on '+' '+ @@SERVERNAME
END
END
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#LastBackUp') IS NOT NULL DROP TABLE #LastBackUp;
IF OBJECT_ID('tempdb..#CSREPORT') IS NOT NULL DROP TABLE #CSREPORT;
SELECT
bs.database_name,
bs.compressed_backup_size,
bs.backup_start_date,
bs.BACKUP_FINISH_DATE,
bmf.physical_device_name,
--Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name,bs.[type] ORDER BY bs.backup_start_date DESC ),
--d.recovery_model_desc
bs.[type]
into #LastBackUp
FROM msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
INNER JOIN master.SYS.databases d on d.name=bs.database_name
WHERE d.state_desc = 'ONLINE'
AND d.is_read_only = 0
AND d.source_database_id IS NULL
and ISNULL(bs.backup_finish_date, GETDATE()-1) > GETDATE()-1
AND bs.type='D'
ORDER BY bms.name
SELECT
@@servername as servername,
CASE WHEN ISNULL((backup_finish_date), GETDATE()-10000) < GETDATE()-31
AND [type] = 'D' THEN 'FAILED'
WHEN ISNULL((backup_finish_date), GETDATE()-10000) < GETDATE()-1
AND [type] = 'I' THEN 'FAILED'
WHEN ISNULL((backup_finish_date), GETDATE()-10000) < GETDATE()-1
AND [type] = 'L' THEN 'FAILED'
WHEN [type] IS NULL THEN 'FAILED'
ELSE 'BACKUP SUCCESS' END AS BackupStatus,
datediff(d, (backup_finish_date), getdate()) as 'full_dayssincelast',
CASE WHEN [type] = 'D' THEN 'Full Backup'
WHEN [type] = 'I' THEN 'Differential Backup'
WHEN [type] = 'L' THEN 'Transaction Log Backup' END AS BackupType,
database_name as databasename ,
backup_start_date,
BACKUP_FINISH_DATE,
CAST((compressed_backup_size) / 1048576 AS DECIMAL(10, 2) ) AS backup_size_mb,
physical_device_name as path,
GETDATE() as insert_date
INTO #CSREPORT FROM #LastBackUp
ORDER BY backup_finish_date desc
DECLARE @tableHTML NVARCHAR(MAX) ;
declare @servername varchar(max)
set @servername=@@SERVERNAME
SET @tableHTML =
N'<H2>Database Backup Status </H2>' +
N'<table border="1" Cellspacing="0" cellpadding="0" style="font-size:x-small;">' +
N'<tr style="background-color:#999999;color:black;font-weight:bold;" align="center">
<td>ServerName</td>' +
N'<td>BackupStatus</td>' +
N'<td> BackupType</td>' +
N'<td> Databasename</td>' +
N'<td> Backup_start_date</td>' +
N'<td> BACKUP_FINISH_DATE</td>' +
N'<td>Backup_size_mb</td>' +
N'<td> Path</td></tr>' +
CAST ( ( SELECT
td = ServerName,'',
td = BackupStatus,'',
td = BackupType,'',
td = Databasename,'',
td = Backup_start_date,'',
td = BACKUP_FINISH_DATE,'',
td = Backup_size_mb,'',
td = Path,''
FROM #CSREPORT
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) )
if (select count(*) FROM #CSREPORT) > 0
Begin
declare @sqlstr varchar (500)
set @sqlstr = 'Database Backup Information '+ @servername
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default', -- DB Mail Profile
@recipients='naresh.koudagani@frontpointsecurity.com', -- Recipients Email
@subject = @sqlstr,
@body = @tableHTML,
@body_format = 'HTML' ;
End
DECLARE @Document NVARCHAR(MAX)
, @DocumentHead NVARCHAR(MAX)
, @DocumentTail NVARCHAR(MAX)
, @TableHead NVARCHAR(MAX)
, @TableBody NVARCHAR(MAX)
, @TableTail NVARCHAR(MAX)
SET @DocumentHead = N'<html><head>'
+ N'<style>'
+ N'th, td'
+ N'{ '
+ N' border: solid black 1px;'
+ N' padding: 1px 5px 1px 5px;'
+ N' font-size: 11pt;'
+ N'}'
+ N'</style>'
+ N'</head>'
+ N'<body>'
+ N'<h3>'
+ N'Disk Space Available on SQL1 SQL Server'
+ N'</h3>';
SET @DocumentTail = '</body></html>';
SET @TableHead = N'<table cellpadding=0 cellspacing=0 border=0>'
+ N'<tr bgcolor=#C0C0C0>'
+ N' <th>Disk Name</th>'
+ N' <th>Available Space in GB </th>'
+ N'</tr>';
SET @TableTail = '</table>';
CREATE TABLE #tbldiskSpace
(
driveName VARCHAR(1000),
freeSpace FLOAT
)
INSERT INTO #tbldiskSpace EXEC master..XP_FixedDrives
SELECT @TableBody = (SELECT ROW_NUMBER() Over(Order By T1.driveName) % 2 As [TRRow]
,T1.driveName AS [TD],
CONVERT(VARCHAR(MAX), Round((freespace)/1024, 1))
FROM #tbldiskSpace T1 ORDER BY drivename
FOR XML RAW('tr'), ELEMENTS);
IF @TableBody IS NULL
BEGIN
PRINT 'N/A';
RETURN;
END
ELSE
BEGIN
-- Replace the entity codes and row numbers
SET @TableBody = Replace(@TableBody, '_x0020_', space(1))
SET @TableBody = Replace(@TableBody, '_x003D_', '=')
SET @TableBody = Replace(@TableBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
SET @TableBody = Replace(@TableBody, '<TRRow>0</TRRow>', '');
SELECT @Document = @DocumentHead + @TableHead + @TableBody + @TableTail + @DocumentTail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default'
, @recipients= 'naresh.koudagani@ABC.com'
, @subject = 'Disk Space Available on SQL SERVER'
, @body = @Document
, @body_format = 'HTML';
END
DROP TABLE #tbldiskSpace
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
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
