October 10, 2018 at 10:25 am
I checked database is IN Recovery.
Error log
Long Sync IO: Scheduler 11 had 1 Sync IOs in nonpreemptive mode longer than 1000 ms
Database recovery progress 42 % - after that the above messages recorded in error log
Server - All drive have free space also LDF file is not too large 2 GB only..
Please suggest, how over come this issues
October 10, 2018 at 11:36 am
SQL Galaxy - Wednesday, October 10, 2018 10:25 AMI checked database is IN Recovery.
Error log
Long Sync IO: Scheduler 11 had 1 Sync IOs in nonpreemptive mode longer than 1000 ms
Database recovery progress 42 % - after that the above messages recorded in error logServer - All drive have free space also LDF file is not too large 2 GB only..
Please suggest, how over come this issues
The error indicates an IO problem. This article explains the error and has a couple of links towards the end - check the first one about stalled IO issues:
How It Works: Sync IOs in nonpreemptive mode longer than 1000 ms
Sue
October 10, 2018 at 3:00 pm
Definitely check IO, as Sue suggested. The system is having trouble reading drives. I'd also run DBCC CHECKDB ASAP on this database. There might be other issues. If you have restored this recently elsewhere, run it there. Verify your backups are good here.
October 10, 2018 at 10:59 pm
thanks Steve and Sue for your reply..
Database is online after recovery process completed. database size MDF size 25GB and LDF size 2GB SIMPLE Recovery mode options..
Transaction log is not huge size even database mode changed to IN Recovery state after rebooting server.
I found some IO related messages in SQL Error log
last target outstanding: 5120, avgWriteLatency37
average writes per second: 180.87 writes/sec
averagethroughput: 1.52 MB/sec, I/Osaturation: 19311, context switches 34972
FlushCache: cleaned up 28785 bufs with 26642writes in 147297 ms (avoided 255 new dirty bufs) for db 12:0
How to fix those errors in SQL 2016 version and server model VMware virtual box, all Data files, tempdb files, TLOG files are placed in same drive only.
As you suggested, executed DBCC CHECKDB Command, results as below
CHECKDBfound 0 allocation errors and 0 consistency errors in database 'COREHMS'.
DBCCexecution completed. If DBCC printed error messages, contact your systemadministrator.
I think again database to changed In-Recovery state if server restarted again. yet to be update latest SQL 2016 service pack, Now RTM.
Thanks
October 11, 2018 at 2:29 pm
SQL Galaxy - Wednesday, October 10, 2018 10:59 PMthanks Steve and Sue for your reply..
Database is online after recovery process completed. database size MDF size 25GB and LDF size 2GB SIMPLE Recovery mode options..
Transaction log is not huge size even database mode changed to IN Recovery state after rebooting server.
I found some IO related messages in SQL Error log
last target outstanding: 5120, avgWriteLatency37
average writes per second: 180.87 writes/sec
averagethroughput: 1.52 MB/sec, I/Osaturation: 19311, context switches 34972
FlushCache: cleaned up 28785 bufs with 26642writes in 147297 ms (avoided 255 new dirty bufs) for db 12:0
How to fix those errors in SQL 2016 version and server model VMware virtual box, all Data files, tempdb files, TLOG files are placed in same drive only.
As you suggested, executed DBCC CHECKDB Command, results as below
CHECKDBfound 0 allocation errors and 0 consistency errors in database 'COREHMS'.
DBCCexecution completed. If DBCC printed error messages, contact your systemadministrator.
I think again database to changed In-Recovery state if server restarted again. yet to be update latest SQL 2016 service pack, Now RTM.
Thanks
You might still want to look at the disks even if you apply the latest service pack. Here are some suggested DMVs and perf mon counters to watch:
Troubleshooting Slow Disk I/O in SQL Server
This article is old but has some good examples which still apply when the hardware, drivers can be the source of the issue - the first couple examples:
Detecting and Resolving Stalled and Stuck I/O Issues
And don't forget to check the Windows event logs for any I/O related messages.
Sue
October 12, 2018 at 9:20 am
***The first step is always the hardest *******
October 25, 2018 at 1:51 am
yeah.. I agreed your points for what are the possible case database state to change the IN-Recovery mode
1. As per application workload TLOG file LDF huge size increase upto 80GB, and configured TLOG backup every 30 min as per business RPO/RTO., currently 50MB data growth set that auto grow size. does it reasonable setting for LDF file growth? Is there best practices method for how to set correct auto growth size setting?
2. Frequently buffer cache/ DISK IO related messages logged in error log file. what could be solution to resolve this issues. server is VMware virtual box, virtual disk and virtual memory. Max memory set to the 70 % out of total 64GB
last target outstanding: 5302, avgWriteLatency 55
FlushCache: cleaned up 9333 bufs with 5602 writes in 98523 ms (avoided 1347 new dirty bufs) for db 9:0
average writes per second: 95.69 writes/sec
average throughput: 1.19 MB/sec, I/O saturation: 6004, context switches 12698
average writes per second: 62.82 writes/sec
average throughput: 0.78 MB/sec, I/O saturation: 3751, context switches 5577
3. Ran DBCC CHECK DB command and not found any consistency errors.. CHECKDBfound 0 allocation errors and 0 consistency errors in database 'COREHMS'
October 25, 2018 at 2:38 pm
SQL Galaxy - Thursday, October 25, 2018 1:51 AMyeah.. I agreed your points for what are the possible case database state to change the IN-Recovery mode
1. As per application workload TLOG file LDF huge size increase upto 80GB, and configured TLOG backup every 30 min as per business RPO/RTO., currently 50MB data growth set that auto grow size. does it reasonable setting for LDF file growth? Is there best practices method for how to set correct auto growth size setting?
2. Frequently buffer cache/ DISK IO related messages logged in error log file. what could be solution to resolve this issues. server is VMware virtual box, virtual disk and virtual memory. Max memory set to the 70 % out of total 64GB
last target outstanding: 5302, avgWriteLatency 55
FlushCache: cleaned up 9333 bufs with 5602 writes in 98523 ms (avoided 1347 new dirty bufs) for db 9:0average writes per second: 95.69 writes/sec
average throughput: 1.19 MB/sec, I/O saturation: 6004, context switches 12698average writes per second: 62.82 writes/sec
average throughput: 0.78 MB/sec, I/O saturation: 3751, context switches 55773. Ran DBCC CHECK DB command and not found any consistency errors.. CHECKDBfound 0 allocation errors and 0 consistency errors in database 'COREHMS'
It's just another indicator of IO issues...usually. Those are typically logged when the checkpoint has exceeded the recovery interval that you configured (sp_configure).
You really would want to check the IO subsystem with the messages you have been getting.
Sue
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply