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

0
votes
1answer
272 views

DB2 - Archive mode - large number of logs in logarchmeth1 folder

maybe for someone this is begginner's questions. I read lot of documentation and set: `LOGPRIMARY 6 LOGSECOND 4 LOGFILSIZ 4096 logarchmeth1 Disk - C:\archive logpath C:\active` Number of logs ...
1
vote
1answer
216 views

SQL SERVER Virtual Log Files

I am new to SQL SERVER, could anyone please explain that why do we have VLFs' in T-LOG and why not have 1 T-log itself. I am aware that VLFs' are logical but my question is why VLFs'? what is the ...
7
votes
1answer
100 views

TEMPDB Log Files [duplicate]

Do I want to store the tempdb log files on the same drive as my user database log files, the same drive as the tempdb data file, or on a separate drive?
2
votes
1answer
73 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 ...
3
votes
1answer
52 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 ...
-2
votes
1answer
174 views

Why do I need Transaction logs at all [closed]

We use Microsoft SQL Server as a repository of images and data about those images. We record in real time and never care about recovery. If the database slows down and can't handle the number of ...
0
votes
2answers
91 views

Copying a restoring database in SQL Server 2012

I've got two SQL Servers, one of these servers (Server A) is backing up transaction logs on some database and uploading them to the other (Server B). Unfortunately I have no access to Server A, I ...
10
votes
1answer
1k 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
2answers
553 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 ...
1
vote
0answers
306 views

Log shipping on secondary server

Log shipping fails with the below error. Sql agent on secondary server has access to the folder and files in security. It is not a firewall issue. I created the jobs using the LS scripts as it fails ...
3
votes
0answers
170 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 ...
2
votes
2answers
95 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 ...
1
vote
2answers
6k 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 ...
1
vote
1answer
487 views

Securely deleting data from SQL Server logs

Is there a good way to purge data from a particular DB's *.LDF? A little background... I'm a SysAd, not a DBA. I'm running SQL Server 2008 R2 for one of my applications and I'm not particularly ...
1
vote
1answer
144 views

Is it possible to change the drive that Microsoft DPM backs up to?

A while ago our sysops guys created a 15 minute transaction log backup schedule using Microsoft DPM. We often have an issue where there won't be enough space on the Log drive to perform a backup ...
33
votes
5answers
13k views

Why does ALTER COLUMN to NOT NULL cause massive log file growth?

I have a table with 64m rows taking 4.3 GB on disk for its data. Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text. I added a a NULLABLE column with ...
1
vote
1answer
7k views

DB2: The transaction log for the database is full, But there is no 'First active log file'

First, I wish to say that I have very little experience with DB2. However, I've been tasked with tracking down the problem and solution to this problem. This is a DB2 database, and our testers are ...
4
votes
4answers
10k views

Manually set log file size after shrink SQL Server 2008 R2

Am becoming a somewhat involuntary DBA at work at teh moment and really need some help on something. We have a 40GB database in Full Recovery Mode, no log backup configured and a huge log file of ...
0
votes
2answers
87 views

Rebuilding index

I have a transaction database and one of nightly job is to rebuild indexes that followed by a log backup and then shrink the log. Is this safe to do so ? Thanks Antony
-2
votes
2answers
422 views

Can the transaction log in SQL Server 2008 Express edition be viewed in text?

Is it possible to read the transaction log in text/table format, and if so, how can I do that?
1
vote
1answer
118 views

Transaction(s) still alive: How do I find the cause?

I can tell that something is hogging the transaction log on the DB2 for one of our Websphere Commerce installations. Basically I have a burst of transaction log files that are timestamped 5 days ago. ...
2
votes
1answer
1k views

DBCC loginfo contains thousands of VLF entries with status 2

I've inherited this huge SQL Server 2000 prod database and suddenly the transaction log ran out of space. The transaction log gets backed up every hour but I think over the weekend some existing jobs ...
1
vote
1answer
836 views

How to flush SQL Server database from RAM to hard disk?

As part of the nightly maintenance plan, my working MS SQL Server 2012 databases are backup-ed with options of simple recovery and full backup. Transaction log is not backup-ed. Also, at nights the ...
0
votes
2answers
366 views

Is an AWS SQL Server RDS “log capture” the same as a transaction log backup?

When an SQL Server RDS instance provided by Amazon is configured for a recovery model using Full transaction logging, will the transaction log be trimmed by the "transaction log capture" that Amazon ...
0
votes
1answer
202 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 ...
6
votes
2answers
432 views

Two Different Sources Taking Transaction Log Backups

I have inherited a database server with SQL Server 2005. The databases have full recovery mode with hourly transaction log backups. There are two sources taking full backups overnight and hourly ...
2
votes
1answer
140 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 ...
10
votes
2answers
1k views

SQL Server transaction log backups: test whether tail log follows last known log backup

We are using SQL Server with full recovery mode. Given a full backup and a series of log backups, we would like to be able to check whether the log chain is complete from the last full backup to the ...
2
votes
2answers
159 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
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 ...
3
votes
1answer
810 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. ...
5
votes
1answer
213 views

My log file is large, what should I do? [duplicate]

My transaction log is larger than I'd like it to be, and I want to know how I can fix that.
9
votes
1answer
890 views

How important are transaction log VLFs for performance?

How important are VLFs when considering database performance? What would describe an optimal situation for VLFs?
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 ...
1
vote
2answers
1k 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
3answers
4k 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 ...
-2
votes
1answer
425 views

Restore to last transaction log restore

We have a log shipping setup where we replicate from our primary database to a secondary. Now that database is, by the way of log shipping, in standby and thus read-only. We set it up so we could ...
1
vote
1answer
434 views

Sneaky rollback in SQL Server 2008 R2

Is it possible for an entire set of committed transactions to be rolled back in SQL Server 2008 R2 without any notification or logging anywhere? We had a set of data apparently get rolled back this ...
0
votes
1answer
123 views

SQL Server automatic transaction log truncation

Have looked through msdn but cant find answers to these: Any help much appreciated: In simple recovery mode: Is there any TSQL or alternative functionality that will provide a notification either ...
0
votes
2answers
275 views

Shrink transaction log while transaction in progress

I have a several-GB import into my SQL Server 2005 (Standard Edition) database. The import is separated into 4 parts, each part in its own transaction. As you can guess, the transaction log files ...
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 ...
0
votes
0answers
21 views

SQL Server 2008 log file growing and wont shrink. Hard Drive Space running out [duplicate]

I have a SQL Server 2008 database which has a log file (.ldf) with a size of 360GB and growing every day. The .mdf file is only 25GB. The hard drive is 950 GB and has 150GB free space. I want to ...
3
votes
2answers
590 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 ...
4
votes
1answer
196 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
95 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 ...
3
votes
1answer
542 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 ...
6
votes
1answer
485 views

When performing a cross-database transaction, in which transaction log(s) is the information stored?

Given the following snippet: -- error checking omitted for brevity begin tran exec database1..my_stored_procedure exec database2..my_other_stored_procedure if (@@error <> 0) rollback ...
6
votes
1answer
3k views

Can I shrink the transaction log file on a mirror database?

This is a followup question to a previous question about why I couldn't shrink the log file on the principal database. To make a long story short, I setup database mirroring but forgot to make sure ...
5
votes
1answer
2k views

How do I shrink the physical Transaction Log file when it's the principal in a mirror?

We setup database mirroring over the weekend, and forgot to re-enable the job that backs up the transaction logs. When I came in this morning, the transaction log had ballooned to 58GB, and was taking ...
3
votes
2answers
463 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 ...