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

2
votes
4answers
166 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 ...
4
votes
4answers
12k 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
520 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
26 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
48 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 ...
3
votes
0answers
35 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
99 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
24 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
58 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
24 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
67 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 ...
1
vote
1answer
88 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 ...
4
votes
1answer
62 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
83 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
83 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
93 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
162 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
473 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 ...
5
votes
2answers
10k 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
2answers
119 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 ...
1
vote
3answers
4k 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
54 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
58 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. ...
4
votes
1answer
57 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 ...
5
votes
2answers
2k 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 ...
30
votes
3answers
75k 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.
4
votes
1answer
91 views

transaction across multiple databases - what is the overhead?

when I run the following update, one single transaction across 2 databases: please note I have not committed the transaction. --================================================================ --RUN ...
5
votes
1answer
817 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
128 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 ...
3
votes
1answer
58 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 ...
2
votes
1answer
86 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
69 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
647 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
50 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 ...
2
votes
3answers
1k 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. ...
2
votes
1answer
958 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 ...
9
votes
6answers
39k 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
32 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
278 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
78 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 ...
2
votes
2answers
20k 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 ...
13
votes
2answers
277 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
36 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
879 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 ...
2
votes
2answers
98 views

Disk size and log file growth during upgrades

I have four SQL Server databases. Per the maximum load requirements, each database file can be between 1 and 2 TB in size. The HDD is a multi-disk DAS with about 9TB of space. During a database ...
3
votes
1answer
218 views

How to get the daily amount of committed transactions on a given MSSQL database?

The number of transactions for the current connection can be obtained with @@TRANCOUNT. How can you get a daily counter of the globally committed transactions on a given database?
2
votes
1answer
977 views

IBM DB2 9.7, possible to temporarily disable transaction logs?

I want to clean the whole database, so I am dropping all tables and DB objects in two FOR loops, within one transaction. This makes the transaction logs become full. I could increase the transaction ...
3
votes
3answers
1k views

Restore a large number of transaction logs

I'm using SQL Server 2008 R2, and I need to restore 36 .trn logs to a database. I have restored the first 4 using the restore transaction log functionality in SSMS (right-click database > tasks > ...