December 20, 2011 at 12:18 am
Hi All,
We have sqlserver 2005 Enterprise edition in production.
This is the first time we have enabled to capture the output of the DBCC checkdb to a file and we realised that it is throwing errors. Previously the intergrity check was done through the maintenance plan.
Database size 500 GB. And the recovery model of the db is in full. We have a database mirroring happening on this database to other remote server.
Please guide me how serious is this issue. Also share your expert knowledge to overcome this issue.
2011-12-20 04:51:19.07 spid203 Error: 17053, Severity: 16, State: 1.
2011-12-20 04:51:19.07 spid203 G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ARSystem.mdf:MSSQL_DBCC6: Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered.
2011-12-20 04:51:19.07 spid216 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 0x00005cfa0de000 in file 'G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ARSystem.mdf:MSSQL_DBCC6'. 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.
2011-12-20 04:51:19.07 spid216 Error: 17053, Severity: 16, State: 1.
December 20, 2011 at 12:37 am
OS Errors 1450 and 665 are reported for database data files
http://support.microsoft.com/kb/2002606
Sparse File Errors: 1450 or 665 due to file fragmentation: Fixes and Workarounds
December 20, 2011 at 1:08 am
Thanks Dev.
One quick question. What approach will you take when it happens to your DB. Do you stop the DBCC checkdb and follow a different approach.
My db files are heavily fragmented. DB grows a GB everyday.
December 20, 2011 at 1:45 am
Several ways you can checkDB a VLDB (though 500GB is no where near a VLDB. It's medium-large these days).
You could restore the backup to another server and run CheckDB there. Takes the load off your production server, verifies that your backups are restorable. Downside is that if checkDB finds corruption you'll have to checkDB the source DB to be sure where the problem started.
Break the checkDB up into portions and run on alternate days. CheckDB is a combination of CheckAlloc, CheckCatalog and CheckTable on all tables, so you can customise to your heart's content.
Considered defragmenting the drive (not index defrags, drive defrags)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 1:55 am
baabhu (12/20/2011)
Thanks Dev.One quick question. What approach will you take when it happens to your DB. Do you stop the DBCC checkdb and follow a different approach.
My db files are heavily fragmented. DB grows a GB everyday.
Gail already answered it. It’s more of hardware (disk) issue so the solution will also be at hardware level i.e. Disk Defragmentation.
December 20, 2011 at 2:30 am
Thank you very much Gail and Dev.
September 12, 2014 at 9:30 am
This message has to do with all the SQL versions after SQL 8.0 and is caused due to file system (OS) and not SQL. Windows 2k8 & R2 solve it by a patch which is included in the RTM of the later OS releases. It’s not caused due to fragmentation of the existing MDF/NDF or for that sake any existing file on the current disk. It’s caused because of internal i.e. kind of logical fragmentation in the snapshot (sparse) file (highlighted below). Typically a file's meta data would get complex as the file's fragmentation level increases. Sparse files are chopped up into 64 KB chunks and then each chunk is evaluated to see if it is just a run of zeros. It’s so we don't have to actually use space to store large runs of zeros. The downside is a file that instead of being contiguous, is in 64 KB chunks....which really really really fragments the file. The more fragmented the file becomes the larger the file's attribute list gets. NTFS has a hard coded limitation for the attribute list (1 KB) to prevent performance issues. Now when you have a very large file that is sparse (or zipped) you may run run into this limitation (40-90 GB). Fix is to format with /L NTFS 64 KB.
The sparse file will created (only on NTFS) if DBCC is not executed with a TABLOCK, it’s created as the DBCC starts & vanishes on end of operation. One separate file is created for each MDF/NDF, size of which is dependent on the RO operations happening in the specific data file. These snapshot files are hidden unless you are using sql 12.0 and not in question if you are on 8.0.
In case you are seeing events 823 & 17053, my suggestion would be the yellow ones others are also good to follow
•DB disks should be formatted with 64 kb clusters.
•Add /L for large volumes.
•Before running DBCC an ESTIMATE_ONLY should be used for the first time.
•Run DBCC CHECKDB at a time when less data modification is taking place.
•Divide the database into a few more files. The limitations are per sparse file and each database snapshot creates matching sparse files for each database file. For example if the database files are each limited to 64GB in total size you can't exceed the limitation on a per file basis. Also the DB files in a file group should be equi sized & have same increment values.
•Find out which tables/indexes result in the most write activity during the lifetime of the snapshot and separate them out into a different file group with many files of comparatively smaller sizes.
Following are also good to follow
•Bytes per-sector should be checked.
•Partition alignment should be cross-verified.
•For me it’s not pointing for these errors were triggered because of a high DB fragment count.
oBut even otherwise there doesn’t seems to be any negative impact of adding /L (due tests needed).
•Create a mannual snapshot on a different drives other than the source data files and with less file activity to reduce file level fragmentation. And run DBCC there.
In short a defrag on the disk won’t help, it (disk) has to be re-formatted with /L as NTFS of 64 Kb.
September 17, 2014 at 4:21 am
First problem is that it appears that the master database has either been moved, or deleted, or the folder has had all rights removed from the SQL Server's account.
I'm guessing that you've also got the rights to the reg keys that the SQL Server needs to update locked down.
September 17, 2014 at 4:51 am
enriquemallon (9/17/2014)
First problem is that it appears that the master database has either been moved, or deleted, or the folder has had all rights removed from the SQL Server's account.
If that was the case, the SQL service wouldn't even start.
p.s. 3 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2015 at 7:51 pm
Sharing some windows updates which are applicable on Win 2012 R2 & worthy instead of /L.
There ‘ve been some changes in the NTFS on Win 2012 R2 once these HF are applied. With the NTFS allocation logic changed, the improvement is seen with file performance and fragmentation as the OS allocates larger blocks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply