Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Created database but used Model Expand / Collapse
Author
Message
Posted Friday, December 17, 2010 9:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 7:20 AM
Points: 30, Visits: 90
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
Posted Friday, December 17, 2010 12:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Posted Friday, December 17, 2010 12:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 7:20 AM
Points: 30, Visits: 90
When I ran that query, I didn't get any results returned, so I will just let it finish. Thanks Adiga.
Post #1036692
Posted Friday, December 17, 2010 12:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:03 PM
Points: 12,963, Visits: 32,512
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 »

Add to briefcase

Permissions Expand / Collapse