Created database but used Model

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Created database but used Model

Created database but used Model

Rate Topic

Display Mode

Topic Options

Author

Message

Kricky

Posted Friday, December 17, 2010 9:59 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 06, 2013 3:52 PM
Points: 23, Visits: 70

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.

Post #1036629

Adiga

Posted Friday, December 17, 2010 12:19 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:50 AM
Points: 1,618, Visits: 20,898

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

Post #1036689

Kricky

Posted Friday, December 17, 2010 12:23 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 06, 2013 3:52 PM
Points: 23, Visits: 70

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

Post #1036692

Lowell

Posted Friday, December 17, 2010 12:47 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:55 AM
Points: 11,605, Visits: 27,643

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1036710

« Prev Topic | Next Topic »

Permissions