Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Agent job errors: 665 File limitation


SQL Agent job errors: 665 File limitation

Author
Message
navis59
navis59
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 16
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:
Log      Job History

Step ID      11
Server      AE06230
Job Name       Append Transactions
Step Name      CDR: I03: Append
Duration      07:14:24
Sql Severity      0
Sql Message ID      823
Operator Emailed      
Operator Net sent      
Operator Paged      
Retries Attempted      0

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.
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3114 Visits: 1439
navis59 (9/8/2012)
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:
Log      Job History

Step ID      11
Server      AE06230
Job Name       Append Transactions
Step Name      CDR: I03: Append
Duration      07:14:24
Sql Severity      0
Sql Message ID      823
Operator Emailed      
Operator Net sent      
Operator Paged      
Retries Attempted      0

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.




My blog

navis59
navis59
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 16
Thanks, looking into it now!
yup
yup
Old Hand
Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)

Group: General Forum Members
Points: 350 Visits: 1024
•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
o   Separate them out into a different file group with multiple files of comparatively smaller sizes.
o   Identify & revise the Index Fill Factor & PAD index values.
o   Use 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.
o   If 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search