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
2answers
45 views

Log File Size near 100% - How to find Active Transaction [duplicate]

The transaction log file size is nearing 100% on one database. I run the below and both return 0 so no active transaction running. The last query tells me that the database is in SIMPLE mode, but ...
5
votes
3answers
86 views

Avamar SQL Agent running against live SQL Server databases

We use a large company as our service provider. The tech people from that service company are pushing very hard to install something called the Avamar SQL Agent which purportedly will be used to ...
1
vote
1answer
43 views

Fix transaction log file after database update

I have a databases that are regularly (monthly) updated. An update includes a lot of new or changed T-SQP procedures, functions, indexes creation, etc. A database X has transaction log file with the ...
3
votes
1answer
41 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
3answers
391 views

Why does transaction log continue to grow in Simple recovery mode with nightly backups

Before immediately marking as duplicate, I have read Mike Walsh's Why Does the Transaction Log Keep Growing or Run Out of Space?, but I don't think it gave an answer to my situation. I looked through ...
2
votes
0answers
53 views

Issue shrinking dev sql server DB's log file

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 ...
0
votes
1answer
44 views

Shrink Transaction Log While Using AlwaysOn Availability Group

We are using AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database. I have read here doing the ...
0
votes
0answers
20 views

Maintenance Plan Transaction Log Backup

I have a backup device set for my transaction log so the sets are contained in 1 file. I have this set to append, is this correct or should it be overwrite in the plan? After the full backup is taken ...
0
votes
1answer
45 views

How to deal with very large transaction logs in SQL Server

Here is my situation: I have being asked to deal with a SQL Server 2008 R2 database that has a transaction log that is several times bigger than the database itself. The database is in full recovery ...
1
vote
0answers
131 views

Transaction log full due to log_backup

I am testing how certain operations are logged under different recovery models.Below are steps i have did till now 1.create database in full recovery model 2.take backup 3.create a table and insert ...
2
votes
1answer
45 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 ...
1
vote
2answers
87 views

Fixing an error 9004 without taking the database offline

My database (SQL Server 2008 r2) is running fine but I’m getting the following error in my event log. Error: 9004, Severity: 23, State: 6. An error occurred while processing the log for database ...
4
votes
1answer
61 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 ...
1
vote
1answer
75 views

Restore Verify Only w/ Transaction Log Backups

Question When RESTORE VERIFY ONLY on a transaction log backup fails, what do you do? Background After FULL and DIFFERENTIAL backups, I run RESTORE VERIFY ONLY on the backup file(s). This rarely ever ...
0
votes
2answers
73 views

Open Transaction that won't go away SPID Ns

There is a transaction that has been open on this instance since it was restarted and is preventing us from clearing the ldf. Here is the transaction information: Transaction information for ...
2
votes
2answers
61 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 ...
1
vote
1answer
49 views

Statistical probability of an incomplete transaction committed by Postgres

tldr After spending time with bad databases which I will not mention, I came to question even the point of a database. I knew that they could serve a purpose to keep permanent data organized, but ...
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 ...
1
vote
1answer
56 views

Dealing with large transaction logs in a cluster

Disclaimer: I'm a dev, not a DBA. I've gleaned what I can from MSDN, but could do with some expert guidance! I'm working with a SharePoint 2013 installation on a SQL Server 2012 cluster of two DB ...
4
votes
3answers
142 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 ...
0
votes
0answers
24 views

Lock rows for long running transactions

I have a question table from which agents try to answer 5 questions at a time. Obviously, I want to avoid agent collision i.e. if an agent is answering a question, other agents can not work on it. Is ...
0
votes
0answers
22 views

Reducing log files? [duplicate]

I have a database that has a log file of nearly 80 GB. Database size 180 GB. The recovery is set to Full. What is the correct way to shrink the log file without using the shrink option in SSMS as ...
-2
votes
1answer
66 views

Non- Logged operation [closed]

I want to run a non-logged operation with changing the recovery model of a SQL Server database. Is their any way to do this?
1
vote
1answer
107 views

Transaction Log Shipping doesn't use the latest transaction log

I have setup transaction log shipping between two servers every 15 minutes, this is on SQL Server 2008 R2. The problem is that when the logs are being restored on the secondary server, it always uses ...
2
votes
2answers
135 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
1answer
95 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
1answer
105 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 ...
5
votes
4answers
214 views

Only do log backup if necessary in SQL Server

We have transaction log backups taken every few minutes from many databases. Is there a way to determine if there were any committed transactions since the last backup and only then do a log backup? ...
2
votes
3answers
520 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 ...
1
vote
1answer
67 views

Database never maintained

Just moved from wrong forum i paste here my question: I just got a new client recently migrated from SQL Server 2000 to SQL Server 2005 due to vendor application limitation to new releases. Vendor ...
1
vote
1answer
186 views

Delete log to increase DB space?

I'm running SQL Server 2008 Express and hitting the 4GB size wall. I'd like to make some space to give me time before i migrate to a larger DB. I don't need my transaction log. So, can i delete ...
3
votes
1answer
87 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 ...
1
vote
0answers
73 views

Mysqldump --flush-logs results in small backup files?

New to MySQL and just learning how to setup a daily full backup (binary logs enabled). My goal is to run a daily full backup of the databases/schemas and reset the binary log. I'm currently using an ...
2
votes
3answers
550 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 ...
5
votes
3answers
774 views

Encountered unexpected growth Transaction log file size

We are using SQL Server 2008 R2 Standard version, we created Maintenance plan Rebuild Index, Reorganized Index and update Statistic , Scheduled Every day at 1 AM. After Executing this Maintenance ...
2
votes
1answer
38 views

Transaction Log Backups on SQL Server

Running SQL2008 R2 SP2, database is in FULL Mode Is it Possible to shrink the transaction log without putting it into SIMPLE mode? Temporarily cannot take log backus and don't want it to grow until I ...
3
votes
1answer
259 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 ...
0
votes
1answer
141 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
125 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 ...
6
votes
1answer
77 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?
0
votes
1answer
67 views

Audit SQL Server database transactions

I found some column values have been changed on a table, I need to view when and who made these changes. I tried to read the transaction log backup file, but I didn't understand it and cannot find ...
2
votes
1answer
67 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
153 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
79 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 ...
3
votes
2answers
287 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
46 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 ...
4
votes
1answer
696 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 ...
1
vote
0answers
154 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 ...
0
votes
2answers
832 views

ORA-00338: log # of thread # is more recent than control file

We are facing the following error on our production database: ORA-00338: log # of thread # is more recent than control file The database crashed and there was no recovery action. What could I ...
3
votes
0answers
143 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 ...