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
72 views

Why is my transaction file growing when db is in simple mode and there are no waiting transactions

I noticed that my database log file has reached 160GBs. My database is in simple recovery mode and I used the following query to see if there are any waiting transactions. SELECT ...
0
votes
1answer
588 views

Will restarting SQL Server clear my transaction log? [closed]

I have a client that restarts his SQL Server instance to clear an application problem he is encountering. I find this is very odd, I don't think you should ever restart the database in normal ...
0
votes
0answers
27 views

Database Log Files Getting out of control [duplicate]

We have a database in which the transaction log file is blowing up to 100GB with the actual database being 1GB in size. It blows up pretty quick as this database is pulling data from another database ...
8
votes
2answers
17k views

Sql Server - Best Practices for Growing Database Files

I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 ...
6
votes
2answers
3k views

Transaction Log and mirroring - looking for dumbest explanation possible

First of all I must admit that I struggle with concept of Transaction Log. I mean - I do understand that it is Log of all Transactions that happen on database, but when it comes to properly putting it ...
1
vote
0answers
57 views

Unsafe statement written to the binary log using statement format

In MySQL error log I am getting this warning message: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it ...
2
votes
1answer
116 views

Use transaction logs multiple times

Every night a full backup is made of all the user databases. Every 15 minutes, transaction logs are created. Today i succesfully restored a database with the use of transaction logs. I set the time ...
0
votes
0answers
25 views

Why is the transaction log file not truncated though its simple recovery model? [duplicate]

My database is simple recovery model and when I view the free space in log file it shows 99%. Why doesn't my log file truncate the committed data automatically to free space in ldf file? When I shrink ...
1
vote
1answer
516 views

What is stored in a transaction log file?

What does the transaction log file store? Is it the blocks of transactions to be executed, is it the snapshot of records before beginning the execution of a transaction or is it just the statements ...
0
votes
0answers
71 views

Why are transactions long running?

My stored procedure has a transaction blog. I pick up a record from a database and process it in that transaction block. I noticed that my transaction log has grown in gigabytes. But my transaction ...
1
vote
1answer
55 views

Why do you have to execute a full backup after a new database for trans backup jobs to run [closed]

Playing around on my local sql server at home after an issue occurred at work. We have full backup jobs scheduled on Mondays only. I created a DB on Tuesday (when doffs and trans are scheduled). ...
6
votes
3answers
13k 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
3answers
5k 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 ...
2
votes
3answers
5k 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
21 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
363 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
672 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
95 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
99 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
1k 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
1answer
17k 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 ...
0
votes
1answer
80 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 ...
0
votes
1answer
75 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 ...
1
vote
1answer
518 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 ...
1
vote
2answers
4k 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
155 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 ...
1
vote
0answers
125 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
200 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
2k 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
115 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
564 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
280 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 ...
4
votes
4answers
6k 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
66 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 ...
2
votes
2answers
115 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 ...
0
votes
1answer
28 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
31 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
188 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
1answer
89 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
190 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
113 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
238 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
241 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 ...
3
votes
1answer
1k 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
203 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
818 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
254 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 ...
1
vote
2answers
69 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 ...