Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

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
0
votes
1answer
210 views

Database Log Reuse Wait shows AVAILABILITY_REPLICA but database is no longer part of an availability group

My database log is growing out of control, I recently removed the database from an availability group by when I look at the log reuse wait description it shows "AVAILABILITY_REPLICA" I have tried to ...
2
votes
2answers
139 views

Corruption in mariadb binlog

I have an installation with mariadb version 10.0.21. I need to backup the binlog file every 5 minutes to TSM, but as the binlog is being written every second, i am afraid of the file getting ...
0
votes
3answers
49 views

how to automate DB restore to another server?

The plan is to have 1 backup set that will contain full and transactional log backup. (Server1) There is already a scheduled job that will append any transactional log to the file after 30 minutes. (...
2
votes
3answers
40 views

Does Postgres automatically rotate WAL files out (pg_xlog) if archive_mode is off?

I'm relatively new to PostgreSQL administration and I'm attempting to pick up the nitty gritty details as I go. My question is, assuming I have no need for disaster recovery and I don't turn on ...
12
votes
2answers
619 views

During a log backup is the data backed up to the start or end of the operation?

Say I am running a log backup, and that log backup takes 10 minutes to complete. During that 10 minute window, further transactions are run. Given the below example, which transactions does the log ...
3
votes
1answer
83 views

Decrypt T-SQL log backup header and read LSN

For some reason I need to read the LSN from the T-SQL logs backups without restoring them or even their headers (I assume even restoring only their headers will change the LSN on the database side too,...
8
votes
2answers
662 views

Why is it not recommended to use RAID 5 for a log file? [duplicate]

While reading SQL Server Query Performance Tuning written by Grant Fritchey, I found it difficult to understand the following part: avoid RAID 5 for t-logs because, for every write request, RAID&...
2
votes
2answers
38 views

Disable redo logs on MariaDB

The title is pretty explicit, I just want to disable completely redo logs on MariaDB 5.5. This is more of a testing database that might eventually be used for a small web forum, but I don't care ...
0
votes
2answers
226 views

Too many VLFs - How do I truncate them?

I have a database that has log file of around 64 GB and the VLF count for the same is 500+. Almost all of them are active. I checked the open transactions and the oldest transaction on that database ...
1
vote
1answer
188 views

Hsqldb transaction log rollback cause

I'm fairly new to the database world but I had to use an hsqldb-2.0.0 database for a netflow collector software. As the software shows no output even if the client is collecting the flows I ...
0
votes
1answer
236 views

Is there a routine in MySQL similar to fn_dblog in SQL-server?

I already search about fn_dblog and it is about transaction log and it is used in SQL SERVER. if there any way to do transaction log in MySQL like fn_dblog? i can't find one
6
votes
3answers
499 views

SQL log file 2TB is not enough, now what?

This is a hypothetical question, that flows from the answer by sepupic where they explain that 2TB is the physical limit for SQL log file. if you need it more than 2Tb you add the second log file. ...
2
votes
1answer
46 views

what could be preventing my transaction log autogrowth to be set to UNLIMITED?

I have been trying to set the autogrowth of one of my databases to unlimited but despite whatever I do it remains the same. First thing I have checked if something is holding any locks to my database:...
0
votes
1answer
943 views

Innodb: When bulk inserting or updating - how to disable transactions?

When doing a mass insert like INSERT INTO SELECT FROM the speed benefit of doing that in chunks of 10k records can be enormous, in my tests a query that was running for 5 days took only 2 hours using ...
11
votes
4answers
2k views

Why would shrinking TEMPDB log lead to slowness?

I recently accidentally shrunk tempdb log to almost 0 after getting alerted that log drive filling up. I am told that it will lead to slowness. Can someone please explain why it will lead to slowness?
0
votes
1answer
505 views

Impact of shrinking transaction log file after transaction log backup in SQL Server

In SQL Server, is there any performance issue if we shrink log file after taking the transaction log backup. If yes, then please explain the reason.
0
votes
1answer
43 views

Azure Backup SQL VM - Not shrinking log file after transaction log backup

I am using Azure Backup to take a transaction log backup from my SQL 2016 Enterprise VM every 15 mins. It also takes one full backup daily. The db is using Full recovery model. Problem is that the ...
0
votes
1answer
359 views

Log Shipping failover when Primary is not accessible

Log Shipping Failover I have set up log Shipping with Cluster. In this scenario I want to simulate a situation when the whole cluster becomes inaccessible. My Steps for failover will be 1) Copy ...
0
votes
2answers
34 views

How does pausing a SQL Server mirror affect transaction logs on the principal?

One of our two datacenter's is undergoing maintenance for a minimum of 72 hours, but it could be up to 5 days, and I have a very old legacy SQL Server running a production service as a mirror across ...
2
votes
2answers
101 views

Trying to read a log file encrypted by TDE

I have a full chain of full backups and log backups. I am looking to investigate exactly what transaction was done by a certain user in a given period of time. The database (thus all backups) are ...
0
votes
1answer
402 views

Unexpected growth Transaction log file 100 GB which is part of Transactional Replication

I configured transactional replication on SQL Server 2014/Windows Server 2012. The database size is only 30 MB, but log file size is 100 GB. Every day, the log file size is growing 5-7 GB. Database ...
0
votes
2answers
52 views

What is the purpose of the NOLOGGING clause on Oracle database TABLES and INDEXES

I was wandering what is the practical purpose of using the NOLOGGING clause on tables and indexes. For what I know from the Oracle documentation, we can prevent the database from generating redo logs ...
2
votes
2answers
336 views

Concurrent backups (full and transaction) and testing recovery model

My intent is to test my recovery model and setup log shipping to replicate a database on another server. I have a database called AdventureWorks on a primary sql server. Presently SQL Backup performs ...
0
votes
1answer
80 views

SQL Server with small database size and huge log file size

I have several databases, for some log file size is growing more than normal even though very little data is added to few table around 50-100 rows a month. i.e. Database 12 data file size is 79MB and ...
2
votes
3answers
7k views

Large transactional log won't shrink after transactional backup [duplicate]

I'll start by saying our databases use full recovery mode and we run SQL Server 2014. Over the last few days our backups appeared to spiral out of control to the point that our full/differential ...
3
votes
1answer
47 views

log_reuse_Wait_desc always return LOG_BACKUP even right after log backup

We are using SQL Server 2012 standard Edition (SP4)- 11.0.700.0(x64) (Build 14393)(HYpervisor). Data File Size = 171 GB Log File Size = 106 GB When I queried sys.databases to see the ...
0
votes
2answers
90 views

SQL Server - Log file not shrinking in AlwaysOn group

We have three-node AO setup in SQL Server. Primary node, one secondary node in Sync mode and another secondary node in Async mode. Main Database on Primary node, log file-size is 434GB, Used 99.91%. ...
1
vote
0answers
122 views

Error 9003 Log Scan is Invalid Error When Restoring Transaction Log After SQL2016 SP2 + CU8 Upgrade

We are using SQL 2016 Enterprise Edition and have recently applied SP2 + CU8. We were previously on SP1 + CU6. Since the update we are getting the following error when restoring transaction logs on ...
0
votes
1answer
73 views

Error 23 on transaction log backup, full backup works fine

I have a database with the database files on the E: drive, and logs files on the D: drive. I'm getting the following error when trying to run a transaction log backup: Read on "D:\LOG\AOA_Log.ldf" ...
1
vote
1answer
41 views

Restoring .trn files on a regular basis to a backed-up database

We have been given a .bak file of a database from our vendor on a completely different network. They are placing their .trn files every 15 minutes on a sftp for us to pickup and write to the .bak file ...
2
votes
0answers
46 views

What do I need to check why log_reuse_wait_desc keeps coming back to REPLICATION

What should I do to check or need to do why log_reuse_wait_desc is REPLICATION? A database of ours keeps coming back to REPLICATION for the log_reuse_wait_desc after essentially following this ...
0
votes
1answer
72 views

Log truncation issue in AlwaysOn AG

We have four nodes in our AG. After the weekly index maintenance job, the log drive became full. We enabled CDC in one of our database, so we disabled CDC to truncate the log. Then we ran 'checkpoint' ...
1
vote
2answers
16 views

Using MySQL binary logs to keep backups between database dumps?

I currently take a full database backup using mysqldump once a day. I'd like to make this more granular (e.g. every 15 minutes), but can't just take more full dumps due to size constraints. Can I use ...
2
votes
2answers
88 views

Does Log File contain both committed and uncommitted transactions?

My question is related to SQL Server architecture. Mainly log file. In the picture below it says that when transaction is committed, log record in LOG CACHE will be moved to Log file. Does it mean ...
1
vote
1answer
67 views

Why do we add NO_TRUNCATE and NORECOVERY options while taking tail log Backup?

Assume that my mdf file is deleted and I set my database into single user mode. The first thing that I do is take tail log backup. My question is do I need to add NORECOVERY option while taking tail ...
-2
votes
1answer
50 views

How to fetch transaction details for Logs Table in SQL Server

I want to create a tracking table for changes made to the database. A tracking table called Logs must be created into which all changes (insert and delete) that are made in the database will be ...
2
votes
2answers
77 views

Log file won't shrink - suggestions?

I have a 225GB DB with a 390GB log file, which has only recently exploded in size. I am trying to shrink it - per these instructions: https://www.brentozar.com/blitz/transaction-log-larger-than-data-...
7
votes
2answers
63k views

Transaction log full due to log_backup

I am testing how certain operations are logged under different recovery models.Below are steps i have did till now 1.create database in full recovery model 2.take backup 3.create a table and insert ...
0
votes
2answers
57 views

Not able to delete transaction log backup file

I have a big SQL Server database (~1.5 TB) and i didn't take log backup of that database for last two weeks for some reason. Today i found that the .ldf file of that database became huge around 1.5 TB....
0
votes
1answer
428 views

DB2 big strange problem - database is returned to CIRCULAR mode but archive logs keep coming to old archive location

my DB2 database (v 9.7) is in Circular mode. Current Logging Type:Circular (USEREXIT) = OFF (LOGRETAIN) = OFF (LOGARCHMETH1) = OFF (LOGARCHMETH2) = OFF (LOGPRIMARY) = 10 (LOGSECOND) = 10 Path to log ...
1
vote
1answer
98 views

Transaction log rollback broke Log Shipping chain?

Is it correct that performing a transaction log restore to a previous point in time will break the log shipping chain? If this is correct, is there a way to recover from this situation without having ...
1
vote
1answer
124 views

TempDB Transaction log not releasing space

Every time I complete a transaction in tempdb, the temp log grows, however the log doesn't seem to release the space once the transaction has completed.The log usage percent is 43.4% but seems to ...
2
votes
1answer
210 views

The transaction log for database 'SolarWindsOrion' is full due to 'XTP_CHECKPOINT'

To start, I am a database novice. I thank you for any help in advance. I have a solarwinds SQL windows 2017 database in full recovery mode in an availability group. The database is roughly 2GB. ...
0
votes
1answer
93 views

Transaction Log usage fluctuation

The Transaction Log Usage fluctuating quite frequently. When I run DBCC SQLPERF('LOGSPACE') it shows Log Space Used (%) as 3% which is like 650MB while at very next minute or two when I run it again ...
1
vote
2answers
176 views

Tail log backup query in log shipping reversal

I am doing log shipping failover and failback configuration in SQL Server 2008 R2 environment. During the Failover, I am taking the tail log backup of primary database with query: Script to backup ...
2
votes
4answers
587 views

fn_dblog not showing records while LDF file is large

I need to find some specific transactions in the transactionlog file, so I use fn_dblog function. But the function is only returning a few rows. Meanwhile the LDF file for my database is a couple of ...
0
votes
1answer
69 views

How SQL Server transaction works with log records

My textbook says: Starting when you begin a transaction, your changes are isolated from other users. What you’re doing is visible to you only,and isn’t really done until you COMMIT — although it ...
11
votes
1answer
987 views

Is ALTER TABLE … DROP COLUMN really a metadata only operation?

I've found several sources that state ALTER TABLE ... DROP COLUMN is a meta-data only operation. Source How can this be? Does the data during a DROP COLUMN not need to be purged from the underlying ...
0
votes
1answer
56 views

PostgreSQL >= 10 does not generate WAL if there are no transactions

I noticed that PostgreSQL >= 10 does not generate WAL if there are no transactions. In previous versions, PostgreSQL generated WAL at the expiration of archive_timout, because it wrote some empty ...
2
votes
0answers
63 views

When updated records get written to the real physical pages in mdf file?

I'm a beginner in SQL Server, just some questions on transaction. My textbook says: A checkpoint is a periodic operation that forces all dirty pages for the database currently in use to be written to ...