How do you use cloud databases? Take the survey.

Databases Duplicated Under Monitored Server

Can anyone tell me why I would see some of my databases on a single instance MSSQL server duplicated in the list of databases on a monitored server?  It does not happen for ALL of the databases, just some (no noticeable reason).  For those that are duplicated, there is one in PascalCase (MyDatabase) and one in all uppercase (MYDATABASE).  The one in PascalCase perform correctly (shows data, etc.).  The uppercase one does not have any data.  Only the PascalCase one has an availability of ONLINE.

Best Answer

  • DanAppleyardDanAppleyard Posts: 9 New member
    I am updating this forum post to explain the discovered reason.  It has nothing to do with SQL Monitor directly.  As stated earlier, it seemed to correspond with the creation of backup-related alerts.  SQL Monitor appears to use the database_name field from msdb.dbo.backupset table.  I had some entries that had the name in uppercase, so SQL Monitor read that and ended up creating new database entries for the given server.

Answers

  • Alex BAlex B Posts: 1,131 Diamond 4
    Hi @DanAppleyard,

    Is you SQL Server instance case sensitive?  If so, is it possible that someone could have created an UPPERCASE version of the same database and then deleted it? 

    If these are possible SQL Monitor would likely have captured information about that database when it existed and it will be displayed until the historical data related to it has been purged.

    You may be able to confirm this by checking for historical data for the UPPERCASE database using the back in time mode on the server overview or the analysis graph.

    If none of those seem to be the case, please let me know!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • DanAppleyardDanAppleyard Posts: 9 New member
    edited January 7, 2019 5:07PM
    Alex,

    These servers NEVER had an uppercase version of the databases in question.  One such of these databases is master (FYI).  I deleted this server (including purging all the data) from monitor, and re-added it.  Initially the listed databases were correct (no duplicates).  It wasn't until the first set of alerts were generated for this server did database duplicates show. 

    I am running version 8.0.19.16748 (the version installed from the 14 day trial).
  • DanAppleyardDanAppleyard Posts: 9 New member
    Alex,

    It appears these duplications are only happening for those databases that are in PascalCase (MyDatabase).  We do have some databases that are in uppercase (MYOTHERDATABASE) and those never get duplicated.  Does SQL Monitor do something when it performs metrics that get the database name from the monitored server and just uppercases it and if that uppercase version of the database is not in the system, it creates a new database entry (or something like that)???
  • Jessica RJessica R Posts: 1,319 Rose Gold 4

    Thanks @DanAppleyard!

    How odd, can I check-- do you by any chance have two base monitors using the same repository, and the server is added to both?

    What type of alerts had come in that made the duplicates return?

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • No, we only have the single monitor.  
    The alerts that came up that correlate with the duplicates showing (not returning, I just want to make that clear - they were not there initially) seemed to be backup related.
  • Jessica RJessica R Posts: 1,319 Rose Gold 4
    Thanks for confirming that, Dan. We've opened a ticket for this case, #128891, and I'm going to reach out to you there shortly to get more detail.

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • JoergHJoergH Posts: 5 Bronze 1
    edited February 13, 2019 3:03PM
    I am updating this forum post to explain the discovered reason.  It has nothing to do with SQL Monitor directly.  As stated earlier, it seemed to correspond with the creation of backup-related alerts.  SQL Monitor appears to use the database_name field from msdb.dbo.backupset table.  I had some entries that had the name in uppercase, so SQL Monitor read that and ended up creating new database entries for the given server.
    Hi Dan, hi Jessica,
    same thing here! Every database that has a database_name entry in msdb.dbo.backupset that differs from the original database name as seen in sys.databases, appears as doubled in SQL Monitor. The ones whose names come from msdb.dbo.backupset are showing no data for actual metrics and history.
    In my case all different written names in msdb.dbo.backupset derived from sql agent backup jobs (e.g. "backup database [abc]" instead of "backup database [AbC]").
    This doesn't seem to be a brand new thing - we already ignored it (sorry for that) since several versions of SQL Monitor (actually on 9.02).
    My Question: How did you resolve that issue?

    Some additional info:
    => We observe those doubled database entries exactly since installing 8.0.19.16748 back in October! I used the following query to find the day of first appearace (CollectionDate) for the doubled entries:

    SELECT _name COLLATE SQL_Latin1_General_Cp1_CS_AS as _name_1<br>&nbsp;&nbsp;&nbsp; , utils.TicksToDateTime(CollectionDate) AS Collection_Datum<br>&nbsp;&nbsp;&nbsp; , * <br>FROM [data].[Cluster_SqlServer_Database_Keys] <br>WHERE Parentid = 15 -- this belongs to that specific monitored server<br>ORDER BY _name_1

    Could someone please provide us with a solution for that problem? 16 out of 38 databases on that server appear as additional ghost entries.

    Regards, Joerg

Sign In or Register to comment.