Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [sql-server-2014]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
8 views

Upgrading Microsoft SQL 2014 and Always On AG Configurations

I'm getting ready to perform an upgrade from SQL 2014 to 2017 on two servers that are configured for Always On. The concern on the upgrade is how to break Always On to stop replication and to keep any ...
1
vote
2answers
66 views

Find jobs that succeeded, but had steps that failed

Is it possible to find jobs that succeeded, but that had steps that failed like the desired output shows? I'm new to this side of development and I'm not sure about all of the system tables, etc. If ...
1
vote
0answers
18 views

Find all tables in a snapshot replication that have primary keys

I've been tasked with moving all tables in a single snapshot replication that have primary keys to the transaction replication. I've tried to break it down into two steps, finding all tables in a ...
0
votes
0answers
21 views

How does DBCC CHECKDB use tempdb?

I would like to know the working principle of DBCC regarding the tempdb. I want to know why it uses tempdb for what purpose. I didn't find any advanced article about that so maybe someone can shortly ...
0
votes
0answers
17 views

what is the difference between replication options proc exec and definition only?

I understand proc exec option as for example when you run a procedure which inserts 1000 records into table, those insert afterwards will be replicated to the subscriber. When it comes to definition ...
0
votes
0answers
21 views

Is subscription database considered as read-only?

I am not sure about that because while users implement SELECT from subscription database, publisher replicates data to the subscription server meaning that INSERT and UPDATE also take place (which are ...
-1
votes
1answer
25 views

Sql Server 2014 - Rows to Columns convert Dynamically- [duplicate]

Below are my query and table details create table #ReviewData ( ProjectId int, ReviewId int, ReviewDataPointKey varchar(max), ReviewDataPointValue varchar(max)) Insert into #ReviewData values (...
-1
votes
1answer
67 views

SQL Server Rows to Columns Dynamically

Below are my table and query details. Table definition create table #ReviewData ( ProjectId int, ReviewId int, ReviewDataPointKey varchar(max), ReviewDataPointValue varchar(max) ...
4
votes
1answer
138 views

How to remove SQL Server Error Logs

I have six error logs plus the current. They are taking up 10G of disk space on my hard drive on my VPS server. Thats about 25% of the server space. The largest file is 6G and growing. How can I ...
1
vote
0answers
32 views

Linked server in Sql Server 2014 invalid path

I am trying to create a linked server to foxpro tables using vfpoledb, but it gives me the error of "Invalid path or file name" when trying to put as source \\xxx.xxx.x.x\same_names\abc (xxx.xxx.x.x ...
0
votes
1answer
27 views

Unable to Insert Casted XML Data into New Column in Temporary Table

Question How can I insert CAST(event_data as XML) data into a newly added column in a temporary table with the following statement: INSERT INTO #Unique_Logins (event_data_xml) SELECT CAST(...
1
vote
1answer
25 views

Cannot take backup into NAS storage at specific time

I have a job which runs every 30 minute and takes t-log backup of the database suing Ola's script. Every Thursday and Friday at 22:45 I get an error: 11/07/2019 22:45:00,Backup,Unknown,Error: 3041<...
0
votes
1answer
18 views

How to convert a VARCHAR DD/MM/YYYY H:MM:SS AM into datetime and then to Json date?

So I know this question is crazy. I just spent days trying to solve this but i think it's impossible. I have a table with a CreatedDate as a VARCHAR field. 10/7/2019 7:12:59 AM What I need to to ...
4
votes
1answer
142 views

Can SET IDENTITY_INSERT be allowed with less privileges than db_ddladmin?

I've inherited a system where the application is running under sysadmin account. I limited this account to db_datareader + db_datawriter + EXECUTE on all database and set extended events session to ...
0
votes
0answers
11 views

convert a varchar datetime value into a Jaon (mongodb) date string

So I have a column that we export from MongoDB and it has a field with dates like this: 2018-07-02T10:30:09.614UTC-03:00 We've created an app that import this data from MongoDB to SQL Server. For ...
0
votes
0answers
26 views

Linked Server using Impersonation fails SSMS GUI Connection test but sp_testlinkedserver succeeds

I have a linked server (Server B) set up on Server A with permissions set as follows: This means that there are five Windows Logins (of which my windows account is one) who can access the linked ...
0
votes
1answer
37 views

Upgrading to SQL Server 2014 SP3 _ Clarifications help needed [closed]

We have SQL Server 2014 SP2 GDR with below: Availability Groups TDE enabled for structured data EFS enabled for unstructured data Now the DBs are with AOAGs and encrypted , I am planning to ...
5
votes
3answers
219 views

How is calculated fill-factor percentage related to size of database?

I have rebuilt all indexes in my database setting fill-factor of 95 (5% free space) using a maintenance plan. After the reindex the database is almost doubled in size - reported free space is 42%. ...
8
votes
2answers
662 views

Why is it not recommended to use RAID 5 for a log file? [duplicate]

While reading SQL Server Query Performance Tuning written by Grant Fritchey, I found it difficult to understand the following part: avoid RAID 5 for t-logs because, for every write request, RAID&...
1
vote
0answers
95 views

Is it possible to reset SA password without stopping SQL Server Service?

I lost a password for sa account. Now I want to reset SA password and at the same time not to restart SQL Server Service or going into single-user mode. I would like to mention that I am windows ...
1
vote
0answers
43 views

Why is failed job not logged in SQL Server Agent Error Log?

I have a failed job which is logged in SQL Server Logs but not logged in SQL Server Agent Error Logs. The job is executed as user: NT SERVICE\SQLSERVERAGENT. Limit size of job history and Remove agent ...
1
vote
0answers
46 views

SQL Prepared statement reuse in plan cache

I'm working on minimising our plan cache usage, and I've read a lot around Adhoc queries and how we can parameterize and reuse rather than having many single use plans. I'm not seeing as much ...
2
votes
1answer
46 views

Automatic seek on an index on a calculated column

I have a table with a varchar field than I need to be unique, but it is too long for the nonclustered index that the uniqueness constraint requires, so I have added a computed column (either using ...
4
votes
1answer
198 views

Why the job's next_run_time is wrong?

I got an issue regarding job stats. So I have a job with the following schedule: Occurs every day every 5 minute(s) between 12:00:00 AM and 11:59:59 PM. Schedule will be used starting on 12/13/2017. ...
2
votes
2answers
58 views

Permissions using a TYPE as a Table Valued Parameter

I'm developing a .Net App that calls an stored procedure that have a table valued parameter declared as a TYPE READONLY. First time I tried to call the SP I received the next error: Msg 229, Level ...
1
vote
2answers
31 views

Different Maximum server memory across Availability Replicas

I have a scenario in SQL Server 2014 AlwaysOn High Availability where other services are running in one of the Secondary read-only replicas. These services obviously demand additional memory. Is ...
0
votes
0answers
11 views

How do I troubleshoot FILESTREAM_WORKITEM_QUEUE waits in SQL Server 2014?

Env: SQL Server 2014 SP2 on Windows Server 2012 R2. I am seeing our top wait as FILESTREAM_WORKITEM_QUEUE, with average waits near 1s and over 5000 waits during a 2-hour period of time. I haven't ...
0
votes
1answer
23 views

Disaster Recovery through cloning drives in sql server 2014 enterprise

I am a DBA noob and recently my work has considered to have a disaster recovery plan for their production DB lets say A (sql server 2014 enterprise). The production server A and Disaster recovery ...
1
vote
0answers
41 views

Tempdb. There is no enough space on the disc encountered

Does anyone know what really happened here? All of the sudden database was not accessible. How to find transactions that cause tempdb filled up? Seems like it happened right after SQL profiler ...
1
vote
1answer
28 views

SQL Server Agent - Report Failure but continue When intermediate step fails

I have a SQL Server Agent job that has three steps with the following control flow: Step 1 - on success - Go to next Step. on fail - job fails Step 2 - on success - Go to next Step. on fail - Go to ...
1
vote
1answer
69 views

I would like to understand better the difference between “sql server stand alone” and “sql server failover instance”

AS these question asks: Are "Always On Failover Cluster Instances" and "SQL Server Failover Clustering" the same thing? Shouldn't Windows Server Failover Clustering already ...
1
vote
2answers
110 views

Querying system health data becomes slow as event volume increases

We get the data from system health XE from SQL server and load into table for later querying. Part of that statements which gets pretty slow is INSERT INTO TABLE1 ( [object_name] ...
0
votes
1answer
20 views

Querying sys.server_principals does not return results for specific user

I'm trying to figure out why this query: SELECT * FROM sys.server_principals WHERE sid = SUSER_SID('Domain\User') OR name = 'Domain\User' Does not return results for one specific user? At the same ...
-2
votes
2answers
86 views

generate views from meta data using STRING_AGG function [closed]

I want to generate the DDL for views from a table of meta data. Since every value for my_column_name maps to a single short_column_names the order is important. If they are both sorted by their ...
0
votes
0answers
25 views

When does setting the SORT_IN_TEMPDB option actually improve performance? [duplicate]

My question is related to index rebuilding, mainly the SORT_IN_TEMPDB option. BOL states that: Even if other users are using the database and accessing separate disk addresses, the overall ...
6
votes
2answers
147 views

Why selecting rowversion using outer join never returns null

Using SQL Server 2014. Given table with rowversion column, other table to join with and a select like this: CREATE TABLE dbo.FooTable( [Id] [int] IDENTITY(1,1) NOT NULL, [RowVersion] [...
0
votes
1answer
77 views

SQL server agent unable to establish connection

I have recently installed sql server 2014 enterprise version on windows 2016 server. For some reason sql server agent is not starting but sql server service is up and running. I am not really sure ...
0
votes
2answers
46 views

How to find Sum of dynamically generated time attribute in sql server 2012

I have a table with two columns (TASKID and Dateadded). I am trying to find difference between timings and would like to calculate the sum of the diff (time) attribute. select taskid,dateAdded,CAST((...
1
vote
0answers
37 views

Changing clustered key on a big table

I have a big fact table in a datawarehouse (40 Gb worth of data in dev, 120gb in production ) which was created in a very classic way CREATE TABLE BigFactTable ( BigFactTableId bigint IDENTITY ...
6
votes
2answers
332 views

Managing a SQL Server database with over one terabyte of data [closed]

Recently we have an audit database that went over one terabyte and, since we have storage problems, management is looking for options. My proposal is at the end of each year we take a backup and ...
11
votes
4answers
241 views

Shouldn't Operator cost at least be as large as I/O or CPU cost that comprises it?

I have a query on one server which the optimizer estimates will have cost of 0.01. In reality it ends up running very badly. it ends up performing a clustered index scan Note: You can find the ...
1
vote
2answers
40 views

Select All Records From Table That Do Not Exist In Secondary Table [duplicate]

This should be straight forward, but I am getting my tail kicked with the proper syntax. I have this syntax Select DISTINCT bm.Location ,bm.[Beer (Upcoming Menu)] FROM ...
0
votes
0answers
23 views

Adding login of a pc on the network - Microsoft SQL Server Management Studio 2014

I have a small office network and I am trying to add new logins for the other PCs on Microsoft SQL Server Management Studio 2014 (all computers are Windows 7). I cannot seem to find the other ...
1
vote
1answer
33 views

Two FOREIGN KEY constraints with SET NULL targeting the same PRIMARY table

I feel like I'm missing something obvious here... I have a situation with a primary table (let's call it People) and a secondary table (let's call it Groups) with two different roles that people ...
0
votes
3answers
51 views

How to free used space from disabled index?

I've come across a need to free used space from a large number of disabled indexes. Is it possible to make SQL Server release the used space from these indexes without a drop/create for each index? ...
0
votes
0answers
11 views

Indexing with transactional replication snapshot

Is there a way to set up a database table structure and indexes on a replication subscriber prior to running the snapshot agent in order to skip having to add the indexes AFTER the snapshot has been ...
0
votes
0answers
43 views

Comparing Performance impact between 2 Scalar UDF's

I have my developer team who uses the data from Change data capture from SQL Server to extract data and load to a different database system. To convert the LSN format within CDC they are using the ...
0
votes
1answer
24 views

Grant User permission to view XE results via sys.fn_xe_file_target_read_file

I need to grant a user (MyDomain\JohnSmith) permission to view one particular XE session results using sys.fn_xe_file_target_read_file I thought the best way to do this would be to encapsulate the ...
0
votes
1answer
25 views

Centralized Database for user information

Our current setup is that we have what I would call a centralized database that holds user information that is shared by several different databases. We have the several databases to separate out data ...
0
votes
1answer
48 views

ANSI NULL OFF By Default in database option

I have a weird behaviour here when creating database in sql server. The default ANSI values is set to false when creating database. ALTER DATABASE [NewDB] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE ...