question

shamim007 avatar image
shamim007 asked

Agent job failed

On Prod server Agent job failed . Executed as user: Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Violation of PRIMARY KEY constraint 'PK_WhoIsActive'. Cannot insert duplicate key in object 'monitoring.WhoIsActive'. The duplicate key value is (Jan 20 2017 8:25AM, 109). [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed. Any idea why i get this error ? What steps should i follow to fix that error ?
agentjob failure
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sunil2711 avatar image
sunil2711 answered
there is primary key constraint on table it won't allow duplicates. Check your data what is inserting and modify SQL.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jason_clark03 avatar image
jason_clark03 answered
> set ansi_warnings off Hope this helps!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Warriors avatar image
Warriors answered
Hello I'm trying to automate restore from prod to test using the latest backup files. We keep 3 days of backup files and need to pull in the latest for restore across a different server. I tried using the below query but keep getting erred out. Would anyone care to share your expertise and make the query work? Thank you in advance. SET NOCOUNT ON DECLARE @LinkedServer Varchar(255) SET @LinkedServer = 'vmsqltest12' DECLARE @DataFileLocation Varchar(255) SET @DataFileLocation = 'e:\data' DECLARE @LogFileLocation Varchar(255) SET @LogFileLocation = 'D:\Tlogs' DECLARE @ExcludeDbs Varchar(255) SET @ExcludeDbs = '(''master'' , ''model'' , ''msdb'', ''tempdb'', ''arch2005'',''audit_c3d'',''bgicsettings'',''bgpamsessions'',''blicense'',''cherwell'',''cherwell_test'',''cherwellproddev'',''citrixwichitatest'', ''cms_c3d'',''gargage'',''mun_easecagdb'',''Mun-EaseCowDb'',''Mun-EaseEmptyDb'',''Mun-EaseExamplesDb'',''Mun-EaseMAEDb'',''Mun-EaseMainDb'',''Mun-EaseMAINFEEDb'', ''OldProductionCherwell'',''pets'',''ReportServer'',''ReportServerTempDB'',''RMA_BIS_Audit'',''RMA_BIS_CMS'',''RMA_DA_REPOSITORY'',''RMA_DA_STAGING'',''RMA_Production'', ''RMA_Production_Docs'',''RMA_Security'',''RMA_Session'',''RMA_SMServer'',''RMA_TaskManager'',''RMA_Training'',''RMA_Training_Docs'',''RMA_Views'',''SysPsi'')' DECLARE @RestoreDbs Varchar(255) SET @RestoreDbs = '' DECLARE @dbname Varchar(100) , @physicalpath Varchar(500) , @BackupDate Datetime , @cmd nvarchar(max) , @logicalname Varchar(255) , @PhysicalFileName Varchar(max) , @type Varchar(5) --Checks linked server exists, if not then linked server is added, requires ALTER ANY LINKED SERVER permission. IF NOT EXISTS (SELECT * FROM SYS.servers Where name = @LinkedServer) EXEC sp_addlinkedserver @LinkedServer -- If data file location is not specified then data files will be restored to default data file location. IF @DataFileLocation IS NULL SELECT @DataFileLocation = SUBSTRING(physical_name, 1,CHARINDEX(N'master.mdf',LOWER(physical_name)) - 2) FROM master.sys.master_files WHERE database_id = 1 AND FILE_ID = 1 -- If log file location is not specified then log files will be restored to default log file location. IF @LogFileLocation IS NULL SELECT @LogFileLocation = SUBSTRING(physical_name, 1,CHARINDEX(N'mastlog.ldf',LOWER(physical_name)) - 2) FROM master.sys.master_files WHERE database_id = 1 AND FILE_ID = 2 CREATE TABLE #filelist ( LogicalName VARCHAR(255), PhysicalName VARCHAR(500), [Type] VARCHAR(1), FileGroupName VARCHAR(64), Size DECIMAL(20, 0), MaxSize DECIMAL(25,0), FileID bigint, CreateLSN DECIMAL(25,0), DropLSN DECIMAL(25,0), UniqueID UNIQUEIDENTIFIER, ReadOnlyLSN DECIMAL(25,0), ReadWriteLSN DECIMAL(25,0), BackupSizeInBytes DECIMAL(25,0), SourceBlockSize INT, filegroupid INT, loggroupguid UNIQUEIDENTIFIER, differentialbaseLSN DECIMAL(25,0), differentialbaseGUID UNIQUEIDENTIFIER, isreadonly BIT, ispresent BIT , TDEThumbprint Varchar(255)) --Queries backupset and backupmediafamily tables on remote msdb database to get latest full backup. SET @cmd = 'DECLARE restore_db Cursor For SELECT a.database_name , BackupDate , physical_device_name FROM ['+ @LinkedServer+'].msdb.dbo.backupset A ' + ' INNER JOIN (SELECT database_name , BackupDate = MAX(backup_finish_date) ' + ' FROM ['+@LinkedServer+'].msdb.dbo.backupset ' + ' WHERE type = ''D'' ' IF @RestoreDbs IS NULL SET @cmd = @cmd + ' AND database_name NOT IN '+ @ExcludeDbs +' And backup_finish_date >= DATEADD(MONTH , -1 , GETDATE()) ' ELSE SET @cmd = @cmd + ' AND database_name IN '+ @RestoreDbs SET @cmd = @cmd + ' GROUP BY database_name ) as b ' + ' ON A.database_name = b.database_name and a.backup_finish_date = BackupDate ' + ' INNER JOIN ['+ @LinkedServer +'].msdb.dbo.backupmediafamily c ON c.media_set_id = a.media_set_id ORDER BY database_name ' exec sp_executesql @cmd DECLARE restore_db cursor for SELECT @dbname , @BackupDate, @physicalpath OPEN restore_db FETCH NEXT FROM restore_db INTO @dbname , @BackupDate , @physicalpath WHILE @@FETCH_STATUS = 0 BEGIN --Check database to be restored is already there in this server, if yes then just restore with replace. IF EXISTS (SELECT * FROM sys.databases WHERE name = @dbname) BEGIN --Get rid of any existing connections, so that our restore process go smoothly. DECLARE @kill varchar(8000) set @kill = 'killing'; SELECT @kill=@kill+'kill '+convert(varchar(5),spid)+';' FROm master.dbo.sysprocesses WHERE dbid=db_id(''+ @dbname + ''); IF len(@kill) <> 0 exec sp_executesql @kill; SET @cmd = 'RESTORE DATABASE [' + @dbname +'] FROM DISK = '''+ @physicalpath +''' WITH STATS = 1 , REPLACE ' Exec sp_executesql @cmd; END ELSE BEGIN -- If database is not already there then go through the filelist and move to appropriate locations. SET @cmd = 'RESTORE FILELISTONLY FROM DISK= '''+ @physicalpath +'''' INSERT INTO #filelist EXEC (@cmd) SET @cmd = 'RESTORE DATABASE ['+ @dbname +'] FROM DISK = '''+ @physicalpath +''' WITH STATS = 1 , ' DECLARE file_list cursor for SELECT LogicalName, PhysicalName, Type FROM #filelist ORDER BY type OPEN file_list FETCH NEXT FROM file_list into @LogicalName, @PhysicalFileName, @type WHILE @@fetch_status = 0 BEGIN -- If it is data file move to data file location. IF @type = 'D' SET @cmd = @cmd + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @DataFileLocation +'\'+ Substring(@PhysicalFileName , LEN(@PhysicalFileName)-CHARINDEX('\' , REVERSE(@PhysicalFileName))+2 , CHARINDEX('\' , REVERSE(@PhysicalFileName))) + ''',' ELSE -- Log files move to log file location. SET @cmd = @cmd + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @LogFileLocation + '\'+ Substring(@PhysicalFileName , LEN(@PhysicalFileName)-CHARINDEX('\' , REVERSE(@PhysicalFileName))+2 , CHARINDEX('\' , REVERSE(@PhysicalFileName))) + '''' FETCH NEXT FROM file_list into @LogicalName, @PhysicalFileName, @type END CLOSE file_list DEALLOCATE file_list truncate table #filelist Exec sp_executesql @cmd END FETCH NEXT FROM restore_db INTO @dbname , @BackupDate , @physicalpath END CLOSE restore_db DEALLOCATE restore_db drop table #filelist
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This seems to be a different question than the original one posted by the OP. You might be better off to start a new thread for this specific issue.
0 Likes 0 ·
Warriors avatar image
Warriors answered
JohnM, I appreciate the input. I tried creating a new thread but being a new member to the forum I did not have enough credential to start a new topic. So thought I would give it a shot and attached my question to an existing thread.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.