0

We are finally planning to close down our last SQL Server 2008 R2 server. Before we can proceed with this we need to track what users are connecting to which databases. We have tried different options like using SQL Audit and checking the logins in the error log but all of these are missing the information to which database the connection are made. We did also look into using the LOGON trigger but as it is triggered when the user logins to the instance it will not track if the user afterwards change the database.

In newer versions I have used Extended Events for this kind of auditing but the sqlserver.login events is not yet available in this database version. It seems like it should be possible to use Profiler for this but I'm worried about continuously running trace specially on this old server. Are there any other options that I have missed or is Profiler the only option?

2
  • 1
    Server side trace is a good option for SQL 2008/R2. Also, you could try XE event sqlserver.database_transaction_begin to capture database usage?
    – Milan
    Feb 9, 2021 at 9:07
  • Thanks for for the suggestion. I'm asuming that database_transaction_begin will work if we are interested in users openes transaction? That will unfortunately not work for us as we are also interested in users that reads data.
    – Peter Å
    Feb 9, 2021 at 10:13

3 Answers 3

1

Using an event session below you could get info about comleted batches. The information includes database_id and username:

CREATE EVENT SESSION [QueryBatchTracking] ON SERVER 

ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.nt_username,sqlserver.session_id,sqlserver.username,sqlserver.sql_text)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),

ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.nt_username,sqlserver.session_id,sqlserver.username,sqlserver.sql_text)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),

ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.nt_username,sqlserver.session_id,sqlserver.username,sqlserver.sql_text)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0))))

ADD TARGET package0.asynchronous_file_target(SET filename=N'd:\ExEvents\QueryBatchTracking.xet')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

ALTER EVENT SESSION QueryBatchTracking 
ON SERVER STATE = START 
GO

Apart from getting only login events, you will get info about actions the user performed.

2
  • Thanks, that is of course when way of doing it. The question is how much stress will this put on the server and how large will the event file grow? Absolutely something i will look into.
    – Peter Å
    Feb 9, 2021 at 12:49
  • 1
    If to talk of us - no stress, the influence on our system was absolutely imperceptible. To estimate the file size I'd suggest start the session for some short period on your particular environment. Feb 9, 2021 at 13:36
1

I faced a similar problem a couple of years ago and created a SQL Agent job to run the query below every few minutes. The query polled sys.dm_exec_sessions, sys.sysprocesses, sys.dm_exec_connections it's a bit rough and ready but it met my purposes and might help you too

CREATE TABLE [dbo].[log_db_connections](
[Database] [nvarchar](128) NULL,
[Login] [nvarchar](128) NOT NULL,
[nt_user_name] [nvarchar](128) NULL,
[LoginTime] [datetime] NOT NULL,
[CollectionTime] [datetime] NOT NULL CONSTRAINT [DF_CollectionTime] DEFAULT GETDATE(),
[Program] [nvarchar](128) NULL,
[client_interface_name] [nvarchar](32) NULL,
[Host] [nvarchar](128) NULL,
[client_net_address] [varchar](48) NULL,
[client_tcp_port] [int] NULL,
[net_transport] [nvarchar](40) NOT NULL,
[net_packet_size] [int] NULL,
[protocol_type] [nvarchar](40) NULL,
[endpoint_id] [int] NULL,
[encrypt_option] [nvarchar](40) NOT NULL,
[auth_scheme] [nvarchar](40) NOT NULL,
[num_reads] [int] NULL,
[num_writes] [int] NULL
) ON [PRIMARY]
GO



-- Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicates
CREATE UNIQUE CLUSTERED INDEX [CIX_log_db_connections] ON log_db_connections
(
    [Database] ASC,
    Login ASC,
    Host ASC,
    Program ASC,
    LoginTime ASC
) WITH (IGNORE_DUP_KEY = ON)

INSERT [dbo].[log_db_connections]
(
[Database],
[Login],
[nt_user_name],
[LoginTime],
[Program],
[client_interface_name],
[Host],
[client_net_address],
[client_tcp_port],
[net_transport],
[net_packet_size],
[protocol_type],
[endpoint_id],
[encrypt_option],
[auth_scheme],
[num_reads],
[num_writes]
)
SELECT
DB_NAME(p.dbid) ,
s.login_name ,
nt_user_name,
s.login_time as LoginTime,
s.[program_name] as Program,
s.client_interface_name,
[host_name] as Host,
c.client_net_address,
c.client_tcp_port,
c.net_transport,
c.net_packet_size,
c.protocol_type,
c.endpoint_id,
c.encrypt_option,
c.auth_scheme,
c.num_reads,
c.num_writes
FROM sys.dm_exec_sessions s
inner join sys.sysprocesses p on
s.session_id = p.spid
inner join sys.dm_exec_connections c on
s.session_id = c.session_id
WHERE s.[program_name] not like 'Microsoft SQL Server Management Studio%'
2
  • This seems to be something we could work with. The question is how often do you need to run it to be able to get the users that only connects and run short queries? Do you have any experiance with that kind of enviroment?
    – Peter Å
    Feb 9, 2021 at 10:20
  • 1
    I think I had it scheduled to run every 2 minutes, but I ran it for a week so the chances of a login/user falling entirely through the gaps were pretty small. Feb 9, 2021 at 13:03
1

I guess my question is why do you need to know which databases are being connected to by the Users? Knowing which Users are logging into the server should likely answer all the questions you need for your new server, since you're planning to shut down the entire old server anyway. Once you know the Logins of the Users connecting to the server, you can view their Mapped Databases via the Security folder at the Server level in SSMS and presumably you'd want to map your Users to the same databases in your new server. No need to granularly try to log which databases are being connected to.

Additionally, if a user connects to Database A but runs a query that accesses a table from Database B (since cross-database querying is surely possible) which database would you want to know that User used? E.g:

USE DatabaseA;

SELECT SomeColumn 
FROM DatabaseB.dbo.Table1

The above runs in the context of DatabaseA.

6
  • Excellent question. We have a large number of databases on the server and most of them are only used for reading data. When closing down the server we need to know which ones are in use and which one are not in use. It is true that even if we get the connection information we don't get the data if they are using cross-database querying but at least we get a good starting point.
    – Peter Å
    Feb 10, 2021 at 5:53
  • 1
    @PeterÅ Gotcha, yea in that case you'll want to do some kind of events auditing for a period of time (whatever you guys are comfortable with calling conclusive, a week?...a month? etc) to see what kind of queries are being ran. You can use either of the other answers to accomplish that or even use the profiler. As a starting point you might want to look at the database User Mapping for each Login as I mentioned, and if there's a database no one is mapped to, it might be safe...
    – J.D.
    Feb 10, 2021 at 12:27
  • 1
    ...to assume that one isn't in use anymore. If you have the possibility, once you guys have your list of databases that are no longer in use, you should take those databases offline but leave the server up temporarily, to perform a scream test and see if any users complain of any issues. Scream tests tend to be the most telling / complete way to know what's still in use.
    – J.D.
    Feb 10, 2021 at 12:29
  • Sure thing, the profiler is one option. As we are not sure about the user profiles we need to do the monitoring for if not months at least weeks. I'm little bit worried about leaving the profiler on for that long period. We now will need to test these different options.
    – Peter Å
    Feb 10, 2021 at 12:37
  • 1
    The scream test could be an option but I should not like to start with that. Maybe run some audit for a short time and handle the rest with the scream test. Thanks for the idea.
    – Peter Å
    Feb 10, 2021 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.