What would cause this problem?

Hi, firstly apologies for the scant details, this was all done in my absence and not by SQL people.....
While I was away the network admin realised he needed to resize the partition that the transaction logs live on.
He used sp_detatch, did his thing with the partition (drive letter and path remained the same), restarted SQL and reattached the DBs.
Everything seemed fine until users started doing certain things in the 3rd party application (SQL backend) and they started recieving coallation errors (coallation same as ever was and always working prior to log move). Apparently a restore of the previous nights (prior to log move) backup of MASTER fixed the problem.
Any ideas?
LVL 29
QPRAsked:
Who is Participating?
 
MikeWalshCommented:
ahh I missed the 6 logs part.. you won't find any other logs on the hard drive in that case...

Run a DBCC CHECKDB on all databases, restart the server a couple times, have all of the apps that have DBs on that server tested, and hopefully you shold be fine.

In all seriousness, you really need to have some discussions with your admin folks and or their management. SQL needs to be locked down.. A lot of companies think "SQL Server is simple, we can mess around without asking the DBA first".. That just causes trouble..

Especially playing around with the physical disks ldf or mdf files exist on.. That is scary without DBA oversight (proper backup taken first, defragmentation done before putting files on the drives, checking for index fragmentation afterwards, ensuring proper disk space, ensuring the server settings aren't changed during the process, having a rollback plan.. Etc. Etc.. Us DBAs are supposed to be a big pain in the behind to any change in the organization.. We are supposed to be annoying anal-retentive, detail oriented whiny pessimistic worry-warts)
0
 
MikeWalshCommented:
Well anything suggested will be a guess..

First some things to do:

1.) Look at the error logs.
2.) Ask the admin if he did anything else?

My guess is somehow someone changed the server collation for some reason. Restoring master solved that because it was the master before it was touched.

and

3.) tell the admin to elave SQL alone!! ;-)
0
 
QPRAuthor Commented:
1.)Seems normal (6 logs for 6 restarts in 10 minutes!)
Entries such as loading dbs, started in single user mode, 2 cpus detected etc etc but then this one.....
Warning: override, autoexec procedures skipped
?

2.) apparently not
3.) :o)
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.

 
MikeWalshCommented:
What version of SQL are you running (edition and version number)? What OS are you on?

Look through older log entries, do you see that same message? It may not be anything huge, but it is worth looking at.

I think the collation issue was a fluke. I have a feeling someone changed a server option accidentally. Have you successfully stopped and restarted the server after all of this to ensure it comes back up and that everything works?

Are there older logs also? You can also look at the location the logs are saved to and look at the physical files. I am not sure if rebuilding master wipes out the log or not. (I think not, but it may)
0
 
QPRAuthor Commented:
Hmmm something just occured to me.... I wonder if he was logged on as network admin (not a member of SA) when he did his thing to the master DB and therefore dbo was not the owner. Restoring would have fixed that. I'm thinking this because looking, I see that MODEL is owner by domain/administrator rather than dbo
0
 
MikeWalshCommented:
that is interesting. I didn't realize he moved system files around. That is scary, and very possible part of the cause. DBO should still own all of the objects, hopefully. Domain\Administrators is probably in the SA group anyway, though.
0
 
MikeWalshCommented:
If you have backups you can go to for the system DBs, it might be best to restore the backups, and just manually specify the new locations and then go from there.

Basically, we may not be able to know what caused the collation problem, but we can check the consistency of all databases with DBCC CHECKDBs, we can ensure the server runs, we can ensure that the applications work, we can ensure connectivity, and we can watch it carefully..

We can do more research on that strange error, but we may never know exactly what caused the problem.
0
 
MikeWalshCommented:
I am still concerned about that "Warning: override, autoexec procedures skipped".. Have a look at what your startup parameters are, since the system dbs were moved your network admin may have played with startup trace flags.

Enterprise Manager --> Your Server --> Right Click, Properties, Startup Parameters.. Look at the list.. There are some there by default, so don't be scared instantly but tell me what is there.

Also stop and restart the service, and look at the error log again. Is it still there?
0
 
QPRAuthor Commented:
Thanks, the reason why I wondered about the ownership of the master was.....
This 3rd party app was apparently working ok until a user "processed a journal" the app passes this to SQL as a "job" to do (specific category of SQL Agent - Jobs) on the server rather than tie up the client. I'm wondering if this has a hardcoding of owner.master and owner no longer matched *shrugs*
As you say, is all guess work from here on in. I'll have a restart at the weekend.
There is a systemDB maintenance plan running each night which appears to be ok.
It checks integrity of DB and indexes during this.... would this cover DBCC CHECKDBS or should I run this manually?
0
 
MikeWalshCommented:
if it does integrity checks you should be good. look at the log after it runs you will see the checkdb results. You can and should just run it manually to have a looksee. Run it on each database.

Definitely look at the startup properties, and those dbs should be owned by sa normally.. I wouldn't be concerned with who "owns" the Databases. I don't believe that has any major bearing, though it is usually sa when in mixed mode, but domain\admins is never going to not be around, you know?

The concern with ownership would come from who owns the objects. The objects in those databases are probably still all owned by DBO and that is how it should be.
0
 
QPRAuthor Commented:
startup params
-dF:\Microsoft SQL Server\SQLDB\MSSQL\data\master.mdf
-eF:\Microsoft SQL Server\SQLDB\MSSQL\log\ERRORLOG
-lG:\Microsoft SQL Server\SQLTLog\mastlog.ldf

I'll have to wait till after hours to stop/start SQL
0
 
MikeWalshCommented:
ok. Well those are all normal startup paramaters. I think you should be fine now. How are all of the apps running?
0
 
QPRAuthor Commented:
Apps running fine so far - they were before I returned... they restored master and this seemed to do the trick.
I was just curious as to what happened so it could be potential avoided in future!
Thanks for the help
0
 
MikeWalshCommented:
well I think what caused it was someone other than you messing around in the system. :-) Unfortunately we may not ever be able to go back and see.. Just do your DBCC CHECKDBs to make sure and keep them out ;)

good luck
0
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.

All Courses

From novice to tech pro — start learning today.