In order to make it easier to understand truncation, space reuse and fragmentation in the log file, Tony gives a brief explanation of how the transaction log works.
This article discusses the different types of backup and recovery models and gives the essential facts that will guide you to being able to achieve a recovery of a database to a point in time.
Tony describes in more detail the SIMPLE recovery model, and the way it works, its advantages and disadvantages.
In FULL recovery mode it is possible to do a point-in-time restore of a database. It is thankfully a rare event. Tony explains what's involved
A DBA may consider switching a database to the BULK_LOGGED recovery model in the short term during, for example, bulk load operations. When a database is operating in the BULK_LOGGED model these, and a few other operations such as index rebuilds, can be minimally logged and will therefore use much less space in the log
This level will examine the most common problems and forms of mismanagement that lead to excessive growth of the transaction log.
Our major goal in terms of log maintenance for all databases under our care is to optimize for write performance, in order to support all activities that require SQL Server to write to the log, including data modifications, data loads, index rebuilds, and so on. However, it's also important to keep an eye on possible log fragmentation, which, as described previously, can affect the performance of processes that need to read the log, such as log backups and the crash recovery process.
From time to time, I encounter blocking situations where I end up issuing a SQL KILL command against the blocking SPID. When I run sp_who2, I can see the killed SPID is in a ROLLBACK state. Is there any way I can get an idea of how long the rollback for the SPID will take?
In this level, we will focus on optimization techniques to apply while building the nonclustered columnstore index, which is available in all versions of SQL Server from 2012 up.
In this first article on the Fabric Modern Data Platform, we look at how to use Generative AI to build tables.
By Steve Jones
immerensis – n. the maddening inability to understand the reasons why someone loves you...
In this post, I will guide you on how to deploy a dockerized simple...
You can now run powerful LLMs like Llama 3.1 directly on your laptop using...
hi, i changed my mysql based linked server's timeout to N'600' instead of N'0'...
Creating an Windows Server Fail-over Clusters with two SQL server 2022 and a File...
hi a few days ago i narrowed down the cause of a new problem...
At what levels can we set the collation in SQL Server 2022?
See possible answers