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
0answers
19 views

Transaction log bloat when writing to table on replicated database with parameterized filter

We are observing abnormally large log writes whenever we update a table that is used for parameterized filter in a merge replication. There are a number of join filters as well. When executing a ...
3
votes
1answer
4k 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. ...
3
votes
4answers
2k views

tempdb in sql server

Using SQL Server 2000, I am getting the following error. The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space." How to solve it? I ...
3
votes
1answer
43 views

SQL Server 2012 mirror setup - how to reduce transaction log files [duplicate]

We just upgraded our Windows Server 2003\SQL Server 2005 setup to Windows Server 2012/SQL Server 2012. Both configurations have a mirror with NO witness server. On the old setup the transaction logs ...
2
votes
1answer
94 views

How to read detached or offline sql server transaction log? [on hold]

All we know commands that allow to read SQL Server transaction log file. But how to read offline/detached SQL Server transaction log, without SQL Server engine? There are tools like apexsql that ...
3
votes
1answer
651 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
116 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 ...
1
vote
1answer
166 views

SQL Server log file continues to grow? [duplicate]

I'm having an issue with a SQL Server log file which will not maintain a steady amount of space held on a HDD which it has been issued. The drive is ~50GB and the log file is growing to occupy this ...
1
vote
3answers
173 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
110 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
39 views

Query trans log or tempdb?

New DBA, all of our databases are SQL 2005. All of our servers run in Simple recovery mode. I work for a school district, where we report student attendance to the state. I run a query to fix ...
0
votes
1answer
54 views

SQL Server Data File/Log File poor performance

I've been asked to look at a database/application server that's "performing slow". No one could really tell me what was performing slow (except for the database backups) so this has been a discovery ...
4
votes
1answer
432 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
0answers
59 views

INSERT INTO with TABLOCK taking longer than without TABLOCK [closed]

I'm doing something like below to ensure the insert is minimally logged: insert into AnotherDatabase.dbo.HeapTable with (Tablock) ( <column fields ) select <column fields> from ...
0
votes
0answers
36 views

Does the transaction log grow during a full backup? [duplicate]

I've been tagged as the occasional DBA in the office. I have minimal experience and it would be great if someone could answer my simple question. I know the trans log grows during transactions, but ...
2
votes
2answers
88 views

The log in this backup set is too recent, but it's from before the last full backup!

I'm trying to restore a full database backup plus transaction logs to the most recent transaction log. I've set up transaction logs to ship out every 15 minutes. This setup was performed via the SMSS ...
3
votes
1answer
3k views

Disable SQL Server Log Shipping to nonexistent destination

I am administering a SQL Server 2008 instance in a dev environment. At some point before I became an administrator, someone enabled log shipping from one of the databases on this dev instance to ...
1
vote
3answers
282 views

MS SQL Server 2008 Transaction log file back up failure

I've done some reading around this subject here and on StackOverflow but nothing really answers my question enough for me to go forward. I'll explain we have a MS SQL 2008 server running, the ...
4
votes
2answers
174 views

Transaction log is full due to 'CHECKPOINT' for DB in simple recovery model

For one of our DB's on SQL server 2012 RTM, We could not perform almost any troubleshooting step as the error says: " Transaction log is full due to 'CHECKPOINT" This being a dev database, its ...
4
votes
1answer
83 views

Tempdb change settings and recommendation

In my production database I have below configuration in TempDb and needless to say I am facing a lot of IO issues on the drive . So, as per recommendation I am planning to change it Change #1 ...
4
votes
1answer
203 views

Large Transaction Backup

On my SQL database I run a full backup at 6am and transactional backups every 15 minutes on week days starting at 8am. Every Monday the first transactional backup is 7 Gb. I want to know why, my ...
0
votes
1answer
50 views

Why temdb and log file size is not reduced by SQL Server

With a series of question Part1, Part2 and Part3. With the answers and research on internet I came to know that TemDb is use for holding different values (ex.: Temp variable, Temp table, CTE, Hash ...
4
votes
1answer
74 views

IO request time and less write latency

I am facing issues related to constantly growing log file due to which I am getting error. When I checked SQL Log I found below messages (error log is filled of these messages almost 90%) SQL ...
3
votes
2answers
122 views

How to minimize log operations in SQL Server to avoid “log full” error

I have a database in production which constantly get log file filled, its a data warehouse and have many jobs/queries running for obvious. Below is the error which I am getting Msg 9002, Level 17, ...
1
vote
1answer
47 views

Dealing with non active transaction in log file and shrinking

I have posted the first part of my question here & I don't want to flood my question with a loads of information and would force people to alter their answers for my each question. I think I am ...
1
vote
1answer
54 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
70 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
24 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 ...
7
votes
2answers
12k 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 ...
25
votes
2answers
57k views

How to identify which query is filling up the tempdb transaction log?

I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
5
votes
2answers
2k 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
25 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
106 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
24 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
106 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
52 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 ...
0
votes
1answer
40 views

Mysql: binary log vs transaction log

In mysql with innodb, there are two types of logs: binary log and transaction log. I know transaction log is specific to innodb. But when comes to replication, backup and crash recover, what roles ...
1
vote
1answer
23 views

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

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
4k 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
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 ...
2
votes
1answer
91 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
2k 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
85 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
360 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
50 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
61 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
161 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 ...
0
votes
1answer
69 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
11k 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 ...