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 ...
- 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 ...
- 540
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 ...
- 1,789
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 ...
- 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 ...
- 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 ...
- 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,...
- 176k
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 ...
- 4,408
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 ...
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 ...
- 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 ...
- 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, ...
- 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. ...
- 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),@...
- 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 ...
- 1,053
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'...
- 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 ...
- 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 ...
- 173k
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’...
- 23.2k
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 ...
- 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. ...
- 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 ...
- 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 ...
- 5,998
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. ...
- 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, ...
- 126
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 ...
- 23.6k
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, ...
- 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 ...
- 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 ...
- 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 ...
Only top scored, non community-wiki answers of a minimum length are eligible