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
1answer
316 views

SQL Server Multi Tenancy Setup

What is the best way to use Multi Tenant Database Architecture when all of the "Tenants" will be from the same company? We are going to be using AWS and SQL Server for our backend system. We ...
3
votes
1answer
95 views

Shrinking Databases in a maintenance plan [duplicate]

Everything I've heard (including from Microsoft) says you should never shrink a database. Yet a software vendor (Symantec) is telling us to do DBCC SHRINKFILE on the SQL 2012 databases and logs ...
1
vote
4answers
116 views

Incorrect total using SUM of DATEDIFF results [closed]

When i run a few DateDiff(SECOND, value1, value2) it returns a lists of values 93, 185, 1879 which totals 2157 sum with the calc and with SUM() However, when i use DateDiff(SECOND, firstValue1, ...
2
votes
1answer
149 views

Order by values with most incidence

I have a table as this example: CREATE TABLE customerEvent( customerID int NOT NULL, eventID int NOT NULL ) customerID may appear in many records. I use the following query to identify ...
3
votes
1answer
156 views

Enabling Constraint Silently Fails

Why would SQL Server silently fail to re-enable a foreign key constraint? I recently attempted to re-enable a number of constraints using ALTER TABLE. Most enabled successfully; however, minority of ...
3
votes
1answer
52 views

How to get the AD account associated to a Proxy?

on the picture below, you can see, I have a sql server job called "Backup SSAS DBS_" that Run as: PowerShell_Proxy How can I find out the AD (active directory) account associated with that Proxy ...
1
vote
1answer
55 views

How can i enforce data integrity in this setup? [duplicate]

I have these tables: |-------------tblSchool -------------| | + ID | | + Name | | | | ...
2
votes
2answers
240 views

One SQL Server 2014 giving different password hash

I've been using script based off Microsoft's EXEC sp_help_revlogin script to synchronise SQL Server usernames and passwords between hosts. This has been working fine for a long time now on SQL Server ...
0
votes
1answer
19 views

SQL Availability Group Network

On a SQL Availability Group what IP/network carries the workload of moving changes to the secondary replica and how do you find this network?
5
votes
1answer
164 views

Cluster node upgrade of SQL Server 2008 R2 to SP3 fails when moving resource

I'm trying to update a cluster to SQL Server 2008 R2 SP3. I've installed SP3 on the passive node but when I failover the cluster it errors when running through it's upgrade scripts. The messages &...
10
votes
4answers
570 views

Is it poor practice to aggregate data from different tables into one?

Background I write a lot of large reports for and generally maintain a large health records DB (write SPs, functions, jobs, etc.). The original schema, and software that uses it, is from a different ...
3
votes
2answers
45 views

Combine the effects of separate delete and insert rows

How to I combine the two rows below: Date Id For_Id Type New_Value Old_Value ------------------------------------------------------------------ 2015-08-13 6 327 ...
0
votes
1answer
62 views

TOTAL column or VIEW that computes?

I've got a couple of tables to track volunteer workers at our non-profit. One stores Volunteers and tracks information about a volunteer such as the person's name, a contact number, primary location ...
7
votes
1answer
111 views

Clustered Index Help, where did i go wrong?

I was asked to review a very busy table and identify any areas of improvement. I have very limited power to change the table just the indexes. Table information 240 columns ~5 million rows Read ...
3
votes
0answers
192 views

How to set ALL the replication to DEADLOCK_PRIORITY LOW?

I have just got this email here: Then looking at the sql server log I find this deadlock was caused by one of our procedures (that was the victim) against the replication. O.K. in this case in ...
2
votes
1answer
64 views

Index usage and update statistics do not correspond?

In order to discover unused and wrong indexes in sql server I went through the following article: https://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/ It gives two queries ...
0
votes
1answer
50 views

Automate Backup of SQL Server Database Structure

I have a data warehouse which isn't huge only about 16Gb in a Microsoft SQL Server Standard Edition 2014 database. However, now that it forms part of our production system I don't like developing on ...
7
votes
2answers
4k views

Latin1_General_BIN performance impact when changing the database default collation

I have set the database collation to Latin1_General_BIN, to make string comparisons case-sensitive. Will this have an impact on performance? Will it have any impact on DML or DDL operations in the ...
1
vote
1answer
271 views

How to find out the account(s) under which the sql server is running?

I have just created a proxy and I need to grant access to the proxy to the account that runs the sql server agent. It will probably be a domain account. How can I find it using T-SQL?
-4
votes
1answer
44 views

Is it allowed to have " in sql server name? [closed]

Double quotes can be given in sql server name. Looking for answers
1
vote
2answers
90 views

Spotlight for SQL Server LockType explanation required

I have just started using Spotlight as I'm a software developer, but I am forced to identify database issues causing blocking (Spotlight shows me AWAITING_COMMAND at the points and all other processes ...
2
votes
2answers
46 views

Database - Resource usage statistics

I would like to find (using SQL DMVs) whether a database is actually in use and if so then how much of that servers resources it is taking. I need some ideas on how we can properly collect usage ...
3
votes
2answers
231 views

Queries on database migration

I have a SQL Server 2008 database of size around 1 TB. I want to migrate it to SQL Server 2012. I have few queries: To migrate, I will take full backup of 2008 and restore it on 2012. I want to know ...
2
votes
1answer
65 views

Microsoft TDE Permissions For Changing Keys

I am setting up roles as a layer of protection inside of MSSQL. One of the roles will only have the ability to change database encryption keys for TDE on the database server. The server will be using ...
1
vote
0answers
109 views

SQL Server 2012 memory issue [closed]

I am having performance issues on a Windows 2012 Server running SQL 2012. When I run the DBCC MEMORYSTATUS command I receive the following. Process/System Counts Value Available Physical Memory ...
0
votes
0answers
32 views

SQL Server 2012 memory issue [duplicate]

I am having performance issues on a Windows 2012 Server running SQL 2012. When I run the DBCC MEMORYSTATUS command I receive the following. ---------------------------------------------------------...
4
votes
1answer
50 views

Scripting Microsoft Data Warehouse Wizard

I am trying to automate the MDW setup process in my environment. Currently I have everything complete except for enabling the data collectors (usually uses a wizard as seen ). Has anyone seen or ...
1
vote
1answer
58 views

Sum currencies excluding duplicatied records

I have a CRMOffer table containing customerID and other CRMOffer data. And a Sell table containing customerID and sellValue and sellPK. Sell table has a explicit PK, CRMOffer sadly doesn't have one ...
7
votes
1answer
219 views

Ola Hallengren Index Script not Reindexing

First, I realize a similar question was asked and the poster had page count set to 1000 for a 679 page index; not what's going on. I have Ola's script set as @Databases nvarchar(max) ,@...
4
votes
1answer
42 views

Configure SSMS intellisense / web browser settings to accept UNC directory path inputs

I would like to navigate to open a directory path from an in-line commented UNC path. SSMS integrates with Windows Explorer and Explorer allows UNC navigation from the address bar. Indeed, when in ...
4
votes
1answer
164 views

Performance problems with TOP and user defined functions in views

I have problem with performance of queries on simple query on view. To keep things simple, assume my view is defined as follows: CREATE VIEW [mon].[ViewDevicesWithGroups] AS SELECT Id, Name, ...
4
votes
3answers
154 views

MS-SQL 2014 Log Shipping: Access permission question

Wonder if someone could provide some advice or point me in the right direction towards achieving the below objective please. Unfortunately I have not been very successful in finding a solution online. ...
8
votes
4answers
4k views

Is it possible to develop a SSIS with a SQL Server Evaluation Version

We are thinking about buying an SQL Standard Server for implementing an ETL via SSIS. Since it is very expensive for us I would like to test developing SSIS Packages on a free version. Since the ...
0
votes
1answer
1k views

SQL Server Management Studio backup not shown when restoring

I am trying to restore a database from a backup taken on another server. I am doing this by using SQL Server 2012 Management Studio. After I have taken the backup on server 1, I go on to select the ...
0
votes
2answers
65 views

Identical SQL Server databases hosted in the same physical DB server

We have hosted similar SQL Server 2014 databases TestDB and TESTDB_Copy on the same physical server. The two databases are almost the same and there may be minor differences in the number of records (...
1
vote
0answers
3k views

SQL Server does not exist or access denied" after installing SSMS

I recently installed SQL Server Management Studio (which also installed SQL Server 2016 to my local machine), and now am unable to connect to SQL Server from applications on my machine. It will come ...
1
vote
2answers
29 views

Dynamically changing an order number to end in _ERR

I'm trying to update multiple orders at once instead of doing them one at a time. Is there a way to dynamically update them all at once? For example: I have 5 orders ABC, BCD, CDE, DEF, EFG. I need ...
5
votes
1answer
50 views

TDE prep: key/certificate backup for restores

I am working in a dev environment to better understand TDE encryption. I have it working along with backups and restores on another server. Had a few questions, I know I need to backup the certificate ...
1
vote
1answer
265 views

Query to get yesterdays data only for a specific time interval

Here is my where condition which I use for daily data but I've got to change the date everyday. WHERE X.stage = 'Approval' AND X.T_ExitTime between '2015-08-06 06:00:00:000' and '2015-08-06 17:00:00:...
1
vote
1answer
56 views

Temporary permissions for DDL

I was hoping to use user impersonation to setup a user that has permissions to modify DDL, and only when I need to deploy DDL changes would I impersonate that user. This way I can use my windows ...
1
vote
0answers
66 views

AlwaysOn Availability Groups

We are looking at setting up SQL AlwaysOn availability groups in the following environment: we have two SQL 2014 Enterprise Servers on two different host Hyper-V 2012 servers. They each have ...
2
votes
1answer
926 views

Can't upgrade a SQL Express to SQL Standard

I have a 64 bit SQL Server 2008 Express R2 installed and I cannot upgrade it to a fully licensed 2014 Standard. When I try to update to SP 4, I get this issue: If I try to run the upgrade edition, ...
1
vote
1answer
126 views

use fulltext formsof inflectional to generate terms

Is there a way to use the FullText query components to generate the list of words in the FullText dictionary that are the inflectional formsof a word? This query would give me matches based on the ...
3
votes
2answers
340 views

Database Schema for Blank Database is large. 20MB

Is it normal for blank databases with just schema to be large? Or is there a way that I can reduce the size of the initial database?
0
votes
0answers
67 views

Tools to insert data faster in MySql database

I am trying to insert data from a flat file which is around 9 GB with about 15 million rows, I used SQl-server 2012 for that. I noticed data insert is much faster with sql server and it took around 30 ...
1
vote
0answers
17 views

Indexing AppFabric Monitoring Database

AppFabric is killing the page life expectancy and disk read metrics on my SQL Server. Does anyone know if I can add indexes to the AsStaging table or will that break things? There are some very ...
0
votes
1answer
149 views

Database structure for object with dynamic attributes

I am developing a proof of concept for a personalized email sending web application (like MailChimp, but with premade dynamic templates). The general structure is that I have my templates (different ...
-1
votes
2answers
43 views

error18470 when i try to connect to Database Engine

I am new in SQL Server. Wrongly I disabled my instance when I'd worked with Logins. Now I can not connect to Database Engine. Could you please help me?
1
vote
3answers
90 views

Sql Script takes much longer than estimated

In order to clean up a very large table holding binary Files in IMAGE Cols, I created the following sequence (based on this: Article) to get rid of a part of those image Blobs quickly: Select * into ...
0
votes
1answer
714 views

SQL Jobs Running - How to Kill

I ran the below query and it shows list of currently running jobs. However, in Activity Monitor none of these are running. How do I verify that these are/are not running? And how do I kill them? ...