Tuesday, March 21, 2017

Minimally Logged Operations in SQL Server

I'm posting this mostly so I can find it later :)
The following article has a great table that defines when operations will be minimally logged.
https://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx

Here's the table, just in case the link goes away at some point in the future:

Table Indexes

Rows in table Hints Without TF 610 With TF 610 Concurrent possible
Heap
Any
TABLOCK
Minimal
Minimal
Yes
Heap
Any
None
Full
Full
Yes
Heap + Index
Any
TABLOCK
Full
Depends (3)
No
Cluster
Empty
TABLOCK, ORDER (1)
Minimal
Minimal
No
Cluster
Empty
None
Full
Minimal
Yes (2)
Cluster
Any
None
Full
Minimal
Yes (2)
Cluster
Any
TABLOCK
Full
Minimal
No
Cluster + Index
Any
None
Full
Depends (3)
Yes (2)
Cluster + Index
Any
TABLOCK
Full
Depends (3)
No

No comments:

The Database is not accessible after creating Always On Availability Group

 Having created a fully functional SQL Server Always On Availability Group, I connect using SSMS. Upon trying to expand the database, I get ...