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

1
vote
1answer
86 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
81 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
64 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 ...
2
votes
1answer
82 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
82 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 ...
1
vote
2answers
74 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 ...
5
votes
1answer
227 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 ...
0
votes
0answers
28 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
16 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.
1
vote
1answer
28 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 ...
1
vote
1answer
121 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 ...
-1
votes
2answers
67 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 databases....
2
votes
1answer
92 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 ...
1
vote
0answers
11 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
53 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
392 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 ...
3
votes
1answer
80 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 ...
3
votes
0answers
178 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
316 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 ...
0
votes
0answers
29 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 ...
3
votes
1answer
271 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, ...
1
vote
1answer
80 views

Log shipping 2008 r2 SP2 server to a 2008 r2 SP3 CLUSTER

We have recently configured one database to be replicated with log shipping in production, using SQL Server 2008 R2. We have up and running more than 150 database replicas without any problems. But ...
2
votes
3answers
352 views

Backup database by copying the MDF and LDF files

I want to backup a SQL Server database by copying its files manually. Is copying the database.MDF and database.LDF files enough or should I also add more files? It might be not the proper way for ...
1
vote
1answer
39 views

Corruption in mariadb binlog

I have an installation with mariadb version 10.0.21. I need to backup the binlog file every 5 minutes to TSM, but as the binlog is being written every second, i am afraid of the file getting ...
3
votes
0answers
60 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 ...
0
votes
1answer
205 views

How to automatically shrink a SQL Server transaction log only if it exceeds a certain limit (e.g. 20 GB)

Our database is set to FULL recovery mode and we take a database backup and a log backup every night. On several occasions we have noticed that the transaction log file is not truncated after the log ...
1
vote
1answer
27 views

Keeping all SQL Server logs

I can not find my SQL Server logs for all days in the past. In only found for last 4 days. Is it because that I restarted the server and logs are dumped if the hosting server is restarted? How can I ...
1
vote
1answer
64 views

Really big LOG backups on a DB with Image field

Normally, our log backups has < 10MB size In a big database with a lot of transactions per hour ). But now we have a database, that is storing Images on a Image field. the problem is, the ...
0
votes
1answer
29 views

Index Rebuilding Log File Growth

I would like to start running regular index maintenance on a server that has been running for a while without it. The database is running in FULL recover with log shipping. The problem I am having is ...
4
votes
1answer
76 views

Would SQL in a cluster failover when transaction log for database is full?

I have a Microsoft SQL Cluster with one instance. If one of the databases is full, would SQL in a cluster failover when transaction log for database is full? If it depends on the reason the ...
2
votes
2answers
120 views

Shrinking log files on development

I've been looking at getting some disk space back on some of our development machines. These are machines that are not being backed up, not being developed on directly, and not being maintained ...
2
votes
1answer
109 views

First log backup after FULL taking lots of space

In the backups I'm doing in my database, the first LOG backup I do after the FULL backup is taking too much space, even more than the full backup. For example: 4:50 AM: FULL BACKUP - .bak 7.2GB 6:...
0
votes
1answer
91 views

Log file shrink attempts are futile

I have got a question relating to SQL server log files in a production environment, however what I am trying to do is first run some testing on a smaller Test Database (E10Test) before I move on to ...
4
votes
2answers
92 views

How to estimate rate of Trans Log usage

We have a moderately sized database (about 500GB) that we're going to switch from Simple to Full Recovery, with Trans Log backups every 15 minutes. Before we make the switch, I want to get an estimate ...
2
votes
1answer
456 views

how to reinitialize log shipping?

Here is the situation: DB on Server A had the transaction log shipping configured and uses Server B as secondary. It has been working well for a long time. However, we recently detected that one of ...
2
votes
1answer
77 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 re-...
1
vote
2answers
867 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 confused/concerned.....
0
votes
0answers
103 views

Transactional Replication issue due to big Transaction Log in sql server 2008 r2

I want an answer of question and that would be highly appreciated Scenario: First of all i am an IT guy from Sports Field and using SQL Server 2008R2. I have 2 production servers server 1 and Server ...
2
votes
1answer
146 views

Backing up to Nul on an SQL Mirror

I've seen and read a lot of questions about the transaction log and I know that backing it up to nul is usually frowned upon, however my understanding is that this is from a data recovery perspective. ...
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 ...
8
votes
3answers
189 views

Poor cardinality estimate disqualifies INSERT from minimal logging?

Why is the second INSERT statement ~5x slower than the first? From the amount of log data generated, I think that the second is not qualifying for minimal logging. However, the documentation in the ...
2
votes
1answer
126 views

How is my database being backed up?

I'm using SQL Server 2008 R2. Someone else set everything up. I encountered an error today: Autogrow of file 'ASPState_log' in database 'ASPState' was cancelled by user or timed out after 1748 ...
2
votes
0answers
111 views

Log file Zeroing

During the creation of the database, I am getting below mentioned information about log file zeroing and I have highlighted my understanding in bold fonts. 2015-08-23 12:42:36.850 spid51 ...
4
votes
1answer
115 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
3answers
619 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
0answers
80 views

How to free up disk space on a drive which has BiztalkMsgBoxDB_log file in it

I have been asked to analyze a issue regarding one of the biztalk servers. I was asked to free up space on a particular drive, where I found the only file BiztalkMsgBoxDB_log.bak is taking up close 90%...
6
votes
1answer
800 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 ...
2
votes
2answers
37 views

Random and constant “Log Growths” Alerting, how to make it stop?

I've set up alerting on all my database servers and every once in awhile I'll get the following error, sometimes it goes on and on and on, other times it'll be a few times here and there. The SQL ...
5
votes
2answers
369 views

SQL Server log file used pecentage does not drop after multiple backups

SQL Server 2005 I ran "dbcc SQLPerf(logspace)", then took two log backups, and ran "dbcc SQLPerf(logspace)" again. No change from 48.55% used, even though "dbcc opentran (foo)" says that there are no ...