Questions tagged [transaction-log]

transaction log is a log file of all actions performed on a database. It helps keep consistency and recover from unexpected failures.

The Transaction Log is a log file that keeps an audit trail of all actions performed on a database. This allows the database system to recover from unexpected shutdowns like power failures. Usually, the system will roll back changes made by uncommitted transactions and re-apply any committed transaction that did not make it to the database. This is done to keep ensure the atomicity and durability of transactions.

The transaction log can also be used to recover accidentally changed or deleted data.

197 questions
7
votes
1 answer

SQL Server Tempdb LOG file growing

On a SQL Server 2000 system, I have a templog.ldf file that seems to grow without bound! But when I check, there are never any open transaction in the tempdb (using DBCC OPENTRAN), nor do I ever use explicit transactions within tempdb. I do,…
Apt605
  • 144
  • 1
  • 2
  • 10
7
votes
3 answers

SQL Server: How do I increase the size of the transaction log?

How do I increase the size of the transaction log? Is is also possible to temporarily increase the transaction log? Let's say I have the following scenario. I have a Delete operation that's too big for the current transaction log. I wan't to:…
Niels Bosma
  • 11,758
  • 29
  • 89
  • 148
6
votes
4 answers

Log SQL queries in production?

I'm having a dilemma on whether or not to log SQL queries in production as well. I don't know how slow writing files is in PHP. Probably some benchmarks could give some answers, but I wanted to see what you guys think before. What does or does not…
treznik
  • 7,955
  • 13
  • 47
  • 59
5
votes
2 answers

Transaction log shipping together with backup job = conflict?

There is already a backup job plan that runs every 15 minutes. I created transaction log shipping process but after 1 or two restored logs this job stops working (error: it can't find matching log). Obviously, there are logs created by two…
ilija veselica
  • 9,414
  • 39
  • 93
  • 147
5
votes
1 answer

How to get SQL statement from TRANSACTION LOG BACKUP FILE?

I just want to ask regarding transaction logs in SQL Server. We can take backup of those log files in .bak format at our any system location. The problem is to extract SQL statement/query from transaction log backup file. We can do it using…
maddy
  • 248
  • 1
  • 5
  • 16
5
votes
2 answers

How can I view SQL Server 2005 Transaction log file

How can I view the SQL Server Transaction Log File (.trn)? I'd like to be able to look inside the trn file at the individual transactions. Some records were deleted from a database and I need to know when, how and by who. Thanks Neal Edit: I have…
Neal Hudson
  • 231
  • 1
  • 5
  • 17
4
votes
2 answers

Transaction log is full (due to NOTHING)... but this database is in simple recovery mode

I'm supporting an antedeluvian webapp (soon to be retired) that still uses "aspnetdb" for its auth system. I was doing some work in prep for its retirement on my test environment, when I found my test server complaining with the following…
Pxtl
  • 880
  • 8
  • 18
4
votes
2 answers

How do you read the Oracle transaction log

Instead of placing triggers on tables everywhere in an Oracle database, is there a Java API that I can use to read transactions off the Oracle transaction log? My purpose is to be able to detect transactions going into a proprietary(vendor) database…
magius
  • 901
  • 1
  • 9
  • 18
4
votes
1 answer

Are ad-hoc read-only queries stored in SQL Server transaction log?

In SQL Server 2008 with the database recovery model configured to full, are queries such as select col1,col2,col3 from TableName logged to the transaction log files. In other words, can I determine what queries were run on the database on a…
Catch22
  • 3,261
  • 28
  • 34
4
votes
1 answer

SQL Server Log full due to active transaction

I have been trying to update a column in a table and I am getting the below error: The transaction log for database 'STAGING' is full due to 'ACTIVE_TRANSACTION'. I am trying to run the below statement : UPDATE…
Doodle
  • 481
  • 2
  • 7
  • 20
4
votes
1 answer

Does updating a table with same values increase the transaction log file size?

Or does a transaction log contain only differences? Are there differences in the log file between these two scripts: DECLARE @f1 INT , @f2 INT CREATE TABLE #t ( int F1, int f2 ) UPDATE #t SET f1 = ISNULL(@f1, f1) , f2 =…
mordechai
  • 829
  • 1
  • 7
  • 23
4
votes
1 answer

Recovering transaction log from corrupt SQL Server database

We have a database that is backed up weekly in simple mode. Yesterday, we had a crc error corrupt the mdf file and we were unable to save it. I restored the backup from last week, but now we have a gap from the time of the backup to the time of…
Don Kirkham
  • 105
  • 9
4
votes
5 answers

DBCC shrinkfile gives error

I am trying to shrink my log file using DBCC SHRINKFILE(db_2.ldf), which is the name for log file It gives me error every time: 8985, Level 16, State 1, Line 1 Could not locate file 'FIelD' for database db in sys.database_files. The file either…
user70636
  • 1,029
  • 3
  • 12
  • 13
4
votes
2 answers

SQL Server 2005 Transaction Log too big

I am running SQL Server 2005. My db backup scheme is: Recovery model: FULL Backup Type: Full Backup component: Database Backup set will expire: after 0 days Overwrite media: Back up to the existing media set, Append to the existing backup set The db…
RevoJoe
  • 41
  • 4
4
votes
5 answers

Why SQL Server seems to be so attached to transaction logs

OK, here is goes. I’m not a database guru or admin. In fact, besides some occasional index / query tuning, I don’t poke around too often in databases. One of the things which often eludes me is the SQL Server transaction log. I know what it’s for,…
Jan Zich
  • 14,993
  • 18
  • 61
  • 73
1
2
3
13 14