March 21, 2018 at 5:07 pm
we have a BIG database (over 2T)
was failing on a drive which had a data (tables) filegroup on it, moved all objects to new filegroup, now it's failing on another drive which has another filegroup on it:
here are some of the error from errorlog:
Read on "J:\Index\ExchangeItem_1_Index.ndf" failed: 23(Data error (cyclic redundancy check).)
Error: 3041, Severity: 16, State: 1.
BACKUP failed to complete the command BACKUP DATABASE ExchangeItem_1. Check the backup application log for detailed messages.
Error: 18210, Severity: 16, State: 1.
BackupIoRequest::ReportIoError: write failure on backup device '34a8de07-73fe-4d1c-b60f-c409b95698e1'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).
Error: 18210, Severity: 16, State: 1.
BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device '34a8de07-73fe-4d1c-b60f-c409b95698e1'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).
that ndf file belongs to ExchangeItem_INDEX filegroup which has objects like following that I don't know how to move them to new file group and drop existing filegroup.
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' and f.name ='ExchangeItem_INDEX'
MetaDataToDisposeDocumentsFromIndexer U IX_MetaDataToDisposeDocumentsFromIndexer_AssetID 2
MessageFolder U Index_Mf_MailboxIDFolderFID 2
MessageFolder U Index_Mf_FolderName 3
MessageFolder U Index_Mf_FolderType 4
MessageFolder U Index_Mf_MessageFolderGuid 5
MessageFolder U Index_Mf_ParentFolderidDeletedDate_MailboxID_IsPSTFolder 6
MessageFolder U Index_Mf_MailboxID_FolderFlags_ParentFolderID_CreatedDate 7
MetaDataToDisposeAttachmentsFromIndexer U IX_MetaDataToDisposeAttachmentsFromIndexer_MessageItemID 2
MailboxSendersCache U MailboxSendersCache_AssetId_NPGalId_SenderType 2
MailboxRecipientsCache U MailboxRecipientsCache_AssetId_NPGalId 2
AssetRetentionLocks U ARL_Ids 2
MessageItem U Index_mi_DeltaHash 2
MessageItem U Index_Mi_ConversationDBId 3
MessageItem U Index_Mi_DehydrateSessionIdDeletedDate 4
and more ....
March 22, 2018 at 7:47 am
You may want to check for any corruption first. Try executiong:
DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS , ALL_ERRORMSGS
Sue
March 22, 2018 at 9:21 am
Sue_H - Thursday, March 22, 2018 7:47 AMYou may want to check for any corruption first. Try executiong:
DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS , ALL_ERRORMSGSSue
DBCC runs successfully with no error
March 22, 2018 at 9:30 am
Sounds to me like you are having hardware issues. Hopefully you are getting your sys admins involved in this as well.
March 22, 2018 at 10:21 am
mitra.zaimi - Thursday, March 22, 2018 9:21 AMSue_H - Thursday, March 22, 2018 7:47 AMYou may want to check for any corruption first. Try executiong:
DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS , ALL_ERRORMSGSSue
DBCC runs successfully with no error
Check the windows event logs on the server itself to see if there are any IO subsystem related errors. You would want to contact whoever manages the storage and windows servers.
Sue
March 22, 2018 at 10:30 am
Sue_H - Thursday, March 22, 2018 10:21 AMmitra.zaimi - Thursday, March 22, 2018 9:21 AMSue_H - Thursday, March 22, 2018 7:47 AMYou may want to check for any corruption first. Try executiong:
DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS , ALL_ERRORMSGSSue
DBCC runs successfully with no error
Check the windows event logs on the server itself to see if there are any IO subsystem related errors. You would want to contact whoever manages the storage and windows servers.
Sue
Suppose I only needed to move this file group to new filegroup, what kind of data the following is and how did I needed to deal with them?
SELECT * FROM sys.filegroups --> 6
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes I
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id
AND i.data_space_id = 6
| Name | Type | Name | index_id | name |
| MetaDataToDisposeDocumentsFromIndexer | U |
March 22, 2018 at 10:30 am
Have you tried backing up to a different location/drive/file?
March 22, 2018 at 10:32 am
Steve Jones - SSC Editor - Thursday, March 22, 2018 10:30 AMHave you tried backing up to a different location/drive/file?
we have Commvault backup and we don't do through sql jobs.
March 22, 2018 at 10:33 am
mitra.zaimi - Thursday, March 22, 2018 10:30 AMSue_H - Thursday, March 22, 2018 10:21 AMmitra.zaimi - Thursday, March 22, 2018 9:21 AMSue_H - Thursday, March 22, 2018 7:47 AMYou may want to check for any corruption first. Try executiong:
DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS , ALL_ERRORMSGSSue
DBCC runs successfully with no error
Check the windows event logs on the server itself to see if there are any IO subsystem related errors. You would want to contact whoever manages the storage and windows servers.
Sue
Suppose I only needed to move this file group to new filegroup, what kind of data the following is and how did I needed to deal with them?
SELECT * FROM sys.filegroups --> 6
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes I
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id
AND i.data_space_id = 6
Name Type Name index_id name MetaDataToDisposeDocumentsFromIndexer U IX_MetaDataToDisposeDocumentsFromIndexer_AssetID 2 MimosaExchangeItem_INDEX MessageFolder U Index_Mf_MailboxIDFolderFID 2 MimosaExchangeItem_INDEX MetaDataToDisposeAttachmentsFromIndexer U IX_MetaDataToDisposeAttachmentsFromIndexer_MessageItemID 2 MimosaExchangeItem_INDEX MailboxSendersCache U MailboxSendersCache_AssetId_NPGalId_SenderType 2 MimosaExchangeItem_INDEX MailboxRecipientsCache U MailboxRecipientsCache_AssetId_NPGalId 2 MimosaExchangeItem_INDEX AssetRetentionLocks U ARL_Ids 2 MimosaExchangeItem_INDEX MessageItem U Index_mi_DeltaHash 2 MimosaExchangeItem_INDEX MessageItem U Index_Mi_ConversationDBId 3 MimosaExchangeItem_INDEX MessageDumpster U ix_nonunique_MessageDumpster_MessageItemID 2 MimosaExchangeItem_INDEX MessageItemRecipientMap U IX_MsgRecipMap_GalCnvTypeOrder 6 MimosaExchangeItem_INDEX AssetPlacementWorkItem U INDEX_AssetPlacementWorkItem_AssetId_ItemDbId_Type 2 MimosaExchangeItem_INDEX AssetPlacementWorkItem U Index_AssetPlacementWorkItem_ExpirationDate 3 MimosaExchangeItem_INDEX AssetPlacementWorkItem U Index_AssetPlacementWorkItem_AssetId 4 MimosaExchangeItem_INDEX AssetPlacementWorkItem U Index_AssetPlacementWorkItem_Type 5 MimosaExchangeItem_INDEX ConversationIndex U CIT_ConversationIndex 2 MimosaExchangeItem_INDEX ConversationThread U CTT_ConversationThread 2 MimosaExchangeItem_INDEX FileExtensions U ix_FileExtensions 2 MimosaExchangeItem_INDEX FlattenedRetentionPolicy U Index_FlattenedRetentionPolicy_assetID_folderID_msgClsID_actiontype_period_GPP_DPGPP_APStubs 2 MimosaExchangeItem_INDEX ItemRetentionLocks U IRL_Ids 2 MimosaExchangeItem_INDEX
how do I find these objects and change their file group?
March 22, 2018 at 1:38 pm
The type of object is in the column o.type. You would move objects the same way as you did when you moved everything from one filegroup to a new one. Since you did it before, then as you know there is no one way as it depends on if it's clustered, nonclustered, heap, etc. These articles discuss the same thing you already did:
How to move data between File Groups in SQL Server
Move data between SQL Server database filegroups
moving large number of tables to different filegroups
Sue
March 22, 2018 at 2:28 pm
Sue_H - Thursday, March 22, 2018 1:38 PMThe type of object is in the column o.type. You would move objects the same way as you did when you moved everything from one filegroup to a new one. Since you did it before, then as you know there is no one way as it depends on if it's clustered, nonclustered, heap, etc. These articles discuss the same thing you already did:
How to move data between File Groups in SQL Server
Move data between SQL Server database filegroups
moving large number of tables to different filegroupsSue
thank you,
before, I had tables that I moved their primary key or clustered index and for the ones didn't have clustered index, created clustered and then moved them.
for this one, I even don't know what are them (meta data? ) or where and how find them on management studio to go to their "storage" and change the file group.
any advise?
March 22, 2018 at 3:51 pm
mitra.zaimi - Thursday, March 22, 2018 2:28 PMthank you,
before, I had tables that I moved their primary key or clustered index and for the ones didn't have clustered index, created clustered and then moved them.
for this one, I even don't know what are them (meta data? ) or where and how find them on management studio to go to their "storage" and change the file group.
any advise?
You'd do the same thing with the indexes...that's what those articles walk through. I thought one of them talked about using management studio for some of this. If you select a table or an index and right click on it, select properties and then on the left select storage, that will have the filegroups. I've never moved things around using SSMS though so I can't help you there. If one of those articles is doing that, it should walk you through. You already know most of what you need to know from when you did it before.
Those things listed were the tables and the table indexes that are in that filegroup. I don't know the specifics of what they are but I did a search on one of the names - MimosaExchangeItem_INDEX. My guess would be it's the database for Mimosa NearPoint email archiving software for Exchange. That would make sense with some of the other names of objects you had in your posts.
You already connected to the database for it when you ran the queries you posted. So the tables, indexes would be in that database.
Sue
March 22, 2018 at 4:04 pm
Just to take a step back, it still sounds like there is a hardware issue that needs to be addressed now. With that, are you using Direct Attach Storage or a SAN/NAS for the database files? I am asking as just moving data to other files/filegroups on other disks may not be the answer depending on the server and/or storage configuration.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply