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
0 votes
1 answer
27 views

what to do when database is not synchronizing in availability groups?

I am using Jonathan Kehayias - Monitoring Availability Group Replica Synchronization SELECT ar.replica_server_name, adc.database_name, ag.name AS ag_name, drs.is_local, drs....
Marcello Miorelli's user avatar
0 votes
0 answers
66 views

Database log file is so big

My database log file is so big(400 giga) and data file is (50 giga) My recovery method is simple and backup plan is daily full backup Log file is configured to auto growth. I checked open transactions,...
Abdelrahman Mamdouh 's user avatar
0 votes
0 answers
55 views

A 27GB Table Population causes the TLOG to grow to 211GB

my brain is fried. I work in an organisation where every night we pull data from the source system into a staging area, then into production and because of this we have out DB's set to Simple Recovery ...
Simon Thorn's user avatar
0 votes
0 answers
33 views

SQL Server 2014 Negative log. Stuck at CHECKPOINT. Unable to do anything

I have a database that is stuck at CHECKPOINT for hours and I already tried all solutions that have been suggested in this site with no avail and I'm starting to lose faith. Keep in mind that the ...
Pedro's user avatar
  • 1
0 votes
1 answer
42 views

When can we say a transaction is committed in mysql?

I know the transaction can ensure that all the queries are committed or rolled back, but what is the point at which we can say ok, the transaction has been committed and all changes are durable? I ...
Kaiyu Yang's user avatar
1 vote
1 answer
52 views

Release Database Transaction Log File Freespace In SQL Server 2016 [duplicate]

Good afternoon, We have 4 node SQL Server configure in HA/DR. Periodically, we purge the old data to free up the space. Recently, after the SQL Job to purge the data was executed, we notice an ...
rushabh138's user avatar
0 votes
1 answer
63 views

Log .ldf file not truncate after several times of log backup on secondary database

i have always on setup in production environment. Scheduled backup (transaction log backup) already in place every 2 hours using netbackup on secondary database. The issue i am facing is sometimes the ...
zul nasir's user avatar
4 votes
1 answer
254 views

Catch Block Inside of a Trigger Does Not Get Hit When a SP With Dynamic SQL Errors Out - Causing the Larger Transaction to Fail

I am not a fan of triggers and dynamic sql, however, what I am working with requires both. CREATE TRIGGER [dbo].[GenerateDynamicFormItemViews] ON [dbo].[tblFormItems] AFTER INSERT, UPDATE AS BEGIN ...
Ross Bush's user avatar
  • 505
0 votes
1 answer
64 views

Managing very large VLF

I'm investigating a database with a large transaction log. Looking at DBCC Loginfo I can see the database has 1200 VLF's, which is on the high side but not terribly concerning. What does worry me ...
AlexP012's user avatar
0 votes
1 answer
76 views

Sql Server Open Transactions

I was notified that the transaction log drive for a sql instance I manage is filling up. I jumped on, found which transaction log was growing, and went to take a look at why the tlog was growing. ...
Patrick's user avatar
  • 678
0 votes
1 answer
96 views

Is there a stronger guarantee of minimal logging when running INSERT INTO [...] WITH (TABLOCK) on a heap than on a B-tree?

The documentation lists the following requirements for bulk imports with minimal logging The recovery model of the database is set to simple or bulk-logged. The target table is an empty or non-empty ...
J. Mini's user avatar
  • 81
3 votes
1 answer
294 views

Are there any possibilties to mitigate data loss risk on delayed durability

Due to poor disk performance I need to evaluate using the delayed durability setting in SQL Server 2016. Microsoft states clearly, that this comes with a data loss risk because transactions are ...
Mainecoon21's user avatar
1 vote
3 answers
127 views

Does a SQL Server Log Backup Include Log Records Backed Up in a Previous Full or Differential Backup?

I've been reading the Microsoft documentation on log backups in SQL Server and came across the following statement: (Log Backup) Specifies a backup of the transaction log only. The log is backed up ...
Fajela Tajkiya's user avatar
1 vote
2 answers
229 views

Is it possible to create a database without a transaction log?

I was recently told that it is possible to create a new database in SQL Server without a transaction log. I've worked with SQL Server for a very long time and I've never seen it done and I didn't ...
AlwaysLearning's user avatar
0 votes
1 answer
63 views

What is the best option to remove duplicates in big heap?

Our Server is SQL Server 2016 Database with Full Recovery model. We have table "A" - heap which has 280 millions of records and size 90 GB with 50 columns. This table contains around 5 ...
adam.g's user avatar
  • 335
0 votes
1 answer
28 views

Understanding the Relationship Between Log Records and Modifications a Log Record Can Do

I am currently playing with SQL Server's fn_dblog function and have come across a concept that I'm having trouble understanding. In the function's result, there is a 'Page ID' column and a 'Slot ID' ...
Fajela Tajkiya's user avatar
0 votes
1 answer
37 views

Correlating SQL Audit transaction_id to LSN

I have a relatively simple question that is "Google resistant". I have a SQL Database Audit spec that logs a transaction_id. I also have a Change Data Capture (CDC) spec that logs __$seqval (...
Vocoder's user avatar
  • 117
0 votes
0 answers
53 views

Backup SQL transaction log to Azure "append blob"?

We have long saved our hourly transaction log backups throughout the day by starting a new t-log file nightly, after the full backup is complete, and then appending to it hourly for the rest of the ...
OKreboots's user avatar
0 votes
0 answers
64 views

SQLServer Physical Log shrinking automatically with AutoShrink off

Running in SQLServer 2016 I have a weird situation where the Physical log is shrinking without any interaction and with AutoShrink=false set. Also, on occasion the size is shrinking below the initial ...
DbaJag's user avatar
  • 1
0 votes
1 answer
150 views

What will happen when SQL Server log drive is full?

One of my production database log file drive filled up and I noticed below messages in SQL Server error log. 2022-02-13 03:16:24.500 spid72 Error: 9002, Severity: 17, State: 4. 2022-02-13 03:16:24....
Fajela Tajkiya's user avatar
0 votes
0 answers
182 views

Transaction log full due to CHECKPOINT

I see that posts already exists for this type of issue, but they are not 100% identical to the one I have here. The solutions proposed also don't work. (I have tried a lot...) I'm trying to restore ...
Scopperloit's user avatar
0 votes
1 answer
57 views

Huge Transaction Log File - Full Database backup, but no Transaction log backups

I hope someone can help. Please be gentle, this type of admin is not what I usually do. I have inherited a database which is set up to do a FULL database backup at 02:00 every day. The transaction log ...
Jubs666's user avatar
0 votes
1 answer
84 views

Transaction log file file size issues

Hope someone can assist me here. I have a DB in mirror mode, i.e. DB on sql1 mirror to sql2 and obviously recovery model is 'Full', and the SQL version is SQL server 2019. Currently, my transaction ...
pavk's user avatar
  • 1
0 votes
1 answer
166 views

How is the current sequence number in the control file used during recovery?

During recovery, how is the current sequence number used to determine the redo log files used to re-construct the database as well as the order in which to apply the changes recorded in them?
Mehdi Charife's user avatar
0 votes
2 answers
467 views

What's inside a redo record in Oracle's Redo log?

While reading the Redo log wiki page, I was confronted with the following statement: For example, if a user UPDATEs a salary-value in a table containing employee-related data, the DBMS generates a ...
Mehdi Charife's user avatar
1 vote
1 answer
85 views

What is the log backup behaviour after AG failover or other node unreachable?

Log backups are important for point in time recovery and to automatically truncate the log file (in full recovery model) to make space for log reuse. But when the secondary is unreachable either due ...
variable's user avatar
  • 3,533
2 votes
1 answer
181 views

Why does SQL Server use two different data types for LSN?

I have noticed in the SQL Server documentation that Log Sequence Numbers (LSNs) are sometimes described as numeric(25,0) and other times as binary(10). Why are there two different data types used for ...
Fajela Tajkiya's user avatar
0 votes
1 answer
117 views

Can not Truncate the database because “secondaries has no log was added”

I am a newbie to SQL Server. I got an error when trying to truncate the database: Database can not shrink until all secondaries have moved past the point where the log was added. When I check the ...
Anh Nguyen's user avatar
0 votes
1 answer
347 views

How to prevent log_reuse_wait_desc REPLICATION stage and log growth

Not a unique problem here. I'm more so asking for advice on how to prevent this issue. We're using a nightly snapshot replication job to offload data to our data warehouse. This job has been setup and ...
jdids's user avatar
  • 309
2 votes
2 answers
10k views

How can I reduce the size of a huge LDF file? [duplicate]

We are a small trading company. We use a software for invoicing and stock keeping which uses SQL Server as a database. Our SQL Server database is 3.3 gb in size. However our LDF file is 173 gb. Both ...
Subliminal Hash's user avatar
0 votes
1 answer
69 views

Transaction Log backup chains and one off full backups [duplicate]

I am trying to create a backup plan consisting of full, differential and log backups however i know one off full backups are taken whenever fixes are done to the database. Dors anyone know whether ...
user avatar
-1 votes
1 answer
41 views

Different speed on VLFs in SQL Server?

As the topic says, can VLFs in the same logfile have different speeds? Yesterday when I updated a table with 12M records, the update speed was about 5x when VLFa was active vs VLFb, is this normal and ...
Marson's user avatar
  • 11
1 vote
1 answer
274 views

How to manage the log file size on secondary replica (log backups)?

I am learning about SQL AG. Currently we have 1 SQL server and we run weekly full and hourly log backups. The log backups truncate the log and prevent from filling up the disk. When I configure an AG ...
variable's user avatar
  • 3,533
7 votes
2 answers
1k views

Does Instant File Initialization work for manual log file growths?

SQL Server 2022 introduced Instant File Initialization for transaction log file growth events. In the What's New in 2022 page, Microsoft notes: In general, transaction log files cannot benefit from ...
Brent Ozar's user avatar
  • 42.9k
2 votes
2 answers
54 views

For a query that changes data, does the change get written to tlog prior to sending it to secondary (AG) and primary data file on disk?

So there are 6 things involved when a transaction is made that writes data: Tlog in memory Data page in memory Tlog on disk Data page on disk Send the tlog to secondary AG Inform the client that ...
variable's user avatar
  • 3,533
0 votes
1 answer
484 views

BACKUP LOG cannot be performed because there is no current database backup. Define Current?

I know what the message is telling me; but I am not sure on the definition of "Current" is, I have a Database backup that was performed at 3am on the previous day, that backup resides on a ...
StixO's user avatar
  • 167
2 votes
2 answers
612 views

Does adding a column to a table increase the transaction log size (SQL Server)?

I googled and read a lot but couldn't find a definitive answer to the simple question: Does adding an empty column to a table increase the transaction log size in SQL Server? I'm asking because we ...
Manuel K's user avatar
1 vote
1 answer
1k views

SQL Server DB Log keeps growing / CDC enabled database

I've enabled change data capture on a SQL Server database. The database is in full recovery mode. The LOG backups of the database are being done hourly. The capture jobs was running without any issues ...
saso's user avatar
  • 243
0 votes
1 answer
85 views

Preserve WALs on promoted node in PostgreSQL 14

I have PostgreSQL streaming replication setup using replication slot. Node1 is primary and Node2 is slave. Now, node1 is down and promoted node2. Node2 is running and in read-write mode. There is ...
Niraj Nandane's user avatar
0 votes
1 answer
565 views

Tempdb Log is FULL [duplicate]

So my tempdb log has 0% free when I checked my Disk Management in my windows server, I went to SSMS and checked the disk usage report GUI and it shows me that my transaction log is 99% free and the ...
WhoIsNotActive's user avatar
0 votes
1 answer
227 views

How does sql server know whether the dirty pages have been written to the data file?

I understand sql server writes the updates to the data pages (making them dirty) in memory and writes the tlog onto the disk before sending the commit notification to the user. This question is to ask ...
variable's user avatar
  • 3,533
9 votes
2 answers
1k views

MS SQL Server DB Transaction Log Growth Rate

I have a busy database, it has been busy for years and I've seen a transaction log size of approx a few GB a day. In the last few weeks, that transaction log size has increased to 40-50 GB every 6 ...
Damo's user avatar
  • 193
3 votes
1 answer
7k views

Postgres max_wal_size and wal_keep_size parameter

I am setting up Postgres v14 database. I have a question related to the max_wal_size and wal_keep_size parameters. According to the documentation: max_wal_size ⇒ Maximum size to let the WAL grow ...
dsingh's user avatar
  • 131
0 votes
2 answers
53 views

Does tail log backup option during restore (UI) truncate the tlog? And is there any way (UI option) to prevent truncation?

When restoring the db via SSMS UI, there is an option (checkmark) to take the tail log backup and optionally to place the db in NO RECOVERY mode. Does such a tail log backup truncate the tlog? And is ...
variable's user avatar
  • 3,533
0 votes
0 answers
54 views

Configuring SQL Server database transaction logs truncation regularly? [duplicate]

How can I configure SQL Server 2014, to allow truncating the logs by itself so that it doesn't fill up my database drive? I need some help as I'm not a DBA myself and this SQL Server is installed ...
Senior Systems Engineer's user avatar
0 votes
1 answer
110 views

How does sp_flush_log work?

I'm reading the documentation for sp_flush_log, which states: Flushes to disk the transaction log of the current database, thereby hardening all previously committed delayed durable transactions. ...
Fajela Tajkiya's user avatar
0 votes
2 answers
271 views

How does SQL Server delayed durability work?

I'm reading documentation for the delayed durability feature of SQL Server and saw this table: It states that database level settings always take precedence over commit level seeing. But I'm not able ...
Fajela Tajkiya's user avatar
0 votes
1 answer
79 views

Restore database and transactions logs [duplicate]

There is a production database ~1T, and ~200GB of log file unused space, simple recovery mode. I want it to restore at test environment and get rid of those 200GB log file to free up space for other ...
NewUser's user avatar
  • 11
3 votes
4 answers
1k views

How do I restore multiple transaction log backups after restoring a full backup SQLSERVER 2019

I am creating my DR strategy, and my full back-up recovers fine. The full backups are taken with a modified version of the Ola Hallengren scripts. The file timestamp of the full backup shows 07/26/...
Thomas's user avatar
  • 37
-1 votes
1 answer
151 views

When DML and DDL changes occur on a Temporal Table do the correlating changes in the History table get logged in the Transaction Log as well?

When DML and DDL queries are executed against a Temporal Table, those changes are appropriately propagated to the correlating History table. E.g. an UPDATE to the Temporal Table results in a new ...
J.D.'s user avatar
  • 37.1k

1
2 3 4 5
17