Skip to main content
61 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 ...
Josh Darnell's user avatar
  • 29.4k
23 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 ...
Ronaldo's user avatar
  • 5,636
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 ...
Brent Ozar's user avatar
20 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, ...
Hannah Vernon's user avatar
  • 70.2k
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 ...
Aleksey Vitsko's user avatar
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 ...
HandyD's user avatar
  • 10k
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. ...
mustaccio's user avatar
  • 26.4k
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'...
Brent Ozar's user avatar
13 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’...
Scott Hodgin - Retired's user avatar
13 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 ...
Paul White's user avatar
  • 86.9k
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 ...
S3S's user avatar
  • 3,588
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 ...
Hannah Vernon's user avatar
  • 70.2k
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 ...
James Jenkins's 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. ...
Josh Darnell's user avatar
  • 29.4k
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 ...
Hannah Vernon's user avatar
  • 70.2k
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 ...
SQLRaptor's user avatar
  • 4,098
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 ...
David Browne - Microsoft's user avatar
10 votes
Accepted

MS SQL Server DB Transaction Log Growth Rate

“The best time to monitor your instance was 20 years ago. The second best time is now.” – Chinese Proverb I recommend enabling the Query Store. Once you start collecting data, you can either use a ...
Zikato's user avatar
  • 5,102
9 votes

Transaction Log won't shrink, DB thinks it is replicating

Solution for restoring a published database We faced a similar problem: A published database is stored on Server1. Every day this database will be backed up and restored on Server2. We frequently ...
Matthias Elflein's user avatar
9 votes
Accepted

Best way to backup & truncate transaction logs on a schedule

Only overnight SSIS is making writes, daytime is all reads - I only need daily recovery. You should choose your recovery model based on your business needs : How much data business can loose and at ...
Kin Shah's user avatar
  • 62.4k
9 votes
Accepted

Is my restore process being badly affected by this third-party non copy-only backup?

Maybe this will help you understand. Let's do a demo! We'll make a dummy database with some dummy data. USE master; /*Create a dummy database*/ CREATE DATABASE LogRestoreTest /*We full now*/ ALTER ...
Erik Darling's user avatar
  • 42.1k
9 votes

Why Can't I shrink log file in full recovery mode

However, why can I not shrink the file in full recovery mode? It's working as designed. You just need to understand more about what is going on, so you can decide which is the appropriate action to ...
BradC's user avatar
  • 10k
9 votes

Do I need to leave some extra space available on the disk which my database log files reside, for log backup operations to successfully occur?

Well log backups don't change the size of the log file, and the only reason you'd need space on that drive is if you were backing up the log there (which is the same kind of bad idea as putting your ...
Aaron Bertrand's user avatar
9 votes
Accepted

Will non copy only full backup impact transaction log backups chain?

If we just take your question and analyse the information stored in the msdb database, then the answer is: Yes, a BACKUP DATABASE ... with the COPY_ONLY parameter not set, will have an impact on the ...
John K. N.'s user avatar
8 votes

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

This is the most frequently faced issue for almost all the DBAs where the log grows and fills the disk. What are some reasons the transaction log grows so large? Long Active Transaction High logging ...
Ramakant Dadhichi's user avatar
8 votes

How to clear pg_xlog directory?

Managed to solve it quite simply. Stopped the db, commented the options in postgresql.conf and restarted. PostgreSQL took care of deleting everything automatically.
Fabrizio Mazzoni's user avatar
8 votes

Unable to truncate transaction log, log_reuse_wait_desc - AVAILABILITY_REPLICA

If you do this: SELECT * FROM sys.databases And the log_reuse_wait_desc shows AVAILABILITY_REPLICA, that means SQL Server is waiting to send log data to one of your Always On Availability Group ...
Brent Ozar's user avatar
8 votes
Accepted

How to Monitor Transaction Log Usage In SQL Server?

The easiest way is to buy an off-the-shelf monitoring tool. They all give you this kind of information - Idera SQL DM, Quest Spotlight, SentryOne SQL Sentry do these kinds of things with really low ...
Brent Ozar's user avatar
8 votes

Database file ..._log has a max file size set to x MB. If it runs out of space, the database will stop working

When someone does a BEGIN TRAN, inserts a row, locks their computer, and goes home for the weekend, your log file will grow and grow and grow. That would be a Bad Thing™. Rather than facing an ...
Brent Ozar's user avatar
8 votes
Accepted

Why does a log file grow even when transaction is not commited?

Transaction log records are flushed as log buffers become full, which is why you observed growth before the final COMMIT. The buffer is also flushed when a durable transaction is committed, which ...
Dan Guzman's user avatar
  • 28.3k

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