September 7, 2011 at 9:27 am
I'm getting errors when running DBCC CHECKDB on a particular database. The database is running on my local workstation (SQL Server 2008 R2, Win 7, fairly fast machine with plenty of HDD space) which is using a PGP encrypted hard drive.
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
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.
Additionally, the log shows the following errors:
The operating system returned error 5(Access is denied.) to SQL Server during a write at offset 0x00000022a00000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\MyFunkyDB.mdf'. 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.
The background checkpoint thread has encountered an unrecoverable error. The checkpoint process is terminating so that the thread can clean up its resources. This is an informational message only. No user action is required
This is on a test database that I just loaded with a table of 10 million rows of data.
Anyone know what this means?
September 7, 2011 at 9:34 am
Anything else that fails?
How did you load that db?
Have you tried running the same script with encryption off?
September 7, 2011 at 9:53 am
Looks like SQL Server service user is unable to write the file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\MyFunkyDB.mdf'.
Check the service account in SQL Server Configuration manager. If you change the service accounts from the services applet, you don't have them added to the service account groups:
SQLServerMSSQLServerADHelperUser
SQLServerMSSQLUser
SQLServerSQLAgentUser
-- Gianluca Sartori
September 7, 2011 at 9:59 am
I'm unable to backup the database; I get the same "access denied" error.
This is a database I use for testing things out. This is how I loaded the data:
SELECT TOP 10000000
SomeID = IDENTITY( INT,1,1 ),
SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1 ,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)
+ CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) ,
SomeMoney = CAST(ABS(CHECKSUM(NEWID())) %
10000 / 100.0 AS MONEY) ,
SomeDate = CAST(RAND(CHECKSUM(NEWID()))
* 3653.0 + 36524.0 AS DATETIME) ,
SomeHex12 = RIGHT(NEWID(), 12)
into My10mil
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2 ;
which comes from this article:
http://www.sqlservercentral.com/articles/Stairway+Series/73779/
I can't turn off encryption unless I want to re-install Windows 7, SQL Server and all other apps. It's PGP whole disk encryption. I suspect it's related to the encryption, but I don't know.
September 7, 2011 at 10:04 am
If you create a new db in the same folder(s). Can you read / write from it?
September 7, 2011 at 10:08 am
Gianluca, the files MyFunkyDB.mdf and MyFunkyDB.ldf are there on the hard drive in the proper folder, same as the other databases I've created without any issues.
Also I'm able to run a select query on the table; there's one user table in the db.
September 7, 2011 at 10:10 am
Try creating a new user db and doing the select into with 10 000 rows.
See if that reproduces the issue.
September 7, 2011 at 10:15 am
Anakin,
Yes, I just created a smaller version of the same database, same kind of data, just 1000 rows instead of 10 million.
No problems, I can back it up ok. So the problem may have to do with the size of MyFunkyDB. 10 million rows, 1 GB mdf, 1-2 GB ldf.
September 7, 2011 at 10:16 am
sqlgreg (9/7/2011)
Gianluca, the files MyFunkyDB.mdf and MyFunkyDB.ldf are there on the hard drive in the proper folder, same as the other databases I've created without any issues.Also I'm able to run a select query on the table; there's one user table in the db.
OK, so now you can exclude File System permission issues.
-- Gianluca Sartori
September 7, 2011 at 10:19 am
sqlgreg (9/7/2011)
Anakin,Yes, I just created a smaller version of the same database, same kind of data, just 1000 rows instead of 10 million.
No problems, I can back it up ok. So the problem may have to do with the size of MyFunkyDB. 10 million rows, 1 GB mdf, 1-2 GB ldf.
Size shouldn't have anything to do with corruption.
Can you re-run the code see if it corrupts again?
P.S. For 10M rows I think you need 3 cross joins to sys.columns
September 7, 2011 at 10:50 am
Ok, I deleted the db and re-created it.
When I try to backup the database, I get the following error:
The operating system returned error 5(Access is denied.) to SQL Server during a write at offset 0x00000001ca98000 in file 'C:\Program Files\Microsoft SQL Server\MSSSQL10_50.MSSQLSERVER\MSSQL\Data\MyFunkyDB.mdf'. 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).
I'm quite sure now that this is somehow related to the PGP whole disk encryption.
btw..
SELECT COUNT(*)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2 ;
--gives me 26,163,225
September 7, 2011 at 10:57 am
Don't delete, create a new blank db (default path).
Then rerun the select into. Maybe you've hit a bug. I'll try repro on my systems later on to confirm.
September 7, 2011 at 11:44 am
OK, tried adding yet another version, but 1 million rows. It's behaving ok, backup runs fine.
Also created another with 20 million rows, and it will backup fine as well!
September 7, 2011 at 2:09 pm
But I still have a 10 million row database that will not back up, same error as before.
Bottom line is that there's no pattern I can see, and I suspect it's related to the whole disk encryption.
September 7, 2011 at 2:13 pm
Well since this is 1 off it could also be a bug in the code. Very unlikely but not impossible I'm afraid.
Did you post the code you ran verbatim? If so I'll try it on a couple versions to see if I can repro the corrupt db.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply