Questions tagged [sql-server-2012]

SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.

621 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8
votes
1answer
268 views

Do I need a “downtime” to update a stored procedure? (Due to “definition changed” error)

I always thought that updating a stored procedure was something I could do while users were actively using the system. But I am currently testing out an update to a stored procedure and while a long ...
6
votes
0answers
382 views

CHANGE_TRACKING_CURRENT_VERSION() incorrect on database in standby

We are using a change tracking solution to export increments for BI purposes for years now. But it was running directly on the production database. To lower the load on the system we wanted it to run ...
5
votes
0answers
143 views

How do I identify the source of the 17886 errors (“server will drop the connection”) in my SQL errorlog?

I have a SQL 2012 server that is throwing the following error every 5 minutes or so: Error: 17886, Severity: 20, State: 1. The server will drop the connection, because the client driver has ...
5
votes
2answers
442 views

Deleting data from a table containing LOBs did not reduce the amount of data reported INTERNALLY by the table or database

Running SQL Server 2012 standard, using both replication data and log shipping. Transaction log backups are created every few minutes. This question is NOT about the amount of space reported by the ...
5
votes
3answers
105 views

Optimizing table/indices for getting the latest row (filtering by one additional column)

I have a SQL Server table (SQL Server 2012 SP3 Standard edition) that stores a bunch of configuration information (basically text blobs) for different organizations. The schema is something like this: ...
4
votes
0answers
281 views

Adding more tempdb data files gives more write latency

I am facing problems with tempdb please help and provide your suggestions. Initially, I have found tempdb contention problem in my server (Sql server) by using Simple talk Script. By using that ...
4
votes
1answer
2k views

“Corrupted key” error when creating/updating SQL Server 2012 credential for proxy account

Following a restart of a SQL Server 2012 SP3 instance caused by a reboot of the unresponsive host WINTEL server, I have a SQL Agent Log Shipping Copy job (which uses a proxy account to access the NAS ...
4
votes
0answers
702 views

BCP neither gives results nor outputs anything when using valid statements but it does throw errors when passing invalid parameters

I have to use bcp command-line tool to export data from an SQL Server database to a file in a Red Hat server. I am (apparently) using valid statements but bcp is not producing any kind of output/...
4
votes
0answers
208 views

Service broker internal spid hogging tempdb

We've recently decommissioned a rather badly implemented service broker setup after a number of near outages, however while doing my morning checks I've noticed that there are 2 Service Broker ...
4
votes
1answer
111 views

SQL Server Replication

We had an issue with replication, where a large (7.5 million row) update was done on a 33 million row table. Replication converted that to 7.5 million individual update statements. When my alerting ...
4
votes
0answers
463 views

SQL Server 2012 duplicate key error with Spatial index that shouldn't be unique

We have a table that has a geometry column that is filled by a stored procedure from two x and y values whenever they're changed ( SW_GEOMETRY = GEOMETRY::STGeomFromText('POINT('+ CONVERT(VARCHAR(50)...
4
votes
0answers
396 views

MEMORYCLERK_SQLOPTIMIZER in SQL 2012 Memory usage extremely high and causes server to crash

I have been having issues with our SQL server for a while now. It seems to run out of memory every few days and when I look at the memory dump, the MEMORYCLERK_SQLOPTIMIZER seem to take over memory ...
4
votes
0answers
1k views

Upgrading SQL Server from 2008 to 2012 doesn't respect existing install / instance paths

Basic Information : Running Microsoft Windows Server 2008 with MS SQL Server 2008 (version 10.0.5869), trying to upgrade to SQL Server 2012. Currently working in a cloned VM of our production server (...
4
votes
0answers
3k views

MS SQL Server 2012 Failover Cluster connection issue

I was about to give up with this but I'll give it a go here if anyone can help. I've set up a fail-over cluster SQL Server with 2 SQL nodes, Domain controller and file witness. All seems to work fine,...
4
votes
0answers
566 views

SQL Server 2012 Error when creating Selective XML Index on XML Query Plan

I'm trying to create a database solution that will enable me to optimise high CPU/IO/Duration queries to improve overall performance of the database. Part of the optimisation requires persisting ...
4
votes
0answers
4k views

SQL Server intermittent error 40 Could not open a connection to SQL Server or The network path was not found

I am getting intermittent errors as per the below stack trace. I am using C# Entity Framework with SQL Server 2012 enterprise running on a separate server computer. I can connect and query the DB ...
4
votes
1answer
878 views

Can we use FILESTREAM in case of SQL Server cluster

I saw this point on Technet article about RBS. The local FILESTREAM provider is supported only when it is used on local hard disk drives or an attached Internet Small Computer System Interface (...
3
votes
0answers
52 views

SQL Server database partitioning purge job failed with an error

We have a SQL Server Agent job that executes package to purge partition file and filegroup older than 25 month. The job was running fine however it failed this month with following error: The ...
3
votes
0answers
291 views

PAGEIOLATCH_EX Wait on Target table of insert..select query

We are running SQL Server 2012 on Windows 2008 R2 with 256GB RAM and 90% memory allocated to SQL Server. No other Apps run on the server. I have a scenario where I’ve to copy a Source (staging) table ...
3
votes
0answers
54 views

ASCII only connection to SQL Server

Let's imagine I have a legacy application that I would like to connect to a modern SQL Server. This legacy application only understands ASCII, but the SQL Server contains Unicode data in nvarchar ...
3
votes
1answer
166 views

Optimize query without rewriting it - vendor app

Here is a query that uses a lot of CPU on my SQL Server (8 cores): DELETE TOP(1) rd FROM (SELECT TOP 99.9999 PERCENT * FROM APM_IisBb_Request_Details ORDER BY RequestDate ASC) rd JOIN ...
3
votes
0answers
294 views

Msg 22830 level 16 on sp_cdc_enable_db_internal - could not update metadata that indicates database is enabled for Change Data Capture

I'm attempting to create an MCVE for this recent question. SQL Server is returning an error when attempting to update the system tables in master to indicate CDC is enabled for my test database. ...
3
votes
2answers
2k views

Full text search using words stored in another table

Is there a way to do a full-text search using words stored in another table? This is my scenario: Table_A contains phrases entered by users in field "UserInput" (and other stuff). I have a full-...
3
votes
1answer
58 views

Ownership of SQL Server Files, Register Entries & Executables

I'm working on a database server hardening project for a client of mine. I'm getting into ownership issues when it comes to the following: .mdf & .ldf executables HKEY--> tree from ...
3
votes
0answers
90 views

Allow Remote Connection to only ONE database SQL Server 2012

I would like to have 2 db (Dev & Prod). CarsDevDB & CarsDB. I have 2 users, CarsDevUser (Allow Remote Access, Port 5000) and CarsAdmin (only allow local connection). I would like to connect ...
3
votes
1answer
3k views

SQL Server 2012 hangs at SqlRsConfigAction_install_ConfigNonRC_Cpu64

For some reason SQL Server 2012 keeps hanging at SqlRsConfigAction_install_ConfigNonRC_Cpu64 There isn't any Windows installer running apart from the SQL Server setup. Update on Resolution Install ...
3
votes
0answers
276 views

Error: “Computer X is Joined to a cluster” when creating a new cluster

I am trying to create a New Cluster for Always On Availability Groups. On Failover Cluster Manager when I try to add the cluster nodes I get the following error: Computer X is joined to a cluster. ...
3
votes
0answers
3k views

Optimize long LEFT OUTER JOIN query

I have query which has 40+ left outer joins. Query is not that slow but I'm interested that can I optimize this (procedure is most used one in db). I had idea that "a" table has type column which ...
3
votes
1answer
272 views

SQL Server High Availability and Sharepoint - log file grows when no database activity

Just started as a dba with a company running SQL Server 2012 High Availability and SharePoint. Short description: Database with no user activity still has growing log file. Long description: They ...
3
votes
0answers
511 views

Move SQL Server cluster from one location to other

I am looking for ideas on how to move SQL Server 2012(standard) two node cluster installed on Windows server 2012 standard to other location which is far away from current location. The two locations ...
3
votes
0answers
110 views

SQL Server 2012 certificate encrypt connection

I'm not a DBA by trade. My organization doesn't have a dedicated DBA, we have to just figure things out. I have a SQL Server 2012 Enterprise installed on a Sever 2012 R2 OS. It is a not in a SQL ...
3
votes
0answers
125 views

Multiple transaction logs and preferred order of use

Here's my situation: I have a SQL Server 2012 installation with a relatively small (185GB) SSD volume, and a large (3TB) RAID10 spinning disk volume. I currently have tempdb data and log files on ...
3
votes
0answers
113 views

Unable To Log In Through Windows Authentication

See this question for details of troubleshooting I've been stepping through (I did answer 2, which didn't work - it was a corrupt master file). In configuration manager, I changed to a Built-in ...
3
votes
0answers
808 views

how to minimize the effect of updating statistics on the plan cache?

The issue I am trying to address: After updating statistics there are slow downs in running many of our processes here. then following the scary dba on this link below: http://www.sqlservercentral....
3
votes
0answers
2k views

SSMS Client Error: Internal connection fatal error

I am running SSMS 11.0.5646.0 on a Windows 7.0 Pro desktop connected by UTP directly to same network switch as my development server (Windows 2008R2 Standard, SQL 2008R2 SE with SP3). All within the ...
3
votes
0answers
215 views

PAGEIOLATCH_SH and WRITELOG waits

We have a slow overnight process which I was asked to look at with the top waits being PAGEIOLATCH_SH and WRITELOG I have been collecting information from sys.dm_io_virtual_file_stats and can see ...
3
votes
0answers
463 views

DBCC TempDB Space Usage

We are using DBCC CHECKDB on a VLDB 1TB in size. Previously when on our 2008 platform the check ran successfully on a drive that was 150GB in size for TempDB, but since moving to SQL 2012 and ...
3
votes
0answers
75 views

Compress backup taken from two different environments take different time to restore

I have two backup files one from Normal SQL Server and one from SQL Server from Azure VM. The restore of these backup files is totally different from each other. Though the database backups are ...
3
votes
1answer
45 views

SQL Server 2012 Explicit transactions taking time with Always on Setup

We have a weird issue while we setup the AlwaysOn on Azure VM. We have explicit transactions in our stored procedures, and which are causing some delays and blocking when till the time we get the ...
3
votes
1answer
474 views

Domain User lockout after SQL Cluster Failover

We have this one user who uses SSMS to connect to a 3 node 2 instance fail over cluster. He also uses SSIS, Excel, SharePoint, SSAS etc.. to connect to this database instance I'll call TEST. There are ...
3
votes
0answers
94 views

Performance differs on same query against two (almost) identical tables?

I'm running these two queries in SQL Server 2012: SELECT tpua.Field1, CAST(round(twg.Field2 * 2, 0) / 2 As Decimal(8,1)) As Field2, CAST((round((twg.Field3/10.0) * 2, 0) / 2) * 10 As int) As ...
3
votes
0answers
385 views

Index Optimization Error in SQL Server 2012

I have an Index optimization job set on the database SWHSJ having two data files with unrestricted growth in the J drive with 174GB free, as you can see below: ╔═══════════╦════════╦══════════════════...
3
votes
0answers
205 views

SQL Server 2012 Installation Has No Option To Uninstall

I've recently inherited a laptop for testing from a client, unfortunately it wasn't a fresh image so it has some software already installed that's causing me some grief. The short version is their IT ...
3
votes
0answers
2k views

SQL Replication With Always On Availability Group

We have an always-on SQL 2012 availability group with two replica servers and an AG listener. Doing merge replication via anonymous subscriptions through IIS between SQL and multiple remote clients ...
3
votes
0answers
439 views

Prevent SQL Server failback after cluster aware updating

We have SQL Server 2012 in a two-node cluster with Windows Server 2012 (node and disk majority). Neither of the nodes are marked as preferred owner and the "Prevent Failback" option is selected. ...
3
votes
0answers
387 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 ...
3
votes
1answer
2k views

User defined table type with a check constraint

I have a user defined table type where I want to make sure that a certain column is within a given range. This range however is defined in another table. So I thought I can use a check constraint on ...
3
votes
0answers
2k views

SQL Maintenance Plan 'Could not find part of the path' - where is it getting the path from?

I have a SQL Server 2012 maintenance plan which backs up all of the databases. Today I had an issue with a failed backup which I was able to resolve by viewing the detail in the 'Log File Viewer' ...
3
votes
0answers
3k views

Once more about SQL Server virtual accounts

Let me reboot my question on SQL Server virtual accounts permissions The (same) MSDN article on the same version 2012 of SQL Server "Configure Windows Service Accounts and Permissions" states: in ...
3
votes
0answers
274 views

SQL Server 2012 Resource Governor and memory used by CLR

In SQL 2012 memory used by CLR is now part of what is controlled by "MAX Server Memory" a) Does Resource Governor in SQL 2012 Enterprise (MAX_MEMORY_PERCENT for resource pool) governs CLR memory? b)...