Sql operating system error 1117

Operating system error 1117 (I/O device error)

August 4, 2020 at 11:20 am

I have 3 Hyper-V servers in a cluster with a SQL2019 VM on one of them.

Recently we got an error in the logs that we cannot find the root cause of.

Could any of you help me in the right direction?

07/29/2020 20:52:57,spid48s,Unknown,The attempt to flush file buffers failed during file close activity.: Operating system error (null) encountered.

07/29/2020 20:52:57,spid48s,Unknown,Error: 17053 Severity: 16 State: 1.

07/29/2020 20:52:57,Logon,Unknown,Login failed for user ‘PAS’. Reason: Failed to open the explicitly specified database ‘ASGLOBALData’. [CLIENT: 192.168.69.10]

07/29/2020 20:52:57,Logon,Unknown,Error: 18456 Severity: 14 State: 38.

07/29/2020 20:52:57,spid108,Unknown,Database ASGLOBALData was shutdown due to error 9001 in routine ‘XdesRMFull::CommitInternal’. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.

07/29/2020 20:52:57,spid108,Unknown,The log for database ‘ASGLOBALData’ is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.

07/29/2020 20:52:57,spid108,Unknown,Error: 9001 Severity: 21 State: 4.

07/29/2020 20:52:57,spid8s,Unknown,Write error during log flush.

07/29/2020 20:52:57,spid8s,Unknown,SQLServerLogMgr::LogWriter: Operating system error 1117(The request could not be performed because of an I/O device error.) encountered.

07/29/2020 20:52:57,spid8s,Unknown,Error: 17053 Severity: 16 State: 1.

07/29/2020 20:52:40 Event ID 140 NTFS —

The system failed to flush data to the transaction log. Corruption may occur in VolumeId: E:, DeviceName: DeviceHarddiskVolume6.

(The I/O device reported an I/O error.)

I cannot find any fault in the physical drives or the physical servers themselves.

I’m not fluent in SQL so bear with me 🙂

August 4, 2020 at 8:19 pm

First thing I’d do is chkdsk on the physical disk to rule out disk corruption.

But it sounds to me like some disk based error and likely not a database error. The error is basically saying that SQL Server tried to write to disk but got an I/O device error and could not write to disk. Since it can’t write to disk, SQL went into panic mode and took the database offline to prevent bad data from going in.

The value in the event log is basically saying that when it tried to write to disk, the device (the disk) reported an I/O error.

All signs that I see here are saying there is something wrong with the disk, not with SQL.

The exception to it being some form of disk corruption is that it could be due to some forced file locking. What I mean here is something like an antivirus grabbed the database file and started doing a scan on it and locked it to prevent data changes while scanning. If SQL can’t write to the log file or the database file due to ANYTHING locking the file, SQL will shut down the database to prevent data corruption or data loss.

TL;DR — make sure the disk is not corrupt or corrupting (chkdsk and if any bad sectors come up, replace the disk) and make sure nothing is locking the database files such as an antivirus or antimalware tool. These must be configured to exclude the database files.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum — you shouldn’t blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run «random code» you found online on any system you care about UNLESS you understand and can verify the code OR you don’t care if the code trashes your system.

August 5, 2020 at 11:51 am

Thank you for your input.

What you say makes sense. I’ll see if I can run chkdsk on a CSV-volume.

August 5, 2020 at 2:23 pm

Quick google — it depends on if it is ReFS or NTFS. If ReFS, it should be doing self-healing. I expect this is true until the disks start failing. On NTFS, chkdsk can be run no problem.

My opinion, on critical systems, these commands should be being run on a schedule similar to how you run checkdb if possible. If the disk is starting to fail, it is MUCH nicer to find out from chkdsk that it is starting to go bad and had been corrected than to find out as your database becomes corrupted. If you are on NTFS, chkdsk to check for errors is an online operation with Windows Server 2012 and newer, so running that during a maintenance window is not required, but still recommended as it will do a lot of disk I/O and thus may have performance impacts on your databases. I say «may» because if your systems have light database usage, you may not notice the performance hit, and if they are on SSD’s, the random read/write times are pretty low so you may not notice the performance hit.

Alternately,if your disks are providing SMART info (which I am not sure a CSV volume would be able to check; I pass those sorts of maintenance off to the server admin guys), that may provide you with some useful info about if a disk is failing and if so, which one.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum — you shouldn’t blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run «random code» you found online on any system you care about UNLESS you understand and can verify the code OR you don’t care if the code trashes your system.

Источник

Sql operating system error 1117

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

We had a CheckDB failed error this morning on 3 of our databases.

Of these 3 databases, one is an important database (the DB of our main Sharepoint site collection) with an MDF size of 800Gigs.

This is the message we got : The operating system returned error 1117(The request could not be performed because of an I/O device error.) to SQL Server during a read at offset 0x00000000660000 in file ‘filePathHere’ .

Our system admin guys say they dont have any problem with the disks.

Now I am actually backing up this database to have it restored on our test server. I will then run CheckDB against it to verify

For the databases that failed the CheckDB, I noticed they have an extra copie of the DB name NameOfDB_ServerName_FB20150922. if the CheckDB runs again, will it override this DB copy?

Has anyone come across this error before and how have you gone about fixing this issue?

Источник

Sql operating system error 1117

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

SQL Server
Version: Microsoft SQL
Server 2012 (SP1) — 11.0.3000.0 (X64) Oct 19 2012 13:38:57 Copyright (c)
Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2
(Build 9200: )

Windows
Version: Windows
Server 2012 Standard

SQL Server
Installation: 5 node
active/active multi instance Failover Cluster

Problem statement:
We recently migrated to SQL Server 2012 on 5 node failover cluster. Just yesterday
(April 03 ,2013) we received twice the subjected error. SQL Server instance do
not allow any access to production database when subjected error occurs. After some
while start allowing access after performing DBCC checkdb (without errors). In addition
to above SQL Server error, we are also observing MPIO errors and warnings in
windows event log since March 08, 2013

windows event log error and Warning

Warning: DeviceMPIODisk9
is currently in a degraded state. One or more paths have failed, though the
process is now complete.

Error: Afail-over on DeviceMPIODisk9 occurred.

Источник

Sql operating system error 1117

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

Hi, m y operating System is Windows Server 2003 Standart x64 and when im doing a database backup in my SQL SERVER 2008 Standart. this problem jumps:

Backup Failed for server «nameofserver».
System Data Sql Client sql Error Read on «D:sqldatadatamart.mdf» failed: 1117 (The request could not be performed . I/O device error)

I tried to do the backup into a usb Disk , a network drive, and another partition.

And t he backup always stops at 80% saying the same thing.

» DBCC CHECKDB ( ‘DATAMART-antiguo’ ) WITH NO_INFOMSGS , ALL_ERRORMSGS»

And it throw this:

Msg 8966, Level 16, State 2, Line 1

Unable to read and latch page (1:4557512) with latch type SH. 1117(The request could not be performed because of an I/O device error.) failed.

Msg 8966, Level 16, State 2, Line 1

Unable to read and latch page (1:4557516) with latch type SH. 1117(The request could not be performed because of an I/O device error.) failed.

Msg 8966, Level 16, State 2, Line 1

Unable to read and latch page (1:4557517) with latch type SH. 1117(The request could not be performed because of an I/O device error.) failed.

Msg 8966, Level 16, State 2, Line 1

Unable to read and latch page (1:4557518) with latch type SH. 1117(The request could not be performed because of an I/O device error.) failed.

CHECKDB found 0 allocation errors and 4 consistency errors not associated with any single object.

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 1067970981, index ID 4, partition ID 72057594240303104, alloc unit ID 72057594274578432 (type In-row data). Page (1:3700412) is missing a reference from previous page (1:4557512). Possible chain linkage problem.

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 1067970981, index ID 4, partition ID 72057594240303104, alloc unit ID 72057594274578432 (type In-row data). Page (1:3700457) is missing a reference from previous page (1:4557517). Possible chain linkage problem.

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 1067970981, index ID 4, partition ID 72057594240303104, alloc unit ID 72057594274578432 (type In-row data). Page (1:3700458) is missing a reference from previous page (1:4557518). Possible chain linkage problem.

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 1067970981, index ID 4, partition ID 72057594240303104, alloc unit ID 72057594274578432 (type In-row data). Page (1:3700503) is missing a reference from previous page (1:4557516). Possible chain linkage problem.

Msg 2533, Level 16, State 1, Line 1

Table error: page (1:4557512) allocated to object ID 1067970981, index ID 4, partition ID 72057594240303104, alloc unit ID 72057594274578432 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.

Msg 8976, Level 16, State 1, Line 1

Table error: Object ID 1067970981, index ID 4, partition ID 72057594240303104, alloc unit ID 72057594274578432 (type In-row data). Page (1:4557512) was not seen in the scan although its parent (1:1615250) and previous (1:1136313) refer to it. Check any previous errors.

Msg 2533, Level 16, State 1, Line 1

Table error: page (1:4557516) allocated to object ID 1067970981, index ID 4, partition ID 72057594240303104, alloc unit ID 72057594274578432 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.

Msg 8976, Level 16, State 1, Line 1

Table error: Object ID 1067970981, index ID 4, partition ID 72057594240303104, alloc unit ID 72057594274578432 (type In-row data). Page (1:4557516) was not seen in the scan although its parent (1:4033765) and previous (1:1136410) refer to it. Check any previous errors.

Msg 2533, Level 16, State 1, Line 1

Table error: page (1:4557517) allocated to object ID 1067970981, index ID 4, partition ID 72057594240303104, alloc unit ID 72057594274578432 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.

Msg 8976, Level 16, State 1, Line 1

Table error: Object ID 1067970981, index ID 4, partition ID 72057594240303104, alloc unit ID 72057594274578432 (type In-row data). Page (1:4557517) was not seen in the scan although its parent (1:4151563) and previous (1:1136364) refer to it. Check any previous errors.

Msg 2533, Level 16, State 1, Line 1

Table error: page (1:4557518) allocated to object ID 1067970981, index ID 4, partition ID 72057594240303104, alloc unit ID 72057594274578432 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.

Msg 8976, Level 16, State 1, Line 1

Table error: Object ID 1067970981, index ID 4, partition ID 72057594240303104, alloc unit ID 72057594274578432 (type In-row data). Page (1:4557518) was not seen in the scan although its parent (1:4151563) and previous (1:1136365) refer to it. Check any previous errors.

CHECKDB found 0 allocation errors and 12 consistency errors in table ‘LOGISTICA.FCosto_Historico_Articulo’ (object ID 1067970981).

CHECKDB found 0 allocation errors and 16 consistency errors in database ‘DATAMART-antiguo’.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DATAMART-antiguo).

In the same directory we have several others databases with no problem at all, but this one in particular is the biggest one (>40 Gigs)

I looked online for help and ended knowing that its a commom problem with virtual servers, but this is not the case.

Источник

Problem

Customer has restored copy of a DB2 database (from a backup) onto a new DB2 server. User has created a new database connection (to point to this new database copy).

Afterwards, user launches ‘Controller Configuration’, opens ‘Database Connections’ and selects a database. User clicks green ‘tick’ to test the connection. An error appears.

Symptom

Connection failed
SQL1117N A connection to or activation of database «CCR» cannot be made because of ROLL-FORWARD PENDING. SQLSTATE=57019

Cause

The database requires rolling forward.

Diagnosing The Problem

Using IBM Data Studio, when connecting to the database you will get a similar error:

Resolving The Problem

Roll forward the database.

Steps:

Ask your I.T. department’s database administrator (DBA) to perform the following:

1. Logon to the DB2 server (as an administrator)

2. Launch an appropriate tool

• For example: DB2 Command Window — Administrator

3. Run the following command:

db2 rollforward db ccr complete

NOTE: Replace ‘CCR’ with the name of your database.

Potential Errors

Depending on how your backup was taken, you may receive an error. Below are some examples:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

(1) SQL1273N An operation reading the logs on database «CCR03» cannot continue because of a missing log file «S0019524.LOG» on database partion «0» and log stream «0».

In this scenario, you must:

1. Copy a log file from the ‘old’ database location (where you created the backup), to the ‘new’ database location.

EXAMPLE: In one real-life example, it was necessary to copy the file «S0019524.LOG«:

• from the folder: E:archived_logsDB2CCR01NODE0000LOGSTREAM0000C0000000
• to the new folder: E:archived_logsDB2CCR03NODE0000LOGSTREAM0000C0000000

2. Perform the rollforward again (for example: db2 rollforward db ccr03 complete )

3. Test.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

(2) SQL4970N Rollforward recovery stopped on the database named «CCR» because the rollforward utility cannot reach the specified stop point (end-of-log or point-in-time) on the following database partitions: «0».

This is caused by missing logs, caused by taking an online (not offline) backup. To solve this:

1. Run a command similar to the following:

db2 restore db ccr logs from . logtarget d:

2. Run this command:

db2 get db cfg for ccr

3. Look for the entry similar to: Path to log files = D:DB2NODE0000SQL00002LOGSTREAM0000

4. Open the folder mentioned (for example ‘D:DB2NODE0000SQL00002LOGSTREAM0000’) and copy the relevant log file (for example ‘S0041211.log’)

5. Browse to the folder where the new database’s logs are stored (for example ‘D:DB2NODE0000SQL00002LOGSTREAM0000LOGSTREAM0000’)

• Paste the log file (for example ‘S0041211.log’) into that folder.

6. Run this command:

db2 rollforward db ccr to end of logs

7. Run this command:

db2 rollforward db ccr complete

8. Test.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

(3) SQL1276N Database «CCR» cannot be brought out of rollforward pending state until roll-forward has passed a point in time greater than or equal to «2017-10-31-12.09.26.000000 UTC», because node «0» contains information later than the specified time.

This is typically caused by the new (target) DB2 server having a different time zone from the original (source) DB2 server. To solve this, run a command similar to:

db2 ROLLFORWARD DATABASE CCR to 2017-10-31-12.09.26.000000

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~