Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

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.

Filter by
Sorted by
Tagged with
265
votes
4answers
281k views

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 ...
65
votes
4answers
160k views

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.
56
votes
5answers
39k views

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-...
26
votes
4answers
15k views

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 ...
24
votes
4answers
44k views

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 ...
23
votes
6answers
109k views

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 ...
19
votes
6answers
53k views

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. ...
18
votes
2answers
7k views

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 ...
17
votes
5answers
51k views

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 ...
17
votes
2answers
53k views

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 ...
16
votes
2answers
36k views

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 ...
16
votes
1answer
501 views

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 ...
14
votes
2answers
14k views

Difference between transaction log and redo log in MySQL

I have read about MySQL. In my opinion, I see two log types as very similar. MySQL saves how and when data is changed in log. Information is used for recovery of MySQL. I am confusing the function of ...
14
votes
2answers
2k views

Can't restore SQL Server db from full backup, log processing fails, database in 'restoring' state

I am trying to set up a database for development purposes on my PC's local SQL Server Developer Edition 12.0.2000.8. I've got a full database backup and separate transaction-log-only backup files ...
13
votes
1answer
5k views

DBCC CHECKDB firing every 20 - 60 seconds

I have a dev environment that is rapidly approaching deployment into production and have noticed in the logs that roughly every 20 seconds I see the message: Starting up database 'dbname' CHECKDB for ...
13
votes
6answers
10k views

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 ...
13
votes
2answers
504 views

How are these two SQL Server rollbacks different?

In SQL Server 2008 R2, how are these two rollbacks different: Run an ALTER statement, for a few minutes, and then hit 'Cancel Executing'. It takes a few minutes to rollback completely. Run the same ...
12
votes
2answers
20k views

View delete statements in the Transaction log

Is it possible to view delete statements that have recently occurred in the Transaction Log?
12
votes
2answers
618 views

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 ...
12
votes
1answer
4k views

How important are transaction log VLFs for performance?

How important are VLFs when considering database performance? What would describe an optimal situation for VLFs?
12
votes
2answers
998 views

Why am I not getting minimal logging when inserting to indexed tables

I'm testing minimal logging inserts in different scenarios and from what I've read INSERT INTO SELECT into a heap with a non clustered index using TABLOCK and SQL Server 2016+ should minimally log, ...
12
votes
4answers
991 views

Keeping (Partial) Backups Small when using SQL Server FILESTREAM

I have a database with nearly 1TB of FILESTREAM data that I don't need backed up (if the data was deleted it'd be recreated automatically in a couple of hours, so it just isn't important). Most of the ...
11
votes
4answers
2k views

Why would shrinking TEMPDB log lead to slowness?

I recently accidentally shrunk tempdb log to almost 0 after getting alerted that log drive filling up. I am told that it will lead to slowness. Can someone please explain why it will lead to slowness?
11
votes
2answers
3k views

SQL Server transaction log backups: test whether tail log follows last known log backup

We are using SQL Server with full recovery mode. Given a full backup and a series of log backups, we would like to be able to check whether the log chain is complete from the last full backup to the ...
11
votes
2answers
5k views

Contents of transaction log file in more detail

I have a question regarding transaction log (let’s just call it LDF for short) contents. I am assuming a database with full recovery model. I have read that LDF file contains (logs) each and every ...
11
votes
2answers
6k views

SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs

After doing some research I cannot seem to find an answer to this question. Background I am attempting to setup a backup plan that fits the following three requirements: Reliability of backups, ...
11
votes
1answer
985 views

Is ALTER TABLE … DROP COLUMN really a metadata only operation?

I've found several sources that state ALTER TABLE ... DROP COLUMN is a meta-data only operation. Source How can this be? Does the data during a DROP COLUMN not need to be purged from the underlying ...
11
votes
1answer
3k views

SQL Server 2012 Simple Recovery Model with LOG_BACKUP log_reuse_wait_desc

While I'm doing my own investigation, does anyone know why a database in SIMPLE recovery model has LOG_BACKUP for the log_reuse_wait_desc? SQL Server 2012 SP1. Database created just a few weeks ago. ...
10
votes
4answers
39k views

Manually set log file size after shrink SQL Server 2008 R2

Am becoming a somewhat involuntary DBA at work at themoment and really need some help on something. We have a 40GB database in Full Recovery Mode, no log backup configured and a huge log file of 84GB....
10
votes
2answers
3k views

Does taking transaction log backups every 15 minutes consume more disk space than log backups every 6 hours?

In our environment the network storage is low on space. At the same time I would like to make sure that we take transaction log backups every 15 minutes instead of current every 6 hours. My question ...
10
votes
3answers
303 views

Poor cardinality estimate disqualifies INSERT from minimal logging?

Why is the second INSERT statement ~5x slower than the first? From the amount of log data generated, I think that the second is not qualifying for minimal logging. However, the documentation in the ...
10
votes
2answers
760 views

How exactly the row visibility is determined?

In the simplest case, when we insert a new row into a table (and the transaction commits), it will be visible to all subsequent transactions. See xmax being 0 in this example: CREATE TABLE vis ( ...
10
votes
2answers
11k views

Transaction Log maintenance on Mirror database

SQL Server Version: 2008 R2 Enterprise SP2 I am trying to get a handle on our SQL Server maintenance and I came across something I think is incorrect. We have a single production instance with 3 ...
9
votes
3answers
19k views

Transaction Log maintenance when switching to Simple Recovery

Background: I recently inherited 50+ SQL Servers with 450+ databases. The nightly backups are roughly 8TB and, needless to say, we're using more disk space than we'd like. All of the databases are ...
9
votes
3answers
9k views

Disabling logging on certain tables

I'm using SQL Server 2005. I have two tables that contain aggregate information. The information is constantly being updated, generating almost 5GB of log data a day. (That's larger than the entire ...
9
votes
3answers
1k views

Is there a performance benefit to placing transaction log files on a separate drive?

There are many blog posts and best practice articles extolling the virtues of placing the SQL Server data file on one hard drive and the transaction log on another. The reason given is that that the ...
9
votes
1answer
3k views

Unable to truncate transaction log, log_reuse_wait_desc - AVAILABILITY_REPLICA

This morning i was woken up by a transaction log full alert on one of our database. This server is an alwayson cluster and also a transactional replication subscriber. I checked log_reuse_wait_desc ...
9
votes
1answer
19k views

Best way to backup & truncate transaction logs on a schedule

I'm no DBA, but things being what they are, I have to wear the DBA hat and set up maintenance plans on my SQL Server instance. So for a while I've been having my SSIS overnight process run a Execute ...
9
votes
1answer
14k views

Can I shrink the transaction log file on a mirror database?

This is a followup question to a previous question about why I couldn't shrink the log file on the principal database. To make a long story short, I setup database mirroring but forgot to make sure ...
9
votes
1answer
7k views

Whats the difference between Transaction logs and Binary logs?

Coming from a SQL Server shop, I now work with MySQL and I was curious. What are the differences between MySQL's binary log and MSSQL's transaction log? By the outlook thus far, it seems that there ...
9
votes
1answer
10k views

Tail Log backup when doing a restore?

Typically when doing a restore of a DB from a production server to a non-production server I will use the WITH REPLACE option as when I forget I get an error about the tail log not being backed up. ...
9
votes
2answers
2k views

SQL Server 'Create Database' statement. How to inherit autogrowth settings?

I am using SQL Server 2008 R2 and creating databases through continuous deployment. In our system, the default 1Mb/10% autogrowth settings in SQL Server work badly with our data. Particularly as we ...
8
votes
3answers
7k views

Does backing up a database shrink the transaction log size?

I am trying to gather some knowledge on SQL databases and I have some questions about the transaction log file (LDF). First off, when you create a database, you have to define an Initial File Size ...
8
votes
3answers
967 views

transaction log in RAM or physical file?

I'm a beginner in transaction, just a question on transaction log. We know that when we commit a transaction, the changes are written to the transaction log, but is transaction log in RAM or physical ...
8
votes
2answers
18k views

Set simple recovery mode and shrink log files for all user created databases

I hope you can point me in the right direction. I'm not a frequent user of T-SQL, but I did some googleing, and found the script below. I corrected the script a bit. I want the script to: To select ...
8
votes
2answers
662 views

Why is it not recommended to use RAID 5 for a log file? [duplicate]

While reading SQL Server Query Performance Tuning written by Grant Fritchey, I found it difficult to understand the following part: avoid RAID 5 for t-logs because, for every write request, RAID&...
8
votes
1answer
2k views

When performing a cross-database transaction, in which transaction log(s) is the information stored?

Given the following snippet: -- error checking omitted for brevity begin tran exec database1..my_stored_procedure exec database2..my_other_stored_procedure if (@@error <> 0) rollback ...
8
votes
3answers
4k views

SQL Server virtual log size

I'm somewhat of an accidental DBA, being a developer who inherited a couple of database servers (2005 and 2008) from someone who knew little about database administration, and seemingly had even less ...
8
votes
2answers
4k views

Transaction Log and mirroring - looking for dumbest explanation possible

First of all I must admit that I struggle with concept of Transaction Log. I mean - I do understand that it is Log of all Transactions that happen on database, but when it comes to properly putting it ...
8
votes
2answers
1k views

SQL Server Transaction Logs in the Cloud

It is a common practice to place the transaction logs (*.ldf) on a separate physical disk system than database files (*.mdf, *.ndf). In a virtual machine cloud environment, is it okay to put the ...