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
2answers
52 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
48 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
39 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
37 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
34 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
45 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
43 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
0answers
30 views

Question about SQL Server log records in a log backup

Let's say I created a new database and then taken a full backup and a log backup in sequence. See below image. When the full backup completed, does it include the log records generated after the ...
0
votes
1answer
35 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'...
5
votes
1answer
85 views

Why is the transaction log not truly circular?

I did a little bit of hands-on research recently about how the transaction log in sql server works. I came to a problem that I can't get my head around. First I created a new test database with full ...
0
votes
1answer
39 views

Does DROPing a table create a bunch of transactions in the transaction log for all the records being purged?

If I have a table with billions of records, is it faster and does it create less data in the transaction log to TRUNCATE it first and then DROP it or can I just DROP it all the same?
1
vote
1answer
54 views

Replication fails with “Transaction log for database is full due to LOG_BACKUP”

Replication is failing with the subject error. The log_reuse_wait_desc states LOG_BACKUP. I have performed both database and log backups. After changing the recovery mode to single, performing ...
0
votes
2answers
87 views

How to debug a growing Redo Queue Size in the Availability Group Dashboard?

The Redo Queue Size has been growing pretty big over the last couple of hours on my secondary replica's server (SQL16). There's nothing long running or heavy running that I've found over the last hour ...
0
votes
2answers
71 views

Sql Server 2017 - Cannot shrink transaction log file, blocked by LOG_BACKUP status

Last week my sql server 2017 instance (running on windows server 2016) ran out of space, filling the disk completely up with a 190GB transaction log file. Luckily this was on a cloud instance, so I ...
0
votes
2answers
68 views

Transaction Log Backup Affecting Applications Performance

We have a database around terabytes of data. We are taking FULL backup every week, differential backup daily and transaction log backup after every hour. Sometimes this transaction log backup takes ...
0
votes
1answer
28 views

Massive Transaction Logs - what are my options?

I have a fairly crazy scenario where I am processing ~250 million inserts and 250 million deletes for a table that contains data that is only relevant for the moments that it is in the database. Once ...
0
votes
2answers
55 views

What things SQL Server takes backup of when we run a full backup

Like I am trying to ask that, suppose we run a full backup using GUI or T-SQL, which file SQL Server takes backup of i.e, only data file or log file as well. I have read that it takes active ...
1
vote
1answer
59 views

What is the use of the transaction log buffer in SQL Server?

I have a question like why do we use in transaction log buffer. Like SQL Server puts the transaction log in the Transaction log buffer, when the checkpoint runs it transfers the dirty pages(committed)...
2
votes
1answer
53 views

Is there a way to get a history of transaction log used space in SQL Server?

I would like to be able to look back and see what the used space was in transaction log over a given period. Not interested in growths or file size as they don't tell me how much space is actually ...
0
votes
1answer
47 views

In SQL server during Log Shippping which portion (active\Inactive) of the Log file (.ldf) backup is taken and further restored in SQL Server [closed]

I am confused, like if we take the backup of the whole log file, wont it clear the transaction log though we have copy only backup. In log file we have 2 parts, like active and inactive logs, in ...
1
vote
1answer
26 views

Why MSDB transaction log file grew while MSDB was in Simple recovery mode?

I have Availability Group of 3 servers, and transaction log backups for databases in Availability Group are taken on a secondary replica, each 1 minute It was working like this for several months ...
0
votes
2answers
43 views

Very large Transaction Log File - When is it normal?

I have a server that is running extremely low on disk space and I dug into to determine if something is not functioning properly or if it simply is a healthy database that just happens to be growing. ...
0
votes
1answer
56 views

Why T-log is not truncated after T-log backup?

I have a database myDB in full recovery model. I took a full backup and restored it on the test server. Afterwards, I checked the size of T-log. The size of t-log was 15600 MB and used space out of ...
2
votes
1answer
69 views

Shrink log with TRUNCATEONLY but there's nothing to truncate?

An external company has a job set up which shrinks the log with truncate only - I checked the default trace and for example last night DBCC SHRINKLOG WITH TRUNCATEONLY happened 300 times. If I also ...
2
votes
1answer
62 views

Log Autogrowths bigger than log size, but no shrinks to explain how

I have a database with a logfile size of 1GB. I implemented some logging of AutoGrowths to eventually setup some notifications for when a database autogrows. What I'm experiencing though, is that ...
5
votes
1answer
1k views

SQL Server - Files timestamps unchanged so what is backed up?

Please see the below snapshot, showing the timestamps of all the data files as well as transaction log files for one of the SQL Server instances under my management. This is a production instance. ...
0
votes
2answers
33 views

On a typical COW filesystem, is the database WAL redundant (in theory)?

EDIT: I was incorrectly assuming that all COW filesystems have a WAL. IT seems that BTRFS doesn't, so this question essentially applies only to ZFS. Typical COW filesystems, like ZFS, have their own ...
7
votes
3answers
528 views

SQL Server Truncates Transaction Logs with Copy Only Backups

I have a database that is set to "Full" recovery mode. The database doesn't show that a backup of the transaction log has ever been made. backup_finish_date shows null for my logs. I take backups ...
0
votes
1answer
76 views

How the transaction logs are maintained in MSSQL RDS?

Just curious, I was asked that if RDS takes transactional log / incremental log backup for point in time recovery, does RDS shrink the logs too? In MSSQL instance, we shrink logs if transactional ...
1
vote
2answers
51 views

What realistic issues can occur from shrinking the log files on my database (either through the SSMS UI or issuing a DBCC ShrinkFile command)?

I've heard talk of potential corruption or data loss occurring from shrinking the database data or log files. I'm curious in particular about only shrinking just the log files, what are some real ...
1
vote
2answers
193 views

What's the exact meaning of `innodb_max_dirty_pages_pct`, and how does it relate to the redo log?

I'm puzzled by the innodb_max_dirty_pages_pct parameter, and its relation to the redo log. The definition itself is simple: InnoDB tries to flush data from the buffer pool so that the percentage ...
0
votes
3answers
56 views

how to automate DB restore to another server?

The plan is to have 1 backup set that will contain full and transactional log backup. (Server1) There is already a scheduled job that will append any transactional log to the file after 30 minutes. (...
2
votes
3answers
519 views

Does Postgres automatically rotate WAL files out (pg_xlog) if archive_mode is off?

I'm relatively new to PostgreSQL administration and I'm attempting to pick up the nitty gritty details as I go. My question is, assuming I have no need for disaster recovery and I don't turn on ...
3
votes
1answer
100 views

Decrypt T-SQL log backup header and read LSN

For some reason I need to read the LSN from the T-SQL logs backups without restoring them or even their headers (I assume even restoring only their headers will change the LSN on the database side too,...
12
votes
2answers
633 views

During a log backup is the data backed up to the start or end of the operation?

Say I am running a log backup, and that log backup takes 10 minutes to complete. During that 10 minute window, further transactions are run. Given the below example, which transactions does the log ...
8
votes
2answers
750 views

Why is it not recommended to use RAID 5 for a log file? [duplicate]

While reading SQL Server Query Performance Tuning written by Grant Fritchey, I found it difficult to understand the following part: avoid RAID 5 for t-logs because, for every write request, RAID&...
2
votes
2answers
198 views

Disable redo logs on MariaDB

The title is pretty explicit, I just want to disable completely redo logs on MariaDB 5.5. This is more of a testing database that might eventually be used for a small web forum, but I don't care ...
6
votes
3answers
600 views

SQL log file 2TB is not enough, now what?

This is a hypothetical question, that flows from the answer by sepupic where they explain that 2TB is the physical limit for SQL log file. if you need it more than 2Tb you add the second log file. ...
2
votes
1answer
130 views

what could be preventing my transaction log autogrowth to be set to UNLIMITED?

I have been trying to set the autogrowth of one of my databases to unlimited but despite whatever I do it remains the same. First thing I have checked if something is holding any locks to my database:...
11
votes
4answers
2k views

Why would shrinking TEMPDB log lead to slowness?

I recently accidentally shrunk tempdb log to almost 0 after getting alerted that log drive filling up. I am told that it will lead to slowness. Can someone please explain why it will lead to slowness?
0
votes
1answer
261 views

Azure Backup SQL VM - Not shrinking log file after transaction log backup

I am using Azure Backup to take a transaction log backup from my SQL 2016 Enterprise VM every 15 mins. It also takes one full backup daily. The db is using Full recovery model. Problem is that the ...
0
votes
2answers
62 views

How does pausing a SQL Server mirror affect transaction logs on the principal?

One of our two datacenter's is undergoing maintenance for a minimum of 72 hours, but it could be up to 5 days, and I have a very old legacy SQL Server running a production service as a mirror across ...
0
votes
2answers
778 views

What is the purpose of the NOLOGGING clause on Oracle database TABLES and INDEXES

I was wandering what is the practical purpose of using the NOLOGGING clause on tables and indexes. For what I know from the Oracle documentation, we can prevent the database from generating redo logs ...
0
votes
1answer
512 views

SQL Server with small database size and huge log file size

I have several databases, for some log file size is growing more than normal even though very little data is added to few table around 50-100 rows a month. i.e. Database 12 data file size is 79MB and ...
3
votes
1answer
145 views

log_reuse_Wait_desc always return LOG_BACKUP even right after log backup

We are using SQL Server 2012 standard Edition (SP4)- 11.0.700.0(x64) (Build 14393)(HYpervisor). Data File Size = 171 GB Log File Size = 106 GB When I queried sys.databases to see the ...
1
vote
0answers
368 views

Error 9003 Log Scan is Invalid Error When Restoring Transaction Log After SQL2016 SP2 + CU8 Upgrade

We are using SQL 2016 Enterprise Edition and have recently applied SP2 + CU8. We were previously on SP1 + CU6. Since the update we are getting the following error when restoring transaction logs on ...
0
votes
1answer
100 views

Error 23 on transaction log backup, full backup works fine

I have a database with the database files on the E: drive, and logs files on the D: drive. I'm getting the following error when trying to run a transaction log backup: Read on "D:\LOG\AOA_Log.ldf" ...
1
vote
1answer
87 views

Restoring .trn files on a regular basis to a backed-up database

We have been given a .bak file of a database from our vendor on a completely different network. They are placing their .trn files every 15 minutes on a sftp for us to pickup and write to the .bak file ...
1
vote
1answer
219 views

Log truncation issue in AlwaysOn AG

We have four nodes in our AG. After the weekly index maintenance job, the log drive became full. We enabled CDC in one of our database, so we disabled CDC to truncate the log. Then we ran 'checkpoint' ...
1
vote
2answers
77 views

Using MySQL binary logs to keep backups between database dumps?

I currently take a full database backup using mysqldump once a day. I'd like to make this more granular (e.g. every 15 minutes), but can't just take more full dumps due to size constraints. Can I use ...

1
2 3 4 5
14