[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

What would cause this problem?

Posted on 2006-05-31
14
Medium Priority
?
195 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:QPR
  • 9
  • 5
14 Comments
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16803288
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
 
LVL 29

Author Comment

by:QPR
ID: 16803553
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16803595
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
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.

 
LVL 29

Author Comment

by:QPR
ID: 16803600
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
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 2000 total points
ID: 16803604
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16803609
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16803637
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16803666
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
 
LVL 29

Author Comment

by:QPR
ID: 16803684
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16803704
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
 
LVL 29

Author Comment

by:QPR
ID: 16803709
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16803727
ok. Well those are all normal startup paramaters. I think you should be fine now. How are all of the apps running?
0
 
LVL 29

Author Comment

by:QPR
ID: 16803743
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16803757
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

873 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