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
1answer
68 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
56 views

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

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 ...
1
vote
1answer
150 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
28 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
51 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
1answer
35 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
40 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
44 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
42 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
2answers
56 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
39 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 ...
1
vote
2answers
194 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
42 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 ...
0
votes
1answer
31 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 ...
0
votes
0answers
39 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 ...
1
vote
1answer
45 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 ...
2
votes
1answer
60 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 ...
0
votes
0answers
65 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 ...
2
votes
2answers
126 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
3answers
125 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 ...
0
votes
1answer
32 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
1
vote
2answers
134 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
2answers
74 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
184 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
60 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
55 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
3answers
572 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 ...
2
votes
1answer
111 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 ...
0
votes
1answer
439 views

Shrink Transaction Log While Using AlwaysOn Availability Group

We are using AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database. I have read here doing the ...
0
votes
0answers
26 views

Maintenance Plan Transaction Log Backup

I have a backup device set for my transaction log so the sets are contained in 1 file. I have this set to append, is this correct or should it be overwrite in the plan? After the full backup is taken ...
0
votes
2answers
84 views

How to deal with very large transaction logs in SQL Server

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 ...
1
vote
0answers
1k views

Transaction log full due to log_backup

I am testing how certain operations are logged under different recovery models.Below are steps i have did till now 1.create database in full recovery model 2.take backup 3.create a table and insert ...
2
votes
1answer
95 views

What is the differnent between transaction log and redo log in MySQL

I have read about MySQL. In my opinion, I see two log type is very similar. MySQL save how and when data is changed in log. Information is used for recovery of MySQL. I am confusing the function of ...
1
vote
2answers
177 views

Fixing an error 9004 without taking the database offline

My database (SQL Server 2008 r2) is running fine but I’m getting the following error in my event log. Error: 9004, Severity: 23, State: 6. An error occurred while processing the log for database ...
4
votes
1answer
87 views

Differential/T-Log LSN question

So, this is now my third question on this topic. I just keep finding more and more than i want to know and understand about how FULL recovery works. We transitioned a database to FULL yesterday by ...
1
vote
1answer
147 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 ...
0
votes
2answers
84 views

Open Transaction that won't go away SPID Ns

There is a transaction that has been open on this instance since it was restarted and is preventing us from clearing the ldf. Here is the transaction information: Transaction information for ...
2
votes
2answers
79 views

Switching from SIMPLE to FULL recovery mode implications

I have several databases that I'd like to move over to FULL recovery mode so we can have point-in-time restore capability. We don't need it on all of our databases, just the ones that are transaction ...
1
vote
1answer
55 views

Statistical probability of an incomplete transaction committed by Postgres

tldr After spending time with bad databases which I will not mention, I came to question even the point of a database. I knew that they could serve a purpose to keep permanent data organized, but ...
3
votes
3answers
126 views

Log File Management - Space on Disk

At our organisation we have several non-Production environments where Developers run free and wild with TSQL code and databases. The DBA team do not normally monitor or maintain them. Recently several ...
1
vote
1answer
91 views

Dealing with large transaction logs in a cluster

Disclaimer: I'm a dev, not a DBA. I've gleaned what I can from MSDN, but could do with some expert guidance! I'm working with a SharePoint 2013 installation on a SQL Server 2012 cluster of two DB ...
5
votes
3answers
200 views

Virtual Log Files (VLFs) Fragmentation

I have a database with 160 VLFs. How do I determine whether the VLFs are fragmented? Further, how do I find the percentage of fragmentation? Another thing that I want to confirm: Are the following ...
0
votes
0answers
24 views

Lock rows for long running transactions

I have a question table from which agents try to answer 5 questions at a time. Obviously, I want to avoid agent collision i.e. if an agent is answering a question, other agents can not work on it. Is ...
0
votes
0answers
22 views

Reducing log files? [duplicate]

I have a database that has a log file of nearly 80 GB. Database size 180 GB. The recovery is set to Full. What is the correct way to shrink the log file without using the shrink option in SSMS as ...
-2
votes
1answer
77 views

Non- Logged operation [closed]

I want to run a non-logged operation with changing the recovery model of a SQL Server database. Is their any way to do this?
1
vote
1answer
183 views

Transaction Log Shipping doesn't use the latest transaction log

I have setup transaction log shipping between two servers every 15 minutes, this is on SQL Server 2008 R2. The problem is that when the logs are being restored on the secondary server, it always uses ...
2
votes
2answers
308 views

How to do a one-time log shrink on a database with transactional replication

I have inherited a 10 GB database with an 80 GB log [only 3% in use according to DBCC SQLPERF(logspace)]. It seems safe to assume the extreme log growth was due to problems from long before I was ...
2
votes
1answer
97 views

SQL Server - Data and Log Separation

We have a SQL Server 2008 R2 which has, among others, a 12K RPM HDD and a 7.2K RPM HDD. The original intention was to store the data files on the 12K drive and the log files on the 7.2K drive, as is ...
2
votes
1answer
138 views

Why write ahead logs in PostgreSQL are generated every second

PostgreSQL version 9.2 generates write ahead log (WAL) every second i.e. 60 WALs are generated in one minute. Is it possible to make them occur less frequently? Is it possible to increase the WAL ...
5
votes
4answers
233 views

Only do log backup if necessary in SQL Server

We have transaction log backups taken every few minutes from many databases. Is there a way to determine if there were any committed transactions since the last backup and only then do a log backup? ...