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

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 ...
17
votes
3answers
3k 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
180 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
322 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
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
275 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
356 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 ...
5
votes
4answers
16k views

Difference between Full backup and Copy-only full backup

I saw in this link that full backup does not truncate the log. So what is the difference between full backup and copy-only full backup? For the log backup there is copy-only backup which prevent the ...
4
votes
1answer
540 views

Issue shrinking dev sql server DB's log file [duplicate]

I have a development db that's running on sql 2014 in 2012 compatibility that I'm having an issue with trying to shrink the log file. The mdf is 60+gb and the log is 80+gb but since it's dev like ...
1
vote
1answer
40 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 ...
1
vote
1answer
49 views

What transaction logs are for

I'm confused about tranasaction logs for SQL Server. I was under the impression they can be used to roll the database forward. For example say I made a software release on Sunday, I added three new ...
0
votes
1answer
208 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 ...
0
votes
1answer
92 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 ...
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:...
4
votes
1answer
78 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 ...
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
2answers
123 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
461 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 ...
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 ...
1
vote
1answer
588 views

CHECKPOINT prevents database drop

I'm working in the following scenario: I'm creating an script that recreates an entire database. The script involves destruction an recreation of the database and all its objects, as well as a great ...
6
votes
2answers
13k views

TempDB Log Space and ACTIVE_TRANSACTION

Our monitoring solution (SCOM) is currently flagging that the tempdb log is running out of space. However we have auto grow set to 1GB chunks for the log and we have 25GB of space left on the drive. ...
1
vote
3answers
7k views

SQL Server 2008 R2 Log File Location

One of the databases I am going to support does not have a validate log file location. The drive which the log file is pointing to does not exist. But I don't see any error in database error log ...
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
152 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. ...
6
votes
2answers
4k views

Transaction log is full due to 'CHECKPOINT' for DB in simple recovery model

For one of our DB's on SQL server 2012 RTM, We could not perform almost any troubleshooting step as the error says: " Transaction log is full due to 'CHECKPOINT" This being a dev database, its ...
37
votes
3answers
88k views

How to identify which query is filling up the tempdb transaction log?

I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
5
votes
1answer
957 views

SQL Server Troubleshooting Replication and Long-Running fn_dblog query

I am trying to get (pull) Transactional Replication working again for a subscriber. Currently, in Replication Monitor I am seeing messages in the Publisher to Distributor tab and the Distributor to ...
1
vote
1answer
181 views

Alter column opreation failed due to disk space in SQL Server

I am attempting to alter a column on a specific table; however the process is failing because the log file runs out of space once the disk runs out of space. The size of the table is less than 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
114 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 ...
1
vote
1answer
732 views

Cannot shrink transaction log [duplicate]

Bit of a headscratcher here. I have a db that has less than 1GB of data, but a 40GB log file. Transaction logs are backed up daily, and there is not a lot of activity on this database; approximately ...
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%...
2
votes
3answers
5k views

How can I reduce the Initial Size of a LOG file?

I've inherited a SQL Server 2008r2 database; the Data file was created with an initial size of 1,000 mb, but the Transaction Log has an initial size of 28,016 mb. No backups were being performed. I'...
2
votes
1answer
1k views

Msg 5145: Autogrow of database log file took 1010871 milliseconds

In the past, I had an update query that ran for 7 days. I couldn't find a solution, so I created a new database with indexes. The update query has now been running for 26 hours. I am seeing messages ...
2
votes
3answers
1k views

Changing a column's data type fills the transaction log

Running this code: ALTER TABLE npidata ALTER COLUMN npi varchar(20) Gives this error: Msg 9002, Level 17, State 4, Line 2 The transaction log for database 'SalesDWH' is full. To find out why ...
10
votes
6answers
51k views

Shrinking the log file does not reduce size

I have a database which had mdf size of 350 MB and ldf size 4.9 GB When i try to shrink the log file it's not shrinking. I know shrinking a database is not good and it should not be done. But still i ...
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
371 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 ...
6
votes
1answer
82 views

Size of individual transactions in log file

is there a way to find out the size of transactions from the transaction log by looking at the log file in SQL Server 2008? I do have an unexpected bloat of my logfile from some maintenance jobs which ...
4
votes
2answers
23k views

How to disable ldf file or limit its size [duplicate]

Possible Duplicate: Why Does the Transaction Log Keep Growing or Run Out of Space? My .ldf file size keeps increasing. I already tried to shrink it but after that it just grows again. How can ...
13
votes
2answers
320 views

How are these two SQL Server rollbacks different?

In SQL Server 2008 R2, how are these two rollbacks different: Run an ALTER statement, for a few minutes, and then hit 'Cancel Executing'. It takes a few minutes to rollback completely. Run the same ...
-1
votes
1answer
48 views

Mysql: How does a rollback operation be performed inside InnoDB

I would like to hear in simple words Please answer me in simple words to the question on the below scenario. ============== Engine: Innodb Trx1 : started and a DML is executed at 10:00 AM and rolled ...
1
vote
1answer
18 views

Optimal time interval for scheduling of Transaction Log Backup

I have a database which is currently of size 50 MB. The database is bound to grow by 10 to 15 MB per hour and the data is important. So, I wanted to ask what should be the optimal time interval in ...
7
votes
2answers
991 views

SQL Server Transaction Logs in the Cloud

It is a common practice to place the transaction logs (*.ldf) on a separate physical disk system than database files (*.mdf, *.ndf). In a virtual machine cloud environment, is it okay to put the ...