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
30 views

Combine Rows with indirect relation

I am trying to create a report from a cloud based EHR so I cannot share real data and some of these tables are fairly massive. I will try to minimize and share the bare minimum and expand if someone ...
0
votes
0answers
19 views

How to change UDP port 1434 ms-sql-m service [duplicate]

I am running sage evolution 200 on a cloud server. Until recently the program was running fine. Then the cloud host decided to block UDP port 1434 on their router as they claim it was being attacked. ...
1
vote
2answers
175 views

Can we reduce row lock contention on an index

We have few queries which check their status into a table every 5 seconds and update their status.. Below is how the query looks like(all table columns are in update query) update table1 set Name='...
2
votes
1answer
106 views

Can adding a columnstore index to a table affect read performance of a query that uses a rowstore index on the same table?

I'm doing some testing of columnstore indexing on a single table that has about 500 million rows. The performance gains on aggregate queries have been awesome (a query that previously took about 2 ...
1
vote
1answer
15 views

Finding matching records in a chain to form a parent user

I need to find commonality across 3 columns of data. For instance, user 1 and user 2 have the same email. user 2 and user 3 have the same device id. So I want to assign a unique parentuserid of 1 to ...
1
vote
1answer
60 views

Is there a limit to the number of objects that can be dropped and recreated (frequently) in SQL Server?

The datatype for field [object_id] in table sys.objects is "int" So for all the objects in a database this is unique and has a limit of 2,147,483,647 (2 billion'ish) What happens when there ...
0
votes
1answer
39 views

SP is locking due to Begin Transaction

Please read the following code SET IMPLICIT_TRANSACTIONS ON BEGIN TRANSACTION DELETE FROM xxxx SELECT * FROM xxxxxxxx WHERE EXISTS ( SELECT * FROM xxxxx WHERE CONCAT(xxxxxxx) = ...
2
votes
1answer
26 views

Risks regarding simple recovery model and importance of log file backup

We have some databases on our server with 'Simple' recovery mode. We are using a third party application for the whole server backup every 30 minutes. We are also taking SQL server database backups ...
1
vote
1answer
49 views

SQL Server - Get the Date Ranges

I have a table with start date and end date. For example, PlaceID | StartDate | EndDate 1 | 2020-01-01 | 2020-01-04 1 | 2020-01-10 | 2020-01-15 2 | 2020-01-03 | 2020-01-06 2 | ...
1
vote
1answer
44 views

How to compress a non-shrinkable table with nvarchar(max) fields?

We have a serious problem which was on our minds for pretty much two weeks already. We have a big database (~250 GB, MS SQL Server 2014) and we needed to shrink it somehow, since our SSD is almost ...
0
votes
2answers
17 views

SQL server alwayson 3 Node Primary Down

I am using three node alwayson Availability Group SQL 2012 Enterprise Edition. 2 Node in DC with File Share Witness quorum also in DC and third node to be setup in DR I need to perform a DR switchover ...
0
votes
1answer
64 views

Recommendations about deleting large set of rows in MSSQL [closed]

I need to delete about 75+ million rows from a table everyday that contains around 3.5 billions of record. Database recovery mode is simple, I have writen a code that deletes 15.000 rows in a while ...
-4
votes
1answer
43 views

Updating Multiple rows of the same column [closed]

I have this query UPDATE users SET objectName = CASE WHEN VideoID =2 AND MusicID = 13 THEN 'Sing with Me!!!' END, CASE WHEN VideoID =2 AND MusicID = 2 THEN 'Awe!!!' END, WHERE ...
3
votes
1answer
193 views

SQL-Server performance metrics: What exactly is Lock Waits/Sec (or: Why is it that unrealistically high?)

I have a production SQL-Server, which seems to have increasing numbers of request taking extremely long (median response time is low, average response time is growing steadily). Therefore I started ...
1
vote
1answer
42 views

SQL TRN logs back [closed]

i am hoping for some help or guidance , i currently have a database that is in excess close to 1 terabyte, we do a full DB backups once every evening and we run trn log backups every 15mins. this is ...
0
votes
1answer
41 views

Moving data from staging table to production table uses up to 200GB of Log space

I have multiple staging tables containing data that needs to be moved to production tables. The process of moving this data is done within a transaction to make it possible to rollback everything in ...
0
votes
0answers
41 views

Cleaning replication tables before snapshot applied to subscriber

We have a snapshot replication setup. In some cases (like previous replications failed etc), some subscribers get the following error The ALTER TABLE statement conflicted with the FOREIGN KEY ...
0
votes
3answers
36 views

how to count second col if first col value is null and avoid duplicate count

I'm making sales statistic report to calculate scanned documents station wise using below Query: SELECT RIGHT(CONVERT(varchar(11),DATEADD(MONTH,DATEDIFF(MONTH,0,InvoiceDate),0),13),LEN (CONVERT(...
0
votes
1answer
31 views

Case with multiple conditions on multiple columns [closed]

I am having trouble making this work: CASE WHEN a.val IS NULL AND ( a.CreatedBy <> 'MDI' OR a.Type <> 'RO' ...
0
votes
1answer
35 views

Dynamic SQL breaks statement

I am attempting to write a query that pivots a certain column that may have any number of distinct values in it. I am able to get this to work with static values, but when I try to parametrize the ...
0
votes
1answer
32 views

Pass a table name as a parameter to a stored procedure

I am trying to create a Stored Procedure, and pass the name of a table as a parameter and I get this message:Must declare the table variable "@tblName". What do I have to change to make it ...
0
votes
0answers
35 views

Clearing plan cache improves performance - bad plans in cache?

I have run into this situation a number of times on a VM with SQL Server Express 12.0.6205.1 (SQL Server 2014 SP3 CU1) Using SSMS, I'll run a stored procedure a few times. It consistently takes, say ...
0
votes
0answers
17 views

How send email with “execute as user”

I need that all my system users can send email from the DB. not to assign everyone the role in the msdb database I created one login/user "send_mail", and grant permission on msdb db, ...
0
votes
0answers
33 views

List of databases present in specific maintenance plan

I have two maintenance plans and also I have two user-defined databases demo1 and demo2. The first maintenance plan is used to take the backup of all the databases. the second maintenance plan is used ...
0
votes
0answers
26 views

Index Spool (Eager Spool), Difference in number of row before and after the operator

I was optimizing one of the Stored Procedure for which I went through the execution plan of it. In the execution plan I see the operator Index Spool (Eager Spool). There I see it is taking 1759 rows ...
0
votes
1answer
61 views

why are estimated rows exponentially increasing with each join?

summary We have an Object-Oriented database schema that we query with Hibernate with: 1 parent table (NAL_Actions) 7 child tables (NAL_*) If I want to query a couple of these "action"-...
0
votes
0answers
25 views

How to troubleshoot slow connection issues from SSMS? [duplicate]

I right click on a server in the Registered Servers pane and select New Query. Sometimes (like 1 out of 3), it will just hang for 5-15 seconds with the Connect dialog (below). Other times, it ...
0
votes
1answer
29 views

“Cannot continue the execution because the session is in the kill state” when querying system tables

This is a snippet from some code I run on a 2012 (SP3-CU9) (KB4016762) - 11.0.6598.0 (X64) system that I just inherited and have not yet had a chance to fully patch. AlwaysOn AG are not enabled, and ...
0
votes
2answers
16 views

Grouping results and combining duplicates as array

I'm trying to write a query that gets all the records grouped by code and the rows that are duplicated are aggregated into an array. +----------+---------+ | code | name | +----------+---------+...
-1
votes
0answers
57 views

SQL Server optimizer doing weird things with sub-select and UNION

We have a weird situation in SQL Server 2017. We have a query where it takes 10 seconds to complete. The problematic part, is the last AND predicate, concering konfkode column and IN(...). So the ...
1
vote
1answer
24 views

What can I do with VIEW DEFINITION permissions that I can't do with ALTER permissions?

I have created a new user which has ALTER permissions on my database: CREATE LOGIN [testuser] WITH PASSWORD = '...'; CREATE USER [testuser] FOR LOGIN [testuser]; GRANT ALTER TO [testuser]; To my ...
0
votes
0answers
40 views

Error “There is insufficient system memory in resource pool 'internal' to run this query.” even after updating to SP3 on SQL Server 2008 R2

We are getting this error : “There is insufficient system memory in resource pool 'internal' to run this query.” everyday, and the error lasts for about a minute, after that the queries are executed ...
0
votes
1answer
31 views

Need to upgrade SQL Server from 2012 to 2017 on a Server 2008 box

I have been asked to upgrade SQL Server from 2012 to 2017, it is currently installed on a server with Windows 2008 OS. My thinking from what I've researched is this: Take a snapshot of the server ...
0
votes
0answers
17 views

database replication or mirroring

I have a database server in HQ, while in branch I need user able to get the data from database server to perform daily task. The problem is the connectivity is not that good. I wish to have another ...
0
votes
2answers
17 views

Grouping results and filtering duplicates by the row's date [duplicate]

I'm trying to write a query that gets all the records grouped by code and the rows that are duplicated take the one with the most recent date. +----------+---------+-------------+ | code | name ...
0
votes
1answer
34 views

How often does SQL Server refresh group policy?

Does SQL Server check against the Windows GPO on the local server it is running on or with the domain controller? Does SQL Server update the GPO at its own interval or immediately when the server has ...
0
votes
1answer
30 views

How to Migrate complete Transactional Replication to other servers without Snapshot?

We have large databases that are being kept in sync using Transactional Replication. They are all running in VMs and we are going to move to a new provider and reorganize the network structure and ...
0
votes
1answer
34 views

Can I change database compatibility level from 80 to 90 or higher with log shipping?

I have a database on SQL 2008 R2 with compatibility level 80 and I want to change it to 90 or 100. I assumed that after changing, I may find a regression in our application's performance. Many backups ...
0
votes
1answer
42 views

MSSQL - Should I mention column max length?

I am a bit confused from what I read so far regarding to this issue. In my database I usually write the max length of a column when I create a table or add a column. Because I need to support other ...
0
votes
0answers
64 views

User, group, or role … already exists in the current database. Changed database context to 'DWConfiguration'. Error code: 0x84BB0001

Most of the question is in the title. My case concerns MS SQL Server 2017 - Developer Edition. The error happens during SQL Server 2017 installation process. While searching for answer online I ...
2
votes
1answer
66 views

How to implement High Availability for all nodes in a sharded database?

I'm doing some research into implementing sharding for one of my large (~2 TB) production databases. Currently the database has HADR via a multi-replica AlwaysOn Availability Group. Since sharding a ...
0
votes
1answer
16 views

How to select Data from ODBC Source in MySQL

I try to select data from MSSQL in MySQL over straight way. I want to store the result in a view. What did I do now: Add the MSSQL Server as a ODBC Datasource.
1
vote
2answers
26 views

Temporarily Turn Off Table Type Reference Checking On A Development Database

During my database design process I have begun to add Table Types to the database and have run into the need to alter a table type. It is painfully clear that one cannot remove or drop the table type ...
0
votes
1answer
38 views

Concatenation of varchar-converted DATEDIFF and placing a string afterwards - issues?

Everyone, the table relationship diagram for the database is the below one In case the full DDL for the database is requested, I can provide it (although it will probably exceed the character limit ...
1
vote
0answers
33 views

How statistics play a major role in legacy CE vs new CE

We have quite some issues going on for one of our SQL server 2014 SP3 after compatibility change to 120 from 100. Yes i am aware of few hints to make use of legacy CE and have implemented as success. ...
0
votes
1answer
57 views

SQL Server 2016 - Availability Groups - How To Copy Permissions To Secondary Node [duplicate]

SQL Server 2016 SSMS v18.4 I want to create the same logins and give the same privileges on both node. In primary node I have no problem about this operation. But when I create the same login on ...
1
vote
0answers
58 views

Database in suspect mode

I'm experiencing a tough issue for me. When I was updating some columns into the database I have previously installed, the main memory corruption led the database jump into the suspect mode. I follow ...
0
votes
1answer
24 views

How to store long string with netstring format in varchar column?

I am involved in project where we have to store very long string in varchar column (varchar(max)). Due to this case have to questions: This column is one of other 40 columns in table (but only this ...
-2
votes
0answers
32 views

SQL server detected a logical consistency -based I/O error: incorrect pageid (expected 1:23823;actual 0:0)

I am seeing following message Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:23822) contains an incorrect page ...
1
vote
1answer
71 views

User has been denied select to table, but has access to table via synonym

User has been denied select to table T1 in DB1, but user can create synonym in DB2 and can select from DB1.TB1. I can not deny create synonyms to user. Is it possible to prevent user to select after ...