Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [transaction-log]

Transaction log is a list/history of modification executed by a database management system to guarantee ACID properties over crashes or hardware failures.

Transaction log is a list/history of modification executed by a database management system to guarantee ACID properties over crashes or hardware failures.

Physically, a log is a file of updates done to the database, stored in stable storage, transaction log is not used for statements that do not modify data.

The transaction log has some important purposes:

  • data recovery if there is a system failure
  • replication
  • system maintenance
  • analysis
779 questions
297
votes
4 answers

Why Does the Transaction Log Keep Growing or Run Out of Space?

This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this: In SQL Server - What are some reasons the transaction log grows so large? Why is my log file so big? What…
Mike Walsh
  • 18,013
  • 6
  • 46
  • 72
80
votes
4 answers

How to identify which query is filling up the tempdb transaction log?

I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
prasanth
  • 1,023
  • 2
  • 9
  • 12
60
votes
5 answers

Why does ALTER COLUMN to NOT NULL cause massive log file growth?

I have a table with 64m rows taking 4.3 GB on disk for its data. Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text. I added a a NULLABLE column with data-type Datetimeoffset(0). I then UPDATED this column…
PapillonUK
  • 703
  • 1
  • 5
  • 5
33
votes
5 answers

Difference between Full backup and Copy-only full backup

I saw in the SQL Server Central thread Does a full backup truncate the log? that full backup does not truncate the log: No. Neither Full or Differential backups truncate the transaction log. - Lynn Pettis No - a full backup does not truncate the…
IT researcher
  • 3,115
  • 15
  • 54
  • 78
32
votes
6 answers

Shrinking the log file does not reduce size

I have a database which has a 350 MB data file (.mdf) and a 4.9 GB log file (.ldf). The recovery model is set to FULL. When I try to shrink the log file, it's not shrinking. I know shrinking a database is not good and it should not be done. But…
Navaneet
  • 883
  • 5
  • 12
  • 23
30
votes
4 answers

Why does transaction log continue to grow in Simple recovery mode with nightly backups

Before immediately marking as duplicate, I have read Mike Walsh's Why Does the Transaction Log Keep Growing or Run Out of Space?, but I don't think it gave an answer to my situation. I looked through a dozen or so similar questions, but the…
DerekCate
  • 438
  • 1
  • 4
  • 7
28
votes
4 answers

The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'

I have a question about XTP_CHECKPOINT. I'm using SQL Server 2014. I have a database that is in SIMPLE recovery model mode. It is also being replicated. There are no open transactions. I've run DBCC OPENTRAN and it returns: "No active open…
user3469285
  • 661
  • 2
  • 8
  • 19
21
votes
6 answers

How to prevent transaction log getting full during index reorganize?

We have multiple machines where we have pre allocated the size of the transaction log to 50gb. The size of the table that I am trying to reorganize is 55 - 60 gb but is going to continuously increase. The main reason I want to reorganize is to…
Sriram Subramanian
20
votes
2 answers

Truncate a table with 17 billion rows in an AG

I need to truncate a table with 17 billion rows, the table is in a database that is part of an AG. What will be the effect of this operation on the AG latency and the size of log backups? Is there a recommended way of doing this?
yegnasew
  • 467
  • 4
  • 8
19
votes
2 answers

During a log backup is the data backed up to the start or end of the operation?

Say I am running a log backup, and that log backup takes 10 minutes to complete. During that 10 minute window, further transactions are run. Given the below example, which transactions does the log backup actually contain? Transaction A…
George.Palacios
  • 5,528
  • 19
  • 48
19
votes
2 answers

Sql Server - Best Practices for Growing Database Files

I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 GB. The DB files auto growth is set to 5MB and I…
Ross Bush
  • 453
  • 1
  • 4
  • 16
18
votes
6 answers

Transaction Log won't shrink, DB thinks it is replicating

I've got a SQL Server 2008 R2 Express database running Kaspersky Security Center, and I have no idea under what circumstances the install happened, but the database appears to think that it's being replicated and will not free any space from the…
Sammitch
  • 306
  • 1
  • 3
  • 9
18
votes
3 answers

Shrink Transaction Log While Using AlwaysOn Availability Group

We are using AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database. I have read here doing the transaction log backup on either the primary…
gotqn
  • 3,709
  • 10
  • 42
  • 77
18
votes
2 answers

SQL Server how to get around the transaction log filling up when updating a column to an int

I have a SQL Server 2005 table called BRITTNEY_SPEARS_MARRIAGES and it has the following columns: MarrigeId tinyint, HusbandName varchar(500), MarrigeLength int Now I have another table BRITTNEY_SPEARS_MARRIAGE_STORIES StoryId int, MarriageId…
codingguy3000
  • 283
  • 2
  • 6
17
votes
1 answer

Why is CHECKDB reading the transaction log file on a database with a memory optimized table?

tl;dr: why is CHECKDB reading the transaction log for a user database with memory optimized tables? It appears that CHECKDB is reading the transaction log file of the user database when it's checking on one of my databases - in particular, a…
Josh Darnell
  • 28,427
  • 5
  • 63
  • 116
1
2 3
…
51 52