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

5
votes
3answers
1k views

Virtual Log Files (VLFs) Fragmentation

I have a database with 160 VLFs. How do I determine whether the VLFs are fragmented? Further, how do I find the percentage of fragmentation? Another thing that I want to confirm: Are the following ...
5
votes
1answer
472 views

Why did my transaction log grow so much? [duplicate]

Possible Duplicate: Why Does the Transaction Log Keep Growing or Run Out of Space? About a month ago I migrated my SQL Server 2008 R2 database to a new schema. I ran scripts to migrate the ...
5
votes
1answer
221 views

Does the PCI require to protect the Credit Card Info in the transaction log?

The PCI Data Security Standard https://www.pcisecuritystandards.org/documents/pci_dss_v2.pdf forbid storing Credit Card info in the transaction log 3.2.1 For a sample of system components, examine ...
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. ...
5
votes
1answer
345 views

When REBUILDing indexes on SQL Server, what bearing does tempdb & LOG disk speed have?

Say I have the a data disk that is 50x faster than the LOG and tempdb (measured by Random Write speed) disk. (Don't ask why that's something we'll be fixing if needed) I have a table that's got 19 ...
5
votes
2answers
2k views

Why can't I read my transaction log backup file using fn_dump_dblog?

I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery. After backing up the transaction log, I attempt to read it ...
5
votes
1answer
2k views

Optimising a query against sys.fn_dblog

Looking for assistance improving a query. This query is used to review the last N transactions in the LDF file and then add some useful information like the user, start time, query text, etc.. Here ...
5
votes
1answer
814 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 ...
5
votes
2answers
894 views

SQL Server 2008 transaction log full [duplicate]

We have a large database, with a size of approx 400GB. We are using bulk insert (from upstream system) with the following parameters: `Driver=/IIS/Appl/Server/branded_odbc/lib/VMsqls24.so` ...
5
votes
1answer
316 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 ...
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
2answers
2k views

Oracle 11g see transaction or session start time

I want to know when a session or transaction started. The deadlock file doesn't give me this information. Is there some logfile that keeps these records? I've got a transaction id "TX-1234-abcd", a ...
4
votes
3answers
6k views

What are the most effective techniques to reduce SQL Server disk space use when updating all rows in a very large table?

Given a SQL Server table with a large number of rows no columns with large-value data types multiple indexes more allocated space than available for the largest possible transaction log size a ...
4
votes
2answers
1k views

How can I find databases with a large number of virtual log files?

I have several databases on a production server that are in the hundreds of gigabytes, and have many thousands of transactions running through them on a daily basis. Almost all these databases are ...
4
votes
1answer
118 views

Log file growth issues

We're consolidating data from a bunch of databases into four reporting databases each night. Because the entire dataset is imported each night we do not need to be able to restore the data to a point ...
4
votes
1answer
4k views

Disable SQL Server Log Shipping to nonexistent destination

I am administering a SQL Server 2008 instance in a dev environment. At some point before I became an administrator, someone enabled log shipping from one of the databases on this dev instance to ...
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
1answer
98 views

Is it important to make transaction log backups? [on hold]

I don't have huge experience with SQL Server, I'm just studying it, and I have a question: Is it important to make transaction log backups? My database is not big, about 2GB, and I prefer a full ...
4
votes
1answer
877 views

Does creating / updating statistics have an impact on the transaction log?

Does creating or updating statistics, assuming on a large table, create significant (or at all) transaction log churn? I would think not as it does not change the underlying data or structure. If it ...
4
votes
1answer
1k views

Transaction Log Truncation with Copy Only

I have a database backup job set to use full backup with the Copy only option on once daily. The copy only is on because from what I have read that is the only way to backup a database that is ...
4
votes
2answers
114 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 ...
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 ...
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 ...
4
votes
1answer
186 views

Tempdb change settings and recommendation

In my production database I have below configuration in TempDb and needless to say I am facing a lot of IO issues on the drive . So, as per recommendation I am planning to change it Change #1 ...
4
votes
1answer
219 views

Large Transaction Backup

On my SQL database I run a full backup at 6am and transactional backups every 15 minutes on week days starting at 8am. Every Monday the first transactional backup is 7 Gb. I want to know why, my ...
4
votes
1answer
3k views

What is the differnent between transaction log and redo log in MySQL

I have read about MySQL. In my opinion, I see two log type is very similar. MySQL save how and when data is changed in log. Information is used for recovery of MySQL. I am confusing the function of ...
4
votes
1answer
603 views

Differential/T-Log LSN question

So, this is now my third question on this topic. I just keep finding more and more than i want to know and understand about how FULL recovery works. We transitioned a database to FULL yesterday by ...
4
votes
1answer
182 views

IO request time and less write latency

I am facing issues related to constantly growing log file due to which I am getting error. When I checked SQL Log I found below messages (error log is filled of these messages almost 90%) SQL ...
4
votes
1answer
1k views

Tail Log backup when doing a restore?

Typically when doing a restore of a DB from a production server to a non-production server I will use the WITH REPLACE option as when I forget I get an error about the tail log not being backed up. ...
4
votes
1answer
328 views

Can I decrypt TDE transaction log files for use with 3rd party software?

Is there a way to decrypt TDE log files on disk for use with 3rd party software? I have the original key and certificates. I am trying to use the transaction files with a 3rd party tool in order to ...
4
votes
1answer
158 views

SQL Server 2012 mirror setup - how to reduce transaction log files [duplicate]

We just upgraded our Windows Server 2003\SQL Server 2005 setup to Windows Server 2012/SQL Server 2012. Both configurations have a mirror with NO witness server. On the old setup the transaction logs ...
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 ...
4
votes
3answers
223 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 ...
4
votes
1answer
519 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 ...
3
votes
4answers
259 views

Does it make sense to use full recovery if the data and log files are on the same HD?

We have a database that was set up to use full recovery and my guess is that the reasoning was only to prevent data loss if a failure happened between full backups. We make daily full backups of the ...
3
votes
1answer
187 views

Shrink a large log file for a “Throw Away” database

I have a copy of my prod database on a development server. I am using it to test a deployment script. But when I run it, it says that the log file is full. I checked and it is at 10 GB (Almost the ...
3
votes
2answers
1k views

What is the undo pass that SQL Server does during a RESTORE WITH STANDBY operation?

It's my understanding that SQL Server generates undo files when applying a restore to a database using the RESTORE WITH STANDBY. From the MSDN documentation (Emphasis mine): The standby file is ...
3
votes
2answers
2k views

SQL Server 2008 Query Saved in Logs?

Every time I create a query and run it to create a report I save the query in my Projects folder in case I have to rerun the query in the future or if I have to modify it for any reason but it seems ...
3
votes
3answers
226 views

Question about logging architecture

I understand that when (for example) an update is issued, the data pages are first read into the buffer pool, locks are acquired and changes are written out to the log file in the buffer, then the log ...
3
votes
2answers
2k views

Running out of Transaction Log space during Alter Table

I'm running an alter table, alter column on a table with nearly 30 million rows and SQL Azure fails after approximately 18 minutes saying that The session has been terminated because of excessive ...
3
votes
2answers
2k views

Reducing high VLF count

I have been using Brent Ozar's sp_blitz script to review our database setup and have found that the VLF count is high (13562!) on the primary DB since changing to Full recovery model. I ran a full ...
3
votes
4answers
6k views

What is the purpose of having a table NOT LOGGED INITIALLY

I came across this recently. When defining a table in DB2 LUW (at least 9.5 or higher), you can define it as NOT LOGGED INITIALLY. Example from the book I read: CREATE TABLE products ( productID ...
3
votes
4answers
750 views

SQL Server restore from transaction log

In SQL Server 2005, if you have a database with no backup - but it is running in full recovery mode - is it possible to "roll back" some deletes? If yes - how?
3
votes
1answer
272 views

Does blocking always mean open transaction?

This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation ...
3
votes
1answer
117 views

Storing Tlogs and data files on the same drive

I'm looking for some clarification of various things I've read. I understand it to be best practice to store your transaction logs on a separate drive (spindle) than where you MDFs and NDFs are ...
3
votes
2answers
2k views

Shrinking a SQL Server Log File Multiple Times

Recently ran into a situation where after a log backup and shrinkfile, the LDF file remained the same size. DBCC LOGINFO clearly showed only one active VLF and SSMS said that 99% of the space in the ...
3
votes
3answers
1k views

When are Archived Redo Logs generated?

We have an instance in Oracle 10g. Only one schema is used for a web application (Internet-facing), which is only user for reading information, no adding or modifying data. The rest of schemas are ...
3
votes
2answers
1k views

How to minimize log operations in SQL Server to avoid “log full” error

I have a database in production which constantly get log file filled, its a data warehouse and have many jobs/queries running for obvious. Below is the error which I am getting Msg 9002, Level 17, ...
3
votes
1answer
986 views

How to pinpoint root cause of excessive log file growth

The log file stays pretty small (~500MB) until about 11PM, and at that point it begins growing until no disk space is available. The log file reached it's maximum size at 12:39AM, so the growth took ...
3
votes
2answers
14k 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 ...