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

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.

0
votes
1answer
28 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
35 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
83 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
32 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
25 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
52 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
37 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
14 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 ...
2
votes
1answer
63 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
52 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
18 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
37 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
107 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
39 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
44 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
402 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
94 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
53 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
29 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
43 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
68 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
209 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
57 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
60 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
42 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
57 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
225 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
24 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
140 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
16 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
31 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
126 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 ...
0
votes
1answer
329 views

Backup Plan on Always On using Maintenance Wizard doing Tlogs on Primary and Secondary

I have my set my back up preference set to primary and the secondary is not readable. Then I did the following: Setup the same maintenance plan on all replicas in the AG through the maintenance ...
2
votes
1answer
185 views

SQL Server 2017 log file growth with memory optimized table

I'm start to using the memory optimized table, the problem that I have is the log file growth also with the database put in simple mode. On my maintenance schedule I checkpoint and shrink, but still ...
0
votes
1answer
40 views

Saving all sql server transactions to txt for a transactional replication to another database [closed]

I have sql server 2014 running my main database at location A. I want to know if it would be possible to export all insert,update and delete queries to txt files for me to then run on a off site ...
1
vote
1answer
180 views

Remote harden of transaction 'CREATE INDEX' - when moving an index to a different filegroup

Remote harden of transaction 'CREATE INDEX' (ID 0x0000000082a3e3fc 0000:8d8f7911) started at Aug 14 2018 6:14PM in database 'cola' at LSN (2599205:10500:111) failed. The statement has been ...
2
votes
2answers
123 views

Estimate transaction log size for single transaction

Today I have tried to execute an update operation on 50M+ rows, something like: UPDATE [table] SET [Column] += 1; after 30min of waiting I have seen that the log has started to filling itself, after ...
0
votes
1answer
32 views

Transaction log for standalone DB vs AlwaysON availability Group

I have this question on the generation of transaction logs. For a high transaction active DB what is the ratio of log generation if it is a standalone DB and when it is part of SQL Alwayson ...
1
vote
1answer
104 views

Database Log Reuse Wait shows AVAILABILITY_REPLICA but database is no longer part of an availability group

My database log is growing out of control, I recently removed the database from an availability group by when I look at the log reuse wait description it shows "AVAILABILITY_REPLICA" I have tried to ...
0
votes
1answer
39 views

Size of Log Backup is not reducing from past three days

Recently we implemented Incremental(Enabled allow incremental dumps) and Log Backup(Disabled trunc Log on chkpt) for Sybase ASE(15.7 SP139) running on HP-UX Itanium server and to our great surprise, ...
0
votes
1answer
74 views

LSN mentioned in Backup is missing in Backupset

I was testing some restore scenario for one of my requirement. SO while i was restoring i got the below error. --Msg 4305, Level 16, State 1, Line 79 --The log in this backup set begins at LSN ...
-1
votes
2answers
72 views

RESTORE hourly transaction log to every 10 min interval

I have an hourly transaction log backup running for my production database. I have to implement the below scenario to check for a deleted data. I have two tlogs. tlog1.trn at 10 AM tlog2.trn at 11 ...
0
votes
3answers
66 views

Restore deleted data from a Table which occurred daily

I am not sure whether this is restore data scenario is possible. Scenario: I have a table with few 100 data inserts every day.I didn't realize that because of some reason(identified later as a bug in ...
0
votes
1answer
249 views

Innodb: When bulk inserting or updating - how to disable transactions?

When doing a mass insert like INSERT INTO SELECT FROM the speed benefit of doing that in chunks of 10k records can be enormous, in my tests a query that was running for 5 days took only 2 hours using ...
0
votes
1answer
171 views

Is there a routine in MySQL similar to fn_dblog in SQL-server?

I already search about fn_dblog and it is about transaction log and it is used in SQL SERVER. if there any way to do transaction log in MySQL like fn_dblog? i can't find one