Stack Exchange Network

Stack Exchange network consists of 175 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
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

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.

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 ...
0
votes
0answers
25 views

Change Tracking seems to be generating enormous transaction logs

Comrades, 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 ...
0
votes
2answers
28 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
54 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
29 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
40 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
35 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
41 views

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

In our environment we have 4 Node Replica 2 nodes in Synchronous Mode and 2 Nodes are in Asynchronous mode. Log back ups are happening every 15 minutes. The application just went live yesterday and ...
2
votes
1answer
134 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
37 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
36 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
488 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
86 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
32 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
42 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
84 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
36 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
37 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
55 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
42 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
25 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
80 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
72 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 ...
1
vote
2answers
48 views

Missing step in backup (DR) plan

Unfortunately, I am assuming like most folks in this role, the title DBA came by default rather than design. I am trying to manage our farm of 5 SQL Server 2016 servers, each having a warm stand-by ...
0
votes
0answers
26 views

Why Update join sql locks a table in Mysql 5.6

I'm encountering transaction error like this. ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2018-12-25 08:45:10 2b128be88700 *** (1) TRANSACTION: TRANSACTION 112497037, ...
1
vote
3answers
50 views

InnoDB: will uncommitted transaction produce redo log?

Before MySQL commit a transaction, it will write REDO log first, then commit the transaction, that is write ahead log. start transaction; update users set uuid = UUID() from user where id = 1 update ...
0
votes
2answers
155 views

Rebuild transaction logs

a colleague deleted accidentally all transaction logs (SQLServer/Logs/*.ldf files) of our SQL Server 2016 instance. Now we cannot start SQL Server service anymore. We tried to use a recovery data tool ...
0
votes
1answer
42 views

Fix deleted transaction log file in SQL Server

I have made the biggest mistake and deleted the transaction log file. Is there a way to fix this issue? I have the backup file, is there a way to restore it or reset everything again?
1
vote
1answer
53 views

SQL Log shipping - Very Slow restore

I have SQL Server 2012 SP4, ten databases and log shipping. One database 2.7TB Ten data files and two log files Log shipping configuration: Transaction log backup job : 3 Min Copy job : 1 Min ...
3
votes
2answers
67 views

How can I justify making changes to my redo logs?

I don't spend a lot of time with my Oracle environment. I'm more of a SQL Server guy. I need to look into the alert logs to check for correlation of an application error. What I found instead was ...
1
vote
2answers
448 views

what is the penalty for NOT setting Disconnect users in the database when restoring backups?

setting up Log shipping between 2 sql server 2016 servers I want my databases read-only standby on the secondary server. there is a tick box saying Disconnect users in the database when restoring ...
0
votes
3answers
208 views

Does MSSQL Log shipping truncate the transaction log?

I have setup log shipping on a database in Microsoft SQL Server 2012. I was having issues with my secondary server and I found that this was probably due to a scheduled backup job messing up the log ...
0
votes
1answer
58 views

is this time to remove the databases from the availability group? which ones?

While patching one of my servers that is part of an availability group, as a secondary, I had some problems - the real problem was that the host machine of one of the virtual servers in the ...
0
votes
1answer
34 views

SQL Server Transaction Rollback: does log shrink or grow?

When cancelling a transaction, be it e. g. via failure or manually when it runs too long, what is the effect on the transaction log and its size? Do all the entries generated during and related to ...
0
votes
0answers
28 views

LDF file size is very huge even after doing Transaction log backup [duplicate]

I work on a SQL Server 2016 Database and in our production environment we have primary SQL server and a fail over SQL server. All the transactions are occurring on the primary SQL server. In primary , ...
1
vote
1answer
44 views

SQL Server 2012 R2 Transaction Log needed to create database mirror?

There's a somewhat lengthy discussion of Transaction Logs in Microsoft's instructions for setting up a database mirror. We've never bothered with Transaction Logs in the past; do we really need to ...
0
votes
2answers
88 views

Log flushes per second appears low

I've newly taken over administration of a system with about 25 databases. Using Redgate Monitor to chart some metrics, I see that each day, one database shows values for log flushes / second that look ...
0
votes
1answer
230 views

Delayed durability - can data loss be prevented in case of crash

This book suggests that "Data loss is possible with delayed durability because you might lose committed transactions. If SQL Server crashes after a transaction commits, but before the 60KB buffer is ...
0
votes
2answers
72 views

Determining why a transaction log file is so large [duplicate]

I have a SQL Server 2016 Enterprise database server that I have recently inherited. One instance on this server has an application that uses two different databases. One of these is the actual ...
0
votes
2answers
74 views

Transaction log backup - Only full recovery model?

I am reading this book from Microsoft - it says: "A log backup cannot be performed unless the database is in full recovery model and an initial full backup has been performed" Is this correct? I know ...
0
votes
2answers
44 views

In Full recovery mode, when does the buffer/transaction log changes get applied to the database data pages?

When a change is to be made, the data page is fetched from the database data file into the buffer and changes are made to the buffer page and are also written to the transaction log. Write ahead ...
1
vote
0answers
60 views

Log Shipping with minimum synchronization gap

We will have an application that will go live soon. I will be using Log Shipping for Disaster Recovery but I am confused about the Job scheduling. The transaction logs will be taken every 10 minutes ...
1
vote
2answers
267 views

After a full backup, how does system know which transactions to restore from the transaction log backup?

Full backup does not truncate the transaction log file. Assuming the Following Scenario Full backup at 6AM TLog backup at 10AM Full backup at 1PM TLog backup at 6PM Crash immediately at the next ...
0
votes
1answer
25 views

Help to understand 'log space used' metric

The chart below comes from a monitoring tool. The metric shown is "log space used" for SQL Server 2016 for the last 7 days. I'm looking for help to understand what's going on here. What I do know is ...
2
votes
3answers
167 views

Transaction log filling the drive if mirroring fails

I am having two SQL Server 2016 machines with mirroring set up. This is supposed to be hot-standby scheme. HDD size is 1TB, and database size is around 600GB (just one DB). This is 90 days worth of ...
1
vote
0answers
17 views

How I can truncate and shrink the Logs file in mirroring (Full Recovery model)?

I am trying to truncate and shrink the database. I have taken the backup of transaction logs and now trying to shrink but it's executing with No effect. I have used dbcc shrinkfile(). Please help
1
vote
0answers
41 views

Spin up an extra sql azure instance to reorganize/rebuild indexes with no downtime

We have a pretty large sql azure database, over 300GB. Many tables have been altered over time and we would like to create fresh indexes, including clustered ones. Of course we would like to minimize ...
2
votes
3answers
159 views

SQL Server Transaction Log VLFs not being released (Status 2) on log shipped Database

Have seen a couple of posts similar to my scenario, but nothing that actually resolves my issue. Scenario: Routine index rebuild happens, once a week, 2 AM Saturday morning. Log file grows about 15x ...