April 2, 2015 at 7:20 am
Can you post SQLAGENT.OUT please?
-- Gianluca Sartori
April 2, 2015 at 7:26 am
Waiting for SQL Server to recover database 'msdb'...
ODBC Error: 0, Invalid character value for cast specification [SQLSTATE 22018]
SQL Server version 12.00.2381 (0 connection limit)
SQL Server ODBC driver version 11.00.3000
NetLib being used by driver is DBNETLIB; Local host server is
32 processor(s) and 65502 MB RAM detected
SQLServerAgent cannot start because the msdb database is read-only
Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
SQLServerAgent terminated (normally)
we tried to reconfigure with override but it is starting the agent the value back to 0 .
April 2, 2015 at 7:32 am
Please post SQL Server ERRORLOG.
-- Gianluca Sartori
April 2, 2015 at 7:38 am
2015-04-02 02:50:21.49 Server Microsoft SQL Server 2014 - 12.0.2381.0 (X64)
Jul 25 2014 18:07:58
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
2015-04-02 02:50:21.50 Server UTC adjustment: -7:00
2015-04-02 02:50:21.50 Server (c) Microsoft Corporation.
2015-04-02 02:50:21.50 Server All rights reserved.
2015-04-02 02:50:21.50 Server Server process ID is 18412.
2015-04-02 02:50:21.50 Server System Manufacturer: 'HP', System Model: 'ProLiant DL380p Gen8'.
2015-04-02 02:50:21.50 Server Authentication mode is WINDOWS-ONLY.
2015-04-02 02:50:21.50 Server Logging SQL Server messages in file 'H:\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2015-04-02 02:50:21.50 Server Registry startup parameters:
-d H:\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf
-e H:\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l H:\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
-T 1118
2015-04-02 02:50:21.50 Server Command Line Startup Parameters:
-s "MSSQLSERVER"
2015-04-02 02:50:22.06 Server SQL Server detected 2 sockets with 8 cores per socket and 16 logical processors per socket, 32 total logical processors; using 32 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2015-04-02 02:50:22.06 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2015-04-02 02:50:22.06 Server Detected 65501 MB of RAM. This is an informational message; no user action is required.
2015-04-02 02:50:22.06 Server Using locked pages in the memory manager.
2015-04-02 02:50:22.07 Server Large Page Allocated: 32MB
2015-04-02 02:50:22.08 Server Large Page Allocated: 32MB
2015-04-02 02:50:22.43 Server Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033)
2015-04-02 02:50:22.49 Server Query Store settings initialized with enabled = 1,
2015-04-02 02:50:22.51 Server The maximum number of dedicated administrator connections for this instance is '1'
2015-04-02 02:50:22.51 Server This instance of SQL Server last reported using a process ID of 19972 at 4/2/2015 2:50:13 AM (local) 4/2/2015 9:50:13 AM (UTC). This is an informational message only; no user action is required.
2015-04-02 02:50:22.51 Server Node configuration: node 0: CPU mask: 0x000000000000ffff:0 Active CPU mask: 0x000000000000ffff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2015-04-02 02:50:22.51 Server Node configuration: node 1: CPU mask: 0x00000000ffff0000:0 Active CPU mask: 0x00000000ffff0000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2015-04-02 02:50:22.68 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.
2015-04-02 02:50:22.68 Server Lock partitioning is enabled. This is an informational message only. No user action is required.
2015-04-02 02:50:22.71 spid8s Starting up database 'master'.
2015-04-02 02:50:22.72 Server CLR version v4.0.30319 loaded.
2015-04-02 02:50:22.88 Server Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
2015-04-02 02:50:23.15 spid8s Resource governor reconfiguration succeeded.
2015-04-02 02:50:23.15 spid8s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2015-04-02 02:50:23.17 spid8s SQL Server Audit has started the audits. This is an informational message. No user action is required.
<{39B75A24-0837-4CEC-AFDF-B960027AE07E}>RsFxNso initialized. InstanceId = 00000003
<{50080099-5EC4-4EAF-A2A2-63C3DA97F8EB}>FsAgent is initialized
<{09C4480B-DBA4-49B7-956F-68A8B8B2445D}>FsAgent is up and running
2015-04-02 02:50:23.19 spid8s FILESTREAM: effective level = 2 (remote access enabled), configured level = 2, file system access share name = 'MSSQLSERVER'.
2015-04-02 02:50:23.24 spid8s SQL Trace ID 1 was started by login "sa".
2015-04-02 02:50:23.24 Server Software Usage Metrics is disabled.
2015-04-02 02:50:23.25 spid8s Server name is 'BY1-PPESQL-02'. This is an informational message only. No user action is required.
2015-04-02 02:50:23.59 spid20s A self-generated certificate was successfully loaded for encryption.
2015-04-02 02:50:23.59 spid20s Server is listening on [ 'any' <ipv6> 1433].
2015-04-02 02:50:23.59 spid20s Server is listening on [ 'any' <ipv4> 1433].
2015-04-02 02:50:23.60 spid20s Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2015-04-02 02:50:23.60 spid20s Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2015-04-02 02:50:23.60 Server Server is listening on [ 'any' <ipv6> 1434].
2015-04-02 02:50:23.60 Server Server is listening on [ 'any' <ipv4> 1434].
2015-04-02 02:50:23.60 Server Dedicated admin connection support was established for listening remotely on port 1434.
2015-04-02 02:50:23.60 spid20s SQL Server is now ready for client connections. This is an informational message; no user action is required.
2015-04-02 02:50:23.60 Server SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
2015-04-02 02:50:23.65 spid22s A new instance of the full-text filter daemon host process has been successfully started.
2015-04-02 02:50:23.71 spid12s The resource database build version is 12.00.2381. This is an informational message only. No user action is required.
2015-04-02 02:50:23.71 spid37s Starting up database 'ManagepointHRSTG_Content'.
2015-04-02 02:50:23.78 spid12s Starting up database 'model'.
2015-04-02 02:50:24.15 spid12s Clearing tempdb database.
2015-04-02 02:50:27.22 spid8s Recovery completed for database PORTALSStg_OME (database ID 54) in 3 second(s) (analysis 95 ms, redo 0 ms, undo 2982 ms.) This is an informational message only. No user action is required.
2015-04-02 02:50:27.31 spid12s Starting up database 'tempdb'.
2015-04-02 02:50:36.46 spid8s Recovery completed for database RITPPE (database ID 117) in 10 second(s) (analysis 21 ms, redo 0 ms, undo 10150 ms.) This is an informational message only. No user action is required.
2015-04-02 02:50:36.48 spid155s The Service Broker endpoint is in disabled or stopped state.
2015-04-02 02:50:36.49 spid155s The Database Mirroring endpoint is in disabled or stopped state.
2015-04-02 02:50:36.52 spid155s Service Broker manager has started.
2015-04-02 02:50:39.35 spid51 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2015-04-02 02:50:39.81 spid51 Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
2015-04-02 02:50:39.82 spid51 Using 'xpsqlbot.dll' version '2014.120.2000' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2015-04-02 02:50:39.87 spid51 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2015-04-02 02:51:04.27 spid52 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
2015-04-02 02:51:04.30 spid52 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2015-04-02 02:51:14.83 spid53 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2015-04-02 02:53:00.07 spid53 Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2015-04-02 02:53:00.09 spid53 Using 'xpstar.dll' version '2014.120.2000' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2015-04-02 02:53:00.42 spid53 SQL Server blocked access to procedure 'dbo.sp_get_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', search for 'Agent XPs' in SQL Server Books Online.
2015-04-02 02:53:14.71 spid54 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2015-04-02 02:53:14.75 spid54 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2015-04-02 02:53:30.28 spid54 Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
2015-04-02 02:53:30.30 spid54 Using 'xplog70.dll' version '2014.120.2000' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2015-04-02 02:58:14.85 spid52 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
2015-04-02 02:58:14.87 spid52 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2015-04-02 02:58:29.17 spid55 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2015-04-02 02:58:37.04 spid56 SQL Server blocked access to procedure 'dbo.sp_get_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', search for 'Agent XPs' in SQL Server Books Online.
2015-04-02 03:03:03.22 spid53 SQL Server blocked access to procedure 'dbo.sp_get_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', search for 'Agent XPs' in SQL Server Books Online.
2015-04-02 03:09:37.48 spid55 SQL Server blocked access to procedure 'dbo.sp_get_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', search for 'Agent XPs' in SQL Server Books Online.
2015-04-02 03:49:07.14 spid52 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2015-04-02 03:49:35.22 spid52 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
2015-04-02 03:49:35.24 spid52 Configuration option 'Agent XPs' changed from 1 to 1. Run the RECONFIGURE statement to install.
2015-04-02 03:49:46.26 spid57 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2015-04-02 03:51:17.21 spid52 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
2015-04-02 03:51:17.24 spid52 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2015-04-02 03:51:28.29 spid57 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
April 2, 2015 at 8:05 am
There's no mention of msdb anywhere in your log.
Also, the entry "recovery is complete" is missing, which suggests that SQL Server hasn't finished recovery yet.
Please restart SQL Server, don't try to start the agent and post the ERRORLOG
Also, please note that none of your databases ever had a CHECKDB. Are you checking consistency regularly?
-- Gianluca Sartori
April 2, 2015 at 8:14 am
2015-04-02 02:33:00.74 Server Microsoft SQL Server 2014 - 12.0.2381.0 (X64)
Jul 25 2014 18:07:58
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
2015-04-02 02:33:00.74 Server UTC adjustment: -7:00
2015-04-02 02:33:00.74 Server (c) Microsoft Corporation.
2015-04-02 02:33:00.74 Server All rights reserved.
2015-04-02 02:33:00.74 Server Server process ID is 19972.
2015-04-02 02:33:00.74 Server System Manufacturer: 'HP', System Model: 'ProLiant DL380p Gen8'.
2015-04-02 02:33:00.74 Server Authentication mode is WINDOWS-ONLY.
2015-04-02 02:33:00.74 Server Logging SQL Server messages in file 'H:\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2015-04-02 02:33:00.74 Server The service account is 'REDMOND\BY1-PPESQL-02$'. This is an informational message; no user action is required.
2015-04-02 02:33:00.74 Server Registry startup parameters:
-d H:\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf
-e H:\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l H:\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
-T 1118
2015-04-02 02:33:00.74 Server Command Line Startup Parameters:
-s "MSSQLSERVER"
2015-04-02 02:33:01.24 Server SQL Server detected 2 sockets with 8 cores per socket and 16 logical processors per socket, 32 total logical processors; using 32 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2015-04-02 02:33:01.24 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2015-04-02 02:33:01.24 Server Detected 65501 MB of RAM. This is an informational message; no user action is required.
2015-04-02 02:33:01.24 Server Using locked pages in the memory manager.
2015-04-02 02:33:01.26 Server Large Page Allocated: 32MB
2015-04-02 02:33:01.27 Server Large Page Allocated: 32MB
2015-04-02 02:33:01.63 Server Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033)
2015-04-02 02:33:01.69 Server Query Store settings initialized with enabled = 1,
2015-04-02 02:33:01.71 Server The maximum number of dedicated administrator connections for this instance is '1'
2015-04-02 02:33:01.71 Server This instance of SQL Server last reported using a process ID of 2752 at 4/2/2015 2:32:52 AM (local) 4/2/2015 9:32:52 AM (UTC). This is an informational message only; no user action is required.
2015-04-02 02:33:01.71 Server Node configuration: node 0: CPU mask: 0x000000000000ffff:0 Active CPU mask: 0x000000000000ffff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2015-04-02 02:33:01.71 Server Node configuration: node 1: CPU mask: 0x00000000ffff0000:0 Active CPU mask: 0x00000000ffff0000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2015-04-02 02:33:01.76 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.
2015-04-02 02:33:01.76 Server Lock partitioning is enabled. This is an informational message only. No user action is required.
2015-04-02 02:33:01.79 spid8s Starting up database 'master'.
2015-04-02 02:33:01.81 Server CLR version v4.0.30319 loaded.
2015-04-02 02:33:01.96 Server Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
2015-04-02 02:33:02.16 spid8s Resource governor reconfiguration succeeded.
2015-04-02 02:33:02.17 spid8s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2015-04-02 02:33:02.17 spid8s SQL Server Audit has started the audits. This is an informational message. No user action is required.
<{39B75A24-0837-4CEC-AFDF-B960027AE07E}>RsFxNso initialized. InstanceId = 00000002
<{50080099-5EC4-4EAF-A2A2-63C3DA97F8EB}>FsAgent is initialized
<{09C4480B-DBA4-49B7-956F-68A8B8B2445D}>FsAgent is up and running
2015-04-02 02:33:02.19 spid8s FILESTREAM: effective level = 2 (remote access enabled), configured level = 2, file system access share name = 'MSSQLSERVER'.
2015-04-02 02:33:02.24 spid8s SQL Trace ID 1 was started by login "sa".
2015-04-02 02:33:02.25 spid8s Server name is 'BY1-PPESQL-02'. This is an informational message only. No user action is required.
2015-04-02 02:33:02.37 Server Software Usage Metrics is disabled.
2015-04-02 02:33:02.54 spid16s A self-generated certificate was successfully loaded for encryption.
2015-04-02 02:33:02.55 spid16s Server is listening on [ 'any' <ipv6> 1433].
2015-04-02 02:33:02.55 spid16s Server is listening on [ 'any' <ipv4> 1433].
2015-04-02 02:33:02.55 spid16s Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2015-04-02 02:33:02.55 spid16s Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2015-04-02 02:33:02.55 Server Server is listening on [ 'any' <ipv6> 1434].
2015-04-02 02:33:02.55 Server Server is listening on [ 'any' <ipv4> 1434].
2015-04-02 02:33:02.55 Server Dedicated admin connection support was established for listening remotely on port 1434.
2015-04-02 02:33:02.56 spid16s SQL Server is now ready for client connections. This is an informational message; no user action is required.
2015-04-02 02:33:02.56 Server SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
2015-04-02 02:33:02.77 spid17s A new instance of the full-text filter daemon host process has been successfully started.
2015-04-02 02:33:02.88 spid12s Starting up database 'mssqlsystemresource'.
2015-04-02 02:33:02.88 spid25s Starting up database 'msdb'.
2015-04-02 02:33:02.99 spid12s Starting up database 'model'.
2015-04-02 02:33:03.18 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/BY1-PPESQL-02.redmond.corp.microsoft.com ] for the SQL Server service.
2015-04-02 02:33:03.18 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/BY1-PPESQL-02.redmond.corp.microsoft.com:1433 ] for the SQL Server service.
2015-04-02 02:33:03.62 spid12s Clearing tempdb database.
2015-04-02 02:33:20.88 spid155s The Service Broker endpoint is in disabled or stopped state.
2015-04-02 02:33:20.88 spid155s The Database Mirroring endpoint is in disabled or stopped state.
2015-04-02 02:33:20.90 spid155s Service Broker manager has started.
2015-04-02 02:33:21.73 spid8s Recovery is complete. This is an informational message only. No user action is required.
2015-04-02 02:33:26.69 spid53 Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
2015-04-02 02:33:26.69 spid53 Using 'xpsqlbot.dll' version '2014.120.2000' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2015-04-02 02:33:50.66 spid54 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2015-04-02 02:34:01.58 spid55 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2015-04-02 02:34:29.17 spid55 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2015-04-02 02:34:29.23 spid55 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2015-04-02 02:38:32.75 spid52 Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2015-04-02 02:38:32.79 spid52 Using 'xpstar.dll' version '2014.120.2000' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2015-04-02 02:43:28.79 spid54 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
2015-04-02 02:43:28.81 spid54 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2015-04-02 02:43:44.85 spid55 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2015-04-02 02:50:07.20 spid155s Service Broker manager has shut down.
2015-04-02 02:50:07.77 spid8s .NET Framework runtime has been stopped.
2015-04-02 02:50:13.95 spid8s SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
2015-04-02 02:50:13.96 spid8s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2015-04-02 02:50:14.60 spid16s The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) for the SQL Server service.
2015-04-02 02:50:14.60 spid16s The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) for the SQL Server service.
April 2, 2015 at 8:18 am
This log is older than the previous one. Please post the latest log.
-- Gianluca Sartori
April 2, 2015 at 8:20 am
Hang on, you work for Microsoft, on the Redmond campus and you're asking people here for help?
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
April 2, 2015 at 9:00 am
no, I got the info
thanks all
April 2, 2015 at 9:06 am
Any chance you're telling us what was wrong?
-- Gianluca Sartori
April 2, 2015 at 9:09 am
Moreover, do yourself a favor and set up a consistency check.
Options:
1. A maintenance plan
2. Ola Hallengren maintenance solution
4. Code your own
-- Gianluca Sartori
March 8, 2016 at 10:04 am
I have the same problem. What was the resolution?
March 8, 2016 at 10:12 am
Please start a new thread and post your ERRORLOG
-- Gianluca Sartori
March 8, 2016 at 10:27 am
I started a new thread and posted the ErrorLog
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply