SQL Server Database Inventory

The best way I'm seeing to do this is to create a trigger. Here's an example that you can use to work on it:

CREATE TRIGGER trg_createdatabase   
ON ALL SERVER   
FOR CREATE_DATABASE   
AS  
	INSERT INTO inventorydb.dbo.databases (databasename, createdby, creationdate)
	SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'), SUSER_SNAME(), GETDATE() 
GO  

Select allOpen in new window

If i create this trigger in a central server and a central database then how can it loop through all SQL servers like if any event happen then capture. So do i have to create this Trigger all my server environment. My goal was to get all servers and database information just in a single server like Central Server.CentralDatabase.CentralTable


Example:--
ServerName, DatabaseName, Database Owner
00001,xxx, xxx
00002,xbb,sddl
0003, wee, wee

i understand that i have to create linked server as well as set this trigger on every server. I created tirgger one of my server but when i create a database then i got the following error.

OLE DB provider "SQLNCLI11" for linked server "xxxxxx" returned message "The transaction manager has disabled its support for remote/network transactions.".

Msg 7391, Level 16, State 2, Procedure trg_createdatabase, Line 5
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "xxxxxxx" was unable to begin a distributed transaction.
Msg 1203, Level 20, State 1, Line 2

I already checked Distributed Transaction Coordinator is running

Process ID 52 attempted to unlock a resource it does not own: DATABASE: 12:0 . Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

My Linked Server connection is fine and linked server login is mapped using SQL Login. i can pull the data
select * from [xxxxxxxxx].dbname.dbo.tablename something like this

But when i tried to create database using different user name or windows account then


Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Process ID 58 attempted to unlock a resource it does not own: DATABASE: 11:0 . Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
Cannot continue the execution because the session is in the kill state.
A severe error occurred on the current command.  The results, if any, should be discarded. (.Net SqlClient Data Provider)


if i use sql login to create a new db then

OLE DB provider "SQLNCLI11" for linked server "xxxxxx" returned message "The transaction manager has disabled its support for remote/network transactions.".

Msg 7391, Level 16, State 2, Procedure trg_createdatabase, Line 5
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "xxxxxxx" was unable to begin a distributed transaction.
Msg 1203, Level 20, State 1, Line 2



Process ID 52 attempted to unlock a resource it does not own: DATABASE: 12:0 . Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Hi Vitor,

Could you please help me if i want to add Status, size, RecoveryModel, Compatibility level within the query

INSERT INTO LinkedServerName.inventorydb.dbo.databases (servername, databasename, createdby, creationdate)
      SELECT @@SERVERNAME, EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'), SUSER_SNAME(), GETDATE()

This is worked for me. Thanks!