New answers tagged sql-server
0
votes
Difference between Backup and Snapshot
Since this can concern filesystems, databases, version control systems, and other things, I'll just use the term "data" for whatever it is you're backing up or snapshotting, and "system&...
- 811
1
vote
Access to folder is denied (log shipping)
Copy job failed with the following message:
Error: Access to the path '\WIN-T97MHK9GED1\Primary' is denied.(mscorlib) ***
I changed the user account of SQL Server Agent service to .\Administrator on ...
- 11
-1
votes
query bad performance
There is also one more issue here (dbo.Templates.Status <> 8).
Do not use inequality operator <> in where clause.It scan and validate <>8 on each row of table or index.Instead use = ...
- 1,611
5
votes
Difference between Backup and Snapshot
You have to be very specific about the terminology used.
SQL Server Database Backups
Backing up your SQL Server database is essential for protecting your data. This discussion covers backup types, ...
- 17.4k
2
votes
Difference between Backup and Snapshot
Just want to point something for snapshot usage. Both snapshot and backup will let you recover database when needed but the ways that they are stored and used are quite different.
Backup, I mean full ...
- 46
1
vote
SQL Database MaxSize higher than total drive capacity/space
Max size is not a requirement. I guess you are confusing file size with max size. File size is reqired. You can shrink the file before backup. Or restore where there is space, shrink and then ...
- 17k
0
votes
Error updating temporal tables
Well -- it's still a problem in SQL 2019. But I did find one workaround: If you don't need explicit transactions, avoid BEGIN/COMMIT TRAN, and wrap the update logic using TRY/CATCH.
--Run first query
...
- 11
3
votes
Difference between Backup and Snapshot
Everything is made in a point in time. That's just how physics work. Unless you live in a black hole where time does not exist or something. That phrase just makes no sense to me. The back up is made ...
- 35.2k
-4
votes
why is this left join faster than an inner join?
the main issue is query optimize which follow its own way (believe its the most efficiency way). the easy solution is to put "option (force order)".
4
votes
Automatically fetch USD Exchange Rate from API and Update it in Microsoft SQL Database
The documentation for sp_execute_external_script says:
Use sp_execute_external_script to execute scripts written in a supported language. Supported languages are Python and R used with Machine ...
1
vote
Cannot Connect (Internal connection fatal error. Error state: 15, Token : 23 (Microsoft.Data.SqlClient)
Some people seem to report this is an issue with specific updates of .NET on Windows. Does your computer have this KB5011048 installed?
Please see this potentially related DBA.StackExchange.com answer:...
- 35.2k
10
votes
Why is SQL server dropping the seconds of values passed to a SMALLDATETIME field?
The documentation you've linked to says explicitly that seconds for a smalldatetime is always zero:
Defines a date that is combined with a time of day. The time is based on a 24-hour day, with ...
- 9,954
-3
votes
How to replicate jobs between nodes with SQL Server 2022
not possible to do. logins and agent jobs are instance level are not covered by Availability groups.
- 1
3
votes
Accepted
How to allow predicate pushing on a view that uses group by
SQL Server is very keen to rewrite apply as a join before optimization begins. It is quite good at it too. It is less good at transforming a join to an apply, which is what you want here.
As a result, ...
- 83k
2
votes
MSSQL Server 2022 on Linux: sys.* tables very slow for non-db_owner users
We are able to reproduce the issue in SQL Server 2022.
SQL Server 2022 CU7 (48 cpu)
For our trouble database, lots of permissions
Run as 'domain end user'
SELECT * FROM sys.database_permissions
...
- 31
1
vote
Query against sys.schemas and sys.synonyms runs very slow for one user
We are able to reproduce the issue in SQL Server 2022.
SQL Server 2022 CU7 (48 cpu)
For our trouble database, lots of permissions
Run as 'domain end user'
SELECT * FROM sys.database_permissions
...
- 31
0
votes
How can I tell BCP to add headers to an output text file?
I am running this code:
[typical BCP query]
only return data,how can I get headers in my file?
There's a few solutions to this problem. One simple way is to hard-code them with a UNION ALL query ...
- 35.2k
2
votes
How can I check if connection to Sql Server is encrypted?
You can also check whether the connection in encrypted in the SSMS, when you connect to a server with encryption, you will see a lock icon next to the server name in the bottom panel:
Without ...
0
votes
Can't connect to named instance from remote server
SQL Browser does not redirect traffic from UDP 1434 port to SQL Server instance. It just sends information about TCP/IP port or a named part of instance client is trying to connect to, as an answer to ...
- 721
0
votes
Can't connect to named instance from remote server
I ended up changing the named instance to use a static port instead of a dynamic port. Then I changed the AGL to use the static port and I am now able to connect using the ServerName/NamedInstance ...
- 573
1
vote
Database Log File Growth change does not reflect on secondary replica sys.master_files
Restarting the node hosting the secondary replica will fix any file size inaccuracies found in the secondary node master's database sys.master_files system view. - Confirmed on an instance running on ...
- 111
0
votes
Why Are Many Open Delta Rowgroups Created On A SQL Server Columnstore Table When Simultaneously Trickle Feeding Rows And Running Select Queries?
The solution I arrived at was to use WITH (TABLOCKX) in the trickle insert statement.
It is yet to be seen if there are any longer term implications of doing this but it has solved the problem at hand....
- 109
3
votes
Accepted
What is a "statement batch"?
A batch of SQL statements is a group of two or more SQL statements or a single SQL statement that has the same effect as a group of two or more SQL statements. In some implementations, the entire ...
- 4,260
3
votes
Accepted
SQL: How to look within each "cluster" value in one column and return those that ONLY have one specific value in another column
What I'm now looking for is, grab me all clusters where only one domain value exists.
Another way to express what you want is:
Select all match clusters
From the table or view
Where the match ...
- 83k
2
votes
SQL Server for Linux 2019 - Windows Authentication not working
For anyone coming here looking for the solution, it is all about the keytab file.
This is a good tutotial
https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?...
1
vote
Accepted
Is there a keyboard shortcut to open a registered server?
If you have the "right-click" key on your keyboard, you can use it, followed by E.
It's often called the "Menu" key, so Menu, E.
If you don't have that key, you can use Shift+F10 ...
- 1,533
5
votes
Accepted
HADR_SYNC_COMMIT waits on SQL Server
I wanted to say kudos for diving in and investigating using XE, that's definitely a correct step in the eventual solution. Having said that, it's unlikely we're going to be able to directly help in ...
- 30.1k
3
votes
HADR_SYNC_COMMIT waits on SQL Server
As far as I can see, this is happening because your alwayson AG is synchronously redoing the index reconstruction on the secondary and this takes time. These waits describe exactly this situation.
If ...
- 4,260
2
votes
Building a web project for my company using asp.net and having a concern about the storage size of SQL Server Express edition
In my company we have around 850 employees.
Bananas are my favorite fruit.
We are going to build ASP.NET web project to handle many day to day procedures.
I eat dessert a few times a week.
My ...
- 35.2k
8
votes
If RAISERROR is as good as deprecated, how are you supposed to flush the PRINT buffer?
lots of things are deprecated
Which for Microsoft just means they're not actively being developed any further. None of that stuff ever really gets pulled from the product though.
But RAISERROR is not ...
- 38.9k
-2
votes
sys.dm_exec_sql_text returns NULL in dbid, objectid, and number columns, but not text
The db_id of the current database. If the code does NOT specify the database which database would get used.?
1
vote
What is a difference in failover time for SQL Server FCI when database is in Full vs Simple recovery mode
If the long failover is due to excessive restore time, consider changing the target recovery time of the database or configuring Accelerated Database Recovery. - David Browne - Microsoft
2
votes
Accepted
What is a difference in failover time for SQL Server FCI when database is in Full vs Simple recovery mode
Would changing recovery model and/or increasing compatibility level reduce failover time of the cluster?
Generically, no, not really. Recovery has to start from the oldest checkpoint (some changes ...
- 30.1k
0
votes
Accepted
What indexes should I put on this table, given this illustration of its contents and the query I will be running on it?
I'm not convinced that this query is the one you should be running in the first place.
It's quite obviously generated by some tool like Access, and could do with a good dose of window functions.
...
- 11.8k
2
votes
Accepted
coalesce more than 1 column in sql server
Looks like you need an OUTER APPLY
select
wi.productSerial,
wi.productName
isnull(pp.price, ppe.price) as baseprice,
isnull(pp.description, ppe.description) as description
from warehouseItems ...
- 11.8k
0
votes
coalesce more than 1 column in sql server
The COALESCE() function accepts more than two parameters. For example, the following is valid syntax:
SELECT COALESCE(Expression1, Expression2, Expression3, Expression4, Etc)
Not sure what you're ...
- 35.2k
0
votes
SQL Server Extended Event Session for Attention is missing field sql_text
If you want to track query timeouts, I found this Extended Event to be more useful:
CREATE EVENT SESSION [QueryTimeouts] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
...
- 516
1
vote
Understanding last_request_start_time and last_request_end_time in sys.dm_exec_sessions
Those columns are not necessarily tied to the same request in a session.
The very first request of a session will have start & end times for the same request, but not subsequently.
On the 4th line ...
- 256
9
votes
Accepted
Why are these columns specified in the include clause of an index rather than as key columns
Why oh why oh why
Because the missing index recommendations kind of suck, that's why.
The only columns that will end up in the key of an index recommendation are columns in the where clause. Other ...
- 38.9k
1
vote
Using table valued function as a table in sql server join
While it is possible for the compiler to remove some unnecessary parts of a query plan against a view or inline TFV, it can't remove everything.
Looking at your TVF, the following are going to cause ...
- 11.8k
1
vote
What could create block in that case when 'blocking' procedure/spid not do any select / almost nothing?
buttery
You'd probably find sp_WhoIsActive a better source of information that any of the built-in sp_who procedures.
Here's an example command that will order the output by which sessions have the ...
- 38.9k
0
votes
Why sql server prefer index scan over the seek?
Why sql server prefer index scan over the seek?
I will try to explain in simple and basic term.
uniqueidentifier are 16 bytes.Therefore index on the uniqueidentifiercolumn uses quite a bit more space....
- 1,611
0
votes
how sorting nullable number and ones is last
You can use CASE in the ORDER BY clause itself without using additional columns if you can specify an arbitrary value like 9999 which will be bigger than any other values in the column.
select *
from ...
- 13
1
vote
how sorting nullable number and ones is last
I defined a column named [Ones] and sorted the 1's to the end of the table and the problem was solved
SELECT [Id]
,[Value]
,[Date]
,(case WHEN Value = 1 THEN 1 ELSE 0 END) AS [Ones]
FROM TABLE ORDER ...
- 11
0
votes
Difference between logical and physical reads/writes
As a DBA's stand point we don't need to worry about logical read; it's our most desired thing. While physical read is where we can have the most PTO.
Logical read is getting a data from Cache or ...
- 13
3
votes
Duplication records with INNER JOIN
If you only need to see rows from MessagesPerf and not MessagesPerfHistory, using an EXISTS subquery will avoid row duplication.
Of course, you may see incorrect results with the NOLOCK hint in place, ...
- 38.9k
4
votes
Accepted
Windows Power Plans for SQL Server
While some hardware vendors say that modern hardware behaves snappier on Balanced Power Plan it is still rule of the thumb to use High Performance plan for production servers.
Power plans are still ...
- 721
1
vote
formating date colums causing performance issues
formating date colums causing performance issues
Like Peter mentioned in the comments, FORMAT() is known to not be the most performant scalar function as Aaron Bertrand discusses in FORMAT() Is Nice ...
- 35.2k
Top 50 recent answers are included