Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql databases 2008

Posted on 2011-10-05
19
Medium Priority
?
189 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:brithol
  • 7
  • 6
  • 6
19 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 36919699
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
 

Author Comment

by:brithol
ID: 36919803
They are in recovery mode.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36919888
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:brithol
ID: 36919933
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
 
LVL 40

Expert Comment

by:lcohan
ID: 36920045
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
 

Author Comment

by:brithol
ID: 36920136
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
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36920140
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
 

Author Comment

by:brithol
ID: 36920173
HI mwvisa1, after a restart in the sql services it recognizes the dbs, but take sometime to put them online again.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36920186
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
 

Author Comment

by:brithol
ID: 36920242
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
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36920340
"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
 
LVL 40

Expert Comment

by:lcohan
ID: 36920354
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
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36920464
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
 
LVL 40

Expert Comment

by:lcohan
ID: 36921929
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
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36922175
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
 

Author Comment

by:brithol
ID: 36927000
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
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36927106
Good deal, so then you just need to focus on keeping the data storage attached. Good luck!
0
 

Author Comment

by:brithol
ID: 36928988
what is the best way of backups when you have a database of 150gb?
0
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36930986
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question