Since this not production, it's not so critical to do this the "right" way. Having said that, running QA the same way as production is beneficial for ensuring everything goes well in production.
"Best practices" would be to gracefully shutdown each VM prior to rebooting the host. That allows the AG to realize the databases need to fail over to the ...
Since you are on standard edition - you will be using Basic Always On availability groups for a single database.
Once we move to HA with 1 listener for each DB, will this new configuration be any issue for me?
The only issue that I see is that you have to failover all the interrelated databases together (since your app has cross db calls e.g. joins, etc)...
One of our servers has a subscriber database. I've not used
replication before and cannot find information on how the mechanism
works. Does the publisher have to wait on the replication completing
before transactions are committed or does it make use of a queue or
the transaction log before applying changes to the subscriber?
You haven't specified ...
Long running and locking queries on subscriber databases wouldn't typically cause performance issues on the publisher. The distributer can be run separately if you are worried about them affecting the performance of your SQL server.
The Log Reader Agent will take the changes from the Publisher and store them in the distribution database. The Distribution ...
Honestly, the amount of time you'll spend writing a version of this that accounts for all possible combinations of indexes, constraints, and default values, and troubleshoot all the combinations you don't expect from your first use case, I don't think you'll ever get that time back no matter how many table types you have to script, which you can always do ...
To answer you question #3
If #2 is the best fix, is there any good way of preserving the data in Query Store that would be deleted?
See How can I export Query Store data? it is not difficult in most cases to export the QS data. I can't say if your error will impact the export.
You may find some data missing when you export, See Why is Query Store ...
I fixed the error changing the compatibility level of my database from 110 to 130 (SQL Server 2016).
My instance build version is 13.0.5062.0 but the databases were migrated from an older version and never changed the compatibility level.
Make sure you configured read-only routing URL per each replica, arranged replicas in read-only routing list. For detailed steps..
URL Example: TCP://HOSTNAME.DOMAIN.LOCAL:1435 (TCP://FQDN of host:SQL Replica Port Number)
Routing List example: As per following example, when SQL03 is primary SQL02 and SQL01 act as read-only replicas
Download SP_WhoisActive from here: http://whoisactive.com/
run it with this query: SP_whoisactive
the result will show everything running at the moment, you will be able to identify your running stored procedure.
then run: kill [session_id from query above]
the transaction will be rolled back.
If the change (Alter login LoginName with DEFAULT_LANGUAGE = English) applied successfully (Commands completed successfully.) the language must be changed though server default is Arabic.
However, you have to disconnect the session and connect with new session then try select @@language.
If you have already done so, and the problem still persist, check ...
You can find the compression level in the sys.partitions dmv
SELECT t.name AS tablename,
i.name AS indexname,
FROM sys.tables AS t
INNER JOIN sys.indexes AS i
ON t.object_id = i.object_id
INNER JOIN sys.partitions AS p
ON i.object_id = p.object_id
AND i.index_id = p.index_id;
To see whether or not data pages are indeed currently "PAGE" compressed, you can use the undocumented DMF sys.dm_db_database_page_allocations(). The is_page_compressed field contains the info you are looking for. You will need to use the DETAILED mode (i.e. 5th parameter) or else the values in that field will all be NULL.
To be clear (based on the wording ...
The job is running as the same identity in both cases. This is a difference only in how the information is reported. NT SERVICE\SQLSERVERAGENT is a least-privilege local identity used for running SQL Agent jobs. Like other built-in local Windows identities this account can access network resources using the computer account. So from the local point-of-...
@HandyD is entirely correct, I only want to highlight some other methods to get compression while inserting into a heap.
From the same document
When a heap is configured for page-level compression, pages receive
page-level compression only in the following ways:
Data is bulk imported with bulk optimizations enabled.
Data is inserted using ...
According to the Docs article on compression:
New pages allocated in a heap as part of DML operations do not use
PAGE compression until the heap is rebuilt. Rebuild the heap by
removing and reapplying compression, or by creating and removing a
This would seem to align with what you're seeing. It seems like you're not actually ...
Renaming Windows computer for SQL Server is supported an operation and described in documentation. For mirroring, it needs to be turned off and re-established after renaming. Remember that renaming Windows requires a reboot, so be prepared for service outage.
When you rename a computer that is configured to use database
mirroring, you must turn off ...
Does implementing gMSA works for 2 node SQL FCI and 3 or 4 Node Always on AG,s?
Yes it does work.
Since I just want to run SQl server with that gMSA account is there any special permissions needs to be granted at server or database level while running both SQL and agent services?
Nope, the installer will take care of that.
Please advise any catch of ...
I spend the day dealing with the same problem in vain, my temporal solution would be to run the SSIS package with a CMD command.
here is my example:
dtexec /ISSERVER "\"\SSISDB\project_____name\Main_Package.dtsx\"" /SERVER server___name /X86 /Par "\"$Project::var___name\"";"\"value___value\""
The completion time message was added in SSMS 18.2
I don't believe there is a way to turn it off
Sometimes you need to open firewall rule for UDP 1434 SQL Browser port. Be sure SQL 2012 and 2016 don't use same TCP port (1433). If multiple instance on same machine they should be configured to use separate TCP port.
Firstly - is there a good script to give me an overview of AG
configuration and health? I understand the console in SSMS gives me an
overview, but is there no script that can automate the task and be
used to detect or report on servers lagging behind, etc?
Keep in mind that there are many things that are important regarding Always On Availability ...
The NT Service Accounts
NT SERVICE\MSSQLSERVER NT
In a Q and A similar to yours, an answer by by Rick Byham, Microsoft explains what these NT SERVICE accounts are
No, they can't be selected in the list of available built-in accounts,
local accounts or domain ...
I'm not sure about NT AUTHORITY\SYSTEM. But according to the SQL Server documentation the others are added to the sysadmin role by SQL Server Setup, so I'm guessing it is required.
The log_reuse_wait_desc is only updated when SQL Server attempts to clear the log. In other words, it's not necessarily a "live" view of whether the log can be cleared or not. Paul Randal explains that in his blog post "Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?".
In the case of tempdb, which uses the SIMPLE recovery model, SQL ...
It's not duplicating of the information, depending on the requirement we could utilize anyone them:
DMV: The prefix sys.dm_ represents DMV the under lying information from Dynamic Management views
Catalog View: No prefix sys. represents catalog views. For more details..
The process with high deadlock priority was chosen as a victim...
I've run into this before with maintenance operations - specifically index reorgs, as described on my blog here: Deadlock Victim Choice in SQL Server - An Exception?
In that case, I was able to reproduce the problem, having a process with the highest deadlock priority (10) chosen as the ...
Its support ends in 7/9/2024 right?
Extended support ends 7/9/2024. Mainstream support ends 5 years before that, or about a month ago. This means it's now in extended so no more bug fixes, only security patches. I guess it depends on how you look at "supported".
what are the reasons to upgrade directly to SQL Server 2016?
At this point, I'd either look ...
Randi's answer works perfectly, and Akina's is much tidier if you are on SQL Server 2017 or better, but on older versions you can do this with slightly less code and fewer operations like reverse/stuff. Given this data:
CREATE TABLE #temp(col varchar(255));
SELECT 'foo' UNION ALL SELECT '"bar"'
UNION ALL SELECT 'blat"' UNION ALL ...
You could try something like this
SELECT CASE WHEN RIGHT(fieldname,1) = '"' AND LEFT(fieldname,1) = '"' THEN REVERSE(STUFF(REVERSE(STUFF(fieldname, 1, 1, '')), 1, 1, ''))
WHEN RIGHT(fieldname,1) = '"' THEN REVERSE(STUFF(REVERSE(fieldname), 1, 1, ''))
WHEN LEFT(fieldname,1) = '"' THEN STUFF(fieldname, 1, 1, '')
ELSE fieldname END as ...
Well, you won't have any history, but you can catch this in the act in the future, since SQL Server Audit can do this, using DATABASE_OBJECT_CHANGE_GROUP:
CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'C:\path\to\audit\folder\', MAXSIZE = 1 GB)
WITH (ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT ServerAudit
WITH (STATE = ON);
Since you're on SQL Server 2016, it's worth mentioning that there is at least one public bug fix for parallel deadlocks involving columnstore indexes:
FIX: A deadlock occurs when you run a parallel query on a clustered columnstore index in SQL Server 2016 and 2017
(thanks to Denis Rubashkin for providing the link initially)
This was released as part of ...
Hope following expected result script help in your case, but you have prepare it for maximum number of fields.
Test environment (1 time execution only) as per your scenario:
Create table #Temp
(formid int, FieldName varchar(50), value_ varchar (50) )
Insert into #Temp
(101, 'FirstName', 'Name 1'),
(101, 'LastName', 'x'),
(101, 'Email', 'Name@...
Dynamic generation of the field list (based on Scott Hodgin's answer):
DECLARE @sql VARCHAR(1000);
WITH fields AS ( SELECT STUFF( ( SELECT ',' + fieldname
FROM ( SELECT DISTINCT test.fieldname
FROM test ) tmp FOR XML PATH('')
), 1, 1,''
WITH t0 AS ( SELECT DISTINCT formid
FROM table )
SELECT t0.formid, t1.value FirstName, t2.value LastName, t3.value Email
LEFT JOIN table t1 ON t0.formid = t1.formid AND t1.fieldname = 'FirstName'
LEFT JOIN table t2 ON t0.formid = t2.formid AND t1.fieldname = 'LastName'
LEFT JOIN table t3 ON t0.formid = t3.formid AND t1.fieldname = '...
Here is an example of using PIVOT to achieve your desired results.
Declare @Table table (FormId int, FieldName varchar(10), [value] varchar(10))
insert into @Table(FormId, FieldName, [value]) values
The problem you're facing is down to how the TRY...CATCH construct handles errors - it consumes them, eats them, and spits nothing back out except for a 0 return code. This is evidenced by a very innocuous line in the docs on TRY...CATCH that says
Errors trapped by a CATCH block are not returned to the calling application. If any part of the error ...
Have you checked following blog on Intra-Query Parallel Thread Deadlocks
The SyncPoint resource indicates the use of an exchange event if I'm not mistaking.
Looking at the participants of your deadlock you can see that they are all coming from the same spid(55) and batch(0), but are using different threads.
This indicates that they are all part of the ...
I am a newbie to SQL and I came across a situation where I have to use save transaction
Savepoints are rarely used (or useful) and it's unusual that "a newbie" would "have to use save transaction".
what is the difference between having a save point and not having a save point transaction in a Stored procedure where we don't use nested transactions
You can potentially find the error message through Extended Events. Create and start the following session:
CREATE EVENT SESSION FailedQueries
ADD EVENT sqlserver.error_reported
ADD TARGET package0....
Instead of processing DataGatheringRuntime table which is quite big at ~100GB) mutiples times
process it single time by putting them in #temp table or CTE
Then remove Distinct. If there is duplicate data then find the reason behind duplicate data
and remove duplicate data by writing correct query.
What is the purpose of Distinct and UNION in same query ?
As Yannick has suggested you can use the ordinal order, or you can use your query as a sub-query and use the column name, but you can use too the alias of CASE statement.
Given the next example:
CREATE TABLE test (ID int IDENTITY PRIMARY KEY, Val1 varchar(100));
INSERT INTO test VALUES
Using the alias:
Since ORDER BY is executed after SELECT you can use the alias that you've defined in your SELECT clause.
ORDER BY ArtHeelNo
Another (less recommended) option would be to just order by the number of the column
ORDER BY 2
It's also possible to just repeat the statements in your ORDER BY, this is less recommended since if your statement would change, you ...
If you want to include null you can map it to a value outside the range that's exluded, i.e.:
WHERE ta.ROW_PROCESSED_THRU = '20190731'
AND COALESCE(ta.CLOSED_DATE, '00000000') NOT BETWEEN '20190701' AND '20190731'
To include the NULLs you need to add an explicit NULL predicate. Also, the convert function may significantly degrade your query's performance, much better would be to explicitly state the date range with two predicates:
AND ( NOT (ta.CLOSED_DATE >= '20190701' AND ta.CLOSED_DATE < '20190801')
OR ta.CLOSED_DATE IS NULL)
On the primary node, I took a drive offline that contained one of the data files of the DB that is in the AG. I ran a select * from a table which read from the missing disk [...]
Since you're on 2016, the database level health checks are checking that the database is online (which taking secondary file offline won't change) and that we can write to the ...
You should cast @id to varchar and concate it. like this.
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"'+cast(@id as varchar)+'"***.value')) Agency,
Complete query is. Hope this will help you.
declare @id INT
set @id = 69
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) CorrespondanceNumber,
The code you pasted in your question looks like you just copied/pasted the whole statement from the docs. You indicate later that you used "new file locations", but it's unclear if you actually did everything you needed to, especially since what you're doing doesn't seem to be working. Try posting your exact code, with specifics as much as possible.