# Tag Info

## New answers tagged sql-server-2016

2

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 ...

0

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)...

1

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 ...

1

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 ...

5

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 ...

2

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 ...

0

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.

0

Verify following: 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 ...

2

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.

0

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 ...

0

You can find the compression level in the sys.partitions dmv SELECT t.name AS tablename, i.name AS indexname, p.data_compression_desc 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;

12

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 ...

3

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-...

7

@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 ...

5

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 clustered index. This would seem to align with what you're seeing. It seems like you're not actually ...

0

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 ...

1

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 ...

1

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\"" 12 The completion time message was added in SSMS 18.2 https://cloudblogs.microsoft.com/sqlserver/2019/07/25/sql-server-management-studio-18-2-is-now-generally-available/ I don't believe there is a way to turn it off 1 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. 1 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 ... 0 The NT Service Accounts NT SERVICE\SQLWriter NT SERVICE\Winmgmt NT SERVICE\MSSQLSERVER NT NT SERVICE\SQLSERVERAGENT What 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 ... 1 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. https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-2016#DE_Prov 2 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 ... 0 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.. 7 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 ... 5 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 ... 3 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)); INSERT #temp(col) SELECT 'foo' UNION ALL SELECT '"bar"' UNION ALL SELECT 'blat"' UNION ALL ... 4 SELECT TRIM(' "' FROM fieldname) fieldname FROM tablename TRIM() function requires SQL Serer 2017. 5 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 ... 2 Change it to: ' + CAST(@formItemId AS NVARCHAR(MAX)) + ' Also, it's always a good idea to post your code rather than a screenshot as this makes it easier for everyone to assist you. 5 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: USE master; GO CREATE SERVER AUDIT ServerAudit TO FILE (FILEPATH = 'C:\path\to\audit\folder\', MAXSIZE = 1 GB) WITH (ON_FAILURE = CONTINUE); GO ALTER SERVER AUDIT ServerAudit WITH (STATE = ON); GO ... 8 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 ... 0 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) ) go Insert into #Temp values (101, 'FirstName', 'Name 1'), (101, 'LastName', 'x'), (101, 'Email', 'Name@... 0 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,'' ... 0 WITH t0 AS ( SELECT DISTINCT formid FROM table ) SELECT t0.formid, t1.value FirstName, t2.value LastName, t3.value Email FROM t0 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 = '... 1 Here is an example of using PIVOT to achieve your desired results. --Demo setup Declare @Table table (FormId int, FieldName varchar(10), [value] varchar(10)) insert into @Table(FormId, FieldName, [value]) values (123,'FirstName','aaaaa'), (123,'LastName','bbbb'), (123,'Email','kk@g.com'), (124,'FirstName','cccc'), (124,'LastName','dddd'), (124,'Email','ll@... 4 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 ... 2 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 ... 2 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 Save ... 1 You can potentially find the error message through Extended Events. Create and start the following session: CREATE EVENT SESSION FailedQueries ON SERVER ADD EVENT sqlserver.error_reported ( ACTION ( sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.nt_username, sqlserver.sql_text ) ) ADD TARGET package0.... 0 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 ? ... 3 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 ('BB'),('FF'),('AA'),('CC'),('DD'); Using the alias: SELECT ID, ... 3 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 ... 0 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' -3 AND (NOT (ta.CLOSED_DATE >= '20190701' AND ta.CLOSED_DATE < '20190801') OR ta.CLOSED_DATE IS NULL) 2 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) HTH 3 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 ... 0 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 select (JSON_VALUE(cast(ad.data_map as varchar(max)),'\$.dataMap."69".value')) CorrespondanceNumber, (JSON_VALUE(cast(...

0

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. ...

Top 50 recent answers are included