Параметр modify file вызвал системную ошибку 665

  • Remove From My Forums

  • Question

  • I have read all other cases that relate to this error and cannot get this to work. Running SQL Server 2012 sp1 on Windows server 2012 R2. Disk space and permissions are fine, but I get the error below when I try and use the check database integrity task
    within my maintenance plan on both system and user databases. I have researched this and fragmentation is not the issue. I’m lost at this point and would appreciate at least some steps to try. databases are not «read only» as I have read this may
    contribute to the problem. All other maintenance tasks run fine.

    Error message from SQL LOG

    Check Database integrity on Local server connection
    Databases: All system databases
    Task start: 2014-01-13T11:00:04.
    Task end: 2014-01-13T11:00:04.
    Failed:(-1073548784) Executing the query «DBCC CHECKDB(N’master’, NOINDEX)
    » failed with the following error: «A database snapshot cannot be created because it failed to start.
    A database snapshot cannot be created because it failed to start.
    MODIFY FILE encountered operating system error 665(The requested operation could not be completed due to a file system limitation) while attempting to expand the physical file ‘E:\\SQLdata\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\master.mdf:MSSQL_DBCC9’.
    The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
    The database could not be exclusively locked to perform the operation.
    Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous
    errors for more details.». Possible failure reasons: Problems with the query, «ResultSet» property not set correctly, parameters not set correctly, or connection not established correctly.

    Error Message from Log File Viewer in SSMS:

    Source: Check Database Integrity Task      Executing query «USE [ReportServer]  «.: 50% complete  End Progress  Error: 2014-01-13 11:31:54.92     Code: 0xC002F210    
    Source: Check Database Integrity Task Execute SQL Task     Description: Executing the query «DBCC CHECKDB(N’ReportServer’)  WITH NO_INFOMSGS  » failed with the following error: «A database snapshot cannot be created
    because it failed to start.  A database snapshot cannot be created because it failed to start.  MODIFY FILE encountered operating system error 665(The requested operation could not be completed due to a file system limitation) while attempting to
    expand the physical file ‘E:\SQLdata\MSSQL11.MSSQLSERVER\MSSQL\DATA\ReportServer.mdf:MSSQL_DBCC9′.  The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not
    support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.  The database could not be exclusively locked to perform the operation.  Check statement aborted. The database could not be checked as a database
    snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.». Possible failure reasons: Problems with
    the query, «ResultSet» property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  Progress: 2014-01-13 11:31:54.93     Source: Check Database Integrity Task     
    Executing query «USE [ReportServerTempDB]  «.: 50% complete  End Progress  Error: 2014-01-13 11:31:55.02     Code: 0xC002F210     Source: Check Database Integrity Task Execute SQL Task    
    Description: Executing the query «DBCC CHECKDB(N’ReportServerTempDB’)  WITH NO_INFOM…» failed with the following error: «A database snapshot cannot be created because it failed to start.  A database snapshot cannot be created because
    it failed to start.  MODIFY FILE encountered operating system error 665(The requested operation could not be completed due to a file system limitation) while attempting to expand the physical file ‘E:\SQLdata\MSSQL11.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB.mdf:MSSQL_DBCC9′. 
    The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.».
    Possible failure reasons: Problems with the query, «ResultSet» property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  Progress: 2014-01-13 11:31:55.02     Source:
    Check Database Integrity Task      Executing query «USE [AddressUpload]  «.: 50% complete  End Progress  Error: 2014-01-13 11:31:55.13     Code: 0xC002F210     Source:
    Check Database Integrity Task Execute SQL Task     Description: Executing the query «DBCC CHECKDB(N’AddressUpload’)  WITH NO_INFOMSGS  » failed with the following error: «A database snapshot cannot be created because
    it failed to start.  A database snapshot cannot be created because it failed to start.  MODIFY FILE encountered operating system error 665(The requested operation could not be completed due to a file system limitation) while attempting to expand
    the physical file ‘E:\SQLData\MSSQL11.MSSQLSERVER\MSSQL\DATA\database1.mdf:MSSQL_DBCC9’.  The database snapshot for online checks could not be created. Either th…  The package execution fa…  The step failed.

Answers

  • ReFS is NOT supported in use with SQL Server 2012. Once such item, which you’ve stumbled upon is the fact that alternate streams and sparse files are not implemented in ReFS and thus these issues are caused. You *could* force the checkdb to execute by using
    WITH TABLOCKX but that’ll require exclusive access to the database for the duration of the checkdb scan and that’s not something I would advise to do.

    Sean Gallardy | Blog |
    Twitter

    • Proposed as answer by

      Tuesday, January 14, 2014 5:55 AM

    • Marked as answer by
      Fanny Liu
      Thursday, January 23, 2014 11:49 AM

Источник

Ошибка 665 (Error 665)Друзья, доброго времени суток.

Недавно столкнулся с новой ошибкой в логе SQL Server. Вот её содержание:

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 0x000053cfa2e000 in file
‘G:\Databases\TestDB.mdf:MSSQL_DBCC15’. 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.

После ряда изысканий я пришёл к следующим заключениям.

Возможные причины:

1. Нехватка дискового пространства

2. Сильно фрагментированные файлы в томе NTFS не можут выйти за пределы определенного размера

3. В момент больших манипуляций с БД запущены команды DBCC

4. Создание моментального снимка базы данных в большой базе данных. После этого выполняется множество операций изменения данных или операций обслуживания в исходной базе данных.

5. Создать моментальный снимок базы данных на зеркальной базе данных.

Способы решения проблемы:

1. Установите последние hotfix на Windows и SQL Server

2. Поставьте правильное автоувеличение файла, чтобы оно не происходило слишком часто. Например, на часто изменяющихся данных поставить заведомо больший размер файла лога или файла БД или больший % автоувеличения

3. Увеличить размер сектора/кластера на диске. По-умолчанию, при форматировании диска, размер сектора 4 кб. При подобной ошибки, да и в большинстве случаев, лучше ставить 64 кб. Чтобы изменить данный размер на рабочем диске, необходимо его форматировать.

4. Не запускать команды DBCC в момент рабочей нагрузки на сервер. В крайнем случае вы можете использовать параметр WITH TABLOCK команды DBCC CHECKDB. Это позволит избежать использование внутреннего napshot

5.  Не запускайте DBCC CHECKDB одновременно с планами обслуживания (перестроение индексов, обновление статистики, сжатие БД)

6. Можно создать snapshot БД на другом диске и выполнить на нём DBCC CHECKDB. Не забудьте удалить его.

7. Изучите возможность использование дополнительных параметров DBCC CHECKDB, таких как PHYSICAL_ONLY, чтобы уменьшить время жизни внутренних snapshot, созданных DBCC командой.

8. Чаще пересоздавайте snapshot

9. Создавайте snapshot на отличном от данных диске

10. Удаляйте snapshot на момент планового ослуживания, после чего не забудьте создать его заного

11. Найдите наиболее нагруженную часть БД и перенесите её на другой диск

12. Убедитесь что отсутствует фрагментация файла на уровне NTFS, например используя Operating System Disk management tools. Избавиться от такой фрагментации можно путём копирования файла на другой диск, удаления и обратного копирования

13. Установите более низкий FILLFACTOR , чтобы уменьшить разрыв страниц

14. Уменьшите размер файла данных путём разделения на несколько

Источник

Have a SQL Server 2014 instance running on Windows 2012 R2 that was running Ola Hallengren’s DB integrity script. This is the error that resulted:

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 0x0001392f6ee000 in file
‘H:\MSSQL12\Data\EDW_Data_6.ndf_MSSQL_DBCC15’. 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.

I checked both the event log and the SQL Server error log but not getting a lot of additional info that could help. I haven’t rerun the DBCC check for this db yet. This database is pretty active as it is a data warehouse database. It’s currently 10.5 TB. I’ve heard that if I rerun the dbcc it may clear out the MSSQL_DBCC15 however I’m hesitant to do that on this server due to the level of activity. My preference would be to copy the backup to another server and run the dbcc there. If I do that, is there any way for me to clean up the DBCC15 files left on disk on the primary server?

Any other places I could find more info other than event log and SQL Server error log?

Appreciate any thoughts on what could cause this error or the best option for running DBCC checkdb based on the situation.

Источник

I maintain an application that collects data from a datalogger and appends that data to the end of a binary file. The nature of this system is that the file can grow large (> 4 gigabytes) small steps at a time. On of the users of my application has seen cases on his NTFS partition where the attempts to append data fail. The error is being reported as a result of a call to fflush(). When this happens, the return value for GetLastError() is 665 (ERROR_FILE_SYSTEM_LIMITATION). MSDN gives the following description for this error

The requested operation could not be completed due to a file system limitation

A search for this error code on google gives results related to SQL server with VERY large files (tens of gigabytes) but, at present, our file is much smaller. This user has not been able to get the file to grow beyond 10 gigabytes. We can temporarily correct the situation when we do some operation (like copying the file) that forces some sort of rewrite in the file system. Unfortunately, I am not sure what is going on to put us in this condition in the first place. What specific conditions in an NTFS file system can lead to this particular error being reported on a call to fflush()?

Doug T.'s user avatar

Doug T.

64.4k28 gold badges139 silver badges202 bronze badges

asked Sep 6, 2011 at 17:27

Jon Trauntvein's user avatar

Jon TrauntveinJon Trauntvein

4,4536 gold badges40 silver badges69 bronze badges

2

This sounds like you’ve reached a limit in the fragmentation of the file. In other words, each flush is creating a new extent (fragment) of the file and the filesystem is having a hard time finding a place to keep track of the list of fragments. That would explain why copying the file helps — it creates a new file with fewer fragments.

Another thing that would probably work is defragmenting the file (using Sysinternals’s contig utility you may be able to do so while it’s in use). You can also use contig to tell you how many fragments the file has. I’m guessing it’s on the order of one million.

If you have to flush the file frequently and can’t defrag it, something you can do is simply create the file fairly large in the first place (to allocate the space all at once) and then write to successive bytes of the file rather than append.

If you’re brave (and your process has admin access), you can defragment the file yourself with a few API calls: http://msdn.microsoft.com/en-us/library/aa363911(v=VS.85).aspx

answered Sep 6, 2011 at 17:37

Gabe's user avatar

6

Источник

October 19, 2013 by Sql Times

Quick one today.

Occasionally, in my development environment, when I run CHECKDB, I run into this error:

ERROR: Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered.

The error message is pretty vague and does not give me any leads to troubleshoot the issue. But upon some head-scratching, I realized this.

When we run CHECKDB, Sql Server internally runs CHECKCATALOG, CHECKTABLE, CHECKALLOC on all the objects. This is a pretty resource intensive operation, so it needs a lot of resources. CPU, Memory and I/O. There is one more thing, Disk. Yes it needs some disk space to run its calculations.

From what I read from Paul Randall, when you run CHECKDB, Sql Server creates a database snapshot of the database and runs checks on the snapshot. This snapshot needs some disk space. If you have a VLDB with a lot of traffic coming in to make changes, then this Snapshot takes up more space. How big does this snapshot get? I can’t tell, but if there are many changes (UPDATEs/INSERTs) happening on the tables, then snapshot database gets bigger due to ‘copy-on-write’.

Just make sure you have enough disk space on the drive that have the original database’s data files.

Hope this helps,
_Sqltimes

Posted in DBA, Error Messages, Technical Documentation, Tips and Techniques | 3 Comments

Источник

Понравилась статья? Поделить с друзьями:
  • Параллельная парковка ошибки как исправить
  • Параллельные синтаксические конструкции ошибки
  • Паразитное пламя baxi ошибка e35
  • Парадокс лаунчер общая ошибка
  • Парад планет необычный природный феномен речевые ошибки