Stack Overflow
Stack Overflow

Questions tagged [transaction-log]

transaction log is a log file of all actions performed on a database. It helps keep consistency and recover from unexpected failures.

The Transaction Log is a log file that keeps an audit trail of all actions performed on a database. This allows the database system to recover from unexpected shutdowns like power failures. Usually, the system will roll back changes made by uncommitted transactions and re-apply any committed transaction that did not make it to the database. This is done to keep ensure the atomicity and durability of transactions.

The transaction log can also be used to recover accidentally changed or deleted data.

197 questions
3
votes
2 answers

SQL - shrink database log file

I am trying to shrink my database log file. I have tried to run: USE databasename BACKUP log databasename WITH truncate_only DBCC shrinkfile (databasename_log, 1) I get the error message: Msg 155, Level 15, State 1, Line 3 'truncate_only' is…
user258133
  • 75
  • 2
  • 5
3
votes
3 answers

SQL Server 2005 Transaction Log Entry : LOP_Format_Page

I am investigating an issue relating to a large log expansion during an ETL process, even though the database is set in bulk logged mode (and it is not running in psuedo simple but truely bulk logged) Using the ::fn_dblog(null,null) function to…
Andrew
  • 26,629
  • 5
  • 63
  • 86
3
votes
2 answers

What is the best possible way to tackle SQL Server transaction logs filling up disk space?

I need the best shortest possible way to handle SQL Server transaction log files as my disk will be having a problem in saving them, in future. I don't know how to tackle this Low Disk Space issue, as my drive's free space is already in MB's. Now,…
Avian
  • 289
  • 2
  • 5
  • 10
2
votes
2 answers

Audit whether stored proc was executed - in the transaction logs

We have SQL Server 2005 database with full backup and transaction logs. We have a problem with the database - and need the SQL CSI Forensic team to help. Is there a way to look at the transaction logs and identify whether a stored procedure was…
aSkywalker
  • 1,381
  • 1
  • 13
  • 23
2
votes
1 answer

How to reduce transaction log size for an SSIS ETL process?

We have an issue with a transaction log growing to a size of about 70 GB for a SQL Server 2008 database of about 10 GB. This happens in the staging database of an ETL process run with SSIS 2008. The process takes about 8 hours to complete and is run…
R. Schreurs
  • 8,587
  • 5
  • 43
  • 62
2
votes
3 answers

SQL Server database backup plan and log truncation

I have a SQL Server 2005 database that is backed up nightly. There backup consists of: FULL backup of the database. backup of the transaction log. These are currently two separate jobs. The log is huge and I'd like to set things up so that: the…
Caveatrob
  • 12,667
  • 32
  • 107
  • 187
2
votes
1 answer

Performance for RBAR vs. set-based processing with varying transactional sizes

It is conventional wisdom that set based processing of tables should always be preferred over RBAR - especially when the tables grow larger and/or you need to update many rows. But does that always hold? I have experienced quite a few situations -…
user806549
2
votes
1 answer

Is it safe to delete virtuoso.trx?

I just looked into one of our Virtuoso db directories and noticed that virtuoso.db is not the biggest file there -- virtuoso.trx (transaction log) is. Assuming that I am happy with the data in virtuoso.db and do not anticipate needing to reverse any…
amphibient
  • 29,770
  • 54
  • 146
  • 240
2
votes
1 answer

SQL Server: SQL request returns after writing to t-log or after b-tree is updated

For mutating SQL statements (insert, update, delete), when does SQL returns response to client? Is this after the change has been written to the transaction log but b-tree hasn't been updated, OR after the change has been written to the…
user21479
  • 1,179
  • 2
  • 13
  • 21
2
votes
0 answers

Why do some of our Elasticsearch indices have large transaction logs and how can we prevent it?

We have an ES cluster with three data nodes storing logging data as part of an ELK stack. It's running smoothly for the most part, however some of our indices have large transaction logs, e.g.: { "logstash-2018.05.01": { "operations":…
Nick Peirson
  • 100
  • 7
2
votes
1 answer

AWS DMS limitation when using SQL Server as a source - replicating rows with more than 8000 bytes of information

I am thinking of creating a read only DB in Amazon RDS, using their DMS to replicate the data from an on-site database. On the Amazon DMS list of general limitations, one of them are: Changes to rows with more than 8000 bytes of information,…
TheTor
  • 53
  • 1
  • 11
2
votes
2 answers

Rollback a delete operation using transaction logs

Need to roll back a delete operation from the transaction logs in sql server 2008 but unsure how to do this. Can anyone give me a point in the right direction for the correct syntax for this operation?? I'd be very grateful, Thanks
109221793
  • 16,477
  • 38
  • 108
  • 160
2
votes
1 answer

How to clean a transaction log in integration flow of HCP PI?

I'm running an integration flow which processing actions are on hold due to the following error: com.sybase.jdbc4.jdbc.SybSQLWarning: The transaction log in database is almost full. Your transaction is being suspended until space…
Diogo Magalhães
  • 441
  • 1
  • 9
  • 19
2
votes
5 answers

MS-SQL 2000: Turn off logging during stored procedure

Here's my scenario: I have a simple stored procedure that removes a specific set of rows from a table (we'll say about 30k rows), and then inserts about the same amount of rows. This generally should only take a few seconds; however, the table has…
John
  • 17,163
  • 16
  • 65
  • 83
2
votes
1 answer

SQL Server: what is the virtual log file?

What is the Virtual Transaction Log file in SQL Server?
jjoras
  • 2,001
  • 5
  • 18
  • 16
1 2 3
…
13 14