All Questions
Tagged with transaction-log transaction
27 questions
1
vote
1
answer
32
views
Database crash just before appending the checkpoint entry to write ahead log
From what I read about WAL, its an append-only file where all the operations to the DB are written to before the operations are actually performed to the data.
There is also a concept of a "...
0
votes
1
answer
105
views
When can we say a transaction is committed in mysql?
I know the transaction can ensure that all the queries are committed or rolled back, but what is the point at which we can say ok, the transaction has been committed and all changes are durable?
I ...
- 13
0
votes
1
answer
231
views
Sql Server Open Transactions
I was notified that the transaction log drive for a sql instance I manage is filling up. I jumped on, found which transaction log was growing, and went to take a look at why the tlog was growing. ...
- 688
3
votes
1
answer
760
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 ...
- 335
2
votes
2
answers
4k
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 ...
- 817
2
votes
4
answers
682
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 ...
- 111
2
votes
1
answer
527
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....
- 1,303
0
votes
2
answers
292
views
Why does SQL Server start REDO phase from minLSN instead of last CHECKPOINT?
while reading an article from BOL, I stuck on the picture which explains the recovery process of database (without ADR):
Shouldn't Phase 2:Redo start from Last CHECKPOINT as till that point ...
- 1,303
3
votes
2
answers
3k
views
Is there a way to get a history of transaction log used space in SQL Server?
I would like to be able to look back and see what the used space was in transaction log over a given period. Not interested in growths or file size as they don't tell me how much space is actually ...
0
votes
1
answer
335
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 ...
- 151
5
votes
1
answer
500
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 ...
- 151
9
votes
3
answers
3k
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 ...
user180527
0
votes
0
answers
20
views
InnoDB: does `redo log` contain initial value and final value? or just final value?
start transaction;
update users set age = 34 where user_id = 3 // update age from 30 to 34 years old.
....
....
when I am updating the age of this user, InnDB will write redo log.
What's the ...
- 523
1
vote
0
answers
606
views
MySQL: why undo log is required in the processes of database recovery?
Let's imagine that an incident happens when you updates.
start transaction;
update users set gender = 1 where id = 3;
Undo log space is allocated.
Previous values are copied to undo log
Record of ...
- 523
1
vote
1
answer
439
views
Transaction log growing out of control on random times [duplicate]
We have a problem with one of our databases. At random times something causes our transaction log to swell up to 20x its original size (it basically fills up the whole HD, so 20x is not accurate since ...
- 11
2
votes
1
answer
695
views
Transaction log records in sql server
Based on Silberschatz's Database system concepts book, log file for a transaction has 3 records,
1 record for transaction start: T_i start,
1 for transaction changes on write(x) operation: T_i,X,...
user139992
5
votes
1
answer
3k
views
how to identify the transactions that are causing the transaction log to grow?
I have a database of 20 GB which insists in having its transaction log over 7GB.
when I used this script to find out the size of the biggest objects in that database, I see they are relatively small.
...
- 16.6k
1
vote
0
answers
321
views
LogFile Size increases by SSIS Package execution
On production server we have 125 GB free space. The SSIS package is doing some delete in Transaction. e.g. Insert 3 columns which are mainly business keys into a staging table and then deleting the ...
- 323
2
votes
1
answer
192
views
Can I truncate log in simple recovery mode? [duplicate]
I have a few SQL Server 2005 databases in simple recovery mode. The disk volume in which my transaction logs are stored had only 1GB free, when I started a stored procedure (massive delete in a single ...
- 41
7
votes
2
answers
35k
views
How to minimize log operations in SQL Server to avoid "log full" error
I have a database in production which constantly get log file filled, its a data warehouse and have many jobs/queries running for obvious. Below is the error which I am getting
Msg 9002, Level 17, ...
- 761
1
vote
1
answer
2k
views
Read transaction log records
I am reading Remus Rusanu article on tlog.In that article he demoed on how to read log payload by using dbcc page option .My question is ,is there a way to read logrecord output of fn_dblog(null,null) ...
- 2,989
1
vote
1
answer
605
views
Behavior of Oracle During Very Large Transactions
I understand that if a transaction is large enough to overflow all of the redo logs on an Oracle database, and the database is in ARCHIVELOG mode, the transaction will wait until the archive log file ...
- 142
1
vote
2
answers
1k
views
Transaction Log Backups
I am working on backup+recovery scenario and I came across a situation where I could not figure out how I would have to deal with it.
We have couple databases where sometimes there may not be any ...
- 326
2
votes
2
answers
6k
views
Shrink transaction log while transaction in progress
I have a several-GB import into my SQL Server 2005 (Standard Edition) database.
The import is separated into 4 parts, each part in its own transaction.
As you can guess, the transaction log files ...
- 183
3
votes
1
answer
5k
views
SQL Server - how transactions and transaction log work (simplified)
I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Let's consider we have a database with full recovery model.
Now, everything ...
4
votes
2
answers
3k
views
Oracle 11g see transaction or session start time
I want to know when a session or transaction started. The deadlock file doesn't give me this information. Is there some logfile that keeps these records?
I've got a transaction id "TX-1234-abcd", a ...
- 577
3
votes
1
answer
362
views
Does blocking always mean open transaction?
This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation (...
- 5,373