Lost Database files on sql server 2005 (from flash memory)
-
Hello,
i have created a database in SQL Server 2005 with following files and filegroups:
C:\testDB\test.mdf on Primary filegroup
H:\TestDB\test1.ndf on FG1 filegroup
C:\testDB\test_log.ldf
Note that H:\ is a removeable USB Flash Disk.
then i try to create a table on each of these filegroups
create table tbl1(col1 int identity, col2 char(1) default 'a') on 'Primary'
create table tbl2(col1 int identity,col2 char(1) default 'b') on FG1
now i try to insert a couple of records in each table.
insert into tbl1 default values
insert into tbl1 default values
insert into tbl2 default values
insert into tbl2 default values
after that i removed the flash disk. then i send this command
select * from tbl2
I didn't Believe it. it worked. not only this but also, any other command which needs to access data files worked (insert, update, delete, create index , ....)
even dbcc checkdb works well. but checkpoint dosn't work.
as soon as i restarted SQLServer Service, the database marked as suspected and i need to recover it.
now, what is the theory behind this action. where the data comes from when the data file is not ready?
any help will be appreciated
thanks in advance
Farzad
-
Data would have come from the cache memory of the database server. Once you restarted the service the cache has got flushed and your database was amrked suspect.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
thanks for your reply,
is there any limitation for amount of changes (and amount of cache)?
i mean, if i change thousands of records, is it possible to read them again or not?
also, the dbcc checkdb , sp_helpdb are working properly. what about these commands? are they reading metadata from cache?
thanks
-
I'm not sure this is correct though. Any modification to data must be hardened to the disk subsystem before SQL says all is well. The subsystem either being the physical disk or disk controller cache. Any selects would read from cache if it is in cache, but modifications get modified and passed through to disk.
Likewise, a DBCC checkDB should have spotted this as that is exactly what it does - check the consistency of data on disk. Not data in cache.
I did not think SQL would even let you create a file on a removable device (Left Memory stick at home so cannot test this)
Are you 100% sure this you created the FG, and added a file to it, on the removable drive?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Another thought, this is _really_ dodgy. Data integrity is of paramount importance. If a server could loose a disk and continue running not reporting anything, no one would know about a failed disk. Fist stop and start of SQL, you would loose an unknown amount of data.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
I'm 100% sure. no doubt
the table is created on FG1 containing a secondary data file. the file is on the flash disk. and flash disk isn't ready. but before restarting SQL Server service, everything works. even dbcc checkdb. even updates. everything except checkpoint.
checkpoint, fails because wants to access datafile which is not ready.
-
Yep,
I can confirm that this works as described. In fact, I just created just one data file on a flash disk and removed the disk.
Data integrity is maintained because the data is hardened to disk (just not the disk you're thinking of) - remember, the log file is on a physical disk and when you perform insert/update/delete statements, as long as the log file is accessible and can be written to then that's fine.
When a checkpoint runs, it will try to write all dirty pages to the data file - which is where it fails if the data file is not accessible. But the data is still in the log file (or a record of the transaction).
When you restart the SQL Server, the database is recovered (or at least SQL Server trys to recover it) and SQL Server will attempt to roll forward commited transactions and rollback uncommitted transactions. Of course, if the data file is not available this cannot happen.
However, if you put the disk back in and then restart SQL Server the database can be successfully recovered. Check the error log and you will see something like this:
11 transactions rolled forward in database 'Test' (15). This is an informational message only. No user action is required.
So, as long as your log file is available you can create transactions that will at some point get rolled forward if you can connect back to your data file. The log file is key to maintaining data integrity in SQL Server.
Interesting test.....
Karl
-
I'll be dammed. I just hooked a memory key of someone and you are right. Even worse, my DBCC comes back clean.
Here's something though: I did the test, all is well. I wanted to see what SQL would say if the DB was simple. It will not let me change it to simple due to write offset error on the rem file. They file is available to the OS when I try this.
Personally, I think it's dodge. bad! Bad!
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
thanks karl,
about transaction log, you are right. as long as accessibility of log file any transaction will be retain and persist on log(disk).
but as you mentioned, its still dirty data. by default the isolation level is read committed. so, why other users can select updated (and still un-checkpointed) data from this table?
next question is that, why some commands like dbcc checkdb or even create index work successfully?
-
SQL Server will always attempt to get data as quickly as possible so if it's available in the buffer cache it will get it from there. So when you perform an insert or an update, even though it's not hardened to disk, when I run a query against that table SQL Server knows that it was recently updated and gets this data from the cache. Either that or directly updates the data in the cache (I'm not sure which). From a transactional point of view the data isn't dirty any more because it was committed to the transaction log. The data is only dirty in a physical sense because it isn't written to the data file.
I imagine that at some point, when the buffer cache gets filled up and starts being reused, that things might start to go wrong. It would be interesting to try and simulate this and see exactly what goes wrong. Maybe allocate a small amount of memory and throw in a few cursors to use up a lot of memory and see what happens.
I must admit, that I am surprised that a dbcc checkdb works successfully. But then, looking at BOL it says: CHECKDB checks the allocation, structural, and logical integrity of all the objects in the specified database. It doesn't say anything about checking physical structure. Even so, you'd expect it to spot that the data file was unavailable.
-
I am sure that the data will be read from and written to cache and particularly if the database runs in full recovery mode where the data is not written to disk until a check point occurs.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply