December 31, 2008 at 10:43 am
We have one customer database on one of their servers that consistently returns the following error when I try to run any of the DBCC commands CHECKDB, CHECKALLOC, CHECKCATALOG...
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 1450(Insufficient system resources exist to complete the requested service.) while attempting to open or create the physical file 'D:\DB\DatabaseName_data.mdf:MSSQL_DBCC9'.
Msg 7928, Level 16, State 1, Line 1
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.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
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.
So it looks like the first two error messages are related to the snapshot creation, then the last few errors are due to the fact that DBCC tries to lock the database to complete and it can't, so we can probably ignore those.
I've been looking around online and found a few articles but nothing specific to this case. One interesting point to note is that if I AM able to get an exclusive database lock, I CAN run the DBCC checks to completion with no errors using the TABLOCK option. So this is definitely an issue with the snapshots on this database. I just don't know how to troubleshoot / fix it.
Some basic info and interesting facts I've noticed:
- SQL Server 2005 Enterprise 64-bit v9.00.3215
- The database is about 670 GB and is in SIMPLE recovery mode
- Data file 1 is set to 10GB growth interval unlimited (563 GB)
- Data file 2 is set to 1MB growth interval unlimited (54 GB)
- Log file is set to 10% growth
- Full backups are taken daily
- The owner is a windows authenticated login that is not 'sa' or the service account
This one database is the only database affected on the server and there are about 8 user databases. This database is by far the largest one.
Please help with any troubleshooting steps or properties to look at which may help resolve the issue. I can post any additional necessary information as well.
Thanks
- John
December 31, 2008 at 11:29 am
Two things I can think of:
1) Is the volume this database resides on NTFS - or, something else? What kind of SAN is presenting this storage - check with the SAN vendor to find out if there is anything specific about how the storage is presented.
2) Is there enough space available on the volume to create the database snapshot required for the CHECKDB operation? I don't know how much space is required - but you definitely need additional space available to create it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 31, 2008 at 11:54 am
I'm not sure the SAN configuration is the issue because if that was the case then I think all of them might have an issue, but I do think you may be onto something with the snapshot size. I read online that the snapshot size shows up in explorer as the size of the database even though it's initial size is really 0KB. I'm thinking maybe Windows thinks there's not enough room for the file and it errors out. In any case, here's the results of the xp_fixeddrives:
C19407
D492563
E155401
F28873
And here's the reported sp_spaceused info:
reserveed data index unused
656757136 KB489434672 KB167115512 KB206952 KB
Here's some info about the files
fileid size driveletter
1607332352D
223416192E
356152064f
Do you think it's possible that it's trying to create the file on the D drive and just failing because windows thinks there's not enough space?
Thanks,
John
December 31, 2008 at 12:03 pm
Yes - I think it is possible that is the issue. However, just because one volume is not having an issue does not mean another volume isn't.
You would need to look at how the volume itself was created and presented to the OS. Is there one volume, partitioned as separate drives? A single partition per volume? Multiple volumes defined as a single partition?
To see how that is defined, right-click on Computer and select Manage. Navigate to the disk management and see how the volumes are laid out.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 2, 2009 at 9:35 am
Can you give more examples about what to look for with the SAN configuration? Specifically, what would cause a problem like this besides the disk not being NTFS.
Also, do you have any links that explain what goes on internally with the DBCC snapshots and any information on how to manage / reset them?
Thanks,
John
January 2, 2009 at 9:46 am
The only tools I know of are the standard OS tools (Disk Management) and specific SAN tools (e.g. SnapDrive for Netapp).
Basically, your SAN presents a volume to the OS. This volume could be any RAID level with any number of physical drives, which all depends upon which SAN vendor and how the volumes are created.
Once a volume is presented to the OS - the OS then creates one or more partitions on that volume. Each partition is then assigned a drive letter. To create the partition, you use DISKPART and then you format the drive. When formatting the drive - you define whether or not the drive is FAT/FAT32/NTFS and define the allocation units (generally, for SQL Server database files this will be 64 on Windows 2003 or lower). On Windows 2008 or higher we are going to start seeing allocation units a lot higher.
As for how the snapshots work - review Books Online which has all the information you could want on this process.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply