Monitor Databases

The procedure Monitor Databases creates an overview of all databases that are found on the Linked Servers. This list is compared to the list that was retrieved the last time this procedure ran. Any changes found are reported.

  • New databases
  • Deleted databases
  • Changed properties
    – Recovery model (Full / Simple / Bulk logged)
    – Quoted identifiers (On / Off)
    – Status (Online / Offline / Readonly)
    – UserAccess (Multi user / Single user)
    – Autoclose (On / Off)
    – Autoshrink (On / Off)

How to use it

The procedure usp_Report_Databases will crawl all Linked Servers and retrieve information on all databases it finds. Information for these databases is stored in the table tbl_Databases.

Create a scheduled job using the Settings window. This will create a job with all necessary information. If you prefer to do this manually, execute the stored procedure with the following parameters:

DECLARE @RC int
DECLARE @Recipient varchar(250)
DECLARE @ExceptionList varchar(250)
DECLARE @MailStats bit
DECLARE @SqlVersion int
DECLARE @Separator nchar(1)

SET @Recipient = ‘yourname@yourdomain.com’
SET @ExceptionList = ‘ServerNotToProcess’
SET @MailStats = 1
SET @SqlVersion = 0
SET @Separator = ‘,’

EXECUTE @RC = [dbo].[usp_Report_Databases] 
   @Recipient
  ,@ExceptionList
  ,@MailStats
  ,@SqlVersion
  ,@Separator
;

  • The recipient is the emailaddress where to send the results to.
  • The ExceptionList is a comma separated list of servers that should not be monitored. 
  • Mailstats = 1 means that you will recieve an email if there is no useful information te report, telling you exactly this. If you set this to 0 then no email is sent unless there is something to report.
  • SqlVersion = 0 means report all (possible) Sql Server Verions. If you set this to 11, then only Sql Server 2012 will be monitored and reported.
  • Seperator is the separator character for the comma separated list. You can choose any single character for this.

We recommend that you schedule this procedure on a daily basis after the Monitor Servers procedure has run. Any servers not found by the Monitor Servers will be skipped, saving time.

Collected Information

  • LinkedServer: The name of the Linked Server that points to this server.
  • DatabaseName: The name of the database found on the Linked Server
  • RecoveryModel: Full / Simple / Bulk logged
  • DateStart: The first time the database was found.
  • DateStop: The first time the database was not found anymore. If found again a new record will be created.
  • QuotedID: Quoted identifier On / Off
  • DBStatus: Status of the database Online / Offline / readonly
  • UserAccess: Multi user (default) / Single User
  • AutoClose: On / Off
  • AutoShrink: On / Off

Future versions of Sequenchel may collect additional properties for databases.