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)

1
vote
2answers
81 views

How can I improve performance on a 3 table query?

I'm attempting to analyze a resource drawdown (the percentage of time individual units are being utilized during a reporting period, e.g. 2-weeks or 1-year). I need the aggregation down to the ...
3
votes
1answer
48 views

SQL Server Clustering - Active/Active or Active/Passive?

What is the logic of the Active/Active cluster vs Active/Passive cluster? I have searched a lot and read lots of documents but have not found the answer. In active active cluster can I have 1 ...
3
votes
1answer
46 views

How does SSMS get a list of files in a backup?

When restoring a database that has multiple files, SSMS gives you the option to restore as other paths and file names. How does it retrieve the list of files from the backup? Can I do that in a SQL ...
0
votes
0answers
32 views

SQL Server Trigger: Move deleted rows from 4 different tables to an archive table

I have a garage database with 20 tables. 4 of these tables - tCar, tCustomer, tAddress and tTelephone hold data about when we service a car. After a few months I want to be able to delete the data ...
1
vote
1answer
18 views

Managing Security for a Log Shipped db across two (unconnected) domains

SQL Server 2008 (soon to be 2012). We have a vendor that is providing us with log shipped backups of our production database. We use this log shipped database for BI staging, reporting, etc. I've ...
10
votes
1answer
172 views

Where does SQL Server physically store the IDENTITY VALUE for a table?

I'm hoping someone can point me in the right direction on this one. Here's my workings out so far. SELECT * FROM sys.identity_columns is a system view which gives "last_value" but the definition for ...
1
vote
1answer
37 views

Unknown System SPID blocking user SPIDs

We are performing a migration of 3rd party data and it is hanging. When we investigate we see SPID 38 (a system/background SPID) is the blocking user SPIDs trying to do the migration. Unfortunately, ...
1
vote
2answers
45 views

Database logging optimization and maintenance in SQL Server

One of the Web applications I am working on, uses NLog to log debug and error context information in the database. Basically, it performs a moderate number of inserts (I appreciate some tens of ...
0
votes
1answer
22 views

Should a Sql Server be accessible through the service's virtual network name?

In a working SQL-Server failover cluster environment I can open a SQL-Server connection using the configured service's IP address independantly of the current node it is on. I wonder if the assigned ...
3
votes
1answer
37 views

What is the impact of temporarily setting ALLOW_PAGE_LOCKS ON

The MSDN ALTER INDEX page says "An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF." I'm modifying our index reorg/rebuild process to be sensitive to indexes that have ...
0
votes
0answers
23 views

Creating clustered primary key index with ONLINE option

I have primary key clustered index which is constantly modified (insert and update) and read (select). Now, in order to change one of the types of the columns from which it is composed I need to ...
2
votes
1answer
22 views

Windows Authentication fails with “Cannot generate SSPI context”

We have a Windows Server 2012 with SQL Server 2012 instance. Its services used to run under an Active Directory account from an old domain, from Windows 2000 Server. A few years ago we replaced our ...
1
vote
1answer
28 views

no free memory but low total memory

I have SQL Server 2012 SP2 that has a max memory of 25.4GB but SQL Server is only using 15GB. When I check free memory with the PerfMon SQLServer:Memory Manger Free Memory (KB) counter only 40MB is ...
0
votes
0answers
26 views

Application Using ODBC Data Source To Connect To A Database Not Working?

Ok so I have a program that needs to access a database in SQL Express 2008 using an ODBC Data Source. The program is used for the inventory of objects in the SQL database using a gui. It basically ...
2
votes
1answer
34 views

Sql Server Cluster with multiple nodes

Does it make sense to think about a failover cluster configuration where less than 50% of all included nodes are standby? For example instead of setting up 4 clusters with two nodes each where one is ...
4
votes
2answers
366 views

Help undoing an accidental restore on SQL Server 2014

Yesterday I made a serious mistake¹: Restoring the wrong database. Background (you may skip this section): I right-clicked the Test database and proceeded to the "Restore Database" section as ...
0
votes
3answers
61 views

Get all records from inner joined tables if second table does not have data

I follow below syntax by which I achieve the result. but it takes time IF NOT EXISTS(select * from #tblxyz) BEGIN INSERT INTO #tblxyz select NULL END Select a.* FROM tblabc a ...
3
votes
2answers
231 views

Identify the type of a SQL Server backup

Long story short, we had a hosting plan on www.hostforlife.eu (btw, terrible service even on more expensive packages, plus they lost ALL files on their servers and we had to restore our backups as ...
1
vote
1answer
20 views

Accessing file share from ETL package in SQL Always-On configuration

How can I access the file share from ETL? Here are my environment details: I have 2 nodes in a windows cluster. node 01 and node 02 SQL Server 2014 is installed on both the nodes. Both are Windows ...
3
votes
1answer
41 views

What is the SQLCLR wait type in Activity Monitor within SQL Server Management Studio (SSMS)?

When I check our SQL Prod server, every time I open Activity Monitor I always see SQLCLR on the first list on the resource wait section in the Activity Monitor. It always has a high number. What is ...
1
vote
1answer
62 views

SQL Server 2014 SP1 installation iso file question

I read a lot regarding the issues with the original SP1 slipstream file and if the file names to download can be clarified it will be very helpful. What files do I need to install SQL 2014 SP1 CU6? ...
2
votes
1answer
41 views

Update with CTE & OUTPUT blocks on concurrent calls

I was having deadlock issues, so I started using this CTE trick that I read somewhere. No more deadlocks. But now each client that I add slows down (blocks?) the sproc. E.g. a 1-2 second update with ...
1
vote
1answer
20 views

Is it possible to attach a description field to the records in INFORMATION_SCHEMA.TABLES?

In SQL Server, [database name].INFORMATION_SCHEMA.TABLES has the following columns: TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE This table has no additional fields, such as "DESCRIPTION" ...
2
votes
1answer
25 views

SQL Server 2012 In-Place3 Upgrade and Timing of Database Version Change

We are updating several SQL Server 2008R2 instances to SQL Server 2012 SP3 CU2. I know that the database versions will be changed from 100 to 110 during this process (e.g. once finished, the databases ...
1
vote
0answers
19 views

Meta Destination Owner Differs From Actual Owner

How can the replication meta data table differ from the properties of the publication through the interface; for instance ... SELECT destination_owner FROM distribution..MSarticles Yet, when ...
1
vote
0answers
29 views

Index on a highly updated field causing excessive locks in SQL server

I'm in a bit of a conundrum about what the best course of action on this (bad?) index is. I have a table which i use as a notification holder that i watch for database changes with. CREATE TABLE ...
3
votes
3answers
173 views

How to return rows between two datetimes stored as date and time?

How to view the result between 2 dates and times? My current Code: SELECT * FROM [DB].[dbo].[TABLE] WHERE [USER_ID] = '005' AND [DATE] BETWEEN '03-04-2016' AND '04-04-2016' AND [TIME] >= ...
0
votes
1answer
45 views

Pull the latest Employee and Manager Comments Based on Modified date

I have a table Comments which is linked to the Plan table. Each time the user adds a comment on a specific plan, the Comments table is populated. This table stores the historical data. I need to pull ...
3
votes
2answers
150 views

Does SSMS follow the same support lifecycle as SQL Server?

I've found information about the support lifecycle for SQL Server (such as this page for SQL Server 2008/2008R2), but haven't found any information about SSMS. Does SSMS follow the same support ...
2
votes
1answer
34 views

Permissions on Trigger Problem

I am working with a database in MS-SQL 2008 R2, and I'm running into a problem. The basic setup is this: There are two tables of interest: dbo.Assesments and audit.Assesments. dbo.Assesments is the ...
0
votes
0answers
31 views

SQL Server accepting Access queries?

I am currently working on migrating a legacy codebase from VB6 and Access to VB.NET and SQL Server. I have run the Microsoft SQL Server Migration Assistant successfully and populated a SQL server ...
4
votes
1answer
91 views

Update with default values in case clause

I have a single table like CREATE TABLE Mytable (ID int identity, Name nvarchar(10)); GO INSERT INTO MyTable (Name) VALUES ('test1'); INSERT INTO MyTable (Name) VALUES ('test2'); GO ALTER TABLE ...
-2
votes
3answers
88 views

Split URL string into columns

Given the string: URL:'ID=1!&user=xxx&depart=21&companyId=43&workOrder=67' I need to get the result: Sno ID user depart companyId workOrder 1 1! XXX 21 43 67 ...
0
votes
0answers
26 views

One-Way Database Synchronization with AlwaysOn Implementation

please give me advice for my database architecture.. According to below image AlwaysOn primary replica are synchronizing with two local database server. Our company is working on virtual call centre. ...
4
votes
1answer
219 views

Partition by ascending and descending performance

I have a table, and for a given set of fields a, b and c, I need to get the first and last rows ordered by d and e, and am using ROW_NUMBER to get these rows. The relevant part of the statement is... ...
1
vote
1answer
149 views

Is select case when null better than iif(exists)?

I try to reduce huge performance issues on our application database. We use SQL Server 2014 and we have a lot of views. I wonder if there is a significant impact of rewriting left joins to use ...
0
votes
1answer
17 views

Communition Link Failure

I want to make a DNS to a sql database by SQL Driver. so I do it as it should be done. I have over hundred Clients with Windows 7 OS. All of them can connect to the database easily just a few of them ...
0
votes
0answers
28 views

Rstudio (on Linux) Connectivity to SQL Server 2012

I've been attempting to make RStudio connect to an MS-SQL database, but I've only been having issues with the Microsoft ODBC drivers. The system and installed software: Centos 7 64bit RStudio (and ...
1
vote
3answers
171 views

Finding count of null values in 500 columns of a single table

I have 500 columns in a table in SQL Server. I want to find the count of null values in each column. Is there a way to do it using a cursor and dynamic SQL?
0
votes
1answer
45 views

Upgrading SQL Server 2012 Enterprise SP1 (11.0.3000.0) to SP2 or SP3

We need to upgrade our production SQL Server 2012 Enterprise SP1 (11.0.3000.0) to SP2 or SP3. So which one would be more stable out of those 2 service packs and also how could this be achieved with a ...
2
votes
2answers
53 views

How to fix a messed up replication on MS SQL Server

I restored a database from a backup. The database uses replication to publish to a different server. Assuming the database restore would break the replication, I tried to delete the replication and ...
3
votes
2answers
79 views

Is there a way in SQL Server to make a table only able to insert by trigger?

The title is pretty self explanatory but if your'e curious as to why I want this; I want this because I have an archive/log table that stores past values of an active table and due to this I don't ...
1
vote
1answer
59 views

Queries not Running in Prod [duplicate]

I've an update statement which uses 2-3 tables. It runs in less than a second in QA but it takes forever in Prod. The number of records are exactly same and all the indexes are same too. In fact Prod ...
4
votes
2answers
215 views

Why does removing these LOWER calls change the execution plan like this?

While troubleshooting a slow query I was misled for a while by the actual execution plan telling me a sort late in the query where there should be few rows was taking >80% of the query time. At the ...
2
votes
1answer
46 views

SQL Merge Statement Spending Large Amount of Time Sorting on Primary Key

We are looking to use the SQL MERGE statement in SQL Server 2012 to handle data replication for a data warehouse that we have access to from the manufacturer of a 3rd party application we use for data ...
0
votes
1answer
696 views

Backup SQL Server to Google Drive

I have Microsoft SQL Server 2014 on an Azure VM (Windows Server 2012 R2) with one database. Is it possible to configure an automatic backup to my Google Drive folder? Maybe a special tool or ...
0
votes
1answer
38 views

Is there anyway to tell when the last query was made against a database? [duplicate]

Is there anyway to tell when the last query was made against a SQL 2008 R2 database without downloading a third-party tool ?
4
votes
1answer
112 views

Identical tables, identical query, completely different execution times

We have two tables with identical columns and indexing (No indexing at all, basically). We run the same query, which in case of the original table takes 5 seconds to run; in the case of the new table ...
2
votes
0answers
56 views

MEMORYCLERK_XE Memory Usage Will not Clear

We've been having an issue where memory usage from Extended Events grows to over 1 GB and then will not clear - even when we stop all XEvent session on the instance. This hasn't been an issue yet on ...
0
votes
3answers
79 views

Select distinct value from row if condition exists

I am new to SQL and I am having trouble constructing a SELECT statement. What I would like to do is select distinct parts that have a location of A1 if it exists. If a part has no locations of A1 I ...