September 7, 2011 at 2:57 pm
This is from the article I mentioned earlier:
/* STEP 1: CREATE THE DATABASE*/
USE master ;
IF EXISTS ( SELECT name
FROM sys.databases
WHERE name = 'HugeDB' )
DROP DATABASE HugeDB ;
CREATE DATABASE HugeDB ON
(
NAME = HugeDB_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\HugeDB.mdf' -- folder name may differ
) LOG ON
(
NAME = HugeDB_log,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\HugeDB.ldf' -- folder name may differ
) ;
/*STEP 2: INSERT A LOT OF ROWS INTO A TABLE*/
USE HugeDB
GO
IF OBJECT_ID('dbo.LogTest', 'U') IS NOT NULL
DROP TABLE dbo.LogTest ;
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 dbo.LogTest
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2;
ALTER TABLE [dbo].LogTest ADD CONSTRAINT [PK_LogTest] PRIMARY KEY CLUSTERED
(
[SomeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
September 7, 2011 at 2:58 pm
And after running that code, I tried taking a full backup and got the errors.
September 7, 2011 at 5:11 pm
sqlgreg (9/7/2011)
And after running that code, I tried taking a full backup and got the errors.
I've pinged in THE expert. Hopefully he knows exactly what's going on.
September 7, 2011 at 10:15 pm
sqlgreg (9/7/2011)
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?
Run DBCC CHECKDB WITH TABLOCK.
September 8, 2011 at 7:18 am
Suresh,
DBCC CHECKDB WITH TABLOCK gives me this error:
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.
right before this, I ran DBCC CHECKDB and got this error, after which SSMS disconnects from the server (my local machine):
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.
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.
Msg 823, Level 24, State 3, Line 1
The operating system returned error 5(Access is denied.) to SQL Server during a write at offset 0x0000001ca92000 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.
September 8, 2011 at 11:31 am
**Update**
I detached the questionable database, stopped the sql server, copied the mdf and ldf files, renamed the old files, and used the new copies to re-attach the db.
Now everything's working ok.. I can backup, and run checkdb without issues.
September 8, 2011 at 11:40 am
So you definitely had a corrupt DB.
I've tried your script a couple times and I couldn't corrupt my db.
I'd do a diagnostics on your disks just to make sure you don't have bad sectors. Not much else to explain this (except ram and an awefully bad luck with bugs).
September 8, 2011 at 12:00 pm
Well, I have a hunch it's related to the PGP whole disk encryption, but I haven't found much online about installing SQL Server on top of that. Maybe it's not ideal, but that's what's required on our workstations.
January 25, 2013 at 9:24 am
I had the same issue and all I had to do was
1) take the database offline
alter database <DatabaseName>
Set Offline
2)bring back online
alter database <DatabaseName>
Set Online
3) DBCC CheckDB now works.
October 16, 2014 at 5:50 pm
During DBCC CHECKDB, If the database being checked has a heavy update workload, more and more pages are pushed into the database snapshot, causing it to grow.In a situation where the volumes hosting the database don’t have much space, this can mean the hidden database snapshot runs out of space and DBCC CHECKDB stops with an error
Refer this
http://sqlism.blogspot.com/2014/10/a-database-snapshot-cannot-be-created.html
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply