August 8, 2008 at 7:50 am
Yesterday when we had errors about insufficient disk space
for the Log file I decided to move LOG to another drive - H:\
I ran this command:
ALTER DATABASE BAP_PROD_2006
MODIFY FILE
(
NAME = BAP_PROD_2006_Log,
FILENAME = 'H:\Microsoft SQL Server\MSSQL\Data\FG_Log.ndf',
SIZE = 5MB,
MAXSIZE = 9MB,
FILEGROWTH = 1MB
)
The message said the new location will be effective after
BAP_PROD_2006 will be restarted.
At 22:51 yesterday Windows server was restarted.
Here is the SQL Server errorlog:
2008-08-07 22:52:14.75 spid19s Starting up database 'msdb'.
2008-08-07 22:52:14.75 spid20s Starting up database 'COMMERCIAL_NET_NEWS_DB'.
2008-08-07 22:52:14.75 spid22s Starting up database 'ASPCAL'.
2008-08-07 22:52:14.75 spid21s Starting up database 'MASTER_RELATIONSHIP'.
2008-08-07 22:52:14.75 spid18s Starting up database 'BUS_OBJ'.
2008-08-07 22:52:14.75 spid23s Starting up database 'APA'.
2008-08-07 22:52:14.75 spid24s Starting up database 'ASPSurvey'.
2008-08-07 22:52:14.75 spid25s Starting up database 'CRN_AUDIT'.
2008-08-07 22:52:14.75 spid26s Starting up database 'CRN'.
2008-08-07 22:52:14.77 spid27s Starting up database 'BAP_PROD'.
2008-08-07 22:52:14.77 spid28s Starting up database 'GOSL'.
2008-08-07 22:52:14.77 spid29s Starting up database 'GOSLDW'.
2008-08-07 22:52:14.77 spid30s Starting up database 'BAP_PROD_2006'.
2008-08-07 22:52:14.77 spid31s Starting up database 'WCRN8'.
2008-08-07 22:52:14.77 spid32s Starting up database 'AdventureWorksDW'.
2008-08-07 22:52:14.77 spid33s Starting up database 'AdventureWorks'.
2008-08-07 22:52:16.05 spid30s Error: 17207, Severity: 16, State: 1.
2008-08-07 22:52:16.05 spid30s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'H:\Microsoft SQL Server\MSSQL\Data\FG_Log.ndf'. Diagnose and correct the operating system error, and retry the operation.
2008-08-07 22:52:16.18 spid30s File activation failure. The physical file name "H:\Microsoft SQL Server\MSSQL\Data\FG_Log.ndf" may be incorrect.
2008-08-07 22:52:16.18 spid30s The log cannot be rebuilt because the database was not cleanly shut down.
2008-08-07 22:52:16.25 spid28s Starting up database 'GSMAP'.
2008-08-07 22:52:16.36 spid30s Starting up database 'TutorialNSMain'.
2008-08-07 22:52:16.43 spid24s Starting up database 'TutorialWeather'.
2008-08-07 22:52:16.58 spid25s Starting up database 'C8'.
2008-08-07 22:52:16.61 spid33s Starting up database 'BAP_PROD_2007'.
2008-08-07 22:52:16.61 spid32s Starting up database 'STAGING'.
Now in Management Studio BAP_PROD_2006 is empty.
No tables, nothing.
There is no a plus icon you can click on to callapse the content.
Any suggestions?
August 8, 2008 at 7:55 am
Did you move the ldf file? Alter database just changes the metadata, it doesn't move the physical file. You have to do that yourself.
Shut SQL down, find the log file (it should be in its old location), move it to where SQL's expecting it and restart SQL.
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
August 8, 2008 at 7:57 am
I'll try this now.
Thank you.
August 8, 2008 at 8:09 am
Here is what I did.
I shut down the service.
I moved "BAP_PROD_2006_Log.LDF"
to
H:\Microsoft SQL Server\MSSQL\Data
and renamed it to
"FG_Lof.ndf"
The name is wrong but it's probably too late to change it now.
This was the name of the file I was using when ran
ALTER DATABASE command.
Now SQL Service cannot start:
The SQL Server (EPP_DM) service terminated with service-specific error 10048.
August 8, 2008 at 8:16 am
I looked at the EERORLOG and there is a whole bunch of network issues.
2008-08-08 10:05:17.72 Server (c) 2005 Microsoft Corporation.
2008-08-08 10:05:17.72 Server All rights reserved.
2008-08-08 10:05:17.72 Server Server process ID is 4700.
2008-08-08 10:05:17.72 Server Authentication mode is MIXED.
2008-08-08 10:05:17.72 Server Logging SQL Server messages in file 'F:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2008-08-08 10:05:17.72 Server This instance of SQL Server last reported using a process ID of 1236 at 8/8/2008 10:02:10 AM (local) 8/8/2008 2:02:10 PM (UTC). This is an informational message only; no user action is required.
2008-08-08 10:05:17.72 Server Registry startup parameters:
2008-08-08 10:05:17.72 Server -d F:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2008-08-08 10:05:17.72 Server -e F:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2008-08-08 10:05:17.72 Server -l F:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2008-08-08 10:05:17.73 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2008-08-08 10:05:17.73 Server Detected 4 CPUs. This is an informational message; no user action is required.
2008-08-08 10:05:17.97 Server Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
2008-08-08 10:05:18.36 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2008-08-08 10:05:18.54 Server Could not query the FIPS compliance mode flag from registry. Error 2(The system cannot find the file specified.).
2008-08-08 10:05:19.15 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2008-08-08 10:05:19.15 Server The time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs.
2008-08-08 10:05:19.56 Server The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
2008-08-08 10:05:19.61 Server Database mirroring has been enabled on this instance of SQL Server.
2008-08-08 10:05:19.62 spid5s Starting up database 'master'.
2008-08-08 10:05:19.75 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2008-08-08 10:05:19.86 spid5s CHECKDB for database 'master' finished without errors on 2008-08-03 01:00:23.283 (local time). This is an informational message only; no user action is required.
2008-08-08 10:05:19.87 spid5s SQL Trace ID 1 was started by login "sa".
2008-08-08 10:05:19.90 spid5s Starting up database 'mssqlsystemresource'.
2008-08-08 10:05:19.94 spid5s The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2008-08-08 10:05:20.25 spid5s Server name is 'TORFDMART1UNT\EPP_DM'. This is an informational message only. No user action is required.
2008-08-08 10:05:20.25 spid9s Starting up database 'model'.
2008-08-08 10:05:20.48 spid9s Clearing tempdb database.
2008-08-08 10:05:20.86 spid9s Starting up database 'tempdb'.
2008-08-08 10:05:20.90 spid12s The Service Broker protocol transport is disabled or not configured.
2008-08-08 10:05:20.90 spid12s The Database Mirroring protocol transport is disabled or not configured.
2008-08-08 10:05:20.92 spid12s Service Broker manager has started.
2008-08-08 10:05:21.51 Server A self-generated certificate was successfully loaded for encryption.
2008-08-08 10:05:21.53 Server Error: 26023, Severity: 16, State: 1.
2008-08-08 10:05:21.53 Server Server TCP provider failed to listen on [ 'any' 2987]. Tcp port is already in use.
2008-08-08 10:05:21.54 Server Error: 17182, Severity: 16, State: 1.
2008-08-08 10:05:21.54 Server TDSSNIClient initialization failed with error 0x2740, status code 0xa.
2008-08-08 10:05:21.56 Server Error: 17182, Severity: 16, State: 1.
2008-08-08 10:05:21.56 Server TDSSNIClient initialization failed with error 0x2740, status code 0x1.
2008-08-08 10:05:21.56 Server Error: 17826, Severity: 18, State: 3.
2008-08-08 10:05:21.56 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2008-08-08 10:05:21.56 Server Error: 17120, Severity: 16, State: 1.
2008-08-08 10:05:21.56 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
August 8, 2008 at 8:20 am
Interesting, and unrelated to the file name issues.
Got a network admin there? First thing you need to do is figure what's listening on port 2987.
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
August 8, 2008 at 8:38 am
I tried to start a couple of time from Surface Area Configuration
and on second attempt it started.
BAP_PROD_2006 is back!
Thank you so much! I owe you lunch... 🙂
Gail,
How can I rename this stupid "FG_Log.ndf"
to
"BAP_PROD_2006_Log.LDF"
Should I run that ALTER DATABASE again
and then restart service?
Something is going wrong with that listening on PORT 2987
thing. I'll deal with it later.
August 8, 2008 at 8:49 am
Run the alter database, then take that database offline, go to the file system, rename the file, bring the DB online. You don't have to restart the entire SQL service.
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
August 8, 2008 at 9:19 am
How do you take the database offline?
Is it detaching?
August 8, 2008 at 9:26 am
In object explorer, right click the database, tasks -> Take offline
To bring it online afterwards, right click the database, tasks -> Bring online
It's not the same as detaching
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
August 8, 2008 at 9:30 am
I like to have scripts for every action.
Is it:
alter database [BAP_PROD_2006] set offline
alter database [BAP_PROD_2006] set online
August 8, 2008 at 9:44 am
Yup.
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
August 8, 2008 at 10:11 am
Gail,
Once again, Thank you so much !
August 8, 2008 at 10:15 am
Pleasure
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 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply