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

62
votes
3answers
51k 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 ...
33
votes
5answers
11k 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 ...
6
votes
2answers
2k 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 ...
5
votes
3answers
2k 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 ...
4
votes
2answers
825 views

How can I find databases with a large number of virtual log files?

I have several databases on a production server that are in the hundreds of gigabytes, and have many thousands of transactions running through them on a daily basis. Almost all these databases are ...
9
votes
1answer
744 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?
4
votes
1answer
776 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 ...
3
votes
2answers
424 views

Missing Transaction log in chain, Possible to skip

We have had one of our clients SQL Servers crash and need to restore it to the most recent possible time. We have a full backup from Monday and transaction logs that run every hour up until this ...
2
votes
2answers
1k 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 ...
1
vote
3answers
571 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 ...
0
votes
0answers
39 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 ...
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 ...
5
votes
2answers
1k 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 ...
2
votes
3answers
898 views

What are the most effective techniques to reduce SQL Server disk space use when updating all rows in a very large table?

Given a SQL Server table with a large number of rows no columns with large-value data types multiple indexes more allocated space than available for the largest possible transaction log size a ...
1
vote
1answer
293 views

Cannot shrink transaction log

Bit of a headscratcher here. I have a db that has less than 1GB of data, but a 40GB log file. Transaction logs are backed up daily, and there is not a lot of activity on this database; approximately ...
0
votes
2answers
168 views

SQL Server transaction log [duplicate]

Possible Duplicate: Why Does the Transaction Log Keep Growing or Run Out of Space? Is it possible to have SQL Server 2008 R2 reset the transaction log for certain databases when the ...