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.
832 questions
0
votes
1
answer
65
views
Migrating SQL Server databases using differential backups, NOT transaction log backups
I need to migrate approximately 10 SQL Server databases totalling 400 GB from one VM to another. Some databases are in simple recovery mode, while others are in full. Below is my general plan:
A day ...
- 1
0
votes
2
answers
75
views
Why can't I restore my SQL-Server database? [closed]
I have a bunch of files: two backups and a list of transaction logs.
The backups are called:
FB20241125_233033.bak
FB20241126_233040.bak
(The filenames correspond with the date/time the backups have ...
- 377
-1
votes
0
answers
74
views
Telerik OpenAccess: Change operation canceled by user [migrated]
I'm working on a C# application, using Telerik OpenAccess to perform modifications on an SQL-Server database.
While trying to set the status of an object ("Status" is one of the columns in ...
- 377
1
vote
0
answers
22
views
Oracle redo and log_buffer
I am trying to speed up a batch process. The issue is at the code level which keeps committing a few hundred times per second, for each record processed, though it should ideally commit after process ...
- 1,029
0
votes
1
answer
33
views
SQL Server VLFs marked as active despite no transactions
I've only noticed this issue on our dev environment with databases that have thousands of VLFs (1-4k range) due to abnormal data loads. I understand the recommendation of limiting the number of VLFs, ...
- 255
0
votes
2
answers
93
views
How to shrink log file for Azure SQL Database?
My Azure Sql Database transaction log file is about 1 TB in size, but only 1 GB is in use:
SELECT file_id, name, type_desc,
CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS ...
- 574
0
votes
0
answers
36
views
DB2 temporarily prevent log files from being written to the archive log dir?
DB2 11.5.7.0 on Linux
We've got a cron job that does:
cd ${ARCH_LOC}
tar -czvf /path/to-backups/${DB}_ARCHIVELOG_${NOW}.tar.gz S*.LOG && rm -vf S*.LOG >> ${SLOG}
ISTM that there's a ...
- 658
0
votes
1
answer
35
views
PRUNE LOGFILE PRIOR TO isn't deleting archived log files
DB2 UDB 11.5.7.0
$ db2 get db cfg for PRPT01D | grep LOGARCH
First log archive method (LOGARCHMETH1) = DISK:/udb/alogs/
Archive compression for logarchmeth1 (LOGARCHCOMPR1) = OFF
...
- 658
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 "...
-1
votes
1
answer
82
views
Size of MDF file is not getting updated after adding transactional log file(s) in SQL 2022
I am working on migration of SQL Server from SQL 2016 to SQL 2022.
I have a process where after database is restored (to "Standby / Read-Only" state) from Full tape (from a third vendor), I ...
- 251
0
votes
1
answer
67
views
Nothing happened in my table according to "fn_dblog", is that correct?
Yesterday, something happened at a customer site, and I would like to know if my analysis is correct:
In order to know, what happened, I launched the following query:
SELECT TABLE_NAME, COLUMN_NAME
...
- 377
0
votes
1
answer
160
views
How to fix checkpoint requests problems?
On PostgreSQL v15 every morning at 5:15 AM starts heavy inserts in our database and finishes around 7:00 AM. I try to see if there are some parameters that can be tuned like checkpoints.
I have ...
- 401
0
votes
2
answers
147
views
How to get current WAL usage per currently executing SQLs from application?
On PostgreSQL v15 on Red Hat 9 today our database has crashed. Database has crashed because there was disk full in pg_wal directory. We have pg_wal directory on separate disk from the rest of database....
- 401
0
votes
0
answers
28
views
What is the meaning of individual Resource Manager like Heap, Btree, XLOG etc from WAL file?
In PostgreSQL v15 I have installed pg_walinspect extension. I am trying to understand what is individual resource manager fields.
Like I understand resource manager tells us where individual data ...
- 401
2
votes
5
answers
191
views
Investigate why SQL Log file increased in size
We had one or more of our log files increase in size over night, causing our SQL logs disk to almost fill up. I've been asked to look at what could have caused this.
Can anyone suggest where to start ...
- 21
0
votes
1
answer
56
views
how can I add the date and time on the autogrowth query below?
while trying to prevent autogrowth from happening,
I got this nice script here.
however, if it does happen, I need to know how to find it out.
that I can do using this very nice script by Max Vernon.
...
- 16.6k
2
votes
2
answers
179
views
Understanding Recovery Forks in SQL Server: Meaning and Significance
I'm trying to understand the concept of recovery forks in SQL Server, particularly in relation to the sys.database_recovery_status view. The documentation mentions several columns related to recovery ...
- 2,062
0
votes
1
answer
66
views
Follow up to - How to restore transactions after full backup
Short Summary:
I'm trying to retire a computer that runs SQL Server and move to a new one. I want to do the following:
Backup a database on old machine and restore it on new machine, leaving the old ...
- 11
2
votes
2
answers
236
views
Transactional Replication - how to set up alerts/notification to send alerts before transaction log space is full on a Publisher?
I am setting up transactional replication on my 2008R2 server to 2012 distribution server to 2012 Subscriber server. All the three servers are on FULL recovery mode. Log backup is taken every 15 ...
- 37
0
votes
1
answer
255
views
How to remove secondary log file when SQL thinks it isn't empty
I had to add a log file to my database to do a one-time load of data in my SQL Server 2019 database in simple recovery model. I added the file with
ALTER DATABASE db ADD LOG FILE (NAME=log2,FILENAME='...
- 1
0
votes
1
answer
77
views
What happens if the Subscriber goes down - will the t-logs keep accumulating on the publisher in Transactional Replication?
I am trying to consider all the options before setting up transactional replication on my 2008R2 server to 2012 distribution server to 2012 Subscriber server. All the three servers are on FULL ...
- 37
0
votes
0
answers
27
views
Accessing RDS transaction logs
Greetings to everyone from a newcomer to this forum!
There is an RDS MySQL 5.7 database the transaction logs of which I would like to inspect: more specifically, upon attempting to add foreign keys to ...
- 101
-3
votes
2
answers
110
views
2x SQL Server 2019 in AlwayOn group - how to remove transaction log [duplicate]
We have a problem with our Microsoft SQL Server 2019. We have 2 servers in an Always On group with one database. Problem is the actual (real) database size which is about 1 GB, but it takes up around ...
- 1
0
votes
1
answer
70
views
SQL 2017 log reuse issue
I have a SQL 2017 Enterprise server where more and more VLFs are being consumed and none are being released. Log backups are running every 15 minutes. It is in an AG but this is working correctly (...
- 11
3
votes
0
answers
78
views
Are Innodb redo logs written atomically?
When flushing dirty pages from buffer pool to disk pages, it uses double write buffering to avoid half written pages. But redo logs do not have such mechanism. So, how does it prevent redo logs from ...
- 135
-1
votes
1
answer
132
views
How do you eliminate extra transaction log files?
I started a new job taking over for a guy that really did not know what he was doing.
He created multiple transaction log files for most of the databases on the server.
Apparently, he did not know how ...
1
vote
1
answer
82
views
Avoid logfile getting too large on SQL Server
I have a SQL Server stored procedure that essentially 'flattens' a complex database into a single, simplified, table in a separate database to allow users to query the data without needing to ...
- 13
0
votes
1
answer
60
views
Does Azure automated backup for SQL database on Azure VM have way to detect log chain?
I have SQL server on Azure VM.
If I enable Azure automated backups for the SQL server on Azure VM to backup the dbs (full/diff/log), and suppose if an admin performs a manual log backup (which ...
- 3,489
0
votes
1
answer
82
views
Ideal VLF size - SQL Server transaction log
Is there an ideal size for the VLFs in a transaction log. This is a system with AlwaysON and Transactional replication. I was wondering if there is any cons to creating large VLF's. Eg, lets say ...
- 1,758
0
votes
1
answer
496
views
what to do when database is not synchronizing in availability groups?
I am using
Jonathan Kehayias - Monitoring Availability Group Replica Synchronization
SELECT
ar.replica_server_name,
adc.database_name,
ag.name AS ag_name,
drs.is_local,
drs....
- 16.6k
0
votes
0
answers
73
views
Database log file is so big
My database log file is so big(400 giga) and data file is (50 giga)
My recovery method is simple and backup plan is daily full backup
Log file is configured to auto growth.
I checked open transactions,...
0
votes
0
answers
59
views
A 27GB Table Population causes the TLOG to grow to 211GB
my brain is fried.
I work in an organisation where every night we pull data from the source system into a staging area, then into production and because of this we have out DB's set to Simple Recovery ...
0
votes
0
answers
88
views
SQL Server 2014 Negative log. Stuck at CHECKPOINT. Unable to do anything
I have a database that is stuck at CHECKPOINT for hours and I already tried all solutions that have been suggested in this site with no avail and I'm starting to lose faith. Keep in mind that the ...
- 1
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
1
vote
1
answer
56
views
Release Database Transaction Log File Freespace In SQL Server 2016 [duplicate]
Good afternoon,
We have 4 node SQL Server configure in HA/DR. Periodically, we purge the old data to free up the space. Recently, after the SQL Job to purge the data was executed, we notice an ...
- 13
0
votes
1
answer
120
views
Log .ldf file not truncate after several times of log backup on secondary database
i have always on setup in production environment. Scheduled backup (transaction log backup) already in place every 2 hours using netbackup on secondary database.
The issue i am facing is sometimes the ...
4
votes
1
answer
482
views
Catch Block Inside of a Trigger Does Not Get Hit When a SP With Dynamic SQL Errors Out - Causing the Larger Transaction to Fail
I am not a fan of triggers and dynamic sql, however, what I am working with requires both.
CREATE TRIGGER [dbo].[GenerateDynamicFormItemViews] ON [dbo].[tblFormItems] AFTER INSERT, UPDATE
AS
BEGIN
...
- 629
0
votes
1
answer
92
views
Managing very large VLF
I'm investigating a database with a large transaction log. Looking at DBCC Loginfo I can see the database has 1200 VLF's, which is on the high side but not terribly concerning. What does worry me ...
- 33
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
0
votes
1
answer
167
views
Is there a stronger guarantee of minimal logging when running INSERT INTO [...] WITH (TABLOCK) on a heap than on a B-tree?
The documentation lists the following requirements for bulk imports with minimal logging
The recovery model of the database is set to simple or bulk-logged.
The target table is an empty or non-empty ...
- 801
3
votes
1
answer
350
views
Are there any possibilties to mitigate data loss risk on delayed durability
Due to poor disk performance I need to evaluate using the delayed durability setting in SQL Server 2016.
Microsoft states clearly, that this comes with a data loss risk because transactions are ...
- 175
1
vote
3
answers
354
views
Does a SQL Server Log Backup Include Log Records Backed Up in a Previous Full or Differential Backup?
I've been reading the Microsoft documentation on log backups in SQL Server and came across the following statement:
(Log Backup) Specifies a backup of the transaction log only. The log is backed up ...
- 1,219
1
vote
2
answers
851
views
Is it possible to create a database without a transaction log?
I was recently told that it is possible to create a new database in SQL Server without a transaction log. I've worked with SQL Server for a very long time and I've never seen it done and I didn't ...
0
votes
1
answer
107
views
What is the best option to remove duplicates in big heap?
Our Server is SQL Server 2016 Database with Full Recovery model.
We have table "A" - heap which has 280 millions of records and size 90 GB with 50 columns. This table contains around 5 ...
- 421
0
votes
1
answer
34
views
Understanding the Relationship Between Log Records and Modifications a Log Record Can Do
I am currently playing with SQL Server's fn_dblog function and have come across a concept that I'm having trouble understanding. In the function's result, there is a 'Page ID' column and a 'Slot ID' ...
- 1,219
0
votes
1
answer
90
views
Correlating SQL Audit transaction_id to LSN
I have a relatively simple question that is "Google resistant".
I have a SQL Database Audit spec that logs a transaction_id. I also have a Change Data Capture (CDC) spec that logs __$seqval (...
- 117
0
votes
0
answers
115
views
Backup SQL transaction log to Azure "append blob"?
We have long saved our hourly transaction log backups throughout the day by starting a new t-log file nightly, after the full backup is complete, and then appending to it hourly for the rest of the ...
0
votes
1
answer
456
views
What will happen when SQL Server log drive is full?
One of my production database log file drive filled up and I noticed below messages in SQL Server error log.
2022-02-13 03:16:24.500 spid72 Error: 9002, Severity: 17, State: 4.
2022-02-13 03:16:24....
- 1,219
0
votes
1
answer
113
views
Huge Transaction Log File - Full Database backup, but no Transaction log backups
I hope someone can help. Please be gentle, this type of admin is not what I usually do.
I have inherited a database which is set up to do a FULL database backup at 02:00 every day.
The transaction log ...
- 1
0
votes
1
answer
116
views
Transaction log file file size issues
Hope someone can assist me here. I have a DB in mirror mode, i.e. DB on sql1 mirror to sql2 and obviously recovery model is 'Full', and the SQL version is SQL server 2019. Currently, my transaction ...
- 1