brithol
asked on
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
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
lcohan
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?
ASKER
They are in recovery mode.
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
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
ASKER
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.
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.
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
is there anything else in SLQ logs - via SSMS, Management section SQL Server Logs
ASKER
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'.
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'.
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.
ASKER
HI mwvisa1, after a restart in the sql services it recognizes the dbs, but take sometime to put them online again.
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\*
exec xp_cmdshell 'dir G:\SQL_Data_files_folder\*
ASKER
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?
But to avoid the future issues like that what you guys recommend?
"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.
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.
This is it - you can't disconnect db files from SQL while this service is running.
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.
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.
"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.
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.
ASKER
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.
Good deal, so then you just need to focus on keeping the data storage attached. Good luck!
ASKER
what is the best way of backups when you have a database of 150gb?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.