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)

2
votes
1answer
64 views

How to find out ALL the users that can connect to SSAS?

I have an user called mycompany\aives that is not listed in the list of administrators in the SSAS, not belong to any of the AD (active directory) groups listed there, however, he can connect to ...
6
votes
1answer
602 views

Dropping a large table

On our production db there is a table 180 GB, around 2 millions records, I want to re-create it as a new empty table in a new filegroup and delete the existing table. What is the best practice to ...
2
votes
1answer
273 views

Removing filestream from a database

I am trying to remove filestream from my database, so I have to do the following I would like to know how to remove the following: ALTER TABLE AMGR_Documents_Tbl ADD UId uniqueidentifier ROWGUIDCOL ...
1
vote
1answer
165 views

SQL Server: modify data file size and shrink

When I try to modify the Database File Size, it shows this error. Specified size is less than or equal to current size I tried this query: Alter database [dbname] modify file ( name=N'data-...
1
vote
1answer
21 views

Logins mapped to dropped database sql server

I was told to delete the logins that are mapped to a database ABC but unfortunately the database ABC has been dropped and no backups exist. Is it possible to find the logins that WERE mapped to ABC?
11
votes
2answers
808 views

Why does my WHERE clause benefit from an “included” column?

According to this answer, unless an index is built over the columns which are used to restrict, the query will not benefit from an index. I have this definition: CREATE TABLE [dbo].[JobItems] ( [...
4
votes
2answers
277 views

Creating MSSQL Performance Baseline Monitoring

In order to get an overview and compareable data my current task is to create a performance baseline to get some figures about the different productive sql instances. My thoughts are: I want to ...
4
votes
1answer
494 views

Database design for a car dealers website

I am creating a new website for all the car dealers in my region to show their cars on and i need your opinion on the database I have created because I have some doubts. For instance, is it best to ...
6
votes
1answer
82 views

Size of individual transactions in log file

is there a way to find out the size of transactions from the transaction log by looking at the log file in SQL Server 2008? I do have an unexpected bloat of my logfile from some maintenance jobs which ...
7
votes
2answers
122 views

Most efficient way to generate a diff

I have a table in SQL server that looks like this: Id |Version |Name |date |fieldA |fieldB ..|fieldZ 1 |1 |Foo |20120101|23 | ..|25334123 2 |2 |Foo ...
5
votes
2answers
983 views

Why does SQL Server Management Studio crash?

When I open SSMS I get this message: SSMS - SQL Server Management Studio has encountered a problem and needs to close. We are sorry for the inconvenience. What can I do? The issue persists ...
3
votes
1answer
69 views

SQL Server 2014 Availability Group failed

I have 3 servers on VMware workstation: Win 2012 R2 as ADDC -Cluster service. Win 2012 R2 as SQL server primary replica . Win 2012 R2 as SQL server secondary replica (auto fail-over). My ...
3
votes
0answers
112 views

How to manage hundreds of SQL jobs? [closed]

I work in Microsoft SQL Server database and I have to monitor and manage hundreds of SQL Jobs to see which job will execute on what day and what time. I also have to troubleshoot the failure of the ...
5
votes
3answers
289 views

Virtualize SQL High CPU

This is a client environment which needs significant overhaul so all I am trying to do is slow the bleeding. Situation CPU is getting crushed, often 80% or higher utilization VMWARE 5.5, Windows ...
2
votes
1answer
149 views

Does a database backup include all files? [closed]

If I have two files in my database (mdf & ndf), when I do a backup of the database with the statement: backup database mydb; are both the two files are backed up?
1
vote
1answer
36 views

Is there a query to see if all of my logins and password are moved during migration from SQL Server 2005 to 2008R2

I am trying to migrate my database from SQL Server 2005 to 2008 R2 test, I did follow back up and restore method to migrate the database, and I ran the QUERY provided by Microsoft to transfer logins ...
1
vote
0answers
152 views

SQL Server: error 7303 (architecture mismatch)

I've searched a lot, but I still didn't find out a solution. I have to create a linked server to get the data from a mdb file (MS Access). I use the SQL Server 2012 Express x64 on the Windows 8.1 x64. ...
1
vote
2answers
208 views

Turning a multiple row resultset into columns with multiple rows [duplicate]

I currently have a resultset that looks like this: Title Value Start Time              1998-01-04 01:38:00.000 End Time                1998-01-04 01:43:00.000 Product Name           ...
8
votes
2answers
7k views

Why scalar valued functions need execute permission rather than select?

I am wondering why, for scalar valued function, that I have to grant the user to execute rather than just a select? meanwhile a table valued functions works just fine with only select permission or ...
3
votes
1answer
318 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
150 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
166 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
573 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
196 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
274 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
232 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
221 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
165 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, ...