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
1
vote
1answer
16 views

In place upgrade from Express 2005 SP 4 to Express 2014, does database size limitation follow?

We are currently attempting get around a file size limitation issue with our database. The database file size hovers around the 4GB mark (the database size limit is 4GB in SQL Express 2005). We always ...
0
votes
0answers
21 views

create_time in sys.dm_xe_sessions is in the future

I have run the following query which shows the running XE sessions on the server: SELECT name, create_time FROM sys.dm_xe_sessions and I get the results +-------------------------------+...
-1
votes
1answer
58 views

Securing data from unwanted users [closed]

I inherited a DBA position not so long ago, and even having worked with SQL for the past 20 years it is a different ball game. Anyway, here is my pain :-) We need to start hiding sensitive data ...
0
votes
1answer
31 views

Columnstore Index maintenance and finding fragmentation

We are using clustered columnstore indexes and partitions in SQL Server 2014. Some tables have a lot of updates and we find ourselves with a lot of deleted rows for some partitions. In our tests, a ...
0
votes
0answers
15 views

Log Reader Agent is not starting

I have setup transactional replication on SQL SERVER 2014. It was working fine yesterday. Today, I am seeing below error. How to investigate what is the problem. Why log reader agent is not starting ...
4
votes
2answers
748 views

Compatibility level of SQL Server

Is there a way to know if the compatibility level has changed In my SQL SERVER DB 2014 ?
1
vote
2answers
30 views

restoring to new db putting old db into restore

The image below says it all. I need to get some data out of a back-up that's two weeks old, so I restored to a new db called ETLFix. However, when I started the restore my production DB, ETL went ...
2
votes
2answers
73 views

Backup files - Are they safe to delete?

I've got a back up process which initially writes to a local disk, then the disk gets backed up remotely. After each back up (log, diff and full) I'd like to delete all the files on the local disk. ...
0
votes
1answer
15 views

SSRS 2014 Report - looking up single row from dataset

I have a table that I'm trying to report out on in SSRS-2014. A cut down version of the table:- TABLE_NAME FEE_NAME FEE_VALUE PDE 1st year 100.00 PDE 2nd year 120.00 PDE ...
0
votes
1answer
32 views

Why do I need DAC if I am able to connect to the server via sqlcmd?

DAC attracted my attention at the moment when I was unable to connect to the SQL Server using a traditional way (the reason was deleting logon trigger). I made some searches on the web and it turned ...
1
vote
0answers
29 views

Slight change of query causes CONVERT of illegal values to be done first, causing a conversion error [duplicate]

I have a table MyTable which has columns OldVal, NewVal, Nme which are all VARCHAR(MAX) Wherever Nme = MyValue, the OldVal and NewVal values will be "integers" IE a numeric value value stored as a ...
1
vote
0answers
10 views

What is the impact of MAX_DISPATCH_LATENCY while configuring EXTENDED EVENT?

By default, MAX_DISPATCH_LATENCY is set to 30 seconds. I chose FILE as a target and write all captures there. I thought that if I write all captures into file, why would then I lower down ...
1
vote
1answer
46 views

Different Date format in predicate when comparing against a date or datetime

I have the following query: SELECT CAST(MyDateTimeCol AS DATE) AS MyDate, Col1, Col2 FROM MyTable WHERE MyDateTimeCol >= '2019-11-25' AND MyDateTimeCol < '2019-11-26' ...
0
votes
0answers
46 views

Problem with calculating an approximate space required for online index rebuild

I have a database called TestDB which is in simple recovery model. I performed index rebuild on my Test Server which has no other user transactions except me. Before an online index rebuild the sizes ...
1
vote
3answers
99 views

Execute a query but not return results to client

I am testing the performance a query and trying to fix a parameter sniffing issue. Based on the parameter sometimes it returns 3 rows and sometimes it returns over 5K rows. I want to test the impact ...
0
votes
1answer
11 views

Assigned filetype for backups not applying

I am working to migrate backups over to a new storage point. I figured the easiest thing to do was to duplicate the configuration over from the existing Maintenance Plan to a new one, just in case ...
3
votes
3answers
131 views

Only return recordset(s) from Stored Procedure if it has rows

I have a stored procedure that returns multiple recordsets for use in an application. Sometimes some of those recordsets are empty. I'd like to reduce overhead and only return those which have 1 or ...
-2
votes
1answer
46 views

Modification in the SQL script

I want sysadmin logins which are not listed in the exceptionssysadmin table. I'm receiving empty columns with the below script. can someone suggest where am i going wrong ? The below is the script ...
0
votes
1answer
11 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
69 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
1answer
25 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
18 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
27 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
69 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
159 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
28 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
27 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
166 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
28 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
40 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
223 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
672 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
133 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
52 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
49 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
47 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
201 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
67 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
34 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
42 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
79 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
111 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 ...