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

0
votes
0answers
7 views

Push all SQL Server changes to a non-SQL Server platform [on hold]

What is the best approach for capturing all the changes (all creates, inserts, updates, and deletes) and sending those into an external platform like Datomic for history/reporting/auditing? I'm ...
2
votes
3answers
3k views

Transaction log has grown huge after maintenance plan to rebuild idex

A couple of days ago I was testing some stuff using the Maintenance Plans in SQL Server 2008. I created one to rebuild indexes and take a full backup every week. (I know, bad thing I've found out ...
0
votes
1answer
86 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
0answers
28 views

How to move records to another database and free some space

We do monthly a "cleanup" of the database: we set the database in simple recovery mode and then we move records to an historic database so we can delete from the original for free some space with DBCC ...
3
votes
1answer
578 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
1answer
64 views

How does the transaction log record a transaction?

We all know what SQL Server's transaction log records information about the transaction. But what, in a full recovery model, is actually recorded in the transaction log? You'll say: THE TRANSACTIONS! ...
2
votes
3answers
1k 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 ...
0
votes
0answers
20 views

SQL Server Transaction Log Huge [duplicate]

I came to work today and I saw the transaction log with 441GB, using almost all disk space. I use SQL Server 2012 and have Full backup, but somehow I lost control of the t-log. So, I need to do 2 ...
1
vote
1answer
39 views

Read transaction log records

I am reading Remus Rusanu article on tlog.In that article he demoed on how to read log payload by using dbcc page option .My question is ,is there a way to read logrecord output of fn_dblog(null,null) ...
0
votes
2answers
255 views

Audit SQL Server database transactions

I found some column values have been changed on a table, I need to view when and who made these changes. I tried to read the transaction log backup file, but I didn't understand it and cannot find ...
0
votes
1answer
34 views

logging in Bulk logged recovery VS Full recovery model

I am trying to understand the logging aspects in Full and Bulk recovery models.As per MSDN ,select * into test from abcsd is logged minimally in Bulk logged ,but in full recovery model,it is logged ...
0
votes
2answers
50 views

Database logs growing during maintenance plan

I have read several threads and sites about SQL logs but I don't feel I have found the answer. Or if I did I did not understand it enough to address my issues. My issue: When I run my SQL maintenance ...
1
vote
1answer
52 views

DB2 ROLLFORWARD option “TO END OF LOGS” - does it read archive logs?

I read about ROLLFORWARD option on link http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001978.html?cp=SSEPGG_9.7.0%2F3-6-2-4-106 but I could not find ...
1
vote
2answers
123 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 ...
0
votes
1answer
38 views

Alter column opreation failed due to disk space in SQL Server

When i apply alter column on table, it full log file and disk too, and fails alter operation. Why this happened even table size is less than free disk? Can anyone please suggest a way to complete this ...
1
vote
1answer
9k views

DB2: The transaction log for the database is full, But there is no 'First active log file'

First, I wish to say that I have very little experience with DB2. However, I've been tasked with tracking down the problem and solution to this problem. This is a DB2 database, and our testers are ...
3
votes
1answer
350 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
50 views

Behavior of Oracle During Very Large Transactions

I understand that if a transaction is large enough to overflow all of the redo logs on an Oracle database, and the database is in ARCHIVELOG mode, the transaction will wait until the archive log file ...
2
votes
1answer
2k 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
1answer
32 views

DB2 Transaction Logs - can they be COPY-PASTE on another Disaster Recovery Server?

We have Server1 with Online Backup1, Archive Logs 1,2,3 for example and Transaction Logs from 1 to 10. We are interested in next scenario: If Server1 fails completely and we want to move everything ...
0
votes
0answers
36 views

Partitioning large table requires lots of extra disk space: does switching recovery model help?

We are in the process of implementing partitioning on a SQL Server 2012 database. It worked fine in our environment, but in the client environment we ran out of disk space. The entire database was ...
1
vote
1answer
74 views

SQL Server differential backup LSN values

I'm looking at restoring databases on SQL Server and I'm not understanding something related to the LSNs. Here is a screenshot from the Restore Database dialog in Management Studio for a sample ...
0
votes
2answers
99 views

DB2 ARCHIVE mode - Can all archive logs be deleted after doing the FULL OFFLINE backup?

I am interested in one thing, if I understood correctly. DB2 is in Archive mode (online backups are made and archive logs keep coming which is OK). If I made offline full backup today and I know I ...
1
vote
1answer
59 views

Shrink log file after configuring backups properly

I had a database that was working in FULL recovery mode but never had transaction log backups done. The consequence of that was the fact that the log file kept growing and now its 10 GB in size. I ...
0
votes
1answer
64 views

DB2 big strange problem - database is returned to CIRCULAR mode but archive logs keep coming to old archive location

my DB2 database (v 9.7) is in Circular mode. Current Logging Type:Circular (USEREXIT) = OFF (LOGRETAIN) = OFF (LOGARCHMETH1) = OFF (LOGARCHMETH2) = OFF (LOGPRIMARY) = 10 (LOGSECOND) = 10 Path to log ...
0
votes
0answers
14 views

tempdb Log Continuing to Fill, but No Long Running Transactions In Flight [duplicate]

I'm suspecting a report is generating a large data set with sorting in tempdb, causing the tempdb tlog to fill. I'm trying to identify the session that's generating the log growth but DBCC OPENTRAN is ...
1
vote
0answers
42 views

Why does shrinking the transaction log generate lots of log (LOP_SHRINK_NOOP) and not shrink the log?

I just tried shrinking an overly big log file (FULL recovery model). This operation is not expected to always succeed and indeed the log was not shrunk. But I observed that a lot of new log was ...
2
votes
2answers
81 views

How should I (re) index the table while keeping the transaction log small?

I have a 170 GB table in a database. It has 70% fragmentation on its index (index has multiple columns in it). The last time I tried DBCC REINDEX the transaction log blew up in the hundreds of GBs. ...
2
votes
3answers
1k 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 ...
2
votes
0answers
36 views

SQL Server transaction log reported full when it isn't [duplicate]

The transaction log for database 'WebDB' is full. No, it isn't. Its 90% free. The transaction log backups run normally. My hardware guy tells me the hardware is running perfectly, with plenty of ...
0
votes
0answers
34 views

Tran Log file growing even after log backups and Log shipping configured

I have log shipping configured for a database in sql server 2005 with below details: A) Database X hosted on primary: Log backup every 1 hour as scheduled in LS Backup job: Data file: 40 GB and Log ...
1
vote
1answer
183 views

Index rebuild log growth

How much log index rebuild will generate.I remebered reading rebuild index should generate same amount of log file as table size.But my tests are showing otherwise.We need this estimate since we are ...
5
votes
3answers
2k views

Encountered unexpected growth Transaction log file size

We are using SQL Server 2008 R2 Standard version, we created Maintenance plan Rebuild Index, Reorganized Index and update Statistic , Scheduled Every day at 1 AM. After Executing this Maintenance ...
3
votes
4answers
233 views

Does it make sense to use full recovery if the data and log files are on the same HD?

We have a database that was set up to use full recovery and my guess is that the reasoning was only to prevent data loss if a failure happened between full backups. We make daily full backups of the ...
2
votes
2answers
346 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 ...
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
41 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 ...
0
votes
3answers
85 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 ...
0
votes
2answers
58 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
84 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
417 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
25 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
24 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
0answers
76 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 ...
2
votes
2answers
50 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
156 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
64 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
108 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
166 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
222 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 ...