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
2
votes
1answer
19 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 ...
-1
votes
0answers
9 views

What is a best place to store inital value of variable at system with Immediate Update

The question is mostly theoretical and not related to any real DBMS. Let's simplify situation and skip locking and other algorithm of preventing "losing update" problem in our question. ...
2
votes
1answer
74 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 ...
0
votes
2answers
22 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
1answer
98 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' ...
0
votes
3answers
29 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 ...
2
votes
4answers
378 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) ...
0
votes
1answer
33 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
1answer
28 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 ...
0
votes
2answers
36 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 ...
3
votes
1answer
144 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 ...
4
votes
2answers
365 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 ...
0
votes
1answer
33 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?
0
votes
2answers
43 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 ...
2
votes
1answer
143 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
2answers
159 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 ...
0
votes
2answers
63 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 ...
0
votes
0answers
15 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 ...
0
votes
0answers
7 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 ...
1
vote
1answer
75 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 ...
1
vote
0answers
48 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
2answers
42 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
0answers
57 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 ...
0
votes
1answer
35 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 ...
0
votes
1answer
27 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 ...
2
votes
1answer
61 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 ...
-2
votes
1answer
26 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, ...
2
votes
2answers
47 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 ...
0
votes
1answer
102 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 ...
1
vote
1answer
56 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 ...
1
vote
1answer
63 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 ...
2
votes
3answers
279 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 ...
2
votes
2answers
174 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 ...
1
vote
1answer
57 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 ...
2
votes
1answer
53 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 ...
6
votes
3answers
371 views

Recommended SQL Server transaction logs settings for ephemeral databases?

I'm looking for recommendations on how should I set up transaction logs for databases that are used by a development team. These databases are ephemeral in the sense that we never care about data ...
0
votes
2answers
53 views

What could be the reason for shrinking the transaction log file before and after the database backup?

I'm an accidental DBA. I'm the only person in a company who has some experience with SQL Server and I have to deal with a system inherited from another company after it was acquired. People who ...
0
votes
1answer
48 views

Delayed durability for reporting database

I have a small database which basically run a couple of reporting SPs. Data gets extracted from an OLTP database and refreshed everyday and most tables in this database get truncated everyday. The ...
-1
votes
2answers
481 views

Transaction log: small transactions vs large transactions for speed [closed]

I am trying to learn how the transaction log works and have been studying it for a couple of day. Some operations become faster when done as many small transactions, e.g. deleting many rows in small ...
0
votes
1answer
57 views

Log files in Always on Disaster Recovery Situation

In my Always on Avaliability group setup there are four nodes in the cluster in two datacenters, an avaliability group has for replicas, one on each node. Two replicas in the same datacenter have sync ...
0
votes
2answers
122 views

Transaction log space used in percent - how to clear or empty

I am trying to learn more about the transaction log, which I think is the bottleneck of many Stored Procedures (used for reporting) which do data modifications. So I tried below: select * from sys....
6
votes
1answer
269 views

Do Log Backups depend on Differential Backups?

I have the following backup strategy set up: My understanding so far, was: Differential backups do not truncate the transaction log, so, there's no relation between Diff and Log Backups and both ...
1
vote
1answer
87 views

Why does the log file grow larger than data file for an availability group database on the secondary replica?

I've got a SQL Server 2016 AG set up with only two replicas (primary and secondary). Both synchronous, non-readable secondary, and backup the primary only. Transaction Log backups occur on the primary ...
0
votes
1answer
41 views

When should you shrink your transaction log?

We do log backups every 3 minutes but despite that the transaction log has still managed to grow to 200+ gb on disk. Its almost completely empty after log backup. How do you know if your transaction ...
1
vote
4answers
190 views

Why can't a committed transaction be undone

Once a transaction has been committed, we cannot undo its effect by aborting it. We have the log file that has all information to undo a committed transaction, so why is this not possible? We should ...
2
votes
1answer
273 views

How crash recovery process works in SQL Server?

I would like to ask a question regarding recovery process while restoring database operation. Let's say I restored a full backup with NORECOVERY followed by restoring two t-log backups with NORECOVERY....
0
votes
1answer
108 views

How to predict database size?

One of requirments for database which I am building is to predict database size to prepare hardware for production environment. Aapplication has 2 main tables wchich are partitioned. Tables are in 8 ...
0
votes
1answer
26 views

Tools for looking at transactions inside of transaction log backup file? [closed]

Are there any tools (commercial or open source) for looking at the contents of a transaction log backup file? Occasionally, get a large transaction log backup file and would like to see the contents /...
0
votes
1answer
38 views

Why first_lsn of full backup and 1st transaction log backup are same?

shouldn't first_lsn of the 1st transaction log backup be 36000000022400001 instead of 36000000022100001? In this case it equals to first_lsn of full backup.
2
votes
1answer
50 views

TransactionLog not getting truncated in AlwaysOn Cluster

Folks, stuck with a wierd transaction log truncation(reuse) problem in an AlwaysOn cluster and running out of options, the transaction log is not getting truncated and keeps growing. Only one database ...

1
2 3 4 5
15