Problem description
The server successfully gets registered and is visible in File servers but the SQL discovery fails and because of which it doesn’t get listed under “All SQL Resources” in the Phoenix console
Cause
The SQL database file has a space in the name
Traceback
The C:\ProgramData\Phoenix\main_service.log file show below error
[2022-10-26 21:34:09,760] [ERROR] Error in Executing sql discovery
[2022-10-26 21:34:09,760] [ERROR] Error <type 'exceptions.WindowsError'>:[Error 2] The system cannot find the file specified: u'D:\\SQL\\xxxxx.mdf'. Traceback -Traceback (most recent call last):
File "agents\sqlserver\SqlAgent.pyc", line 165, in run_discovery
File "agents\sqlserver\SqlHelper.pyc", line 164, in get_discovery_info
File "agents\sqlserver\SqlHelper.pyc", line 205, in _get_instances_info
File "agents\sqlserver\SqlHelper.pyc", line 225, in _add_db_size_in_all_db_info_dict
File "agents\sqlserver\SqlHelper.pyc", line 242, in _calculate_db_size
WindowsError: [Error 2] The system cannot find the file specified: u'D:\\SQL\\xxxxx.mdf'
Resolution
Make sure the account used for Hybrid Workloads Agent service
has sysadmin rights
is part of Administrators group on the SQL server
has read permissions on the folder where the database files reside, D:\SQL in this case
Verify that there is no leading or trailing space in the file name of the database file
Open SQL Server Management Studio. Ensure that the version of the SQL Server Management Studio matches the version of the SQL instance running on the machine.
Run either of the following queries to get a list of database names and their locations.
USE <database_name> SELECT * FROM sys.database_files OR USE <database_name> SELECT name, physical_name from sys.database_filesIn the query results, determine the database whose physical name has a double slash /space in the physical_name column.column.
Execute the following command to modify physical path to the file
ALTER DATABASE <database name> MODIFY FILE (NAME = '<logical name>', FILENAME='<path to file>'); where <database name> is the name of the database that is mentioned in the error, no changes needed <logical name> is the logical name of the file mentioned in the error, no changes needed <path to file> is the full path to the file that is mentioned in the error, without spaces at the beginning or the end