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
1
vote
1answer
29 views

Can I restore my SQL Server database to a point before the last full backup with my current backup strategy?

If I have this backup strategy(weekly full backup and 1 hour log backup), can I restore the database to the green highlighted period of time? Btw, does log backup 2 includes all log records that have ...
1
vote
1answer
24 views

Reduce transaction log file utilisation

I have a SQL Server 2017 database that currently has an 80GB data file and a 90GB log file. I want to get the log file down to a more manageable size, because I am about to run some big scripts which ...
2
votes
3answers
105 views

Urgent help with TempDB log file growth mystery

I need some urgent guidance for a tempdb issue for past last week: The tempDB log file is growing starting a specific time, lets say 2 PM. And almost continue to grow upto 150 GB and then comes down ...
2
votes
2answers
109 views

Fast recovery vs Accelerated database recovery in SQL server 2019

while comparing the features available in different SQL server editions, i see that Fast recovery and Accelerated Database recovery are 2 separate features. I am unable to find any documentation ...
1
vote
1answer
47 views

SQL Server: Should the same log records be backed up multiple times?

I came to SQL Server database administration from an Oracle DBA background. So maybe naturally, I always try to understand the differences between the two DBMSs as well as the similarities. One ...
2
votes
1answer
37 views

Exponential log file growth on quiet database with log backups

I have a small (~3GB) database in Full recovery mode, with full backups at midnight and transaction log backups at noon. Despite being a testing database with virtually no activity, the size of the ...
6
votes
3answers
355 views

Recommended SQL Server transaction logs settings for ephemeral databases?

I'm looking for recommendations on how should I set up transaction logs for databases that are used by a development team. These databases are ephemeral in the sense that we never care about data ...
0
votes
2answers
50 views

What could be the reason for shrinking the transaction log file before and after the database backup?

I'm an accidental DBA. I'm the only person in a company who has some experience with SQL Server and I have to deal with a system inherited from another company after it was acquired. People who ...
0
votes
1answer
45 views

Delayed durability for reporting database

I have a small database which basically run a couple of reporting SPs. Data gets extracted from an OLTP database and refreshed everyday and most tables in this database get truncated everyday. The ...
-1
votes
2answers
58 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 ...
0
votes
1answer
52 views

Log files in Always on Disaster Recovery Situation

In my Always on Avaliability group setup there are four nodes in the cluster in two datacenters, an avaliability group has for replicas, one on each node. Two replicas in the same datacenter have sync ...
0
votes
2answers
51 views

Transaction log space used in percent - how to clear or empty

I am trying to learn more about the transaction log, which I think is the bottleneck of many Stored Procedures (used for reporting) which do data modifications. So I tried below: select * from sys....
6
votes
1answer
175 views

Do Log Backups depend on Differential Backups?

I have the following backup strategy set up: My understanding so far, was: Differential backups do not truncate the transaction log, so, there's no relation between Diff and Log Backups and both ...
1
vote
1answer
63 views

Why does the log file grow larger than data file for an availability group database on the secondary replica?

I've got a SQL Server 2016 AG set up with only two replicas (primary and secondary). Both synchronous, non-readable secondary, and backup the primary only. Transaction Log backups occur on the primary ...
0
votes
1answer
39 views

When should you shrink your transaction log?

We do log backups every 3 minutes but despite that the transaction log has still managed to grow to 200+ gb on disk. Its almost completely empty after log backup. How do you know if your transaction ...
0
votes
4answers
139 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 ...
2
votes
1answer
223 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....
0
votes
1answer
59 views

How to predict database size?

One of requirments for database which I am building is to predict database size to prepare hardware for production environment. Aapplication has 2 main tables wchich are partitioned. Tables are in 8 ...
0
votes
1answer
25 views

Tools for looking at transactions inside of transaction log backup file? [closed]

Are there any tools (commercial or open source) for looking at the contents of a transaction log backup file? Occasionally, get a large transaction log backup file and would like to see the contents /...
0
votes
1answer
29 views

Why first_lsn of full backup and 1st transaction log backup are same?

shouldn't first_lsn of the 1st transaction log backup be 36000000022400001 instead of 36000000022100001? In this case it equals to first_lsn of full backup.
2
votes
1answer
33 views

TransactionLog not getting truncated in AlwaysOn Cluster

Folks, stuck with a wierd transaction log truncation(reuse) problem in an AlwaysOn cluster and running out of options, the transaction log is not getting truncated and keeps growing. Only one database ...
0
votes
1answer
43 views

Log File Size and dbcc sqlperf (logspace)

Consider the following log file setting for a database named XYZ: Initial size = 250MB, Autogrowth by 500MB, Limited to 1GB I have created a SQL Job to monitor the size of XYZ database ldf using the ...
3
votes
2answers
125 views

Extremely bloated transaction log during delete

Now, I know that the log can get large during big deletes and one should try to chop it into batches. But I find this scenario strange and if someone could explain it to me that would be GREAT!. I ...
0
votes
1answer
45 views

How to get transaction command text from SQL server Log file

I got a list of the transaction from fn_dblog and would like to get the command text of the specific transaction by [Transaction Id]. Would you please guide me about that
0
votes
1answer
48 views

Cannot restore data using Postgres WAL files

I'm a newbie at PostgreSQL. I'm trying to setup a streaming replication (postgresql 9.1). And I want to test backup & restore on local. I also made backup files successfully (I think so) by using ...
0
votes
2answers
152 views

Oracle 12cR1 - RMAN-11003, ORA-00354, ORA-00353 - Corrupt Redo Log

Oracle 12cR1, disaster database. ORA-00283: recovery session canceled due to errors ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 8 change 80357235718 time 11/01/2020 ...
0
votes
1answer
40 views

SQL Server second transaction log seems to be used as well - not only after first one is full

we have come across a strange phenomenon using a second transaction log file for a database. Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) Standard Edition. On Windows Server 2016, a single instance,...
0
votes
2answers
136 views

Transaction log backup after full backup is too big

Our current production database performs a full backup daily at 7AM. The transaction log backup takes place every 15 minutes (24/7). Database version: Microsoft SQL Server 2014 - 12.0.4100.1 (X64) ...
0
votes
1answer
109 views

log_reuse_wait_desc - AVAILABILITY_REPLICA Unable to truncate transaction log

I have just 1 secondary replica and just one database in my availability group. Both Primary and Secondary replicas are in synchronous-commit availability mode. I inserted 1000 records into the table ...
1
vote
0answers
16 views

Transaction log file - Migration SSMA

I'm attempting to migrate a relatively large Oracle DB to MSSQL using SSMA however due to disk space constraints, I had to stop the full migration last night when it was 50% of the way through. Upon ...
1
vote
1answer
127 views

Transaction Logs: How much log data generated (hourly or daily)?

I am trying to find out the amount of transaction-log data generated by SQL Server 2016 during every hour or every day. By "data generated" I mean how much data (in bytes/KBs/etc) was ...
0
votes
2answers
136 views

SQL Server native log backup running every 5 minutes

What will happen if SQL Server native log backup is running ever 5 minutes and due to high active transaction the backup was not able to complete in 5 minutes ? Will the next log backup be initiated ...
0
votes
1answer
27 views

Which are the options I have to create a structured log of all INSERT, UPDATE and DELETE from all tables of my PostgreSQL database?

With MySql, I'm using replication api to record all changes from my database. pymysqlreplication and https://github.com/nevill/zongji are what I'm using. I wonder which are the options I have to do ...
0
votes
1answer
31 views

alter table alter column causes huge log and data file growth

I have a table with 2.6B rows in it and I need to change a column from Char(1) to Varchar(64). the table is over 300GB with a few indexes. The column in nullable, so the transaction i'm running is: ...
0
votes
1answer
23 views

Transaction backup plan and size [duplicate]

I am using SQL Server 2016. I have setup SQL Agent for Full backup (Weekly) and Transaction Log backup (Daily). Backup transaction log into a single file using: BACKUP LOG [XXX] TO DISK = N'E:\SQLDB\...
2
votes
0answers
46 views

estimate Log growth in SQL Server [duplicate]

I would like to estimate the log growth consumption for a database with full recovery model for a particular transaction/group of transactions which is filling up my log drive. I would like to ...
1
vote
1answer
28 views

Can a transaction log backup create a new database before any full database backup is applied? SQL Server 2017

We have a large database we restore daily from a single t-log backup from a remote supplier. We are looking to move to 15 minute t-logs, but applying these to the current database on prem it is then ...
1
vote
2answers
85 views

Provisioning SQL Server 2019

I'm provisioning a SQL Server 2019 build and have been allocated a virtual server running Windows Server 2016. It has 4 virtual processors and I have 410Gb of drive space to work with. My build has to ...
1
vote
1answer
26 views

Why did full backup file size grow after data movement for Availability Group replica was suspended?

Found an error log informing that data movement to a secondary availability replica was suspended. I'm not sure whether the cause of the suspension is relevant to answering my question, but the cause ...
20
votes
2answers
3k views

Truncate a table with 17 billion rows in an AG

I need to truncate a table with 17 billion rows, the table is in a database that is part of an AG. What will be the effect of this operation on the AG latency and the size of log backups? Is there a ...
0
votes
2answers
253 views

Oracle 12c - Logically There Is A Redo Group But Not Physically - Database Can't Open

I have an Oracle 12c Database for my enterprise manager. I could not connect to enterprise manager because the database was closed. I opened listener. However, the database remained mounted mode. When ...
0
votes
1answer
173 views

Moving data from staging table to production table uses up to 200GB of Log space

I have multiple staging tables containing data that needs to be moved to production tables. The process of moving this data is done within a transaction to make it possible to rollback everything in ...
0
votes
2answers
123 views

Maximum size transaction log and impact

I was doing some research on what the recommended maximum size for the transaction log, but I cannot find any substantiate or documented answer. I learned that a log size of about 25 - 50 percent of ...
1
vote
2answers
57 views

Is it possible to restore a database with database backups starting on top of a restored server point in time snapshot that is out of date?

If I regularly backup my whole server where a SQL instance and database lives, and restore it to a different machine, can I use the database backups that have occurred (on the original server) after ...
0
votes
1answer
52 views

How to troubleshoot large transaction log file size?

I'm following up on this Why is my SQL Log File Huge? article. Looking at the disk usage report, the transaction log space use is practically zero. It shows 99.1% unused. It is schedule to backup ...
1
vote
0answers
47 views

SQL Server Replication How to keep primary key schema at publisher

I have implemented Transactional replication and want to have similar schema structure at both subscriber and publisher end. But while creating the publication, Not For Replication appears in front of ...
0
votes
0answers
38 views

Review of scheduled bulk deletion on a production database

I have a EU privacy requirement to delete production data regularly. The script runs weekly when the traffic is quiet. I wondered if there is any way to improve this script. I can't delete directly ...
0
votes
1answer
89 views

Is accelerated database recovery available in SQL Server 2019?

SQL Server 2019 CTP introduced a new feature – accelerated database recovery. But then something strange started to happen: If we look at doc page about this feature, we would see no mention of SQL ...
0
votes
2answers
69 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 ...
0
votes
1answer
92 views

Transaction log still full after backup

A database in an availibility group on MS SQL 2014 with a decent log file which at some occasions has been shrinked because of some major jobs filling it up. Usually don't shrink logfiles but as there'...

1
2 3 4 5
15