SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.

learn more… | top users | synonyms (1)

0
votes
0answers
4 views

In-memory file group and log are growing huge and fast

We have a DB in stress environment: SQL Server 2014 (SP1-CU3) + Always ON The whole db is around 850GB, but in some point something happens and in memory FG is growing to 250GB and a log to 450GB (...
0
votes
0answers
8 views

CPU trends in DB level report by script in sql server 2014

CPU trends in DB level report by script in sql server 2014. Any script for finding the DB level CPU usage report for couple of days. Example: We have 10 DB's in server and need to know about CPU ...
0
votes
1answer
17 views

SSISDB Maintenance job cleaning data very slow on SQL Server 2014

On SQL Server 2014 Microsoft (12.0.2430) SQL Server Enterprise: Core-based Licensing (64-bit) after changing the retention period from 365 to 90 since last 2 days job clean up about 2 GB of data only. ...
0
votes
0answers
21 views

how to find out the next time a job is scheduled to run in sql - in real time and even if the job has never been run before

I have a very similar question here but the problem with that question/ answer is that it does not work for all situations. for example, for the jobs that I have just created and have not been run as ...
3
votes
2answers
27 views

Filtering extended events with configuration table

I've created E.E. session to audit logins, but there are many logins that I need to filter out. I'm wondering, if there is a better option(like creating conf. table with unwanted logins and filter ...
0
votes
0answers
39 views

SSMS 2016 Maintenance Plan Designer Perms

We have some older maintenance plans that were built with SSMS 2014 on a Sql Server 2014 instance. The connection defined for the plan uses a Sql Server account with sysadmin perms. When you edit ...
1
vote
2answers
55 views

By what policy are SQL Server 2014 updates installed automatically?

Tonight our server decided to install SQL Server 2014 CU7 for SP1. The update appears in the Windows Update history and I found downtime in our logs for it. The previous patch level was CU3. I was ...
0
votes
0answers
15 views

OLTP to OLAP process causing deadlocks and impacting customers

I should preface this with the fact that I'm a .net developer who has fallen into DBA and performance work because it has become business critical, so I'm missing the DBA skills for this kind of work. ...
2
votes
1answer
110 views
+50

Why the same execution plan causes LOB reads in some cases?

I have a spatial index and the following query: SELECT DS2.[ID] ,DS2.[InstanceID] ,DS2.[Location].STDistance(@Location) FROM [dbo].[DataSource01] DS1 INNER JOIN [dbo].[DataSource02] DS2 ...
0
votes
0answers
23 views

Increase in IO and CPU cost when explicit conversion is used in a query

I want to check the notable difference between the IO and CPU cost when explicit conversions like Convert or Cast are performed and when not as we need to take a call whether to use or not explicit ...
1
vote
2answers
29 views

Updating dbi_dbccLastKnownGood

I have a VLDB and I offload the DBCC CHECKDB to another server. Is it possible to update the dbi_dbccLastKnownGood on the VLDB server so it passes any scripts that check that date like sp_Blitz?
0
votes
1answer
65 views

Incorrect syntax near the keyword 'WHERE' - SQL Server 2014

The error I get is Incorrect syntax near the keyword 'WHERE'. Script: SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[msp3_account_login] @LOGINID nvarchar(50), ...
1
vote
5answers
86 views

Passing comma separated values to parameters in stored procedure

I have a requirement to retrieve data from a table based on the comma separated values sent in the parameters for stored procedure. As of now I have made the code work for one single value not sure ...
0
votes
0answers
43 views

SQL 2014 - Disable use of capitals in stored procedures [duplicate]

I'm a complete newbie to this but I am hoping the problem we face can be resolved the stored procedures for our database. I am running a game server and we have a problem when people use a password ...
0
votes
0answers
11 views

AlwaysOn DR/BCP Test Plan

I have setup new environment of AlwaysON on SQL Server 2014 for application.Now I have to test the DR/BCP failover.May I know the process to be followed and what all steps do I need to take care while ...
0
votes
3answers
50 views

How can I limit the number of emails sent by SQL Server agent?

I use SQL Server agent notifications a lot for notifying of failed jobs. Usually jobs run every hour so I dont mind getting a few emails if the job fails. However, I want to run a job every minute now ...
0
votes
0answers
48 views

Cannot execute code connecting to SQL Server 2008 R2 in SQL Server 2014 SSMS

I am not able to connect and execute a code in SQL Server 2008 R2 while connected to SQL Server 2014 SSMS. SQL Server 2008 R2 is on Windows 2008 R2 64 Bit SP2 (Stand Alone) SQL Server 2014 is on ...
0
votes
1answer
44 views

Rebuild system databases to a different location

Firstly, forgive my poor question title. I couldn't think of the best way to summarise this. Essentially I'm putting together some scenario based DR documentation, with this specific scenario being ...
12
votes
2answers
248 views

How to track blocking that happen for less than a second - SQL Server

I am trying to troubleshoot a blocking issue which happens for less than a second. The OLTP application is very sensitive and has to have an response time of less than 200ms for some transactions as ...
0
votes
1answer
12 views

Connecting Remote Shares via Powershell

I had a big problem which I just happened to solve, but it's a permissions issue I don't quite understand. Unfortunately I'm in a tight bind and no amount of googling in the past week has really ...
-1
votes
2answers
72 views

How to PIVOT/UNPIVOT if I don't know how many columns will it have?

I have this simple query: I want to list something like this: Unidade1 \ PLaca1 \ Placa2 \ Placa3 Unidade2 \ Placa1 \ Placa2 But I don't know how many placas a unidade will have. I was ...
0
votes
0answers
78 views

SQL query runs fast on local computers but slow on production server

I am running a fairly basic query on my local development machine which runs almost instantly. However on the server its taking around 3-4 seconds to return results. I have ran Client Statistics on ...
0
votes
0answers
22 views

Why does SQL Server Managed Backup create so many full backups?

I have a SQL Server 2014 database with Managed Backup configured to send regular backups to Azure storage. It's been happily churning out log backups for months now that provide full coverage for the ...
0
votes
1answer
23 views

Rebuild Windows cluster for Alwayson.

I am setting up Alwayson,somehow my cluster is gone ( get-clusternode shows down,down,down and Joining). SQL Server is working fine. What Should I do? Create new cluster or build new server? Any ...
0
votes
1answer
30 views

Creating multiple windows login on a single databae

I am able to create a window login on a database I created. This windows authentication login is based on a windows user that I created earlier, but I don't seem to know how to login into SQL server ...
1
vote
1answer
29 views

Can I use SNAPSHOT for large catalogs?

I've been reading about Snapshots, and it says you can send small catalogs lets say, in a monthly routine. I liked the Idea and i will start to study about this, but can I use snapshot for large ...
3
votes
2answers
59 views

Tempdb data files are growing fast in SQL server 2014?

A few days back we have moved to 2014 from 2012 server and we kept the same settings as earlier like we are using 8 data files in Tempdb for better performance and all placed in single drive. As this ...
0
votes
1answer
29 views

Full scans/sec metric seems very high, dropped after killing 2 queries

Our app today (java spring web application) suffered a major slump and Investigating the SQL Server metrics I found what seems to me a very high full scans/sec value. I killed the 2 SPIDs that where ...
0
votes
1answer
56 views

Using an in-memory table with a regular stored procedure

I am working on converting one of the tables in my database to an in-memory table in SQL Server Enterprise 2014. Let's call the table in question table A. When I convert table A from a regular table ...
0
votes
1answer
52 views

AG (SQLServer 2014) - Suspend Data Movement

I have a primary replica and one secondary. Multiple availability databases. Secondary replica is located at DR site. For DR testing, is it possible to failover to the secondary replica, make (test) ...
3
votes
1answer
82 views

how to find out what process(es) caused the autogrowth of a database or a database file?

I have a process(es) hungry for tempdb, but I am struggling to identify this process. any ways I could achieve this? We have been alerted as Tempdb has now grown into the space you reclaimed ...
0
votes
0answers
19 views

Is VSS Backup a better solution than the native backup?

Since i started to work as a DBA, i'm making normal backups ( with scripts, ad hoc and etc ). What would be the better solution for a backup? lets say I need to migrate a 2tb database to development ...
0
votes
1answer
27 views

Change the Hosts files on both SQL nodes to show 127.0.0.1 LocalHost

How to do this ? Change the Hosts files on both SQL nodes to show 127.0.0.1 LocalHost
0
votes
1answer
41 views

“CREATE FILE encountered operating system error 32” when attempting to attach database

I am having trouble connecting to a database I created in SSMS. Keep in mind this is my first time working with databases. All I've done in this database is create some tables and add a few records. ...
0
votes
0answers
25 views

MAXDOP Recommendations - Is my understanding correct? [duplicate]

I have a VM SQL 2014 Server with the following CPU setup. If I follow MS recommendations for MAXDOP settings (https://support.microsoft.com/en-us/kb/2806535) I believe that MAXDOP should be 2 as the ...
0
votes
2answers
29 views

SQL 14 AG go into read only when cluster is down, how?

Looking at SQL 14 AG and trying to get this to work https://blogs.technet.microsoft.com/dataplatforminsider/2013/06/27/alwayson-in-sql-server-2014-ctp1/ Increased availability of readable ...
0
votes
0answers
11 views

Cluster shared volumes storage configuration

When setting up and configuring Cluster Shared Volumes (CSV) for a SQL Server 2014 build out, is it recommended to create separate csv volumes for tempDB, SystemDB's, UserDb's, and UserLog's?
0
votes
1answer
34 views

Is there a way remove SQL Server listener IP on maintenance

Is there a any way to stop listening listener on the SQL Server on production server. I want to be sure nobody is trying to connect my production server when it is maintenance mode. I disabled all ...
0
votes
1answer
36 views

Microsoft ODBC Driver 11 for SQL Server: Named Pipes Provider: Could not open a connection to SQL Server

I am aware this question has been asked before. I have been following the extensive troubleshooting steps at MSQL Tips as well as every answer I can find on Google. I am really stumped. The error is ...
0
votes
1answer
44 views

Cannot attach My Database without log file

I deleted my whole database by mistake and all my accounting data is gone. I recovered the .mdf file via FileScavenger. Now when want to attach it without .ldf file it gives this error: Could not ...
0
votes
1answer
27 views

How can I insert this union all selects inside my procedure?

I'm having problems with logic, to think how can I put this code inside a procedure, as a field.Tried with CTE but failed too. SELECT r.cod_rep,r.cod_perito_primeiro, tbSolicitacoes....
0
votes
0answers
17 views

How to use a proxy with SQL Server Management Studio

I am doing some work with IP white listing and penetration testing against a SQL Server connection. I have found that that SQL Server Management Studio is a great client side tool and I am wondering ...
0
votes
0answers
26 views

Using a Window Function to Replace a Subquery

I am having an issue with a query that I am trying to change to take advantage of the Windows Functions - mainly as a learning tool. The working query is below: SELECT A.county_name AS [CountyName] ...
0
votes
0answers
21 views

SQL Server 2014 Dev Edition installation permissions issues

I am a guinea pig developer working with the IT team to devlop procedures for our teams to begin working with SQL Server 2014 Developer edition. We are having some permissons issues doing side-by-side ...
0
votes
3answers
39 views

Allow remote access only from certain IP addresses

If I've understood what I've read, all you need to do in order to allow remote access to SQL Server is to open TCP port 1433 in Windows firewall. But that will leave your server wide open to the ...
0
votes
0answers
97 views

Cannot Update a System Collection Set

I'm trying to tweak the "Server Activity" Data Collection set as outlined in this blog post (I wrote the post, btw. Sorry--it's kinda long). It is working in SQL 2008 R2 and also in SQL 2014. However,...
0
votes
2answers
34 views

AVG with OVER - invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [closed]

I have what I believe to be a correctly written sql query that calculates the average using an over clause to window the results. However, I'm getting an error when I include the over clause. SELECT ...
0
votes
1answer
30 views

Can't login to sql server custom db

I have wamp installed in my system and sql server 2014, and configure so i can access sql server db through php.. with this code: <?php $serverName = "MY-PC\sqlexpress"; $connectionInfo = array(...
3
votes
0answers
41 views

How much disk space do I need to add a primary key to a very large table?

I'm using Microsoft SQL Server 2014 - 12.0.4100.1 Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) I have a 491 GB table with 3.6 billion rows that does not have a primary key. The ...
4
votes
1answer
347 views

Why does my execution plan (sometimes) include a left join?

I found a similar question and am aware that in order for a query of the form SELECT COUNT(1) FROM foo f LEFT OUTER JOIN bar b ON f.Value = b.Value AND f.Value = b.Value2 to perform without ...