September 23, 2009 at 10:38 am
Have u come across this situation - model log not available and db not coming up?
If so, how did u resolve it?
The server has 3.7GB of ram and the tempdb is 4.5GB
Thanks
Murali
September 23, 2009 at 10:58 am
Hi,
By seeing this error what i can suggest is to check whether model databaese .ldf file is their or not. Please check it.:-P
September 23, 2009 at 11:09 am
Sorry i did not provide more details.
Yes, that's the first thing i did.
The .ldf file was missing.
I copied the .ldf from another location.
still does not work.
I checked the sql log and have never started sql server with -f option.
Can someone throw more light on it?
September 23, 2009 at 11:30 am
i tried starting sql server manually with -f option: still the same problem.
This is the error:
2009-09-23 10:25:17.08 server Logging SQL Server messages in file 'E:\MSSQL\log\ERRORLOG'.
2009-09-23 10:25:17.09 server Warning: -f command line flag used...minimal server configured.
2009-09-23 10:25:17.09 server SQL Server is starting at priority class 'normal'(8 CPUs detected).
2009-09-23 10:25:17.47 server SQL Server configured for thread mode processing.
2009-09-23 10:25:17.49 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2009-09-23 10:25:17.63 server MS DTC initialization skipped because of startup option.
2009-09-23 10:25:17.63 spid3 Warning ******************
2009-09-23 10:25:17.63 spid3 SQL Server started in single user mode. Updates allowed to system catalogs.
2009-09-23 10:25:17.66 spid3 Starting up database 'master'.
2009-09-23 10:25:17.83 spid3 Server started with '-f'. Auditing will not be started.
2009-09-23 10:25:17.83 server Using 'SSNETLIB.DLL' version '8.0.818'.
2009-09-23 10:25:17.83 spid5 Starting up database 'model'.
2009-09-23 10:25:17.84 spid3 Server name is 'ABCDEF'.
2009-09-23 10:25:17.89 spid5 Analysis of database 'model' (3) is 100% complete (approximately 0 more seconds)
2009-09-23 10:25:17.97 server SQL server listening on 192.168.199.21: 1433.
2009-09-23 10:25:17.97 server SQL server listening on 127.0.0.1: 1433.
2009-09-23 10:25:18.02 spid5 LogMgr::FixupLogTail (): alignBuf 0x120cf000, writeSize 0xf000, filePos 0x83900800
2009-09-23 10:25:18.02 spid5 blankSize 0x1e000, blkOffset 0x36304, fileSeqNo 1346, totBytesWritten 0x0
2009-09-23 10:25:18.03 spid5 fcb status 0x3100142, handle 0x498, size 5532808 pages
2009-09-23 10:25:18.05 spid5 FixupTail: Operating system error 33(The process cannot access the file because another process has locked a ortion of the fil
e.) encountered.
2009-09-23 10:25:18.17 spid5 LogMgr::FixupLogTail (): alignBuf 0x120cf000, writeSize 0xf000, filePos 0x8390f800
2009-09-23 10:25:18.17 spid5 blankSize 0x1e000, blkOffset 0x36304, fileSeqNo 1346, totBytesWritten 0xf000
2009-09-23 10:25:18.19 spid5 fcb status 0x3100142, handle 0x498, size 5532808 pages
2009-09-23 10:25:18.20 spid5 FixupTail: Operating system error 33(The process cannot access the file because another process has locked a ortion of the file.) encountered.
2009-09-23 10:25:18.25 server SQL server listening on TCP, Shared Memory, Named Pipes.
2009-09-23 10:25:18.27 server SQL Server is ready for client connections
2009-09-23 10:25:18.34 spid2 LogWriter: Operating system error 33(The process cannot access the file because another process has locked a portion of the file.) encountered.
2009-09-23 10:25:18.34 spid2 Write error during log flush. Shutting down server
2009-09-23 10:25:18.36 spid5 Error: 9001, Severity: 21, State: 4
2009-09-23 10:25:18.36 spid5 The log for database 'model' is not available..
2009-09-23 10:25:18.38 spid5 Clearing tempdb database.
2009-09-23 10:25:18.66 spid5 Error: 9001, Severity: 21, State: 1
2009-09-23 10:25:18.66 spid5 The log for database 'model' is not available..
2009-09-23 10:25:18.69 spid5 WARNING: problem activating all tempdb files.
See previous errors. Restart server with -f to correct the situation.
September 23, 2009 at 11:44 am
1. Stop your SQL server if it is runnig
2. Run command prompt (click start then run, type cmd then click OK)
3. Type next command, it runs your SQL server in minimal configuration c:\path_to_the_server_executable\sqlservr.exe -f
DO NOT CLOSE THAT WINDOW !!!
4. Run another command prompt
5. Use osql utility to attach databases ( location of that utility is based on instalation, standard is C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ )
osql -S server_name -U sa -Q "sp_attach_db 'model', 'c:\path_to_model_mdf_file\model.mdf', 'c:\path_to_model_ldf_file\modellog.ldf'"
it asks you for password for user 'sa' if you are using thrusted connections, than use
osql -S server_name -E -Q "sp_attach_db 'model', 'c:\path_to_model_mdf_file\model.mdf', 'c:\path_to_model_ldf_file\modellog.ldf'"
6. Switch to window with SQL server running ( that from step 3 )
7. Press CTRL + C or CTRL + BREAK, it asks you to stop the server, type 'Y'
8. Start your SQL server by service manager or by Enterprise Manager or by your favourite way
10. Do not forget to remove -T3608 startup parameter by Enterprise Manager, if it is still there
11. Close that 2 command prompt windows
September 23, 2009 at 11:45 am
Looks like some process has the .ldf of the model database locked. I would run procexp.exe (from Microsoft) to determine what process is locking the file, then kill that process.
September 23, 2009 at 11:50 am
Looks like some process has the .ldf of the model database locked.
It could be not a SQL Server process, but OS process as a result of previous attempts to fix a problem.
September 23, 2009 at 11:55 am
certainly a possibility.
September 23, 2009 at 12:13 pm
1. I ran procexp and did not see anything impacting the system.
2. I ran sql server in -f option. Still failing.
3. On another window i tried to attach the model db and it gave the message
[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).
( my guess is this is because sql server is down)
Starting up is not possible.
2 questions.
1. I have a model db backup as of 10 to 15 days back. Will restoring db from command line help?
2. How to forcibly start sql server even if model db log is not available?
Thanks
murali
September 23, 2009 at 12:24 pm
Just to be clear- you should execute proc exp, do Find > Find Handle and enter "modellog.ldf" to ensure that some process is not locking the file.
September 23, 2009 at 12:28 pm
Can we attach only .MDF file through cmd mode.
September 23, 2009 at 12:30 pm
1. I have a model db backup as of 10 to 15 days back. Will restoring db from command line help?
You are on SS2000.:(
The model and msdb databases can only be restored from backups that are created on a Microsoft SQL Server 2005 server. Restoring backups of these databases made on Microsoft SQL Server 2000 or earlier versions is not supported.
September 23, 2009 at 12:32 pm
good catch.
I am trying to attach the model db from E drive before and it failed.
I did found that the modellog.ldf system handle is pointing to D drive.
How do i get rid of this before proceeding?
September 23, 2009 at 12:48 pm
vsmurali111 (9/23/2009)
good catch.I am trying to attach the model db from E drive before and it failed.
I did found that the modellog.ldf system handle is pointing to D drive.
How do i get rid of this before proceeding?
Do you have the SS2000 installation disk?
If yes, try to copy the modellog.ldf file from there to the location where you have your model.mdf file.
Then remove ReadOnly attribute from this file.
Then try to restart SQL Server in a normal mode.
Please update us after all.
September 23, 2009 at 1:13 pm
Good news.
After the server was restarted, I was able to start sql server normally.
I checked the model files and they were not set to READ only.
I left it as it is.
I have been successful in logging to db thru query analyzer.
Ran a select * from sysdatabases and got the results back.
But the EM seems to hang (it has lot of apps and only 4GB of ram, may be that's why).
I will consider the issue is closed and will address rest of things separately.
Appreciate all of you guys for jumping in.
Thanks much.
Murali
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply