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
8 votes
2 answers
384 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 ...
  • 183
0 votes
1 answer
36 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 ...
  • 101
0 votes
2 answers
24 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 ...
  • 2,792
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
58 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. ...
0 votes
2 answers
35 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 ...
0 votes
1 answer
55 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
3 answers
78 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/...
  • 35
-1 votes
1 answer
63 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 ...
  • 26.5k
0 votes
1 answer
18 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 ? ...
0 votes
1 answer
133 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 ...
  • 463
4 votes
0 answers
57 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 ...
  • 2,978
0 votes
0 answers
185 views

Transaction Logs generated per hr /transaction log disk usage per min/hr in PostgreSQL AWS RDS

Need to estimate the space allocation for transaction logs in AWS RDS PostgreSQL based on the number of logs generated/disk space used for transaction logs per day. I know this information is ...
4 votes
3 answers
932 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 ...
  • 2,792
1 vote
1 answer
339 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 ...
  • 99
1 vote
2 answers
108 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 ...
  • 655
0 votes
1 answer
81 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 ...
  • 171
3 votes
1 answer
138 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 ...
  • 157
4 votes
3 answers
274 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 ...
  • 55
0 votes
2 answers
79 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
469 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 ...
0 votes
1 answer
41 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 ...
0 votes
0 answers
46 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 ...
2 votes
1 answer
240 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 ...
0 votes
2 answers
138 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
0 votes
1 answer
79 views

SQL Server 2019 - Recovery after reboot (for windows updates)

lately one of our SQL server 2019 database has been going 'In recovery' after a restart. The databases aren't particularly large. The server has 8 databases of ~17GB per database. The database itself ...
  • 101
1 vote
3 answers
333 views

Why does MySQL require two or more redo log files for InnoDB?

A single redo log file can also be written in a circular fashion. In fact, MariaDB has changed its InnoDB redo log to a single file, and its developers claimed that "the log file was ...
0 votes
1 answer
316 views

Restoring SQL Server DB trans log files error

I am getting error when restoring DB trans log files. Running RESTORE VERIFYONLY FROM DISK = xxxxxx is returning a message The backup set on file 1 is valid However when running RESTORE LOG [...
's user avatar
2 votes
1 answer
177 views

Postgres WAL full and database stopped/exited with error - No space left on device

I am new to postgresql. We had a full disk issue on PostgreSQL DB where data and WAL was set. WAL was taking almost 65 GB. This is a lower environment and we don't need to restore to a point. Compared ...
2 votes
1 answer
102 views

Transaction log file size growing, what will be the consequences of setting recovery mode to simple

Recovery mode is set to FULL, and FULL backup is taken on alternate days. No log/differential backups. Log file size is growing too big. I've shrank log file (googled) a week back and was fine. Now ...
  • 23
1 vote
3 answers
224 views

WordPress MYSQL DB BINARY LOG - Droplet/SERVER Volume Increasing rapidly

My WordPress website uses Nginx. Recently I have noticed that server volume has increased from 8GB to 40GB. I have found that the WordPress MYSQL Binary Log is taking more space. Each second, database ...
2 votes
1 answer
723 views

SQL Server log_reuse_wait_desc is ACTIVE_TRANSACTION but there are no active transactions

Just had an issue with a SQL Server 2016 database where this query returned ACTIVE_TRANSACTION for log_reuse_wait_desc: select name,log_reuse_wait_desc from sys.databases where name='database name' ...
  • 21
0 votes
3 answers
61 views

Transactional log is max at 2tb, can I create another log file?

The database trans log is in simple recovery mode, but after bulk insert, it has reach the max log size, any advice on how to proceed, I tried to shrink it and it will not shrink. Can I create another ...
  • 21
2 votes
4 answers
409 views

What is the impact of power failure on recovery assuming checkpoint has not occurred?

When an INSERT query is fired, SQL server records this in its log and sends the confirmation to user that query is complete. Meanwhile it also updates the data page. Both of these (log and data page) ...
  • 2,792
0 votes
1 answer
48 views

All file in redo log group corrupt

ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/u02/oraredo/O12C/redo02b.rdo' 0RA-00312: online log 2 thread 1: '/u02/oraredo/O12C/redo02A.rdo' i got ...
-2 votes
1 answer
34 views

During restore, how does sql server know the point until where the tlog record is applied to the data page?

During restore, when the tlog records are applied to the data page, redo, undo phase, how does sql server know which transactions are already present in the data page and which transaction to start ...
  • 2,792
0 votes
2 answers
45 views

Do I need to be carefull about log shipping log backups truncating tlog?

We have full backups running every Sunday. Differential every day. Log backups every hour. This is all configured as part of our recovery strategy. I have been requested to configure log shipping on ...
  • 2,792
3 votes
1 answer
163 views

Manual log backup makes restore strategy useless upto next full backup only?

Suppose I do full backup at 12am. Then log backups every hour. Then suppose at 8.30pm I do a manual log backup (thus truncating the log) and delete this file. Thus the subsequent log backup at 9pm ...
  • 2,792
5 votes
2 answers
592 views

Will non copy only full backup impact transaction log backups chain?

We have full backups and log backups configured. During restore we apply full backup and subsequent log backups. I know that if I take a manual full backup (non copy only mode) then it will break the ...
  • 2,792
0 votes
1 answer
56 views

What does this mean "index is also maintained in the transaction log"?

"Index is also maintained in the transaction log" Does this mean when a table having an index is queried, the entire index is pulled into the memory and subsequently logged into tlog?
  • 2,792
0 votes
2 answers
203 views

Is there an InnoDB max transaction size and what happens if it is reached?

I would like to know if there is a max transaction size for InnoDB. Probably it is innodb_log_file_size * innodb_log_files_in_group + innodb_log_buffer_size? And this max size would be shared between ...
  • 103
2 votes
1 answer
165 views

Need to determine log space required for a large transaction (MSSQL)

I need to update billions of rows in a partitioned table; it's also likely that the updates will move data between partitions. I'm trying to figure out how much additional log space I would require to ...
3 votes
2 answers
315 views

Why does a Full Backup restore of my database give me the error "RESTORE can only create a database when restoring either a full backup..."?

I've setup a maintenance plan to take a daily Full backup of all databases on my server. I've also setup a second maintenance plan to take Transaction Log backups every 5 minutes. The Transaction Log ...
  • 26.5k
0 votes
2 answers
161 views

Why is it not important whether data and log files share the same disk or not in a SAN? [closed]

I have a hard disk. I create 3 drives on it. One for OS. One for sql data files. One for sql log files. However I am reading that suppose my storage layer is not a hard disk, but a SAN based ...
  • 2,792
0 votes
0 answers
36 views

Shrinking log files in SQL Server 2014

I ran sp_Blitz in our Production server over the weekend and it output a few results regarding the log file growth. Example: The database [MYDB] has a 104 GB transaction log file, larger than the ...
1 vote
1 answer
491 views

postgres 11, pg_xact, is it possible to purge very old files?

Under PGDATA, there is the pg_xact directory containing the transaction logs. Every night we stop and restart the postgresql daemon. We have no replicas. We have files older than a month in this ...
  • 121
2 votes
1 answer
99 views

Does a fragmented database cause the transaction log to grow faster?

Hoping to get some clarity here as I've reached the limits of my SQL knowledge. The company I work for sells products storing data in Microsoft SQL Server databases, but our focus isn't really on the ...
1 vote
2 answers
98 views

Track SELECT statements on specific tables

I'm searching for a way to keep track of any SELECT statements on a specific table in a database where logs are not enabled. I can't find any solutions to this except to force people to use a stored ...
0 votes
0 answers
63 views

transaction log grows despite recovery=simple. SHRINKFILE does not release space

I have this script that restores a DB, sets recovery=simple, trims some tables & shrinks files: RESTORE DATABASE [ABC] FROM DISK (....) ALTER DATABASE [ABC] SET RECOVERY simple go exec [ABC]..trim ...
  • 1,005
0 votes
1 answer
81 views

Turning off Secondary Replica in Always on availability group for maintenance

We are planning to turn off the secondary synchronous replica in our always on availability group for few hours for maintenance . Once we are done with the maintenance then we will need to failover ...
  • 1,007

1
2 3 4 5
16