November 6, 2017 at 7:20 am
Could WaitForDelay function cause memory pressure like I'm seeing this below? I have a sql server agent job that generates a text file and then wait for another 30 mintues to generate another file. When that happens, sql server close the connection and nobody can access to it and this is what's get written in the error log. Anybody has any idea on this?
2017-11-06 02:16:10.46 spid30s AppDomain 100 (SSISDB.dbo[runtime].158) is marked for unload due to memory pressure.
2017-11-06 02:16:10.46 spid30s AppDomain 100 (SSISDB.dbo[runtime].158) unloaded.
2017-11-06 02:17:13.88 spid18s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 340 seconds. Working set (KB): 9900916, committed (KB): 314175296, memory utilization: 3%.
November 6, 2017 at 7:36 am
Is the WAITFOR statement part of a transaction, either explicitly in the code or in the way the task is configured in the package?
John
November 6, 2017 at 7:36 am
SQL_Surfer - Monday, November 6, 2017 7:20 AMWhen that happens, sql server close the connection and nobody can access to it ...
Which connection are you referring to? Is there some locking going on?
Are any other intensive processes running at the time this happens?
WaitForDelay is a T-SQL command, yet the error log entries indicate that this is an SSIS problem. What is the structure of your Agent job?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 6, 2017 at 7:43 am
John Mitchell-245523 - Monday, November 6, 2017 7:36 AMIs the WAITFOR statement part of a transaction, either explicitly in the code or in the way the task is configured in the package?John
It's an agent like this below
exec master.dbo.xp_cmdshell 'echo here>\\server\folder\file1.txt'
WAITFOR DELAY '000:30:00'
exec master.dbo.xp_cmdshell 'echo here>\\server\folder\file2.txt'
And this has been working great for several years now.
November 6, 2017 at 7:47 am
Phil Parkin - Monday, November 6, 2017 7:36 AMSQL_Surfer - Monday, November 6, 2017 7:20 AMWhen that happens, sql server close the connection and nobody can access to it ...Which connection are you referring to? Is there some locking going on?
Are any other intensive processes running at the time this happens?
WaitForDelay is a T-SQL command, yet the error log entries indicate that this is an SSIS problem. What is the structure of your Agent job?
So there are other agent jobs running from other server try to access this server and fails. There are some reports trying to access this server and fails. When I tried to manually connect to it, it fails (times out).
No other process running on the server
Yeah, it is kind of off odd that it indicates SSIS problem. There are SSIS packages runs off this box but at the time when error happens, those are done.
November 6, 2017 at 5:27 pm
SQL_Surfer - Monday, November 6, 2017 7:47 AMSo there are other agent jobs running from other server try to access this server and fails. There are some reports trying to access this server and fails. When I tried to manually connect to it, it fails (times out).
No other process running on the server
Yeah, it is kind of off odd that it indicates SSIS problem. There are SSIS packages runs off this box but at the time when error happens, those are done.
It's not necessarily saying that it's an SSIS problem - that was just the AppDomain that was unloaded. May or may not have something to do with SSIS.
Have you checked other non-SQL Server processes running on the server that may be consuming a good deal of memory?
Did you set Max Memory for the SQL Server instance?
You may want to do some monitoring of the memory on the server with Perf Mon. SQL Server may not necessarily be the cause. When you can't connect during the issues, trying using the DAC to connect so that you can connect and see whats going on with SQL Server itself.
Sue
November 7, 2017 at 1:59 am
So, I checked the sql server today and there wasn't anything running on the box. Again, SQL server became unresponsive for few minutes and this is the below message on the error log.
Anybody can point me on what I should do?
A significant part of sql server process memory has been paged out. This may result in a performance degradation.
Duration: 602 seconds. Working set (KB): 3860628, committed (KB): 342039316, memory utilization: 1%.
November 12, 2017 at 1:24 pm
Is SSIS on the same server as the SQL Server instance?
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply