Transaction log is a list/history of modification executed by a database management system to guarantee ACID properties over crashes or hardware failures.

learn more… | top users | synonyms

1
vote
1answer
13 views

When is it safe to disable InnoDB doublewrite buffering?

MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked. How could InnoDB still be able to maintain ...
3
votes
1answer
204 views

Issue shrinking dev sql server DB's log file

I have a development db that's running on sql 2014 in 2012 compatibility that I'm having an issue with trying to shrink the log file. The mdf is 60+gb and the log is 80+gb but since it's dev like ...
1
vote
4answers
4k views

What is the purpose of having a table NOT LOGGED INITIALLY

I came across this recently. When defining a table in DB2 LUW (at least 9.5 or higher), you can define it as NOT LOGGED INITIALLY. Example from the book I read: CREATE TABLE products ( productID ...
0
votes
1answer
25 views

The purpose of transaction log in Restore First

Based on this article , the golden role for restore first After restoring full database backup, restore latest differential database backup and all the transaction log backup after that to get ...
1
vote
1answer
447 views

TempDB Log Space and ACTIVE_TRANSACTION

Our monitoring solution (SCOM) is currently flagging that the tempdb log is running out of space. However we have auto grow set to 1GB chunks for the log and we have 25GB of space left on the drive. ...
0
votes
3answers
61 views

I do not understand SQL Server Transaction Logs

I understand that a Microsoft SQL Server Database has two components: A database file A transaction log file Whenever I make a change to a table, does it get written to both the database file AND ...
-3
votes
0answers
27 views

I want to reduce SQL Server Transaction log file size [duplicate]

I have 7 TB of db and this db having 3 data files and 5 log files. In this 5th log file having 1.2 TB space. And this db is in bulk-logged recovery model. Now what are the steps needs to do for reduce ...
0
votes
2answers
30 views

Log Sequence Numbers in Full BackUp and Log Backup

My full back-up starts daily at 02:00 AM. It takes about 3 hours. I also take T-Log back-ups every 15 minutes (at 15,30,45 and 00 hours). I can verify the LSN sequences of T-log backups when I ...
2
votes
2answers
65 views

Deleting rows from a table with a very small amount of expansion space

I have multiple tables that I have backed up the data in, and now am deleting the old data for the time, archiving is not an option due to space constraints at this time. I have tried running the ...
7
votes
4answers
223 views

Transaction Log won't shrink, DB thinks it is replicating

I've got a SQL Server 2008 R2 Express database running Kaspersky Security Center, and I have no idea under what circumstances the install happened, but the database appears to think that it's being ...
0
votes
1answer
23 views

Removing Components from a backup

Ok, I will start this off by: Yes, I know I should have paid more attention. That being said, I was trying to create a full backup of my DB prior to doing truncation on a number of the tables. When ...
0
votes
0answers
23 views

Controlling SQL Backups From Two Machines

I'm far from a DBA, and don't really have a great understanding of backup chains, but I was asked the following question by a network administrator, and wanted to be sure that my answer was correct. ...
1
vote
2answers
95 views

How to find what frequent and/or large transaction have taken place in MS SQL Server?

Each day is experiencing about 1GB sum total of all the transaction log backups that day, while at the end of that week the Full DB backup is about 180MB. For a business database for buying/selling ...
1
vote
0answers
48 views

Breaking up large transaction - worth it?

I'm in charge of monitoring the performance of an industrial chemistry process. Every hour, a process gathers 50 million rows from various subsystems and feed into the central database. The whole ...
3
votes
1answer
486 views

SQL Server Troubleshooting Replication and Long-Running fn_dblog query

I am trying to get (pull) Transactional Replication working again for a subscriber. Currently, in Replication Monitor I am seeing messages in the Publisher to Distributor tab and the Distributor to ...
2
votes
2answers
37 views

Sqlserver: Get Meta-Info about TRN file

Is there any way to get the timespan of the data contained in a TRN-File? What I have: A backup of a transaction-log What I need: The timestamp of the first and the last entry inside the log ...
2
votes
1answer
140 views

Checkpoints and Logging

I'm working on getting full grasp of logging architecture. Please correct me if I'm wrong here: I understand that a checkpoint occurs (also lazywriter) to write the dirty pages in the memory to the ...
0
votes
0answers
52 views

Availability Groups, Log File Backups and Recovering the Secondary

I am currently working on ironing out some kinks in a testing setup for our large (1.5TB+) database in an Availability group and have a few things I need some help with. Setup I have a two node ...
1
vote
1answer
82 views

Find out who ran a query using transaction log backups

We have a SQL Server 2012 Enterprise database running in Full Recovery Model with transaction log backups running every minute. A query was run last night which means we need to recover data from a ...
3
votes
3answers
147 views

Question about logging architecture

I understand that when (for example) an update is issued, the data pages are first read into the buffer pool, locks are acquired and changes are written out to the log file in the buffer, then the log ...
0
votes
1answer
64 views

Is there a way to minimally log insert under Always on?

I have a primary database in FULL recovery mode which is part of Always On group. Is there a way to minimally log insert operation under FULL recovery model? I have a process that is executed each ...
1
vote
2answers
102 views

How to deal with very large transaction logs in SQL Server [duplicate]

Here is my situation: I have being asked to deal with a SQL Server 2008 R2 database that has a transaction log that is several times bigger than the database itself. The database is in full recovery ...
0
votes
1answer
81 views

A transaction log grows unexpectedly [duplicate]

I am trying to understand what process is making my transaction log file to grow. For example, currently it is 120 GB and only 5-10 MB of it are in use. Before: FULL recovery mode a backup of the ...
2
votes
1answer
67 views

what are the events recorded in the transaction log?

in regards to what events are recorded in the transaction log , is the below an accurate/comprehensive list? How are things recorded? are the pages that are marked as modified written to the ...
0
votes
2answers
66 views

Is it good idea to repair sql file?

We are having an ETL process which inserts lots of data into tables. This database is set to Simple Recovery Model and the transaction log is growing a lot. I was thinking that would it help to set ...
1
vote
0answers
44 views

What is the RecoveryUnitID in DBCC LOGINFO

In SQL Server 2012, MSFT added a new column to the output of DBCC LOGINFO, called RecoveryUnitID. I know that this DBCC command is undocumented, but I am surprised that I can't find it anywhere what ...
3
votes
3answers
4k views

Difference between Full backup and Copy-only full backup

I saw in this link that full backup does not truncate the log. So what is the difference between full backup and copy-only full backup? For the log backup there is copy-only backup which prevent the ...
3
votes
1answer
95 views

Database is happily available even during log growth?

I’ve been creating a process to go through all our databases and analyze the log structure, backup, shrink, and resize appropriately taking operational requirements and VLF sizing/amount into account. ...
1
vote
2answers
146 views

Does a database backup reduce size/increase free space of the log file? [duplicate]

Novice question, sorry! Got a SQL Server database and the log file is massive and keeps growing every day. I've archived data from the database and then done a full backup. I was expecting the log ...
6
votes
2answers
2k views

Transaction Log maintenance on Mirror database

SQL Server Version: 2008 R2 Enterprise SP2 I am trying to get a handle on our SQL Server maintenance and I came across something I think is incorrect. We have a single production instance with 3 ...
2
votes
3answers
3k views

Simple model database transaction log full 'CHECKPOINT'

I have a SQL Server 2012 SP1 database which has a problem that the transaction log is full. It consists of a single data file and a single log file, both about 800MB. I had 5GB free disk space, and ...
1
vote
1answer
361 views

Cannot shrink transaction log

Bit of a headscratcher here. I have a db that has less than 1GB of data, but a 40GB log file. Transaction logs are backed up daily, and there is not a lot of activity on this database; approximately ...
1
vote
1answer
237 views

Restore Verify Only w/ Transaction Log Backups

Question When RESTORE VERIFY ONLY on a transaction log backup fails, what do you do? Background After FULL and DIFFERENTIAL backups, I run RESTORE VERIFY ONLY on the backup file(s). This rarely ever ...
1
vote
1answer
179 views

Transaction Log is big! But simple recovery mode is chosen!

I am wondering: We have a Database on our MS SQL Server 2012. The recovery mode is "simple". The DB itself is about 2.7GB big. I have an MAX Size for Trn-Log configured (3GB). Now i made a ...
1
vote
1answer
33 views

Viewing how much transaction log is used in Simple Recovery Model

We have a DW database in Simple Recovery Model. Every night, we load in data from different sources and run a few procs. I'd like to see how much the transaction log is actually used during this ...
1
vote
1answer
64 views

Upgrading 8.1 to 9.3 - using statement log and WAL to check compatibility?

We're migrating from postgres 8.1 to 9.3, since we need streaming replication badly. All the application developers have deserted the company a long time ago, way before my time here, so there is ...
1
vote
1answer
216 views

Shut Down MySQL during InnoDB rollback after crash

I am puzzled once again. We had a server crash last night during some huge queries. Upon restart in the morning MySQL went into recovery mode. I needed to shut down the MySQL service and it is ...
1
vote
2answers
63 views

VLF, Shrink and Simple Recovery

We currently have a transaction log thats been growing incrementally and has too many VLF files. I am going to follow a process of shrinking and then re-growing the log file to a suitable size to ...
2
votes
1answer
56 views

What is the best storing device for DB transaction log?

I'm using MySQL with InnoDB storing engine. Transactional log is placed to dedicated HDD. Still as it is HDD atopsar shows big load (>50%) to this HDD because of big amount of small IOs (about 50 ...
1
vote
1answer
128 views

Mysqldump --flush-logs results in small backup files?

New to MySQL and just learning how to setup a daily full backup (binary logs enabled). My goal is to run a daily full backup of the databases/schemas and reset the binary log. I'm currently using an ...
1
vote
2answers
78 views

SQL Server 2008 R2 Log File Location

One of the databases I am going to support does not have a validate log file location. The drive which the log file is pointing to does not exist. But I don't see any error in database error log ...
1
vote
0answers
58 views

Alternatives to log shipping / Replicate a local SQL Server database on a remote DB accesible only by IP

I'm trying to replicate a local SQL Server 2008R2 DB to a remote DB instance accessible only by IP (specifically, a RackSpace cloud site DB). I believe log shipping is ideal, since the remote only ...
4
votes
2answers
873 views

How can I find databases with a large number of virtual log files?

I have several databases on a production server that are in the hundreds of gigabytes, and have many thousands of transactions running through them on a daily basis. Almost all these databases are ...
1
vote
2answers
216 views

What is causing my Transaction log to grow

The TLOG in my SQL Server 2008 is growing faster than usual. The DB has a Full recovery model and we have a TLOG backup every hour. Can you please write me or refer me to link that describes a way to ...
2
votes
1answer
48 views

Is tlog backup on replica clearing VLFs on the primary?

Is it true that a transaction log backup made on the secondary database is clearing the inactive VLFs on the tlog file on the primary database (in case of Availability Groups)? I was not able to find ...
4
votes
5answers
11k views

Shrinking the log file does not reduce size

I have a database which had mdf size of 350 MB and ldf size 4.9 GB When i try to shrink the log file it's not shrinking. I know shrinking a database is not good and it should not be done. But still i ...
0
votes
0answers
61 views

Too much IO to MySQL InnoDB log files

I am using MySQL version: 5.5.38 I have dedicated HDD to InnoDB log files using innodb-log-group-home-dir option. atopsar -d 30 shows big load (>50%) to this HDD The thing is that I have only 75 ...
0
votes
1answer
54 views

MySQL InnoDB log files on different location

I am using MySQL version: 5.5.38 There are following parameters that can control InnoDB log files: innodb_log_files_in_group - amount of log files in the group, by default 2. Will create 2 files ...
2
votes
1answer
82 views

How to find the active transactions in VLF?

Is there a way to find the T-SQL statement that is part of the active transaction that is blocking the reuse of the transaction log file VLFs? I am performing tlog backups on every 15 minutes, but ...
1
vote
1answer
116 views

Transaction Log Shipping Removal, Backup and Drop Database

I have databases (about 15 different) Production Databases set for Log_Shipping and I would like to take a full backup of it and need to drop from production later on because we have already move it ...