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.
790
questions
0
votes
1
answer
13
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 (...
- 117
0
votes
0
answers
10
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
0
votes
0
answers
62
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 ...
- 1
0
votes
1
answer
53
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....
- 1,041
0
votes
0
answers
56
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 ...
- 101
0
votes
1
answer
41
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 ...
- 1
0
votes
1
answer
66
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 ...
- 1
0
votes
1
answer
127
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?
- 131
0
votes
2
answers
207
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 ...
- 131
1
vote
1
answer
45
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 ...
- 3,348
2
votes
1
answer
103
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 ...
- 1,041
0
votes
1
answer
88
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 ...
0
votes
0
answers
67
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 ...
- 309
0
votes
2
answers
3k
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 ...
- 111
0
votes
1
answer
57
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 ...
Jessica Nutt
-1
votes
1
answer
38
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
1
vote
1
answer
99
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 ...
- 3,348
0
votes
0
answers
48
views
Is there a way of identifying transaction boundaries from MongoDB ChangeStream?
The MongoDB documentation (https://www.mongodb.com/docs/manual/reference/change-events/) specifies that for a change event, the transaction associated with that event can be uniquely identified by the ...
6
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 ...
- 42.6k
2
votes
2
answers
52
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 ...
- 3,348
0
votes
1
answer
297
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 ...
- 167
2
votes
2
answers
509
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 ...
- 39
1
vote
1
answer
727
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 ...
- 243
0
votes
1
answer
48
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 ...
- 115
0
votes
1
answer
292
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 ...
0
votes
1
answer
143
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 ...
- 3,348
9
votes
2
answers
852
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 ...
- 193
1
vote
1
answer
4k
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 ...
- 111
0
votes
2
answers
48
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 ...
- 3,348
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 ...
0
votes
1
answer
94
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.
...
- 1,041
0
votes
2
answers
157
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 ...
- 1,041
0
votes
1
answer
70
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 ...
- 11
3
votes
4
answers
826
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/...
- 37
-1
votes
1
answer
134
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 ...
- 34k
0
votes
1
answer
72
views
How to change transaction logs size in singlestore
We have a table that memsql studio says is in 5gb in size, though on disk we see approx 27gb in the data folder. After further analyzing it seems the data/logs is holding approx 16gb.
Is this needed ? ...
- 252
1
vote
1
answer
2k
views
How to work with "fn_dblog" in SQL-server 2019
In my previous question, I asked about how to know about what happened in my SQL-Server 2019 database. After a while, I found out that there is some kind of table, called fn_dblog, which gives some ...
- 523
4
votes
0
answers
116
views
Log Backup occasionally blocked by automated update
We have a canary table in each of our databases that our automation updates once a minute. The automation is a sql server agent job that utilizes a cursor to iterate over the databases and then ...
- 3,018
4
votes
3
answers
1k
views
How to recover from a SQL server log file drive crash?
Assuming data files are on D: and Log files are on E:
Say the E drive crashes and the log files (.ldf) are lost. I attach a new empty disk to the E: and start the sql server.
Upon startup, SQL Server ...
- 3,348
2
votes
1
answer
1k
views
How make Db2 database transaction log full fully robust?
Db2 v11.5.7.0 on Linux and for some reason few times per year database hits the transaction log full error.
My current solution was to increase logsecond, logprimary or logfilsiz and now I hit to ...
- 303
1
vote
2
answers
288
views
Log records in the transaction log
The log record for data modifications in the transaction log: does it only contain the before and after states for the rows and columns affected or does it contain the before and after states for all ...
- 785
1
vote
1
answer
430
views
SQL Server backup log - hasn't been run in a long time, what could go wrong?
SQL Agent was accidentally not enabled on a SQL Server after a restart, and no jobs were run for 3 weeks, including no backups (full or diff or log).
SQL Agent is enabled now. The full backup of the ...
- 181
3
votes
1
answer
459
views
Does the SQL Transaction Log clear at the end of a loop?
I'm running SQL Server 2019 Enterprise Edition. Every month, we run a stored procedure that loads millions of records with service dates going back five years. I have 400GB of space allocated across 4 ...
- 159
4
votes
3
answers
991
views
Full recovery model and transaction log
I will be changing a 5TB database from simple to full recovery model due to new business requirement. The database has been in simple recovery model since 5 years.
My question is once I change the ...
- 57
0
votes
2
answers
518
views
Database Backups and logs file size too large
I'm running SQL Server 2019 and I need to do manage my backups and logs better.
My main database (mdf) is only 9GB however the log (ldf) is 792GB. I've created back ups of these using SSMS > Tasks &...
- 103
3
votes
1
answer
627
views
What is the need for log cache in MSSQL server?
If every transaction record is immediately hardened in transaction log file, then what is the need for a log cache?
If a transaction record remains in log cache when there is a system failure, then ...
- 41
0
votes
1
answer
74
views
Can I set up Wal Archiving on a three years old Postgres database?
If I set up Wal Archiving on a three years old Postgres database (used in production); do the Continuous Archiving and Point-in-Time Recovery (PITR) will work from the beginning? Will I have all the ...
- 113
0
votes
0
answers
50
views
Why are both log file being written at the same time
I have a strange situation and I would like to get someone else taught.
I have a database that have 2 log files (I know, it should not and I plan of fixing it soon).
Now for what I know, log file are ...
- 3,068
2
votes
1
answer
632
views
Error 9002 - State 4, Class 17: SQL Server reports full transaction log
I have the problem when running an external program that accesses a SQL Server 2014 database. I get the following error message.
The transaction log for the 'TService' database is full. The
...
- 21
0
votes
2
answers
385
views
Restoring secondary backup files on primary server in sql server
I want to restore log backup file on copy-only full backup in restoring mode on primary replica server
these backups were taken from secondary replica .
I took copy only full back up on secondary ...
- 1