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

3
votes
2answers
710 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
3k 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 ...
3
votes
3answers
3k 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 ...
3
votes
2answers
1k 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
718 views

Multiple Transaction Log files and performance impact

So I've gotten into an argument with several people regarding the number of transaction log files a give databases should have. I've seen several post saying you should only have 1 transaction log ...
3
votes
2answers
1k 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
1answer
254 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
88 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
3answers
597 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
4answers
451 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
292 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
506 views

How large will the Transaction Log backup be?

I encountered a production database of 12 GB with a log of 90 GB. Full and differential database backups are made with a third-party backup tool, but no one is backing up the transaction log so it got ...
3
votes
2answers
4k views

How to monitor SQL Server data and log file growth?

I'm trying to find a way to monitor data and log file size from SQL Server databases (within an SQL Server instance). I would not want to use third party tools, but simply pure SQL or PowerShell. I ...
3
votes
1answer
454 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 ...
3
votes
3answers
123 views

Log File Management - Space on Disk

At our organisation we have several non-Production environments where Developers run free and wild with TSQL code and databases. The DBA team do not normally monitor or maintain them. Recently several ...
3
votes
1answer
630 views

How to troubleshoot db/app latency during transaction log backup

I have an application that uses a SQL server (2008 R2) database and we are periodically having performance problems with the application and it coincides with our 15-minute transaction log backups. ...
3
votes
2answers
1k views

Is this procedure to Failover to Logshipped secondary and Fail back again procedure correct?

I have a primary database in Amsterdam (AMDB), and a secondary in London (LNDB). I want to fail over from AMDB, to LNDB and then back again, without having to make another full backup (because the ...
3
votes
1answer
52 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. ...
3
votes
2answers
374 views

Missing Transaction log in chain, Possible to skip

We have had one of our clients SQL Servers crash and need to restore it to the most recent possible time. We have a full backup from Monday and transaction logs that run every hour up until this ...
3
votes
1answer
48 views

Implement Log Shipping in prod env

We have a 3.5 TB DB which is already involved in log shipping to two different servers, as one will be in restore sate only and one will be in stand by mode which helps for reporting needs. All three ...
3
votes
2answers
423 views

Time to apply transaction logs: does it matter how many logs?

When restoring from a backup in SQL Server, the procedure is to restore the .bak file and then apply any .trn files since the last full backup. Does it make a difference how many .trn files there ...
3
votes
2answers
193 views

SQL Server transaction log - shrinking and reattaching

I have the next scenario in SQL Server 2008: one big database (production DB). I want to backup this db then restore it on another server. Then I do some delete operation from some big tables. After ...
3
votes
1answer
516 views

PostgreSQL WAL Archiving: Does having archive_mode set to 'on' without copying logs cause performance issues?

Using PostgreSQL 8.4/CentOS 6: if I set the archive_mode parameter to on in postgresql.conf, but don't actually store the generated WAL files anywhere via the archive_command parameter, is there any ...
3
votes
0answers
150 views

Why are we suddenly getting time-outs on multiple transactions? [closed]

Two days ago the transaction log filled up and my user's couldn't update or insert. One of my techs truncated the log, shrank the db, then did a full backup. The next day (Monday) we noticed that ...
3
votes
1answer
420 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 ...
2
votes
1answer
172 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 ...
2
votes
3answers
2k views

ASPState Log file growing tremendously - SQL Server 2008

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Web Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) I have an ASPState database online, and its log file ASPState_log is growing ...
2
votes
2answers
114 views

Transaction Log Backups

I am working on backup+recovery scenario and I came across a situation where I could not figure out how I would have to deal with it. We have couple databases where sometimes there may not be any ...
2
votes
2answers
134 views

Large number of VLF and shrinking the log

If there are lot number of VLF then it will effect the performance of the queries. I saw that solution is to shrink the log file. But shrinking the file is also bad. So how to take control of whole ...
2
votes
3answers
237 views

Transaction Log file .ldf is too small

Current database is almost 200GB however the transaction log file is only 4.1GB. I am worried if this influences the performance of databse and how could it be solved. When I do this query DECLARE ...
2
votes
2answers
2k views

SQL Server 2008, switching to simple recovery mode

Currently our transaction log is 50 GB; impressive huh? I want to switch to simple recovery mode and remove the transaction log entirely. Do I need to stop any user activity and detach the database. ...
2
votes
1answer
97 views

SQL Server - Data and Log Separation

We have a SQL Server 2008 R2 which has, among others, a 12K RPM HDD and a 7.2K RPM HDD. The original intention was to store the data files on the 12K drive and the log files on the 7.2K drive, as is ...
2
votes
2answers
1k 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 ...
2
votes
1answer
469 views

Maintenance Plan Takes Too Long - Locks Tables - Indexing To Blame

System: I have a Maintenance Plan that Rebuilds Indexes for Several Tables (10 tables, 50M records total). Issue: During the Index Rebuild (~20 minutes), we fail to insert data into the DB ...
2
votes
2answers
209 views

How to do a one-time log shrink on a database with transactional replication

I have inherited a 10 GB database with an 80 GB log [only 3% in use according to DBCC SQLPERF(logspace)]. It seems safe to assume the extreme log growth was due to problems from long before I was ...
2
votes
2answers
139 views

separating tlog and db file when using fusionio

We have a FusionIO card that we use in our production database. We have both the transaction log as well as the database files on that same drive. I know the recommendation is to place the tlog files ...
2
votes
2answers
376 views

How do you fix Autogrowth being set to 12800%

We're running SQLServer 2005 SP1 and for some weird reason our one database transaction log automatically changed it's Autogrowth to 12800% which isn't even a valid number. I cannot change the growth ...
2
votes
3answers
741 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 ...
2
votes
1answer
125 views

Transaction log autogrowth duration

I am using event notifications to capture data and log file autogrowth events for all databases on my servers. I'm using the data to for analysis of database storage configuration. In looking at the ...
2
votes
1answer
2k 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 ...
2
votes
1answer
40 views

Is tlog backup on replica clearing VLFs on the primary?

Is it true that a transaction log backup made on the secondary database is clearing the inactive VLFs on the tlog file on the primary database (in case of Availability Groups)? I was not able to find ...
2
votes
2answers
76 views

Switching from SIMPLE to FULL recovery mode implications

I have several databases that I'd like to move over to FULL recovery mode so we can have point-in-time restore capability. We don't need it on all of our databases, just the ones that are transaction ...
2
votes
2answers
2k views

Transaction log has grown huge after maintenance plan to rebuild idex

A couple of days ago I was testing some stuff using the Maintenance Plans in SQL Server 2008. I created one to rebuild indexes and take a full backup every week. (I know, bad thing I've found out ...
2
votes
1answer
247 views

Postgres synchronized backup - what's with the archive?

I am setting up a Postgres 9.1 master and a hot standby-server. I read the documentation but I'm not quite sure yet what I should do with my WAL files. Every commit is done on both servers before ...
2
votes
1answer
54 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 ...
2
votes
1answer
119 views

Why write ahead logs in PostgreSQL are generated every second

PostgreSQL version 9.2 generates write ahead log (WAL) every second i.e. 60 WALs are generated in one minute. Is it possible to make them occur less frequently? Is it possible to increase the WAL ...
2
votes
1answer
68 views

Log File over 400gigs…need to bring this database back to life

I'm not a dba so I don't understand the admin side of SQL Server too much. One of our sharepoint instances blew up and our IT staff asked if I could assist bring it back to life. I found backups for ...
2
votes
1answer
100 views

Restoring a database back to a point in time before full backup was made

A problem with one of our database tables arose at the end of last week and I wanted to restore that database to a server in order to retrieve the table. We only have the last backup, and a full set ...
2
votes
2answers
90 views

Searching transaction logs for changes

I currently have a database with an attached CMS. The CMS has about 50+ people with various permission levels that can log in etc... I would like to know if there is a way I can look at the ...
2
votes
1answer
436 views

SQL Server 2005 Percent Log Used Alert fires too fast?

I have a SQL Server 2005 database thats roughly half a terabyte. Third party software that I don't control writes to the database. I have a Maintenance Plan which backs up the transaction log, and ...