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

Processing Dimension completed but error occurs

I have problem while processing cube in SSAS, everytime I have an error: I tried to process every dimension separately and it looks like something bad was going on after processing 'Date' dimension. ...
0
votes
1answer
18 views

Double Transactional Replication (e.g. Two report servers)

Is it a good plan to use SQL Server Transactional Replication from server one to server two then another from server two to server three? Most of us DBAs here are advise against it. The boss wants ...
3
votes
1answer
51 views

where can I find the DNS name associated to the listener of an Availability Group?

I have an availability group that has a listener. The listener is called SQLPROD as you can see on the picture below. It uses port 1433 When I run the following script: ALTER AVAILABILITY GROUP [...
0
votes
1answer
50 views

why are my Database backups occasionally failing?

Part of our back up routine, on our main production server, I nightly take a full backup of all of the databases. These databases get restored on several dev severs and data is obfuscated. These ...
0
votes
1answer
29 views

Does a login need to be created on MSSQL Always On replica before being granted roles on the primary? [duplicate]

Consider a SQL Server Login MyLogin that exists on PrimaryServer but not SecondaryServer in an Always-On Availability group. MyLogin has a number of roles already assigned. If the login MyLogin is ...
0
votes
1answer
38 views

How to make date intervals from a list of date

How to make date intervals from a list of date (Group by columns A and C, the date range consists of a continuous date sequence):
3
votes
1answer
64 views

How can SUSER_SNAME return the localized user name?

I need to assign network service user to SQL Server database programmatically, so I am running this script: USE [MyDB] GO DECLARE @user nvarchar(50) DECLARE @SQLStatement nvarchar(500) SET @user = ...
0
votes
0answers
28 views

Move database from second instance to mount drives [closed]

SQL server 2012 is running in our environment. I am able to move the data to the new VHDs mounted as drives but was unable to get the databases on the second instance moved to the new Mount points. So ...
-1
votes
1answer
34 views

“No column name was specified for column…” when aliasing results of CROSS APPLY

I have this table: from which I create this View: This view's CardId allows me to JOIN against the Card table with so I can retrieve the Count from any CardId. Here's my SQL: SELECT * FROM ( ...
1
vote
0answers
72 views

Restarting SQL service doesn't fix performance, hard reboot of system does, how to troubleshoot? [closed]

I have a high-load OLTP SQL Server 2008 Enterprise. 4 NUMA, 96 cores, 430 GB RAM allocated to SQL. Most data and logs are on NAND SSD, some logs on 10K drives. It's linked to a few other systems as ...
0
votes
1answer
33 views

Check partition function if list of value exists, if not iterate through list and add new partition value to the partition function?

I have an azure sql db with a partitioned table with a partition function on varchar datatype. In an Etl process I will get a list of partiton values as a parameter that i will the need to: check ...
1
vote
2answers
26 views

Oversize DB restore to SQLExpress

I made a back of an express DB, it was 14.7GB big (how is this possible) but anyway, I need to restore it back to express but obviously it is too big, but there must be somehow I can do this. I need ...
8
votes
2answers
346 views

Creating an Index that is not used by a (SELECT) query reduces performance of that query

I have just watched this video by Pinal Dave. He has a SELECT query that produces ~370k reads in tempdb and ~1200 reads of the table the query is SELECTing from. He then creates an index (Let's call ...
0
votes
2answers
29 views

Grouping data with an interval but with overlapping time range groups

I currently have constructed a group by query in which returns a list of time with a 15 minutes interval and its list of count aggregate SELECT CONVERT(DATE, DATETIME_COLUMN) AS DATE, ...
0
votes
1answer
149 views

Slow query compilation, lots of reads during compilation phase

I have a query of the following form exec sp_executesql N' UPDATE table SET column1 = 1, modify_date=N''2020-02-12 04:55:59.000'' WHERE (column5=@P1 AND column6=@P2 )',N'@P1 ...
1
vote
1answer
30 views

Why do I have to change Media Sets to create a Full Backup to a new file and what does that mean?

I'm trying to create an ad-hoc Full Backup as a snapshot of the database in time and when I added a new file to backup to I get the following error: I see I can surpass this error by going to Media ...
2
votes
2answers
92 views

Truncate partitioned table with values from partitioned column?

I have partitioned a table on a column with varchar datatype. The documentation on truncate table with partitioning uses the partitionids: Truncate table parttable with (partitions (1,2,5)) I only ...
0
votes
1answer
26 views

SQL SERVER: Header Blocker Across Multiple Databases with wait type CXCONSUMER

We have an instance of SQL Server which has multiple databases. A process in one database seems to be blocking a process in another database. When I look in activity monitor I can see a header blocker ...
1
vote
1answer
57 views

Upgrading to SQL Server 2017 from SQL Server 2012 time allowed [closed]

We have a customer running a SQL 2012 database for their document management system but this needs to be upgraded, They want to upgrade to SQL Server 2017 and looking at the forums the best method is ...
2
votes
1answer
36 views

Removing Database from AlwaysOn Availability Group

I have a 2-node SQL Server AlwaysOn Availability Group. When I execute this T-SQL command on the Primary server: USE master go ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE TestDB it removes the ...
0
votes
1answer
34 views

Backup from Azure SQL Managed Instance and restore on an On-Prem version

As per my understanding Azure SQL MI runs SQL Server under the hood. So a backup from MI can be restored on latest version of SQL Server (2019). I have a task to find a rollback strategy if the move ...
1
vote
1answer
64 views

Can sp_BlitzFirst pass the @ExpertMode parameter to sp_BlitzWho [closed]

When running sp_blitzFirst @expertmode = 1 the first result set(result set of sp_blitzwho)does not return nearly as many columns and it does in Brent's video. With a little troubleshooting it was ...
-1
votes
0answers
32 views

how to determine what to change to make a scan into a seek [duplicate]

Analysis of index usage shows some that are used frequently with scans vs seeks. I need to find how to determine what to change to turn the scan into a seek. Example in the last 24 days, the index ...
1
vote
1answer
41 views

Update statistics

For the data retention purpose I am deleting records from some 60 tables. Some of the tables have less number of records while some have more. I don't want to update the stats for all the tables. Is ...
0
votes
1answer
15 views

Is there a way to specify which schema the database task “Export Data-tier Application” exports for the other entities besides the tables?

I noticed in the Export Data-tier Application database task, you can specify which schema to export data from in regards to tables, but I also want to exclude certain schemas for the other object ...
0
votes
2answers
42 views

How to get first and last value by same item in sequence, with possibility of the same item occurring again

Similar to this question but with the possibility that the same name can appear again in the following rows. I have this table: id name value 1 Daniel 3 2 Daniel 7 3 Daniel 2 4 Carol ...
0
votes
0answers
37 views

Audit sysadmin activities on SQL Server

because of GDPR we need a system that monitores all activities that administrators does on an SQL Server. First goal is to audit sysadmin activities (selects too), but if possible activities of ...
2
votes
2answers
63 views

Query list of logins that have logged in the last X days on SQL Server

Is there any good query that included internal system objects like sys.dm_exec_sessions or sys.dm_exec_connections that would list logins that have connected to the SQL server over the last X days? 15,...
0
votes
1answer
52 views

How to capture data from a PowerShell script that runs as part of a SQL Agent Job?

I have a PowerShell script called from an Agent job which opens an Excel file, converts it to .CSV and saves the .CSV file in a particular location, ready for me to import it with BULK INSERT. What I ...
1
vote
1answer
28 views

Can I add new tables to a transactional replication subscriber

I'm looking into using transactional replication in MS SQL Server 2016 to create a reporting database from our production HR system. I'm wondering if it's possible to create new stored procedures and ...
1
vote
1answer
74 views

How memory allocation happened in SQL Server

Suppose I have a 32 GB machine, now I have allocated 12GB memory to the maximum server memory for my database application to perform. Now I am running some load simulation which is going to consume ...
-1
votes
2answers
72 views

T-SQL Query performance

We are using SSIS package to execute the following statement. SQL statement delete from ABC OUTPUT DELETED.[ABC_Id], 5 into XYZ (OId, OCode) where ([ABC_Id] = ?...
0
votes
2answers
65 views

What's the best way to migrate a database to a server of a lower SQL version?

I have Database A on SQL Server 1 that is SQL Server version 2016. I need to migrate it to Server 2 that is SQL Server version 2012. Database A is pretty small, only about 5 GB total. I don't think ...
1
vote
3answers
50 views

Carry forward non-null value with a windowing function? [duplicate]

In SQL server, I often see this referred to as a "smear" and there's blog posts about using a "quirky update" to do it. But how would I do it with windowing functions. For example, if I have: name ...
0
votes
0answers
73 views

Performance of Polybase [closed]

We've been experimenting with Polybase on a SQL Server 2019 (CU2) box and the performance is pretty awful. We've gone as far as scripting every statistic from the remote server onto the external ...
1
vote
1answer
108 views

Funky cardinality estimate skews, high memory grants and missing statistics

I have a select statement within a procedure that is causing me some grief at the moment. The query estimates about 100 million rows and that in turn grants large chunks of memory (about 8 gigs per ...
3
votes
1answer
112 views

Changing to compatibility level 150 makes SQL Server consume all available memory [closed]

We're moving an existing DB to a new SQL Server 2019 instance. If we change the compatibility level to 150, nothing works. Task manager shows that sql server service is consuming all available ...
0
votes
0answers
42 views

Within a given database: what objects cant have the same name?

In a given database what objects (Table, View, Index, Constraint, Trigger etc) can or can't have the same name? So a table or view can have the same name if they are in a different schema. Is this ...
0
votes
1answer
30 views

Find the nearest time from another table and add column

Please help. I need to create a view of Assembly details and when they have gone through a particular machine. I have two tables. Table 1 has the main assembly data and Table 2 has the machine data. ...
0
votes
1answer
31 views

How to create a new user in a remote offline database without elevating roles [closed]

An enterprise C++ system that needs to be "translated", uses ODBC for connecting to SQL Server Database. The system has one central main database, many distributed local databases, which are actually ...
0
votes
0answers
39 views

HOW to program a daily task directly on SQL Server (export data to CSV file )? [closed]

I would like to program an export of data in CSV file from sql-server (job) and put a date on their names so that they do not get overwritten. How can I do it? what I already tried to do: first , I ...
1
vote
2answers
74 views

Viability of a large database without using indexes and some other things

I'll explain my situation as briefly as possible. My group is working for another company and I'm mostly creating stored procedures for making reports. The twist is that, last month, they decided to ...
8
votes
3answers
664 views

Is it possible for master, tempdb, model and msdb to have a database_id other than 1,2,3,4 respectively?

SQL Server System Databases, as far as I know, always have those same IDs and I have seen lots of maintenance scripts on the internet relying on the predicate WHERE database_id > 4 to exclude them ...
0
votes
1answer
30 views

Query to get data from 2 different tables is taking long time

I have this query, select Column1,Column2, etc.. from Db1.dbo.Tb1 b Where b.Id1 in ( select distinct Column3 as Id1 from Db2.dbo.Tb2 s where s.Id2 <> '' union select distinct ...
0
votes
1answer
45 views

Optimizing calculation query

In a legacy project I've the following query: SELECT concat(SR_PO, '-', SR_RlsSequence) as Order, count(*) as Todo, sum(isnull(SR_Enabled,0)) as ToLoad, sum(isnull(SR_Done,0)) as ...
0
votes
1answer
45 views

Help with SQL CASE WHEN using % Query

I have two columns in a SQL Query ( Estimate and Billed ) which contain un-formatted numbers. I am trying to write a CASE WHEN query for when the BILLED column is between 50% and 70% of the BILLED ...
0
votes
1answer
22 views

How To Grant A User In AD Groups Which Is Denied To DML Commads, To Update On A Spesific Tables

I have a Active Directory user account named SQLQRY in my sql server. SQLQRY is permitted only read data. Those users can use onyl select statement. I need to give just update and delete permission on ...
0
votes
0answers
31 views

Index Rebuilding on Live Server [duplicate]

Hi I have one live server A , db A and another replication server B , db B on stand by. I am using Microsoft SQL Server 2014. I plan to put the live server, db on stand by and turn on the the ...
0
votes
1answer
29 views

SQL Server High Disk Queue, Low CPU

I'm running a large-ish SQL Server Enterprise 2016 Data Warehouse where we've for some time now had issues where we are seeing the following symptoms: High Disk Queue length, including 100% activity ...
0
votes
1answer
22 views

Find the value that is unique [closed]

I have a table with Cust_num and trans_id (1,2,3---,50). There are multiple cust_num who have performed all transactions and there are few customers who has performed trans_id 1 only. I want to ...