Sfrgttk

Is it possible to detect 100% of SQLi with a simple regex?

Sing Baby Shark

Is the following statement true, false, or can't be determined? Why?

Which was the first story to feature space elevators?

Is 'bad luck' with former employees a red flag?

Negotiating 1-year delay to my Assistant Professor Offer

Apparently I’m calling random numbers but nothing in call log?

Is candidate anonymity at all practical?

Coworker is trying to get me to sign his petition to run for office. How to decline politely?

Simple Question About Conservation of Angular Momentum

Who, if anyone, was the first astronaut to return to earth in a different vessel?

Why do climate experts from the UN/IPCC rarely mention Grand Solar Minimum?

Why is Shelob considered evil?

The totem pole can be grouped into

What happens if you declare more than $10,000 at the US border?

Why does calling Python's 'magic method' not do type conversion like it would for the corresponding operator?

How to not forget my phone in the bathroom?

Identical projects by students at two different colleges: still plagiarism?

Why did Tywin never remarry?

Is it appropriate to give a culturally-traditional gift to a female coworker?

How to announce in an ATIS message that two parallel runways are in use?

Buying a "Used" Router

Is there a technology capable of disabling the whole of Earth's satellitle network?

Should corporate security training be tailored based on a users' job role?

SQL Server: how to monitor activity in several databases

Monitor MySQL activity per database?Disabling logging on certain tablesSQL Server - Check for opened Activity MonitorQuery to get total time logged in per weekHuge data and performance in SQL ServerSQL Server: Using Dual Databases for Performance?Database logging optimization and maintenance in SQL ServerAscending Key Problem - Leading column branded “Stationary” - SQL ServerAggregate an AggregateSQL Server backups are suddenly smaller

0

I have several dbs in production and I'd like to have a high level overview of the activity.

Ideally I'd like to know:

• number of users accessing the db per day


• number of inserts per table per day


• number of updates per table per day

I thought about a little script that would run a query to get the rowcount of each table, and then save it to another db. I could execute it at night.

That would only give the number of inserts, but at least would be a first step.

Is there some tool, logging device, system table, or something that would let me get this kind of info?

I have SQL Server from 2008 onward.

sql-server transaction-log monitoring

share|improve this question

edited 1 hour ago

Tony Hinkle

2,4801422

asked 1 hour ago

opensasopensas

13914

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  What edition of SQL Server? Also, are your users accessing the databases with a unique user, or through a shared application user?
  
  – HandyD
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Accessing is tough. SELECTs aren’t logged anywhere so you’d have to enable auditing or something to capture those. And if you have a query (or batch) that accesses 2 tables in db1, 3 tables in db2, and 2 tables in db3, how many “accesses” are those? Where? For updates/inserts you can like at sys.dm_db_index_usage/operational_stats - this doesn’t tell you how many rows but it does track insert/update/delete operations.
  
  – Aaron Bertrand♦
  1 hour ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It's a mix of several versions, most ancient one is sql server 2008
  
  – opensas
  12 mins ago
  
  
  

  
  
  
  

add a comment | 

0

I have several dbs in production and I'd like to have a high level overview of the activity.

Ideally I'd like to know:

• number of users accessing the db per day


• number of inserts per table per day


• number of updates per table per day

I thought about a little script that would run a query to get the rowcount of each table, and then save it to another db. I could execute it at night.

That would only give the number of inserts, but at least would be a first step.

Is there some tool, logging device, system table, or something that would let me get this kind of info?

I have SQL Server from 2008 onward.

sql-server transaction-log monitoring

share|improve this question

edited 1 hour ago

Tony Hinkle

2,4801422

asked 1 hour ago

opensasopensas

13914

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  What edition of SQL Server? Also, are your users accessing the databases with a unique user, or through a shared application user?
  
  – HandyD
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Accessing is tough. SELECTs aren’t logged anywhere so you’d have to enable auditing or something to capture those. And if you have a query (or batch) that accesses 2 tables in db1, 3 tables in db2, and 2 tables in db3, how many “accesses” are those? Where? For updates/inserts you can like at sys.dm_db_index_usage/operational_stats - this doesn’t tell you how many rows but it does track insert/update/delete operations.
  
  – Aaron Bertrand♦
  1 hour ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It's a mix of several versions, most ancient one is sql server 2008
  
  – opensas
  12 mins ago
  
  
  

  
  
  
  

add a comment | 

I have several dbs in production and I'd like to have a high level overview of the activity.

Ideally I'd like to know:

• number of users accessing the db per day


• number of inserts per table per day


• number of updates per table per day

I thought about a little script that would run a query to get the rowcount of each table, and then save it to another db. I could execute it at night.

That would only give the number of inserts, but at least would be a first step.

Is there some tool, logging device, system table, or something that would let me get this kind of info?

I have SQL Server from 2008 onward.

sql-server transaction-log monitoring

share|improve this question

edited 1 hour ago

Tony Hinkle

2,4801422

asked 1 hour ago

opensasopensas

13914

share|improve this question

edited 1 hour ago

Tony Hinkle

2,4801422

asked 1 hour ago

opensasopensas

13914

share|improve this question

edited 1 hour ago

Tony Hinkle

2,4801422

asked 1 hour ago

opensasopensas

13914

edited 1 hour ago

Tony Hinkle

2,4801422

edited 1 hour ago

Tony Hinkle

2,4801422

asked 1 hour ago

opensasopensas

13914

asked 1 hour ago

opensasopensas

13914

1 Answer
1

active

oldest

votes

2

To track the inserts and updates, you can use the below query which will collect the SUM of inserts, updates and deletes against the HEAP or CLUSTERED INDEX for each user table in the database.

Log this to a table and then query the deltas over time to see the insertsupdatesdeletes per day. NOTE: This would have to be run against each database you wish to monitor as these DMVs are database-scoped.

USE [Database]

GO

SELECT 

    DB_NAME() AS [DATABASE NAME],

    SUM(A.LEAF_INSERT_COUNT) AS INSERTS, 

    SUM(A.LEAF_UPDATE_COUNT) AS UPDATES, 

    SUM(A.LEAF_DELETE_COUNT) AS DELETES

FROM

    SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A 

    INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID

WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

    AND I.INDEX_ID IN (0,1)

The user access is a little harder. If your users access the databases through an application, and that application uses a single login for SQL Server access, then you can't just track logins to determine user access. You would need to track this via your application.

If you're using individual logins, then you could use a login trigger to add a record to a tracking table to record the user, login time and initial database. See the documentation for more info. A good example can be found here. This is actually for logging sysadmin logins, but could easily be modified for your purpose.

share|improve this answer

answered 1 hour ago

HandyDHandyD

919112

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Great tip, there are different approaches to how the logins are treated, so I understand that it will be harder to get the user count in a general way, but getting how many times anybody logs to the db would be pretty useful
  
  – opensas
  17 mins ago
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230191%2fsql-server-how-to-monitor-activity-in-several-databases%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

2

To track the inserts and updates, you can use the below query which will collect the SUM of inserts, updates and deletes against the HEAP or CLUSTERED INDEX for each user table in the database.

Log this to a table and then query the deltas over time to see the insertsupdatesdeletes per day. NOTE: This would have to be run against each database you wish to monitor as these DMVs are database-scoped.

USE [Database]

GO

SELECT 

    DB_NAME() AS [DATABASE NAME],

    SUM(A.LEAF_INSERT_COUNT) AS INSERTS, 

    SUM(A.LEAF_UPDATE_COUNT) AS UPDATES, 

    SUM(A.LEAF_DELETE_COUNT) AS DELETES

FROM

    SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A 

    INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID

WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

    AND I.INDEX_ID IN (0,1)

The user access is a little harder. If your users access the databases through an application, and that application uses a single login for SQL Server access, then you can't just track logins to determine user access. You would need to track this via your application.

If you're using individual logins, then you could use a login trigger to add a record to a tracking table to record the user, login time and initial database. See the documentation for more info. A good example can be found here. This is actually for logging sysadmin logins, but could easily be modified for your purpose.

share|improve this answer

answered 1 hour ago

HandyDHandyD

919112

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Great tip, there are different approaches to how the logins are treated, so I understand that it will be harder to get the user count in a general way, but getting how many times anybody logs to the db would be pretty useful
  
  – opensas
  17 mins ago
  

  
  
  
  

add a comment | 

2

To track the inserts and updates, you can use the below query which will collect the SUM of inserts, updates and deletes against the HEAP or CLUSTERED INDEX for each user table in the database.

Log this to a table and then query the deltas over time to see the insertsupdatesdeletes per day. NOTE: This would have to be run against each database you wish to monitor as these DMVs are database-scoped.

USE [Database]

GO

SELECT 

    DB_NAME() AS [DATABASE NAME],

    SUM(A.LEAF_INSERT_COUNT) AS INSERTS, 

    SUM(A.LEAF_UPDATE_COUNT) AS UPDATES, 

    SUM(A.LEAF_DELETE_COUNT) AS DELETES

FROM

    SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A 

    INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID

WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

    AND I.INDEX_ID IN (0,1)

The user access is a little harder. If your users access the databases through an application, and that application uses a single login for SQL Server access, then you can't just track logins to determine user access. You would need to track this via your application.

If you're using individual logins, then you could use a login trigger to add a record to a tracking table to record the user, login time and initial database. See the documentation for more info. A good example can be found here. This is actually for logging sysadmin logins, but could easily be modified for your purpose.

share|improve this answer

answered 1 hour ago

HandyDHandyD

919112

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Great tip, there are different approaches to how the logins are treated, so I understand that it will be harder to get the user count in a general way, but getting how many times anybody logs to the db would be pretty useful
  
  – opensas
  17 mins ago
  

  
  
  
  

add a comment | 

To track the inserts and updates, you can use the below query which will collect the SUM of inserts, updates and deletes against the HEAP or CLUSTERED INDEX for each user table in the database.

Log this to a table and then query the deltas over time to see the insertsupdatesdeletes per day. NOTE: This would have to be run against each database you wish to monitor as these DMVs are database-scoped.

USE [Database]

GO

SELECT 

    DB_NAME() AS [DATABASE NAME],

    SUM(A.LEAF_INSERT_COUNT) AS INSERTS, 

    SUM(A.LEAF_UPDATE_COUNT) AS UPDATES, 

    SUM(A.LEAF_DELETE_COUNT) AS DELETES

FROM

    SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A 

    INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID

WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

    AND I.INDEX_ID IN (0,1)

The user access is a little harder. If your users access the databases through an application, and that application uses a single login for SQL Server access, then you can't just track logins to determine user access. You would need to track this via your application.

If you're using individual logins, then you could use a login trigger to add a record to a tracking table to record the user, login time and initial database. See the documentation for more info. A good example can be found here. This is actually for logging sysadmin logins, but could easily be modified for your purpose.

share|improve this answer

answered 1 hour ago

HandyDHandyD

919112

USE [Database]

GO

SELECT 

    DB_NAME() AS [DATABASE NAME],

    SUM(A.LEAF_INSERT_COUNT) AS INSERTS, 

    SUM(A.LEAF_UPDATE_COUNT) AS UPDATES, 

    SUM(A.LEAF_DELETE_COUNT) AS DELETES

FROM

    SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A 

    INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID

WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

    AND I.INDEX_ID IN (0,1)

share|improve this answer

answered 1 hour ago

HandyDHandyD

919112

answered 1 hour ago

HandyDHandyD

919112

answered 1 hour ago

HandyDHandyD

919112