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
2answers
70 views

Speed up Key Lookup (clustered)

I have a slow query in Sql 2017. Key lookup (Clustered) is taking 62%. There is also an Index Seek (nonclustered) taking 30%. Is there anyway I can speed this query up? Execution plan: https://www....
4
votes
1answer
5k views

DBA Blocking Query Email Alert

Does anyone have a good Sql Alert which sends email notifications, when query Blocking has occurred over many minutes? I know how to write my own, however seems to be a good code library on internet. ...
1
vote
1answer
25 views

SQL Server Replication: Is there a way to know which articles are being used in a replicated database?

I've come into a situation where there are about 1000+ tables being replicated to a database. When it was created, I think the creators just put a bunch of tables they thought would be needed. I'd ...
7
votes
2answers
658 views

How do I fix Uneven TempDB Files?

I recently added more CPU cores to a SQL Server and as best practices advise, I separated out the tempDB files into the same number of cores(8). Also along with best practices, I expanded the size of ...
10
votes
4answers
15k views

Removing secondary data files. DBCC SHRINKFILE: Page could not be moved because it is a work table page

I have too many secondary data files (.ndf) created for tempdb. To remove the excess files, I need to empty the file (content will be moved to other files): DBCC SHRINKFILE('tempdbfile8', EMPTYFILE); ...
2
votes
2answers
736 views

Removing Tempdb .ndfs

Tempdb is configured with 8 files and we are reducing them to 4. I read many blogs where SQL Server will allow you to remove excess .ndf's if you run the 4 dbcc drop and free statements, then run the ...
1
vote
2answers
53 views

Shorthand for “UNIQUEIDENTIFIER” data type name?

The data type keyword "UNIQUEIDENTIFIER" is a chore to type. I want a shorthand replacement. At first glance, it seems that SSMS provides two; the words "GUID" and "UID" ...
0
votes
1answer
38 views

How can we implement “A student can enroll in up to 4 units in a semester ”

I have a practice problem on Database implementation and am confused. For this database, I need to store enrollment information on students. Information such as which units a student has enrolled into....
0
votes
1answer
52 views

Migrating to server with much fewer CPU cores [closed]

We are in process of migration from our legacy SQL servers hosted on 2014 version to 2019. Currently the no. of logical cores provided on physical host is 64 and we've been asked to migrate these ...
2
votes
3answers
82 views

How do I determine the correct index on MS-SQL-Server based on a select?

I have two questions about the index management of MS-SQL-Server 2019 If the term "table scan" appears in the execution plan, is that an indication of a "full table scan", and ...
2
votes
1answer
368 views

Avoiding a full re-initialization when making changes to transactional replication

When I use the GUI to make any changes to transactional replication on SQL server, I live in fear of the "the changes you have made require a reinitialization of all articles" dialog. I have had ...
2
votes
3answers
4k views

Allow a SQL Server job to run for specific time before killing it

SQL Server 2016 Standard Ed We have a clean up job that runs some pretty simple sql: use productionDB go delete from transactionaltable where createdat < DATEADD(day, -21, GETDATE()) go This ...
1
vote
6answers
166 views

MS-SQL-Server 2019 is slower than SQL-Server 2008

Since we updated from SQL Server 2008 to 2019, the queries have become much slower. The following query ran for ten minutes on version 2008. On version 2019 it runs for over 30 minutes. Until ...
0
votes
3answers
1k views

msdb..sp_send_dbmail does not work with @query only with @body

I'm trying to execute the following: msdb.dbo.sp_send_dbmail --@body='Hi from sql server in a sproc' @query='select 1' ,@recipients='mkohn@***.com' ,@subject = 'Test' When I uncomment @body line, ...
4
votes
2answers
290 views

Will installing a second SQL Instance cause an outage on existing instances?

Like the title says, I just want to verify if we have the need to install a secondary SQL instance on a server that already has one SQL Instance, will the installation of the secondary instance cause ...
2
votes
2answers
46 views

How does JOIN involving database across 2 servers store the intermediate data?

In case of a SQL JOIN on 2 databases across servers. For example: Select * From server1.db1.dbo.tbl1 a Join server2.db2.dbo.tbl2 b On a.common_id=b.common_id Where a.some_col = 1000 Suppose this ...
0
votes
1answer
39 views

Does the length of the fields play a role in index access in MS-SQL?

Last week I had already started two threads dealing with slow SQL selects that probably did not use the index. Today I noticed the following: The customer and the material are involved in almost all ...
0
votes
1answer
45 views

Partition switching vs schema transfer

I'm designing a database whose data will be generated daily by an ETL. After some research, I found that one can use partition switching or schema transfer to maximize the uptime of the "live&...
1
vote
1answer
1k views

SSRS 2016 subscriptions error - Something went wrong. Please try again later

I have a SSRS 2016 instance which gives me below error whenever I try to view my existing report subscriptions: An error has occurred. Something went wrong. Please try again later. Strange thing ...
20
votes
2answers
34k views

SQL Server Join/where processing order

After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
2
votes
1answer
476 views

Log file on SQL Server 2014 grown out of control - LOP_SHRINK_NOOP

Server is SQL Server 2014 running in SQL Server 2008 compatability mode. Recovery Model is Full. We have auto shrink turned on (seems this is related to LOP_SHRINK_NOOP) We have about 4.5 million ...
1
vote
1answer
103 views

Parsing a manifest.xml of a table schema to script?

I have a manifest.xml sent to me by a vendor that accompanies some extracts they are also sending. I assume there should be a way to parse this into an actual table structure (or a script to create a ...
1
vote
1answer
1k views

SSRS 2016 report is failing with 'EXCELOPENXML' render format error

Few reports are failing with 'ExcelOpenXML' render format error after rs migration from 2014 to 2016. I made following change to the RS config file so I can use old excel format however some of the ...
1
vote
1answer
141 views

How to delete foreign key with lots of references

Table B have several thousand records that references table A with a foreign key. The application sends a request to delete the row from table A. It's important that the synchronous operation would ...
0
votes
1answer
22 views

Can no longer connect to localdb after updating from Sql Server 2016 to 2019

I need to restore a backup for an assignment, but SMSS said I was running v13 while the backup was made in v15. So I download and installed SQL Server Express 2019 and uninstalled the older version. ...
1
vote
1answer
229 views

openssl s_client does not indicate that SQL Server is using TLS, however the connection is secure. How is that possible?

I am connecting to SQL Server 2017 using ODBC and MSSMS. The connection is not using TLS; I checked it using openssl s_client. However, the DBA assures me that the connection is secure. What ...
2
votes
3answers
52 views

Preserving full refresh data source

I have a SQL Server database that gets updated every week with a completely new set of data. I am currently using two staging databases. A series of view databases are connecting to these and one week ...
0
votes
1answer
29 views

'You cannot add or change a record because a related record is required in table 'Booking'.' error when removing a PKFK value in a SQL database in C#

System.Data.OleDb.OleDbException: 'You cannot add or change a record because a related record is required in table 'Booking'.' My sub-routine to get rid of the booking with a given BookingID in the ...
6
votes
2answers
336 views

Select permission denied when using linked server in a function unless inlining is off

We are encountering an unusual error in our beta environment after upgrading to SQL Server 2019. This occurs when selecting from a scalar-valued function. This error does not occur in our production ...
0
votes
1answer
666 views

error 15466 An error during decryption

I have a stored procedure that runs xp_cmdshell with a user with sysadmin right.(it was created by someone else long time ago). For security purposes I need to remove the sysadmin right from this user....
2
votes
1answer
516 views

Should I switch my database LOG volumes from IO1 to ST1

I've got a SQL 2014 database that's been running in AWS for a couple of years now, in a dual-server mirrored configuration, using separate EBS volumes for data, logs, etc. When the system was created,...
0
votes
1answer
21 views

How To Make Replication From PostgreSQL To SQL Server [closed]

I have a question and need your opinions on the subject. I have a PostgreSQL and a SQL Server database server and i want to replicate the servers with each other from PostgreSQL to SQL Server. Have ...
3
votes
2answers
127 views

How can I successfully decrypt column data through the whole database safely?

We're moving to Azure SqlDb but as part of that, we're needing to migrate away from EncryptByPassPhrase as it's not supported. As such, we tried the process on the weekend but we ran into the log ...
0
votes
1answer
43 views

Installing Patches on MS SQL Server Tips [closed]

What is preventing Agent Service from starting after: Failing to install CU. Running a successful repair after disabling HIPS. Background: Installing CU's for Microsoft SQL Server I found I have an ...
2
votes
2answers
66 views

How can I query on-premise SQL Server database from Azure SQL database using stored procedure?

I have a Azure SQL database and it has a stored procedure from where I am trying to join a table which resides in on-premise SQL server database. Essentially, I am trying to query a table which sits ...
1
vote
2answers
57 views

Should I use a temp table or join

I am creating a stored procedure in sql server 2019 that needs to use multiple select statements to get a parent row and then its related data. I have the primary key clustered value for the parent so ...
2
votes
1answer
119 views

Need help with SQL Server 2014 and disaster recovery

I am tasked with building a highly available SQL Server 2014 Enterprise environment for our new policy admin system. This database will eventually grow to 1TB +. Background Our normal method of ...
6
votes
2answers
23k views

Do you set an index on JOIN clauses or where clauses, or both?

Say my query looks like: SELECT t1, t2 FROM t1 LEFT JOIN t2 ON (t1.id = t2.id AND t2.userid = @userid) WHERE t1.enabled = 1 AND t1.startDate <= ??? AND (t1.counter = -1 OR t2....
0
votes
1answer
169 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 ...
1
vote
0answers
43 views

SQL 2019 Linux, slow at GUI fast at sqlcmd/Query Analyzer

Server information OS Redhat 7 Memory 100GB CPU usage 1% User db size 2 gb each on avg total 7 user databases SSMS installed on a jump server Azure Data studio is also installed App Gui on application ...
1
vote
1answer
3k views

SQL Server Linked Server Permissions

On a SQL Server 7 server, I have a linked server to a Sybase database server running this command: select * from openquery(SYBASE_SERVER, 'select * from DBA.TESTTABLE') works fine if I'm logged in ...
17
votes
7answers
106k views

Changing sa password

I'm trying to change the password of the sa account using SQL Server Management Studio 2012. I've followed the steps below but the password is still the same. Login into the SQL Server Management ...
2
votes
2answers
3k views

CTE To Use Alternate Select When Select Returns No Rows

I have created an involved CTE, with no recursion, but with multiple selects being done as it processes the business logic of the requirement. Though, I have been advised that the logic needed for the ...
3
votes
1answer
209 views

Can you switch recovery mode to simple from full in an Always ON cluster setup?

I need help understanding my options. Given: SQL server cluster setup that is maintained by a DBA. System is NOT in production yet (not handed over to the customer). Initial high volume push of data ...
0
votes
1answer
439 views

Generate Script for Event Notification

I need to migrate my Event Notifications from current server to another one. I would like to generate that code, but I couldn't find an way to do that. Try 1 I have tried reverse engineering from ...
6
votes
1answer
157 views

String manipulation in Recursive CTE

I am trying to make something like the following work: WITH results(n, string ) AS ( SELECT 1,'lol' UNION ALL SELECT n+1, string + ' lol' FROM results ...
0
votes
1answer
409 views

Why am I seeing high execution counts for the system stored procedure sys.sp_columns_managed

We are seeing high wait times during business hours on one of our databases. Looking at SolarWinds I am finding that the system stored procedure 'sys.sp_columns_managed' is being executed more than ...
3
votes
2answers
870 views

Best way to divide CPU along all sql instances on 1 server

I have a physical server with 40 CPU's (hyperthreaded) with 2 NUMA nodes. On this server I have 20 SQL Server instances installed and I need to make sure each instance has some dedicated CPU assigned ...
1
vote
3answers
60 views

Shrinking data file to save disk space before migration

I know this is most discussed answered on various forums: I completely understand answer is no in most of the time or cases. However want to know if it can be good approach under any scenario: Lets ...
-2
votes
0answers
35 views

Error to migrate DB 2008 R2 to 2017 Standard

I have data base with 35 GB when i try to migrate it from 2008 R2 to 2017 standard with the usual method detach and attach file she show me that I was same partition Id crashed and the same when I do ...