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

learn more… | top users | synonyms (1)

0
votes
0answers
7 views

Log shipping with active active cluster configuration

Scenario: We are planning to configure active active cluster for our SQL Server. Both will be having separate instances(A instance on node 1, B instance on node 2). 'A' instance will have Production ...
-1
votes
0answers
16 views

try_parse differences from SQL Server 2012 to 2014? [on hold]

My queries which ran correctly in SQL Server 2012 are now failing. I Isolated this down to declare @d datetime2 select @d=TRY_PARSE('3.3.2019' as datetime2 using 'de') select TRY_PARSE(@d as date) ...
1
vote
1answer
14 views

Setting up HA and a synced up reporting box all together using SQL Server 2014 Standard Edition

Scenario: We are going to upgrade our third party application. Also we are going to upgrade our DBMS from SQL Server 2008 R2 to SQL Server 2014 Standard edition. We also wanted to have a high ...
1
vote
1answer
19 views

How to run a job in snapshot isolation?

We have a long running job for report data generation and we are not worried about changes to the source data after the job has started. For this case the SNAPSHOT isolation mode seems to be a good ...
0
votes
1answer
30 views

Can I restore only the LOG of a database?

We have a 1.5TB database. I could create some replication strategies do replicate the database to another server in a fixed time.But the developers don't know when they will need to replicate this ...
0
votes
2answers
31 views

A tail-log backup of the source database will be taken ? Why I am seeing this warning

I took a backup of a database from 1st server (which runs SQL Server 2014). When I try to restore the backup to a 2nd server (which runs SQL Server 2012) I get this warning message: Warning ...
11
votes
1answer
120 views

SQL Server: How to track progress of CREATE INDEX command?

SQL Server 2014, Std Ed I have read that percent_complete in dm_exec_requests does not work for CREATE INDEX, and in practice, percent_complete sticks at 0. So that doesn't help. I currently use the ...
0
votes
1answer
15 views

Catching two error messages/Throw into table

My goal is to catch errors from backup jobs into monitoring table. The problem is, that there are cases that backup statement returns more than one error, so ERROR_MESSAGE() is not enough: Msg ...
1
vote
1answer
65 views

How to find High CPU queries

CPU utilization is very high and the SQL Server process is consuming a lot of CPU. We have run few queries and notice that there are a few SPIDs taking a long time to complete and these queries may be ...
0
votes
0answers
20 views

SQL Server 2014 Standard Max Memory vs. Cluster Shared Volume Cache

Deploying a new two node SQL Server 2014 FCI with Storage Spaces on a local (Cluster Shared Volume) CSV—JBOD will be attached directly to both SQL Server machines and the storage will not be tiered. ...
1
vote
1answer
26 views

Wingdings font in SSMS's column

I do want to create a column which should has check marks and cross marks. Here is the part of my query: iif(order_details.order_status = 'Y',char(252),char(251)) as [completed?] After I execute ...
0
votes
0answers
21 views

How to export settings (Query Execution) with SQL Server Studio Management 2014

Actually, we're migrating from SQL Server 2000 to SQL Server 2014 (approximately 300 servers. With SQL Server Enterprise Manager 2000 and SQL Query Analyzer, we can export some settings (Connections ...
0
votes
0answers
15 views

Software to manage ERD [on hold]

We have a small database (Around 50 tables) under development, and I am being asked to produce an ERD for the development team when ever a change is made. Using Management Studio is tricky, because we ...
0
votes
0answers
14 views

How do I set an SSIS Sql Agent job to unexpected error or failure?

I was running a series of SSIS packages in a SQL Agent job on a virtual server when the server was shutdown - hard. The package execution report shows it as still running even though I believe it is ...
1
vote
2answers
63 views

Update table using values from another table in SQL Server

I have 2 table in my database. Table #1 ------------------------------------------------------------------------- | name | family | phone | email | gender | phone2 | address | birthdate | ...
1
vote
0answers
38 views

Speed Up Garbage collection in FileStream process

In my current process, we have two production site SQL Servers 2014 (Sync), and one DR site SQL Server 2014 (ASync) in one WSFC. I will be hosting 5-6 databases for AOAG set up, out of which one of ...
0
votes
1answer
14 views

Prevent Sql Management Studio to change destination DB on restore

Is there a way to prevent Management Studio 2014 to automatically set the destination database when you try to restore a backup to another database? I just had a nasty surprise when I wanted to ...
0
votes
0answers
25 views

Ola's IndexOptimize fails with log_shipping_primary_databases select permission error

I am using Ola Hallengren's IndexOptimize maintenance solution for online defragmenting a database on a SQL 2014 Enterprise edition server. Many tables get updated through file loading which results ...
5
votes
1answer
54 views

Index creation leads to large transaction log growth

I have an approximately 1 billion row table. I then create an index as follows: CREATE NONCLUSTERED INDEX [IX_Index1] ON [dbo].[MyTable]( [CustomerID] ASC ) -- CustomerID is uniqueidentifier WITH ...
0
votes
0answers
59 views

SQL Server Performance: SELECT…INTO vs INSERT…INTO

I read couple of articles on comparing performance of SELECT...INTO vs INSERT...INTO. Some people even exaggerate that the first one is 10X or 100X faster! :) I tested it by inserting 25 million ...
4
votes
2answers
642 views

Is it possible to rebuild a dropped index?

I'm studying for the 70-462 exam and one of the training resources I'm using gives a scenario where an ETL process is taking a long time to complete an insert into a heap table due to non clustered ...
2
votes
1answer
22 views

Geography data type and Upgrading from 2008 r2 to SQLServer 2014

I am getting a warning about a data type called geography and the warning says Warning about client side usage of GEOMETRY, GEOGRAPHY and HIERARCHYID. Our system has 1 column of data type geography. I ...
0
votes
1answer
25 views

patching SQL Server with Availability Group and Secondary Replica

Scenario: SQL Server 2014 CU6. 1 AG with 2 databases (compatibility level 100). Failover Cluster with 3 nodes on Windows Server 2012 R2. Primary Availability Replica on node 1 and node 2. Secondary ...
0
votes
0answers
38 views

how to find overlapping or duplicated indexes? when is it O.K. to keep them?

this is a script that does the following: create a database called "radha" for testing purposes, create the table ProductItemDetailsDenorm create the clustered primary key create ...
6
votes
3answers
126 views

Does alter column change datatype use tempdb version store?

I was running alter column from INT to BIGINT, my table size is 250 GB and after 1 hour I got the version store error because tempdb was full. As far as I know it only takes your memory pages and log ...
1
vote
1answer
26 views

DR Site avaialability if primary goes down

I have 2 PROD SQL Servers at production site(2 NODE WSFC) and 1 sql server at DR site.If I add DR site node to existing WSFC to configure AOAG. If both the servers at Primary site goes down will WSFC ...
1
vote
1answer
13 views

One one SQL 2014 instance - DBs are created with different collations - ideas why?

we have one SQL 2014 instance, which houses many related databases, and new ones are added via our script regularly. When our app is used to create the database - the databases are created with ...
0
votes
1answer
16 views

File share Witness or Disk Witness

I have 2 SQLServers at production site Windows Server Failover Cluster and 1 SqlServer at DR site. I will add the DR node to existing windows cluster and configure AOAG. So for this should I go for ...
0
votes
0answers
14 views

Optimal Number of Queries Per Batch for SQL Server

I am optimizing an application for a scenario where it will import an sql file and execute over 11 million create commands, which have have some expensive subqueries that are outside my control. This ...
0
votes
2answers
32 views

How to restore backup and keep exisiting data in MSSQL? [closed]

I have test database and test table with some values, for example my table have name and family tables and john,doe as values. I want to restore my old backup to test table,database and keep my ...
2
votes
0answers
17 views

Reverting Compatibility Mode 120 to 100

We recently upgraded the compatibility level of our production databases from 100 to 120. With a couple days users started complaining of increased wait times and general slowness. It was decided to ...
1
vote
1answer
64 views

Repair with data loss did not fix index corruption. What now?

A few months ago we migrated our SQL Server 2014 database to a virtual environment. Soon afterwards, corruption started showing up in indexes, causing all sorts of trouble. I've made repairs using ...
1
vote
0answers
18 views

Database Design for Sports Stats

I am designing tables for tracking statistics for multiple sports like basketball and football. So my question now is - would it make more sense to design a table when both data for basketball and ...
11
votes
2answers
284 views

How to write a query in SQL Server to find nearest values

Let's say that I have the following integer values in a table 32 11 15 123 55 54 23 43 44 44 56 23 OK, the list can go on; it doesn't matter. Now I want to query this table and I want to return a ...
0
votes
0answers
29 views

INSERT Trigger not inserting values

I have a table called tbemissoes. When there is an insert, I need to execute this procedure : create procedure exec pr_replicaRemessa --49 , 2016 @RemessaNumero int, @RemessaAno int AS Begin ...
0
votes
0answers
54 views

SQL Server 2014 client installation [closed]

We are upgrading from SQL Server 2005 to 2014. I am trying to install tools on my client machine to connect to the new test server. I downloaded SSMS Express and installed the management tools and ...
0
votes
1answer
22 views

Log Backup in AlwaysOn

In our current process, log backup is done by log shipping backup job. Is there any need to configure backup job for transaction log in next AlwaysOn environment ? (If we must configure backup, we ...
0
votes
0answers
33 views

SSIS - The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

I am trying to use explisit transactions within SSIS. The database I am working with does not have the DTC enabled, so I cannot use SSIS' inbuilt transaction handling. I have execute sql tasks to ...
0
votes
0answers
18 views

Is it possible to receive and save attachments from emails using Database Mail in SQL 2012 or 2014?

I know you used to be able to do this using the stored procedure 'sp_processmail' using SQL Mail but is there a way to do this now?
1
vote
0answers
16 views

Maintenance Plan job failing with access denied for NT SERVICE\SQLSERVERAGENT

I used the Maintenance Plan Wizard under SQL Server 2014 to create a simple maintenance plan that once a week: Check Database Integrity including indexes Rebuild Indexes Clean Up History When the ...
1
vote
0answers
24 views

Buffer pool extensions disabled for no apparent reason

I've tested Buffer Pool Extensions on an Amazon EC2 virtual machine. The VM has a single non-persistent high-performance SSD. On VM shutdown the SSD contents are lost, so it can't be used to store ...
0
votes
1answer
35 views

In what circumstances I am more likely to benefit from auto update stats async on?

I update statistics on a weekly basis using Ola Hallengren's solution. Based on the article below, I am considering enabling the Auto Stats Async and turning on trace flag 2371. Databases With ...
0
votes
1answer
28 views

How to list the missing multicolumn statistics?

I have used the database engine tuning advisor, DTA, in order to improve the performance of one of my databases. It produced a list of recommendations, mostly multicolumn statistics. These ...
1
vote
1answer
37 views

Does SQL Server 2014 have a feature like Oracle Multitenant databases?

In Oracle 12c, a feature called Multitenant databases is available. This way you can have two seemingly separate databases installed on one system, yet be able to manage Backups and Resource ...
0
votes
1answer
44 views

Multiple databases in production

We have 3 level hierarchy in our business model. Entities at level 3 contains huge data (like dailyfacts tables). In our current DB architecture, we have one single database & all level 1 ...
0
votes
0answers
22 views

Quorum Drive needed?

Do I need a quorum drive for configuring Always On on SQL Server 2014? I am sure it is not mandatory as the 'last man standing' concept is based on node and disk majority. But I need a little more ...
0
votes
1answer
26 views

Having trouble with getting non owner to execute a job

I've attempted to implement solution three of the following link: Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it Table created and sproc created in msdb, inserted rows as ...
1
vote
1answer
71 views

Does Clustered Index on GUID create more fragmentation than Non Clustered Index?

I have a simple table: CREATE TABLE [dbo].[UserTestGroups]( [UserTestGroupId] [int] IDENTITY(1,1) NOT NULL, [Token] [nvarchar](100) NOT NULL, [TestId] [bigint] NOT NULL, [Group] ...
4
votes
1answer
87 views

Subtracting sum of previous rows' data from current row's data in SQL

I want to create a table or view in SSMS which should has a column called qoh. In that column should be calculate sum values of the column rel_qty. i.e. The first data of qoh is the same of the ...
2
votes
2answers
19 views

SQL Server 2014 - Add Node inherits wrong configuration

I'm setting up a simple 2-node failover cluster with SQL 2014 on Server 2012 R2 in Core mode (no GUI). I have the cluster configured with identical machines, and I have the first node configured and ...