Sfrgttk

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

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

Publication rates for different areas of mathematics?

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

Does an increasing sequence of reals converge if the difference of consecutive terms approaches zero?

Run a command that requires sudo after a time has passed

Why are energy weapons seen as more acceptable in children's shows than guns that fire bullets?

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

How to write pow math?

Badly designed reimbursement form. What does that say about the company?

How to explain one side of Super Earth is smoother than the other side?

What have we got?

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

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

Why Third 'Reich'? Why is 'reich' not translated when 'third' is? What is the English synonym of reich?

Sing Baby Shark

Not Developing a Piece in the First Five Moves

Are there any rules or guidelines about the order of saving throws?

The totem pole can be grouped into

Ethernet cable only works in certain positions

How to make clear what a part-humanoid character looks like when they're quite common in their world?

Can a planet be tidally unlocked?

Could a route among the asteroids be built to reach Jupiter's moons once the technology to reach and set up a base in Mars is ready?

Can you make a Spell Glyph of a spell that has the potential to target more than one creature?

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
  17 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
  17 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
  22 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
  22 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
  22 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