Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

SQL Agent Job sometimes failes

We have a SQL Agent Job which runs every day (at night). Sometimes this job failes at one step with the following error message:

"Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000] (Error 0) " .

At this time nothing is going on on the database!
The step which failes executes an Stored Procedure.
Here is the Code of that Stored Procedure:
DELETE FROM table WHERE DATEDIFF (dd, column, getdate()) = @iAnzahlTage)
SELECT table, table2, var1 = convert(varchar(8), ...)
 
INTO #tmpTable
FROM Table
WHERE DATEDIFF ...
AND ...
 
 
INSERT INTO ...
SELECT ...
FROM #tmpTable
GROUP BY ...
 
drop table #tmpTable

Open in new window

0
ALSO_DB
Asked:
ALSO_DB
  • 5
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>DELETE FROM table WHERE DATEDIFF (dd, column, getdate()) = @iAnzahlTage)
should be better:
DELETE FROM table WHERE column < DATEADD (dd, - @iAnzahlTage, getdate()) 
same expression should be used on the other WHERE clauses, and ensure you have a index on the column...

Open in new window

0
 
ALSO_DBAuthor Commented:
That comment didnt help me to find a solution, why does this Job failed sometimes and sometimes its having no failure...!!!
0
 
ALSO_DBAuthor Commented:
No helpful answer recieved...
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
ALSO_DBAuthor Commented:
No helpful answer recieved...
0
 
ALSO_DBAuthor Commented:
No helpful answer recieved
0
 
ALSO_DBAuthor Commented:
No helpful answer recieved...
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now