Some time back one of our application teams complaint that their application is breaking down every weekend because of maximum connections reached from application side to the database (There application had some maximum limit of connections to me made to database at one time). Application users during weekend are supposed to be very less. So, who is trying to make so many connections from application to the database in the weekend?
So, as a DBA we got a task to keep monitoring the connections to this database over the weekend and collect data related to the connections made to this database over the weekend. In order to collect this data we planned to automate this process. We collected this connections data of this production database and stored it in one of the database in test server so that the output can be analyzed as required.
NOTE – We will be collecting data for all the connections made to database DBSource residing on the instance Source_instance. This data will be collected and stored in output table in DBSource_Data database residing on instance Destination_Instance.
Steps we did in order to automate this process is as follows: -
1) Connect to database instance Destination_Instance and create a database named DBSource_Data
2) Now create a table ‘output’ in this newly created database
CREATE TABLE [dbo].[output](
[JobRunTime] [datetime] NULL,
[ProcessId] [smallint] NULL,
[UserLoginTime] [datetime] NULL,
[LastActivityTime] [datetime] NULL,
[ConnectionStatus] [nchar](30) NULL,
[Hostname] [nchar](128) NULL,
[ProgramName] [nchar](128) NULL,
[UserLoginName] [nchar](128) NULL,
[SQLQuery] [nvarchar](max) NULL
) ON [PRIMARY]
3) Create a login Test_123 and minimum of grant ‘data_reader’ and ‘data_writer’ roles on DBSource_Data database. We will be using this Login, when we create linked server.
4) Connect to Source_instance database instance and create a linked server by name ‘LinkedServerName’. Here we make use of Microsoft OLEDB provider for SQL Server drivers.
EXEC master.dbo.sp_addlinkedserver @server = N’ LinkedServerName ‘, @srvproduct=N’SQLServer’, @provider=N’SQLNCLI’, @datasrc=N’Destination_Instance’
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’LinkedServerName’,@useself=N’False’,@locallogin=NULL,@rmtuser=N’Test_123′,@rmtpassword=’########’
Replace ######## with the password of Test_123 account created in last step.
5) Now create a SQL Server agent job and run the below script as the only step of this job. This script will collect the list of connections made to DBSource database and will insert it in the ‘output’ table in DBSource_Data database.
insert openquery (LinkedServerName, ”select * from DBSource_Data.dbo.output”)
select GETDATE() ,spid, login_time,last_batch, status, hostname, PROGRAM_NAME, loginame, st.text from sysprocesses SP
CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS ST
where SP.dbid =<Database_id>
Replace Database_id with the database id of DBSource database for which we have to collect the connection details.
More details on the above select query can be found here…..
Schedule this job to run every 10-minutes so that it will collect the connection details and store it in the DBSource_Data database in Source_instance database instance.