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
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

Transaction log is a list/history of modification executed by a database management system to guarantee ACID properties over crashes or hardware failures.

1
vote
0answers
40 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
38 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
17 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
72 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
0answers
32 views

Unable to shrink the Transaction Log File on the Primary Database - Always On Config [on hold]

We have an AlwaysOn Setup where in we have 3 replicas in all. Primary Replica and the one of the Secondary Replicas are in the same datacenter while the second secondary replica is in a different ...
0
votes
1answer
23 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 ...
0
votes
0answers
19 views

How to reset or resume transaction log chain for a database in high availability during restore of database

I ran into this specific error "The log in this backup set begins at LSN X:Y:Z, which is too recent to apply to the database. An earlier log backup that includes LSN A:B:C can be restored." I came ...
1
vote
0answers
47 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
27 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
25 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
65 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
48 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
24 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
114 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
32 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
54 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
79 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
31 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
27 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
50 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
94 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
47 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.
0
votes
1answer
130 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
45 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
45 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
284 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
29 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
503 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 ...
0
votes
0answers
56 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
29 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 ...
2
votes
2answers
372 views

The transaction log for database is full due to 'OLDEST_PAGE'

We have a database hosted on azure. It is part of an azure elastic pool. From yesterday all our database operation are consistently failing with following error. The transaction log for database is ...
0
votes
1answer
42 views

Missing data in specific tables post log backup and shrink

We have two DB servers which are part of Always On. Recently we noticed that log backups haven't been running on them for sometime and their .ldf files were growing. We took a log backup and tried to ...
1
vote
2answers
205 views

Set default space free in sql log

We have a database that needs 40% free in the log files at all times or we get an alert. I cannot find a way to set growth when a limit is reached. Of course we can set autogrowth to a certain limit ...
6
votes
1answer
89 views

Snapshot_isolation_state_desc on a Read only/Stand by Database

I have configured Log Shipping where the primary server database has snapshot_isolation_state_desc reported as off. I tried to turn it off on the secondary which is a standby read only by using the ...
0
votes
1answer
47 views

Log Shipping failover when Primary is not accessible

Log Shipping Failover I have set up log Shipping with Cluster. In this scenario I want to simulate a situation when the whole cluster becomes inaccessible. My Steps for failover will be 1) Copy ...
7
votes
3answers
327 views

Database enters in recovery mode every time the Transaction Log is full

I am facing a situation that it is being somewhat hard to address. I need help to understand what is happening. TL;DR: Every time the Transaction Log gets full in SQL Server it needs to shutdown the ...
1
vote
1answer
97 views

One database in AlwaysOn Group not truncating log file

I have been stuck with this issue for two days now and am not hoping someone here might know the answer. I have multiple databases in one availability group, all of the databases follow the same ...
1
vote
2answers
63 views

Tail log backup query in log shipping reversal

I am doing log shipping failover and failback configuration in SQL Server 2008 R2 environment. During the Failover, I am taking the tail log backup of primary database with query: Script to backup ...
8
votes
3answers
2k views

Does backing up a database shrink the transaction log size?

I am trying to gather some knowledge on SQL databases and I have some questions about the transaction log file (LDF). First off, when you create a database, you have to define an Initial File Size ...
2
votes
1answer
65 views

Disk Architecture on SQL Server [closed]

I am building a new environment using the SQL Server 2017 Always-On system. This will be active 24/7 and there will be lots of DML processes. I have a 100GB SAS for the OS, a 1TB SAS for Data and a ...
16
votes
1answer
245 views

Why is CHECKDB reading the transaction log file on a database with a memory optimized table?

tl;dr: why is CHECKDB reading the transaction log for a user database with memory optimized tables? It appears that CHECKDB is reading the transaction log file of the user database when it's checking ...
5
votes
4answers
964 views

Why Can't I shrink log file in full recovery mode [duplicate]

I have a log file that is 302MB. I have done a log backup which has left the log file mostly free (I can see this through the Disk Usage standard report) If i try to run DBCC SHRINKFILE (N'...
0
votes
1answer
55 views

why is the Log File is 5 times the database overnight when deleting?

The log file alone is close to 500 GB while the database is 80 GB. This is not a production system so I tried to delete a table which had 780,000 records. this table has 5 individual indexes that ...
0
votes
0answers
25 views

Analyzing the log backups to understand the pattern

I am not sure if my understanding is correct on this behavior of log backups. We believe the increase in traffic, as log backups have increased after migration event to cluster from stanadlone SQL ...
0
votes
0answers
30 views

what transactions are currently open and what operations are inside each of them?

quite often I need to do updates and inserts on production systems. before I do an update or insert I backup the affected records, or the records to be inserted. statements are run on a command by ...
0
votes
1answer
41 views

Is conditionally SQL Server log backup possible?

I have a SQL Server database and right now we have weekly full backup, daily differential backup and 5 minutes log backup. Most of the time there are reads and writes happening in the database. ...
1
vote
0answers
117 views

Log Reader Agent Blocking Distributor Agent T-log Stuck with log_reuse_wait_desc Replication

I've got a nasty SQL Server 2012 replication issue. I have a transactional publication going to a pull subscriber. The log reader agent on the publisher has gotten stuck in a loop of: Delivering ...
5
votes
2answers
278 views

Why are virtual log files not always allocated in order?

Quarterly I check the VLF on all of my servers through CMS with a query from the tigertoolbox/Fixing-VLFs/ on github. This includes suggestions (and code) for correcting what is found. I always try ...
2
votes
1answer
75 views

SQL Server Transaction log Percent Used in Perfmon

I am doing some research into monitoring transaction log use using windows perfmon. In the AdventureWorks database I have created the following statement SET IDENTITY_INSERT Person.BusinessEntity ON ...