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
668
votes
21 answers

How do you clear the SQL Server transaction log?

I'm not a SQL expert, and I'm reminded of the fact every time I need to do something beyond the basics. I have a test database that is not large in size, but the transaction log definitely is. How do I clear out the transaction log?
Kilhoffer
  • 32,375
  • 22
  • 97
  • 124
84
votes
10 answers

DBCC SHRINKFILE on log file not reducing size even after BACKUP LOG TO DISK

I've got a database, [My DB], that has the following info: SQL Server 2008 MDF size: 30 GB LDF size: 67 GB I wanted to shrink the log file as much as possible and so I started my quest to figure out how to do this. Caveat: I am not a DBA (or even…
Ed Sinek
  • 4,829
  • 10
  • 53
  • 81
57
votes
5 answers

Restore SQL Server DB without transaction log

Given a SQL Server 2008 .bak file, is there a way to restore the data file only from the .bak file, without the transaction log? The reason I'm asking is that the transaction log file size of this database is huge - exceeding the disc space I have…
LondonPhantom
  • 1,867
  • 3
  • 19
  • 26
53
votes
3 answers

How to view transaction logs in SQL Server 2008

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…
109221793
  • 16,477
  • 38
  • 108
  • 160
31
votes
4 answers

How to get the logical name of the transaction log in SQL Server 2005

I am trying to write a T-SQL routine that shrink the transaction log file using DBCC SHRINKFILE based on the logical name of the database. The DB_NAME() function gives you the logical name of the database. Is there an equivalent one for the…
DanM
  • 323
  • 1
  • 3
  • 6
22
votes
3 answers

Where can I find the MySQL transaction log?

Does MySQL keep a transaction log and if so where could I find it? A number of rows have mysteriously been deleted from a table and I want to try and see how and when it occurred.
php-b-grader
  • 3,191
  • 11
  • 42
  • 53
18
votes
4 answers

How Can I Manage SQL Server Log Size

I'm trying to manage the size of a SQL Server 2008 log file. I have a reporting database that is loaded once a day. The Simple recovery model is the best fit as there are no transactions other than the morning load, and I can re-create those…
Ed Mays
  • 1,730
  • 4
  • 13
  • 12
17
votes
2 answers

SQL Server: how to query when the last transaction log backup has been taken?

I would like to query for all databases (in SQL Server 2008 instance) date when the last transaction log backup has been taken. How to do that? I know that this information is somewhere, but I don't know where.
juur
  • 5,633
  • 10
  • 32
  • 36
11
votes
2 answers

Transaction log library

I am in need of transaction log library with following features: maximum performance. No force (flush), let O/S write buffers at its own discretion. File size increases in big chunks, to minimize metadata modifications. I don't care if some last…
Alexei Kaigorodov
  • 13,189
  • 1
  • 21
  • 38
9
votes
2 answers

TSQL: How do I get the size of the transaction log?

How do I get the current size of the transaction log? How do I get the size limit? I'd like to monitor this so I can determine how often I need to backup the transaction log. I usually have a problem with the transaction log when I perform large…
Niels Bosma
  • 11,758
  • 29
  • 89
  • 148
8
votes
4 answers

Does the transaction log drive need to be as fast as the database drive?

We are telling our client to put a SQL Server database file (mdf), on a different physical drive than the transaction log file (ldf). The tech company (hired by our client) wanted to put the transaction log on a slower (e.g. cheaper) drive than the…
Clinemi
  • 906
  • 6
  • 20
  • 33
8
votes
2 answers

Does TRUNCATE TABLE grows up transaction log?

I have read that one of the differences between DELETE and TRUNCATE TABLE in Sql is the TRUNCATE operation cannot be rolled back and no triggers will be fired (as written in this site for example) : QUESTION: Does this mean that when I TRUNcATE…
pencilCake
  • 51,323
  • 85
  • 226
  • 363
7
votes
3 answers

How can I delete expired data from a huge table without having the log file grow out of control?

I have a huge table (3 billion rows), which unfortunately contains mostly expired data. I want to simply delete all of these expired rows, and keep the rest. I can execute a statement like this: delete from giganticTable where exp_date <…
polygenelubricants
  • 376,812
  • 128
  • 561
  • 623
7
votes
3 answers

Is Event Hub's intended to be used for Event Sourcing / append-only log architectures

Event Hubs don't let you store messages longer than 7 (maybe up to 30) days. What is Azure's suggested architecture for PaaS Event Sourcing with these limitations? If it's Event Hub + snapshotting, what happens if we somehow need to rebuild that…
randomsolutions
  • 2,075
  • 1
  • 19
  • 22
7
votes
1 answer

How to perform SQL Roll back from transaction logs

I have transaction log file that goes back 6 months. I need to roll back everything that happened after 5/20/2013 from a database. Can anyone please enlighten me on how to do this?
torres
  • 1,283
  • 8
  • 21
  • 30
1
2 3
…
13 14