SQL Server: how to monitor activity in several databasesMonitor MySQL activity per database?Disabling logging...

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.










share|improve this question




















  • 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


















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.










share|improve this question




















  • 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
















0












0








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.










share|improve this question
















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















share|improve this question













share|improve this question




share|improve this question








edited 1 hour ago









Tony Hinkle

2,4801422




2,4801422










asked 1 hour ago









opensasopensas

13914




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
















  • 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










1




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





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




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







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







It's a mix of several versions, most ancient one is sql server 2008

– opensas
17 mins ago












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
























  • 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











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


















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















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






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
























  • 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
















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
























  • 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














2












2








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













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












share|improve this answer



share|improve this answer










answered 1 hour ago









HandyDHandyD

919112




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



















  • 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

















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





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


















draft saved

draft discarded




















































Thanks for contributing an answer to Database Administrators Stack Exchange!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














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















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

ORA-01691 (unable to extend lob segment) even though my tablespace has AUTOEXTEND onORA-01692: unable to...

Always On Availability groups resolving state after failover - Remote harden of transaction...

Circunscripción electoral de Guipúzcoa Referencias Menú de navegaciónLas claves del sistema electoral en...