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
4 votes
3 answers
860 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 ...
user avatar
  • 2,249
1 vote
1 answer
35 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 ...
user avatar
  • 77
-2 votes
2 answers
85 views

SQL Server 2017 transaction log does not shrink logfile after rebuild index [closed]

I have 10GB database and i have configured transaction log shipping. My rebuild indexes job executed indexes fillfactor changed from 100 to 70 and transaction logfile has growth 60GB. I have tried so ...
user avatar
1 vote
2 answers
76 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 ...
user avatar
  • 587
0 votes
1 answer
39 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 ...
user avatar
  • 169
3 votes
1 answer
91 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 ...
user avatar
  • 157
4 votes
3 answers
132 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 ...
user avatar
  • 55
0 votes
2 answers
35 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 &...
user avatar
  • 103
3 votes
1 answer
440 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 ...
user avatar
-1 votes
1 answer
32 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 ...
user avatar
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 ...
user avatar
2 votes
1 answer
96 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 ...
user avatar
0 votes
2 answers
69 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 ...
user avatar
  • 1
0 votes
1 answer
41 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 ...
user avatar
  • 101
1 vote
3 answers
156 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 ...
user avatar
0 votes
1 answer
172 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 [...
user avatar
2 votes
1 answer
31 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 ...
user avatar
2 votes
1 answer
89 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 ...
user avatar
  • 23
1 vote
3 answers
102 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 ...
user avatar
2 votes
1 answer
356 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' ...
user avatar
  • 21
0 votes
3 answers
48 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 ...
user avatar
  • 21
2 votes
4 answers
390 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) ...
user avatar
  • 2,249
0 votes
1 answer
36 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 ...
user avatar
-2 votes
1 answer
33 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 ...
user avatar
  • 2,249
0 votes
2 answers
40 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 ...
user avatar
  • 2,249
3 votes
1 answer
152 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 ...
user avatar
  • 2,249
5 votes
2 answers
456 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 ...
user avatar
  • 2,249
0 votes
1 answer
39 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?
user avatar
  • 2,249
0 votes
2 answers
94 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 ...
user avatar
  • 103
2 votes
1 answer
151 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 ...
user avatar
3 votes
2 answers
228 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 ...
user avatar
  • 24.2k
0 votes
2 answers
95 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 ...
user avatar
  • 2,249
0 votes
0 answers
20 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 ...
user avatar
0 votes
0 answers
11 views

postgres 8.4, pg_clog: di is it possible to purge very old files?

Under PGDATA, there is the pg_clog directory containing the transaction logs. Every night we stop and restart the postgresql daemon. We have files older than one year in this directory. Their ...
user avatar
  • 121
1 vote
1 answer
240 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 ...
user avatar
  • 121
2 votes
1 answer
71 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 ...
user avatar
1 vote
2 answers
54 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 ...
user avatar
0 votes
0 answers
60 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 ...
user avatar
  • 1,005
0 votes
1 answer
58 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 ...
user avatar
  • 1,007
0 votes
1 answer
42 views

Actual used and free space in WAL segments

I am new to the PostgreSQL world. I have an experience in administrating Microsoft SQL Server and Oracle databases. In SQL Server, we are able to find how much space is actually used in pre-allocated ...
user avatar
  • 1,163
2 votes
1 answer
82 views

Does simple recovery model not log bulk inserts?

I have a db in full recovery model. There is a planned insert of a million rows that needs to be done next week. In order to not increase the log size, I am told there are 2 options. Option 1 is ...
user avatar
  • 2,249
-2 votes
1 answer
29 views

Strategy of restoring database with multiple logging files (24 total) at once

I am trying to see whether it is safe and efficient approach if I run a multiple line of SQL command to restore multiple (total 24) logging files at once. These files are formatted in bak file format, ...
user avatar
  • 195
2 votes
2 answers
94 views

In SQL Server, what's the impact of a checkpoint to log records in log buffer?

Does checkpoint operations flush everything in the log buffer to log file? Or just the log records relating to the dirty pages that are about to be flushed? I found some inconsistent description about ...
user avatar
0 votes
1 answer
203 views

Index Reorganize fails

I am using the default SQL Server Maintenance task to reorganize index only which was running until I got the below error. Executing the query "ALTER INDEX [PK_abc] ON " failed with the ...
user avatar
  • 1,007
1 vote
1 answer
61 views

Can I restore my SQL Server database to a point before the last full backup with my current backup strategy?

If I have this backup strategy(weekly full backup and 1 hour log backup), can I restore the database to the green highlighted period of time? Btw, does log backup 2 includes all log records that have ...
user avatar
1 vote
1 answer
213 views

Reduce transaction log file utilisation

I have a SQL Server 2017 database that currently has an 80GB data file and a 90GB log file. I want to get the log file down to a more manageable size, because I am about to run some big scripts which ...
user avatar
2 votes
3 answers
502 views

Urgent help with TempDB log file growth mystery

I need some urgent guidance for a tempdb issue for past last week: The tempDB log file is growing starting a specific time, lets say 2 PM. And almost continue to grow upto 150 GB and then comes down ...
user avatar
  • 2,127
2 votes
2 answers
206 views

Fast recovery vs Accelerated database recovery in SQL server 2019

while comparing the features available in different SQL server editions, i see that Fast recovery and Accelerated Database recovery are 2 separate features. I am unable to find any documentation ...
user avatar
1 vote
1 answer
76 views

SQL Server: Should the same log records be backed up multiple times?

I came to SQL Server database administration from an Oracle DBA background. So maybe naturally, I always try to understand the differences between the two DBMSs as well as the similarities. One ...
user avatar
  • 403
2 votes
1 answer
75 views

Exponential log file growth on quiet database with log backups

I have a small (~3GB) database in Full recovery mode, with full backups at midnight and transaction log backups at noon. Despite being a testing database with virtually no activity, the size of the ...
user avatar

1
2 3 4 5
16