We’re rewarding the question askers & reputations are being recalculated! Read more.

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
3answers
47 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
34 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 ...
3
votes
1answer
81 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,...
12
votes
2answers
615 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 ...
8
votes
2answers
656 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
34 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 ...
6
votes
3answers
496 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:...
11
votes
4answers
1k 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
39 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
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 ...
0
votes
2answers
51 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 ...
0
votes
1answer
78 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 ...
3
votes
1answer
42 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 ...
1
vote
0answers
107 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
72 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
40 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 ...
0
votes
1answer
70 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 ...
1
vote
1answer
59 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
2answers
84 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 ...
0
votes
2answers
80 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%. ...
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-...
-2
votes
1answer
49 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 ...
1
vote
1answer
119 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 ...
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
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
974 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
55 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 ...
1
vote
1answer
62 views

Trans Log fills during delete, conceptual question

I'm learning everything I can about backups and transaction logs and such. I want a little feedback on if I'm understanding this, so I have a conceptual question. First, here is what I observed: I ...
1
vote
1answer
230 views

Transaction log is full (due to NOTHING)… but this database is in simple recovery mode

I'm supporting an antedeluvian webapp (soon to be retired) that still uses "aspnetdb" for its auth system. I was doing some work in prep for its retirement on my test environment, when I found my ...
1
vote
2answers
66 views

Transacton log backup

What happen with the active transaction log when I stop SQL Server? Under the stop process all transactions will write to the db? Or will put when the db will starting... Is it possible when I stop ...
2
votes
1answer
42 views

SQL Server Replication and Recovery Model

In SQL Server for Replication when recovery model is not very important then how it saves the transactions and also keeps the secondary instance in sync? I tried to check on MSDN and found the ...
1
vote
0answers
151 views

Transaction Log File corruption on Mirror server SQL 2017

Wondering if anyone has encountered database transaction log file corruption when database mirroring is enabled in SQL 2017 running on Windows Server 2019, on the Mirror server? If so, curious what ...
2
votes
1answer
206 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. ...
1
vote
1answer
68 views

SQL Server shrink log after full and log backup

I have an SQL Server database in Full recovery model. The data file is just 1 GB in size, but the log file is 20 GB. I have a space problem, so I took a full backup and then a log backup with ...
0
votes
0answers
15 views

Where is transaction log truncated to if full dump is taken while txn log dump is running?System info: MS SQL 2014 Enterprise

We have a very large database with a very large (over 1.5 TB) log. A transaction log dump was started, and while it is running, a full DB backup was run (successfully). The log dump is still running. ...
1
vote
1answer
39 views

Can the LOG file fill up from index rebuilds where the indexes are not fragmented?

Working on SQL Server 2014. I have noticed that a log file (60GB) on a database has filled up within the past day. To my knowledge, I am the only developer using this database and I have not been ...
1
vote
3answers
55 views

Adding a Far DR secondary server Log shipping on already configured SQL server

We have a setup where there are 3 SQL Server instances in which 1 primary,1 Near Secondary,1 Far Secondary. So the there is already log shipping configured between Primary and Near DR Secondary. Now ...
0
votes
0answers
79 views

Differences Between Setting Up Transaction Log Shipping through Wizard vs Scripts

There appears to be a fairly significant difference after setting up transaction log shipping (in Standby/ Read-Only mode) through the SSMS wizard, which generates jobs that then call on outside .exe ...
1
vote
2answers
44 views

Can anything reveal that 2 independant entries were created or updated at the same time?

Imaginary example, simplified to better explain the question. Let's say I have a form with the following fields : user email who did you vote for in 2012 who did you vote for in 2016 When submitted, ...
8
votes
3answers
965 views

transaction log in RAM or physical file?

I'm a beginner in transaction, just a question on transaction log. We know that when we commit a transaction, the changes are written to the transaction log, but is transaction log in RAM or physical ...
1
vote
1answer
94 views

What will be inside the last differential backup file?

I am new in DBA and while learning different types of backup, one question came into my mind. So the question is, what will be inside the last differential backup, just the sum of the previous ...
1
vote
0answers
54 views

SQL Server Transaction Log Full affect on FileStream [closed]

These are general question I would like to receive some inputs from experts with BLOB SQL Data. How will SQL Server Transaction Log (.ldf file) linked to SQL Server FileStream Is it different from ...
7
votes
2answers
2k views

Why is my log file so massive? 22gb. I am running log backups

I can't seem to figure out the answer. I've seen multiple answers like this: Why Does the Transaction Log Keep Growing or Run Out of Space? and everyone talks about running back ups on your log file ...
2
votes
0answers
70 views

Change Tracking generating enormous transaction logs

I'm troubleshooting a 24GB DB that is generating approx 2GB (compressed) of transaction logs/60min. Using the following command I'm tried to examine the contents of the log: SELECT Operation, ...
0
votes
2answers
43 views

The actual cause of transaction log full under below scenario

in one of the instance, it shows The transaction log for database 'DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases The instance ...
1
vote
1answer
64 views

Unable to trace the transaction log space

im new to sql server. One of the instance encounters a scenario it shows the transaction log full, and the database need to perform backup in order to solve the issue. I want to replicate the ...
0
votes
0answers
69 views

Can MSSQL CDC be used to capture old committed transactions?

I can successfully capture change data from mssql CDC by issuing the query cdc.fn_cdc_get_all_changes_capture_instance ( from_lsn , to_lsn , '<row_filter_option>' ) Here, from_lsn is the ...