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

Merge replication sync fails after ALTER TABLE… NOT NULL

We have a SQL Server 2005 database that uses merge replication to sync to SQL Compact Edition subscriptions. We added a column to a table using: ALTER TABLE Shifts ADD COLUMN StateOrProvince NOT NULL ...
2
votes
0answers
29 views

Can SSMS Object Explorer Details configurations be exported?

A number of employees make use of SQL Server Management Studio (SSMS). I have a configuration file for all new users for Environment (etc) configurations. I want to deliver a uniform view for each ...
2
votes
0answers
43 views

Clustered index on a fact table

I'm trying to work out what field(s) to use for the clustered index of my fact tables (which all have a similar structure). I'll use FactSales as an example: CREATE TABLE dbo.FactSales ( ...
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 ...
2
votes
0answers
30 views

SAN Admin vs. DB Admin free allocated tempdb

I really want to understand. My SAN-Admin told me I have to make sure my TempDB does not grow to max hard disk space. I have 380 Gig on a dedicated disk, on a dedicated LUN, on a dedicated machine. My ...
2
votes
0answers
39 views

Does HOBT shared lock prevent writes

I am attempting to execute long running reads against a table which is also the target of insert bulk queries from sqlbulkcopy. In order to achieve this I have added the with(nolock) to the query ...
2
votes
0answers
38 views

ERD to SQL Server

I am working on an assignment and am having one heck of a time getting my head wrapped around these questions. I need to be able to write in SQL Server 2014 certain select statements based solely off ...
2
votes
0answers
44 views

DBCC failure due to sparse files on Windows Cluster

We have a SQL Server 2014 SP1 cluster on Windows Server 2012 R2, and came across a problem with DBCC jobs failing and issuing the following messages: Msg 1823, Level 16, State 2, Line 3 A ...
2
votes
0answers
44 views

where to add a column with low cardinality, decimal type into an index?

I got a question regarding the best way to put a bad column for an index in an index, so that I can satisfy my query below, and similar others that follow the same pattern. on the query below, all ...
2
votes
0answers
40 views

Classification of Subqueries?

Just trying to wrap my head around the use of subqueries. Is it correct to say that they come in three main categories: Scalar: returns a single value Table-valued: Returns a table, to compare or ...
2
votes
0answers
35 views

how to get column name from cross reference query

I used a script from here: http://stackoverflow.com/questions/13757387/getting-sql-server-cross-database-dependencies that Milica Medic posted and it works GREAT thank you :) my question is how do I ...
2
votes
0answers
44 views

security concerns over enabling data access for the current server

Included in the code of the creation of some stored procedures, I have the enabling of data access as you can see below. BEGIN TRY PRINT @@SERVERNAME PRINT DB_NAME() PRINT 'enable the ...
2
votes
0answers
34 views

AlwaysOn Multi Subnet Configuration

Environment Two Nodes A & B A - Site/Subnet 1 B - Site/Subnet 2 File Share - at Site 3 We have an AG listener that has two IP addresses spanning the two subnets (1 up, the other down ...
2
votes
0answers
51 views

TSQL Generic Row number theoretical question

To start, We have a pretty complicated system that's been a compilation of over 20 years of code... And the code needs to work with SQL2005 all the way to SQL2014 (can't use the offset Fetch Next) ...
2
votes
0answers
58 views

What is the correct way to rename/swap a table in production

I have two tables, tblCode and tblCodeAlternative tblCode has about 4100 rows and is used in production. tblCodeAlternative has about 4200 rows and is in the production database but not used ...
2
votes
0answers
45 views

Cross database - Table valued Stored Procedure

I have the same table type in two databases (DB1 and DB2), DB1 has an stored proc (sp1) which has the above type as an input parameter. Now, I want invoke this SP from DB2. But, I am getting this ...
2
votes
0answers
58 views

SQL Server Check Constraint Lock Order

I'm having problems with SQL Server 2012 filetable. Filetable defines an check constraint on a path_locator field with definition as: /*ParentsAreDirectories*/ ...
2
votes
0answers
55 views

Unable to use select top N shortcut on Azure SQL Database via SSMS

Yesterday morning I was able to query my Azure databases via SSMS and the Select Top N menu shortcut. That afternoon I received a couple of alerts that my web applications couldn't connect to the ...
2
votes
0answers
45 views

Max Degree of Parallelism for SQL Server 2008

Microsoft have recommendations around setting MAXDOP on various versions of SQL Server, but this (old) article raises some issues and recommends setting MAXDOP = 1 generally for SQL Server 2005 and ...
2
votes
0answers
13 views

Installing SQL Server 2012 Standard on a machine with more than 64GB RAM

I’m in the process of doing the specification for a new server that will host SQL Server 2012 including SSIS, SSRS and SSAS I’m aware SQL Server 2012 Standard has a limit of 64GB. However from what ...
2
votes
0answers
34 views

Is there impact on query execution as more partitions are added to a table?

We are looking to heavily use partitioned tables in our data warehouse environment. We are looking to take advantage of fast partition switching as well as partition elimination during analytical ...
2
votes
0answers
45 views

SQL Server Trace Flag 834 on VMware

It's recomended to enable trace flag 834 to use large-page on buffer pool for SQL Server running on VMware? The tuning options kb of Microsoft (https://support.microsoft.com/en-us/kb/920093) don't ...
2
votes
0answers
145 views

SQL Server 2012 Installation Error on Windows 2008 Server

I am facing an error while trying to install SQL Server 2012 EXPRESS ADVANCED EDITION(32bit) on Windows Server 2008 Enterprise(64bit). After passing Rule Check, at install updates section, error ...
2
votes
0answers
52 views

Shrinking a physical transaction log file on a mirrored database

I have 2 SQL Server 2008 Standard Edition instances that are mirrored. Both instances are running Service Pack 1. I run Full backups daily and transaction log backups every half hour (through ...
2
votes
0answers
71 views

DB snapshot goes into SUSPECT mode (SQL 2014 on WIN2K8R2)

I was hoping someone here might have encountered this sort of issue and if so what did you do to fix it. Here's some details: DB server: SQL 2014 Enterprise (12.0.4100.1) OS: Windows 2008R2 ...
2
votes
0answers
31 views

SQL Server - Debugging - How to show parametervalues as HEX

I am writing a script which sets Parameters in the Registry for SQL Server. The following works on some of my servers but not all. DECLARE @Parameters AS VARCHAR(MAX) SET @Parameters = '-T1118' ...
2
votes
0answers
53 views

How to avoid Network issues for SQL Server 2012 AlwaysOn

I have SQL Server 2012 and have setup AlwaysOn for High Availability. The infrastructure is on Azure VM's. After moving to this setup, we are frequently observing Network issues which either causes ...
2
votes
0answers
67 views

Rebuild index online without risk to other requests

We have a MS sql server 2012 enterprise database whose indices fragment very fast. We use a maintenance plan that reorganizes those indices daily, and rebuilds them weekly. The rebuild happens ...
2
votes
0answers
61 views

CPU utilization reached 100% after changing OS time on SQL Server with Mirroring configured for a database

We have configured mirroring on SQL database server. Suddenly principal database server reached 100% CPU, windows log events shows following 2 logs This event is generated when the system time ...
2
votes
0answers
64 views

How to setup SQL Server 2014 replication for a demo site

Background: We have been looking at replication of our SQL Server 2014 databases for a couple of scenarios.One which just works and its hunky dory and the other which just doesn't seem to work, ...
2
votes
0answers
59 views

SQL Server 2014 Log shipping - column error

I'm trying to set up log shipping between two instances of SQL Server 2014 Dev edition. Backup and copy jobs work flawless, but the restore one keep failing with the following error: An error ...
2
votes
0answers
103 views

Database corruption extends beyond nonclustered index issue in SQL Server

Background I have a server running SQL Server 2008 R2 on the matching MS OS. Over the last few months this server had a RAID drive failure on two separate occasions (which simply required hotswapping ...
2
votes
0answers
107 views

CDC capture job failing due to trigger execution error apparently

I'm using Change Data Capture(CDC) to track DML changes that are being performed on my SQL Server Tables. However, the capture job is failing post a DDL alteration, with the following error: Msg ...
2
votes
0answers
92 views

SQL Server archiving

We are trying to archive our data for the first time in our database. The database is around 250 GB. Our approach is as follows. Create a copy of those tables without any index or constraints. And ...
2
votes
0answers
35 views

My stored procedure randomly fails due to invalid object in a swapping database instance

I have a stored procedure in my database (say it is CustomerDB) which joins with a table function in a different database (say it is dbPerformance), the sp is something like: select something from ...
2
votes
0answers
49 views

Simple insert with Wait_Info = WRITELOG()

We have a query ( the same as some questions I made here ). a simple insert: INSERT INTO [My_Ttable]([IDChamada],[Evento], [Resposta],[IDTarifador],[ProximoEvento],[NomeDaMaquina],[DataMaquina]) ...
2
votes
0answers
63 views

session id null in sys.dm_os_tasks

We are seeing parallelism causing worker thread shortage in SQL,while investigating we found session id is null for some sessions,Below is the query used.What does this session id null means ? select ...
2
votes
0answers
91 views

Log file Zeroing

During the creation of the database, I am getting below mentioned information about log file zeroing and I have highlighted my understanding in bold fonts. 2015-08-23 12:42:36.850 spid51 ...
2
votes
0answers
154 views

How do I provide a failsafe for retrieval of data from excel to SQL

So say there is a process in place for importing data from excel spreadsheet to SQL using SSIS. And there are two tables, one with new data and one with data from its previous run. I sometimes have ...
2
votes
0answers
639 views

SQL Server 2014 Avaliability Group DbMgrPartnerCommitPolicy

I have an environment with 2 Windows Server 2012 R2 servers, both with SQL Server 2014 Expensive edition. Whenever a change happens in the AGs (such as: AlwaysOn Availability Groups connection with ...
2
votes
0answers
134 views

SQL Server Always On Failover Cluster Design

I have two databases. One I'd like to set up in an availability group between two servers (two instances, one on each server). The database is not huge and I'm not expecting it to grow ...
2
votes
0answers
22 views

How to ignore spatial index?

When i do Schema Compare in Visual Studio i see that spatial index in table was changed and this changes will included in generated script. But i do not want see this changes in my script. It's ...
2
votes
0answers
96 views

Any way to ensure that Change Data Capture clean up process does not delete the change data that you haven't processed yet?

I'm using CDC feature to track changes that are being made, since the cleanup period is set to 3 days by default, I have to run jobs to archive this change data from the CDC system tables to my ...
2
votes
0answers
864 views

How to resolve COM server application local activation permission for machine account

I am trying to enable SSIS package logging to event logs. When an attempt is made to write to the event log the following error appears in the system event logs: The application-specific ...
2
votes
0answers
65 views

Mirroring won't fail back

Got an interesting mirroring issue. I am using SQL Server 2008 R2. Server A mirrors 8 databases to Server B ideally. Right now the 8 databases are principal on Server B (otherwise healthy). When I ...
2
votes
0answers
56 views

SQL 2014 Availability Group Cluster or Listener

We recently deployed a setup of SQL 2014 availability groups and we are having some difficulty with the failover. Setup: Two windows server 2012R2 VMs in a windows cluster with a CNAME pointed at ...
2
votes
0answers
76 views

Techniques for moving database from Enterprise to Standard Editiion

I have a SQL Server 2012 Enterprise edition and a Model database. On a virtual machine I have installed SQL Server 2012 Standard Edition and want to create a copy of the Model database on it. I have ...
2
votes
0answers
84 views

Moving from MS Access to SQL Server 2012, need help with column history

I'm moving an access database from all the data being in MS Access to MS Access being just a frontend, but I'm having trouble figuring out how to do a sort of column history. I have a ...
2
votes
0answers
51 views

Column Store Indices versus Dimensional Tables

I have a Data Warehouse and want to think ahead one year when I may get a database update allowing me to use column store indices. My dimensional tables are mostly junk dimensions containing columns ...
2
votes
0answers
41 views

BULK INSERT clent and session requirements

I'm setting up an ETL process using SQL Server 2014 and SSIS. My Bulk Insert Task is utilizing a source file and format file on ShareA targeting a DB on InstanceA. The package is run via SSA job on ...