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

11
votes
1answer
114 views
+100

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 ...
0
votes
1answer
186 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 ...
1
vote
3answers
419 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 ...
0
votes
1answer
198 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 ...
3
votes
4answers
103 views

Is it enough to have only full backup and transaction log backup only for a good backup plan?

If Full backup is scheduled for every Sunday at 12 pm and differential back up is scheduled for for every 6 hours and transaction log backup is scheduled for every 15 minutes. My problem is what is ...
0
votes
1answer
640 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 ...
6
votes
1answer
13k 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 ...
5
votes
1answer
68 views

Online clustered index rebuild using more log space than expected

I have a table TableA ID PK BIGINT TrxID BIGINT CardBalance INT AccountBalance INT SP_SPACEUSED 'TableA' output: name rows reserved data index_size unused TableA 904054184 ...
4
votes
2answers
8k views

Set simple recovery mode and shrink log files for all user created databases

I hope you can point me in the right direction. I'm not a frequent user of T-SQL, but I did some googleing, and found the script below. I corrected the script a bit. I want the script to: To select ...
42
votes
5answers
30k 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
1answer
27 views

Clear out remnant LDF log files of Sharepoint DB not visible on SBS 2008 Domain Server?

Typical ways for dealing with LDF files that take up a lot of space, recommend these processes to shrink/ eliminate the drive space being occupied. ...
131
votes
3answers
121k 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 ...
3
votes
1answer
59 views

Tempdb log file was growing continuously on SQL Server 2008 R2-SP2. Could a pending insert statement which uses temporary table be a cause?

I just started at my new job as a DBA. All of a sudden, we started getting alerts about disk space. When looked into it, I found that tempdb log file was growing continuously. There was an active ...
0
votes
1answer
57 views

Sql Server log: is 'Backup|Log was backed up' the same as 'BackupDatabase: Log files done'?

One transaction log file is growing and growing for a couple of days and I can't find out why. Now I figured out that either the way the activities are stored in the sql logfile OR the behaviour of ...
5
votes
1answer
531 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 ...
1
vote
0answers
14 views

MySQL quick database transfer between two servers

I am migrating a reasonably large (18GB) database used for a website from one server to another. There are two tables which make up 95% of that size and they do not have to be fully transferred for ...
0
votes
1answer
26 views

SQL Server 2014 log growing just in Navision 2015 just when always on is active

My question is about if anybody can help me to find out why my LOG file growing to more than 2T just when I activate ALWAYS ON in my NAVISION database. I have more databases in my SQL Server 2016 and ...
0
votes
1answer
20 views

How to restore a transaction log

Could you tell me the reason why it is mandatory to restore the full backup with NORECOVERY option before restoring the transaction log? otherwise Transaction log is always grayed (Task -> restore -> ...
0
votes
0answers
36 views

The transaction log for database is full due to 'ACTIVE_TRANSACTION' [duplicate]

We are facing problems related to transaction logs becoming full. The transaction log for database is full due to 'ACTIVE_TRANSACTION' My database logs are becoming full after every 4-5 days ...
0
votes
1answer
33 views
1
vote
1answer
245 views

fn_dblog not showing up deleted table data

I unintentionally deleted 4 rows from a table. I tried to fetch back the deleted data using a stored procedure (got from internet). However, I was able to see my data in the result of stored ...
1
vote
1answer
46 views

Can't do a full backup because the log is full [duplicate]

I have a server with 25GB of free space. The software stopped working because the transaction log is full. I noticed that the database has two logs files; one is 45Gb and the other is 65Gb. I think ...
2
votes
1answer
68 views

backup log larger than log file

Today I noticed that the backup of the log of our database was 18GB but the size of the log file is only 8GB. I used the following command to backup: BACKUP LOG [myDB] TO DISK = ...
2
votes
2answers
67 views

Backup Strategy for Transaction Log. Tail or Standard Log Backup?

We are forced to perform some essential maintenance on one of our production environments. Instructions from the vendor state the recovery model of the database should be set to SIMPLE before the work ...
3
votes
1answer
94 views

Availability Group log_reuse_wait = Replication

We have a SQL 2014 Availability Group with 3 replicas of our primary database - 2 in our main data center and 1 in our DR center. We did a large index rebuild on the primary which obviously needs to ...
1
vote
2answers
374 views

Switching to Simple Recovery - shrinking transaction logs

Standard disclaimer: I am an "involuntary DBA" (a nice phrase I saw in this article) and have done lots and lots and lots and lots and lots of reading on this subject, but am still ...
0
votes
0answers
16 views

3023 : File serialization issue with transaction log backups

I am trying to take a transaction log backup on one of the AO replica servers. It throws an error saying that backup operations must be serialized. I googled this and found that only one backup should ...
1
vote
2answers
71 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 ...
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 ...
2
votes
1answer
56 views

Why can a log backup not be taken without first taking a full backup?

I've created a new database using the FULL recovery model. Now I am trying to take a log backup without first taking a full backup. I'm aware that SQL Server won't allow us to do so, but I want to ...
4
votes
2answers
220 views

BACKUP LOG cannot be performed because there is no current database backup

I have a SQL Server 2005 database using the full recovery model. If I execute the following commands: ALTER DATABASE test SET RECOVERY SIMPLE; ALTER DATABASE test SET RECOVERY FULL; or BACKUP LOG ...
0
votes
1answer
38 views

Will an hourly T-Log backup in a Maintenance plan write a file if there are no changes to a DB?

I have an hourly maintenance plan the runs a T-Log backup. Some of my databases may have every hour represented in the file system, while others have some hours "missing" or even none at all. Is it ...
3
votes
2answers
15k views

How to Fix log_reuse_wait_desc=LOG_BACKUP? LOG Backup not possible

I checked sys.databases to find out which databases need LOG backups. It surprised me that I had quite a few where log_reuse_wait_desc=LOG_BACKUP meaning, the JOB I have set up to run LOG backups on ...
1
vote
1answer
24 views

Transaction Log file size is increasing in my Azure (IAAS) SQL Server 2014 database [duplicate]

Recently we have migrated our application (both web app and database) to Azure (IASS). There we have a VM with SQL Server 2014 installed. Also I have restored all the necessary databases and SQL ...
1
vote
1answer
79 views

What is the role of eager writer?

What is the role of eager writer? How this process works internally in SQL server? What is the major difference between eager writer and lazy writer/checkpoint? My understanding here is, that both ...
1
vote
1answer
53 views

Transaction log rollback broke Log Shipping chain?

Is it correct that performing a transaction log restore to a previous point in time will break the log shipping chain? If this is correct, is there a way to recover from this situation without having ...
1
vote
0answers
50 views

LogFile Size increases by SSIS Package execution

On production server we have 125 GB free space. The SSIS package is doing some delete in Transaction. e.g. Insert 3 columns which are mainly business keys into a staging table and then deleting the ...
1
vote
0answers
19 views

Hsqldb transaction log rollback cause

I'm fairly new to the database world but I had to use an hsqldb-2.0.0 database for a netflow collector software. As the software shows no output even if the client is collecting the flows I ...
1
vote
1answer
58 views

Shrink Transaction Log While Using AlwaysOn Availability Group 2014

I'm running a SQL Server 2014 Availability Group with a single secondary in asynchronous mode. My logs in all the Availability Group databases keep growing and the built-in shrink tool only takes ...
2
votes
0answers
45 views

SQL server database log full for application databases [duplicate]

We have an issue that our application database gets log full because we have a schedule job to write to a flat table for reporting purpose every hour in the application database. I wonder if we should ...
5
votes
1answer
52 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
2answers
31 views

Transaction log backups cleaning

I have a quick question regarding the cleaning of no longer necessary transaction log backups. So, I have a database in FULL recovery mode. Every night, at 10 PM a backup takes place (via maintenance ...
4
votes
3answers
248 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 ...
3
votes
1answer
133 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 ...
1
vote
3answers
4k 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 ...
6
votes
2answers
81 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 ( ...
5
votes
1answer
162 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 ...
2
votes
2answers
200 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
71 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
32 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 ...