question

Bit-101 avatar image
0 Votes"
Bit-101 asked Cathyji-msft commented

Read SQL logs

I´m a beginner.

I only want to view when a certain admin in SQL done somethings for about 2 weeks ago.
Where in SQL should I looked and is there any easy way to search for something like this

Hope, this is the right forum for my question.

:)

sql-server-generalsql-server-reporting-services
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Bit-101,

We have not received a response from you. Did the reply(s) could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·

Hi @Bit-101,

Any update for this thread?

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered pituach edited

Good day Peeter and welcome to the QnA forum :-)

I only want to view when a certain admin in SQL done somethings for about 2 weeks ago. Where in SQL should I looked and is there any easy way to search for something like this

Like most general questions, the answer is: it depend. What exactly do your want to view? Are you looking for actions in the past or in the future (get information which was stored by default or monitor actively for future action). by default, SQL Server do not stores all actions or even monitor all, since it will cost resources and reduce performance.

First point is probably what you are looking - reading the logs, and you can keep reading for for getting more information or monitor future actions.

(1) (most common and for most users) Actions like errors and events that are captured you can view using SSMS as fully explained in this document:

https://docs.microsoft.com/en-us/sql/relational-databases/logs/open-log-file-viewer?view=sql-server-ver15

This is a nice tutorial for using SQL Server Audit (Database Engine)
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15

(2) Actions which related to changing data or database structure are logged in the transaction log for internal use. You can watch them using undocumented tools, but this usually require a deep understanding and it is an advance task.

For example, in the following post I show how to find objects which were created ONLINE. This came as an answer to an issue which someone in the forum had when he used Transaction Replication from SQL Server Enterprise to SQL Server Standard. It was an advance question and more advance answer.
https://ariely.info/Blog/tabid/83/EntryId/302/SQL-Server-Transaction-Replication-from-Enterprise-to-Standard-error-Online-index-operations-can-only-be-performed-in-Enterprise-edition.aspx

Another example where I show a practical need to read the transaction log is when someone wanted to find who is the user that deleted rows from the table (not SQL Server user but the actual user of the OS) as you can see in this post.
https://ariely.info/Blog/tabid/83/EntryId/154/SQL-Server-Who-deleted-my-record-last-time.aspx

I have more examples of getting information from the transaction log file using the undocumented function fn_dblog and fn_dump_dblog, and like me other people in the forum have such posts - use Google to find more if this is relevant but it seems too advance for a beginner.

(3) Many actions which are done in the database are not really done physically. For example if your admin deleted rows, remove column and so on then in many cases you can find the data of these rows or column in the database file - again, very advance topic to read the binary data directly from the data file.

(4) In most cases you know in advance that you need to monitor what is going to happen. This is much more simple case since there are tools to monitor each action in the server and the database. These tools cost resource and it is not recommended to continue the monitoring for long time or if not a must.

The most common and flexible option is to use Extended Events. You can read more in the following document:
https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver15

There are other tools which you can use like system tables and views.

We need to know what exactly you are looking for.

(5) and you always can use a third party tools (there are many great professional tools out there) - use google to find these




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @Bit-101,

You can using ApexSQL tool to read the SQL server transaction log. The result is as below screenshot.

141975-screenshot-2021-10-20-161508.jpg


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.