Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

New answers tagged

1 vote

Understanding read uncommitted isolation level's relation with clustered index and page splits

Awkward wording. It means you might miss rows even though they were not the target of a change by another concurrent process. Yes, though DELETE can also cause a page split. MERGE too. Heap tables do ...
user avatar
  • 69.4k
-1 votes

Does the order of columns in a columnstore index matter?

Ordering is significant for columnstore indexes on large tables since it helps to ensure good segment elimination. (See the following for a good example: https://www.red-gate.com/simple-talk/databases/...
user avatar
0 votes

stored procedure runs fine in all sessions but one

I wish I had more rep so I could just leave a comment on Marcello's answer... But I would recommend against the global temp table as they're often a security risk since any session can access it's ...
user avatar
0 votes
Accepted

stored procedure runs fine in all sessions but one

I found a way out of this problem. I could not find out nor took the time to understand why exactly only one session was having this issue. This procedure of mine is packed with dynamic sql, and it ...
user avatar
0 votes
Accepted

Why is my auto-restore procedure not dropping the databases when I expect it to?

Several years later, I figured this out. It's one of those facepalm moments; one simple missed command caused me to look into this issue several times over the years, but it was never a high enough ...
user avatar
  • 227
5 votes
Accepted

Reading from table being created in transaction

No, I'm afraid not. Creating a table requires a schema modification lock, which isn't with compatible anything, not even a schema stability lock which is used when doing read uncommitted. You most ...
user avatar
0 votes

SQL Server is terminating this session. c0000005 EXCEPTION_ACCESS_VIOLATION

I would suggest you open case with MS. Before opening the case please make sure you are on latest SQL Server 2019 CU. This may be a bug fixed in CU release. - Shanky And in response from the OP: My ...
user avatar
6 votes

Detail on MS SQL CTEs

Main Difference The main difference between Common Table Expressions and temporary objects is that CTEs do not materialize a result set, and every time you reference them the inner query needs to be ...
user avatar
  • 33.5k
0 votes

Backup on Distributed AG setup

Generally you would set this on the AG Group itself. By allowing backups to happen on any replica you should then be able to.
user avatar
5 votes

How to get call stack info in SQL Server's catch block?

I believe it's still correct. The recommended way is to rethrow the errors to the application via the THROW command and let the app handle it. The best resource is still this article by Erland ...
user avatar
  • 1,130
0 votes

Issue with idle connections/open transactions from Python to SQL Server

The driver used for the database connection should have connection settings that govern transaction behavior. The pyodbc driver for python, for example, has the “autocommit” setting. With autocommit ...
user avatar
  • 1,158
2 votes

How do I create a SQL loop that generates future dates based on different frequencies and intervals for each item until a specified end date?

I recommend using a Calendar table. Aaron Bertand has a blog series about them For this code sample, I'll be using my calendar table. It's quite a bit of code so this is a link to my GitHub For the ...
user avatar
  • 1,130
1 vote

DBCC CHECKDB getting terminated abnormally

DBCC CHECKDB terminated abnormally due to error state 6 The error state 6 means process is killed or terminated by external process at least as far as my experience goes, this is not documented ...
user avatar
  • 18.3k
5 votes

Which execution plan is stored in the plan cache?

Just for reference a would like to add this to the great Paul's answer. This is the microsoft doc about execution plans: https://docs.microsoft.com/en-us/sql/relational-databases/performance/execution-...
user avatar
  • 3,427
2 votes

Detail on MS SQL CTEs

CTEs, Views, Subqueries, and Inline Table-Valued Functions don't specify the order of processing. The query optimizer operates on the complete query as submitted by the client, and optimizes it as a ...
user avatar
0 votes

Always On Availability Groups connection with secondary database established for primary database

I'm seeing this messages almost everyday in one of the production server. I don't feel comfortable seeing this..is there anything we can do to suppress this messages ? These are informational ...
user avatar
0 votes

Finding the maximum values of a group count

DECLARE @content TABLE ( title VARCHAR(10) NOT NULL, topic VARCHAR(10) NOT NULL, id_user INT NOT NULL ); INSERT INTO @content (title, topic, id_user) VALUES (N'Title 1', N'Topic 1', 1), (...
user avatar
13 votes

Which execution plan is stored in the plan cache?

Yes, it is correct, though the terminology used isn't perfect. The terms 'estimated' and 'actual' are a convenient way to distinguish between a plan without runtime statistics ('estimated') and one ...
user avatar
  • 69.4k
0 votes

Backup on Distributed AG setup

from the link you provided: BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it's executed on secondary replicas. Copy-only backups don't impact the log ...
user avatar
  • 3,427
1 vote

Can transactions on main table be delayed/deadlock when maintenance transaction on temporal table runs?

If you are scared about locking you can always delete those rows in a loop preventing table lock DECLARE @Rows INT SET @Rows = 1 WHILE (@Rows > 0) BEGIN DELETE TOP (4500) FROM myTable WHERE date &...
user avatar
  • 3,427
3 votes
Accepted

How to allow remote MSSQL user to bulk insert my local txt file?

As mustaccio pointed out, a local path is not going to be accessible from the SQL Server, which is the error you're receiving regarding "D:/My_Files/my_file.txt. It's looking for a file on the D: ...
user avatar
  • 24.3k
2 votes

Remove results if id has specific value with multiple values

Since you didn't include sufficient details in your question, I can only give you a basic idea how that should work: select * from yourtable x where not exists ( select 1 from yourtable y where ...
user avatar
  • 21.2k
0 votes

How to convert data separated pipe to rows?

The problem here is that you have violated 1NF by storing multiple values in a single tuple. You are going to have to parse this into rows. You can use the built in STRING_SPLITfor this. select * from ...
user avatar
5 votes
Accepted

Storage space used for NULL values

Some people say that the overhead for having nullable column is 1 bit per row, some that it is 1 byte per row Which one is true ? I assume is 1 bit per row (so 8 rows make 1 byte), am I right ? The ...
user avatar
  • 24.2k
2 votes
Accepted

GRANT ADMINISTER Database Bulk Operations - Incorrect syntax near ADMINISTER

The documentation says "ADMINISTER DATABASE BULK OPERATIONS Applies to: SQL Database." In the context of the docs "SQL Database" is Azure SQL DB. Not the SQL Server running on ...
user avatar
  • 14.4k
5 votes
Accepted

How to get `dbcc checkdb` report when run via agent job?

You want the output from the DBCC CHECKDB command how? In the job history (sysjobhistory in msdb) as you can open in SSMS? As a text file? Included in an email sent to you? For 1, this is already ...
user avatar
-1 votes

Logging Which Columns Have Been Read Or Written To

Not exactly what you are looking for but might help: Have a look at Brent Ozars sp_BlitzIndex: exec sp_BlitzIndex @DatabaseName='AdventureWorks2012', @mode=2; GO It will show you date and ...
user avatar
  • 31
1 vote

Check auto-numa is enabled

Just enough information for me to guess what is going on, not quite enough for me to be certain my guess is correct :-) Here's my guess at a few details, then an explanation of the outcome based on ...
user avatar
  • 1,158
1 vote

How to estimate cpu based on peak rate of query requests?

@AMtwo is right to point out that number of concurrent requests - without additional information - doesn’t usually work well for system sizing. For a given workload on a given system, cpu utilisation ...
user avatar
  • 1,158
0 votes

How to get the specific queries that are contributing to high lock wait times?

Identifying SQL Server queries contributing to long lock waits and queries with long lock hold times that are risks for long lock waits. I’ll give you the most frustrating answer ever: it can’t be ...
user avatar
  • 1,158
1 vote

How to get the specific queries that are contributing to high lock wait times?

You can see these articles. It describes the locking process in the SQL server: Techniques to identify blocking queries and causes of SQL Server blocks Determine Which Queries Are Holding Locks ...
user avatar
0 votes

SQL Server 2019 Cannot Change Password for sa

I was going through the same problems as you did. I loved Hannah's explanation and directions but after I turned my server into a single-user mode on step 5 I took a slightly different approach: 6) ...
user avatar
  • 1
-1 votes

Alter role shows role does not exists

you are not alone. it's typical MS complexity unfortunately ;-) here's my quick ref for a normal user for an App to connect. ------------------------------------ -- STEP 1 create an auth login using ...
user avatar
10 votes

Is there a deterministic relation between the page life expectancy and disk IO throughput?

It’s pretty far off the mark to suggest such a direct relationship between database data file bytes/s throughput - which can be measured directly over a period of time - and SQL Server page life ...
user avatar
  • 1,158
1 vote

Sql Server Large amount of unused database users and logins

If you're not disabling these LOGINs afterwards, it gives someone malicious more avenues for access. – Larnu Also we have found that the more logins that exist, the longer login creation takes. Which ...
0 votes

CREATE LOGIN for AD user in master not allowing connection

You will need to change the containment option first before you can create a contained user in Azure SQL Managed Instance. It is done by default for the Azure SQL database. Azure SQL Managed Instance ...
user avatar
  • 12.2k
0 votes

Can't identify which server is primary and which is secondary in a SQL Server Availability group

Use sys.fn_hadr_is_primary_replica. - Sean Gallardy
3 votes
Accepted

Does selecting data from heap (table without clustered index) with nolock block writers?

Does selecting data from heap (table without clustered index) with nolock block writers? Only writers performing a bulk load*, as explained by Paul Randal in the link provided: The BULK_OPERATION ...
user avatar
  • 69.4k
-1 votes

very simple amateur question. Build a table then do group_concat

You want to learn about WITH. You can combine the two queries by adding WITH appQuote as (<select statement from your first script>) <select statement from your second script>. Whenever ...
user avatar
6 votes
Accepted

Temporarily turn on Cascade Delete

SQL Server doesn't let you ALTER CONSTRAINT to alter an existing constraint to cascade on delete. However, SQL Server will happily let you create identical Foreign Key constraints. So rather than ...
user avatar
  • 14.4k
1 vote

SQL Server Management Studio throws exception when using Always Encrypted

To resolve the problem, ensure that the user used to login, check key vault -> access policy -> key permissions for the user and be sure to uncheck "Release","Rotate","...
user avatar
0 votes

Temporarily turn on Cascade Delete

There is no syntax for that, I'm afraid. I.e., there's no "ALTER CONSTRAINT" in SQL Server. You can ALTER TABLE and DROP CONSTRAINT and then ADD CONSTRAINT to add the new one. If you feel ...
user avatar
0 votes

How can I export Query Store data?

I wonder if there is a way to use the clone database option and some classic jiggery pokery to keep the data for analysis in the QS GUI. Clear down QS in restored DB in test environment Run baseline ...
user avatar
7 votes
Accepted

How to estimate cpu based on peak rate of query requests?

There's no way to estimate core count based on number of requests. 500 inexpensive queries may execute concurrently in a few milliseconds of CPU time or could take several hours of CPU time. Depending ...
user avatar
  • 14.4k
0 votes

Can't identify which server is primary and which is secondary in a SQL Server Availability group

One way you could approach this is to utilise the function DATABASEPROPERTYEX with the property 'Updateability'. For example: DECLARE @STATE VARCHAR(100); SET @STATE = (select CAST(DATABASEPROPERTYEX('...
user avatar
  • 1
0 votes

SQL query to return rows whose column has a common value

WITH sameProfiles AS ( SELECT COUNT([instNum]) cnt , [instNum] FROM ( SELECT distinct [instNum] , [Profile] FROM myTable1 ) q ...
user avatar
0 votes

Does max SQL memory include SQL AG memory requirements?

I believe that memory for the cluster and AG are separate from the 94GB max sql memory setting. For AG memory would come from max server memory. SQL Server max server memory controls The SQL Server ...
user avatar
  • 18.3k
0 votes

Split intervals

There are a lot of interesting solutions to this problem (stated in different terms) here and its preceding pages. There it is presented as matching supply and demand in an auction. The units supplied/...
1 vote

DB STARTUP command active for an online database

Just in case someone finds this, each database in an availability group has a redo thread, and the command is always DB STARTUP. In fact, that is how you can identify the redo spid associated with ...
user avatar
  • 11

Top 50 recent answers are included