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

136
votes
3answers
130k views

Why Does the Transaction Log Keep Growing or Run Out of Space?

This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this: In SQL Server - What are some reasons the ...
43
votes
5answers
31k views

Why does ALTER COLUMN to NOT NULL cause massive log file growth?

I have a table with 64m rows taking 4.3 GB on disk for its data. Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text. I added a a NULLABLE column with data-...
37
votes
3answers
86k 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.
17
votes
3answers
3k views

The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'

I have a question about XTP_CHECKPOINT. I'm using SQL Server 2014. I have a database that is in SIMPLE recovery model mode. It is also being replicated. There are no open transactions. I've run DBCC ...
15
votes
2answers
5k views

SQL Server how to get around the transaction log filling up when updating a column to an int

I have a SQL Server 2005 table called BRITTNEY_SPEARS_MARRIAGES and it has the following columns: MarrigeId tinyint, HusbandName varchar(500), MarrigeLength int Now I have another table ...
13
votes
2answers
315 views

How are these two SQL Server rollbacks different?

In SQL Server 2008 R2, how are these two rollbacks different: Run an ALTER statement, for a few minutes, and then hit 'Cancel Executing'. It takes a few minutes to rollback completely. Run the same ...
12
votes
3answers
166 views

Keeping (Partial) Backups Small when using SQL Server FILESTREAM

I have a database with nearly 1TB of FILESTREAM data that I don't need backed up (if the data was deleted it'd be recreated automatically in a couple of hours, so it just isn't important). Most of the ...
10
votes
2answers
2k 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 ...
10
votes
1answer
3k views

DBCC CHECKDB firing every 20 - 60 seconds

I have a dev environment that is rapidly approaching deployment into production and have noticed in the logs that roughly every 20 seconds I see the message: Starting up database 'dbname' CHECKDB for ...
10
votes
6answers
49k 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 ...
9
votes
2answers
11k views

View delete statements in the Transaction log

Is it possible to view delete statements that have recently occurred in the Transaction Log?
9
votes
1answer
2k 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?
9
votes
2answers
2k views

SQL Server 'Create Database' statement. How to inherit autogrowth settings?

I am using SQL Server 2008 R2 and creating databases through continuous deployment. In our system, the default 1Mb/10% autogrowth settings in SQL Server work badly with our data. Particularly as we ...
8
votes
2answers
18k 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 ...
8
votes
4answers
24k 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 ...
8
votes
2answers
3k 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 ...
8
votes
1answer
15k views

Shrink Transaction Log While Using AlwaysOn Availability Group

We are using AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database. I have read here doing the ...
8
votes
3answers
187 views

Poor cardinality estimate disqualifies INSERT from minimal logging?

Why is the second INSERT statement ~5x slower than the first? From the amount of log data generated, I think that the second is not qualifying for minimal logging. However, the documentation in the ...
8
votes
3answers
892 views

Is there a performance benefit to placing transaction log files on a separate drive?

There are many blog posts and best practice articles extolling the virtues of placing the SQL Server data file on one hard drive and the transaction log on another. The reason given is that that the ...
8
votes
1answer
5k views

Whats the difference between Transaction logs and Binary logs?

Coming from a SQL Server shop, I now work with MySQL and I was curious. What are the differences between MySQL's binary log and MSSQL's transaction log? By the outlook thus far, it seems that there ...
8
votes
5answers
3k 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 ...
7
votes
3answers
15k 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 ...
7
votes
1answer
1k 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 ...
7
votes
1answer
391 views

Is it possible to restore SQL Server DB from transaction log (no MDF)?

We had a SQL Server 2005 server that housed roughly 200 low/no traffic archive databases, and that server was not backed up to any other device. The database files (MDF and LDF) were stored on a RAID ...
7
votes
3answers
2k 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 ...
7
votes
2answers
6k 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 ...
7
votes
2answers
971 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 ...
7
votes
1answer
167 views

TEMPDB Log Files [duplicate]

Do I want to store the tempdb log files on the same drive as my user database log files, the same drive as the tempdb data file, or on a separate drive?
7
votes
1answer
2k views

SQL Server transaction log file refuses to grow

Problem: I have a SQL Server database sitting on a 1 TB drive. The log will not grow past 5 MB even when the MAXSIZE is set much larger than 5 MB. Error received: The transaction log for database ...
6
votes
2answers
750 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 ...
6
votes
3answers
12k 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 ...
6
votes
2answers
101 views

How exactly the row visibility is determined?

In the simplest case, when we insert a new row into a table (and the transaction commits), it will be visible to all subsequent transactions. See xmax being 0 in this example: CREATE TABLE vis ( ...
6
votes
3answers
4k views

Disabling logging on certain tables

I'm using SQL Server 2005. I have two tables that contain aggregate information. The information is constantly being updated, generating almost 5GB of log data a day. (That's larger than the entire ...
6
votes
3answers
3k views

SQL Server virtual log size

I'm somewhat of an accidental DBA, being a developer who inherited a couple of database servers (2005 and 2008) from someone who knew little about database administration, and seemingly had even less ...
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 ...
6
votes
1answer
56 views

Best way to backup & truncate transaction logs on a schedule

I'm no DBA, but things being what they are, I have to wear the DBA hat and set up maintenance plans on my SQL Server instance. So for a while I've been having my SSIS overnight process run a Execute ...
6
votes
1answer
4k views

What is the differnent between transaction log and redo log in MySQL

I have read about MySQL. In my opinion, I see two log type is very similar. MySQL save how and when data is changed in log. Information is used for recovery of MySQL. I am confusing the function of ...
6
votes
4answers
327 views

Only do log backup if necessary in SQL Server

We have transaction log backups taken every few minutes from many databases. Is there a way to determine if there were any committed transactions since the last backup and only then do a log backup? ...
6
votes
1answer
8k 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 ...
6
votes
2answers
8k views

MS SQL Server - Checkpoint process stuck in SLEEP_BPOOL_FLUSH

We seem to have a problem that came out of nowhere. The checkpoint process is stuck in SLEEP_BPOOL_FLUSH state, and not actually reducing the usage in the log file. This never seemed to happen before....
6
votes
2answers
13k 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. ...
6
votes
2answers
3k 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 ...
6
votes
1answer
82 views

Size of individual transactions in log file

is there a way to find out the size of transactions from the transaction log by looking at the log file in SQL Server 2008? I do have an unexpected bloat of my logfile from some maintenance jobs which ...
6
votes
1answer
730 views

Can't restore SQL Server db from full backup, log processing fails, database in 'restoring' state

I am trying to set up a database for development purposes on my PC's local SQL Server Developer Edition 12.0.2000.8. I've got a full database backup and separate transaction-log-only backup files ...
5
votes
4answers
15k 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 ...
5
votes
3answers
3k 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 Plan,...
5
votes
2answers
349 views

SQL Server log file used pecentage does not drop after multiple backups

SQL Server 2005 I ran "dbcc SQLPerf(logspace)", then took two log backups, and ran "dbcc SQLPerf(logspace)" again. No change from 48.55% used, even though "dbcc opentran (foo)" says that there are no ...
5
votes
2answers
3k 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
306 views

Can We Perform an “Up-To-The-Minute Restore” After Taking Transaction Log Backups?

The Setup: Say we have a Database in Full Recovery Mode that does a nightly Database Backup, Transaction Log Backup, and Transaction Log Shrink. Now the Transaction Log grows so quickly during the ...
5
votes
1answer
226 views

Run Transaction Log backups only during Work hours?

Currently we have a number of databases that are in full recovery model. This is a conscious decision. We also run the transaction log backups every 15 minutes. But we run the transaction log ...