SQL Database Properties Not Discovered


I ran into an issue recently where some SQL Databases were not showing any properties in OpsMgr, other than the database name:


image


 


To get these properties, the database discovery script runs the “sp_helpdb” stored procedure against the database.  To test this, open SQL Server Managment Studio, connect to the SQL Instance in question, open a new query window and run “sp_helpdb <database name>””:


image


NOTE: You should run this under the same account that is used for the “SQL Server Discovery Account” RunAs Profile….if you haven’t defined an account for this profile, then use the Action Account.


If this doesn’t return any results (as shown below), then the problem is likely due to permissions.  From the SQL MP guide, the requirements for DB discovery are:






· EXEC permissions for (sp_helpdb)


· Select from sys.databases table in the master database


image


Also, before running sp_helpdb, the discovery script will query to get a list of databases.  In SQL 2005/2008, the query is:


SELECT name, state_desc FROM sys.databases WHERE source_database_id IS NULL


In SQL 2000, the query is:


SELECT name FROM sysdatabases


 


The difference is that in SQL 2005/2008, we have the “WHERE source_database_id IS NULL“ clause, which will eliminate snapshot databases…..so if the SQL instance has any snapshot databases, they will not be discovered.  We also select the “state” column from sys.databases in SQL 2005/2008 DB discovery, and if the state is not “ONLINE”, then the discovery ends there….so this would be another reason why the database properties do not show up in OpsMgr.


Attached to this blog are debug version of the database discovery script:


DiscoverSQL2005DB_debug.txt – Use this for SQL 2005/2008


DiscoverSQL2000DB_debug.txt – Use this for SQL 2000


To run the script:



  1. Rename to .vbs

  2. Run the following command:


cscript DiscoverSQL2005DB_debug.vbs <fqdn> <Server\instance> “exclude:”


Replace the bold items with:


<fqdn> = Full Qualified Domain Name of the SQL Server (server.domain.com)


<Server\instance> = SQL Server instance that we want to discover DBs on.  If it is the default instance, it will just be the server name (SERVER), otherwise it will be SERVER\INSTANCE



Sample output from my server:


Server name is jimmyhsql1.jimmyhdom.com


SQL instance is OpsDB


Command line is cscript discoversql2005db_debug.vbs jimmyhsql1.jimmyhdom.com jimmyhsql1\opsdb “exclude:”



Output (I only copied the output for the first couple DBs):


Entering DoDatabaseDiscovery function…
Connection string is Server=jimmyhsql1\opsdb;Database=master;Trusted_Connection=
yes
Error number is 0
Querying for list of non-snapshot databases…
Error number is 0
==================================
DatabaseName: master
DatabaseState ONLINE


Runing sp_helpdb master
ErrorNumber: 0
If no results are listed below, then sp_helpdb did not return anything….check
permissions


DatabaseSize: 4
DatabaseSizeNumeric: 4
LogSize: 0.5
LogSizeNumeric: 0
RecoveryModel: SIMPLE
Updateability: READ_WRITE
UserAccess: MULTI_USER
Collation: SQL_Latin1_General_CP1_CI_AS
DatabaseAutogrow: True
LogAutogrow: True
Owner: sa
==================================


==================================
DatabaseName: tempdb
DatabaseState ONLINE


Runing sp_helpdb tempdb
ErrorNumber: 0
If no results are listed below, then sp_helpdb did not return anything….check
permissions


DatabaseSize: 23.0625
DatabaseSizeNumeric: 23
LogSize: 1
LogSizeNumeric: 1
RecoveryModel: SIMPLE
Updateability: READ_WRITE
UserAccess: MULTI_USER
Collation: SQL_Latin1_General_CP1_CI_AS
DatabaseAutogrow: True
LogAutogrow: True
Owner: sa
==================================

DB_Discovery_debug_scripts.zip

Comments (1)

  1. Anonymous says:

    Blogs – Jimmy Harper’s Operations Manager Blog – Site Home – TechNet Blogs