Stack Overflow
Stack Overflow

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
1
vote
2 answers

What happens when the transaction log gets full

What happens when the transaction log gets full during an extensive update? does the transaction gets blocked?
Nelssen
  • 1,023
  • 1
  • 17
  • 42
1
vote
1 answer

Is there an alternate strategy for truncating a large transaction log? (> 200gb)

I am about to attempt one of the other answers for similar questions, but wanted to ask if truncating the transaction log will differ for a log that is 200gb vs a lot of the examples I've seen around 20gb? The other questions take a 20gb…
Grahame A
  • 3,903
  • 12
  • 46
  • 70
1
vote
1 answer

Do SQL Server Transaction Logs get 'locked' when there is a non-zero log_reuse_wait?

Scenario: I have an application attempting to write to a SQL Server DB, and I'm getting the message: The transaction log for database 'DB_NAME' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in…
codeAndStuff
  • 507
  • 6
  • 19
1
vote
0 answers

Why does merging empty partition boundaries generate lots of data in the transaction log?

I have the following partitions: ObjectId | PartNo | Boundary | Rows ----------+--------+---------------------+----- 146099561 | 97 | 2013-10-04 23:59:59 | 0 146099561 | 98 | 2013-10-05 23:59:59 | 0 146099561 | 99 |…
rudimenter
  • 3,242
  • 4
  • 33
  • 46
1
vote
2 answers

SQL Server 2005 failed backup effect on transaction logs

I have an SQL Server 2005 instance whose full backup (.BAK) failed due to low disk space. However half hourly transaction log backups continue (.TRN). Assuming I have an older full backup, could these continuing transaction logs be used to restore…
m.edmondson
  • 30,382
  • 27
  • 123
  • 206
1
vote
1 answer

How to optimize my queries to avoid full transaction log in sql server

I found my database over limit in Godaddy database server and i was told by their support team that this is because the full transaction log After they truncate it as i don't have administrative rights on my shared hosting it got full once more in…
Mariam
  • 533
  • 2
  • 12
  • 22
1
vote
1 answer

SQL Server backup file growing excessively

I am a software developer with not much expertise on SQL Server other than the basics. On a daily basis, using the SQL Server Agent I back up the databases using the following command for each of the databases: BACKUP DATABASE [DatabaseName] TO…
diegol
  • 47
  • 1
  • 6
1
vote
1 answer

SQL Server 2008 log shipping keeps getting out of sync

I have inherited a SQL Server 2008 live server with a hot swap-able backup server which has transaction logs shipped every 15 minutes from multiple production servers. One of the production servers keeps getting out of sync, when I came it the last…
DotN3TDev
  • 138
  • 9
1
vote
1 answer

Transaction log growth 35% at the time of rebuilding index

I have a small database around 3 GB and the index are highly fragmented. There are few index which are 97% fragmented. In order to reduce the fragmentation, I started rebuilding the index. But after rebuilding the index, I find that this activity…
Atul Bansal
  • 151
  • 2
  • 12
1
vote
3 answers

Transaction log growing despite regular transaction log backup

I'm facing a popular issue, i'm afraid : my transaction log is growing and growing again on SQL SERVER. But I can't find the answers on the web. I have a daily full backup, differential backup every hour and transaction log every 15 minutes. And…
Sylvain Pauly
  • 11
  • 4
1
vote
1 answer

Why committing transaction in each iteration of a WHILE loop does not prevent transaction log to grow up?

I am trying to delete millions of records from a database table. I used a WHILE loop in order delete TOP 25000 rows in every iteration with committing them. My assumption was, if I delete portion of rows and commit in every iteration I would prevent…
pencilCake
  • 51,323
  • 85
  • 226
  • 363
1
vote
1 answer

Taking SQL SERVER Database backup without Logs

I have a database "DBName" on SQL Server 2008. I want to take backup of it without logs(.ldf file). Because this log file is around 20 GB and I don't want to increase the size of backup file. I also want to do this without truncating logs from…
Arjun
  • 431
  • 2
  • 8
  • 21
1
vote
2 answers

Sql Server: chunking deletes still fills up transaction log; on fail all deletes are rolled back - why?

Here is my scenario: we have a database, let's call it Logging, with a table that holds records from Log4Net (via MSMQ). The db's recovery mode is set to Simple: we don't care about the transaction logs -- they can roll over. We have a job that…
Paul K
  • 103
  • 1
  • 10
1
vote
1 answer

SQL server 2008R2 The transaction log for database 'MGR' is full due to 'ACTIVE_TRANSACTION'

I run a query in which I wanted to update more then 130 mln of records. After few hours I got an error: The transaction log for database 'MGR' is full due to 'ACTIVE_TRANSACTION'. now I ve got 70 MB free on my C disk drive. I supose that the problem…
gruber
  • 28,739
  • 35
  • 124
  • 216
1
vote
1 answer

SQL Server 2008 transaction log issue

I am learning about SQL Server 2008. I have created a database with full backup mode and made a transaction without commit. In the system database info the log_reuse_wait_desc column is still has the value 'NOTHING'. But the tutorial which I am…
user3649433
  • 21
  • 2
1 2 3
…
13 14