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)

3
votes
6answers
303 views
+50

Database design for an exam

I am designing database for an exam, and I am stuck. I do not know how to do it. Here is the relevant information: Student answers 10 questions. Each question has 3 options (answers) and student ...
9
votes
4answers
912 views

SSMS Registered Servers with ReadOnly Application Intent

We are working on SQL Server 2014 POC testing with AlwaysOn and one of the user asked about saving the configuration of SSMS with ReadOnly Intent using the registered servers in Local server group. ...
4
votes
2answers
63 views

SQL Server- Limit to Concurrent table queries?

In SQL Server, or RDBMS systems in general, is there a limit to the number of concurrent queries that can be made against a table? How is it that N number of users can access the same physical data ...
1
vote
1answer
27 views

Can SQL Server R Services be enabled on a readable secondary?

To avoid excess load on the primary SQL Server, I'd like to install and enable R Services on a secondary replica of the primary server. Is this possible? Also, can I push computations from a remote R ...
0
votes
0answers
21 views

SQL Server 2012 failover cluster node won't start cluster services

We have 2 node SQL server failover cluster setup in our environment. Node2 SQL server machine got shut down. I turned it back on and connect back to the failover cluster. It successfuly connected ...
-3
votes
0answers
27 views

How to calculate the difference in values between two tables

There are two tables T1 and T2, T1 contains the current values, in case the user Needs to update/correct the values. The concerned row from T1 moves to T2 and a new tuple is added to the T1 with ...
0
votes
0answers
22 views

How keep overview about database permissions?

As DB-Administrator I need to keep overview about my servers and databases in general - and from a security context about logins, users, db-roles and permissions in particular. It is possible to ...
3
votes
1answer
56 views

DBCC IND not working

I'm trying to get the pages used by a clustered index on table1 in a SQL2008 R2 DEV box using the below query. dbcc traceon(3604, -1) go dbcc ind (0, 'dbo.table1', 1) go but it still displays ...
15
votes
4answers
27k views

How do I move SQL Server database files?

I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server. How can I do this?
1
vote
1answer
172 views

I am getting a failed login message in the SQL Server error logs however, nothing actually failed?

I have a server which runs third party software (called Visualcut) to email Crystal Reports to our users. When I log onto the Visualcut Server, I can open the software and then preview a report. This ...
1
vote
1answer
27 views

Recommended ways to upgrade AlwaysOn AG from 2014 to 2016

What is the recommended process of upgrading Availability Groups with one Sync Replica from SQL 2014 to 2016? My understanding is that in-place upgrades are not really liked among database ...
0
votes
1answer
82 views

SQL Query error driving me crazy! [on hold]

I'm wondering if anyone can find the mistake in my SQL-Query which I can't seem to find.. Been starring at the same lines of code for like 2 hours and it's driving me nuts! So, here's the query... (...
1
vote
1answer
26 views

Restrict access to Edition- or instance-specific databases

I have the following issue with SQL Server. On my Windows Sever, SQL Server 2008 Express Edition is running. Now I have installed SQL Server 2008 Web Edition on the same server. My problem is, when ...
0
votes
0answers
18 views

OraOLEDB.Oracle not showing up in Providers in Linked Server

Im trying to link the Oracle database to our SQL Server database. However OraOLEDB.Oracle is not in the Providers list on the Linked Servers. I have installed Oracle Database Client (12.1.0.2.0) for ...
0
votes
1answer
120 views

Unable to login to SQL Server using SSMS and SQL agent failing

I have SQL Server 2008 R2 ENT insalled on Windows server 2008 R2 ENT. I am unable to login to SQL Server locally after the installation. Getting below error: Connection handshake failed. An OS ...
0
votes
0answers
52 views

Why can't a user in a group with the sysadmin role see any stored procs in SSMS?

A developer who is in a "Developers" group granted the server fixed role "sysadmin" on the development SQL Server installation is unable to see any stored procedures in a database - only the "System ...
1
vote
0answers
44 views

Backup command wait long for completion of CHECKPOINT

I executed simple query to backup my 189GB database on a server with 32GB RAM. BACKUP DATABASE DB_NAME TO DISK='D:\BACKUP\FILE1.BAK', DISK='D:\BACKUP\FILE2.BAK', DISK='D:\BACKUP\FILE3.BAK', DISK='D:\...
0
votes
1answer
251 views

Breaking up long running query into batches runs fine for a while and then produces fatal error

I'm using a variation of this post which details using "careful batching" to delete records in batches from a table that contains 38MM records. The script below runs for a while and shows results ...
1
vote
1answer
2k views

RegisterAllProvidersIP 1 or 0 For The Cluster Resource

I have set up clustering across two sites, at each site there is a single instance of SQL 2014 enterprise and I am using Availability Groups. I know I need to set RegisterAllProvidersIP for the AG ...
1
vote
2answers
24 views

How can one identify if FK's needed to be indexed apart from the NC indexes already residing

I was going through many articles, which said indexing the FK's is beneficial for good performance, specially for the environment where most of the reporting is done. I have a similar env where there ...
0
votes
0answers
32 views

SQL 2016 Installation Error

I am trying to install sql 2016 standard on a windows server 2012 standard edition, and I am running into an odd error, hopefully someone can point me in the right direction. I am getting account ...
0
votes
1answer
143 views

Communication Link Failure in SQL

I have an SSIS package that runs daily in SQL agent, there are days that the job is failing due to "Communication Link Failure" but sometimes successful. I search the net for some solutions and there ...
1
vote
1answer
32 views

Table frequency metric Count (minimum 2 hits every 5 seconds using a datestamp within 24 hours)

I need to find out how many times a table has an insert within a 24 hour period per 5 seconds, if the table is inserted at least twice (2x) or more for each 5 seconds of the day, it should be counted. ...
3
votes
1answer
113 views

.NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314)

I am migrating a database application from Windows 2008 R2/SQL Server 2008 R2 to Windows 2012 R2/SQL Server 2016 that uses a third party .NET CLR assembly to parse strings. The error I get is: An ...
4
votes
2answers
632 views

Cannot remove char 0x0000 from NVARCHAR column

So, I know all about the Replace function and a bug with char(0). I have a column (NVARCHAR(128)) that has some NCHAR(0x0000) characters from a bad import. I'm using SQL Server 2008 R2. The ...
5
votes
2answers
4k views

Why isn't the objectname and indexname populated in deadlock graph?

I got a request to check out some errors on a 2008 sp3 server (10.0.5500.0 - Enterprise Edition (64-bit)) and see if I could find deadlocks that were happening earlier. Traceflag 1222 wasn't enabled ...
0
votes
0answers
25 views

Top Recommended Books/Courses For Learning Mid-Advanced Database Programming Beyond Basic CRUD Operations [on hold]

I have been a SQL DBA for the past two years and am looking to learn more indepth database programming to up my skill set and make me more attractive to potential employers. Currently with my day to ...
6
votes
1answer
2k views

Storage order vs Result order

This is a spin-off question from Sort order specified in primary key, yet sorting is executed on SELECT. @Catcall says this on the subject of storage order (clustered index) and the output order ...
0
votes
1answer
37 views

Use of nonclustered index [duplicate]

I have a table that does not have a primary key. This is because there is no unique field. So I created an index on SerNum, ScanDate so I could look at the table in SerNum, Scandate order. It appears ...
10
votes
5answers
11k views

How to monitor deadlocks

When do you start troubleshooting a SQL Server 2005/2008 deadlocks and how? The alert is turned on SSMS via SQL Server performance condition alert, objects->SQLServer:Locks, Counter->Lock Waits/Sec, ...
2
votes
1answer
35 views

How to Load Balance Availability Group

I have 2 Servers under SQL Server Cluster (Server 1, Server 2) 2 Databases configured with Availability Group (Database 1, Database 2) Server 1: Database 1 PRIMARY REPLICA Database 2 PRIMARY ...
0
votes
2answers
301 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 ...
4
votes
1answer
3k views

PAGELOCK Conversion Deadlock From Update to Intent Exclusive

A customer of ours is experiencing frequent deadlocks. The deadlocks are mostly on the same UPDATE statement. The deadlocks follow a pattern in which both SPIDs have acquired an update (U) lock on a ...
7
votes
0answers
90 views

Is it legal for SQL Server to fill PERSISTED columns with data that does not match the definition?

I'm following up on this question about strange values in a PERSISTED computed column. The answer there makes a few guesses about how this behavior came to be. I'm asking the following: Is this not ...
-1
votes
1answer
132 views

Comma Separated values in sql server [closed]

I have three tables with relationship. TableDetail ID(PK), TableNo BookingDet ID(PK), BookID(FK BookingMstr.BookID), TblID(FK TableDetail.ID) BookingMstr BookID(PK), Date I want to get table ...
0
votes
0answers
15 views

How do you find the VPC IP address of an AWS RDS instance

I'm trying to copy a table from my test server to my production server. Both are hosted on AWS in RDS instances. I found this thread which has the following post *second last at the time of writing): ...
13
votes
2answers
663 views

In SQL Server 2016, what is the difference between Always Encrypted and Transparent Data Encryption?

As I write this I am still awaiting the official release of SQL Server 2016 so that we can explore the usefulness of its "Always Encrypted" feature. I would just like to know what the specific ...
0
votes
0answers
42 views

Problems caused by SQL Server 2016 Query Store

I have been testing SQL Server 2016 (Standard) on an Azure VM and have encountered a problem. In my SQL logs I am seeing SQLExceptions and eventid 1001 and this is happening every 15 minutes. 2016-...
9
votes
3answers
23k views

What is the use of “Backup set will Expire:” option while creating Backup Database task in Maintenance plan

While backing up the database, I would like to use "Backup set will Expire:" option to delete/overwrite old database backups. I am not able to use this option. Any advice on how to use "Backup set ...
1
vote
1answer
70 views

Why am I seeing OPTION (MAXDOP 1) in my Actual query plan even though I've not set it in the stored proc?

SQL Server is set with MAXDOP 0 by default. In the stored procedure I'm testing I keep seeing WITH (MAXDOP 1) in the Actual Execution Plans even though I've not specified any hints other than ...
1
vote
1answer
44 views

Update all tables containing column name

In SQL Server, I can find all tables containing a COLUMN_NAME: SELECT * FROM INFORMATION_SCHEMA.columns WHERE COLUMN_NAME = 'COLUMN_NAME' How can I update all of these columns like: UPDATE <...
1
vote
1answer
21 views

SQL Server Table Design for Report - Is OLAP Schema Suitable

A work colleague is developing SQL Server database tables for a reporting project. Basically, the only activity on the table will be populating it from a ETL process (SSIS). He insists on creating a ...
17
votes
2answers
2k views

If positive, sum all items. If negative, return each one

I am needing to find a way to SUM() all of the positive values for num and return the SUM() of all positive numbers and an individual row for each negative number. Below is a sample DDL: Create ...
127
votes
18answers
30k views

Should developers be able to query production databases?

Should developers be given permission to query (SELECT / read only) production databases? The previous place I worked, the development team had the db_datareader role; where I work now the ...
0
votes
1answer
30 views

When Execution Plan Cost Differs From IOPs and Time

Generally, the query execution plan's overall cost aligns with the statistical IOPs. However, I've run into a situation where this isn't the case; one plan, which uses no throttling of CPU, shows it'...
0
votes
1answer
68 views

Secondary data file is in 'Recovery Pending' state, but database is online

I got an issue. I have a database (Say, DBASE) which have three files - Two data files (namely D:\Data1.mdf and D:\Data2.ndf) and a log file (E:\log.ldf) and all are in different file group. The ...
1
vote
1answer
43 views

Installing SQL Server version 10.50.4000 is failing

I am attempting to install SQL Server 2008 R2 version 10.50.4000 on a virtual machine running Microsoft Windows Server 2008 R2. When I try to install SQL Server, I get an error that, when I look it ...
0
votes
1answer
19 views

Trying to get password has for a login through PowerShell

I've trying to get the password hash for a SQL authentication login via powershell. The code I'm executing is something like this Invoke-Sqlcmd -Query "SELECT LOGINPROPERTY('$LoginName', '...
4
votes
1answer
770 views

Cannot Delete FileStream Group from Sql Server 2008

We have migrated all our varbinary(max) column data to Azure Blob Storage and so we want to remove the old filestream columns that remain in our Sql 2008 database and the filestream filegroup but when ...
41
votes
1answer
33k views

MERGE a subset of the target table

I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning: It ...