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
0 answers
48 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 ...
user avatar
  • 2,968
0 votes
0 answers
19 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 ...
user avatar
4 votes
3 answers
893 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,375
1 vote
1 answer
78 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
  • 97
1 vote
2 answers
79 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
48 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
  • 171
3 votes
1 answer
99 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
151 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
39 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
444 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
0 votes
1 answer
36 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
117 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
83 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
46 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
180 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
213 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
44 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
90 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
130 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
437 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
52 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
392 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,375
0 votes
1 answer
37 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,375
0 votes
2 answers
41 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,375
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,375
5 votes
2 answers
488 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,375
0 votes
1 answer
43 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,375
0 votes
2 answers
117 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
152 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
234 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.6k
0 votes
2 answers
107 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,375
0 votes
0 answers
21 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
17 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
291 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
76 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
67 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
61 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
64 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
48 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
84 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,375
-2 votes
1 answer
33 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
111 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
256 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
66 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
244 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
605 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,137
2 votes
2 answers
214 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
85 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

1
2 3 4 5
16