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
0
votes
1answer
34 views

Disk errors in one of the SQL cluster nodes and SQL patching was failing with a lot of errors related to disk and permission issues

We have two cluster nodes in a SQL cluster and the application use this cluster is Microsoft Identity Manager. Since few month both nodes ( Node 1 and Node2) are throwing disk error and SQL team is ...
4
votes
1answer
98 views

Get the full text of a long request

How to get the full text of the request from the following request: SELECT t.[text] FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t Here, with a sufficiently long ...
3
votes
1answer
51 views

What happen with the Log backup if I set the SQL Server clock back?

What happen with the Log backups if I set the SQL Server clock back? In countries with daylight saving this happens at least once a year. Will the point in time restore with the STOPAT option fail? ...
0
votes
1answer
72 views

Multiple mdf, empty one is missing

Hi guys i have a big problem. I know i did such stupid and unforgivable mistake. And know please help me if you can. I wanted to make copy of database. I should make it by backup and then restore as ...
-1
votes
3answers
60 views

SQL Server Always On Availability Group [on hold]

I have problem with MS SQL Always On Availability. Suppose the connection between primary and secondary replica is broken. The client inserts data to the primary database (replication cannot be done ...
4
votes
1answer
82 views

SQL Server query scanning more partitions than expected

So my team has the following select statement in one of our stored procedures SELECT ai.Name ,dc.Component ,SUM(dc.Value) Value FROM Warm.DailyCosts dc JOIN Warm.AccountInfo ai ...
0
votes
3answers
39 views

In-place upgrade vs. new install

My search for this question yielded some similar questions, but none that I could see exactly like this. Question: In your experience, when doing a SQL Server upgrade, say from 2008 to 2017, or from ...
0
votes
0answers
11 views

I'm using Transaction Replication and some commands are deadlocking on the subscriber, do I need to re-initialize replication?

I'm trying to understand the nuts and bolts of transactional replication and I have a few questions: 1) If a subscriber command fails to replicate, where is this logged? 2) How am I notified if a ...
-2
votes
0answers
21 views

What would be the suitable certification for SQL Data protection and privacy? [on hold]

This posting is not related to any specific issue. I couldn't find suitable Certification code related to SQL Data Protection , Privacy , SOX ( Sections 302,404,408, 409 etc..) What would be the ...
1
vote
3answers
111 views

Slow query in production, bad execution plan or bad index?

I've just fixed a production performance issue by dropping an index and recreate it. I suspect dropping the index also dropped executions plans that used it and one of them happen to be bad. ...
0
votes
3answers
46 views

Return rows that meet a condition in two different columns

I have the following table describing the color and smell of an animal. I am trying to filter it to see what animals are the same color and smell. I do not care if multiple animals use the same color ...
4
votes
1answer
39 views

Generating Error when data size is larger then max text repl size

I recently ran into an issue with where an application tried to do an insert with data over the max text repl size. this time the failed insert was captured by the application log, and I was made ...
3
votes
1answer
58 views

Constant Scan joining

In preparation of my previous Constant Scan question I was experimenting with VALUES in various ways and came across the thing regarding joining of VALUES that looks odd to me. Setup is simple ...
-4
votes
0answers
31 views

Resolve key-Key deadlock scenario [closed]

We are currently facing issues because of deadlock. Due to security reasons i am not able to share the deadlock graphs or queries related to it. Can someone guide on how should i proceed to check on ...
1
vote
2answers
44 views

Need to move TempDB to another drive. But its files cannot be dropped or shrunk

Today I had a task to move all TempDB files to a new, smaller drive (RAMdisk). I analysed TempDB files space usage - not all the space inside of its files was used so I decided that new drive is ok. ...
0
votes
0answers
34 views

Deleting records does not affect unused space in data file [duplicate]

I have a SQL Server 2014 database where one table had around 485 million records. After deleting around 120 million records, the amount of free space in the data file did not change. I figured I ...
0
votes
1answer
40 views

insert results from store procedure with parameters into table

I have a stored procedure that I want to insert into a table, the stored procedure however has parameters I have to pass, how can I do this? DECLARE @RC int DECLARE @startweek varchar(20) DECLARE @...
-3
votes
0answers
36 views

Executing a script every 2-3 days on the server [on hold]

I want my server to run a particular script every 2nd day. Is it possible?
-2
votes
1answer
45 views

How to fetch transaction details for Logs Table in SQL Server

I want to create a tracking table for changes made to the database. A tracking table called Logs must be created into which all changes (insert and delete) that are made in the database will be ...
1
vote
0answers
47 views

SQL Server on Ubuntu - An existing connection was forcibly closed by the remote host after reboot

I have been running SQL Server on Ubuntu for a while and everything has been working fine but I rebooted the server and now all connections to SQL Server fail with the error: A connection was ...
-2
votes
0answers
23 views

State of the art of MSSQL for NoSQL data as of August 2019 [on hold]

I'm beginning to work on a preexisting system, and while studying it one of the first things I've noticed is a quite obvious issue on how a part of the data is stored. Long story short, they have a ...
1
vote
2answers
48 views

Is it bad practice to automate the updating of statistics and re-making of query plans on a relatively small SQL Azure database?

I have a SQL Azure database that's about 500MB in size. Every 2-3 weeks the database will suddenly start to run slow and some queries (generated by LINQ) will timeout. There is no gradual increase in ...
3
votes
1answer
120 views

how to script out the user defined table types?

I can Get name and definition of all table types using either of the following scripts: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED IF OBJECT_ID('TEMPDB..#RADHE') IS NOT NULL DROP TABLE #...
0
votes
0answers
21 views

SQL Instance not upgrading after install SQL Express 2014 [on hold]

I'm trying to upgrade SQL Server 2008 R2 version 10.52.4000.0 to SQL Server Express 2014. All the packages seem to install correctly, however the Database Instance never upgrades. I don't see any sign ...
0
votes
1answer
18 views

Benchmarking MongoDB full text search vs MSSQL full text search

I'm working on a project for comparing mongodb and mssql databases performance on full text search. I started with using YCSB benchmark but now that I have a deeper understanding about this benchmark, ...
0
votes
1answer
20 views

How to change the reporting services report service mode from Native to integrated?

In SSRS-2016, I am trying to change the Reporting services report service mode from Native to integrated. I added 2 new shared features to existing installation (See in the Pic) I am trying to ...
-4
votes
0answers
31 views

So working from SSMS but does nothing when ran from application [closed]

I have a stored procedure which works fine when ran in SSMS but doesn't work when called from application. Any suggestions ?? Thanks in advance !
3
votes
1answer
56 views

Scan on trigger Seek on SSMS [on hold]

I'm having a performance issue with a trigger when insert/delete on a table, it scan an index associated to a view, but if I execute the update manually it generate a seek. I updated statistics for ...
0
votes
2answers
37 views

SQL Server — see when user/ login was last modified? And other meta-data

I know you can see when tables or views were last modifed in sys.tables or sys.views in SQL Server. It does not appear there is a table like this for users or logins --- or is there? Basically, a ...
0
votes
1answer
25 views

How to find out which table/object the KEY or PAGE or EXTENT locks in the sys.dm_tran_locks belong to?

When I query sys.dm_tran_locks, a couple of questions arise: I can see there are some locks that are placed at KEY, PAGE, EXTENT, RID level (resource_type column). How to find out to which table or ...
0
votes
1answer
26 views

adding 2nd catalog for linked server to DB2

I am unable to figure out how to add multiple catalogs for a DB2 linked server. I have an initial Linked server setup and working properly, but I am struggling to figure out how to add multiple ...
2
votes
0answers
23 views

MS SQL FileTable CreatedBy and LastModifiedBy

When using a SQL FileTable table, I would like to store additional metadata for the file such as CreatedBy and LastModifiedBy. By looking at SQL File Table Schema, this information is not available by ...
0
votes
1answer
41 views

Ola Hallengren Transaction Log Backups of System Databases

I am attempting to backup the transaction logs of master, model in addition to the msdb. MSDB transaction log backs up without any issues, but the master and model do not. Here is the script that ...
0
votes
1answer
53 views

Read Only Routing Does Not Work After Failover in SQL Server 2016 Always On [on hold]

I have 2 nodes in an Always On Cluster - Node A and Node B I have configured read only routing. When Node A is Primary and node B is secondary , the read only routing works and read only connections ...
-1
votes
1answer
25 views

How to kill a stored procedure which is in open transaction?

I have a stored procedure which is running since four hours but it's uncommitted. How to check if it's running currently and kill it.
-2
votes
0answers
30 views

Nested procedures with transactions [closed]

What happens when two nested stored procedures with transactions is wrapped up in an outer procedure ? Does the transaction might be open and stop from re executing the outer procedure again?
0
votes
1answer
50 views

Identifier that starts with is too long. Max length is 128

I referenced a few other blogs and I can't isolate the issue to this logic. Code is below. Throws the error message attached. -- 1. Import Multiple Delimited Text Files into a SQL ...
0
votes
0answers
30 views

DatabaseBackup - SYSTEM_DATABASES - LOG [closed]

I've recently started using Ola Hallengren's scripts for backups and I'm curious what others are doing for System Databases that are in Full Recovery Mode vs. Simple. A job was not created for "...
1
vote
2answers
135 views

Are views treated like a subquery in SQL SERVER?

I'm trying to optimize a view which grabs the most recent history record for an item based on some specific criteria. I wrote a query which ran fairly quickly, and then turned it into a view. The ...
2
votes
0answers
32 views

the mystery of the table showing up in the subscription, but not in the publication

TL;DR: This question refer to sql server 2016, transactional replication I've got a table that shows up in the subscription but not in the publication. Here are the details: In one of my servers ...
0
votes
0answers
37 views

How would you move data for only the tables that exist in the destination?

I have Database A (which has Tables a, b, c, d) on Server1 and Database B on Server2 (which has tables c, d, e, f). The tables are added/edited/removed at both locations randomly and may change at any ...
1
vote
1answer
57 views

How to SELECT TOP(1) with COALESCE to pick the top NOT NULL row for each column?

I have a hierarchy of settings that have a default for everyone, can be overridden by groups, or by an individual user, for a specific screen, etc... These are stored in a table with a Hirarchy-ID to ...
-1
votes
0answers
12 views

Getting function sequence error in Codeigniter [closed]

I am trying to clone a database and its working fine if I disable db_debug mode but throwing below error while running it in db_debug enable mode in Codeigniter but on another side its working fine in ...
0
votes
1answer
33 views

Comparing id s of same column and assign value of one to another

I have table with id name 1 aa 1 bb 2 ff 3 df I want to check if value is repeated in column "id" then assign the "name" value to next matching ids row Expected: id name 1 aa 1 aa 2 ...
4
votes
1answer
76 views

MSSQL - How to support aggregate pushdown on indexed view with SUM(decimal)

I have an indexed view with non-clustered columnstore index, e.g. CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS SELECT BusinessDate, ItemId ,...
2
votes
0answers
22 views

Is it normal for Snapshot Replication job to be constantly running?

Our SQL Server 2016 already has Merge replication running successfully for some tables in 2 databases. We then needed to restore Snapshot replication for another set of tables between same pair of ...
1
vote
2answers
70 views

Do long-running and locking queries on subscriber databases cause performance issues on the publisher dbs?

One of our servers has a subscriber database. I've not used replication before and cannot find information on how the mechanism works. Does the publisher have to wait on the replication completing ...
0
votes
1answer
22 views

SQL Server Service Accounts Max Character Length

I've been considering a new naming convention for all SQL Server service accounts for instances in our enterprise. I ran into some issues on my lab environment. Is there a character limit to SQL ...
0
votes
0answers
23 views

Need help on designing database schema for high frequency update app

This app will call API endpoints every second and get JSON data and it needs to be saved in DB and then query it to the client. But Before saving, it needs to check if the data already exists or not, ...
0
votes
2answers
121 views

How can I use SQL Profiler to capture a single stored procedure

Using SQL Server SQL Profiler, which configuration can be used to monitor a single stored procedure? I'd like to capture all EXEC sprocName to include the parameter list. I'd like to capture this ...