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
0answers
194 views

Why can't we have multiple cascade paths?

You can see that many questions have being asked about multiple cascade paths. For example: http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths ...
2
votes
0answers
70 views

Output in SQL Profiler has results with Database ID as NULL

I ran profiler to filter out certain applications connecting an environment but the results has application for Database_id "NULL".. Am I missing anything ? Also another question , does all the ...
2
votes
0answers
75 views

SQL Agent AD account gets permission denied for ODBC connections in SSIS packages

We have an AD account specifically created for the agent service and when running SSIS packages that connect to databases we get permission denied even though we have granted that account access to ...
2
votes
0answers
27 views

Unable to install SQL named instance as it says RS feature exist, even after dropping it

We are trying to Un-install a named instance from one of our servers. But, don't know why it did not un-installed the reporting services even after proper selection was made during uni-install. ...
2
votes
0answers
49 views

Create Nested XML in a Sql Server Indexed (Materialized) View

This page https://msdn.microsoft.com/en-us/library/ms188276.aspx indicates that if you wanted nested XML (ie an XML Tree) then you need to setup your query like this: SELECT Col1, Col2, ...
2
votes
0answers
175 views

SSIS: FTP task - concurrent connections

I have created an SSIS package for downloading file from FTP server. When I debug the package, everything goes fine. But when I shedule the package as Job step, it sometimes fails to connect. The job ...
2
votes
0answers
255 views

Transactional Replication in SQL server 2014

We need to set up transactional replication in SQL server 2014, Enterprise edition so that: Database to be replicated from Publisher which is approximately 2 TB in size. This would be used 24*6. Here ...
2
votes
0answers
46 views

SQL Server 2005 Datawarehouse DB - Integrity failure caused by NonClustered indexes

Last year I've been working with a data warehouse database (SQL Server 2005) loaded on a daily basis during the nights. Overall database performance is acceptable. However, there is a "known error" ...
2
votes
0answers
64 views

Alwayson Database Recovery Failure during TDE encryption

Last week I enabled TDE encryption on my AlwaysOn Availability Group databases of SQL Server 2012. During the encryption of one of the larger databases, bigDB (~200gb) a failover event occurred. Of ...
2
votes
0answers
68 views

speed up ghost cleanup

In SQL Server I have a table that is filled often and also gets deleted often. Because several threads write and erase data from that table I cannot even truncate it because there is always someone ...
2
votes
0answers
37 views

Alwayson cannot fail over using tsql

I have an Alwayson test environment with the following settings. My test goals are as follows: When SqlNod1 is primary instance, I want SqlNod2 to answer read-only queries. When SqlNod2 is primary ...
2
votes
0answers
81 views

Performance Implications of Explicit Collation Conversions across Linked Servers

I locally host host databases from two vendors on two different SQL instances, but on the same network. These servers frequently communicate with each other (through joins and whatnot), so we have ...
2
votes
0answers
19 views

Filegroup design for Document Table for BLOB Storage

I have a table for storing documents as VARBINARY types in my database (I'm not using filestream). My database is setup with only one data file, but I am wondering if there is a benefit to creating a ...
2
votes
0answers
339 views

Multi-Instance Windows Failover Cluster - Min / Max Memory Allocation Dilemma

We run numerous 2 node SQL Server 2008 R2 Failover Clusters in Windows Server 2008 R2 on a VMWare eSXi virtual platform. Each cluster hosts 3-4 SQL instances with varying degrees of memory ...
2
votes
0answers
201 views

Case Statements, subqueries, and datediff

First time posting on this website so please let me know if you require any other information. This first section of code worked perfectly and resulted in correct information. [Field 1] = (CASE ...
2
votes
0answers
2k views

How to resolve RESOURCE_SEMAPHORE and RESOURCE_SEMAPHORE_QUERY_COMPILE wait types

We are trying to figure out root cause of slow running sql server queries hitting/fetching data from one of the database , size 300 GB, hosted on server with below configuration: Windows server ...
2
votes
0answers
591 views

SQLNCLI10.x vs 11.x when connecting SSIS 2008R2 to a SQL Server 2012 database

Starting from the question and working backwards. My question: is it OK to use SQLNCLI10.1 as a provider for SSIS 2008 R2 to connect to SQL Server 2012 database(s)? I have a SSIS box running SQL ...
2
votes
0answers
543 views

How to raise Procedure Cache Hit Ratio?

I have a SQL Server 2012 instance with 96 GB of RAM that has the Buffer Cache Hit Ratio consistently at 100% and the PLE consistently over 500,000, so memory is obviously not a problem. However, my ...
2
votes
0answers
161 views

Merge-Replication: New column doesn't get replicated down by update command

I do this frequently but all clients have this fault: I added a new column to a table using a SQL statement, which causes the column to be created and added to every publication that uses this table. ...
2
votes
0answers
155 views

How to copy SQL Server log shipping files to AWS EC2 instance

I am using SQL Server 2008 R2 and would like to log ship from my local data center SQL Server instance to a log shipping target SQL Server running in AWS EC2. What is the best way to copy the log ...
2
votes
0answers
77 views

Restore Database w Master Key and storing password in plain text

We have a (SQL Agent)job that backs up our production db nightly(and restores to a test db server) and we're adding encryption to some of the columns in our tables. I created a master key and bound ...
2
votes
0answers
59 views

SQL Server 2008 R2 - Should I be worried at this statistics regarding indices causing locks?

We have some mild table locking issue during our peak hours so I figured the following query query taken from this site would be a good start. And boy, does it show me some seriously concerning ...
2
votes
0answers
73 views

Why are foreign keys ignored on subscribers?

We are using merge replication on SQL Server 2008 R2. There are many tables with foreign keys providing a logical dependence on each other. For this question, let's say there are User and File tables, ...
2
votes
0answers
86 views

Why would a default constraint be missing an index?

I have a query that looks at the sys.dm_db_missing_index_group_stats system view to determine what missing indexes there are for maintenance purposes. If I remove the filter from the query that ...
2
votes
0answers
51 views

Had to force loop join and don't understand why

Hello SQL Server Experts. I have a table (table_A) with about 2 million rows and a table_B with about 10 million rows. SELECT b.title FROM table_A A JOIN table_B B ON A.id = B.A_id WHERE ...
2
votes
0answers
122 views

Covering Index - Insert Performance

We are running some performance tuning on our database in SQL Server. The database in itself is pretty small with around 13 tables. But there are multi-level relationships between tables. When I try ...
2
votes
0answers
49 views

How to identify whether a log backup or job executing a SP is causing High Page Reads/sec

For one of my servers( SQL server 2008R2) we can continuously witness High Page Reads/sec during business hours say: from 7 AM to 9 PM EST. I kept the counter settings to monitor the same and found ...
2
votes
0answers
97 views

SQL Server changes in tables reverted after two three days in two different DBs

I'm having a strange issue in one SQL Server 2008 R2 I admin. Three days ago, a major renaming on the columns of a table was made due to a development. After the columns where renamed, the change was ...
2
votes
0answers
181 views

Proxy Account SSIS and NETWORK SERVICE

I currently have an instance of SQL running as NETWORK SERVICE which for various reasons I can't change. I have a maintenance plan running under a proxy account which has access to a network share to ...
2
votes
0answers
178 views

Convert Select statement results into binary and insert into a varbinary column

I am attempting to accomplish the following and 1) not sure if it can be done and 2) if it can I have no idea how! I have tried a couple of approaches but no joy. FYI I am not a full time DBA....just ...
2
votes
0answers
281 views

Excessive CPU Usage caused by long running query?

We have been getting intermittent excess CPU usage at 100%. There is not a whole amount running on the system. We also get warning of Batch Requests/sec above threshold.: 1030, which would seem to ...
2
votes
0answers
83 views

When will SQL Server automatically use a supporting indexed view?

I have the following expensive query that runs against our data warehouse: SELECT d.DateYear, COUNT_BIG(*) AS WidgetCount FROM Widgets AS w JOIN Dates AS d ON d.DateId = w.DateId WHERE ...
2
votes
0answers
256 views

Moving data from MS Access to MS SQL Server

I'm trying to migrate data into SQL Server from an existing legacy set of MS Access databases. Currently everything is working except a few core tables that are wide and contain a number of ...
2
votes
0answers
195 views

Exclude a User From an Audit

I need to set up a SQL Server audit to capture all database activity for all logins except one - the login our application runs under. In this case the login I want to exclude is CPSDOM\cpointedb. In ...
2
votes
0answers
35 views

Replication stopped working, no errors

I have a TOP server (SQL Server 2012) with a merge replication to around 4 other servers. When I subscribe to the publication, everything is downloaded fine to the subscriber. The insert of one of ...
2
votes
0answers
4k views

The underlying provider failed on Open. Error in Windows Service

I have a Windows Service(c#) that receives data and inserts it into DB using Entity Framework 6. Database is SQL Server 2014 Enterprise Edition. Both (Service and DB) are hosted on VPS( Windows Server ...
2
votes
0answers
284 views

Scripting Local Policy Perform Volume Maintenance Tasks

I am working on an automated SQL 2012 install process on Windows 2012 R2 and have everything complete except for one task. My process launches secpol.msc but I am still required to drill in and ...
2
votes
0answers
445 views

AFTER DELETE trigger will not fire during a cascade delete

I've got two tables, corresponding to Nodes in a graph and Aisles to which they may belong. In addition, each Aisle has a Node at which it starts and ends. Aisle Table: Aisle char(4) Start_Node ...
2
votes
0answers
664 views

SQL Server mirroring causing issues

So we have a couple of SQL Server instances involved on mirroring without witness and operating mode is asynchronous in all cases. After we set put mirroring in place, we started facing connections ...
2
votes
0answers
94 views

Deadlock when CODES table has 41900 rows

I'm having a deadlock accessing the same table from two different jobs. I'm testing it on a SQL SERVER 2012 Enterprise edition. Running SQL Profiler I get this graph: You can find the SQL Server ...
2
votes
0answers
71 views

Can there be any case where it would require additional indexes while all columns compose primary key

Ok i don't know whether this question is asked before or not Here my table SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblProductPagesFeatures]( ...
2
votes
0answers
92 views

Service broker routes and DNS

Apologies for the long post but I've recently seen some strange behaviour with Service Broker when the routes it uses reference a DNS entry, and I am wondering if anyone has seen the same? We have ...
2
votes
0answers
430 views

Sql Server 2012 Disk I/O Too high

I am running SQL Server 2012 on windows azure VM Windows server 2012. This is a live Database, about 250GB, with high usage. I applied some best practice articles for the temp-db long ago, and also ...
2
votes
0answers
121 views

Domain name change of SQL servers involved in replication

I have a situation where client wants to change their domain name of servers hosting SQL Servers(e.g. from AB to CD). I have a transactional replication with updatable subscription configured with 1 ...
2
votes
0answers
961 views

DBCC CHECKDB reports torn page. How do I find what is in the torn page?

Using SQL Server 2005 I get the following error when running DBCC CHECKDB SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual ...
2
votes
0answers
210 views

AlwaysOn FCI + HA Setup Question

I am planning to create FCI + HA solution in the same datacenter, but running into some problems. I'd really appreciate if someone can provide some guidance. Here is the current configuration (All ...
2
votes
0answers
43 views

How to cope with SQL Server 2012 vs SQL (Azure) Database sql files

I'm trying to port my system to SQL Database (Azure instance) from SQL Server 2012. I'm using Visual Studio 2013 and I have my .sqlproj with all the definition of my database inside. Given that a ...
2
votes
0answers
13k views

Transaction log full due to log_backup

I am testing how certain operations are logged under different recovery models.Below are steps i have did till now 1.create database in full recovery model 2.take backup 3.create a table and insert ...
2
votes
0answers
195 views

How do I pass a parameter in a stored procedure to call a DTS package

I have created a DTS package which fetches data from a text file into a database table. I want to pass the file name dynamically from which it fetches data. Can I pass the filename dynamically?
2
votes
0answers
255 views

Slow query execution using ODBC 32

When executing a simple select query on a SQL Server 2012 system, the execution time is very different if I use OLE or ODBC 32bit. Unfortunately I am forced to use ODBC 32bit. The Query: SELECT ...