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

8
votes
3answers
719 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 ...
3
votes
2answers
241 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
601 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
268 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
533 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 ...
10
votes
1answer
2k views

DBCC CHECKDB firing every 20 - 60 seconds

I have a dev environment that is rapidly approaching deployment into production and have noticed in the logs that roughly every 20 seconds I see the message: Starting up database 'dbname' CHECKDB for ...
3
votes
1answer
649 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
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
5answers
14k 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? ...
0
votes
1answer
828 views

How to manage a large SQL Server database: transaction log, reduce size, alter table

Hello I have some confusion on how to manage a large SQL Server database. Let's say size is 15GB, with 3GB available, in simple recovery mode. Server is not managed by me, I cannot change the size, ...
3
votes
2answers
5k 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
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 ...
2
votes
2answers
3k 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. ...
4
votes
5answers
3k views

Managing Transaction Log Size using a Maintenance Plan [duplicate]

Are there any best practices for configuring a Maintenance Plan in SQL Server 2008? Currently, I'm removing database backups and transaction logs greater than 40 hours old, then backing them up. ...
4
votes
2answers
1k 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 ...
2
votes
4answers
909 views

Does a full transaction log cause a rollback?

Will a transaction that is trying to write something into the transaction log be rolled back if the transaction log is full?
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 ...
3
votes
1answer
3k 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 ...
6
votes
1answer
4k views

Whats the difference between Transaction logs and Binary logs?

Coming from a SQL Server shop, I now work with MySQL and I was curious. What are the differences between MySQL's binary log and MSSQL's transaction log? By the outlook thus far, it seems that there ...
2
votes
5answers
4k views

Keep transaction log size under control during batch processing

tl;dr I think my verbosity has obscured the real question I'm asking here, so I apologise for that. My main issue is that the checkpoint command appeared to be working for at least 800 iterations of ...
1
vote
2answers
2k views

SQL Server 2008 - Log file out of control & cant shrink it [duplicate]

Possible Duplicate: Why Does the Transaction Log Keep Growing or Run Out of Space? I am having trouble with my database, the log files is currently 250GB in size, I need to shrink this down ...
2
votes
2answers
451 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 ...
3
votes
1answer
269 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
4answers
2k views

tempdb in sql server

Using SQL Server 2000, I am getting the following error. The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space." How to solve it? I ...
3
votes
4answers
633 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?