December 2, 2007 at 10:35 am
I have a situation where my mdf and log files both sit on the same RAID volume.
The problem is that I only have one volume. I know its best for log files to be on a (seperate) volume but I don't have the option of doing that. Should I move my log files to the c drive to get them off of the same drive or leave them on the RAID volume even though they will be on the same drive as the mdf files?
Also I have a few jobs that fail at night pretty often. there is nothing wrong with the job itself but i was wondering this:
I always get a "[SQLSTATE 42000] (Error 7399) 'salesTable' is not a valid table or business view name or cannot be opened.] ".
could this have to do with a latency problem associated from having sequentially read log files on the same drive as my randomly read mdf files?
December 2, 2007 at 12:39 pm
Keep the log and data together if you don't have another volume. You want them protected.
The error has nothing to do with log and data files. It sounds like the job is connecting to another database by default and this table isn't in the context of what the job is trying to do.
December 3, 2007 at 1:56 am
Agreed with keeping both data and log files together rather than moving one to a drive that's not high availability.
However, I would also add that you make damn sure any backups you write to disc first instead of streaming direct to tape are written to a different disc, even if it's not high availability. Grandmothers and sucking eggs, I know, but I'd rather be slated for stating the obvious than not.
Semper in excretia, suus solum profundum variat
December 3, 2007 at 8:50 am
Are you implying your C drive is not configured as RAID 1 or any other type of RAID configuration? If so, you should consider mirroring the OS and then move your log files to the C drive. The decision to move the log file(s) away from the data file(s) also depends upon how the server is utilized and if good performance is a necessity. Keep in mind if your database server has a very low amount of transactions and the transactions are small you may not notice much of a performance improvement by moving the log files to the C drive.
Dave
December 3, 2007 at 9:16 am
DBADave (12/3/2007)
Are you implying your C drive is not configured as RAID 1 or any other type of RAID configuration? If so, you should consider mirroring the OS and then move your log files to the C drive. The decision to move the log file(s) away from the data file(s) also depends upon how the server is utilized and if good performance is a necessity. Keep in mind if your database server has a very low amount of transactions and the transactions are small you may not notice much of a performance improvement by moving the log files to the C drive.Dave
I'm certainly not going to suggest that Dave's approach is wrong, but I personally try to avoid putting any database files of any sort on a system partition. My reasoning is that there are too many types of files already growing there (e.g. windows updates), and adding one that's actually designed to grow fairly substantially makes the challenge greater of ensuring you've always got enough disc space free. And if the system partition runs out of disc space, not only will the database stop dealing with further requests, the server itself will also fall into a steaming heap - two problems instead of one.
As I said though, that's just my take, and there are a whole host of reasons why a decision like that might be sensible even considering the proviso I've outlined.
Good luck either way.
Semper in excretia, suus solum profundum variat
December 3, 2007 at 10:16 am
I agree it is not ideal and I've only done that for a few older servers based upon recommendations from Microsoft engineers. For new systems our policy is to keep OS, data, logs, tempdb and backups all on their own arrays, but in smaller companies this may not be an option financially. When placing the log file on the OS partition it is important to set a maximum file size for the log file so as not to consume all of the OS drive space. To play it safe I would make sure the C drive has at least 10% free space, but the actual amount depends upon the size of your C drive. If you get too low performance will suffer. It is also important to create an alert to notify you when the log file is approaching the maximum setting. If the server is dedicated to SQL Server the overhead on the OS partition is low prior to moving the log file(s) to the C drive. This assumes the drive also contains your basic software such as that used for backups, anti-virus, security (CSA, etc.), monitoring (MOM) and perhaps auditing/inventory (SMS, Altiris, etc...). However, if the server is not dedicated to SQL Server then moving the log file(s) to the C drive requires greater thought.
Dave
December 3, 2007 at 4:48 pm
If you have to, the logs can run on C, but I'd limit the max growth and perhaps even put a 1GB or so spaceholder out there for emergencies.
December 3, 2007 at 4:55 pm
Ok thanks guys all of your information was helpful.
December 4, 2007 at 4:15 am
One thing to consider on the SalesTable error is the schema name. Is the default name dbo? If not, you might have to edit whatever query is referencing that table so that it uses the proper schema.
Remember, if schema is not specified, SQL looks for the current user's schema context first and then dbo second. And if neither references the schema that SalesTable belongs to, you're going to get that error.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply