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
1answer
27 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
65 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
45 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
votes
0answers
30 views

Can Transaction Logs be deleted by a DBA to hide what happened from managers? [closed]

I am one of your fans and really appreciate your posting those informative articles that benefit all of us. I am a DW and BI Developer and have a question: can a DBA with bad intention delete specific ...
1
vote
1answer
36 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
50 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
61 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
760 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
29 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
56 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
96 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 ...
1
vote
1answer
33 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
82 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
109 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
62 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
37 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
36 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
78 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
42 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
889 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
66 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
52 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
51 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
41 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
60 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
47 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
57 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
40 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
56 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
166 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
64 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
506 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
98 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 ...
0
votes
1answer
33 views

SQL Server: how to monitor activity in several databases

I have several dbs in production and I'd like to have a high level overview of the activity. Ideally I'd like to know: number of users accessing the db per day number of inserts per table per day ...
1
vote
1answer
94 views

How do I shrink the MASTER log file in SQL Server 2008?

This is for a non-system database: USE AdventureWorks GO ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE GO DBCC SHRINKFILE (AdventureWorks_Log) GO ALTER DATABASE AdventureWorks SET RECOVERY FULL ...
2
votes
2answers
89 views

SQL Server mistaken update rows without backup

I did a mistake UPDATE query yesterday. Database engine is MSSQL and the version is 2017. I have not any backups. All I have is the database itself. Do you know any ways to redo my modifications to ...
2
votes
0answers
39 views

What do I need to check why log_reuse_wait_desc keeps coming back to REPLICATION

What should I do to check or need to do why log_reuse_wait_desc is REPLICATION? A database of ours keeps coming back to REPLICATION for the log_reuse_wait_desc after essentially following this ...
0
votes
0answers
54 views

Azure Managed Instance Log file Larger than Data File

Good Afternoon, I am working with the Brent Ozar tools on an Azure SQL Server Managed Instance. Once of the things that came up when I ran the sp_Blitz procedure was that I had several databases that ...
1
vote
2answers
59 views

Log Sql Server Transactions (Queries + username)

I need to see which user has made what query with a lookback of ~90(?) days. I have activated change tracking on the database with 90 days. I have found several solutions: SELECT deqs.*, ...
0
votes
1answer
57 views

How safe is to use BACKUP LOG WITH TRUNCATE_ONLY

As per the title, how much is it safe to use BACKUP LOG db_name WITH TRUNCATE_ONLY in last STEP of any SQL-job execution? Randomly found on google that this Code line can be very harmful in use. But ...
0
votes
0answers
15 views

InnoDB: does `redo log` contain initial value and final value? or just final value?

start transaction; update users set age = 34 where user_id = 3 // update age from 30 to 34 years old. .... .... when I am updating the age of this user, InnDB will write redo log. What's the ...
0
votes
0answers
60 views

MySQL: why undo log is required in the processes of database recovery?

Let's imagine that an incident happens when you updates. start transaction; update users set gender = 1 where id = 3; Undo log space is allocated. Previous values are copied to undo log Record of ...
3
votes
1answer
122 views

Attach ldf more current than mdf

Is it possible to create a database where the ldf is more current then the mdf? Or to attach the older (more mature) ldf to the younger mdf (less mature)? I am working on Steve Stedman's Week 3 ...
0
votes
0answers
133 views

What makes ALTER TABLE X SET UNLOGGED; to be slow?

We develop and maintain a JEE Application with Postgres (multiple version of it as we've got different customers using different versions). The Code evolves over time; database changes are organized ...
0
votes
0answers
22 views

Log file is growing up and is not releasing space [duplicate]

The database is under Full Recovery Method, the DBCC SQLPERF (LOGSPACE) shows 3 % used. Even after I take Log back ups frequently it is not releasing space. There is no active transaction and ...