June 29, 2013 at 3:41 pm
Hi all,
I have a big problem (it's two actually).
I wanted to move system databases back to C: drive (they were installed to a different drive), so I shut down SQL server and copied sys DBs to another drive. (MSSQL 2008R2 Enterprise x64, Win Server 2008R2 x64)
I modified the startup command for new master location and admin mode:
-dc:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-c;-m;-T3608
I started SQL service, but it didn't start and cannot open tempdb files (read in Win event log).
I had 4 fixed size tempdb files on a dedicated T: partition (4 GB each).
I thought I remove 2 piece to free up some space, so I made this in console:
(c:\Program Files\Microsoft SQL Server\100\Tools\Binn>SQLCMD.EXE" -S localhost -E)
ALTER DATABASE [tempdb] REMOVE FILE [tempdev4]
ALTER DATABASE [tempdb] REMOVE FILE [tempdev3]
Then I deleted the 2 useless files in Windows.
After this I started up SQL service (-T3609 helped to resolve tempdb access problem)
SQLSERVR.EXE -m -c -T3608 -T3609
... and tried to attach model DB:
sp_attach_db 'model','c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf','c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf'
..and tempDB was starting... and tried to reach tempdev3 and tempdev4!!
But those are already deleted!
I think it's a "deadlock" situation. "model" DB needs "tempDB", "tempDB" needs "model" DB...
It would be very important to fix this, it's a production server.
What should I do?
Thanks in advance!
Balint
June 29, 2013 at 4:37 pm
https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/
Not the sections on model and tempDB, you've messed that up beyond repair, the section on master, on rebuilding the system databases.
Rebuild them, then restore backups of master and msdb, then if necessary try again to move the system database the documented way (it's in BoL) after testing in a non-production environment.
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
June 29, 2013 at 5:10 pm
Thanks for the help!
I read it and try to fix it.
June 29, 2013 at 6:39 pm
I ran the rebuild...
[font="Courier New"]Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=MYDOMAIN\srvc_sqlservice[/font]
and got this error below.
Could you give some advice about this? Thank you.
[font="Courier New"]
SQL Server 2008 R2 Setup has stopped working
Description:
SQL Server 2008 R2 Setup has encountered an error.
Problem signature:
Problem Event Name:SQL100Exception
Problem Signature 01:SQL Server 2008 R2@RTM@
Problem Signature 02:0x8E91D6AA
Problem Signature 03:0x8E91D6AA
Problem Signature 04:0x97A656BB@1306@17
Problem Signature 05:0x97A656BB@1306@17
Problem Signature 06:SqlEngineConfigAction_rebuilddatabase_validation
Problem Signature 07:0x085CD773
Problem Signature 08:Unknown
Problem Signature 09:Unknown
Problem Signature 10:Unknown
OS Version:6.0.6002.2.2.0.272.7
Locale ID:1033
Additional information about the problem:
LCID:1033
Read our privacy statement:
http://go.microsoft.com/fwlink/?linkid=50163&clcid=0x0409
[/font]
June 30, 2013 at 3:55 am
There's nothing there of any use. Are there any logs anywhere with more details?
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
June 30, 2013 at 4:38 am
I have so big luck, I can not belive it 🙂
I found an earlier set of copy of system db files ( I made it earlier, but forgot about them).
I copied them back, the service started up and created the missing tempdev files also.
Anyway, thank you for your help, I learned a lot 🙂
June 30, 2013 at 6:26 am
Hopefully including:
Test all changes on a non-production system
Don't delete database files.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply