All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

0
votes
2answers
12 views

Create new instance MS SQL Server 2014

I have installed MS SQL Server 2014 , but after installation there is no instance created. There is only the default instance. How make a new instance in existing MS Sql Server ? I found a lot of ...
1
vote
2answers
167 views

How to use SET operation in CASE clause in SQL?

I have the following structure, and I'm doing a SET operation in a CASE clause but it is producing an error: Incorrect syntax near SET Query: DECLARE @tbl_Result TABLE ( RowID INT ...
-2
votes
1answer
35 views

I have created a zip file using 7z for backup but its taking time to open [closed]

I have created a zip file using 7z for backup but its taking time to open. Any possibility of making fast. Please suggest me.
0
votes
0answers
35 views

AG Automatic Failover when SQL Server service shut down

I set up an availability group listener, and am able to failover manually without data loss. If I reboot the host OS, it fails over automatically. However, when I stop the SQL Service on the node ...
32
votes
5answers
4k views

Why does adding a TOP 1 dramatically worsen performance?

I have a fairly simple query SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc JOIN CORRESPONDENCE_JOURNAL cj ...
3
votes
1answer
42 views

How to enable query store on the SSMS object browser with Sql Azure?

I am following this guide: Monitoring Performance By Using the Query Store I am using SSMS 2016 CTP3.2 and Sql Azure V12 I have enabled the query store on the database properties. I can use the ...
2
votes
0answers
28 views

Create File,secondary FileGroup then move a table and restore database with 'Primary'

i created secondary FileGroup , linked a File then moved a table there and restore database with 'Primary' however the table name still there and when I use the select statement I got an error that it ...
3
votes
2answers
67 views

How can I extract the perfmon counter “Memory: Pages/Sec” using t-sql?

I've googled this and so far, no luck so turning to all you awesome gurus for help. I'm trying to figure out if there's a way to get the perfmon counter "Memory: Pages/Sec" using t-sql as I'm trying ...
2
votes
2answers
185 views

Use Distinct With Sum?

I want to sum a few values using case statements. My problem is compiler complains if I do not add a group by to the end of the query, which then that negates my Distinct, hah. For example, in my ...
4
votes
2answers
158 views

How can I tell what database/procedure is using a linked server?

I have 3 servers, one of which has a linked server configured that points to the other - I'll call this Server A. Server A has over 100 user databases for various purposes. Server B is running SQL ...
2
votes
0answers
47 views

SQL Server Check Constraint Lock Order

I'm having problems with SQL Server 2012 filetable. Filetable defines an check constraint on a path_locator field with definition as: /*ParentsAreDirectories*/ ...
4
votes
1answer
49 views

Why are transactions running as distributed (DTCXact)?

I am actually trying to analyze and solve some regularly occurring deadlocks that all include at least one transaction named "DTCXact", this makes me ask. I repetitively run the following query ...
0
votes
1answer
40 views

SQL Server 2012 Remote Access to Certain Users [closed]

Recently, I've started working on SQL Server 2012. As there is a risk in allowing anyone to access SQL Server remotely, I'm trying enable remote connections only to specific users within my developer ...
1
vote
1answer
55 views

Compare strings from two tables SQL Server 2014

I'm using these two tables in a SQL Server 2014 database: CREATE TABLE #FORMULAS ( [ID] [int] IDENTITY(1,1) NOT NULL, [DataSourceAddress] [nvarchar](255) NULL, [Description] ...
0
votes
0answers
11 views

How can i delete or clear log files in sql? [duplicate]

I would like to clear the space occupied by log files how to do that in an sql server
2
votes
2answers
50 views

DBCC Usage on production system

We have a system running SQL Server 2008 R2 on a Hyper-V VM (64GB RAM and 16 virtual cores). Another VM (the application server) within the system accesses the db server quite intensively for both ...
2
votes
1answer
43 views

Database in SIMPLE recovery model full using SSIS bulk insert task

I have a database in SIMPLE recovery model with 10GB free. The file I am trying to load through SSISs bulk load is 1GB. The table is empty (truncated prior to each loading), is a heap and has no ...
0
votes
0answers
21 views

How should we be alerted of failures in the SQL replication distribution agent job?

When setting up SQL replication, the default retry count for the replication agent job step is 2147483647, retrying every minute. This means that if replication fails, the failure notification for the ...
-2
votes
3answers
57 views

Extract Portion From A Decimal [closed]

I need to extract any value > .5 from a decimal. Here is sample ddl Create Table #b ( fv decimal(16,4) fv1 decimal(16,4) ) Insert Into #b (fv) Values ('.8032') How can I capture the .3032 and ...
-2
votes
1answer
38 views

Can Log Shipping be recovered and resumed by scripts after DR?

Server A has DB1 log shipping to Server B as DB1_LS in a DR environment. Before the company DR exercise, I scripted out the log shipping configuration from DB1 properties window so I basically got one ...
4
votes
3answers
80 views

Make queries and BCP faster on a very large table

This question is somewhat of a continuation to this: Huge data and performance in SQL Server, but different enough to merit its own question. I'm going to copy and paste some of the text from that ...
4
votes
1answer
63 views

Why is my @@Version unchanged after upgrading?

I have SQL Server 2008 R2. I installed SQL Server 2014 with the option: Upgrade from SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012 and the process ended correctly. But ...
3
votes
3answers
307 views

Restore database to point in time from full backup only

I have a database with recovery mode set to FULL. It's a dev database and I have two full backups dated 19th and 27th i.e. latest. One of the developer did some changes on 26th and I want to rollback ...
0
votes
0answers
19 views

DBCC Msg 2576 The Index Allocation Map (IAM) [duplicate]

How do I resolve this error? Msg 2576, Level 16, State 1, Line 109 The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:32707) in object ID 0, index ...
3
votes
3answers
551 views

Who is the person using a SQL login?

Is it possible to find out who used a SQL-Server login? Let's assume we suspect a person to abuse a SQL login that is supposed to be used by an application only to logon to SQL-Server. Is the domain ...
-1
votes
2answers
67 views

Calculating multiple medians

I have defined function with table value parameter which returns median as per values passed to it. Function defined as: CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY) ...
1
vote
2answers
97 views

Stored procedure working when run manually, not running in SQL Agent Job

When I execute a Stored Procedure through a SQL Agent Job, I get the following error message: Executed as user: NT AUTHORITY\SYSTEM. XML parsing: line 10, character 33, unexpected end of ...
3
votes
2answers
95 views

Convert int to RGB or hex color

I'm working with a database where colors are stored as integers. For some unknown reason, they are negative integers actually. Now, I need to use them in the CSS background-color property, which ...
0
votes
0answers
45 views

Cannot See Stored Procedure on Linked Server

Cannot see Stored Procedure on Linked Server. I have connected two server using linked server, I can see only tables and views. I'm not able to see stored procedures. I have set RPC to true.
0
votes
1answer
44 views

Conversion failed when converting the varchar value 'Hours' to data type int

Conversion failed when converting the varchar value 'Hours' to data type int. This is my query: Sum(Cast(Cast(DATEDIFF(HH, ld_time_tracker_start_time, fld_time_tracker_end_time)%3600/60 As int) ...
5
votes
0answers
58 views

Restored DB has data, DBCC CHECKDB is clean, but SELECT TOP 1 returns zero rows

Having restored a client's SQL 2014 EE DB onto our Developer Edition instance, without issue, then running a DBCC CHECKDB that found zero errors, and a clustered columnstore index alleging over 1Bn ...
1
vote
0answers
15 views

how columns ordering in sql server database table impact performance? [duplicate]

As I read about database design from logical model to physical databases, I noticed that the column ordering may need to be changed the sequence recorded in the logical data model. The DBMS is SQL ...
10
votes
1answer
153 views

Does CREATE TABLE SomeSchema.#TempTableName have a bug?

Simple test-bed: USE tempdb; GO /* This DROP TABLE should not be necessary, since the DROP SCHEMA should drop the table if it is contained within the schema, as I'd expect it to be. */ ...
9
votes
2answers
297 views

SUM of DATALENGTHs not matching table size from sys.allocation_units

I was under the impression that if I were to sum the DATALENGTH() of all fields for all records in a table that I would get the total size of the table. Am I mistaken? SELECT SUM(DATALENGTH(Field1)) ...
4
votes
1answer
47 views

SQL Server Memory Determination

We will be modifying a SQL Deployment in our Hosted Environment. We have a solution which is currently configured to use one Application (and not SQL) System Database (which holds Security and System ...
0
votes
0answers
42 views

Query Freezes and SSMS Crashes

I feel like I have the syntax set on my query, but anytime I try to run this SSMS freezes and I have to reboot and once I get my PC back up and running no records have been updated. The table only ...
1
vote
1answer
37 views

Return Bit Value based on previous records by Category

How would I return TRUE or FALSE if four of the last six records failed for a given MAKEID? So there has to be 4 failures (or FALSE records) in the last six MAKEID FAILURES CREATEDATE 101 ...
0
votes
0answers
14 views

Setting up SSRS on multiple servers on network

I have a group of virtual machines on the same domain running Windows server 2003 and sql server 2005 standard edition. I already have SSRS set up on one of the virtual machines which is working fine ...
1
vote
1answer
30 views

How to combine 2 SQL statements into one?

I have these 2 SQL statements. Each year, I need to add the new one for the new DB. How can I write it so it will just work for the new years instead of me manually editing this each year? Is there a ...
1
vote
0answers
40 views

How do I enable modify column order in Datagrip [closed]

How do I can enable the up and down buttons for column order in DataGrip? I'm using DataGrip 1.0, Build #DB-143.1410, built on December 15, 2015 with a SQL Server 2008 database. As an example, for ...
2
votes
1answer
77 views

How to select a subset from a query result

I have a query which calculates the Time Difference and Distance Between the Next and Previous Row: SELECT * FROM ( SELECT iVehicleMonitoringId AS PreviousId, ...
2
votes
2answers
46 views

Finding and listing all columns in a SQL Server database with identity [duplicate]

I wanted to RESEED all the identity columns in that database, so I need to create a script so I can find out which column in which table has the identity property.
2
votes
1answer
19 views

Merge data from a MSSQL backup

I have a backup of a MSSQL database that I would like to restore. The issue is that the server that I would restore it to already have a datbase with the same name, layout etc, wich contains data. Is ...
4
votes
1answer
53 views

Permission error when executing a 5.6 GB sqlcmd file

I am trying to run a very large file through the sqlcmd command and it works perfectly with files of 100 MB up to 1.4 GB. This is what I have tested so far, but when I try to run a file which is 5.6 ...
4
votes
1answer
46 views

Performance of a self join in a temporal table with a single day's difference between end_date and start_date

Title says it all really. A pretty standard temporal table design has a start_date and an end_date where the end_date is the day before the next record's start_date. In this case, there is an ...
3
votes
1answer
43 views

How is an Active Log different from MinLSN?

MinLSN is the LSN of the oldest uncommitted transaction: What exactly does Active Log hold? Isn't it supposed to hold all the uncommitted transactions? Does it hold any other log records which are ...
-2
votes
1answer
41 views

why unable to execute table-valued function?

Here is table-value function I have declared like: CREATE FUNCTION fn_GetMedian(@List TypeMedian READONLY) RETURNS INT AS BEGIN RETURN ( Select ( ( ...
4
votes
2answers
343 views

How can I pass column to function in sql?

I'm calculating median as: DECLARE @TEMP TABLE ( ID INT ) Select ( ( Select Top 1 ID From ( Select Top 50 Percent ID ...
1
vote
1answer
24 views

How does a transaction log backup deal with active log?

What happens to the log records that maintain any open uncommitted transactions when a transaction log backup starts? Will these records also be included in the log backup?
4
votes
2answers
71 views

Get 2 Most Recent Records By Product

I am stumped. I need to return a TRUE or FALSE by ProductId if the 2 most recent records created are 'pass' and the LEVEL values are different. PRODUCTID CREATEDATE STATUS LEVEL ABC ...