sql-server – World Of Nubcraft

Prevent THREADPOOL waits due to idle worker thread trimming

After reading Unusual THREADPOOL Waits by Josh Darnell, a Twitter user mentioned there is an undocumented trace flag to prevent trimming idle workers:

The idea is that once SQL Server has created enough threads to service the peak workload, it should not then trim worker threads (release them to the OS) after 15 minutes or so of them not being needed.

The idle worker threads will continue to use resources (e.g. memory) but there will not be the burst of THREADPOOL waits when more workers are suddenly required. Apparently this can be of assistance when using Always On Availability Groups.

What is this undocumented trace flag, and how does it work?

Go to Source
Author: Paul White 9

Is this SP safe to SQL Injection?

CREATE PROCEDURE [sp_Test] (
     @param nvarchar(Max)
) AS BEGIN

DECLARE @Output nvarchar(Max) = 
N’Select ‘ + @param

Select @output
Return

Intended Use

exec sp_test ‘5’

Returns “select 5”

Malicious Use

exec sp_test ‘5; drop database’

Returns(would be safe):
“select 5; drop database“

—-OR—-

Returns(not safe):
“select 5”
…but also actually dropping the database

MS SQL Server

Go to Source
Author: Donnie

What database and cloud service should I use for an Instagram-like app?

I am a beginner in the world of databases and I’m so confused with the amount of tools available. I am building an Instagram like app in Flutter and wanted to know what database should I study for it?

And which cloud service should I use, AWS or Azure?

Go to Source
Author: Aeden Thomas

estimate Log growth in SQL Server

I would like to estimate the log growth consumption for a database with full recovery model for a particular transaction/group of transactions which is filling up my log drive. I would like to forcast this before a transaction is run.Is there a way to find this out in SQL Server(any version)

Thanks in advance.

Go to Source
Author: kevin

SQL Server Unique Constraint on two columns with an exception

Hi all and thanks for your advice.

Expense(SupplierID(Foreign Key), DocumentID(vchar))

I understand how to add a simple unique constraint on two columns. However, if DocumentID = ‘NA’, I would like to ignore the rules of the constraint.

Some suppliers in our system do not provide an invoice id, for example. Therefore, I leave the field NULL. I would like to remove all nulls for the field ‘DocumentID’ to avoid accounting for the NULLS in my client code.

I am new to SQL Server, but I could figure out how to do this using a trigger. The reason I’m asking here is to see if there is a better way to respond to this scenario or to avoid it by a different design.

Thanks!

Go to Source
Author: Tom Schreiner

Active Directory Name Change

Our Active directory will change the accounts name for example John Doe Jdoe@abcd.com to John.Doe@abcd.com.

My question is do I need to change all the SQL Logins individually or the person can login to SQL server management studio with their old names. What also happens if the login is an owner of a database or job.

Go to Source
Author: SQL_NoExpert

How to Overcome a MYSQL Temporary Write File Failure

If you’re upgrading your website and you keep getting a “Temporary write file failure” when you run a website upgrade script and you know you did everything right, what can be done inside of your PHPmyAdmin MYSQL dashboard to make the upgrade script work? I’ve enabled necessary file permissions and the upgrade script is still saying I have a MYSQL temporary write file failure.

Anyone ever experience this before and overcame it? If so, what steps did you take to overcome it?

Go to Source
Author: DNN

Differences in the queries

How do i explain the following different queries like i am teaching a 5 year old kid?

 i) SELECT suser_sname(owner_sid) as 'Owner', state_desc,name as DatabaseName, database_id FROM sys.databases
 ii) SELECT * FROM sys.server_principals
 iii) SELECT * FROM sys.sysusers
 iv) SELECT * FROM sys.sysmembers
 v) SELECT * FROM sys.databases
 vi) SELECT * FROM SYS.SYSLOGINS

Go to Source
Author: teo93

Secondary replica database file location in Availability Group

I inherit a setup of two nodes (primary & secondary replica) Availability Group.

On my secondary replica node, the SQL server instance default files are stored at:

My default primary replica node files are as follow:

When I look at the synchronizing database’s properties on my secondary replica, I see its database files as follow

They are primary replica node directories. Is this how it works in Availability Group?

That is when you look at the file group of your secondary replica database, they show the paths of the primary replica database even though the data file and log file are stored at a different local directory?

Go to Source
Author: Fylix

My question is in a select statement how do you return the RID along with the table data?

I need your help with a concept I want to show developers, that concept is to help them understand how SQL Server retrieves data from the table. In the event that a developer does not use a clustered index on a table I want to be able to show them the RID (Row Identification number) SQL Server uses to find the record. So my question is in a select statement how do you return the RID?
I know that you can use the following to find where SQL Server stores the data in the data file.

SELECT TOP(100) sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot], *
FROM  [dbo].[Badges] ;

SELECT   TOP(100) *
FROM   [dbo].[Badges]
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%);

My question is in a select statement how do you return the RID along with the table data?

Go to Source
Author: Andy Eggers