Questions tagged [sql-server]
All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.
26,362
questions
0
votes
0answers
11 views
Two query statament, two result set but need combine as Pivot
I have two table which record Monthly-Allowance and OT-Allowance which both table have Department column and Track column
I need report that have all department but separate with Track Column 'DL','...
0
votes
1answer
29 views
Backup too big to restore
I have a backup which in file explorer is roughly 160GB.
If I run
select sum((size*8)/1024) From sys.database_files where type=0
I get a result of 169493, fine.
When I try to restore this ...
0
votes
0answers
17 views
Single User Mode so I can reset SA password, I always get “Only one administrator can connect at this time.” regardless of what I do
I added -m to the start parameters, reset it and try to connect with SQL cmd, and get this error.
I tried the following batch file:
net stop MSSQL$SQLEXPRESS
net start MSSQL$SQLEXPRESS -m
sqlcmd -S ....
1
vote
0answers
15 views
How to force SQL Studio not to use copy-only backups to build database restore script?
How to force SQL Studio not to use copy-only backups to build database restore script? I want to ommit copy-only backup (because it's not longer available) and force studio to use last full backup (I ...
0
votes
0answers
25 views
Database Mail with SSL (like GMAIL SMTP) not working
Is anybody successful sending email via MS SQL Server Database Mail while secure connection is set ON in account settings?
I'm trying different SMTP servers with SSL to set up - like Gmail - and none ...
0
votes
0answers
20 views
SQL Server Agent runs a job on one server, not on the other
I have two SQL servers, each running on their own Windows server. The versions of both Windows and SQL Server are the same, they are on the same domain, they're virtually identical.
On these servers,...
0
votes
1answer
18 views
Restore database with Availability Groups
I have high availability enabled with two servers and when trying to restore a database I realised I had to un-synchronize the database and then remove it from the availability group.
I use Ola ...
-1
votes
0answers
23 views
How To get anti-virus exclusion list using cmd or powershell? [on hold]
I want to list all excluded files/folder using cmd or powershell
1
vote
1answer
23 views
SQL Server Agent - Report Failure but continue When intermediate step fails
I have a SQL Server Agent job that has three steps with the following control flow:
Step 1 - on success - Go to next Step. on fail - job fails
Step 2 - on success - Go to next Step. on fail - Go to ...
0
votes
1answer
18 views
SSMS Activity Monitor Expensive Query start time
I keep seeing rows in "Recent Expensive Queries" for queries that have not been run for hours.
This confuses me when trying to figure what is snarling things up.
Is there a way to add start time ...
1
vote
0answers
20 views
Can we change Log Shipping backup file naming convention
Seems the Log Shipping backup files naming convention (especially the date-time stamp in the name) is followed by UTC time, i believe that is default and standard.
I would like to clarify on ...
0
votes
1answer
42 views
SQL Server RAM usage is incresing and query executions is slowing down wih time
I'm using the SQL server on a server with 48GB ram. when I'm starting SQL Server's service, it's using 12% of the RAM at first but after 24 hours it's reaching 90% and query executions slowing down ...
0
votes
3answers
31 views
I want to know which table the value comes from in inner join
I have a simple query where i do 2 inner joins to combine data from 3 tables. within the table i would like to know which table each value comes from. i attempted the following but the syntax is wrong....
0
votes
1answer
24 views
Updating multiple columns using case conditional clause
In the following TSQL code the second column TypeId2 is not updated.
What could be the problem here?
declare @dd table(Id int,number int, TypeId int)
declare @tt table(Id int,TypeId1 int, TypeId2 ...
0
votes
1answer
26 views
Define Database Size
I want to create a database with Size limitation. I am using sql server 2008r2 edition.
Maximum size should be only 20GB. That means it should allow only 20GB space for particular database.
How can ...
3
votes
0answers
36 views
Strange Hostname/Application name when hunting for SA login errors [on hold]
So I've noticed thousands of login failed for user S.A. after I've changed sa password. We all agreed with this, its a test environment and nobody was using SA and since i'm new here I would like to ...
7
votes
0answers
277 views
Database Stuck 'In Recovery' with command XTP_DB_RECOVERY
We recently converted a table to memory optimized data. Our backups have all ballooned (3x300GB files to 3x600GB files for Full, 3x50GB to 3x250GB for Diffs), and startup has been getting ...
0
votes
2answers
39 views
Extract only Date from the string
I have one field in database and I am getting the multiple dates in the same field. I want to extract multiple dates from the string. Please help me on this
String like:
"7/3/2019 1:20 AM - Request ...
0
votes
1answer
34 views
SQL Server - High buffer time
I'm noticing a high number of waitingRequestsCount on SQL server. The latch_class is buffer. See attached screenshots. Going through SQL server dashboard, I see high number (32221334) waits for Buffer ...
2
votes
0answers
28 views
Advantage of querying data outside a filtered index
Is there any way to take an advantage of having a filtered index to speed DML queries targeting data sets outside that index?
My playground:
Table with billion rows and 100 columns
Unique clustered ...
2
votes
1answer
21 views
Select IDS where all values can't be found in another table
Hey I'm pretty new to SQL and I've come up with a problem that I haven't been able to solve
I'm trying to make a query to bring up ID's that have ALL null values and are located in another table.
DB ...
1
vote
1answer
43 views
I would like to understand better the difference between “sql server stand alone” and “sql server failover instance”
AS these question asks:
Are "Always On Failover Cluster Instances" and "SQL Server Failover Clustering" the same thing?
Shouldn't Windows Server Failover Clustering already ...
1
vote
1answer
34 views
SSMS - Openrowset maximum file size
Apologies if this has been answered elsewhere but I couldn't find anything here or online.
Does openrowset have a file size limit? I'm trying to write a stored procedure, part of which checks if ...
1
vote
1answer
27 views
Logging security events in MSSQL
I need to log events of admin changing someone's password, and user changing his password. How do I do that?
I've found a nice solution based on Service Broker, however the resulting table only ...
0
votes
2answers
44 views
Editing all columns
I have a db table with more than 200 rows (table name: ports).
There is this column (port name). The data in the column is like: aaa 1/15.
I want to edit all the data in this and turn the aaa 1/15 ...
1
vote
2answers
28 views
Regarding Always On Licensing (Secondary replica no license required) [on hold]
I have few questions regarding the licensing of SQL Server when it comes to Always On:
Under which scenario / settings we can say that secondary replica is truly passive and will not be considered ...
0
votes
0answers
26 views
Template error handling in a SQL Server script
I have multiple SQL Scripts like the one below, and in each script I'm inserting data into a table or creating tables (separate scripts), all covered by error handling and logging the error or success ...
0
votes
1answer
36 views
DB Encryption Algorithm [on hold]
Which DB encryption is best in SQL server 2016 SE as TDE is enterprise feature. I can't use always encrypted because my goal is to encrypt complete database, data on rest or on move is not the a ...
0
votes
0answers
35 views
Normalization of the this relation just want to know what are the functional dependencies
Consider the Customer account database of the ABC big bank, which keeps data for customers and their orders. A database of only one relation is designed, as shown below:
ABC(
customerNo,
...
0
votes
1answer
24 views
changing the connection string in SQL Server
we deployed the packages from old server to new server .
how to change the connection string i.e initial catalog or server name parameter from Agent job > configuration > connection manger .Is there ...
0
votes
1answer
32 views
How to bulk insert data in vertical into sql server?
My data in below format. I have to insert into sql server table.
agency NO
booknbr 06000330
bookdttm 2006-07-19 10:56:00
arrdttm 2006-05-07 05:42:00
aj_id A000009454
casenbr 06006640
...
1
vote
0answers
30 views
Random deadlocks SQL Server 2012 using ODBC Driver 17 and simple “Select * FROM” Stored Procedures
I'm getting random deadlock errors in my application on sometimes the simplest of stored procedures.
The application environment where I'm experiencing the error uses the GUNICORN server in ...
0
votes
0answers
37 views
Unable to access SQL Server from SSMS 2014
I'm unable to access the server after I installed Visual Studio Isolated Shell. No matter how I try to access the server, the result will always be the same (image below). How can I access it back ...
1
vote
0answers
35 views
Querying system health data is sometimes fast and other times too slow
We get the data from system health XE within SQL server and load into tables for later querying.
Part of that statements which gets pretty slow is
INSERT INTO TABLE1
(
[object_name]
...
0
votes
1answer
46 views
Column as an Included column or as a key column
I have a query which has an optional where condition on column classID(not unique).
This same classID is being displayed as a select column in various other queries.
Currently i have classID as an ...
3
votes
3answers
75 views
I need help with a SQL Query. SQL beginner here! [on hold]
I'm totally new to SQL and I've inherited a customer's server (the company that managed it no longer exists). They need me to run a SQL Query that shows their inventory sold for pots.
Specifically, ...
-1
votes
0answers
25 views
SSIS Package won't run SQL agent
I've upgraded my sql server and moved all the Databases and agent jobs to a new server from 2012 to 2016 and exported and imported the SSIS packages.
I have maintenance job which run a SSIS package ...
10
votes
4answers
1k views
Why would shrinking TEMPDB log lead to slowness?
I recently accidentally shrunk tempdb log to almost 0 after getting alerted that log drive filling up. I am told that it will lead to slowness. Can someone please explain why it will lead to slowness?
-1
votes
0answers
22 views
DBDEFENCE for SQL Server [on hold]
Has anybody used DBDEFENCE as SQLdb encryption solution in production? If yes, Could you provide a overall feedback about it(like Reliability, performance,durability, easy etc).
Thanks
1
vote
0answers
32 views
Football Matches - build temp standings table for each Gameweek
I have the following data in a sql database and i need that for each game in the table to add new columns with the Rank in Table, Number of Points and Goals Scored at home, for each Gameweek (the ...
0
votes
3answers
50 views
SQL server 2016/2017 in terms of better performance
Which SQL version or edition should we use out of SQL server 2017std/ent or 2016std/ent in terms of better performance gain when you are dealing with SP's (approx 1000 or more)in single database which ...
2
votes
1answer
45 views
Install last CU for SP2 without installing SP1 in sql server
A client asked me to update their SQL Server.
It's an 2016 base release (13.00.1601.00)
Microsoft already released the SP2 CU10. I know that cumulative updates are... like... cumulative, but even ...
0
votes
1answer
46 views
How to use comma separated columns in CASE WHEN Statement?
I am trying to add below CASE WHEN Statement in SQL WHERE Clause, But SQL Server not allowing me to do so. All column used are of same Data Types but still its giving me error, Can anyone please check ...
4
votes
1answer
97 views
Deadlock Graph - Surviving Statement inputbuf does not show the object that was locked
I have captured a deadlock on my system and the (anonymised) XML output is as follows:
<deadlock>
<victim-list>
<victimProcess id="processf4d9233468" />
</victim-...
0
votes
1answer
49 views
Deadlock in SQL Server
I have been trying to resolve this deadlock for a while now with no success, its seems like the users are opening multiple tabs when executing this task. Below is the deadlock trace from red gate. Can ...
0
votes
1answer
38 views
How to find average of transactions every 15 min in Sql server for last 7 days?
This question might not be new in forums , but struggling to get the expected out put.
Please any body share the similar requirements if we face with solutions.
I would like to find average of total ...
2
votes
1answer
70 views
Granting temporary sysadmin rights
Using SQL Server 2017. I have an issue that I need to allow a Windows authenticated user the ability to read excel files that are on the network. They have public rights as well as a host of different ...
6
votes
1answer
400 views
Replace long GROUP BY list with a subquery
This is a repost of my question on Stack Overflow. They suggested to ask it here:
I found an online article from 2005, where the author claims, that many devs use GROUP BY wrong, and that you should ...
-1
votes
0answers
35 views
Name matching - 1 million row under 1 seconds [on hold]
I have requirement to have fuzzy name matching for around 1 million row under 1 second. I use a simple Jaro Winkler function for 20k row it cost around 5 seconds to execute and the rate increase ...
2
votes
2answers
43 views
Is DB mail supported on SQL Server hosted on Azure VM (IaaS)?
Is DB mail present in SQL Server hosted on Azure VM (IaaS)?
If DB mail is not supported, then what are the features available?
