Why do I get update conflict in this situation instead of just blocking
It is a product defect, which is fixed in SQL Server 2019.
A snapshot write conflict occurs when a snapshot transaction attempts to modify a row that has been modified by another transaction that committed after the snapshot transaction began.
The reason for the incorrect behaviour in ...
And the second theoretical question:
How does SQL Server handle include columns update?
I mean how does SQL Server update all nonclustered index which have an include columns when we update this value? I don't see anything related in the query plan.
I'm not sure I understand what's going on with the first point, and I find the difference in behavior ...
The key here is this:
What's worse, if I cancel this query and do a ROLLBACK to release the
table, it rolls back everything. Not just the last iteration, as one
would expect, but the whole thing.
So apparently your work was already transaction protected. Breaking it up to several DELETE did noting for you, since apparently they are all still one ...
Some of this information like index depth can be found in DMF dm_db_index_physical_stats(). Some outer interesting information that can be found in it is number of used data pages and the fragmentation level. Personally I have used the following query to get the state of the indexes for current database
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
Revised answer, given clarifications in the question
The question is basically, for a given combination of person_code and facility, when you sort the records in [timestamp] order, you want to make sure every row showing an inout value of 'in' is followed by 'out' and vice versa - and that the last row shows 'out'.
Here's the solution using lead - which is a ...
The sys.fulltext_stoplists doc says the column principal_id is the ID of the database principal that owns the stoplist. You can specify it as one of the Arguments of the CREATE FULLTEXT STOPLIST command:
AUTHORIZATION owner_name Specifies the name of a database principal to
own of the stoplist. owner_name must either be the name of a principal
of which the ...
The DBATools PowerShell module is very useful for such tasks.
You can use Get-DbaService function from DBATools to get list of SQL related services installed on one or more servers.
Get-DbaService -ComputerName YourServer
If I read correctly, your failover mode is set to "manual". In this case, SQL will not trigger a failover by itself.
If you are expecting node 2 to be promoted as primary automatically when node 1 goes down, then you will need to change the failover mode to "automatic" and then, that should do it.
Using Database Mail Configuration Wizard
You can achieve that by selecting the Basic authentication option and filling the user name in the Domain\Login format:
You can check the New Account Page doc to understand each parameter of the wizard.
You can also use the system stored procedure sysmail_add_account_sp to do that. Here I used the ...
If xp_cmdshell is enabled you could run this.
CREATE TABLE #WindowsSvc (results VARCHAR(MAX));
INSERT INTO #WindowsSvc
EXEC xp_cmdshell 'net start'
SELECT results FROM #WindowsSvc WHERE results LIKE '%SQL Server%'
As @MSSQLServerDBA says only a sysadmin can alter another user's job or change the job's owner. But the reason for this is that any user who can do that is effectively a sysadmin.
SQL Agent TSQL Job Steps always connect as the Agent Service which is always a sysadmin, but before the job step Agent impersonates the job owner with
EXECUTE AS LOGIN = N'...
Only users in the sysadmin role can edit jobs they aren't the owner of via the SSMS object explorer so if you add all of the users to an AD group that is in the sysadmin server role, it will work, but you probably don't want to do that.
Is there a proven way to monitor or avoid this behavior?
TempDB is a database like any other in many respects, so monitoring tools that can watch metrics like space allocated & used and raise alerts based on those metrics can be used to monitor TempDB too. As a quick proof of that run
Currently the tempdb size for ...
After fighting with this for half a day I took SQL out of the equation and tried to perform the same task using Power Shell. This also failed, my network guy ran a PCAP between SQL and relay server and we have identified the issue.
File was getting to the relay server but for whatever reason on that particular SQL server (AWS) by the time the file made it to ...
SQL Cannot "Seek" as the order of this index is by date first and then by PrimaryKey. As you are selecting with a date that match the full content of the index and as the PrimaryKey are not ordered (they are but only within each specific date), SQL have to scan it.
Try to change your index for this one:
CREATE NONCLUSTERED INDEX ...
I believe we have several misunderstandings here, so lets try to sort them out one by one and see if that clarifies the big picture in the end.
What you refer to as an "unnamed instance" is what we call a default instance. Just as FYI.
It is not the first installed instance that get port 1433. 1433 is a Well Known Port for the default instance. ...
You use a CASE expression that you alias as version. Based on that CASE expression, the data in the version column of tblOperatingsystem has data like '10.0.14393'.
In your compat2 and compat3 CASE expressions, you reference tblOperatingsystem.version, comparing it to a value LIKE '1809'. 1809 is the value in the output of your CASE expression, not the value ...
sys.indexes includes columns that identify if the index is unique, supports a unique constraint, or supports a primary key constraint. The query below lists unique indexes that do not support a constraint:
WHERE is_unique = 1 AND is_unique_constraint = 0 AND is_primary_key = 0;