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

0
votes
0answers
18 views

Database logs that contain attack traces [on hold]

Is there a publicly available logs of database queries (could be any SQL or NOSQL) that contains attack traces ( attacks could be exfiltration, misuse, insider, anomaly or any attacks). Needed for ...
0
votes
1answer
733 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 ...
5
votes
1answer
63 views

Index creation leads to large transaction log growth

I have an approximately 1 billion row table. I then create an index as follows: CREATE NONCLUSTERED INDEX [IX_Index1] ON [dbo].[MyTable]( [CustomerID] ASC ) -- CustomerID is uniqueidentifier WITH ...
5
votes
1answer
599 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 ...
0
votes
0answers
25 views

Secondary database log file in alwayson is very big [duplicate]

I have an alwayson environment (AlwaysOn AG). Database [UsersDb] in the slave server, His log file is very big . How can I reduce his size(shrink)?
1
vote
1answer
40 views

Create a stored-procedure to insert multiple rows on a junction table and other tables

I am new to Databases and Design and needs some help on a INSERT issue. I want to create a procedure that will insert multiple values to different tables, I need to make sure it stays atomic so I need ...
3
votes
0answers
56 views

Influence of index rebuild tasks in tempdb on the transaction log?

I have read pretty much about index maintenance and all the pros/cons of reorg, rebuild, online, offline, TEMPDB and timing and right now I am kind of more confused than ever before... The goal is to ...
132
votes
3answers
124k 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 ...
6
votes
3answers
12k 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 ...
1
vote
2answers
71 views

Reclaim Transaction Log space of SQL Server

I have a SQL Server databases for which log files have grown huge. I have log backup scheduled to run only once a day. But now all the disk space is used by these log files. Can anyone help me ...
12
votes
3answers
151 views

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
194 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
468 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
199 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
122 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 ...
6
votes
1answer
14k 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
69 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
35 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. ...
3
votes
1answer
65 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
60 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 ...
1
vote
0answers
18 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
32 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
46 views
1
vote
1answer
256 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
49 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
70 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
74 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
100 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
479 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
17 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
226 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
41 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
16k 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
27 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
95 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
64 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
53 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
32 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
251 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 ...