SQL Sam and the Phantom Connection - Part 1 (with clues)

Tuesday Aug 24th 1999 by Steve Hontz
Share:

I'm having a problem loading the database dump on the test machine.

Environment: SQL Server 6.5, SP5a
Turn OFF clues

SQL Sam stood in front of the screaming crowd, with the game-show host urging him on. "Now Sam", he said, "You have to make a choice. Will it be Door Number 1, Door Number 2, or Door Number 3? Remember, behind one door is an incredible prize. Behind another door is a tiger. What's your choice?"

"What's behind the door marked 'Null' ?" asked Sam.

"Who knows???" shrugged the host with the brillant-white teeth. "What's your decision?"

The crowd screamed louder. "Pick number 1! ... Pick 2! ... NOT Null! ..." SQL Sam started to sweat. "I pick...uhhh...Number...uhhh..."


"...Sam...Sam! Excuse me, Sam! Please wake up!" Jimmy Jerry, the junior DBA with two first names, was gently shaking SQL Sam.

"Oh. Sorry, J.J. Must of dozed off," yawned Sam. "I was up all night helping to repair the production server. What's up?"

"Well, Sam, I'm trying to be a good DBA and make sure that our databases aren't corrupt by running consistency checks against them. Trouble is, we can't run the checks on the production servers- they cause too much blocking for too long. So, I thought I could restore the databases from the last database dump from the production server to my test machine, and do the checks there. But I'm having a problem loading the database dump on the test machine."

"Did you create the database with the exact same data and log device layout, size and order as the production server?"

"Oh, sure," grinned Jimmy. "I remember you telling us in DBA training how important that was. So I went to www.swynk.com and found a great script for reverse-engineering the database devices that make up a database. That's how I created the database on the test server. But the problem I'm having happens when I try to create a A CLUE!scheduled task to load the database."

Continue with SQL Sam and the Phantom Connection

Go to the solution now!

Back to SQL Sam Cases


Steve Hontz is President of The Bit Corner, Inc., a Phoenix-based consulting firm specializing in Windows NT, C++, and SQL Server development. You can reach him at steve@bitcorner.com.


SQL Sam and the Phantom Connection - Part 2

Environment: SQL Server 6.5, SP5a
Turn OFF clues

"What does your scheduled task look like?" asked Sam. "And what is the failure message?"

"Well, the TSQL command I'm running is...

load database testDb from disk='f:\mssql\backup\weeklyDump.dat'

...and the failure message I'm getting is ' A CLUE! Database in use. System Administrator must have exclusive use of database to run load. (Message 3101)' But there can't be anyone in the database; I'm the only user on the system."

"Hmmm..." mulled Sam. "Have you tried running sp_who or sp_who2 to see if there are users in your database? You might be surprised...it might be you, if you have any query windows open on that database."

"Sql Sam, you're the greatest!" exclaimed Jimmy. "I'm sure that's it!" Jimmy scurried off to make sure he wasn't connected to the database.

A little while later, Jimmy came back, his shoulders slumped. "Well, that seemed like the problem... I had two query connections open. But I closed those, and I still get the same failure. I even ran sp_who before and after I ran my task, and there wasn't anyone in the test database. All my connections are connected to master. There must be A CLUE! some sort of phantom connection that I can't see."

"Let's look at your scheduled task, Jimmy," said Sam. They opened the task up in the SQL Enterprise Manager:

"Ah-ha!" said Sam. "Not all of your connections are to master!"

What did SQL Sam see? Go to the solution now!

Review SQL Sam and the Phantom Connection - Part 1

Back to SQL Sam Cases


Steve Hontz is President of The Bit Corner, Inc., a Phoenix-based consulting firm specializing in Windows NT, C++, and SQL Server development. You can reach him at steve@bitcorner.com.


SQL Sam and the Phantom Connection - Solution!

"What do you mean, Sam?" Jimmy asked. "I don't see any connections..."

"Don't forget, Jimmy, that when you run a scheduled task, the SQL Executive Service makes a connection into SQL Server. And it has to establish a connection to a particular database."

"How does it decide what database to connect to?" asked Jimmy.

"Keep looking," smiled Sam. "You'll see it..."

"Oh!" Jimmy exclaimed. "It's right there, in the task options: Database testDb! I put TestDb as the database name because I figured that was the database where the work should be done."

"Well, that does make sense," admitted Sam. "But in this case, it keeps you from loading the database properly. You'll want to have the connection log into a different database."

Jimmy changed the database the scheduled task used to master, like this:

This time, when Jimmy ran the task, it worked. Jimmy was well on his way to creating a weekly scheduled process to check his databases by using an offline server. SQL Sam cautioned Jimmy that sometimes there are true phantom connections. These seem to be left behind from disconnected applications, and they won't show up in sp_who. They do have an annoying tendency to keep the sa from doing operations that require exclusive database access (like a database load). So far, the only solution to that problem seems to be to stop and restart SQL Server.

Go read more exciting SQL Sam Cases!

See the story behind the story in Behind the SQL: The Making of SQL Sam!


Steve Hontz is President of The Bit Corner, Inc., a Phoenix-based consulting firm specializing in Windows NT, C++, and SQL Server development. You can reach him at steve@bitcorner.com.


Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved