Просмотр журнала транзакций MS SQL Server
Для просмотра журнала транзакций MS SQL Server используется недокументированная функция DBCC:
Результат функции DBCC:
Важно понимать, что журнал транзакций не предназначен для пользователей и сисадминов баз данных. Это служебная фишка MS SQL Server. Данные в журнале хранятся в шестнадцатиричном виде. Но поддаются визуальному представлению в удобном виде с помощью платной программы ApexSQL Log.
How to view transaction logs in SQL Server 2008 [closed]
We don’t allow questions seeking recommendations for books, tools, software libraries, and more. You can edit the question so it can be answered with facts and citations.
Closed 7 years ago .
I need to view the transaction logs of a database on SQL Server 2008 in order to find a delete transaction and hopefully roll it back.
Unfortunately I have no clue where to start, and I’m finding it difficult to determine which are good articles on Google.
What should I do?
3 Answers 3
You could use the undocumented
For example, DBCC LOG(database, 1)
You could also try fn_dblog.
For rolling back a transaction using the transaction log I would take a look at Stack Overflow post Rollback transaction using transaction log.
You can’t read the transaction log file easily because that’s not properly documented. There are basically two ways to do this. Using undocumented or semi-documented database functions or using third-party tools.
Note: This only makes sense if your database is in full recovery mode.
DBCC LOG and fn_dblog — more details here and here.
You can also check out several other topics where this was discussed:
I accidentally deleted a whole bunch of data in the wrong environment and this post was one of the first ones I found.
Because I was simultaneously panicking and searching for a solution, I went for the first thing I saw — ApexSQL Logs, which was $2000 which was an acceptable cost.
However, I’ve since found out that Toad for Sql Server can generate undo scripts from transaction logs and it is only $655.
Lastly, found an even cheaper option SysToolsGroup Log Analyzer and it is only $300.
Чтение журнала транзакций SQL Server
Журнал транзакций SQL Server содержит подробную информацию обо всех операциях, совершённых в базе данных. Этой информации достаточно, чтобы восстановить базу данных на определённый момент времени, повторно воспроизвести все операции над данными или отменить их. Но как просмотреть эту информацию, найти конкретную транзакцию в журнале, определить, что именно происходило в базе и откатить какие-нибудь изменения, например, восстановить случайно удалённые записи?
Разобраться в той информации, которая хранится в журнале транзакций или в резервной копии журнала транзакций не так просто.
Если открыть файл журнала транзакций *.LDF или файл резервной копии журнала *.TRN в любом двоичном редакторе, то информация, которую вы увидите, будет мало чем информативна. Ниже представлен фрагмент LDF-файла:
Функция fn_dbblog
fn_dblog – это недокументированная функция SQL Server, которая позволяет просматривать активную часть журнала транзакций в режиме реального времени.
Давайте посмотрим, как с ней работать:
-
Выполните функцию fn_dblog
Функция возвращает 129 столбцов, поэтому желательно сузить результирующий набор по необходимым наборам полей и по возможности ограничить выборку только нужным типом транзакций
Из всего набора данных, который возвращает функция fn_dblog выведем только нужные транзакции.
Например, выберем только транзакции на вставку строк в таблицу:
Чтобы увидеть транзакции на удаление строк, выполните следующий скрипт:
Информация по вставленным или удалённым строкам храниться в столбцах – RowLog Contents 0, RowLog Contents 1, RowLog Contents 2, RowLog Contents 3, RowLog Contents 4, Description и Log Record
Для каждого типа транзакций используются разные столбцы, для того, чтобы получить нужную вам информацию вы должны точно знать какие столбцы используются для каких транзакций, а сделать это не просто, так, как официальной документации с описанием нет.
Вставленные и удаленные строки хранятся в шестнадцатеричных значениях. Для того, чтобы вытащить данные из этих значений вы должны знать формат хранения, понимать биты состояний, знать общее количество столбцов и так далее.
fn_dbLog замечательный бесплатный инструмент для чтения журнала транзакций, но эта функция имеет ряд ограничений – разобраться в данных достаточно сложно, т.к. среди прочей информации содержатся записи, связанные с системными таблицами, функция отображает только активную часть журнала и не отображает информацию по обновлению BLOB-значений.
Операция UPDATE при минимальном протоколировании журнала транзакций не содержит полное значение, которое было до и после изменений, а хранит только то, что изменилось (SQL Server может записать, что изменилось значение “G” на “D”, хотя в действительности изменилось слово “GLOAT” на “FLOAT”). В этом случаи вам потребуется вручную восстанавливать все промежуточные состояния записи на странице от первой её вставки до момента, который вас интересует.
При удалении BLOB-объектов сами объекты не записываются в журнал, а лишь фиксируется факт удаления. Для восстановления, удалённого BLOB-объекта вам необходимо найти в журнале пару для этого удаления, которой является ранее осуществлённая вставка, а она скорее всего уже не содержится в активной части журнала.
Функция fn_dump_dblog
fn_dump_dblog – это ещё одна недокументированная функция, которая позволяет просматривать журнал транзакций из резервной копии журнала транзакций, как сжатого, так и обычного.
-
Ниже пример запуска функции fn_dump_dblog, обратите внимание, что необходимо указать все её 63 параметра
Т.к. функция fn_dump_dblog возвращает так же, как и fn_dblog 129 столбцов, то желательно сократить этот набор полей
Но вам потребуется опять расшифровать шестнадцатеричные значения, чтобы найти искомые записи
И вы опять получаете те же самые ограничения, что и при работе с функцией fn_dblog.
Для восстановления БД из копии журнала транзакций до определённого момента времени или до конкретной транзакции, вам необходимо:
DBCC PAGE
Ещё одна полезная команда DBCC PAGE, но также, как и две предыдущих функции –недокументированная. Она позволяет просматривать содержимое файлов MDF и LDF. Её синтаксис:
Для просмотра содержимого первой страницы журнала транзакций БД AdventureWorks2012, необходимо выполнить:
В качестве результата вы получите сообщение:
По умолчанию результат команды DBCC PAGE не выводится в SQL Server Management Studio и для её отображения первым шагом необходимо включить флаг трассировки 3604:
И теперь повторно выполните команду:
Вы увидите несколько ошибок и заголовок страницы, которые можно проигнорировать. Ниже вы получите шестнадцатеричное отображение LDF-файла:
Полученный результат ничем не отличается от того, который вы можете получить в любом hex-редакторе, а может быть даже и в менее наглядном виде. Главное отличие – это возможность просматривать файл в режиме реального времени, без отключения БД, но дружелюбным такой формат никак нельзя назвать.
Use ApexSQL Log
ApexSQL Log – это инструмент, который позволяет работать с журналом транзакций SQL Server в наглядном виде. Он позволяет просматривать текущий журнал транзакций в режиме реального времени, обращаться к резервным копиям журнала транзакций, как обычным, так и созданных в режиме компрессии. При этом приложение самостоятельно считывает данные из резервных копий БД, чтобы получить всю необходимую информацию для успешного восстановления. С помощью ApexSQL Log вы можете просматривать цепочки транзакций, которые произошли в вашей БД, даже те, которые были совершены до установки утилиты. В отличии от недокументированных и неподдерживаемых функций, рассмотренных выше, вы получите наглядную информацию о том, какие операции происходили над объектами, сможете увидеть старое и новое значение.
-
Запустите ApexSQL Log
Подключитесь к базе данных, чей журнал транзакций вы хотите проанализировать
На шаге Select SQL logs to analyze, выберите записи, которые нужно прочитать. Убедитесь, что они образуют полную цепочку
Используйте фильтр на шаге Filter setup, чтобы уменьшить количество считываемых транзакций с помощью указания временного диапазона, типа операций, таблицы и другие фильтры
Нажмите Open
Полный результат можно будет увидеть в табличном виде
Вы сможете отследить, когда операция началась и когда закончилась, тип операции, схему и объект, над которым произошла операция, имя пользователя, совершившего эту операцию, а также имя компьютера и приложения из которого эта операция была совершена. Для операций обновления (UPDATE) вы сможете увидеть, как новое, так и старое значение.
Чтобы избежать нечитаемых шестнадцатеричных значений, недокументированных функций, непонятного содержимого колонок, запросов со сложной конструкцией, сложных сценариев получения данных, неполных данных операций UPDATE, а также проблем с получением BLOB значений из журнала транзакций SQL Server, используйте программу ApexSQL Log. Она за вас выполнит все сложные операции и предоставит результат в читабельном виде. Кроме того, она позволит вам с помощью одного нажатия отменить или повторно выполнить нужную транзакцию.
Ingenious Guide to View Log File of SQL Server
Microsoft SQL Server application is one of the biggest waves in the relational database management system and handles huge database in a well-structured manner.
Nowadays, Ex-employees or hackers intentionally modifies the values of databases in order to damage the organization assets. And it becomes difficult to analyze or examine who is the culprit manually. As a result, the Organizations run into big trouble.
In SQL Server, there is a transaction Log file that keep records of all transactions & modifications in database executed on a database in a Microsoft SQL Server. By reading the Log file, one can easily check who deleted data from table in SQL Server database. Plus, it is used by forensic investigator to examine SQL Server Transaction Log and view & check every log detail in a detailed manner. In short, with SQL Log file, it becomes easy to find out which query performed on which table at what time.
Here, we are going to answer how to view log file of SQL Server by using various workarounds. Just go through this article once and understand how to open or read transaction log file in Microsoft SQL Server 2017 / 2016 / 2014 / 2012 / 2008 / 2008 R2 / 2005.
Moreover, if user want to restore the deleted query from a log file, then they can go through this blog – How to Recover Data from Log file in SQL Server – A Complete Guide .
Methods Use For How to View Transaction Log File of SQL Server
In the following section, you will understand how to open, check and read transaction file to retrieve information about the data which had been altered. So, let’s get started!!
Workarounds to Read SQL Log File
- Check SQL Server Logs Using SSMS
- View SQL Transaction Log File Via. Fn_dblog()
- Use SQL Log Analyzer to examine SQL Server Log file
#Approach 1: Use Log File Viewer in SQL Server Management Studio
Basically, this method exclusively used to open and view the information about following logs in SSMS:
- Audit Collection
- Database Mail
- Job History
- Data Collection
- SQL Server
- SQL Server Agent
- Windows Events
Its prime function of Log File Viewer is to provide the report of activities taken place in SQL Server Management Studio. In fact, one can open the Log File Viewer wizard in different ways on the basis of information that you want to check. Now, go through the instructions to view log details in SQL Server.
How to View Log File of SQL Server Via. Log File Viewer
Step 1: Open Microsoft SQL Server Management Studio application. Here, we are using SQL Server 2014 environment for reading SQL Server Error Log.
Step 2: Connect to Server windows pops-up. Here, you need to select the Server Name and Type of Authentication. Afterward, click on Connect.
Step 3: In Object Explorer, go to Management as shown in the screenshot to examine or read log file of SQL Server 2014.
Step 4: Now, move to SQL Server Logs option.
Step 5: Now, Right-click on SQL Server Logs and select View >> SQL Server Log sequentially.
Step 6: All the Log summary displayed on Log File Viewer window. Here, you can select other logs such as SQL Server Agent , Database Mail from the left panel to check its information too.
#Approach 2: View Log File of SQL Server Via. Undocumented fn_dblog()
Originally, the function fn_dblog() is used to extract data from Transaction file of SQL Server for forensic purposes to analyze every log event performed on the table. So, let’s check out how to read transaction log file in Microsoft SQL Server 2017 / 2016 / 2014 / 2012 / 2008 / 2008 R2 / 2005 editions.
Steps to View Log File in SQL Server Using Fn_dblog()
Step 1: We have a table named as ‘Employee’. So, first view the values of the table using the following T-SQL.
Select * from employee.
Step 2: Afterward, alter the table data using update command. For this, execute the query;
Update employee set department =’IT’ where emp_name = ‘jeevan’
Step 3: Again, view the table values using the Select Query. Now, you can see a modified table.
Step 4: Run the fn_dblog function according to the need. Here, we execute the query to check out the time when update operation was executed.
Select [Begin Time], [Transaction Name] from fn_dblog(null , null) where [Transaction Name] = ‘Update’
Step 5: In a situation, when you want to analyze all the logs such as Delete etc. , then run the following T-SQL query.
Select [Begin Time], [Transaction Name] from fn_dblog(null, null)
However, there are some consequences attached with fn_dblog(). Actually, this function only provide the time of the query when it was committed instead of which data entry gets affected. Due to which, it becomes cumbersome to find out which table data get altered. This problem is overcome with the third technique where user can view the log file of SQL Server without any hassle. Apart from this, both the described technique can run in SQL Server Management Studio only. You cannot read a Transaction Log File in offline environment with Log File Viewer and Fn_dblog().
#Approach 3: Use Smart Solution to Analyze Transaction File Easily
To get exact information from SQL Log File, take the help of SysTools SQL Log Reader Software. With the help of this software, user can scan and analyze T-log file in human readable format. However, the tool works in Online as well as Offline environment. User can get the information like Transaction , Login Name , Time , Table Name , Query . It is a best software solution that answers the question – how to read SQL Server Transaction Log file.
Related : How to Fix Log File Corruption – Step-By-Step Guide
In fact, after viewing the log file of SQL Server, user can export the query in Live SQL Server database environment , SQL Compatible Scripts , and in CSV format. Moreover , the software can read Transaction log file of every SQL Server edition.
That’s all about on how to View Log file of SQL Server. Now, go through the methods and opt the best that suitable for you and examine SQL Server Transaction Log file.
Frequently Asked Questions:-
Try SQL Log Analyzer tool to easily scan and read the Transaction .ldf file records.
Use Fn_dblog() function to read the details of transaction in SQL Server.
With the help of SQL Log Viewer, one can read .ldf file and view Transaction, Transaction time, Table name and Query of Microsoft SQL Server 2017, 2016, 2014, 2012, 2008 and SQL Server 2005
Yes, with the help of mentioned workaround, one can easily examine SQL LDF file .
Exclusive Offers & Deals, Grab it Now!
An entrepreneur, technical analyst, a writer with innovative and authentic thoughts when it comes to technology. Renders brilliant solutions to deal with issues users face while working with technology. Having amazing knowledge in technical arenas in numerous field.
Subscribe to our newsletter to get the latest offers
SysTools Software Pvt. Ltd.
P.O. Box 36, Springville, Utah — 84663
Call Us
USA: +1 888 900 4529
UK: +44 800 088 5522
See All Offices
Delhi Office
SysTools Software Pvt. Ltd.
528, City Centre, Sector-12, Dwarka, New Delhi — 110075, India
Pune Office
SysTools Software Pvt. Ltd.
502 — P4, Pentagon, Magarpatta Cyber City, Pune — 411028, India
Mumbai Office
SysTools Software Pvt. Ltd.
Techno IT park (Near Eskay Resorts & Times Square Restaurant, Link Road, Borivali West Mumbai — 400091, India
Banglore Office
SysTools Software Pvt. Ltd.
Queens Road, Bangalore, India
© Copyright 2007-2023 by SysTools.
SysTools ® is a Registered Trademark of SysTools Software Pvt. Ltd.
Your Choices Regarding Cookies on this Site
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on More Information to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.