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

3
votes
0answers
36 views

The transaction log for database 'database' is full due to 'LOG_BACKUP'. Nothing works

I have about 30 databases on SQL server 2012. One of the databases is erroring with "The transaction log for database 'database' is full due to 'LOG_BACKUP'." I have spent several hours googling and ...
2
votes
2answers
177 views

How does the transaction log record a transaction?

We all know what SQL Server's transaction log records information about the transaction. But what, in a full recovery model, is actually recorded in the transaction log? You'll say: THE TRANSACTIONS! ...
0
votes
2answers
62 views

How many log files does a database usually have? [duplicate]

I'm studying SQL Server. I have read that a database can have several data files and one log file, but recently I was heard that a database can have more than one log file. Is this true?
2
votes
1answer
26 views

High Log Space Used for model System database

I was running DBCC SQLPERF(LOGSPACE) and noticed that while my user databases have nice, low Log Space Used percentages, my model system database is currently at 98%. I have it set to the default Full ...
-7
votes
1answer
136 views

How can I intentionally grow my transaction log to 500GB for testing purposes? [closed]

I have been tasked with creating a 500GB transaction log with the intention of developing an automated transaction-log shrinker. I need to be able to record the time take for this task. How can I ...
0
votes
1answer
30 views

Reduce the allocated size of a DB file [duplicate]

Is it possible to reduce the initial log-file size of a DB. See the screen-shoot below, the log-file shouldn't be more than 500 MB with an autogrowth of 100 MB. I dont want to shrink the file, i want ...
3
votes
1answer
29 views

Redefine Transaction log size restricion fails

I am trying to set the transaction log's max size restriction to UNRESTRICTED using SSMS using the following dialogue: The actual setting is set to 2.097.152 MB - a nonsense value that exceeds the ...
4
votes
0answers
32 views

Why isn't transaction log free space always reused?

After monitoring the Transaction log files for a while I noticed that some of them sometimes behave in a way I did not expect. Here are two samples for comparison. All data is based on EXEC('DBCC ...
0
votes
0answers
10 views

Any way to view the list of commited transactions in a period of time?

I want to view the list of commited transactions to my oracle database that users have issued today for exemple. Is that possible ?
6
votes
1answer
106 views

Is it important to make transaction log backups? [closed]

I don't have huge experience with SQL Server, I'm just studying it, and I have a question: Is it important to make transaction log backups? My database is not big, about 2GB, and I prefer a full ...
1
vote
1answer
33 views

Get Table Name from Transaction Log query

I can't figure out why this join isn't working to return the table name when querying the transaction log file. SELECT [Transaction ID], [Current LSN], PartitionId, sp.object_id, ...
3
votes
0answers
52 views

LOG BACKUP is TRUNCATING in Log Shipping

I had configured Log shipping from INSTANCE1 to INSTANCE2. For the past few days, am able to find that the log backup is truncating only at the midnight. Due to this, LSRestore Job in INSTANCE2 got ...
3
votes
1answer
43 views

How is an Active Log different from MinLSN?

MinLSN is the LSN of the oldest uncommitted transaction: What exactly does Active Log hold? Isn't it supposed to hold all the uncommitted transactions? Does it hold any other log records which are ...
4
votes
1answer
4k views

Disable SQL Server Log Shipping to nonexistent destination

I am administering a SQL Server 2008 instance in a dev environment. At some point before I became an administrator, someone enabled log shipping from one of the databases on this dev instance to ...
4
votes
3answers
224 views

How to find what frequent and/or large transaction have taken place in MS SQL Server?

Each day is experiencing about 1GB sum total of all the transaction log backups that day, while at the end of that week the Full DB backup is about 180MB. For a business database for buying/selling ...
1
vote
1answer
24 views

How does a transaction log backup deal with active log?

What happens to the log records that maintain any open uncommitted transactions when a transaction log backup starts? Will these records also be included in the log backup?
1
vote
0answers
40 views

SQL Server 2012 - log shipping issue

I'm sort of new to SQL Server so bear if the doubts are silly. I have configured log shipping for my DR purpose. SQL Server 2012 is the version. Today I encountered the following error in my log ...
0
votes
1answer
165 views

DB2 big strange problem - database is returned to CIRCULAR mode but archive logs keep coming to old archive location

my DB2 database (v 9.7) is in Circular mode. Current Logging Type:Circular (USEREXIT) = OFF (LOGRETAIN) = OFF (LOGARCHMETH1) = OFF (LOGARCHMETH2) = OFF (LOGPRIMARY) = 10 (LOGSECOND) = 10 Path to log ...
5
votes
1answer
331 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
2answers
2k 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 ...
2
votes
1answer
66 views

Change recovery mode and enforce a transaction log backup?

I am going to do a special data deletion operation - once and during a maintenance with no user access (Sql 2008 r2). The result is a large deletion of millions of blob data from a table by ...
1
vote
2answers
533 views

MySQL InnoDB log files on different location

I am using MySQL version: 5.5.38 There are following parameters that can control InnoDB log files: innodb_log_files_in_group - amount of log files in the group, by default 2. Will create 2 files ...
6
votes
1answer
219 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 ...
1
vote
1answer
62 views

Which TLogs are causing large block change in SAN snap?

The environment is SQL Server 2014 Enterprise Edition, NetApp storage managed by CommVault Simpana. We have 100 dbs setup on an instance, all TLogs are on the same Volume. The SAN Admin has setup ...
0
votes
2answers
228 views

shrink database in always on availability

hi i was using this query for shrinking the database log file USE Tfs_CoE; GO ALTER DATABASE Tfs_CoE SET RECOVERY SIMPLE; GO DBCC SHRINKFILE (Tfs_CoE_Log, 1); GO ALTER DATABASE Tfs_CoE SET RECOVERY ...
40
votes
5answers
26k 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 ...
0
votes
0answers
25 views

Does Postgresql have a transaction log?

My question is pretty simple; does postgresql have something similar to the transaction log in SQL Server? If so, how do you access it to truncate dead transactions, or set what size it should be? ...
0
votes
0answers
11 views

Do all types NoSQL databases have a transaction log?

Do all types NoSQL databases have a transaction log? It seems to me that there should be at least one type that doesn't but I don't know which one.
0
votes
5answers
223 views

I do not understand SQL Server Transaction Logs

I understand that a Microsoft SQL Server Database has two components: A database file A transaction log file Whenever I make a change to a table, does it get written to both the database file AND ...
1
vote
1answer
25 views

Why is Log File Auto Grow sometimes performed by my application user and sometimes by sa?

Many database tasks (including Data File Auto Grow, Log File Auto Shrink, and Data File Auto Shrink) are completed only by the sa user, but Log File Auto Grow seems to be associated with both sa and ...
3
votes
1answer
143 views

What is the best storing device for DB transaction log?

I'm using MySQL with InnoDB storing engine. Transactional log is placed to dedicated HDD. Still as it is HDD atopsar shows big load (>50%) to this HDD because of big amount of small IOs (about 50 ...
0
votes
1answer
173 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
11k 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 ...
-1
votes
2answers
62 views

Database BackupSize has Changed after migration from SQL Server 2012 Exterprise to SQL Server 2012 Express [closed]

I would like to say that after migration of MS SQL server 2012 Enterprise to MS SQL Server 2012 Express. I have seen there is some subtle diffence in DB backupsize as well as in "LSN" of all ...
1
vote
1answer
443 views

Mysql: binary log vs transaction log

In mysql with innodb, there are two types of logs: binary log and transaction log. I know transaction log is specific to innodb. But when comes to replication, backup and crash recover, what roles ...
2
votes
1answer
88 views

Can I truncate log in simple recovery mode? [duplicate]

I have a few SQL Server 2005 databases in simple recovery mode. The disk volume in which my transaction logs are stored had only 1GB free, when I started a stored procedure (massive delete in a single ...
2
votes
1answer
63 views

SQL Server 2008 - SIMPLE database log file is HUGE relative to the actual DB?

So... I'm not a SQL Server DBA but was asked to look at a customer's situation where they have a 2GB database that has a 60GB transaction log. I've read up on a whole lot of articles on the web (Aaron ...
1
vote
0answers
10 views

how to clear the postgrsql-9.1-main-log

Is there an easy way to clear the logfile of postgresql? the logfile is 6044 MB, can you use cp /dev/null file-name or do you mess up the database?
2
votes
0answers
46 views

Shrinking a physical transaction log file on a mirrored database

I have 2 SQL Server 2008 Standard Edition instances that are mirrored. Both instances are running Service Pack 1. I run Full backups daily and transaction log backups every half hour (through ...
7
votes
1answer
363 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 ...
17
votes
3answers
2k 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 ...
3
votes
0answers
88 views

SQL Server 2014 log file very large [duplicate]

I have a database with a memory optimized table and table partitions. The log file is over 300GB in one .ldf file, and another 40GB in another. I have tried shrinking the db, files, log DBCC ...
2
votes
1answer
24 views

Detail of all transactions on a specific row

I've a User table and It's data can be added/ updated from a User Screen. I want detail of all transactions on any row. For example, I have data in table like this Name - Age - Salary ...
4
votes
2answers
120 views

MSDB log file size

We have a SQL Server 2008 cluster with 60 databases. Full backup runs every night, log backup runs every 15 minutes. The MSDB database recovery model is set to simple (default). To be able to see ...
2
votes
1answer
3k 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 ...
0
votes
0answers
26 views

How to shrink the .ldf(log) file of the particular live production database? [duplicate]

I would like to say that as one of my production database (.ldf) log file is growing larger than databse size. The size of database file (.mdf) and their log file (.ldf) is as follows:- 1)database ...
1
vote
2answers
3k views

When is it safe to disable InnoDB doublewrite buffering?

MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked. How could InnoDB still be able to maintain ...
3
votes
1answer
93 views

Misaligned Log IO Issues and Unmatched LUN sector sizes in AlwaysOn

I'm running into a little bit of a snag on a high log send queue in a 2014 SP1 Asynchronous Mode AlwaysOn Environment with a secondary replica in Azure. For one of our higher transaction volume DB, ...
7
votes
3answers
10k 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 ...
118
votes
4answers
110k 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 ...