Transaction log is a list/history of modification executed by a database management system to guarantee ACID properties over crashes or hardware failures.
2
votes
1answer
41 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 ...
1
vote
2answers
86 views
Missing or lost transaction and no errors to show what went wrong
I have 2 missing records in SQL Server and I am trying to identify the problem that caused this issue.
New records are inserted using a stored procedure, which will return an error code to the ...
6
votes
2answers
104 views
SQL Server 2012 Simple Recovery Model with LOG_BACKUP log_reuse_wait_desc
While I'm doing my own investigation, does anyone know why a database in SIMPLE recovery model has a LOG_BACKUP for the log_reuse_wait_desc?
SQL Server 2012 SP1. No replication, no mirroring, no log ...
1
vote
4answers
87 views
Simple model database transaction log full 'CHECKPOINT'
I have a SQL Server 2012 SP1 database which has a problem that the transaction log is full. It consists of a single data file and a single log file, both about 800MB.
I had 5GB free disk space, and ...
4
votes
1answer
78 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
...
4
votes
1answer
47 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 ...
1
vote
4answers
94 views
Shrinking the log file does not reduce size
I have a database which had mdf size of 350 MB and ldf size 4.9 GB
When i try to shrink the log file it's not shrinking. I know shrinking a database is not good and it should not be done. But still i ...
3
votes
1answer
58 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
92 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
120 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
57 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 ...
1
vote
2answers
138 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
63 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
69 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
240 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, ...
3
votes
1answer
89 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
32 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
50 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 ...
4
votes
1answer
123 views
Transaction Log maintenance on Mirror database
SQL Server Version: 2008 R2 Enterprise SP2
I am trying to get a handle on our SQL Server maintenance and I came across something I think is incorrect. We have a single production instance with 3 ...
2
votes
4answers
408 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
101 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
0answers
211 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 ...
1
vote
2answers
86 views
Who changed the size of the transaction log?
Can I know, who and when changed size of database transation log?
5
votes
1answer
126 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 ...
3
votes
0answers
153 views
Why can't I read my transaction log backup file using fn_dump_dblog?
I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery.
After backing up the transaction log, I attempt to read it ...
5
votes
2answers
154 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 ...
3
votes
2answers
140 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
121 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
481 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
221 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
187 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 ...
3
votes
1answer
324 views
SQL Server - contents of transaction log file in more detail
I have a question regarding transaction log (let’s just call it LDF for short) contents. I am assuming a database with full recovery model.
I have read that LDF file contains (logs) each and every ...
0
votes
1answer
256 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 ...
2
votes
1answer
56 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
2answers
78 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 ...
12
votes
2answers
4k views
Why Does the Transaction Log Keep Growing or Run Out of Space?
This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this:
In SQL Server -
What are some reasons the ...
2
votes
3answers
465 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
128 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 ...
29
votes
4answers
2k 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 ...
3
votes
4answers
147 views
Does it make sense to use full recovery if the data and log files are on the same HD?
We have a database that was set up to use full recovery and my guess is that the reasoning was only to prevent data loss if a failure happened between backups.
We make daily full backups of the ...
1
vote
1answer
573 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 ...
4
votes
3answers
174 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 ...
0
votes
2answers
95 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 ...
-2
votes
1answer
190 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?
2
votes
3answers
145 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 ...
4
votes
2answers
161 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
88 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 ...
4
votes
3answers
732 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 ...
5
votes
1answer
90 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 ...
2
votes
2answers
267 views
How can I find databases with a large number of virtual log files?
I have several databases on a production server that are in the hundreds of gigabytes, and have many thousands of transactions running through them on a daily basis.
Almost all these databases are ...
