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
8
votes
4answers
642 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
29 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
32 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
40 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
75 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
37 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
68 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
69 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
35 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 ...
0
votes
1answer
61 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
15 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
vote
1answer
42 views

Why do we add NO_TRUNCATE and NORECOVERY options while taking tail log Backup?

Assume that my mdf file is deleted and I set my database into single user mode. The first thing that I do is take tail log backup. My question is do I need to add NORECOVERY option while taking tail ...
2
votes
2answers
61 views

Does Log File contain both committed and uncommitted transactions?

My question is related to SQL Server architecture. Mainly log file. In the picture below it says that when transaction is committed, log record in LOG CACHE will be moved to Log file. Does it mean ...
0
votes
2answers
61 views

SQL Server - Log file not shrinking in AlwaysOn group

We have three-node AO setup in SQL Server. Primary node, one secondary node in Sync mode and another secondary node in Async mode. Main Database on Primary node, log file-size is 434GB, Used 99.91%. ...
2
votes
2answers
76 views

Log file won't shrink - suggestions?

I have a 225GB DB with a 390GB log file, which has only recently exploded in size. I am trying to shrink it - per these instructions: https://www.brentozar.com/blitz/transaction-log-larger-than-data-...
-2
votes
1answer
49 views

How to fetch transaction details for Logs Table in SQL Server

I want to create a tracking table for changes made to the database. A tracking table called Logs must be created into which all changes (insert and delete) that are made in the database will be ...
1
vote
1answer
82 views

TempDB Transaction log not releasing space

Every time I complete a transaction in tempdb, the temp log grows, however the log doesn't seem to release the space once the transaction has completed.The log usage percent is 43.4% but seems to ...
0
votes
2answers
54 views

Not able to delete transaction log backup file

I have a big SQL Server database (~1.5 TB) and i didn't take log backup of that database for last two weeks for some reason. Today i found that the .ldf file of that database became huge around 1.5 TB....
0
votes
1answer
67 views

How SQL Server transaction works with log records

My textbook says: Starting when you begin a transaction, your changes are isolated from other users. What you’re doing is visible to you only,and isn’t really done until you COMMIT — although it ...
11
votes
1answer
914 views

Is ALTER TABLE … DROP COLUMN really a metadata only operation?

I've found several sources that state ALTER TABLE ... DROP COLUMN is a meta-data only operation. Source How can this be? Does the data during a DROP COLUMN not need to be purged from the underlying ...
0
votes
1answer
45 views

PostgreSQL >= 10 does not generate WAL if there are no transactions

I noticed that PostgreSQL >= 10 does not generate WAL if there are no transactions. In previous versions, PostgreSQL generated WAL at the expiration of archive_timout, because it wrote some empty ...
2
votes
0answers
58 views

When updated records get written to the real physical pages in mdf file?

I'm a beginner in SQL Server, just some questions on transaction. My textbook says: A checkpoint is a periodic operation that forces all dirty pages for the database currently in use to be written to ...
1
vote
1answer
62 views

Trans Log fills during delete, conceptual question

I'm learning everything I can about backups and transaction logs and such. I want a little feedback on if I'm understanding this, so I have a conceptual question. First, here is what I observed: I ...
1
vote
1answer
173 views

Transaction log is full (due to NOTHING)… but this database is in simple recovery mode

I'm supporting an antedeluvian webapp (soon to be retired) that still uses "aspnetdb" for its auth system. I was doing some work in prep for its retirement on my test environment, when I found my ...
1
vote
2answers
66 views

Transacton log backup

What happen with the active transaction log when I stop SQL Server? Under the stop process all transactions will write to the db? Or will put when the db will starting... Is it possible when I stop ...
2
votes
1answer
38 views

SQL Server Replication and Recovery Model

In SQL Server for Replication when recovery model is not very important then how it saves the transactions and also keeps the secondary instance in sync? I tried to check on MSDN and found the ...
1
vote
0answers
122 views

Transaction Log File corruption on Mirror server SQL 2017

Wondering if anyone has encountered database transaction log file corruption when database mirroring is enabled in SQL 2017 running on Windows Server 2019, on the Mirror server? If so, curious what ...
2
votes
1answer
167 views

The transaction log for database 'SolarWindsOrion' is full due to 'XTP_CHECKPOINT'

To start, I am a database novice. I thank you for any help in advance. I have a solarwinds SQL windows 2017 database in full recovery mode in an availability group. The database is roughly 2GB. ...
1
vote
1answer
68 views

SQL Server shrink log after full and log backup

I have an SQL Server database in Full recovery model. The data file is just 1 GB in size, but the log file is 20 GB. I have a space problem, so I took a full backup and then a log backup with ...
0
votes
0answers
13 views

Where is transaction log truncated to if full dump is taken while txn log dump is running?System info: MS SQL 2014 Enterprise

We have a very large database with a very large (over 1.5 TB) log. A transaction log dump was started, and while it is running, a full DB backup was run (successfully). The log dump is still running. ...
1
vote
1answer
38 views

Can the LOG file fill up from index rebuilds where the indexes are not fragmented?

Working on SQL Server 2014. I have noticed that a log file (60GB) on a database has filled up within the past day. To my knowledge, I am the only developer using this database and I have not been ...
1
vote
3answers
44 views

Adding a Far DR secondary server Log shipping on already configured SQL server

We have a setup where there are 3 SQL Server instances in which 1 primary,1 Near Secondary,1 Far Secondary. So the there is already log shipping configured between Primary and Near DR Secondary. Now ...
0
votes
0answers
79 views

Differences Between Setting Up Transaction Log Shipping through Wizard vs Scripts

There appears to be a fairly significant difference after setting up transaction log shipping (in Standby/ Read-Only mode) through the SSMS wizard, which generates jobs that then call on outside .exe ...
1
vote
2answers
44 views

Can anything reveal that 2 independant entries were created or updated at the same time?

Imaginary example, simplified to better explain the question. Let's say I have a form with the following fields : user email who did you vote for in 2012 who did you vote for in 2016 When submitted, ...
8
votes
3answers
923 views

transaction log in RAM or physical file?

I'm a beginner in transaction, just a question on transaction log. We know that when we commit a transaction, the changes are written to the transaction log, but is transaction log in RAM or physical ...
1
vote
1answer
94 views

What will be inside the last differential backup file?

I am new in DBA and while learning different types of backup, one question came into my mind. So the question is, what will be inside the last differential backup, just the sum of the previous ...
1
vote
0answers
54 views

SQL Server Transaction Log Full affect on FileStream [closed]

These are general question I would like to receive some inputs from experts with BLOB SQL Data. How will SQL Server Transaction Log (.ldf file) linked to SQL Server FileStream Is it different from ...
7
votes
2answers
2k views

Why is my log file so massive? 22gb. I am running log backups

I can't seem to figure out the answer. I've seen multiple answers like this: Why Does the Transaction Log Keep Growing or Run Out of Space? and everyone talks about running back ups on your log file ...
2
votes
0answers
66 views

Change Tracking generating enormous transaction logs

I'm troubleshooting a 24GB DB that is generating approx 2GB (compressed) of transaction logs/60min. Using the following command I'm tried to examine the contents of the log: SELECT Operation, ...
0
votes
2answers
42 views

The actual cause of transaction log full under below scenario

in one of the instance, it shows The transaction log for database 'DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases The instance ...
1
vote
1answer
63 views

Unable to trace the transaction log space

im new to sql server. One of the instance encounters a scenario it shows the transaction log full, and the database need to perform backup in order to solve the issue. I want to replicate the ...
0
votes
0answers
57 views

Can MSSQL CDC be used to capture old committed transactions?

I can successfully capture change data from mssql CDC by issuing the query cdc.fn_cdc_get_all_changes_capture_instance ( from_lsn , to_lsn , '<row_filter_option>' ) Here, from_lsn is the ...
0
votes
1answer
70 views

Proper resizing of Log File after shrinking - SQL

Recently I've discovered our 2012 SQL Server database contains over 100,000 Virtual Log files by running the DBCC LOGINFO command. After doing a good deal of research I am still uneasy as to what ...
1
vote
1answer
41 views

SQL Server Backups-Order of FULL & T-log backups

I have an elementary question. I just started as a DBA with no administration experience and upon reviewing the Backup schedule, I noticed on one instance that the maintenance plan that performs the ...
0
votes
0answers
68 views

Always on availability group database not truncating log files and keeps growing

We have 4 node replica, 2 nodes in synchronous mode, and 2 nodes in asynchronous mode. Log backups are happening every 15 minutes. The application just went live yesterday and one thing does not look ...
2
votes
1answer
177 views

Transaction rollback hanging from past two months(MSSQL 2016)

I executed a query using SQLCMD on 2019-01-30, and the query caused ldf file to bloat exponentially and it became 2TB in size, then my MSSQL started rollback of transaction at 2019-02-01. I used KILL ...
0
votes
2answers
82 views

Can sp_Blitz, sp_BlitzCache, or sp_BlitzFirst identify what queries caused a major growth operation to occur on a particular log file?

I'm currently logging the outputs of sp_Blitz, sp_BlitzCache, and sp_BlitzFirst regularly (via SQL jobs). A particular database's log file grew somewhat exponentially and I believe I can see the ...
0
votes
0answers
41 views

oracle 11g redo logs problem

On a dev server I have had over the past few days very frequent log switches (as many as 200 per hour), plus "thread 1 cannot allocate new log" and "Checkpoint not complete" errors every few minutes. ...
5
votes
2answers
510 views

Do I need to leave some extra space available on the disk which my database log files reside, for log backup operations to successfully occur?

If I size my log files to evenly split the entire drive they reside on, leaving no extra space available, will log backups still be able to occur successfully? I have several databases, with one log ...
2
votes
1answer
101 views

Why does my database using the simple recovery model have such a large transaction log?

Not a duplicate with: Transaction Log is big! But simple recovery mode is chosen! Why is my transaction file growing when db is in simple mode and there are no waiting transactions Reason: My log is ...