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.

Filter by
Sorted by
Tagged with
1
vote
1answer
65 views

Why does a Full Backup restore of my database give me the error "RESTORE can only create a database when restoring either a full backup..."?

I've setup a maintenance plan to take a daily Full backup of all databases on my server. I've also setup a second maintenance plan to take Transaction Log backups every 5 minutes. The Transaction Log ...
9
votes
4answers
5k views

SSRS error "something went wrong please try again later" in web portal

I have a brand new installation of SQL Server Reporting Services 2019 on top of MSSQL 2019 Enterprise CU1. SSRS has been installed and configured using default settings, apart from the service account ...
0
votes
0answers
6 views

Hidden SQL server replica in always on availablity group

I want to deploy SQL server 2017 always on availablity group also with a replica for DR purpose. I want to use 1 primary(prod) 2 secondary(prod) 1 secondary(DR) Now my question is, the failover can ...
0
votes
0answers
2 views

SQL server 2017 distributed availability group connectivity after failover

I'm going to deploy SQL server distributed availability groups with SQL server 2017. From the documentation, I understand that we can have listeners for primary AG and secondary AG. The application ...
-4
votes
1answer
52 views

I want to perform max and sum aggregate on same column for different where clause in SQL server

I have a patient with 2 rows that have same admit and discharge dates and their admit flag is 1 and los is 14, 15 and other patient with 3 rows with different admit and discharge dates that have admit ...
0
votes
1answer
47 views

Does putting constraints decrease the required size to store my data in SQL Server?

I would like to know if putting check constraints decrease the required column size or not? For example, when I create an int column and a check constraint on it to store the numbers between 100 and ...
0
votes
1answer
174 views

Remote Desktop SQL Server Management Studio Dialog boxes Font size too small

When I connect from my local machine to a remote server through Remote Desktop Connection. And then try to use SQL Server Management Studio, the Dialog boxes in the application for example Connection ...
0
votes
2answers
101 views

SQL Server - Deadlock during two concurrent DELETE

I've got a Java app exposing a REST API to delete records in a table (SQL Server), sometimes the API must handle concurrent deletion requests (each request uses a different DB session) Here is an ...
1
vote
2answers
720 views

Produce fixed length output to file regardless of the length of data in each column/row

Not sure about this as I'm kind new to this. I need to output rows to fixed length files format. The table's columns are ID, Name & Desc. The output should be the same length for each row ...
0
votes
1answer
42 views

Count and last row of a batch(Top) with a insert statement

Problem statement : I've a big table and I read only Top 10000 elements of the table at a time. This is for batch processing(insertion) which has a where clause and one order by statement. Now, in ...
11
votes
3answers
5k views

Is SQL Server data compression categorically good for read-only databases?

Some literature on SQL Server data compression I read state that the write cost increases to about four times what would normally be required. It also seems to imply that this is the primary downside ...
1
vote
1answer
1k views

Distribution clean up job in transactional replication removed records but not files

Distribution clean up job ran without errors according to schedule, but I noticed that the snapshot files were not removed even if when created beyond max_disretention period. Records from ...
0
votes
1answer
93 views

To list the databases and logins

Can anyone tell me the query to list the databases and SSRS reports and different logins in a particular server ?
0
votes
3answers
101 views

SQL Server 2019 Cannot Change Password for sa

I have SQL server 2019and cannot change the password of my 'sa' login. I have read https://stackoverflow.com/questions/37245394/sql-server-2014-password-wont-change already and tried it, but it simply ...
2
votes
1answer
62 views

OBJECT_ID(...) function vs join on sys.tables and sys.schemas

When querying metadata for a specific table, I can either use the OBJECT_ID function or join the relevant system tables (sys.tables and sys.schemas). For example, both of those queries will return ...
0
votes
1answer
54 views

Number of executions of the same sub query by the DB engine

If I have this SQL query SELECT (select count(*) from tableA) as cnt, t1.* FROM ( SELECT t.* FROM ( SELECT * FROM tableA UNION ALL SELECT * ...
7
votes
2answers
376 views

SQL Server 2017 - CU25 - sp_pkeys - Wrong order

After our customers have installed CU25 there is a problem with the stored procedure sp_pkeys. It may now return the wrong order if a primary key has multiple columns. It can be tracked down in the ...
10
votes
1answer
156 views

Could I have an undetected deadlock?

Upon running sp_whoisactive on a server for the first time, I was greeted by something unexpected: Two sessions had been running for 13 days but both of them seemed to be blocking each other. Taking ...
5
votes
1answer
150 views

Index Definition Order and ORDER BY Clause

So i was doing my morning blog reading and stumbled upon this fun exercise: https://www.erikdarlingdata.com/sql-server/lets-design-an-index-together-part-3/ Here is the query in question from the ...
62
votes
1answer
21k views

What event information can I get by default from SQL Server?

I often see questions where people want to know if a certain thing happened, or when it happened, or who performed the action. In a lot of cases, SQL Server just doesn't track this information on its ...
1
vote
2answers
670 views

SSIS Script transform package won't compile when deployed to dev [Visual Studio 2017]

I have visual studio 2017. I have a script transform that I can run locally just fine, however when I deploy to the Integration Services Catalog on the dev server and run it, it fails with the ...
0
votes
0answers
29 views

load large data and split and store into two tables in sql server

I need to take large amount of data from a table and split that into two different tables in databse (sql server). How it is possible. i need to automate this action either in sql server side or ...
11
votes
6answers
37k views

SQL Server 2012 Can't log in with machine account

We're migrating an IIS/SQL Server site from 2008 to 2012, and I'm having a problem with authentication. We use Windows Authentication to authenticate as DOMAIN\COMPUTER$, but this isn't working, ...
1
vote
1answer
3k views

Select multiple columns as JSON in a single query

How can I combine several columns (of varying types, e.g. int, datetime, nvarchar) as a single JSON-formatted expression in a select query? For example, I have columns col1, col2 and want the query to ...
7
votes
4answers
14k views

SQL Server update query on linked server causing remote scan

I have a SQL Server 2012 setup as a linked server on a SQL Server 2008 server. The following queries executes in less than 1 second: SELECT kg.IdGarment FROM Products p INNER JOIN ...
1
vote
3answers
31 views

SQL Server replication additional subscriber

I have a publisher with the distributor on the same server which is transactional replication. There is also an active subscriber using push method. I need to set a new subscriber up going to a ...
2
votes
1answer
55 views

MS SQL attach MDF/LDF without FileTable folder

Is it possible to attach a DB that had a FileTable w/o the FileTable files? We were hit with ransomware and the image files are corrupt but the mdf/ldf are fine. I can lose the images, but I really ...
0
votes
1answer
5 views

Sql server transactional replication latency

I'm analyzing the distribution database to see if there any latency in the replication between publisher and subscriber The architecture is one publisher, 1 distrubution db (on publisher server) and ...
0
votes
1answer
23 views

SSIS debuging tool in SSMS 2016

I want to trace the execution and see the changes in variables in the package during the runtime. the Microsoft report tool for the SSIS package is not sufficient. I want more details and don't have ...
-1
votes
1answer
3k views

Export all SSIS packages from SSISDB

We have quite a few SSIS packages on SQL Server 2012 deployed to SSISDB, you can right-click on a project and select 'Export' saved as an *ispac file, however, is there a script to Export All Projects ...
4
votes
1answer
9k views

SQL Server 2012 - The application has failed to start because its side-biy-side configuration is incorrect

When opening Sql Server Management Studio I'm getting the following error message: Then when I have a look at the Event Viewer the following message is displayed related to this: Activation context ...
0
votes
1answer
412 views

Lock Escalation happening while inserting few million records into a table in production

I want to insert few million records into a table in my production database. I am not updating any existing rows.I am just doing INSERT INTO TAB1 SELECT COL1,COL2,COL3 FROM TAB2 This table has an ...
2
votes
1answer
63 views

Updating varchar column over linked server with parameterized query causes remote scan and cursorfetch

I'm issuing a fairly simple update of single varchar column against a remote linked server. E.g.: UPDATE Hydrogen.CRM.dbo.Customers SET EyeColor = 'Blue' WHERE CustomerID = 619 And that works fine ...
0
votes
0answers
18 views

Why Sql execution delays sometimes?

I made my project in VB.NET with SQL Server as backend but sometimes SQL query execution delays even from SSMS. I have the following query which I execute every time I want to fetch a product list: ...
0
votes
1answer
45 views

sp_BlitzCache and SPID

I'm using sp_BlitzCache and it's very helpful, I ran this command: Sp_BlitzCache @SortOrder = 'duration'; GO How do you relate the query with a SPID as I found the query with the longest duration? So ...
8
votes
1answer
119 views

Check another session's IMPLICIT_TRANSACTIONS setting

In SQL Server, you can find IMPLICIT_TRANSACTIONS value for own session via @@OPTIONS. How to find the IMPLICIT_TRANSACTIONS value for another session? This can definitely be done, because SQL Server ...
0
votes
2answers
58 views

Difference between statement for declaring variable MSSQL

DECLARE @Variable AnyType SELECT @Variable = something OR DECLARE @Variable AnyType = something
0
votes
0answers
27 views

Kerberos - configure constrained delegation using SQL Server's virtual accounts

I'm trying to setup Kerberos constrained delegation to solve my double hop problem. I'm using standard SQL Server configuration with Virtual Account (NT SERVICE\MSSQLSERVER) as a service account for ...
0
votes
0answers
29 views

script to detect alwayson redo latency in SQL Server

I have 2 node SQL 2019 AlwaysON setup where i regularly see latency for redo thread in AG dashboard when applying the redo transactions on secondary. we have identified that this redo thread delay in ...
-1
votes
1answer
34 views

How to correct number of rows sampled (index statistics) in a large table?

I am looking at index statistics of a large table. The number of rows sampled are 300. Where as the total rows in the table are 345100. I want to fix such that a substantial amount of rows are sampled,...
2
votes
1answer
887 views

Extended event for lock count does not show locks [closed]

I recently came across the article Locking and Performance. It has following statements If we manually monitor locking activity while this query executes, we see that without the page locking ...
2
votes
1answer
2k views

Unable to get output variable of Oracle procedure using Sql linked server

I have created a Heterogeneous connection between SQL Server and Oracle and tried to call a oracle procedure from SQL Server.I got below error when i tried to execute the procedure. OLE DB provider ...
0
votes
0answers
34 views

sp_WhoIsActive CREATE statement in sql_text column while executing object inside another [duplicate]

I am running fabulous sp_WhoIsActive with these parameters: EXEC dbo.sp_WhoIsActive @get_task_info = 2 ,@find_block_leaders = 1 ,@get_outer_command = 1 ,@get_transaction_info = 1 ,@...
-1
votes
0answers
29 views

How does sql server decide when to stop after starting to read an index?

Let's say the sqls server is reading an index. It may be clustered or non clustered index. Say the index has values from 1 to 999999. And assume sql server decides to read the index. When the where ...
-1
votes
0answers
19 views

Is the cost shown on the select an addition of cost of all the items on the right of select?

In the plan, is the cost shown on select an addition of cost of all the items on the right of select? Or is it specifically the cost of only select.
0
votes
0answers
10 views

DACPAC: Altering primary key results in rebuilding tables during deployment

Question: Is there any better way to avoid rebuilding of table when clustered Index column is changed Details: We have Database project with all default settings. There are tables with primary key ...
3
votes
2answers
145 views

Permission hierarchy vs. WITH GRANT OPTION

I have an Azure SQL database with this security setup: SchemaUpdater user with db_ddladmin, db_datawriter, and db_datareader roles. The user is used to execute database change scripts during ...
1
vote
1answer
35 views

Group by latest date with conditions

I need your help! Let's say I have this table: Instance Date MetricID Value Marc 09/14/21 1 5 Marc 09/14/21 2 2 Marc 09/14/21 3 1 John 09/14/21 1 10 John 09/14/21 2 1 John 09/14/21 3 1 Marc 09/...
2
votes
3answers
89k views

Must Declare Scalar Variable Error

My syntax keeps giving me the below error, which is blowing my mind as I think (and please kindly correct me if I am incorrect), I have declared and set this variable above. Msg 137, Level 15, ...
0
votes
0answers
17 views

How to troubleshoot SQLServerAgent startup

I wonder what can cause SQLServerAgent to stay in "starting" stage after restart. The service restarted successfully, but I can't get details of any job, or start/stop/delete with msdb ...

1
2 3 4 5
613