58 votes

Truncate a table with 17 billion rows in an AG

Logging Extents The amount of log data generated (and thus sent over the network to your other AG nodes) depends on how big the rows are in your 17 billion row table are. TRUNCATE will definitely be ...
user avatar
  • 27.1k
40 votes

Difference between Full backup and Copy-only full backup

The key difference between Full and Copy-only backups is whether or not the LSN (Log Sequence Number), and specifically the DatabaseBackupLSN is updated. When you take a Full backup, the ...
user avatar
31 votes

Why Does the Transaction Log Keep Growing or Run Out of Space?

You can also see the content of your log file. To do that, you can use the undocumented fn_dblog, or a transaction log reader, such as ApexSQL Log. It doesn't show index reorganization, but it shows ...
user avatar
24 votes
Accepted

Difference between transaction log and redo log in MySQL

REDO log is Oracle terminology, transaction log is InnoDB terminology. Now that all are Oracle engineers, people use both to refer to the same thing in MySQL. The transaction log is, by default- it ...
user avatar
  • 14k
21 votes
Accepted

Shrink Transaction Log While Using AlwaysOn Availability Group

In AGs writes can only occur on the primary. Shrink operations are writes. Therefore you must do the shrink on the primary. Note that the shrink may not shrink as much as you expect, your test on the ...
user avatar
  • 50.8k
21 votes

Does backing up a database shrink the transaction log size?

The transaction log file (LDF) is made up of lots of virtual log files (VLFs) inside. Think of it like a cabinet with several pull-out drawers. You could choose a large cabinet, or a small one - but ...
user avatar
  • 41.3k
20 votes
Accepted

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

It is impossible for us to guess what is causing it, but SQL Server doesn't just grow a log file to 300 MB for the heck of it, it grows to 300 MB because at some point since your last shrink operation,...
user avatar
19 votes
Accepted

Truncate a table with 17 billion rows in an AG

Truncate table command truncates rows instantly and does not write deleted rows to transaction log file Usually Truncate table executes instantly and there is no noticeable network traffic between ...
user avatar
18 votes

Difference between Full backup and Copy-only full backup

Assume that we have a database with scheduled backups. The full backup runs once in 24 hours at 00:00, also we have differential backups that run every 6 hours, and transaction log backups that run ...
user avatar
16 votes
Accepted

During a log backup is the data backed up to the start or end of the operation?

The log backup file shall contain all the transactions (including the ones that hasn't been committed yet) up to the moment the backup finished running and you can verify it as follows: CREATE ...
user avatar
  • 4,384
15 votes

transaction log in RAM or physical file?

You can find a pretty comprehensive guide to this question here, but to summarise, SQL Server will not return control to the application that committed a transaction until that transaction has been ...
user avatar
  • 9,222
15 votes
Accepted

Is ALTER TABLE ... DROP COLUMN really a metadata only operation?

There are certain circumstances where dropping a column can be a meta-data-only operation. The column definitions for any given table are not included in each and every page where rows are stored, ...
user avatar
  • 67k
14 votes
Accepted

Why is it not recommended to use RAID 5 for a log file?

Transaction log writes, when they occur, are synchronous operations, that is, the activity that has caused a log write must wait until log I/O completes before continuing to do whatever it is doing. ...
user avatar
  • 21.1k
13 votes
Accepted

Set simple recovery mode and shrink log files for all user created databases

Use Script to Shrink Log files of all databases other than the system DBs. USE MASTER GO SET QUOTED_IDENTIFIER ON GO SET ARITHABORT ON GO DECLARE @DBName NVARCHAR(255),@...
user avatar
  • 3,913
13 votes
Accepted

how to reinitialize log shipping?

Disable the log shipping jobs on both servers. Back up the database on Server A. Restore the database with the backup you just created and with norecovery on Server B. Re-enable the log shipping jobs ...
user avatar
13 votes
Accepted

Does taking transaction log backups every 15 minutes consume more disk space than log backups every 6 hours?

No, but it may make your transaction log smaller - so your SQL Server will use less space. To keep things simple, say you generate 1MB of transaction log activity every minute. After 15 minutes, you'...
user avatar
  • 41.3k
13 votes
Accepted

Why would shrinking TEMPDB log lead to slowness?

I'd ask the person who told you that, to at least hear why they think it will decrease performance. One reason is that your TLOG isn't going to stay at 0. Since you shrunk it so small, I presume you ...
user avatar
  • 3,488
12 votes
Accepted

When is it safe to disable InnoDB doublewrite buffering?

The only situation I can think of is reloading a large mysqldump. Why ? Check out this Pictorial Representation of InnoDB (Percona CTO Vadim Tkachenko) From the picture, you can see that the InnoDB ...
user avatar
12 votes
Accepted

Is It Possible to Run Out of Log Sequence Numbers?

I found the following answer from a post written by Paul Randal. Is It Possible to Run Out of Log Sequence Numbers? Paul writes: There’s no need to worry because for all practical purposes it isn’...
user avatar
12 votes

Why am I not getting minimal logging when inserting to indexed tables

I can reproduce your results on SQL Server 2017 using the Stack Overflow 2010 database, but not (all of) your conclusions. Minimal logging to the heap is unavailable when using INSERT...SELECT with ...
user avatar
  • 69.1k
11 votes

Run Transaction Log backups only during Work hours?

On a technical level: is this a good practice or not? I would say not. If the database is experiencing no activity at all during the business off hours, then taking the backups is very low overhead. ...
user avatar
  • 50.8k
11 votes
Accepted

Why are virtual log files not always allocated in order?

Paul Randal details a scenario where log VLFs may be allocated out of the expected sequence in this blog post. Essentially, log file growth may result in unexpected allocation order, even in simple ...
user avatar
  • 67k
11 votes
Accepted

SQL Server Truncates Transaction Logs with Copy Only Backups

I recommend you use the code in one of the two existing answers to validate that t-logs are not being taken. Depending on the access your users have, someone may be taking backups without your ...
user avatar
11 votes
Accepted

SQL Server - Files timestamps unchanged so what is it that's being backed up?

In answer to your main question ("what is being backed up"), everything that is supposed to be 😀 More seriously, you can't use those time stamps as a sign of changes happening in the database files. ...
user avatar
  • 27.1k
10 votes
Accepted

The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'

I had a similar problem: I did not have replication but once I used Memory Optimized table as a test, database in Simple recovery mode, but my transaction logs weren't truncated. Manual truncation, ...
user avatar
10 votes

SQL Server log file used pecentage does not drop after multiple backups

In addition to the additional space marked "in-use" by SQLPERF you might have space in your transaction log that is not being freed for other reasons. You should probably take a look at the ...
user avatar
10 votes
Accepted

Is it possible to restore SQL Server DB from transaction log (no MDF)?

Is it possible to restore the database to the last transaction (which should be just after the database was created) using just the transaction log, or is this something that can't be done? No, ...
user avatar
  • 61.1k
10 votes
Accepted

Why is CHECKDB reading the transaction log file on a database with a memory optimized table?

Even though the SQL Server documentation states that databases with "In-Memory" tables do not support snapshots, the "internal" snapshot required for DBCC CHECKDB can still be created since the ...
user avatar
  • 67k
10 votes
Accepted

what is the penalty for NOT setting Disconnect users in the database when restoring backups?

From this page in the MS documentation: There are two options for configuration when you place the secondary database in standby mode: You can choose to have database users disconnected when ...
user avatar
  • 3,935
10 votes
Accepted

Decrypt T-SQL log backup header and read LSN

Could anyone confirm or denies that restoring the header only wouldn't affect the sys.fn_dblog or anything else? RESTORE HEADERONLY doesn't target any database in its syntax: RESTORE HEADERONLY ...
user avatar

Only top scored, non community-wiki answers of a minimum length are eligible