SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Doubts with SQL SErver
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

jhnegrao
Yak Posting Veteran

Brazil
81 Posts

Posted - 07/03/2006 :  14:09:54  Show Profile
Hello, All!

Good Afternoon!
At this afternoon, I have a big problem and I don't know what will be the effect to SQL Server and my DataBases.
My server Database use a Disk Storage to store my database files and logs. I had a problem with this storage and it's came down. Now, my drives was disconnected during Database activity and my SQl SErver service is running.
So, I would like to know what's better for my Databases integrity, I should stop my SQL Server service and start it again after solve my Storage problem or I should keep everything on and wait my disks come back on-line.

Thanks a Lot

Juliano Horta

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 07/03/2006 :  21:27:06  Show Profile  Send jen a Yahoo! Message
1. does the disk(s) affected contain any database files? -- i'm assuming they're hot swappable since the sql services are still running
2. can you still access the databases via enterprise manager? -->do you see anything in suspect mode?
3. do you have backups?

with regards to your last question, just to be on the safe side, do a proper shutdown and startup, I reckon the raid system will recover the files spread across them

HTH

--------------------
keeping it simple...
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/04/2006 :  00:43:37  Show Profile
If you are worried that your database might be corrupted run:

DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS

and check that it reports 0 errors.

If that reports errors I suggest you post the complete output in the
Data Corruption Issues forum - folk there will advise if the errors are repairable, or whether you need to recover from backup.

Kristen
Go to Top of Page

jhnegrao
Yak Posting Veteran

Brazil
81 Posts

Posted - 07/04/2006 :  08:55:17  Show Profile
Hello, Thanks for the answers.

My problem was solved without more effects.
About your first question, my disks aren't hot swappable, because it's external storage system. Yes, my disks contain all database files and log files.

About second question, I could access Enterprise Manager but not DataBases. Nothing was in Suspect Mode or Offline.

I shutdown the server, the storage guy solved the problem and I restart the server. The DataBases come up and everything looked fine. I ran a DBCC CHeck DB and No errors.

I think that I lost everything that was in memory and haven't been flush to disk. wasn't it?

Thanks for all

Bye

Juliano Horta



quote:
Originally posted by jen

1. does the disk(s) affected contain any database files? -- i'm assuming they're hot swappable since the sql services are still running
2. can you still access the databases via enterprise manager? -->do you see anything in suspect mode?
3. do you have backups?

with regards to your last question, just to be on the safe side, do a proper shutdown and startup, I reckon the raid system will recover the files spread across them

HTH

--------------------
keeping it simple...

Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/05/2006 :  04:57:57  Show Profile
"I think that I lost everything that was in memory and haven't been flush to disk. wasn't it?"

No, if SQL reported back that the transaction was completed it is guaranteed to be on disk.

Well, unless you have caching disk controllers that didn't "physically" write the data to disk, but told the O/S that they had done!

When you do a "transaction" in SQL then the stuff is committed when the transaction finishes.

If you use

BEGIN TRANSACTION

... stuff ...

COMMIT

then nothing is there until the "COMMIT". If you are not explicitly using Transactions then usually each individual statement is treated as a transaction (but that's configurable, so YMMV).

Stuff which is "half way through" a transaction will be rolled back - it just won't be there. So your database will not be half-and-half - well, assuming that you have written your application to use transactions properly!

Like, for example, fresh inventory arriving

BEGIN TRANSACTION

UPDATE CustomerOrder
SET QtyShipped = QtyShipped + 10
WHERE OrderNumber = 1234
      AND ProductCode = 'SUGAR'

UPDATE ProductStock
SET QtyOnHand = QtyOnHand - 10
WHERE ProductCode = 'SUGAR'

COMMIT

Clearly a failure between the two update statements would leave your database in a mess if there was NO TRANSACTION.

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000