DB snapshot goes into SUSPECT mode (SQL 2014 on WIN2K8R2)
I was hoping someone here might have encountered this sort of issue and if so what did you do to fix it?
Here's some details:
DB server: SQL 2014 Enterprise (12.0.4100.1)
OS: Windows 2008R2 Enterprise
Data drive: 800GB
Database to snapshot: 430GB
I can create multiple snapshots of this database and they function perfectly fine until the actual size on disk reaches 38.1GB. At this point there is still well over 150GB of free space on the drive. After that the SnapShot file encounters an IO error and the snapshot enters SUSPECT mode. Here's the error message in the SQL ERRORLOG:
The operating system returned:
error 665(The requested operation could not be completed due to a file
system limitation) to SQL Server during a write at offset
0x00002dc1520000 in file 'E:SQL Data 1devDB_seq1501_rba63692865.ss'.
Additional messages in the SQL Server error log and system event log
may provide more detail. This is a severe system-level error condition
that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error
can be caused by many factors; for more information, see SQL Server
Books Online.
E:SQL Data 1devDB_seq1501_rba63692865.ss: Operating system error
665(The requested operation could not be completed due to a file
system limitation) encountered.
Database snapshot 'devDB_seq1501_rba63692865' has failed an IO
operation and is marked suspect. It must be dropped and recreated.
This article: https://support.microsoft.com/en-us/kb/2002606 mentions a similar sort of issue (the 665 error) on a heavily fragmented file system, but I'm not really sure how to approach this.
Any insight into what might be happening here would be greatly appreciated.
sql-server snapshot
edited Jul 6 '17 at 12:35
SqlWorldWide
8,28521335
asked Dec 16 '15 at 14:59
C-FourC-Four
212
|
show 6 more comments
I was hoping someone here might have encountered this sort of issue and if so what did you do to fix it?
Here's some details:
DB server: SQL 2014 Enterprise (12.0.4100.1)
OS: Windows 2008R2 Enterprise
Data drive: 800GB
Database to snapshot: 430GB
I can create multiple snapshots of this database and they function perfectly fine until the actual size on disk reaches 38.1GB. At this point there is still well over 150GB of free space on the drive. After that the SnapShot file encounters an IO error and the snapshot enters SUSPECT mode. Here's the error message in the SQL ERRORLOG:
The operating system returned:
error 665(The requested operation could not be completed due to a file
system limitation) to SQL Server during a write at offset
0x00002dc1520000 in file 'E:SQL Data 1devDB_seq1501_rba63692865.ss'.
Additional messages in the SQL Server error log and system event log
may provide more detail. This is a severe system-level error condition
that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error
can be caused by many factors; for more information, see SQL Server
Books Online.
E:SQL Data 1devDB_seq1501_rba63692865.ss: Operating system error
665(The requested operation could not be completed due to a file
system limitation) encountered.
Database snapshot 'devDB_seq1501_rba63692865' has failed an IO
operation and is marked suspect. It must be dropped and recreated.
This article: https://support.microsoft.com/en-us/kb/2002606 mentions a similar sort of issue (the 665 error) on a heavily fragmented file system, but I'm not really sure how to approach this.
Any insight into what might be happening here would be greatly appreciated.
sql-server snapshot
edited Jul 6 '17 at 12:35
SqlWorldWide
8,28521335
asked Dec 16 '15 at 14:59
C-FourC-Four
212
just a guess (I remember running into this a loong time ago) but worth exploring. On the diskE:, click on properties -->shadow copies--> check the drive properties forE:and see if there is any limit imposed. You should see the limit might be imposed on the maximum size. If you change it to NO limit or just increase the limit and then take snapshot .. are you able to generate a snapshot ?
– Kin
Dec 16 '15 at 15:34
Thanks for suggesting this, Kin. I checked this drive and while shadow copy was not enabled it did have a limit. In your case was the issue related to shadow copy backups or SQL server Database snapshots?
– C-Four
Dec 16 '15 at 16:21
Can you change to NO limit and try to create a snapshot to see if it works or you get same error ?
– Kin
Dec 16 '15 at 16:22
I changed it to NO limit and recreated the database snapshot. I'll just need to wait until enough data has changed to see if I get past the 38ish GB actual size the .ss file gets to before hitting an IO error and going suspect.
– C-Four
Dec 16 '15 at 16:31
1
Did you run DBCC CHECKDB? Are there other entries in error log?
– Priyanka
Dec 28 '18 at 11:35
|
show 6 more comments
I was hoping someone here might have encountered this sort of issue and if so what did you do to fix it?
Here's some details:
DB server: SQL 2014 Enterprise (12.0.4100.1)
OS: Windows 2008R2 Enterprise
Data drive: 800GB
Database to snapshot: 430GB
I can create multiple snapshots of this database and they function perfectly fine until the actual size on disk reaches 38.1GB. At this point there is still well over 150GB of free space on the drive. After that the SnapShot file encounters an IO error and the snapshot enters SUSPECT mode. Here's the error message in the SQL ERRORLOG:
The operating system returned:
error 665(The requested operation could not be completed due to a file
system limitation) to SQL Server during a write at offset
0x00002dc1520000 in file 'E:SQL Data 1devDB_seq1501_rba63692865.ss'.
Additional messages in the SQL Server error log and system event log
may provide more detail. This is a severe system-level error condition
that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error
can be caused by many factors; for more information, see SQL Server
Books Online.
E:SQL Data 1devDB_seq1501_rba63692865.ss: Operating system error
665(The requested operation could not be completed due to a file
system limitation) encountered.
Database snapshot 'devDB_seq1501_rba63692865' has failed an IO
operation and is marked suspect. It must be dropped and recreated.
This article: https://support.microsoft.com/en-us/kb/2002606 mentions a similar sort of issue (the 665 error) on a heavily fragmented file system, but I'm not really sure how to approach this.
Any insight into what might be happening here would be greatly appreciated.
sql-server snapshot
edited Jul 6 '17 at 12:35
SqlWorldWide
8,28521335
asked Dec 16 '15 at 14:59
C-FourC-Four
212
I was hoping someone here might have encountered this sort of issue and if so what did you do to fix it?
Here's some details:
DB server: SQL 2014 Enterprise (12.0.4100.1)
OS: Windows 2008R2 Enterprise
Data drive: 800GB
Database to snapshot: 430GB
I can create multiple snapshots of this database and they function perfectly fine until the actual size on disk reaches 38.1GB. At this point there is still well over 150GB of free space on the drive. After that the SnapShot file encounters an IO error and the snapshot enters SUSPECT mode. Here's the error message in the SQL ERRORLOG:
The operating system returned:
error 665(The requested operation could not be completed due to a file
system limitation) to SQL Server during a write at offset
0x00002dc1520000 in file 'E:SQL Data 1devDB_seq1501_rba63692865.ss'.
Additional messages in the SQL Server error log and system event log
may provide more detail. This is a severe system-level error condition
that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error
can be caused by many factors; for more information, see SQL Server
Books Online.
E:SQL Data 1devDB_seq1501_rba63692865.ss: Operating system error
665(The requested operation could not be completed due to a file
system limitation) encountered.
Database snapshot 'devDB_seq1501_rba63692865' has failed an IO
operation and is marked suspect. It must be dropped and recreated.
This article: https://support.microsoft.com/en-us/kb/2002606 mentions a similar sort of issue (the 665 error) on a heavily fragmented file system, but I'm not really sure how to approach this.
Any insight into what might be happening here would be greatly appreciated.
sql-server snapshot
sql-server snapshot
edited Jul 6 '17 at 12:35
SqlWorldWide
8,28521335
asked Dec 16 '15 at 14:59
C-FourC-Four
212
edited Jul 6 '17 at 12:35
SqlWorldWide
8,28521335
asked Dec 16 '15 at 14:59
C-FourC-Four
212
edited Jul 6 '17 at 12:35
SqlWorldWide
8,28521335
edited Jul 6 '17 at 12:35
SqlWorldWide
8,28521335
edited Jul 6 '17 at 12:35
SqlWorldWide
8,28521335
8,28521335
asked Dec 16 '15 at 14:59
C-FourC-Four
212
asked Dec 16 '15 at 14:59
C-FourC-Four
212
asked Dec 16 '15 at 14:59
C-FourC-Four
212
212
just a guess (I remember running into this a loong time ago) but worth exploring. On the diskE:, click on properties -->shadow copies--> check the drive properties forE:and see if there is any limit imposed. You should see the limit might be imposed on the maximum size. If you change it to NO limit or just increase the limit and then take snapshot .. are you able to generate a snapshot ?
– Kin
Dec 16 '15 at 15:34
Thanks for suggesting this, Kin. I checked this drive and while shadow copy was not enabled it did have a limit. In your case was the issue related to shadow copy backups or SQL server Database snapshots?
– C-Four
Dec 16 '15 at 16:21
Can you change to NO limit and try to create a snapshot to see if it works or you get same error ?
– Kin
Dec 16 '15 at 16:22
I changed it to NO limit and recreated the database snapshot. I'll just need to wait until enough data has changed to see if I get past the 38ish GB actual size the .ss file gets to before hitting an IO error and going suspect.
– C-Four
Dec 16 '15 at 16:31
1
Did you run DBCC CHECKDB? Are there other entries in error log?
– Priyanka
Dec 28 '18 at 11:35
|
show 6 more comments
just a guess (I remember running into this a loong time ago) but worth exploring. On the diskE:, click on properties -->shadow copies--> check the drive properties forE:and see if there is any limit imposed. You should see the limit might be imposed on the maximum size. If you change it to NO limit or just increase the limit and then take snapshot .. are you able to generate a snapshot ?
– Kin
Dec 16 '15 at 15:34
Thanks for suggesting this, Kin. I checked this drive and while shadow copy was not enabled it did have a limit. In your case was the issue related to shadow copy backups or SQL server Database snapshots?
– C-Four
Dec 16 '15 at 16:21
Can you change to NO limit and try to create a snapshot to see if it works or you get same error ?
– Kin
Dec 16 '15 at 16:22
I changed it to NO limit and recreated the database snapshot. I'll just need to wait until enough data has changed to see if I get past the 38ish GB actual size the .ss file gets to before hitting an IO error and going suspect.
– C-Four
Dec 16 '15 at 16:31
1
Did you run DBCC CHECKDB? Are there other entries in error log?
– Priyanka
Dec 28 '18 at 11:35
just a guess (I remember running into this a loong time ago) but worth exploring. On the disk
E:, click on properties --> shadow copies --> check the drive properties for E: and see if there is any limit imposed. You should see the limit might be imposed on the maximum size. If you change it to NO limit or just increase the limit and then take snapshot .. are you able to generate a snapshot ?– Kin
Dec 16 '15 at 15:34
just a guess (I remember running into this a loong time ago) but worth exploring. On the disk
E:, click on properties --> shadow copies --> check the drive properties for E: and see if there is any limit imposed. You should see the limit might be imposed on the maximum size. If you change it to NO limit or just increase the limit and then take snapshot .. are you able to generate a snapshot ?– Kin
Dec 16 '15 at 15:34
Thanks for suggesting this, Kin. I checked this drive and while shadow copy was not enabled it did have a limit. In your case was the issue related to shadow copy backups or SQL server Database snapshots?
– C-Four
Dec 16 '15 at 16:21
Thanks for suggesting this, Kin. I checked this drive and while shadow copy was not enabled it did have a limit. In your case was the issue related to shadow copy backups or SQL server Database snapshots?
– C-Four
Dec 16 '15 at 16:21
Can you change to NO limit and try to create a snapshot to see if it works or you get same error ?
– Kin
Dec 16 '15 at 16:22
Can you change to NO limit and try to create a snapshot to see if it works or you get same error ?
– Kin
Dec 16 '15 at 16:22
I changed it to NO limit and recreated the database snapshot. I'll just need to wait until enough data has changed to see if I get past the 38ish GB actual size the .ss file gets to before hitting an IO error and going suspect.
– C-Four
Dec 16 '15 at 16:31
I changed it to NO limit and recreated the database snapshot. I'll just need to wait until enough data has changed to see if I get past the 38ish GB actual size the .ss file gets to before hitting an IO error and going suspect.
– C-Four
Dec 16 '15 at 16:31
1
1
Did you run DBCC CHECKDB? Are there other entries in error log?
– Priyanka
Dec 28 '18 at 11:35
Did you run DBCC CHECKDB? Are there other entries in error log?
– Priyanka
Dec 28 '18 at 11:35
|
show 6 more comments
1 Answer
1
active
oldest
votes
Did the drive the snapshot is on or the drive that the source DB is on run out of space?
There's no way to repair a corrupt snapshot. The only thing you can do with it is drop it.
answered 16 mins ago
PriyankaPriyanka
698
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f123833%2fdb-snapshot-goes-into-suspect-mode-sql-2014-on-win2k8r2%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Did the drive the snapshot is on or the drive that the source DB is on run out of space?
There's no way to repair a corrupt snapshot. The only thing you can do with it is drop it.
answered 16 mins ago
PriyankaPriyanka
698
add a comment |
Did the drive the snapshot is on or the drive that the source DB is on run out of space?
There's no way to repair a corrupt snapshot. The only thing you can do with it is drop it.
answered 16 mins ago
PriyankaPriyanka
698
add a comment |
Did the drive the snapshot is on or the drive that the source DB is on run out of space?
There's no way to repair a corrupt snapshot. The only thing you can do with it is drop it.
answered 16 mins ago
PriyankaPriyanka
698
Did the drive the snapshot is on or the drive that the source DB is on run out of space?
There's no way to repair a corrupt snapshot. The only thing you can do with it is drop it.
answered 16 mins ago
PriyankaPriyanka
698
answered 16 mins ago
PriyankaPriyanka
698
answered 16 mins ago
PriyankaPriyanka
698
answered 16 mins ago
PriyankaPriyanka
698
698
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f123833%2fdb-snapshot-goes-into-suspect-mode-sql-2014-on-win2k8r2%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
just a guess (I remember running into this a loong time ago) but worth exploring. On the disk
E:, click on properties -->shadow copies--> check the drive properties forE:and see if there is any limit imposed. You should see the limit might be imposed on the maximum size. If you change it to NO limit or just increase the limit and then take snapshot .. are you able to generate a snapshot ?– Kin
Dec 16 '15 at 15:34
Thanks for suggesting this, Kin. I checked this drive and while shadow copy was not enabled it did have a limit. In your case was the issue related to shadow copy backups or SQL server Database snapshots?
– C-Four
Dec 16 '15 at 16:21
Can you change to NO limit and try to create a snapshot to see if it works or you get same error ?
– Kin
Dec 16 '15 at 16:22
I changed it to NO limit and recreated the database snapshot. I'll just need to wait until enough data has changed to see if I get past the 38ish GB actual size the .ss file gets to before hitting an IO error and going suspect.
– C-Four
Dec 16 '15 at 16:31
1
Did you run DBCC CHECKDB? Are there other entries in error log?
– Priyanka
Dec 28 '18 at 11:35