June 27, 2008 at 9:27 am
Hello All,
I just got assigned to a database server this week as we had a DBA leave the company.
I have been tasked to create a new database on the above server (SQL Server 2000 8.00.2039 SP4).
When I try to create the database in either Enterprise Manager or Query Analyzer I get the following error message:
The name model was not found in the databases collection
I click ok to the above message and I get the fields to create a database like I normally would.
I enter the new database name (again I'm using EM) and when I click ok to create the database I get this error message:
error 3501 could not find row in sysdtabases for database id 3 at checkpoint time
I click ok and no database gets created.
I did notice the model database is missing.
I have a backup of the model db from a week ago but have been unable to restore or attach it (assuming that's what causing the error messages above).
Any advice?
I'm on a tight deadline to create this database as the project it's for cannot move forward until it's completed.
Thanks in advance for any assistance!
June 27, 2008 at 9:47 am
Never seen this! You can't actually delete the model database from the management studio or execute drop database against it.
How is the service even running as well?
Anyways, I just took a copy of the model database from another instance and placed it in the same folder as the master and msdb databases and I started the service no problem. Checked the logs and it was all clear.
I'd recommend trying that.
June 27, 2008 at 11:43 am
Clive Strong (6/27/2008)
Never seen this! You can't actually delete the model database from the management studio or execute drop database against it.
Odd because the model db is not listed in EM at all.
I've tried to restore the backup copy I have and that has failed.
Keep in mind I have no experience restoring system dbs (this is my first time).
When you say you copied it over from another server Clive did you mean that you took a .bak file of any model db and restored it onto the new server?
Could I detach the files from another server, copy them and transfer them to the box I'm having trouble with and reattach them?
Thanks for your advice and help!
~Lost in N.J.
June 27, 2008 at 1:30 pm
this is weird. Did the DBA leave under a cloud because he may have left you a leaving present and possibly hacked the sysdatabases table. Or maybe he saw this problem and decided to leave!
Are the physical model .mdf and .ldf present in the data directory where the other system databases are held?
what error do you get when trying to restore model? I presume it is failing because first thing it would do is create a database shell, which it needs model for!
your problem is the instance may be functioning now (up to a point) but if its stopped it likely won't start again.
if model files are present
check out the sysdatabases table in master, see if there is an entry for model (dbid = 3)
run a checkdb on master see if there any corruptions
if there are see if they are fixable
if unfixable corruptions do you have a backup of master you can restore, hopefully this will have an intact copy of sysdatabases.
hacking sysdatabases is possible but won't be easy, and even if you get it working I recommend restoring from a clean master backup if you have one after hacking
be careful because restoring master means restarting instance in single user mode, so check up on that and starting without a model db (trace flag 3608 I think). Also check up on rebuildm utility, you may have to run that to get a clean master database and your system db files back.
If model files are not present you can try copying them from another instance as long as it is exactly the same version (ie 2039). Service will need restarting to pick them up though.
---------------------------------------------------------------------
June 27, 2008 at 1:48 pm
Hi John,
I copied the files.
Assume instance A has no model database. I stop services on instance A & B. Copy the model data & log file from the intance B folder to the instance A folder. Restart both instances.
In my test, they both started with no errors.
Hope that helps
June 30, 2008 at 9:21 am
Did the DBA leave under a cloud because he may have left you a leaving present and possibly hacked the sysdatabases table.
It's possible as I created a database on that server two days before he left without any issues.
Are the physical model .mdf and .ldf present in the data directory where the other system databases are held?
Yes.
if model files are present
check out the sysdatabases table in master, see if there is an entry for model (dbid = 3)
There is no entry for the model database in the sysdatabases table.
run a checkdb on master see if there any corruptions
Ran with no corruption found.
hacking sysdatabases is possible but won't be easy, and even if you get it working I recommend restoring from a clean master backup if you have one after hacking
Is there no way to add the info for the model database into the sysdatabases table?
If model files are not present you can try copying them from another instance as long as it is exactly the same version (ie 2039). Service will need restarting to pick them up though.
Based on what you said earlier I'm afraid to stop the services for fear they won't start up!
Same thing with Clive's advice- it requires stopping/starting the services.
NOTE: I realized that I posted this in the wrong forum.
The system is actually a cluster (with 15 drives assigned to it so I'm quite intimidated by it as I've never worked on anything so complex before!).
It is also running SQL Server 2000.
Sorry if that changes the landscape.
Regardless it's an important production system that would probably require all types of approvals to be taken offline esp. for me to hope I can fix it.
Any other advice that may help me?
Thanks for the help so far Clive and George!
June 30, 2008 at 9:27 am
Hi John,
If your worried about databases not starting up, take a backup of the master and msdb databases.
If they don't start, you have a backup from which to restore to a new instance. The user databases can be copied to the new instance and attached to save time on backing them up.
As I can't reproduce the problem, I can suggest a hack. I would have thought copying the model from another instance and then adding a row to sysdatabases might work, but in all honesty I would try and avoid hacking the system databases.
June 30, 2008 at 9:34 am
Have you tried attaching the existing model.mdf and model.ldf files since you say the exist in the data directory with the other system database files.
😎
June 30, 2008 at 10:34 am
I'm torn here because I don't want to drop you in the proverbial.
the CORRECT way to fix this would be to stop the instance, restart in single user mode and restore a correct copy of the master database. See http://support.microsoft.com/kb/224071 for instructons on how to do this and start with only master database recovered. Basically cmd
net start mssqlserver -c -m -T3608, then logon and restore your master database from a good backup. Service will then stop and you can restart it as normal.
System table hack is tempting here though because its a simple fix and sysdatabases has no relationships with any other system tables.
Either way you need to be sure you backup of master is clean, so if you have such a backup MAKE SURE YOU KEEP IT SOMEWHERE SAFE. (for ever). If you are able to copy the model files off somewhere safe as well, do that, but you might get a file in use message.
To ensure your backup is clean, Restore your master backup as a user database, i.e. a db of another name
restore database master_orig from disk = 'master backup' with move.....
check sysdatabases in this database see if model is present.
NOW BACKUP master and msdb
If you choose to try a direct update of systemdatabases you can now insert from master_orig.sysdatabses into master.sysdatabases where name = model. (thats not the SQL!)
before insert:
sp_configure 'allow updates',1
go
reconfigure with override
go
after insert
sp_configure 'allow updates',0
go
reconfigure with override
go
If you have no clean master backup you can insert a row for model taking values from any other master database, altering server specific values to match those for the master row, else you are into scriping out your logins with sp_help_revlogin and running rebuildm.
I don't normally advocate hacking system table so here is a DISCLAIMER: I take no responsibility for your system.
Who knows, other tables might have had rows deleted.
---------------------------------------------------------------------
June 30, 2008 at 11:30 am
Maybe your system Databases are hidden...
ChecK--> right click SERVERNAME--> Edit SQL Server Registration Properties. anc check to see if the "Show system Databases and system Object" is checked. if not then check it.
Maninder
www.dbanation.com
June 30, 2008 at 11:43 am
Here is another trick:
Run this Query and Post the results:
select name,dbid from sysdatabases
where dbid<=4
and verify to see the DBID of Model Database, i bet it will not be 3, so something else is using dbid as 3. (My guess)
If the DBID's are correct.. Well then there is another issue here..
Right Click on SQLServer and click Properties and Goto Startup parameters. and include the -T3608 in the startup options..Stop and Restart the SQL Server.
RUN this Script
sp_detach_db 'msdb'
sp_attach_db 'model',
'C:\path\Data\model.mdf',
'C:\path\Data\modellog.ldf'
sp_attach_db 'msdb',
'C:\Path\Data\msdbdata.mdf',
'C:\Path\Data\msdblog.ldf'
Now Run this again to verify...
select name,dbid from sysdatabases
where dbid<=4
Maninder
www.dbanation.com
June 30, 2008 at 12:38 pm
Lynn Pettis (6/30/2008)
Have you tried attaching the existing model.mdf and model.ldf files since you say the exist in the data directory with the other system database files.😎
Yeah with no success. Here is the error message I receive:
Error 5123: CREATE FILE encountered operating system error 32 (The process cannot access
the file because it is being used by another process) while attempting to open or create
the physical file 'G:\MSSQL\Data\model.mdf'.
June 30, 2008 at 12:50 pm
Mani,
The system databases are not hidden as I checked following your instructions.
I can see the master, msdb and temp dbs listed in Enterprise Manager.
I ran your query:
select name,dbid from sysdatabases
where dbid<=4
and got the following results:
namedbid
master 1
tempdb 2
msdb 4
(3 row(s) affected)
This is a production box with 25 user databases that as of now I can't take offline.
I'm losing hair by the minute here!
And I have to say thanks again for the responses- you've all been great!
June 30, 2008 at 1:38 pm
ok atleast now we know what the issue is..
you Just have to perform the above Query once you have some time for outage.... the Model Db is not attached...
Maninder
www.dbanation.com
June 30, 2008 at 2:09 pm
Thanks Mani but let me ask you this- is an outage the only way to reattach the model db?
How did it get deattached? Would the services have to be stopped for that also?
I'm sorry but I've been a junior DBA for some time and got thrust into this advanced system and am nervous about breaking anything.
Thanks again!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply