January 6, 2014 at 11:55 am
Hi All,
in our production boxes , one of the sqlserver instance has been running without having MODEL Database. I was surprised that, we have reboot the instance
several time , but did not find any reboot issues. as per the risk management team , now we are looking to take the backup of model database. but it is not available in system databases.
model database not listed in sp_helpdb, select *from sysdatabase and select *from msater_files.
sqlserver details are verions is 2005 with sp4 and enterprise edtion.
can some help to bring the model database in to online.
Regards,
Shenkar Gade
January 7, 2014 at 3:32 am
Can you SELECT * FROM sysdatabases and see if you have a database with an ID of 2. If you have, this DB is being used as the model database.
There are a number of reasons why the DB with Id of 2 is not called Model, and all of them revolve around someone messing about with the system databases and getting things wrong.
a) Someone has managed to rename model to a different name
b) Someone has tried to move model to another location and ended up getting a different database registered with Id of 2 (most likely this is msdb)
c) Someone thought it would be 'helpful' to change model into a normal database
When you have worked out what the problem is, the next stage is to fix it. My personal preference would be to build a new server then migrate the user databases and logins to the new server and decommission the old server.
If you want to try to fix the old server, then my advice is to set up a similar situation on a spare server and try to fix it. When you know what will work then apply the same change to the live server.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 7, 2014 at 6:10 am
Also try and search for .mdf and .ldf files on your server and see if there are model.mdf and ldf on the server. Also, the system database files are on the install CD.
It is very odd that SQL Server starts and runs without a model db. Have you tried creating a new empty db? If it creates OK there is a model db according to SQL Server. If this is a live production SQL Server messing with MODEL db could have big impacts on it. Tread lightly.
January 7, 2014 at 6:51 am
EdVassie (1/7/2014)
Can you SELECT * FROM sysdatabases and see if you have a database with an ID of 2. If you have, this DB is being used as the model database.
Ed, I think you meant ID = 3, ID 2 is tempdb
I cannot see how SQL would run without a model database, what does the following query return?
select DB_NAME(database_id) as dbname, database_id,type_desc,name,physical_name from master.sys.master_files
---------------------------------------------------------------------
January 7, 2014 at 7:06 am
Can you post (as an attachment) one of the error logs for the server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2014 at 7:25 am
Sorry, I did mean Id = 3 !!!
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 7, 2014 at 7:29 am
I've noticed that in your query you wrote the name Model. If the server is case sensitive, you won't find the model database if you'll use a capital later.
Also I'd be very surprise if the server can boot without the model database. As far as I know it needs it to create the tempdb and without the creating it, it can't start in a normal way (meaning that no user database is accessible).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 7, 2014 at 8:24 am
Hi ,
i have verified that, server has build on feb 2009 with sp2 and after sp4 we have applied sp4 recently. model database datafile and log file are existing in root path and also all system databases are existing in same path. model database last updated date is showing as feb 2009 only .. in query result master =1 ,tempdb=2 and msdb=3 showing. we have information like no one has renamed the database.
Regards,
Shenkar Gade
January 7, 2014 at 8:42 am
we cannot see what you can see so difficult to troubleshoot, please post result of query above and portion of errorlog showing start up.
---------------------------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply