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

5
votes
2answers
310 views

Two Different Sources Taking Transaction Log Backups

I have inherited a database server with SQL Server 2005. The databases have full recovery mode with hourly transaction log backups. There are two sources taking full backups overnight and hourly ...
2
votes
1answer
117 views

Transaction log autogrowth duration

I am using event notifications to capture data and log file autogrowth events for all databases on my servers. I'm using the data to for analysis of database storage configuration. In looking at the ...
2
votes
2answers
122 views

Large number of VLF and shrinking the log

If there are lot number of VLF then it will effect the performance of the queries. I saw that solution is to shrink the log file. But shrinking the file is also bad. So how to take control of whole ...
9
votes
2answers
908 views

SQL Server transaction log backups: test whether tail log follows last known log backup

We are using SQL Server with full recovery mode. Given a full backup and a series of log backups, we would like to be able to check whether the log chain is complete from the last full backup to the ...
1
vote
2answers
895 views

Shrinking a SQL Server Log File Multiple Times

Recently ran into a situation where after a log backup and shrinkfile, the LDF file remained the same size. DBCC LOGINFO clearly showed only one active VLF and SSMS said that 99% of the space in the ...
3
votes
1answer
487 views

How to troubleshoot db/app latency during transaction log backup

I have an application that uses a SQL server (2008 R2) database and we are periodically having performance problems with the application and it coincides with our 15-minute transaction log backups. ...
5
votes
1answer
182 views

My log file is large, what should I do? [duplicate]

My transaction log is larger than I'd like it to be, and I want to know how I can fix that.
8
votes
1answer
545 views

How important are transaction log VLFs for performance?

How important are VLFs when considering database performance? What would describe an optimal situation for VLFs?
2
votes
2answers
1k 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 ...
1
vote
2answers
707 views

Running out of Transaction Log space during Alter Table

I'm running an alter table, alter column on a table with nearly 30 million rows and SQL Azure fails after approximately 18 minutes saying that The session has been terminated because of excessive ...
1
vote
1answer
284 views

Sneaky rollback in SQL Server 2008 R2

Is it possible for an entire set of committed transactions to be rolled back in SQL Server 2008 R2 without any notification or logging anywhere? We had a set of data apparently get rolled back this ...
3
votes
2answers
3k views

Difference between Full backup and Copy-only full backup

I saw in this link that full backup does not truncate the log. So what is the difference between full backup and copy-only full backup? For the log backup there is copy-only backup which prevent the ...
-2
votes
1answer
332 views

Restore to last transaction log restore

We have a log shipping setup where we replicate from our primary database to a secondary. Now that database is, by the way of log shipping, in standby and thus read-only. We set it up so we could ...
3
votes
3answers
2k views

Transaction Log maintenance when switching to Simple Recovery

Background: I recently inherited 50+ SQL Servers with 450+ databases. The nightly backups are roughly 8TB and, needless to say, we're using more disk space than we'd like. All of the databases are ...
0
votes
1answer
102 views

SQL Server automatic transaction log truncation

Have looked through msdn but cant find answers to these: Any help much appreciated: In simple recovery mode: Is there any TSQL or alternative functionality that will provide a notification either ...
0
votes
2answers
187 views

Shrink transaction log while transaction in progress

I have a several-GB import into my SQL Server 2005 (Standard Edition) database. The import is separated into 4 parts, each part in its own transaction. As you can guess, the transaction log files ...
0
votes
0answers
21 views

SQL Server 2008 log file growing and wont shrink. Hard Drive Space running out [duplicate]

I have a SQL Server 2008 database which has a log file (.ldf) with a size of 360GB and growing every day. The .mdf file is only 25GB. The hard drive is 950 GB and has 150GB free space. I want to ...
3
votes
2answers
429 views

How large will the Transaction Log backup be?

I encountered a production database of 12 GB with a log of 90 GB. Full and differential database backups are made with a third-party backup tool, but no one is backing up the transaction log so it got ...
4
votes
1answer
167 views

Can I decrypt TDE transaction log files for use with 3rd party software?

Is there a way to decrypt TDE log files on disk for use with 3rd party software? I have the original key and certificates. I am trying to use the transaction files with a 3rd party tool in order to ...
1
vote
2answers
579 views

Missing or lost transaction and no errors to show what went wrong

I have 2 missing records in SQL Server and I am trying to identify the problem that caused this issue. New records are inserted using a stored procedure, which will return an error code to the ...
7
votes
3answers
1k views

SQL Server 2012 Simple Recovery Model with LOG_BACKUP log_reuse_wait_desc

While I'm doing my own investigation, does anyone know why a database in SIMPLE recovery model has a LOG_BACKUP for the log_reuse_wait_desc? SQL Server 2012 SP1. No replication, no mirroring, no log ...
1
vote
4answers
2k views

Simple model database transaction log full 'CHECKPOINT'

I have a SQL Server 2012 SP1 database which has a problem that the transaction log is full. It consists of a single data file and a single log file, both about 800MB. I had 5GB free disk space, and ...
6
votes
1answer
363 views

When performing a cross-database transaction, in which transaction log(s) is the information stored?

Given the following snippet: -- error checking omitted for brevity begin tran exec database1..my_stored_procedure exec database2..my_other_stored_procedure if (@@error <> 0) rollback ...
4
votes
1answer
85 views

Log file growth issues

We're consolidating data from a bunch of databases into four reporting databases each night. Because the entire dataset is imported each night we do not need to be able to restore the data to a point ...
4
votes
5answers
5k views

Shrinking the log file does not reduce size

I have a database which had mdf size of 350 MB and ldf size 4.9 GB When i try to shrink the log file it's not shrinking. I know shrinking a database is not good and it should not be done. But still i ...
3
votes
1answer
365 views

Does creating / updating statistics have an impact on the transaction log?

Does creating or updating statistics, assuming on a large table, create significant (or at all) transaction log churn? I would think not as it does not change the underlying data or structure. If it ...
6
votes
1answer
2k views

Can I shrink the transaction log file on a mirror database?

This is a followup question to a previous question about why I couldn't shrink the log file on the principal database. To make a long story short, I setup database mirroring but forgot to make sure ...
5
votes
1answer
2k views

How do I shrink the physical Transaction Log file when it's the principal in a mirror?

We setup database mirroring over the weekend, and forgot to re-enable the job that backs up the transaction logs. When I came in this morning, the transaction log had ballooned to 58GB, and was taking ...
3
votes
2answers
391 views

Time to apply transaction logs: does it matter how many logs?

When restoring from a backup in SQL Server, the procedure is to restore the .bak file and then apply any .trn files since the last full backup. Does it make a difference how many .trn files there ...
3
votes
2answers
1k views

Reducing high VLF count

I have been using Brent Ozar's sp_blitz script to review our database setup and have found that the VLF count is high (13562!) on the primary DB since changing to Full recovery model. I ran a full ...
5
votes
1answer
242 views

When REBUILDing indexes on SQL Server, what bearing does tempdb & LOG disk speed have?

Say I have the a data disk that is 50x faster than the LOG and tempdb (measured by Random Write speed) disk. (Don't ask why that's something we'll be fixing if needed) I have a table that's got 19 ...
2
votes
1answer
94 views

Restoring a database back to a point in time before full backup was made

A problem with one of our database tables arose at the end of last week and I wanted to restore that database to a server in order to retrieve the table. We only have the last backup, and a full set ...
0
votes
2answers
998 views

SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs

After doing some research I cannot seem to find an answer to this question. Background I am attempting to setup a backup plan that fits the following three requirements: Reliability of backups, ...
5
votes
1answer
318 views

SQL availability groups log maintenance

I have a AG with 2 nodes in a site in sync mode and 1 offsite in async mode. I'm having trouble understanding how the logspace is maintained in this scenario. For instance, I'm taking log backups ...
0
votes
0answers
33 views

Performing a Transaction Log Backup on a Live Database [duplicate]

I have a server 2012 database that has a transaction log that is growing at a rapid rate. i have a job that runs once a day after production hours which backs up the log file and shrinks it. The ...
1
vote
1answer
108 views

Online backups with VENDOR library: what should be set for ACTIVE LOG PATH?

My customer is using Veritas NetBackup so we use its library file in the LOGARCHMETH1 parameter for the VENDOR option. That is for archive logging path. What path should be set for ACTIVE LOG PATH ...
6
votes
1answer
1k views

Transaction Log maintenance on Mirror database

SQL Server Version: 2008 R2 Enterprise SP2 I am trying to get a handle on our SQL Server maintenance and I came across something I think is incorrect. We have a single production instance with 3 ...
2
votes
4answers
6k views

Manually set log file size after shrink SQL Server 2008 R2

Am becoming a somewhat involuntary DBA at work at teh moment and really need some help on something. We have a 40GB database in Full Recovery Mode, no log backup configured and a huge log file of ...
0
votes
2answers
266 views

Database not naturally shrinking log file [duplicate]

I have a database that is set to Simple recovery mode. There is 99% free space in the log file and I can manually truncate the log file right down to 500 MB. The next day, after a daily import ...
2
votes
1answer
392 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 ...
1
vote
2answers
112 views

Who changed the size of the transaction log?

Can I know, who and when changed size of database transation log?
5
votes
1answer
350 views

Why did my transaction log grow so much? [duplicate]

Possible Duplicate: Why Does the Transaction Log Keep Growing or Run Out of Space? About a month ago I migrated my SQL Server 2008 R2 database to a new schema. I ran scripts to migrate the ...
4
votes
2answers
614 views

Why can't I read my transaction log backup file using fn_dump_dblog?

I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery. After backing up the transaction log, I attempt to read it ...
6
votes
2answers
401 views

SQL Server Transaction Logs in the Cloud

It is a common practice to place the transaction logs (*.ldf) on a separate physical disk system than database files (*.mdf, *.ndf). In a virtual machine cloud environment, is it okay to put the ...
3
votes
2answers
192 views

SQL Server transaction log - shrinking and reattaching

I have the next scenario in SQL Server 2008: one big database (production DB). I want to backup this db then restore it on another server. Then I do some delete operation from some big tables. After ...
0
votes
2answers
374 views

Restore to a point between two full SQL Server backups

I have two SQL Server database backups from Server1. Backup1 was made on, say, 2013-01-01 and Backup2 was made on 2013-01-03. What I want to do is restore Server2 to the intermediate state on ...
0
votes
2answers
6k views

How to disable ldf file or limit its size [duplicate]

Possible Duplicate: Why Does the Transaction Log Keep Growing or Run Out of Space? My .ldf file size keeps increasing. I already tried to shrink it but after that it just grows again. How ...
1
vote
1answer
1k views

SQL Server - how transactions and transaction log work (simplified)

I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Let's consider we have a database with full recovery model. Now, everything ...
1
vote
3answers
511 views

Point in time recovery from the same database - what does it mean?

I have two questions. I have a database in full recovery mode. Lets call transaction log an LDF file for short. SQL Server allows me to restore to point in time in the past when restoring from ...
3
votes
1answer
938 views

SQL Server - contents of transaction log file in more detail

I have a question regarding transaction log (let’s just call it LDF for short) contents. I am assuming a database with full recovery model. I have read that LDF file contains (logs) each and every ...