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
1answer
20 views

Necessity of backing up tail log when restoring transaction log?

I have a question about whether or not it is necessary to back the tail log up when performing a restore of a database in Full Recovery mode. I've sprawled for a good while now, and have gotten ...
0
votes
2answers
45 views

Should I shrink the Log File

I am a developer but have to do DBA stuff now and then. We had a problem where the Transaction log files of our DBs where increasing to large size of 50GB while the data file (MDF) was only 10-15 GB. ...
0
votes
2answers
24 views

Transaction Log Backup Cleaning [duplicate]

If you back up your transaction log every hour through a maintenance plan, why does your log file continue to grow? I would think the system would delete or shrink the log after the backup. Is this ...
1
vote
2answers
151 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 ...
6
votes
1answer
818 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
1answer
215 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 ...
2
votes
2answers
20 views

Concurrent backups (full and transaction) and testing recovery model

My intent is to test my recovery model and setup log shipping to replicate a database on another server. I have a database called AdventureWorks on a primary sql server. Presently SQL Backup performs ...
9
votes
6answers
3k views

Transaction Log won't shrink, DB thinks it is replicating

I've got a SQL Server 2008 R2 Express database running Kaspersky Security Center, and I have no idea under what circumstances the install happened, but the database appears to think that it's being ...
2
votes
1answer
15k views

Transaction log full due to log_backup

I am testing how certain operations are logged under different recovery models.Below are steps i have did till now 1.create database in full recovery model 2.take backup 3.create a table and insert ...
0
votes
1answer
210 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 ...
3
votes
2answers
58 views

Applying Transaction Logs to an existing Database

I am wondering if this possible, I can not seem to apply another transaction log. I have restored the Full database backup, the differential backup and my last Transaction Log WITH RECOVERY. If later ...
3
votes
0answers
90 views

Heavy I/O for Microsoft Transaction Log

We have a Microsoft SQL Server (SQL Server 2012 SP3), we've noticed that transaction log I/O is very high. We do have a busy server; we're hosted at Rackspace with SSD block storage. The Logs are are ...
1
vote
1answer
45 views

Log and Data Drive Configuration in VM environment

Anyone have any good feeling on how to arrange the underlying disk configuration for a VM (Hyper-V) implementation of SQL Server vs installing straight up on the hardware? For a native Install, best ...
2
votes
1answer
59 views

What is the cardinality of entity relationship between Employee and Department?

Recently, a friend of mine asked me this question about the cardinality between a Department and Employee entities, I replied many to many, then he, my friend, showed me drawings from the internet ...
1
vote
2answers
84 views

Affect of Shrinking Database before Transaction log backup

I am newbie in the world of Database administration, so excuse my stupid question :) I have the following maintenance jobs scheduled through SQL Server Management Studio : Full DB Backup at 2 am, ...
1
vote
2answers
93 views

MySQL doesn't log queries anymore

Some time ago I updated my MySQL server to the v5.6: SELECT VERSION(); 5.6.30-0ubuntu0.14.04.1 Now the query logging isn't working though the configs in the /etc/mysql/my.cnf seem to be correct: [...
0
votes
0answers
67 views

MySQL: Log only 'select' queries executed on a specific set of tables of a database

I have a Magento site in which I want to log only SELECT queries(after SELECT it could be any column or all columns, in short all SELECT statements) executed on a specific set of tables, also as it's ...
1
vote
2answers
2k views

DB2 ROLLFORWARD option “TO END OF LOGS” - does it read archive logs?

I read about ROLLFORWARD option on link http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001978.html?cp=SSEPGG_9.7.0%2F3-6-2-4-106 but I could not find ...
3
votes
1answer
2k views

How to find the active transactions in VLF?

Is there a way to find the T-SQL statement that is part of the active transaction that is blocking the reuse of the transaction log file VLFs? I am performing tlog backups on every 15 minutes, but ...
5
votes
2answers
3k views

SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs

After doing some research I cannot seem to find an answer to this question. Background I am attempting to setup a backup plan that fits the following three requirements: Reliability of backups, ...
1
vote
1answer
32 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
0answers
68 views

Log file on SQL Server 2014 grown out of control - LOP_SHRINK_NOOP

Server is SQL Server 2014 running in SQL Server 2008 compatability mode. Recovery Model is Full. We have auto shrink turned on (seems this is related to LOP_SHRINK_NOOP) We have about 4.5 million ...
6
votes
1answer
65 views

Best way to backup & truncate transaction logs on a schedule

I'm no DBA, but things being what they are, I have to wear the DBA hat and set up maintenance plans on my SQL Server instance. So for a while I've been having my SSIS overnight process run a Execute ...
142
votes
3answers
135k 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 ...
2
votes
1answer
137 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 ...
-3
votes
1answer
52 views

SQL Server 2008 R2 - Rebuild Log

I'm trying to rebuild a log file for my database. I want to know if what is happening is normal. This is what I'm doing: use master ALTER DATABASE my_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER ...
3
votes
2answers
289 views

What exactly is written to transaction log?

Here's the scenario: DB with Simple recovery model, Roughly 14 GB of data (~80 million rows), INSERT ... SELECT statement into empty table with 7 non-clustered, non-unique, single column indexes and ...
2
votes
1answer
300 views

what are the events recorded in the transaction log?

in regards to what events are recorded in the transaction log , is the below an accurate/comprehensive list? How are things recorded? are the pages that are marked as modified written to the ...
1
vote
1answer
54 views

SQL log file growing exponentially [duplicate]

We have a SQL database on FULL back up with a client and they have NOT implemented a Log file maintenance and noticed that the log file grows exponentially, filling the 20GB they have allocated to it ...
0
votes
0answers
43 views

Shrink Log file in an Always On Availability Group [duplicate]

My LDF file is too big (almost 500 GB) because we had some problems. I know that is not good to shrink files, but we need to do it this time. My database is using the FULL recovery model and is in ...
0
votes
1answer
914 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
69 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 (...
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
50 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
62 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 ...
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
105 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
176 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 ...
1
vote
3answers
637 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 ...
3
votes
4answers
133 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 ...
8
votes
1answer
16k 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
82 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
9k 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 ...
44
votes
5answers
32k 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 data-...
0
votes
1answer
43 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. http://www.sharepointboris.net/2008/10/sharepoint-...
3
votes
1answer
78 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
74 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
19 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
34 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
21 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 -> ...