March 15, 2013 at 12:02 pm
When I reboot my server and log into SSMS my database is listed but it cannot be expanded. The table is not available. If I rightclick and take the DB offline then bring it online the database is then expandable and available. Server is VM with database on ISCI SAN on a volume.
Suggestions Please!
March 15, 2013 at 12:45 pm
weird, i haven't seen that issue before specifically; you clearly have dba rights to take it offline and online, so that's not the issue, especially since it's available afterwards.
I have seen weird things when you use things like the 2005 verison of SSMS to conenct to a 2008/R2/2012 server; the SMO is older and sometimes doesn't support; can you check your SSMS client version and see and see if it's older than the server you connect to?
Lowell
March 15, 2013 at 12:54 pm
I have 3 servers with DBs replicating and they are all configured the same but only this one server has the problem. Shouldn't be the server or its configuration. I am thinking something to do with the network between the server and DB. I know its not exactly the same but I'm not a network guy and my network admin has no idea.
March 15, 2013 at 1:01 pm
Any relevant messages in the error log?
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
March 15, 2013 at 3:39 pm
No errors and the same informational messages on my servers that don't have this problem.
March 15, 2013 at 3:47 pm
This is off the wall, but did "AUTO_CLOSE" somehow get set on for that db?
That's a longshot, since even if it did it should auto_open when you reference it, but I'm just trying to think of anything that could cause that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 15, 2013 at 3:48 pm
dcyoung (3/15/2013)
No errors and the same informational messages on my servers that don't have this problem.
Can you post the error log, from startup to the time you offline/online the DB? That's assuming auto_close isn't on, if it is, that's possibly the problem, along with a long recovery time.
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
March 15, 2013 at 3:57 pm
My network guy is thinking it might be that sql server is starting befor ISCSI gets its job done.
Here is my entire error log since the reboot this morning.
Date,Source,Severity,Message
03/15/2013 07:56:46,,Warning,[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
03/15/2013 07:56:46,,Warning,[260] Unable to start mail session (reason: No mail profile defined)
03/15/2013 07:56:46,,Information,[129] SQLSERVERAGENT starting under Windows NT service control
03/15/2013 07:56:46,,Error,[364] The Messenger service has not been started - NetSend notifications will not be sent
03/15/2013 07:56:45,,Information,[432] There are 12 subsystems in the subsystems cache
03/15/2013 07:56:44,,Information,[339] Local computer is LAWFBXPL-R2 running Windows NT 6.1 (7601) Service Pack 1
03/15/2013 07:56:44,,Information,[310] 2 processor(s) and 32768 MB RAM detected
03/15/2013 07:56:44,,Information,[103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
03/15/2013 07:56:44,,Information,[102] SQL Server ODBC driver version 10.50.2500
03/15/2013 07:56:44,,Information,[101] SQL Server LAWFBXPL-R2 version 10.50.2550 (0 connection limit)
03/15/2013 07:56:44,,Information,[100] Microsoft SQLServerAgent version 10.50.2550.0 ((Unknown) unicode retail build) : Process ID 1900
03/15/2013 07:56:39,,Information,[393] Waiting for SQL Server to recover databases...
March 15, 2013 at 4:02 pm
dcyoung (3/15/2013)
My network guy is thinking it might be that sql server is starting befor ISCSI gets its job done.
Very possible, which is why I want to see the SQL error log as that kind of condition results in a very distinctive set of error messages.
03/15/2013 07:56:44,,Information,[100] Microsoft SQLServerAgent version 10.50.2550.0 ((Unknown) unicode retail build) : Process ID 1900
That's the SQL Server Agent log, not going to be of much use. The SQL Server error log please.
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
March 15, 2013 at 4:12 pm
Sorry bout that.
Date,Source,Severity,Message
03/15/2013 13:00:10,Backup,Unknown,Log was backed up. Database: Prolaw<c/> creation date(time): 2012/10/26(19:21:51)<c/> first LSN: 66562:5011:1<c/> last LSN: 66688:41:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'R:\Transaction_logs\Prolaw_backup_2013_03_15_130000_7710445.trn'}). This is an informational message only. No user action is required.
03/15/2013 10:00:18,Backup,Unknown,Log was backed up. Database: Prolaw<c/> creation date(time): 2012/10/26(19:21:51)<c/> first LSN: 66407:6124:1<c/> last LSN: 66562:5011:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'R:\Transaction_logs\Prolaw_backup_2013_03_15_100008_1200247.trn'}). This is an informational message only. No user action is required.
03/15/2013 09:10:18,spid56,Unknown,CHECKDB for database 'Prolaw' finished without errors on 2013-03-10 04:00:08.227 (local time). This is an informational message only; no user action is required.
03/15/2013 09:10:18,spid56,Unknown,Recovery completed for database Prolaw (database ID 7) in 1 second(s) (analysis 41 ms<c/> redo 263 ms<c/> undo 45 ms.) This is an informational message only. No user action is required.
03/15/2013 09:10:17,spid56,Unknown,Recovery is writing a checkpoint in database 'Prolaw' (7). This is an informational message only. No user action is required.
03/15/2013 09:10:17,spid56,Unknown,0 transactions rolled back in database 'Prolaw' (7). This is an informational message only. No user action is required.
03/15/2013 09:10:16,spid56,Unknown,52 transactions rolled forward in database 'Prolaw' (7). This is an informational message only. No user action is required.
03/15/2013 09:10:15,spid56,Unknown,Starting up database 'Prolaw'.
03/15/2013 09:10:15,spid56,Unknown,Setting database option ONLINE to ON for database Prolaw.
03/15/2013 09:10:06,spid56,Unknown,Setting database option OFFLINE to ON for database Prolaw.
03/15/2013 09:09:32,Logon,Unknown,Login failed for user 'LAW\dcyoung-adm'. Reason: Failed to open the explicitly specified database. [CLIENT: 10.9.64.7]
03/15/2013 08:50:44,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
03/15/2013 08:44:39,Logon,Unknown,Login failed for user 'LAW\clbills'. Reason: Failed to open the explicitly specified database. [CLIENT: 10.9.65.43]
03/15/2013 08:44:39,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
03/15/2013 08:44:16,Logon,Unknown,Login failed for user 'LAW\pahartnell'. Reason: Failed to open the explicitly specified database. [CLIENT: 10.9.65.48]
03/15/2013 08:44:16,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
03/15/2013 07:57:00,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
03/15/2013 07:56:44,spid51,Unknown,Using 'xplog70.dll' version '2009.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
03/15/2013 07:56:44,spid51,Unknown,Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
03/15/2013 07:56:45,spid51,Unknown,Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
03/15/2013 07:56:45,spid51,Unknown,Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
03/15/2013 07:56:44,spid51,Unknown,Using 'xpsqlbot.dll' version '2009.100.1600' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
03/15/2013 07:56:44,spid51,Unknown,Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
03/15/2013 07:56:42,spid7s,Unknown,Recovery is complete. This is an informational message only. No user action is required.
03/15/2013 07:56:42,spid12s,Unknown,Recovery completed for database msdb (database ID 4) in 1 second(s) (analysis 314 ms<c/> redo 250 ms<c/> undo 94 ms.) This is an informational message only. No user action is required.
03/15/2013 07:56:42,spid12s,Unknown,Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
03/15/2013 07:56:42,spid12s,Unknown,0 transactions rolled back in database 'msdb' (4). This is an informational message only. No user action is required.
03/15/2013 07:56:41,spid12s,Unknown,893 transactions rolled forward in database 'msdb' (4). This is an informational message only. No user action is required.
03/15/2013 07:56:41,spid12s,Unknown,The tail of the log for database msdb is being rewritten to match the new sector size of 4096 bytes. 2048 bytes at offset 3041280 in file C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf will be written.
03/15/2013 07:56:41,spid19s,Unknown,Recovery is writing a checkpoint in database 'ReportServer' (5). This is an informational message only. No user action is required.
03/15/2013 07:56:41,spid19s,Unknown,0 transactions rolled back in database 'ReportServer' (5). This is an informational message only. No user action is required.
03/15/2013 07:56:41,spid19s,Unknown,1 transactions rolled forward in database 'ReportServer' (5). This is an informational message only. No user action is required.
03/15/2013 07:56:41,spid20s,Unknown,Recovery is writing a checkpoint in database 'ReportServerTempDB' (6). This is an informational message only. No user action is required.
03/15/2013 07:56:41,spid20s,Unknown,0 transactions rolled back in database 'ReportServerTempDB' (6). This is an informational message only. No user action is required.
03/15/2013 07:56:41,spid20s,Unknown,1 transactions rolled forward in database 'ReportServerTempDB' (6). This is an informational message only. No user action is required.
03/15/2013 07:56:41,spid21s,Unknown,File activation failure. The physical file name "P:\DB\Prolaw_log.ldf" may be incorrect.
03/15/2013 07:56:41,spid21s,Unknown,FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'P:\DB\Prolaw_log.ldf'. Diagnose and correct the operating system error<c/> and retry the operation.
03/15/2013 07:56:41,spid21s,Unknown,Error: 17207<c/> Severity: 16<c/> State: 1.
03/15/2013 07:56:41,spid21s,Unknown,Unable to open the physical file "P:\DB\Prolaw.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
03/15/2013 07:56:41,spid21s,Unknown,Error: 5120<c/> Severity: 16<c/> State: 101.
03/15/2013 07:56:41,spid21s,Unknown,FCB::Open failed: Could not open file P:\DB\Prolaw.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
03/15/2013 07:56:41,spid21s,Unknown,Error: 17204<c/> Severity: 16<c/> State: 1.
03/15/2013 07:56:41,spid20s,Unknown,Starting up database 'ReportServerTempDB'.
03/15/2013 07:56:41,spid12s,Unknown,Starting up database 'msdb'.
03/15/2013 07:56:41,spid21s,Unknown,Starting up database 'Prolaw'.
03/15/2013 07:56:41,spid19s,Unknown,Starting up database 'ReportServer'.
03/15/2013 07:56:40,spid12s,Unknown,A new instance of the full-text filter daemon host process has been successfully started.
03/15/2013 07:56:38,Logon,Unknown,Login failed for user 'LAW\lawplsql-adm'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
03/15/2013 07:56:38,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
03/15/2013 07:56:37,Logon,Unknown,Login failed for user 'LAW\lawplsql-adm'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
03/15/2013 07:56:37,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
03/15/2013 07:56:37,spid14s,Unknown,Service Broker manager has started.
03/15/2013 07:56:37,spid14s,Unknown,The Database Mirroring protocol transport is disabled or not configured.
03/15/2013 07:56:37,spid14s,Unknown,The Service Broker protocol transport is disabled or not configured.
03/15/2013 07:56:36,spid10s,Unknown,Starting up database 'tempdb'.
03/15/2013 07:56:36,Server,Unknown,SQL Server is now ready for client connections. This is an informational message; no user action is required.
03/15/2013 07:56:36,Server,Unknown,The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/LAWFBXPL-R2.law.loc:1433 ] for the SQL Server service.
03/15/2013 07:56:36,Server,Unknown,The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/LAWFBXPL-R2.law.loc ] for the SQL Server service.
03/15/2013 07:56:36,Server,Unknown,Dedicated admin connection support was established for listening locally on port 1434.
03/15/2013 07:56:36,Server,Unknown,Server is listening on [ 127.0.0.1 <ipv4> 1434].
03/15/2013 07:56:36,Server,Unknown,Server is listening on [ ::1 <ipv6> 1434].
03/15/2013 07:56:36,Server,Unknown,Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
03/15/2013 07:56:36,Server,Unknown,Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
03/15/2013 07:56:36,Server,Unknown,Server is listening on [ 'any' <ipv4> 1433].
03/15/2013 07:56:36,Server,Unknown,Server is listening on [ 'any' <ipv6> 1433].
03/15/2013 07:56:36,Server,Unknown,A self-generated certificate was successfully loaded for encryption.
03/15/2013 07:56:36,spid10s,Unknown,Clearing tempdb database.
03/15/2013 07:56:36,spid10s,Unknown,Recovery is writing a checkpoint in database 'model' (3). This is an informational message only. No user action is required.
03/15/2013 07:56:36,spid10s,Unknown,0 transactions rolled back in database 'model' (3). This is an informational message only. No user action is required.
03/15/2013 07:56:36,spid10s,Unknown,1 transactions rolled forward in database 'model' (3). This is an informational message only. No user action is required.
03/15/2013 07:56:35,spid7s,Unknown,Server name is 'LAWFBXPL-R2'. This is an informational message only. No user action is required.
03/15/2013 07:56:35,spid10s,Unknown,Starting up database 'model'.
03/15/2013 07:56:35,spid7s,Unknown,The resource database build version is 10.50.2500. This is an informational message only. No user action is required.
03/15/2013 07:56:35,spid7s,Unknown,Starting up database 'mssqlsystemresource'.
03/15/2013 07:56:35,spid7s,Unknown,SQL Trace ID 1 was started by login "sa".
03/15/2013 07:56:34,spid7s,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.
03/15/2013 07:56:34,spid7s,Unknown,Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
03/15/2013 07:56:34,spid7s,Unknown,0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
03/15/2013 07:56:34,spid7s,Unknown,262 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
03/15/2013 07:56:33,spid7s,Unknown,The tail of the log for database master is being rewritten to match the new sector size of 4096 bytes. 1024 bytes at offset 64512 in file C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf will be written.
03/15/2013 07:56:33,spid7s,Unknown,Starting up database 'master'.
03/15/2013 07:56:33,Server,Unknown,Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
03/15/2013 07:56:32,Server,Unknown,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.
03/15/2013 07:56:31,Server,Unknown,Detected 2 CPUs. This is an informational message; no user action is required.
03/15/2013 07:56:31,Server,Unknown,SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
03/15/2013 07:56:31,Server,Unknown,Registry startup parameters: <nl/> -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf<nl/> -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG<nl/> -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
03/15/2013 07:56:31,Server,Unknown,This instance of SQL Server last reported using a process ID of 1264 at 3/14/2013 4:31:03 PM (local) 3/15/2013 12:31:03 AM (UTC). This is an informational message only; no user action is required.
03/15/2013 07:56:31,Server,Unknown,Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
03/15/2013 07:56:31,Server,Unknown,Authentication mode is MIXED.
03/15/2013 07:56:31,Server,Unknown,System Manufacturer: 'VMware<c/> Inc.'<c/> System Model: 'VMware Virtual Platform'.
03/15/2013 07:56:31,Server,Unknown,Server process ID is 1228.
03/15/2013 07:56:31,Server,Unknown,All rights reserved.
03/15/2013 07:56:31,Server,Unknown,(c) Microsoft Corporation.
03/15/2013 07:56:31,Server,Unknown,Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) <nl/>Jun 11 2012 16:41:53 <nl/>Copyright (c) Microsoft Corporation<nl/>Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
March 15, 2013 at 4:16 pm
Me thinks that Gail and your Network admin are thinking the same thing here. Sort of looks familar to when our SAN did not come before some of our servers a few times at a previous employer.
March 15, 2013 at 4:21 pm
Thanks, network admin was looking into how to set a dependency on ISCSI for SQL server.
March 15, 2013 at 5:03 pm
Would this be the database in question?
03/15/2013 07:56:41,spid21s,Unknown,Unable to open the physical file "P:\DB\Prolaw.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
03/15/2013 07:56:41,spid21s,Unknown,Error: 5120<c/> Severity: 16<c/> State: 101.
03/15/2013 07:56:41,spid21s,Unknown,FCB::Open failed: Could not open file P:\DB\Prolaw.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
03/15/2013 07:56:41,spid21s,Unknown,Error: 17204<c/> Severity: 16<c/> State: 1.
If so, at the time SQL starts, the path 'P:\DB' does not exist.
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
March 17, 2013 at 6:25 am
If he is a member of the DDL-ADMIN fixed role he could take a database offline (as far as I am aware) and then bring it online.
He wouldn't be able to expand it if he was not mapped to the database at the time of trying to expand the tree or had DB_DENYDATAREADER. At the time he brings the database back online he is automatically mapped which means expanding the tree is no longer a problem.
Only a rough guess that I pulled out of the air.....
March 17, 2013 at 6:29 am
If that was the case he wouldn't be able to take the database offline in the first place. He was quite clear that he could see the database (the file exists therefore) and bring it back online (the path is right).
Somehow it reaks a bit of a permissions problem.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply