Skip to main content

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
1 answer
47 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. ...
Marcello Miorelli's user avatar
1 vote
2 answers
96 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 ...
Just a learner's user avatar
0 votes
1 answer
33 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 ...
user3258623's user avatar
2 votes
2 answers
191 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 ...
Preety's user avatar
  • 27
0 votes
1 answer
63 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='...
Jake G's user avatar
  • 1
0 votes
1 answer
48 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 ...
Preety's user avatar
  • 27
0 votes
0 answers
18 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 ...
ΑΘΩ's user avatar
  • 101
-3 votes
2 answers
78 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 ...
TBS's user avatar
  • 1
0 votes
1 answer
46 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 (...
Bruce Benson's user avatar
3 votes
0 answers
72 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 ...
William's user avatar
  • 135
-1 votes
1 answer
64 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 ...
David Eaton's user avatar
1 vote
1 answer
70 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 ...
Andy's user avatar
  • 13
0 votes
1 answer
45 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 ...
variable's user avatar
  • 3,468
0 votes
1 answer
57 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 ...
DMDM's user avatar
  • 1,750
0 votes
1 answer
190 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....
Marcello Miorelli's user avatar
0 votes
0 answers
69 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,...
Abdelrahman Mamdouh 's user avatar
0 votes
0 answers
58 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 ...
Simon Thorn's user avatar
0 votes
0 answers
52 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 ...
Pedro's user avatar
  • 1
0 votes
1 answer
79 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 ...
Kaiyu Yang's user avatar
1 vote
1 answer
55 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 ...
rushabh138's user avatar
0 votes
1 answer
81 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 ...
zul nasir's user avatar
4 votes
1 answer
326 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 ...
Ross Bush's user avatar
  • 557
0 votes
1 answer
76 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 ...
AlexP012's user avatar
0 votes
1 answer
122 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. ...
Patrick's user avatar
  • 688
0 votes
1 answer
120 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 ...
J. Mini's user avatar
  • 499
3 votes
1 answer
324 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 ...
Mainecoon21's user avatar
1 vote
3 answers
204 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 ...
Fajela Tajkiya's user avatar
1 vote
2 answers
530 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 ...
AlwaysLearning's user avatar
0 votes
1 answer
83 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 ...
adam.g's user avatar
  • 355
0 votes
1 answer
31 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' ...
Fajela Tajkiya's user avatar
0 votes
1 answer
61 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 (...
Vocoder's user avatar
  • 117
0 votes
0 answers
77 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 ...
OKreboots's user avatar
0 votes
0 answers
64 views

SQLServer Physical Log shrinking automatically with AutoShrink off

Running in SQLServer 2016 I have a weird situation where the Physical log is shrinking without any interaction and with AutoShrink=false set. Also, on occasion the size is shrinking below the initial ...
DbaJag's user avatar
  • 1
0 votes
1 answer
319 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....
Fajela Tajkiya's user avatar
0 votes
0 answers
272 views

Transaction log full due to CHECKPOINT

I see that posts already exists for this type of issue, but they are not 100% identical to the one I have here. The solutions proposed also don't work. (I have tried a lot...) I'm trying to restore ...
Scopperloit's user avatar
0 votes
1 answer
72 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 ...
Jubs666's user avatar
0 votes
1 answer
97 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 ...
pavk's user avatar
  • 1
0 votes
1 answer
225 views

How is the current sequence number in the control file used during recovery?

During recovery, how is the current sequence number used to determine the redo log files used to re-construct the database as well as the order in which to apply the changes recorded in them?
Mehdi Charife's user avatar
0 votes
2 answers
709 views

What's inside a redo record in Oracle's Redo log?

While reading the Redo log wiki page, I was confronted with the following statement: For example, if a user UPDATEs a salary-value in a table containing employee-related data, the DBMS generates a ...
Mehdi Charife's user avatar
1 vote
1 answer
108 views

What is the log backup behaviour after AG failover or other node unreachable?

Log backups are important for point in time recovery and to automatically truncate the log file (in full recovery model) to make space for log reuse. But when the secondary is unreachable either due ...
variable's user avatar
  • 3,468
2 votes
1 answer
258 views

Why does SQL Server use two different data types for LSN?

I have noticed in the SQL Server documentation that Log Sequence Numbers (LSNs) are sometimes described as numeric(25,0) and other times as binary(10). Why are there two different data types used for ...
Fajela Tajkiya's user avatar
0 votes
1 answer
162 views

Can not Truncate the database because “secondaries has no log was added”

I am a newbie to SQL Server. I got an error when trying to truncate the database: Database can not shrink until all secondaries have moved past the point where the log was added. When I check the ...
Anh Nguyen's user avatar
0 votes
1 answer
786 views

How to prevent log_reuse_wait_desc REPLICATION stage and log growth

Not a unique problem here. I'm more so asking for advice on how to prevent this issue. We're using a nightly snapshot replication job to offload data to our data warehouse. This job has been setup and ...
jdids's user avatar
  • 309
4 votes
2 answers
18k views

How can I reduce the size of a huge LDF file? [duplicate]

We are a small trading company. We use a software for invoicing and stock keeping which uses SQL Server as a database. Our SQL Server database is 3.3 gb in size. However our LDF file is 173 gb. Both ...
Subliminal Hash's user avatar
0 votes
1 answer
79 views

Transaction Log backup chains and one off full backups [duplicate]

I am trying to create a backup plan consisting of full, differential and log backups however i know one off full backups are taken whenever fixes are done to the database. Dors anyone know whether ...
user avatar
-1 votes
1 answer
41 views

Different speed on VLFs in SQL Server?

As the topic says, can VLFs in the same logfile have different speeds? Yesterday when I updated a table with 12M records, the update speed was about 5x when VLFa was active vs VLFb, is this normal and ...
Marson's user avatar
  • 11
1 vote
1 answer
369 views

How to manage the log file size on secondary replica (log backups)?

I am learning about SQL AG. Currently we have 1 SQL server and we run weekly full and hourly log backups. The log backups truncate the log and prevent from filling up the disk. When I configure an AG ...
variable's user avatar
  • 3,468
7 votes
2 answers
1k views

Does Instant File Initialization work for manual log file growths?

SQL Server 2022 introduced Instant File Initialization for transaction log file growth events. In the What's New in 2022 page, Microsoft notes: In general, transaction log files cannot benefit from ...
Brent Ozar's user avatar
2 votes
2 answers
55 views

For a query that changes data, does the change get written to tlog prior to sending it to secondary (AG) and primary data file on disk?

So there are 6 things involved when a transaction is made that writes data: Tlog in memory Data page in memory Tlog on disk Data page on disk Send the tlog to secondary AG Inform the client that ...
variable's user avatar
  • 3,468
0 votes
1 answer
687 views

BACKUP LOG cannot be performed because there is no current database backup. Define Current?

I know what the message is telling me; but I am not sure on the definition of "Current" is, I have a Database backup that was performed at 3am on the previous day, that backup resides on a ...
StixO's user avatar
  • 177

1
2 3 4 5
17