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

Could not redo log record on mirror

One of my database mirrors is in the suspended state today and the error log on the mirror shows the following. Could not redo log record (338934:102862:477), for transaction ID (0:848150408), ...
0
votes
0answers
17 views

Options for reducing transaction log size when disk space is restricted [duplicate]

I have a very unfortunate situation in which I have some SQL Server (standard edition) databases living on a small (~120gb) disk, and a transaction log which has grown to 50gb+. Unfortunately, the ...
3
votes
1answer
419 views

Unable to shrink log file due to snapshot replication

Our database log (SQL Server 2008 R2) is growing too big, and truncating it via DBCC SHRINKFILE doesn't work (the database is set to simple recovery mode). A bit of background. I released some ...
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 ...
4
votes
1answer
159 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
2answers
245 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
221 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 ...
0
votes
1answer
56 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
243 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 ...
1
vote
0answers
1k 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
38 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
2k 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 ...
1
vote
3answers
1k 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
1answer
187 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
219 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
89 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
186 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
1k 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
88 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
69 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
414 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
26 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
16k 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
53 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
114 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
395 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
65 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
47 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
10k 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
4k 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
164 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
4k 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
240 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
574 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
90 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 ...
1
vote
2answers
88 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
782 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
16k 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
73 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
73 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
431 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
3k 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
129 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
111 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
168 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 ...