Most significant variable is the size of the transaction log backup that you are trying to ship, larger files :-
take longer to copy,
longer to restore &
more time to create the standby file.
If tlog backup size doesn´t explain more than 90% of the variation then you can start to troubleshoot whether server CPU or network congestion is involved.
Any reason you could not simplify further and use a four part Linked Server address and ditch OPENQUERY altogether?
SET pg.Additionalbarcode = UDF_ADDITIONALBARCODE
FROM WEBDB.DBName.dbo.ProductGroup AS pg
INNER JOIN LocalDB..Items AS CI ON pg.Id = CI.itemcode
WHERE 1 = 1
AND NULLIF(UDF_ADDITIONALBARCODE, '') <> ...
I don't know exactly why that is failing, but there are probably better alternatives.
First is the general best-practice to avoid the propriatary and confusing UPDATE FROM:
Start with a working SELECT and then do
with q as
Select itemcode, UDF_AdditionalBarcode, Id, Additionalbarcode FROM
openquery([remoteserver], ' SELECT Id ,[AdditionalBarcode] FROM
It is impossible for us to answer whether you want to compress your indexes or not. Only you have access to your data, server and queries. Considerations includes:
Page compression is pretty expensive to crack ("decompress"). Pages lives in memory in compressed form, meaning that every time a page is accessed (including in memory), it has to be ...
TLDR; Make sure the user re-authenticates on the local windows machine that the client is establishing the connection from.
From my experience, It seems SQL doesn't go check group membership when you connect to SQL but relies on the connecting client providing the security group information they belong to. If the local client authenticated - logged into ...
It seems your option is this undocumented stored procedure running in a job:
EXEC sp_fulltext_recycle_crawl_log @ftcat = 'FullTextCatalogName';
Check the Recycle Fulltext Catalog Log Files article by Jonathan Kehayias.
I just tested it in a lab environment and it purges the oldest fulltext log file creating a new live file (in my lab I could see 1 live file ...
Windows-Verified Disk Defragmentation solutions won't cause corruption in your databases, however in most scenarios performing physical defragmentation of SQL Server data and log files is unnecessary.
I say "most scenarios" since most production SQL Servers will have a dedicated drive setup for SQL Server data and log files. However, if your SQL ...
It depends on what column you sort by. To dig deeper, I suggest you read the source code that these are on. I blogged about AM a while ago, here is a part from that blog post:
The “Recent Expensive Queries” pane:
This shows the most expensive queries, based on what column you sort on, executed since the last snapshot. If you have, say, a 10 second snapshot ...
Perhaps the user had an invalid Kerberos ticket (for example expired), which caused the two "access denied" errors. At some point in time after that the user's workstation contacted a DC and renewed the ticket, allowing the user to execute the procedure the third time.
Another possible root cause would be a misunderstanding of how a stored procedure definition is terminated. Take this for example:
CREATE OR ALTER PROCEDURE dbo.MyTestProc
SELECT result = 1;
GRANT EXECUTE ON dbo.MyTestProc TO [SomeUser];
The intention here is to define the procedure and then grant permissions to it.
However, the first time ...
Related to Josh's answer: An alternative could be that this person was a member of an AD group for which there was an explicit DENY to do the operation. And then this person was removed from the AD group with that DENY.
I'm talking about a user in the database. A user that points to a login, where that login points to an AD group i.e. the deny could have ...
If the Windows user account was added to an Active Directory (AD) group in the meantime, and that AD group had permission to run the procedure, then that could create this scenario where a user gained access to a procedure with no changes within SQL Server.
Also get the network admin to check if the user logged off and on again. Group membership only gets ...
No, after a lot of improvements over the years to the DBCC CHECKDB command, it no longer explicitly takes out locks and it does not specifically block backups. Here's a good article about it: A SQL Server DBA myth a day: (2/30) DBCC CHECKDB causes blocking
The only issue to be aware of is that the DBCC CHECKDB command can be resource intensive and backups ...
I have an SQL Server 2014 (12.0.2000.8)
RTM version? I recall running into excessive compilation duration (minutes) for some queries. The issue was fixed post RTM. I suggest you patch your server to a supported patch level (SP3+).
I expect that most of the compilation time is spent by the optimizer considering different approaches to re-ordering the many joins.
Two options for fixing this are:
Break the query up
One approach that might work, depending on your query, is separating the relational from the informational part of your query. This involves breaking up the query into two ...
You can monitor for statement level recompile events using Extended Events. I have a blog post here that lays out exactly how to do it. You can see that there is a column for the recompile reason. That will tell you explicitly why any given statement was recompiled.
You can, for a plan that stays in cache, prevent it from recompiling while it's in cache by ...
Yes the SA account does have an SID and you can verify what it is by logging into the SQL Server as the SA account and using this SQL function: SELECT SUSER_SID('sa');
MS Docs on it: https://docs.microsoft.com/en-us/sql/t-sql/functions/suser-sid-transact-sql?view=sql-server-ver15
There's nothing special about using SA or any other account in an AlwaysOn ...
I agree with @J.D. said about temp table variables. Because until SQL Server 2019 version, Query Engine thinks the estimate of temp table variables only returns one row.
Otherwise, I think you should leave also Clustered Index Scan on anotherbigtable. Because it cost 98%
I didn't know your indexes, but I recommend the index below.
CREATE INDEX ...
The first thing I would do is avoid using table variables in heavy SQL operations. You should use a temp table instead. Either initially or select your table variable @table1 into a temp table then use that temp table in your update statement.
Table variables are historically known to be bottlenecks because statistics aren't maintained on them, and even with ...
While CDC is one useful methodology in SQL Server, it can also be a little performance heavy too depending on how much data and how frequently it changes.
You might want to consider a 3rd party tool to manage your synchronization between SQL Server and SQLite, which can help minimize the performance and maintenance overhead of a homebrew solution.
CHECKDB need two types of storage while executing:
Tempdb. This is to keep track of where it is (to simplify quite a bit). This is what Dominique refers to. However, this was not your problem (as we can see by the error message).
On the same disk where each data file for the database resides. It creates one "CHECKDB working file" for each data ...
It depends on the database size.
You can run
dbcc checkdb ('master') WITH ESTIMATEONLY
To get an estimation of the space requirement. Usually, this is not really accurate but I guess it's better then nothing.
You can try getting the same info using dbatools PowerShell module and see if that helps:
Get-DbaAgentJobHistory -SqlInstance sql2\Inst2K17 -StartDate '2017-05-22' -EndDate '2017-05-23 12:30:00'
More info about the parameters of the cmdlet can be found here so you can filter even more the result, if needed.
No unfortunately it is not possible. As @Newman mentioned, you can query the msdb.dbo.sp_help_jobhistory procedure to get the data back or you can adjust your jobs timeframe cutoff or total number of rows cutoff (whichever is more suitable to your business needs): https://docs.microsoft.com/en-us/sql/ssms/agent/resize-the-job-history-log?view=sql-server-...
Adding a nullable column like that is generally a meta-data-only operation. That is it will complete nearly instantly as long as it can get a schema stability lock on the table for the short time it takes to add the column.
If you're seeing it take a long time, the cause is most likely that other sessions are blocking the ALTER TABLE statement. Realize, ...
You can get failure reason by looking at message section of SQL-Agent Job history window.
If the error produce more than 1024 characters, you can query msdb.dbo.sysjobhistory, to be specific to the requirement, you can use following query:
Declare @job_name sysname = 'YourJobName',
@DateYYYYMMDD int = 20201030,
@step_num int = 1;
You should be able to adjust this query to your criteria - taken from What is the Query to display the failed sql jobs
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh
Are you asking about the format or the value?
By "value" I mean if it is in UTC or the local datetime value.
The function doesn't have a stored value, it work with what you pass into it and returns a datetime value based on that. I.e., there's no UTC relevance for this.
The columns, for instance in sysjobhistory, are not in UTC. They are in the ...