June 5, 2011 at 3:51 pm
We have 2 larger databases that had their logs moved to a virtual drive as they were too big.
During a recent outage the physical server was brought back by service team before the virtual and therefore the log files could not be located and the server created new ones back on the original locations (due to model db I guess). This was not spotted and caused major issues crashing the server during heavy reporting! BAH!
Anyway - my question is - is there a setting I don't know of that tells a db to stay offline if the log files cannot be found rather than switching to a different location?
Cheers
Shark
June 5, 2011 at 5:35 pm
Heya!
First of all I have to say "Tut tut" if you have a database server on a SAN or NAS environment the SQL engine should be the absolute last thing you start up or the lovely world of data corruption ensues.
But to answer your question, if you connect to your SQL server using the Microsoft SQL Server Management Studio, right hand click on the root of your database server and select "Properties" A "Server Properties" window will pop up, select "Database Settings" from the left hand side and and the bottom of that window you will see.
Default save location, Data: Log: - Change those to what you want to do - Save and your done!!
I hope this helps!!
Sam
June 6, 2011 at 7:34 am
Will the db just stay offline if the default location is not available? or will it increase chances of corruption somehow? (database is backed up the the eyeballs anyway)
It will help on this server.
We have a similar one in the same boat that is a SQL 2000 server. The default locations are blank on this one and yet it defaults to the D:\. Is it following this setting or model in SQL 2000?
June 6, 2011 at 8:33 am
Shark Energy (6/5/2011)
We have 2 larger databases that had their logs moved to a virtual drive as they were too big.During a recent outage the physical server was brought back by service team before the virtual and therefore the log files could not be located and the server created new ones back on the original locations (due to model db I guess). This was not spotted and caused major issues crashing the server during heavy reporting! BAH!
Anyway - my question is - is there a setting I don't know of that tells a db to stay offline if the log files cannot be found rather than switching to a different location?
Cheers
Shark
I cannot think of a way this was caused by SQL Server. To my knowledge if any database file isn't available on startup the database will be placed in SUSPECT mode and that is the end of processing for that db until the reason for suspect status is cleared and followup actions taken to get database back ONLINE. There must be some other reason why files got created.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 6, 2011 at 8:57 am
Yeah I must admit I was surprised.
The chain of errors reads -
- Device activation error. The physical file name 'E:\Program Files\MSSQL\Logs\reporting_log.LDF' may be incorrect.
- Attempting to rebuild primary log file for database Reporting.
- New log file D:\Program Files\MSSQL\Data\reporting_log.LDF built.
Until this moment I thought model db was just for settings and default location was for when setting up your db.
June 6, 2011 at 9:33 am
Shark Energy (6/6/2011)
Yeah I must admit I was surprised.The chain of errors reads -
- Device activation error. The physical file name 'E:\Program Files\MSSQL\Logs\reporting_log.LDF' may be incorrect.
- Attempting to rebuild primary log file for database Reporting.
- New log file D:\Program Files\MSSQL\Data\reporting_log.LDF built.
Until this moment I thought model db was just for settings and default location was for when setting up your db.
So were those errors in the SQL Server error log? Can you post the full error log information related to this event (i.e. with error numbers etc)
Are you CERTAIN that no attempts were made to attach said database or create database with for attach or for attach_rebuild_log options?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 6, 2011 at 9:49 am
Well the people doing the reboots wouldn't even know how to do a SELECT statement so the chance of them attaching or creating a db is zero.
The database logs were moved within the last few months with their old location being the D:
I did take those from the SQL Error Log of the 2000 box with the issues but no error numbers. They do what they say on the tin and all that.
June 6, 2011 at 9:59 am
original log location: D:local
moved 4 months ago to X: (external virtual drive)
Virtual environment poopoos.
Database server comes back up before virtual environment and looks for log on x: (does not exist) and defaults to creating a new log in the default location, D:.
errors ensue..
is that an accurate sequence of events?
June 6, 2011 at 10:01 am
Spot on.
Now tell me where I've mucked up? haha
June 6, 2011 at 10:17 am
Now, I'd still suggest providing the requested information to Mr. Guru. He might be on to something I am not in regards to possible data corruption in the mdf files.
If all you lost was the intactness of the log chain, and your mdf files are dandy, you should be fine. You'll need to do your backups again (from full) and note the break in the backup sequence in case someone wants data from that time.
As far as I am aware, there's no way to check to see if a file location is available before bringing a database online after starting an instance.
June 6, 2011 at 10:29 am
I am at a loss. I have never heard of this happening other than for attach/create for attach rebuild log type scenarios.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 6, 2011 at 1:56 pm
To calvo - the data isn't an issue as we don't point in time report and the users were out of the system (it was a planned outage that encountered issues)
To all - I'm left totally stumped on this. No error numbers to provide. No reasoning for it, and clearly at least 4 of you have not heard of this happening before. I'll try and recreate on a dev server and see what crops up.
Will report back if I work anything out. Thanks for your time
June 7, 2011 at 5:22 am
Another thought may be that you check the server properties.
1. Under general, what is the root directory?
2. Under security, what are the login auditing and options?
3. Under database settings data and log?
Also, I would check the system event logs, starting with the application log and see if it has any errors that may apply for the period that this occurred?
June 7, 2011 at 2:52 pm
Discovered something else of interest. 5 databases that are smaller (sub 1gb) all kept the logs on the virtual drive until it had returned. It was only the big databases that recreated. I'm going to look through the logs for more clues as to why it did it for those but not others.
Will reply to you shortly m_cg thanks for your interest 😀
June 8, 2011 at 9:08 am
m_cg (6/7/2011)
Another thought may be that you check the server properties.1. Under general, what is the root directory?
The D:\ folder
2. Under security, what are the login auditing and options?
None.
3. Under database settings data and log?
Both blank hilariously.
Also, I would check the system event logs, starting with the application log and see if it has any errors that may apply for the period that this occurred?
Nothing of note in the logs. Just a false tcpip error we get when the server boots each time (but has always happened on this box and is a red herring to any issues!)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply