sql databases 2008

Hi, I have a blade server where I have runing on it more than 15 databases.
Because of the disk space I was forced to put some of the big databases in a storage Synology and it is connected using the Microsoft ISCSI Iniatiator.
My problem is: the connection of the server to the storage is connected trough a Cisco Switch because I cant connect the blade server directly conected to the storage because of a configuration on the network cards of the chassis of the blade servers.
The problem that I have is that sometimes the connection is disconnected and I have to put back the connection online and have to go to the sql and take offline and online the databases, but now I have 3 databases that when I want to put the database ofline it says that the databases are not accessible.
What can I do to put the dbs online?

Thanks
Joel
britholAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
Disconnecting SQL database files from SQL Server while SQL Service is running can be disastruous and yous SAN or disk aray should never do that. Can you see the databases you mentioned above listed in SQL SSMS on that SQL server? What status are they in? RECOVERY?
0
britholAuthor Commented:
They are in recovery mode.
0
lcohanDatabase AnalystCommented:
You may be lucky and in this case you should be able to bring it back ONLINE. If some transactions are lost....you may incure some data lose but frist try to run following command against mster db in that SQL box:

ALTER DATABASE database_name SET ONLINE;
GO


If the command above runs clean you MUST issue a DBCC CHECKDB immediately after that like:

DBCC CHECKDB (database_name) WITH NO_INFOMSGS;
GO

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

britholAuthor Commented:
The first command runned correctly but the second one gave these error

Msg 945, Level 14, State 2, Line 1
Database 'PFC_IMG_ARCH' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
0
lcohanDatabase AnalystCommented:
Do you have enough disk space where ALL SQL database files are? I mean all physical locations for system db's and user db's

is there anything else in SLQ logs - via SSMS, Management section SQL Server Logs
0
britholAuthor Commented:
Yes I have disk space available 7.6gb.

In the log file:

Date            10/5/2011 2:12:19 PM
Log            SQL Server (Current - 10/5/2011 2:12:00 PM)

Source            spid27s

Message
Starting up database 'PFC_IMG_ARCH'.
0
Kevin CrossChief Technology OfficerCommented:
lcohan has you on the right track. I will only throw in that I have seen instances with network connectivity issues with disk is that sometimes the operating system does not recognize the volume is back online until after a reboot. Or if it is seeing the volume, SQL may not be recognizing the path is back online (i.e., accessible), so you can try restarting the SQL services to see if it picks it up. Have not tried the latter as in the past when these kinds of things happened with SANs, a reboot is usually followed on servers connected. Having redundant paths is expensive, but as stated it is disastrous if this is consistent as you are potentially removing disk in the middle of I/O operations.
0
britholAuthor Commented:
HI mwvisa1, after a restart in the sql services it recognizes the dbs, but take sometime to put them online again.
0
lcohanDatabase AnalystCommented:
That is true and based on "mwvisa1"'s comment can you "see" the files from inside SQL? I mean if you would run a command like below against that drive leter:\folder what do you get? Even though - a restart for SQL service at least might be required.

exec xp_cmdshell 'dir G:\SQL_Data_files_folder\*.*'
0
britholAuthor Commented:
I made a restart of the sql services and now all the dbs are running.

But to avoid the future issues like that what you guys recommend?
0
Kevin CrossChief Technology OfficerCommented:
"Having redundant paths is expensive, but as stated it is disastrous if this is consistent as you are potentially removing disk in the middle of I/O operations." Alternatively or in addition, find the root cause of when these drops occur. Maybe there is some configuration, firmware, etc. that needs to be looked at with Network Engineers.
0
lcohanDatabase AnalystCommented:
Your SQL files MUST be on a physical location (SAN, ARRAY, local disks, etc) ALLWAYS connected to the SQL server computer WHILE SQL Server Service is running.
This is it - you can't disconnect db files from SQL while this service is running.
0
Kevin CrossChief Technology OfficerCommented:
Exactly. To make my comment more clear, if you cannot get rid of instances where a particular ISCSI switch disconnects for a short period of time -- I think in the past even millisecond lapses cause issues, then you will need to have dual switches/paths to your SAN or disk array. That way the OS and SQL do not suffer a disconnect because of one failing. Knowing WHEN and WHY the disconnects are happening may help to determine the risk of BOTH paths going offline at the same time by ensuring the proper portion of route is made independent. In other words, if the root cause is power, having the switches on the same PDU does you know good. Likewise, if it is the switch, having dual connections in the same switch does you no good. Hopefully that makes more sense as I see I did not give enough details before.
0
lcohanDatabase AnalystCommented:
Besides all the above, I don't want to sound out of tune however....

"Yes I have disk space available 7.6gb."

for a SQL server hosting multiple databases seems extremely little today...I would put immediately alerts to let me know if the disk(s) system(s) hosting SQL DB files is runing on less than 20% free disk space.
That is - a bad query with some cartesian product can fill very quick the 7.6GB available in the log file.
0
Kevin CrossChief Technology OfficerCommented:
I agree. I have one application written very poorly that fills 30gb during one transaction. It may even go higher if that was not all we had left since we have not migrated to new hardware yet. Very annoying.
0
britholAuthor Commented:
The 7.6 gb free is on the C drive... I have all the dbs in the secondary partition and there I have at least 60gb of free space.
0
Kevin CrossChief Technology OfficerCommented:
Good deal, so then you just need to focus on keeping the data storage attached. Good luck!
0
britholAuthor Commented:
what is the best way of backups when you have a database of 150gb?
0
Kevin CrossChief Technology OfficerCommented:
You may want to start that as a whole new question where you put some more details on your situation on environment. For example, if you are in a large corporation that has separate backup software that can be outfitted with an open file agent or SQL server specific plugin, then there may opportunities to leverage that architecture as the biggest thing is keeping 150gb copies (yes, several) or having a specific strategy like full backup on Sunday with incremental ones throughout the week. So my point is that this is possibly a long subject and you probably want to start fresh to give clarity to question, but -- more importantly -- to attract the most Experts you can to get differing opinions. With the length and description of this thread, you are likely not to get many new participants looking to discuss backup strategies. Just my humble advice anyway. *smile*
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.