Created database but used Model

Question

Created database but used Model

Kricky

SSC Veteran

Points: 205
More actions
December 17, 2010 at 9:59 am

#230891

Hi all - I needed to create a database, but somehow my dropdown for the database that I was in in SSMS, had been selected as "model". I came back to see that the Create Database statement was still running long after I had started it. I have now killed that process, but it is still cancelling 12 hours later. What is the best solution on this? I am guessing that since the Model db is open, I cannot create any other databases until this query is stopped. If I stop and start SQL, I am concerned that my model db will come back as suspect. I am not an expert db administrator, and we don't really have one. Hard to search the web for this topic.
Thanks.
Kris Robinett

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

When the create database statement is running, it requires exclusive access to model database. Now that you have killed the sessions, do you see any other session blocking it?

select * from sys.dm_exec_requests where blocking_session_id<>0

If that query is not causing any performance issues, you can wait for it to complete. Restarting the SQL Server is also an option but I don't think it will cause the MODEL database to go suspect.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Kricky SSC Veteran Points: 205 More actions · Answer 2

When I ran that query, I didn't get any results returned, so I will just let it finish. Thanks Adiga.

Kris Robinett

Lowell SSC Guru Points: 323504 More actions · Answer 3

Kricky (12/17/2010)
When I ran that query, I didn't get any results returned, so I will just let it finish. Thanks Adiga.

kill it! kill it with fire!

it doesn't matter what database scope you are currently in, a new database is always a copy of the model database;

it should only take a second or two to complete, unless you make a mistake like alloting 1000 gigs instead of 10 meg or something...

regardless, if it's been goign 12 hours, I'd kill it and start fresh.

nothing good will come from a sql that has been that long running.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!