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

2
votes
2answers
28 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
125 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
36 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 ...
2
votes
1answer
180 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
1answer
68 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
140 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
51 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
1answer
143 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. ...
1
vote
2answers
96 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
67 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
65 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
40 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
92 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
100 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 ...
3
votes
1answer
469 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
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
331 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
193 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
168 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
52 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
125 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
53 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
54 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
116 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
64 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
49 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
862 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
208 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
43 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
10k 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
57 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
43 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
73 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
78 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 ...
0
votes
0answers
126 views

Log full and an Active_transaction on database

I have limited access to this server eg I cannot SELECT * FROM sys.dm_tran_session_transactions for example I, perhaps foolishly, ran this query to insert some test data... begin transaction update ...
1
vote
3answers
142 views

SQL Server 2005 Log Truncate - how long for a 60Gb log; mins, hours or days? [closed]

I have taken over an old website. To cut a long story short the people that host the computer, backup all the database files, but there is no maintenance plan in place for the backup (e.g. they ...
2
votes
2answers
135 views

Transaction Log Backups

I am working on backup+recovery scenario and I came across a situation where I could not figure out how I would have to deal with it. We have couple databases where sometimes there may not be any ...
1
vote
2answers
405 views

How do you create a job in SQL server 2008 R2 that will clear transaction logs?

I have created a maintenance plan in SQL Server 2008 to delete transaction logs backups older than 3 days. Every day I look in the folder designated and the logs are still there. The job states it has ...
0
votes
1answer
47 views

Log Shipping Alert

I have 3 databases which I have took them offline and I have Log Shipping setup on them and I wanted to stop alerts for those databases. How can I do that? Thank You for help
0
votes
2answers
84 views

Log File Size near 100% - How to find Active Transaction [duplicate]

The transaction log file size is nearing 100% on one database. I run the below and both return 0 so no active transaction running. The last query tells me that the database is in SIMPLE mode, but ...
5
votes
3answers
293 views

Avamar SQL Agent running against live SQL Server databases

We use a large company as our service provider. The tech people from that service company are pushing very hard to install something called the Avamar SQL Agent which purportedly will be used to ...
1
vote
1answer
77 views

Fix transaction log file after database update

I have a databases that are regularly (monthly) updated. An update includes a lot of new or changed T-SQP procedures, functions, indexes creation, etc. A database X has transaction log file with the ...
3
votes
1answer
67 views

Tail Log backup when doing a restore?

Typically when doing a restore of a DB from a production server to a non-production server I will use the WITH REPLACE option as when I forget I get an error about the tail log not being backed up. ...
4
votes
1answer
865 views

Why can't I read my transaction log backup file using fn_dump_dblog?

I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery. After backing up the transaction log, I attempt to read it ...
0
votes
2answers
1k views

ORA-00338: log # of thread # is more recent than control file

We are facing the following error on our production database: ORA-00338: log # of thread # is more recent than control file The database crashed and there was no recovery action. What could I ...
4
votes
3answers
893 views

Why does transaction log continue to grow in Simple recovery mode with nightly backups

Before immediately marking as duplicate, I have read Mike Walsh's Why Does the Transaction Log Keep Growing or Run Out of Space?, but I don't think it gave an answer to my situation. I looked through ...