SQL Agent job errors: 665 File limitation
-
Environment: 64 bit version of SQL Server 2008 R2
Issue: When I tried to run a job which pulls about 25GB of data, it kept hanging. I waited for it to roll back. Now the step completed after 8 hrs, but gave this error. I am a developer not a DBA and my DBA also has no clue about this error. Any help would be greatly appreciated. Here's the error:
LogJob History
Step ID11
ServerAE06230
Job Name Append Transactions
Step NameCDR: I03: Append
Duration07:14:24
Sql Severity0
Sql Message ID823
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: dddaaron. ...eration could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00001cc56cc000 in file 'U:\usrdb1\dcfinance\CDR.mdf:MSSQL_DBCC22'. 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. [SQLSTATE 01000] (Message 823) 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 0x0000253035c000 in file 'U:\usrdb1\dcfinance\CD.mdf:MSSQL_DBCC22'. 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. [SQLSTATE 01000] (Message 823) 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 0x0000253035c000 in file 'U:\usrdb1\dcfinance\CENTRAL.mdf:MSSQL_DBCC22'. 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. [SQLSTATE 01000] (Message 823) 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 0x00002415962000 in file 'U:\usrdb1\dcfinance\CENTRAL.mdf:MSSQL_DBCC22'. 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. [SQLSTATE 01000] (Message 823) 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 0x00002415962000 in file 'U:\usrdb1\dcfinance\CEN.mdf:MSSQL_DBCC22'. 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. [SQLSTATE 01000] (Message 823) 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 0x00001d4429c000 in file 'U:\usrdb1\dcfinance\CENTRAL.mdf:MSSQL_DBCC22'. 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. [SQLSTATE 01000] (Message 823) 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... The step succeeded.
-
navis59 (9/8/2012)
Environment: 64 bit version of SQL Server 2008 R2Issue: When I tried to run a job which pulls about 25GB of data, it kept hanging. I waited for it to roll back. Now the step completed after 8 hrs, but gave this error. I am a developer not a DBA and my DBA also has no clue about this error. Any help would be greatly appreciated. Here's the error:
LogJob History
Step ID11
ServerAE06230
Job Name Append Transactions
Step NameCDR: I03: Append
Duration07:14:24
Sql Severity0
Sql Message ID823
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: dcrecon_admin. ...eration could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00001cc56cc000 in file 'U:\usrdb1\dcfinance\CDR.mdf:MSSQL_DBCC22'. 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. [SQLSTATE 01000] (Message 823) 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 0x0000253035c000 in file 'U:\usrdb1\dcfinance\CENTRAL DATA REPOSITORY.mdf:MSSQL_DBCC22'. 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. [SQLSTATE 01000] (Message 823) 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 0x0000253035c000 in file 'U:\usrdb1\dcfinance\CENTRAL DATA REPOSITORY.mdf:MSSQL_DBCC22'. 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. [SQLSTATE 01000] (Message 823) 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 0x00002415962000 in file 'U:\usrdb1\dcfinance\CENTRAL DATA REPOSITORY.mdf:MSSQL_DBCC22'. 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. [SQLSTATE 01000] (Message 823) 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 0x00002415962000 in file 'U:\usrdb1\dcfinance\CENTRAL DATA REPOSITORY.mdf:MSSQL_DBCC22'. 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. [SQLSTATE 01000] (Message 823) 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 0x00001d4429c000 in file 'U:\usrdb1\dcfinance\CENTRAL DATA REPOSITORY.mdf:MSSQL_DBCC22'. 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. [SQLSTATE 01000] (Message 823) 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... The step succeeded.
This link may help: http://blogs.msdn.com/b/psssql/archive/2008/07/10/sql-server-reports-operating-system-error-1450-or-1452-or-665-retries.aspx. Try to anonymise you company info, like user names, server names, etc.
-
Thanks, looking into it now!
-
•Avoid running DBCC CHECKDB at a time when other / major data modifications are taking place.
•Divide the database into a multiple files. The limitations are per sparse file and each database snapshot creates matching sparse files for each data file.
•Find out which tables/indexes result in the most write activity during the lifetime of the snapshot
oSeparate them out into a different file group with multiple files of comparatively smaller sizes.
oIdentify & revise the Index Fill Factor & PAD index values.
oUse check table or as appropriate on those.
•Format NTFS partitions with /L (Windows versions before 2012 will require this HF 967351) for large volumes for SQL 2012 & below. With SQL Server 2014 there’s this option to move to REFS but not recommended for now.
oIf the disk can’t be reformatted with /L, add another disk that’s formatted with /L and having “Bytes Per FileRecord Segment” at 4 KB, create a snapshot there & run DBCC on the specific snapshot.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply