Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
0answers
36 views

Open transaction preventing log reuse?

I have a SQL servers in an availability group, set as synchronous commit. Due to this setup, log backups only reclaim log space when a backup of the secondary has been processed. I noticed my log ...
0
votes
1answer
27 views

SQL Server Transaction Rollback: does log shrink or grow?

When cancelling a transaction, be it e. g. via failure or manually when it runs too long, what is the effect on the transaction log and its size? Do all the entries generated during and related to ...
0
votes
0answers
28 views

LDF file size is very huge even after doing Transaction log backup [duplicate]

I work on a SQL Server 2016 Database and in our production environment we have primary SQL server and a fail over SQL server. All the transactions are occurring on the primary SQL server. In primary , ...
0
votes
0answers
34 views

how to recover database if redo logs are deleted and I have no archives

I'm working on oracle 11g database, I accidentally removed all redo logs and it happens that I don't have archive logs, I've tried to recover the database using the steps in this link https://asktom....
1
vote
1answer
36 views

SQL Server 2012 R2 Transaction Log needed to create database mirror?

There's a somewhat lengthy discussion of Transaction Logs in Microsoft's instructions for setting up a database mirror. We've never bothered with Transaction Logs in the past; do we really need to ...
0
votes
2answers
45 views

Log flushes per second appears low

I've newly taken over administration of a system with about 25 databases. Using Redgate Monitor to chart some metrics, I see that each day, one database shows values for log flushes / second that look ...
0
votes
1answer
189 views

Delayed durability - can data loss be prevented in case of crash

This book suggests that "Data loss is possible with delayed durability because you might lose committed transactions. If SQL Server crashes after a transaction commits, but before the 60KB buffer is ...
0
votes
2answers
46 views

Determining why a transaction log file is so large [duplicate]

I have a SQL Server 2016 Enterprise database server that I have recently inherited. One instance on this server has an application that uses two different databases. One of these is the actual ...
0
votes
2answers
52 views

Transaction log backup - Only full recovery model?

I am reading this book from Microsoft - it says: "A log backup cannot be performed unless the database is in full recovery model and an initial full backup has been performed" Is this correct? I know ...
0
votes
2answers
40 views

In Full recovery mode, when does the buffer/transaction log changes get applied to the database data pages?

When a change is to be made, the data page is fetched from the database data file into the buffer and changes are made to the buffer page and are also written to the transaction log. Write ahead ...
1
vote
0answers
53 views

Log Shipping with minimum synchronization gap

We will have an application that will go live soon. I will be using Log Shipping for Disaster Recovery but I am confused about the Job scheduling. The transaction logs will be taken every 10 minutes ...
1
vote
2answers
210 views

After a full backup, how does system know which transactions to restore from the transaction log backup?

Full backup does not truncate the transaction log file. Assuming the Following Scenario Full backup at 6AM TLog backup at 10AM Full backup at 1PM TLog backup at 6PM Crash immediately at the next ...
0
votes
1answer
23 views

Help to understand 'log space used' metric

The chart below comes from a monitoring tool. The metric shown is "log space used" for SQL Server 2016 for the last 7 days. I'm looking for help to understand what's going on here. What I do know is ...
2
votes
3answers
129 views

Transaction log filling the drive if mirroring fails

I am having two SQL Server 2016 machines with mirroring set up. This is supposed to be hot-standby scheme. HDD size is 1TB, and database size is around 600GB (just one DB). This is 90 days worth of ...
1
vote
0answers
14 views

How I can truncate and shrink the Logs file in mirroring (Full Recovery model)?

I am trying to truncate and shrink the database. I have taken the backup of transaction logs and now trying to shrink but it's executing with No effect. I have used dbcc shrinkfile(). Please help
1
vote
0answers
24 views

Spin up an extra sql azure instance to reorganize/rebuild indexes with no downtime

We have a pretty large sql azure database, over 300GB. Many tables have been altered over time and we would like to create fresh indexes, including clustered ones. Of course we would like to minimize ...
2
votes
3answers
107 views

SQL Server Transaction Log VLFs not being released (Status 2) on log shipped Database

Have seen a couple of posts similar to my scenario, but nothing that actually resolves my issue. Scenario: Routine index rebuild happens, once a week, 2 AM Saturday morning. Log file grows about 15x ...
0
votes
1answer
144 views

Backup Plan on Always On using Maintenance Wizard doing Tlogs on Primary and Secondary

I have my set my back up preference set to primary and the secondary is not readable. Then I did the following: Setup the same maintenance plan on all replicas in the AG through the maintenance ...
2
votes
1answer
133 views

SQL Server 2017 log file growth with memory optimized table

I'm start to using the memory optimized table, the problem that I have is the log file growth also with the database put in simple mode. On my maintenance schedule I checkpoint and shrink, but still ...
0
votes
0answers
144 views

An Internal Routine Has Requested A Dump Of Selected Redo

The following error is in the alert log around the time (and multiple times) a size of audit files metric alert went off: An internal routine has requested a dump of selected redo. This usually ...
0
votes
1answer
40 views

Saving all sql server transactions to txt for a transactional replication to another database [closed]

I have sql server 2014 running my main database at location A. I want to know if it would be possible to export all insert,update and delete queries to txt files for me to then run on a off site ...
1
vote
1answer
125 views

Remote harden of transaction 'CREATE INDEX' - when moving an index to a different filegroup

Remote harden of transaction 'CREATE INDEX' (ID 0x0000000082a3e3fc 0000:8d8f7911) started at Aug 14 2018 6:14PM in database 'cola' at LSN (2599205:10500:111) failed. The statement has been ...
0
votes
0answers
42 views

Transaction log shipping

I have transaction log shipping set up from my primary server to my secondary server. This morning, the LSRestore job failed due to insuffient drive space. The logs were still available on the ...
0
votes
0answers
26 views

Transaction log files not found in a path

I am using Ola Hallengren backup maintenance job for Log backups and I am on SQL Server 2008 R2. Ola Hallengren log backup maintenance script is utilized to take all three types of backups but ...
2
votes
2answers
90 views

Estimate transaction log size for single transaction

Today I have tried to execute an update operation on 50M+ rows, something like: UPDATE [table] SET [Column] += 1; after 30min of waiting I have seen that the log has started to filling itself, after ...
0
votes
1answer
28 views

Transaction log for standalone DB vs AlwaysON availability Group

I have this question on the generation of transaction logs. For a high transaction active DB what is the ratio of log generation if it is a standalone DB and when it is part of SQL Alwayson ...
1
vote
1answer
73 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 ...
0
votes
1answer
33 views

Size of Log Backup is not reducing from past three days

Recently we implemented Incremental(Enabled allow incremental dumps) and Log Backup(Disabled trunc Log on chkpt) for Sybase ASE(15.7 SP139) running on HP-UX Itanium server and to our great surprise, ...
0
votes
1answer
54 views

LSN mentioned in Backup is missing in Backupset

I was testing some restore scenario for one of my requirement. SO while i was restoring i got the below error. --Msg 4305, Level 16, State 1, Line 79 --The log in this backup set begins at LSN ...
-1
votes
2answers
69 views

RESTORE hourly transaction log to every 10 min interval

I have an hourly transaction log backup running for my production database. I have to implement the below scenario to check for a deleted data. I have two tlogs. tlog1.trn at 10 AM tlog2.trn at 11 ...
0
votes
3answers
60 views

Restore deleted data from a Table which occurred daily

I am not sure whether this is restore data scenario is possible. Scenario: I have a table with few 100 data inserts every day.I didn't realize that because of some reason(identified later as a bug in ...
0
votes
1answer
134 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 ...
0
votes
1answer
150 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
0
votes
0answers
33 views

How much size of log file may create if we alter one column of table having size of 136 GB?

We have one table in production environment having 870 million records.The size of table is around 136 GB.We are altering (i.e increasing its precision) one decimal column in the same table.We have ...
1
vote
1answer
58 views

Log size is different between primary and secondary node with log_since_last_log_backup_mb

We get log backups from the secondary node. But now I see that log_since_last_log_backup_mb in sys.dm_db_log_stats returns null for databases which are in Availability groups, so Ola Hallengren's log ...
1
vote
2answers
100 views

How to access user information via sys.fn_dump_dblog

I am reviewing sys.fn_dump_dblog and I can see the old states of the records, but I can not see which users are doing the operations. SPID, Transaction SID columns are always null, how to fix it, or ...
2
votes
2answers
39 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
0answers
30 views

Why do my SQL Server 2016 transaction logs keep growing? [duplicate]

I'm trying transaction replication for the first time on SQL Server 2016 I have the distribution database on C:\ (I know, but I'm waiting on a new drive), and my bhdb database on D:\. My problem is ...
1
vote
0answers
50 views

Log Shipping Job Failed

I have a transaction log shipping configured. Job activity monitor shows that both Copy and Restore steps have failed, however, I see that backup files are in fact copied across to the secondary ...
2
votes
1answer
52 views

Overlap in LSN's backed up

My current backup strategy for our AlwaysOn clusters is a daily full backup and hourly transaction log backups via a third party backup program. We had a request to minimize the amount of log backups ...
2
votes
1answer
286 views

How can I track down what's filling my 'tempdb' transaction log?

We have a large and fairly well tuned SQL Server 2016 Enterprise Edition. Given the number of cores we have, our tempdb is currently comprised of 16 2,950 MB files running on a ram disk with a 16 GB ...
0
votes
1answer
122 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.
1
vote
1answer
211 views

Should I have both Transaction Log Backups and Log Shipping

I set my database to Full Recovery a few months ago (to allow point-in-time restores) and set up Transaction Log backups with Maintenance Plan. Now we want to conduct Log Shipping with the same ...
0
votes
1answer
46 views

Does large VARCHAR take up more room in the transaction log

On SQL Server, would VARCHAR(500) take up more space in the transaction log than VARCHAR(100) when all the rows have no more than 100 characters?
1
vote
1answer
69 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
votes
2answers
440 views

Why am I not getting minimal logging when inserting to indexed tables

I'm testing minimal logging inserts in different scenarios and from what I've read INSERT INTO SELECT into a heap with a non clustered index using TABLOCK and SQL Server 2016+ should minimally log, ...
0
votes
2answers
33 views

Storing logical WAL in the same database

I have a simple Postgres setup (one machine, one disk) and would like to record a changelog for some of the tables. As I would like to access that changelog from the same web app that uses the ...
1
vote
1answer
507 views

Why does a log file grow even when transaction is not commited?

Below is what I did: Created database in simple recovery mode. Created table. Began tran and inserted so many rows to exceed size of the log file. Committed the transaction. Before step 4 I noticed ...
1
vote
0answers
57 views

Can I run an index rebuild on a sql server HA replica or a copy the DB, and then swap with the primary?

We have a large database hosted in sql azure and we need to occasionally run maintenance tasks such as index rebuild. Pretty common of course, and we also try to minimize downtime. We use the premium ...
2
votes
1answer
33 views

Cost of replaying transaction log on sql server HA replica

I've been reading about the sql azure scale out. I suppose it makes senses to leverage the replicas hardware for read-only workloads. But it got me wondering, which is actually doing more work? The ...