Transaction log is a list/history of modification executed by a database management system to guarantee ACID properties over crashes or hardware failures.
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 ...
12
votes
2answers
636 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 ...
11
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 ...
7
votes
3answers
247 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 ...
6
votes
3answers
1k views
View delete statements in the Transaction log
Is it possible to view delete statements that have recently occurred in the Transaction Log?
6
votes
1answer
372 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 ...
6
votes
1answer
86 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 ...
6
votes
2answers
98 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 ...
6
votes
1answer
609 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 ...
6
votes
2answers
798 views
SQL Server 'Create Database' statement. How to inherit autogrowth settings?
I am using SQL Server 2008 R2 and creating databases through continuous deployment.
In our system, the default 1Mb/10% autogrowth settings in SQL Server work badly with our data. Particularly as we ...
5
votes
1answer
89 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 ...
5
votes
1answer
122 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 ...
5
votes
2answers
151 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 ...
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 ...
5
votes
1answer
105 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 ...
5
votes
2answers
536 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`
...
4
votes
4answers
234 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?
4
votes
1answer
68 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
3answers
728 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 ...
4
votes
3answers
171 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 ...
4
votes
2answers
160 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 ...
4
votes
1answer
46 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 ...
4
votes
1answer
118 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 ...
4
votes
2answers
488 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 ...
4
votes
1answer
371 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 ...
4
votes
1answer
1k views
MS SQL Server - Checkpoint process stuck in SLEEP_BPOOL_FLUSH
We seem to have a problem that came out of nowhere. The checkpoint process is stuck in SLEEP_BPOOL_FLUSH state, and not actually reducing the usage in the log file.
This never seemed to happen ...
4
votes
1answer
115 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 ...
4
votes
1answer
1k 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 ...
3
votes
4answers
144 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 ...
3
votes
2answers
379 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 ...
3
votes
2answers
350 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 ...
3
votes
3answers
286 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 ...
3
votes
1answer
187 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
3answers
226 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 ...
3
votes
1answer
304 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 ...
3
votes
2answers
490 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 ...
3
votes
2answers
2k 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
1answer
57 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 ...
3
votes
1answer
88 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 ...
3
votes
2answers
578 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
2answers
55 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
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 ...
3
votes
0answers
150 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 ...
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 ...
2
votes
4answers
380 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 ...
2
votes
3answers
455 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
3answers
144 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 ...
2
votes
1answer
295 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
2answers
1k 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. ...
2
votes
2answers
77 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 ...
