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
467 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
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 ...
5
votes
1answer
286 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 ...
2
votes
1answer
114 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 ...
0
votes
2answers
1k views

SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs

After doing some research I cannot seem to find an answer to this question. Background I am attempting to setup a backup plan that fits the following three requirements: Reliability of backups, ...
5
votes
1answer
349 views

SQL availability groups log maintenance

I have a AG with 2 nodes in a site in sync mode and 1 offsite in async mode. I'm having trouble understanding how the logspace is maintained in this scenario. For instance, I'm taking log backups ...
0
votes
0answers
34 views

Performing a Transaction Log Backup on a Live Database [duplicate]

I have a server 2012 database that has a transaction log that is growing at a rapid rate. i have a job that runs once a day after production hours which backs up the log file and shrinks it. The ...
1
vote
1answer
129 views

Online backups with VENDOR library: what should be set for ACTIVE LOG PATH?

My customer is using Veritas NetBackup so we use its library file in the LOGARCHMETH1 parameter for the VENDOR option. That is for archive logging path. What path should be set for ACTIVE LOG PATH ...
3
votes
2answers
209 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 ...
0
votes
2answers
324 views

Database not naturally shrinking log file [duplicate]

I have a database that is set to Simple recovery mode. There is 99% free space in the log file and I can manually truncate the log file right down to 500 MB. The next day, after a daily import ...
1
vote
2answers
121 views

Who changed the size of the transaction log?

Can I know, who and when changed size of database transation log?
5
votes
1answer
397 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 ...
6
votes
2answers
505 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 ...
0
votes
2answers
520 views

Restore to a point between two full SQL Server backups

I have two SQL Server database backups from Server1. Backup1 was made on, say, 2013-01-01 and Backup2 was made on 2013-01-03. What I want to do is restore Server2 to the intermediate state on ...
0
votes
2answers
9k 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 ...
1
vote
1answer
2k 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 ...
1
vote
3answers
601 views

Point in time recovery from the same database - what does it mean?

I have two questions. I have a database in full recovery mode. Lets call transaction log an LDF file for short. SQL Server allows me to restore to point in time in the past when restoring from ...
1
vote
1answer
724 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 ...
5
votes
3answers
2k views

SQL Server virtual log size

I'm somewhat of an accidental DBA, being a developer who inherited a couple of database servers (2005 and 2008) from someone who knew little about database administration, and seemingly had even less ...
2
votes
2answers
146 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
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
1answer
174 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 ...
5
votes
2answers
757 views

SQL Server 2008 transaction log full

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` ...
0
votes
2answers
174 views

SQL Server transaction log [duplicate]

Possible Duplicate: Why Does the Transaction Log Keep Growing or Run Out of Space? Is it possible to have SQL Server 2008 R2 reset the transaction log for certain databases when the ...
1
vote
1answer
2k views

Why does my transaction log use so much space?

I have a staging table with 20 million rows that uses about 8GB of storage. After populating foreign keys and sanitizing data I need to move this data into a production table (which does not need all ...
1
vote
1answer
588 views

In Sybase, what can I do to figure out why my Transaction Log is filling up?

We have a process that runs each morning to load about 40,000 rows from one server to another. In the past few weeks, the process has been having a 'log suspend' once or twice a week. This is ...
5
votes
3answers
1k views

Disabling logging on certain tables

I'm using SQL Server 2005. I have two tables that contain aggregate information. The information is constantly being updated, generating almost 5GB of log data a day. (That's larger than the entire ...
2
votes
3answers
254 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 ...
5
votes
2answers
668 views

How do I tell what is filling up the transaction log?

We have a transaction log on a sql database and for some reason it has filled up to 50GB when our live system only has about 2GB. Is there any way I can profile sql or the transaction log to try and ...
0
votes
1answer
118 views

How are transactions logs inserted?

I have an ms-access database at work that has a table that's a transaction log. The table contains the following fields: Date - The date the transaction took place. Table_Name - Name of the ...
5
votes
1answer
178 views

Can We Perform an “Up-To-The-Minute Restore” After Taking Transaction Log Backups?

The Setup: Say we have a Database in Full Recovery Mode that does a nightly Database Backup, Transaction Log Backup, and Transaction Log Shrink. Now the Transaction Log grows so quickly during the ...
6
votes
1answer
2k views

SQL Server transaction log file refuses to grow

Problem: I have a SQL Server database sitting on a 1 TB drive. The log will not grow past 5 MB even when the MAXSIZE is set much larger than 5 MB. Error received: The transaction log for database ...
7
votes
2answers
6k views

View delete statements in the Transaction log

Is it possible to view delete statements that have recently occurred in the Transaction Log?
-3
votes
5answers
9k views

Safely deleting a SQL Server log file (.LDF)

I can make sure my app is in a consistent state. I can rollback all the uncompleted transactions if any (just in case) it's ok I can DETACH the database What do I need the log file for after that? ...
3
votes
1answer
545 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
3answers
684 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 ...
8
votes
3answers
532 views

Is there a performance benefit to placing transaction log files on a separate drive?

There are many blog posts and best practice articles extolling the virtues of placing the SQL Server data file on one hard drive and the transaction log on another. The reason given is that that the ...
5
votes
3answers
451 views

Is it possible to recover day's worth of queries from sql server log file?

I lost all data entered and edited in my system yesterday, and my ISP has announced to me that last night's backup is corrupt and cannot be recovered. Is there any way to access the database's log ...
4
votes
1answer
855 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 ...
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 ...
14
votes
2answers
2k views

SQL Server how to get around the transaction log filling up when updating a column to an int

I have a SQL Server 2005 table called BRITTNEY_SPEARS_MARRIAGES and it has the following columns: MarrigeId tinyint, HusbandName varchar(500), MarrigeLength int Now I have another table ...
4
votes
2answers
2k views

Transaction Log and mirroring - looking for dumbest explanation possible

First of all I must admit that I struggle with concept of Transaction Log. I mean - I do understand that it is Log of all Transactions that happen on database, but when it comes to properly putting it ...
5
votes
1answer
180 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 ...
2
votes
1answer
260 views

Initial recovery Postgres with backup in sync commit mode

I've got a master and a slave postgres 9.1.3 database. I would like to init the backup database for the first time. I did the following steps: Stopped master Copied all files to Slave Create ...
3
votes
2answers
215 views

Will removing a 2nd log file break log shipping?

We have a large database 200GB+ to which we added a second log file to cater for growth during an upgrade. This is now no longer needed so we want to remove it. Normally this would be a simple ALTER ...
2
votes
1answer
470 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 ...
2
votes
1answer
505 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
1answer
249 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 ...
1
vote
3answers
413 views

Big size of database logs SQL Server 2008

I have a database which is running under Microsoft SQL Server 2008. Now, I have seen that the log of the database (the .ldf file) is growing to big size. The database file (.mdf) has a size of 630MB ...
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 ...