New answers tagged

0

Better late than never: We have similar case, same symptoms. Check: SELECT log_reuse_wait_desc FROM sys.databases where name = '..' on all your AlwaysOn -secondary instances. if log_reuse_wait_desc is REPLICATION on one of them, switch primary to instance, and disable replication on it. If there is no replication already, use sp_removedbreplication.


0

By default the public server role can see all the databases. You can change this by revoking VIEW ANY DATABASE to public. You can restore it with: use master grant view any database to public


2

When it comes to writing, there is no such thing as "same time." One session will "win" the race for the lock, and update the value first; the "loser" will update the value next, and that's whose change will be reflected for the next reader. As an aside, isolation level doesn't have any effect here (unless there are explicit ...


0

You mean a ##temp table? It isn't logged in error log. But it must be logged in transaction log since you can rollback a truncate table command.


3

You need better indexing, and a bit of a rewrite. Because you’re only using one table, you can use ROW_NUMBER() in a sub-query and then grab the top row from each partition. SELECT grp_fk_obj_id, grp_name, grp_id FROM ( SELECT grp_fk_obj_id, grp_name, grp_id, ROW_NUMBER() OVER (PARTITION BY grp_fk_obj_id ORDER BY date_from DESC, ISNULL(date_to, '4000-01-...


3

You can use a plan guide. What type of plan guide to use depends on whether the query is in a proc (OBJECT) or not (SQL). Example: sp_create_plan_guide @name = N'myGuide', @stmt = N'SELECT col1, col2 FROM MyTable', @type = N'SQL', @module_or_batch = NULL, @params = NULL, @hints = N'OPTION (RECOMPILE)' Documentation for plan guides here: https://docs....


-1

This answer is very similar to Dan Guzman's, but it does differ slightly. RDP into an always on box for which you have rights and submit for execution from there. Always on box can then raise some sort of semaphore or do some further processing. Use Task Scheduler if you have to have a client submit request and then you don't even have to RDP anymore. ...


1

If you can bear to have a function to supply functionality not (yet) built into the engine, you can create this function which returns an ordinal position alongside each value (there are dozens of different ways to write this splitting function): CREATE FUNCTION dbo.SplitCSVInOrder ( @List nvarchar(max) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ...


3

Free Edtions SQL Server 20xx Express Editions SQL Server 2019 Express Edition is free for production, non-production, and redistribution. After install you'll find the license terms at C:\Program Files\Microsoft SQL Server\150\License Terms\SQLServer2019_License_EXPR_1033.rtf SQL Server 20xx Developer Editions If you install the free SQL Server 2019 ...


1

This question isn't particularly suited for the dba.se site, for a couple of reasons. It's a little vague still, and it's gonna be outdated before a while. It's not good for StackOverflow for the same reason. here's a good resource for knowing what sort of things we're good at answering here and on SO. All that being said, there's always room for real-world ...


0

The answer goes way back to relational theory, and the idea that column values should always be scalars. Arrays, structs, and nested result sets can alternatively be modeled as separate tables, and normally should be. It's baked right in to the First Normal Form. It turns out that this is not really a great rule, especially for query results, where having ...


1

Simple Way This ignores different types of Products and proper normalization of elements that can have multiple values. If you do normalize, you'll need to pivot to make things work for this. To do this, create three entities LabelTemplate, LabelTemplateLine, and ProductColumn. ProductColumn is just a list of available columns from Product that can be ...


4

Not only must all memory-optimized data be loaded into memory before any tables are available in the entire database, but because non changes to indexes on memory-optimized tables are logged, all indexes must be recreated on all memory-optimized tables as well. That's why it's critical to have fast storage, and spread your containers across multiple volumes. ...


2

All salient information is covered in the documentation under Restore and recovery of memory optimized tables. Notably, But unlike disk-based tables, memory-optimized tables must be loaded into memory before the database is available for user access. and Loading memory-optimized tables into memory can affect performance of the recovery time objective (RTO)...


7

I think it is related to a new and underreported bug in Azure SQL Database (only), where Temporal Tables are treated different from normal tables, when the errors 245 and 2628 occur.


15

As the answer referenced in your question indicates, SQL Server will cancel the executing query and rollback the transaction when it detects the network connection has terminated. A SQL Server session needs a connected client to query return results and messages (informational, warning, and error messages). You have a few options to maintain the connection/...


11

Not directly using normal tools (SSMS etc.) and access libraries - if you disconnect progress should stop and any active transactions will get rolled back. You could run the statements via an agent job set to start immediately, assuming that this is configured and your login has sufficient privileges.


2

NVARCHAR is always UTF-16. You can try Latin1_General_100_CI_AS_SC_UTF8 with a VARCHAR column, but that doesn't look like an encoding problem. It really shouldn't matter what the source encoding / collation was as long as you correctly indicate the encoding used to transfer the data. Either way, those 3 bytes / characters could be a bug in the import tool/...


0

After debugging the function using : DECLARE @EXECUTION_STRING2 VARCHAR(1000) ='echo '+@ZoneIds +' >> C:\Users\Public\debuginfo.txt' EXEC xp_cmdshell @EXECUTION_STRING2 The probleme come from the type of @ZonesIds, after changing the type from VARCHAR(1000) to NVARCHAR(max) everything work as expected.


2

My production experience with columnstore indexes is on SQL Server 2016 and later versions but I believe everything in this answer also applies to SQL Server 2014. The simplest answer is that you can look at the ratio of row count and space used by each partition if you prefer to not use the sys.column_store_row_groups dmv. The more complicated answer is ...


0

It can make sense to do so, yes. Sometimes a business requirement will need to be enforced via a primary key or a unique constraint. Both of those result in an underlying index on a table with a clustered columnstore index. A clustered columnstore index isn't a good data structure for some types of queries. Examples where extremely poor performance are ...


0

I had a similar issue - for me it was because the foreign key index was marked as non-unique. Presumably the query needs to do a scan to find multiple rows that match the foreign key, rather than just one? Anyway, updating the foreign key's index to be unique changed the delete from being a scan to a seek for me.


0

You can follow this tutorial. https://lalitvc.wordpress.com/2016/12/28/mssql-to-mysql-data-migration-using-mysql-workbench-6-3 I think, the problematic step for not copying data is in "Data Migration Setup". Please select "Online copy of table data to target RDBMS". Hopefully, your problem will be resolved.


1

I've ran into the same problem after restoring the SSISDB database from a lower SQL version to a higher SQL version; I've ran into: SSIDDB Upgrade not working or erroring out on the "sql version doesn't match" Validating packages erroring on customized_logging_levels not existing all other errors one can encounter... I noticed the newer SSISDB ...


-3

Adding on sql Agent commang job SET TEXTSIZE 2147483647; exec (your proc name) works


1

Thanks Tibor. This is what I ended up with and it fits the bill. use CMRBE CREATE UNIQUE NONCLUSTERED INDEX idx_SupplierID_DocumentID ON Expense(SupplierID, DocumentID) WHERE DocumentID <> 'NA';


4

Sounds like a simple group by: select tagname, count(*) from alarmmaster where tagname like '%Sensor%' or groupname like '%Sensor%' group by tagname;


5

The difference is caused by noise word "W". By default SQL Server uses stoplist to exclude some short or frequently used words like "and", "does", "could". You can see the full list using such query: select * from sys.fulltext_system_stopwords where language_id = 1033 And you can disable the stoplist for your full ...


0

I checked if in other enviroments the same query on dm_tran_active_snapshot_database_transactions returns the same results and no! It isn't normal having DB STARTUP snapshot active from days. I checked the errorlog of 13 days ago and I found errors about an unexpected shutdown and failover. So I tried to remove the database with DBID 7 from the availability ...


0

To avoid having to change the application in all the different places where we use this type of queries and because of high urgency the customer agreed to enable the old cardinality estimator after which all problems were solved instantly: ALTER DATABASE SCOPED CONFIGURATION SET LEGACY CARDINALITY ESTIMATION = ON; I want to thank everyone for contributing ...


6

Main idea: WITH cte AS ( SELECT UserId, CheckTime, LEAD(CheckTime) OVER (PARTITION BY UserId ORDER BY CheckTime) NextTime, CheckType, LEAD(CheckType) OVER (PARTITION BY UserId ORDER BY CheckTime) NextType ) SELECT UserId, CheckTime, NextTime FROM cte WHERE CheckType = 'I' ...


2

It is indeed in the execution plan, for those stats that were used. There are two possible reasons why you don't see it: You use an older version of the database engine, where this info wasn't present in the XML. You use an older version of SSMS, which doesn't expose the info in the XML. The answer to which of the above you have is by looking in the XML ...


0

I know this question is old. But here's my solution. It worked for me every time: Open C:\Windows\System32\drivers\etc\hosts with Notepad Add this line to it 127.0.0.1 SomeNameHere Save (make sure it's saved by reopening it) Now connect to SQL Server Management Studio, using SomeNameHere


2

An execution plan was not included, but the typical issue with queries like this (sorting aside) is the optimizer choosing a nested loops anti semi join without a good supporting index. It can also be a rogue top (1), or a poorly-performing transformation to a semi-join with nested start-up filters and an anti-semi join. Regardless, there are two usual ...


3

You can create a unique index with a where clause to filter out those values for which you want to avoid duplicates.


7

Is it possible to construct a query where there is a difference between those two trees? No. The sequence of events is: The Join-collapsed Tree is printed immediately after Heuristic Join Reordering. Memory usage before project normalization is printed (if enabled). Normalize Projects is called. If project normalization has been disabled, it exits. ...


5

The behavior you are seeing (i.e. meta-data being replaced by *** in error messages for non-sysadmin / non-dbo accounts) appears to be the effect of trace flag 3625: Metadata Visibility Configuration. I don't have an Azure account to test with, but the documentation does indicate that this feature is available in Azure SQL Database. A far less likely ...


1

Start by using DATE datatype not DATETIME since you don't care about the time at all. And you make a very common mistake about the maximum value for time - it varies depending on datatype but "23:59:59" is not the upper limit for datetime. It appears you should check the day of the month first to see if it is 1 - so start with DAY(). Then check the ...


3

Your query is probably very simple and does not require any memory grant. It is trivial to reproduce: See Understanding SQL Server memory grants for more background.


9

The answer depends on what isolation level your .NET project is setting when it accesses SQL Server. If it's READ COMMITTED (the default), then there's not really any additional overhead (other than the additional BEGIN TRAN and END TRAN statements being executed). You might think additional information would be written to the transaction log, but ...


0

DR solutions are not built around existing infrastructure, they are built around business requirements. Define your business need for DR and build a system that supports that need. P.S. You need to build a new system anyway. SQL 2012 is out of mainstream support and has less then 24 months before end of extended support (July 12, 2022).


6

If the procedures are being called, you should absolutely see the parameter values in the 'statement' column. I have a blog post that demonstrates exactly this issue. There's literally nothing magical to make this happen. It's just a part of the statement. Now, to make the statement appear in the grid in the Live Data Viewer, you can either right click in ...


3

The documentation for sys.allocation_units is incorrect, it seems. It states that data_pages is number of used pages and it should include LOB pages. LOB pages are pages for data types such as the (MAX) types, XML, geospatial and columnstore data. But, looking on my machine (SQL server 2019), I see 0 data pages for LOB data. If you remove the GROUP BY and ...


0

I think you'd need to use INSERT INTO .. FROM OPENROWSET(BULK ..) DECLARE @CustId BIGINT = 0; INSERT INTO dbo.Customers (Name, Address) VALUES ('xxx','yyy'); SET @CustId = SCOPE_IDENTITY(); INSERT INTO dbo.Addresses (CustomerID, Name, Address1, Address2, City, Phone) SELECT @CustId, Name, Address1, Address2, City, Phone FROM OPENROWSET(BULK 'xxx'); ... or ...


8

You have three main problems: There is no useful index to support the join on id. The TOP (100) introduces a row goal, so estimations may be too low. The UPDATE is non-deterministic. Multiple rows from table2 could match on id, so it is not clear which matching row from table2 should be used to provide values for the OUTPUT clause. The aggregate is there ...


0

Try with a different driver? SQL Server and PostgreSQL Linked Server Configuration You might also want to try implicitly casting the columns to tsql datatypes, i.e. SELECT CAST(Id AS INT) AS Id, CAST(old_message AS NVARCHAR(MAX)) AS old_message, CAST(message AS NVARCHAR(MAX)) AS message FROM OPENQUERY...


1

There is a user "Guest" in each database. This user gives some permission for everyone. My guess is that for the database where you can "Expend" the DB, your "guest" user in those database is probably enable and probably have the "Connect" to the database securable (and I hope not more then connect) To disable that ...


0

The solution was to use initialize from backup option and to import a database dump from the publisher on the subscriber. Enable initialize from backup option in publication Create database dump from publisher Recover database dump on subscriber On publisher create subscription by T-SQL USE [yourpublishedDB] EXEC sp_addsubscription -- name of publication @...


-1

it worked for me only when i started service using command line net start <service> /mSQLCMD then sqlcmd -S. -E


10

It looks like this table was defined with dynamic data masking, and the user that the application uses to access the database doesn't have permission to view masked data (which is good!). This is why the behavior differs between the application and SSMS: I expect you're using a higher-privileged user when running the query from SSMS Here's a demo: CREATE ...


Top 50 recent answers are included