July 6, 2017 at 6:45 am
Hi guys,
We have 3 instances bidirectionally replicated (transactional).
One of them crashed. Sql dump files were created every 2 seconds saying "Access violation when reading address".
We think installing SP3 might help. Should we install it in any order?
2 instances run on windows server 2012 and the other instance runs on windows server 2008 r2.
Regards
July 6, 2017 at 6:47 am
Yes you should always install the latest service pack.
Thanks
July 6, 2017 at 7:00 am
Btw, all 3 instances are configured to use uo all memory.
Do you think this is an issue?
July 6, 2017 at 7:31 am
rogelio.vidaurri - Thursday, July 6, 2017 7:00 AMBtw, all 3 instances are configured to use uo all memory.Do you think this is an issue?
Limit the max memory of all instances to leave room for the OS and what ever else is running on the servers.
😎
Haven't had any problems with 2012 instance which is without any patching, it's been running in a sandbox since the release of 2012, no downtime so far.
July 6, 2017 at 7:31 am
Yes it will be a problem as it will leave no memory for the OS to use. You need to split the memory between the 3 instances plus leave some for the OS.
How much memory in total do you have?
Thanks
July 6, 2017 at 8:15 am
for clarification: 3 SQL server instances = I meant 3 different database servers, not the same database server
all of them have the same exact configuration
2 of them have 64GB RAM
how much should I reserve for OS? it's just a database server, nothing else running
does it require a reboot?
thanks!
July 6, 2017 at 8:22 am
Set your Max Server memory to 60gb and no it doesn't need a reboot.
Thanks
July 6, 2017 at 8:33 am
so, do you suggest us to limit memory, and redirect traffic back to the primary database instance (currently we are running on the secondary instance) and see if it doesn't happen again?
instead of installing SP3
regards
July 6, 2017 at 8:38 am
rogelio.vidaurri - Thursday, July 6, 2017 8:33 AMso, do you suggest us to limit memory, and redirect traffic back to the primary database instance (currently we are running on the secondary instance) and see if it doesn't happen again?
instead of installing SP3regards
You need to limit memory on both and you should also install SP3 on both.
Thanks
July 6, 2017 at 8:41 am
NorthernSoul - Thursday, July 6, 2017 8:38 AMrogelio.vidaurri - Thursday, July 6, 2017 8:33 AMso, do you suggest us to limit memory, and redirect traffic back to the primary database instance (currently we are running on the secondary instance) and see if it doesn't happen again?
instead of installing SP3regards
You need to limit memory on both and you should also install SP3 on both.
Thanks
Do you have anymore details on the error you are getting besides "Access violation when reading address"?
Thanks
July 6, 2017 at 9:22 am
NorthernSoul - Thursday, July 6, 2017 8:41 AMNorthernSoul - Thursday, July 6, 2017 8:38 AMrogelio.vidaurri - Thursday, July 6, 2017 8:33 AMso, do you suggest us to limit memory, and redirect traffic back to the primary database instance (currently we are running on the secondary instance) and see if it doesn't happen again?
instead of installing SP3regards
You need to limit memory on both and you should also install SP3 on both.
Thanks
Do you have anymore details on the error you are getting besides "Access violation when reading address"?
Thanks
We started to have blocking issues (seen by sp_whoisactive) in our primary database server. a SQLText "NULL" process executed on master was blocking SQL dependency processes and some Stored Procedures too. It took more than an hour for that blocking process to go away. However, the SQL dumps were still occurring.
Time matched the first SQL dump file.
In this folder C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\Log we started to see a lot of SQLDump files (each of them = 400MB aprox, every 2 minutes). Of course, they filled our drive.
They show this error:
***Stack Dump being sent to C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0039.txt
SqlDumpExceptionHandler: Process 129 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 07/05/17 17:08:44 spid 129
*
*
* Exception Address = 00007FFE5DCF7A8E Module(sqlmin+0000000000C27A8E)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 0000000000000010
* Input Buffer 109 bytes -
Regards
July 6, 2017 at 9:38 am
rogelio.vidaurri - Thursday, July 6, 2017 9:22 AMNorthernSoul - Thursday, July 6, 2017 8:41 AMNorthernSoul - Thursday, July 6, 2017 8:38 AMrogelio.vidaurri - Thursday, July 6, 2017 8:33 AMso, do you suggest us to limit memory, and redirect traffic back to the primary database instance (currently we are running on the secondary instance) and see if it doesn't happen again?
instead of installing SP3regards
You need to limit memory on both and you should also install SP3 on both.
Thanks
Do you have anymore details on the error you are getting besides "Access violation when reading address"?
Thanks
We started to have blocking issues (seen by sp_whoisactive) in our primary database server. a SQLText "NULL" process executed on master was blocking SQL dependency processes and some Stored Procedures too. It took more than an hour for that blocking process to go away. However, the SQL dumps were still occurring.
Time matched the first SQL dump file.
In this folder C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\Log we started to see a lot of SQLDump files (each of them = 400MB aprox, every 2 minutes). Of course, they filled our drive.They show this error:
***Stack Dump being sent to C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0039.txt
SqlDumpExceptionHandler: Process 129 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 07/05/17 17:08:44 spid 129
*
*
* Exception Address = 00007FFE5DCF7A8E Module(sqlmin+0000000000C27A8E)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 0000000000000010
* Input Buffer 109 bytes -Regards
Update both the OS and the SQL Server instances to the latest patch level, run diagnostics on the hardware and configure the max memory for the SQL Server instances to leave breathing space for the OS. Are there any other services running on there?
😎
Further, check if anti-virus excludes the data and log directories, turn off windows file indexing for those directories etc. Nothing but SQL Server should be accessing those.
July 6, 2017 at 10:22 am
Eirikur Eiriksson - Thursday, July 6, 2017 9:38 AMrogelio.vidaurri - Thursday, July 6, 2017 9:22 AMNorthernSoul - Thursday, July 6, 2017 8:41 AMNorthernSoul - Thursday, July 6, 2017 8:38 AMrogelio.vidaurri - Thursday, July 6, 2017 8:33 AMso, do you suggest us to limit memory, and redirect traffic back to the primary database instance (currently we are running on the secondary instance) and see if it doesn't happen again?
instead of installing SP3regards
You need to limit memory on both and you should also install SP3 on both.
Thanks
Do you have anymore details on the error you are getting besides "Access violation when reading address"?
Thanks
We started to have blocking issues (seen by sp_whoisactive) in our primary database server. a SQLText "NULL" process executed on master was blocking SQL dependency processes and some Stored Procedures too. It took more than an hour for that blocking process to go away. However, the SQL dumps were still occurring.
Time matched the first SQL dump file.
In this folder C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\Log we started to see a lot of SQLDump files (each of them = 400MB aprox, every 2 minutes). Of course, they filled our drive.They show this error:
***Stack Dump being sent to C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0039.txt
SqlDumpExceptionHandler: Process 129 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 07/05/17 17:08:44 spid 129
*
*
* Exception Address = 00007FFE5DCF7A8E Module(sqlmin+0000000000C27A8E)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 0000000000000010
* Input Buffer 109 bytes -Regards
Update both the OS and the SQL Server instances to the latest patch level, run diagnostics on the hardware and configure the max memory for the SQL Server instances to leave breathing space for the OS. Are there any other services running on there?
😎
Further, check if anti-virus excludes the data and log directories, turn off windows file indexing for those directories etc. Nothing but SQL Server should be accessing those.
a monitoring tool, an enterprise anti-virus, a backup software, and SQL Server are running
I've seen SQL Server use up to 61 out of the 64 GB
Should I reserve 60GB for SQL then, or less?
anti-virus has always excluded MDF and LDF files
I think we don't have the windows file indexing service as it is not listed in services.msc
thanks!
July 6, 2017 at 10:42 am
rogelio.vidaurri - Thursday, July 6, 2017 10:22 AMEirikur Eiriksson - Thursday, July 6, 2017 9:38 AMrogelio.vidaurri - Thursday, July 6, 2017 9:22 AMNorthernSoul - Thursday, July 6, 2017 8:41 AMNorthernSoul - Thursday, July 6, 2017 8:38 AMrogelio.vidaurri - Thursday, July 6, 2017 8:33 AMso, do you suggest us to limit memory, and redirect traffic back to the primary database instance (currently we are running on the secondary instance) and see if it doesn't happen again?
instead of installing SP3regards
You need to limit memory on both and you should also install SP3 on both.
Thanks
Do you have anymore details on the error you are getting besides "Access violation when reading address"?
Thanks
We started to have blocking issues (seen by sp_whoisactive) in our primary database server. a SQLText "NULL" process executed on master was blocking SQL dependency processes and some Stored Procedures too. It took more than an hour for that blocking process to go away. However, the SQL dumps were still occurring.
Time matched the first SQL dump file.
In this folder C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\Log we started to see a lot of SQLDump files (each of them = 400MB aprox, every 2 minutes). Of course, they filled our drive.They show this error:
***Stack Dump being sent to C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0039.txt
SqlDumpExceptionHandler: Process 129 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 07/05/17 17:08:44 spid 129
*
*
* Exception Address = 00007FFE5DCF7A8E Module(sqlmin+0000000000C27A8E)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 0000000000000010
* Input Buffer 109 bytes -Regards
Update both the OS and the SQL Server instances to the latest patch level, run diagnostics on the hardware and configure the max memory for the SQL Server instances to leave breathing space for the OS. Are there any other services running on there?
😎
Further, check if anti-virus excludes the data and log directories, turn off windows file indexing for those directories etc. Nothing but SQL Server should be accessing those.a monitoring tool, an enterprise anti-virus, a backup software, and SQL Server are running
I've seen SQL Server use up to 61 out of the 64 GB
Should I reserve 60GB for SQL then, or less?anti-virus has always excluded MDF and LDF files
I think we don't have the windows file indexing service as it is not listed in services.msc
thanks!
Double check that the anti-virus excludes the mdfs and the ldfs and set the permissions to both the directories and the files properly, nothing but SQL Server needs to access those files. In fact, those files cannot be infected as any file level alteration to the files will corrupt them.
Having said that, do you do regular integrity checks on the databases?
Set the max memory so it leaves at least 4Gb for the OS but mind you, if you RDP into that box, then you'll have to allocate more memory to the OS. My recommendation is to NEVER RDP on to production boxes, there is no need once the server has been set up.
You will still have to monitor the SQL and the OS for any memory pressure, you might want to consider adding memory to the box since that is not too expensive these days.
What backup software are you using? Some are good and some are not so good, I've seen outages caused by some backup software, especially the one that use Java clients.
Recommend using Ola Hallengren's Maintenance Scripts and then do a file level copy of the backups for the "off prem"
😎
July 6, 2017 at 11:53 am
Eirikur Eiriksson - Thursday, July 6, 2017 10:42 AMrogelio.vidaurri - Thursday, July 6, 2017 10:22 AMEirikur Eiriksson - Thursday, July 6, 2017 9:38 AMrogelio.vidaurri - Thursday, July 6, 2017 9:22 AMNorthernSoul - Thursday, July 6, 2017 8:41 AMNorthernSoul - Thursday, July 6, 2017 8:38 AMrogelio.vidaurri - Thursday, July 6, 2017 8:33 AMso, do you suggest us to limit memory, and redirect traffic back to the primary database instance (currently we are running on the secondary instance) and see if it doesn't happen again?
instead of installing SP3regards
You need to limit memory on both and you should also install SP3 on both.
Thanks
Do you have anymore details on the error you are getting besides "Access violation when reading address"?
Thanks
We started to have blocking issues (seen by sp_whoisactive) in our primary database server. a SQLText "NULL" process executed on master was blocking SQL dependency processes and some Stored Procedures too. It took more than an hour for that blocking process to go away. However, the SQL dumps were still occurring.
Time matched the first SQL dump file.
In this folder C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\Log we started to see a lot of SQLDump files (each of them = 400MB aprox, every 2 minutes). Of course, they filled our drive.They show this error:
***Stack Dump being sent to C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0039.txt
SqlDumpExceptionHandler: Process 129 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 07/05/17 17:08:44 spid 129
*
*
* Exception Address = 00007FFE5DCF7A8E Module(sqlmin+0000000000C27A8E)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 0000000000000010
* Input Buffer 109 bytes -Regards
Update both the OS and the SQL Server instances to the latest patch level, run diagnostics on the hardware and configure the max memory for the SQL Server instances to leave breathing space for the OS. Are there any other services running on there?
😎
Further, check if anti-virus excludes the data and log directories, turn off windows file indexing for those directories etc. Nothing but SQL Server should be accessing those.a monitoring tool, an enterprise anti-virus, a backup software, and SQL Server are running
I've seen SQL Server use up to 61 out of the 64 GB
Should I reserve 60GB for SQL then, or less?anti-virus has always excluded MDF and LDF files
I think we don't have the windows file indexing service as it is not listed in services.msc
thanks!
Double check that the anti-virus excludes the mdfs and the ldfs and set the permissions to both the directories and the files properly, nothing but SQL Server needs to access those files. In fact, those files cannot be infected as any file level alteration to the files will corrupt them.
Having said that, do you do regular integrity checks on the databases?
Set the max memory so it leaves at least 4Gb for the OS but mind you, if you RDP into that box, then you'll have to allocate more memory to the OS. My recommendation is to NEVER RDP on to production boxes, there is no need once the server has been set up.
You will still have to monitor the SQL and the OS for any memory pressure, you might want to consider adding memory to the box since that is not too expensive these days.
What backup software are you using? Some are good and some are not so good, I've seen outages caused by some backup software, especially the one that use Java clients.
Recommend using Ola Hallengren's Maintenance Scripts and then do a file level copy of the backups for the "off prem"
😎
"Double check that the anti-virus excludes the mdfs and the ldfs"
I did
"Having said that, do you do regular integrity checks on the databases?"
Never. We run a 24/7 system. Do they impact performance? We all hate blocking processes :crazy:
"if you RDP into that box, then you'll have to allocate more memory to the OS"
we do, to check backup files are being made through a provider's console mostly
"You will still have to monitor the SQL and the OS for any memory pressure, you might want to consider adding memory to the box since that is not too expensive these days."
what happens if SQL reaches the memory limit?
"What backup software are you using?"
Tivoli
thanks for your suggestions!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply