Questions tagged [sql-server-2014]
SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
2,469
questions
0
votes
0answers
4 views
One or more subscriptions have been marked inactive. Drop and re-create all subscriptions for this node that are failing with this error
We have a production server error causing replication agent got failed and subscription became inactive. When I tried to restart synchronizing process, it is getting failed.
Should I delete/drop the ...
0
votes
0answers
18 views
Migration of domain controller in a clustered environment
Recently we have switched our domain controller and during the migration I have noticed on the cluster manager the below error
Cluster network name resource 'Cluster Name' encountered an error ...
0
votes
0answers
24 views
Trying to fill an MS Sql table with dummy data to fill up a FILE in a FILEGROUP
I have a PRIMARY FILEGROUP that contains 2x files.
I'm trying to fill up my a FILE in my PRIMARY FILEGROUP to test that when the 1st FILE is filled, it will then start filling the 2nd FILE in the PFG....
0
votes
1answer
35 views
SQL Server 2014 SP3 DB emailer TLS 1.2 support
I have SQL Server 2014 (SP3-CU4) 12.0.6329.1 running on Windows Server 2012R2 with .NET 3.5.30729.4926 installed
I can't get Database emailer to work with TLS 1.2
I tried everything in the article "...
1
vote
1answer
45 views
How to reinstall SSISDB
I have a SQL Server 2014 Enterprise Edition where the SSISDB catalog is already here, but corrupted because of a rogue package that made it balloon to enormous size and a failed cleanup attempt.
Is ...
-1
votes
2answers
72 views
T-SQL Query performance
We are using SSIS package to execute the following statement.
SQL statement
delete from ABC
OUTPUT DELETED.[ABC_Id], 5
into XYZ (OId, OCode)
where ([ABC_Id] = ?...
1
vote
2answers
29 views
With MS Sql Server + Multiple files in a file group -> when does it start placing data on the 2nd file in the filegroup?
In a previous question, it was suggested that I should add an extra "File" to my existing (Primary) Filegroup to easily add more disk space for my DB.
So I did that and nothing broke.
But I'm not ...
1
vote
1answer
109 views
Funky cardinality estimate skews, high memory grants and missing statistics
I have a select statement within a procedure that is causing me some grief at the moment. The query estimates about 100 million rows and that in turn grants large chunks of memory (about 8 gigs per ...
0
votes
1answer
22 views
How To Grant A User In AD Groups Which Is Denied To DML Commads, To Update On A Spesific Tables
I have a Active Directory user account named SQLQRY in my sql server. SQLQRY is permitted only read data. Those users can use onyl select statement. I need to give just update and delete permission on ...
1
vote
1answer
25 views
script insert SQL Server 2005 from SQL Server 2014 Express
I have a database in SQL Server 2014 Express, and I want to import it to SQL Server 2005.
I have generated the scripts to generate the database and to insert the data. All the script generated to be ...
0
votes
0answers
47 views
Inline function inside CASE statement
I have a scalar function which was getting used inside several queries. Scalar function was called inside WHERE and INSIDE CASE statement. This created huge IO and CPU performance issue due to the ...
0
votes
1answer
47 views
SQL - Return most recent batch of sequential rows
Version:
Microsoft SQL Server 2014 (SP3-CU4) (KB4500181)
What i'm trying to accomplish here is pull in all the rows where there is a break of less than lets say 40 days between the next row.
So in ...
0
votes
0answers
11 views
Changing stored procedure in Transactional replication in order to avoid restoring from snapshot
As the title suggest, I have a transactional replication set up on a database of about 4 TB in size. This is an old replication that was set up a couple of years ago and what I can see is that the ...
0
votes
1answer
33 views
Can I use LetsEncrypt to secure SQL Server?
I am running SQL Server 2014, and use LetsEncrypt to encrypt some websites on the server. I want to remain PCI compliant, which requires an encrypted connection to the database, if accessing from ...
0
votes
0answers
71 views
String or binary data would be truncated, with an nvarchar(max)
I'm facing a well known error, but this time I cannot figure out the problem.
I'm working with SQL 2014 and I need to replace some values inside an NVARCHAR(MAX) column. To do this I retrieve the ...
3
votes
2answers
81 views
Regarding Sql server 2014 express edition
I need to know if adding another data file in a database (let say MYDB) in express edition will have another 10 GB size or 10 GB will be the total size of all the data files we add in a database. ...
3
votes
1answer
124 views
Running sys.dm_db_index_physical_stats in parallel
I am trying to get fragmentation information on a few tables which are over 500 GB and I am using the ...DETAILED option of sys.dm_db_index_physical_stats. I am doing this on a restore copy of the ...
0
votes
1answer
33 views
How to Grant A User Under A Sql Domain Account To Insert and Update Records
I have a sql domain account in sql server like DOMAIN\GroupName. There are many user accounts under that account like DOMAIN\user.name. I want to give insert and update permission only to a user under ...
1
vote
2answers
97 views
How to restart MS SQL server right, what is best practice to do that?
I`m quite new to DB administration.
I have a problem when I restart SQL server, one of the databases goes into Recovery mode and automatically repairs the DB in 30 min. I don't know why this happens....
2
votes
2answers
98 views
MS SQL running out of Disk Space - can FileGroups be used to free up some space?
Problem
We have a Sql Server 2014 with a 1TB attached disk (on Azure) that's running out of disk space. We have about 20GB's left (maybe a few weeks of space). As such, we need to move some data off ...
4
votes
1answer
63 views
Why should I apply a CU for SP2 that was released after the release of SP3?
Checking SQL Server 2014 build versions to identify the right update for my server I got a little confused as what update to use. Did some research and found out I should Apply the latest Service Pack ...
0
votes
1answer
45 views
How do cardinality estimates affect CPU and Reads in SQL Server?
Trying to understand a concept, not sure where to look for answers. I have been researching cardinality estimates and their affects on memory. In the process of optimizing a query to get better ...
2
votes
1answer
47 views
Finding stored procedures with XACT ABORT ON OR OFF
I am trying to troubleshoot blocking problems related to active open transaction with sleeping status and probably have found issues related to XACT ABORT option missing or as OFF read from many blog ...
0
votes
1answer
22 views
Copy all agent jobs to separate files based on date and keep five copies
Looking for a crazy script to run after database backup ...
Want to look at all agent jobs and if modified date is equal to today save it to a shared drive, if not skip. Also want to delete the ...
3
votes
1answer
61 views
Is Memory Grant size 'cached' for subsequent executions of a cached plan?
I hope the title of the question is clear. I am a DB developer as one of my job roles. I recently asked a question regarding index statistics and I have spent a lot of time reading about statistics, ...
0
votes
1answer
74 views
SQL Server take an unencrypted full backup
I have to take an unencrypted full backup of a database so that it can be sent over to other team after which they can restore it in their own environment. The other team has a different environment ...
3
votes
1answer
125 views
Exactly same query and plan but different duration and total reads. I Know parameter sniffing but
I know what is parameter sniffing but this seems different, I have this simple query:
select * from A400RDATA
This table has only a cluster index and approximately 25000 rows. The query usually ...
0
votes
3answers
107 views
How did you split strings before string_split()
I have some strings I need to split on a SQL Server 2014 instance.
I would normally do it something like this:
declare @bk nvarchar(1000)
set @bk = 'SIS5.0~person_id~696969'
select value from ...
2
votes
1answer
80 views
Querying a SQL Server's CNAME alias instead of the actual instance name
We don't have a DBA here and our infrastructure analyst is trying his best to give us a test instance on a server using a CNAME alias which neither he nor I have done before.
The actual database ...
0
votes
2answers
66 views
Deny a Sql Server user from a specific hostname
Is it possible to block a sql server user not to execute DDL and DML operations from a specific hostname (client)?
I have a CRM web application. That app connects to my database with a sql server ...
2
votes
0answers
41 views
sys.database_files DMV, max_size column
I was just modifying my script that I use to get size of database file and wanted to use max_size to display if a max size was set for a database file. As per Microsoft Docs, the column description ...
0
votes
1answer
78 views
AutoIncrement non-primary key or Make unique composite key?
I have a db with a table that has a rollover value such as OrderNumber and another column is year.
This is the primary key - a composite key - but there are other tables with which I need to use a ...
3
votes
3answers
163 views
Why does the concatenation order change with a User-Defined Table Type?
I've encountered what seems to me to be a strange ordering issue when using CONCAT with a User Defined Table Type.
I've put the SQL below that allows me to reproduce this:
I have the following user ...
0
votes
1answer
51 views
Index / Check DB Script question
I'm using Ola Hallengren's maintenance scripts and I wanted to see if I could create 2 tables so that it logs integrity checks and index optimizations to different tables.
0
votes
1answer
64 views
Where to put single quotes?
I am trying to execute the below statement which creates a database called service2019:
DECLARE @dbname VARCHAR(50);
SET @dbname = 'service' + CAST(YEAR(GETDATE()) AS VARCHAR(4));
EXEC (
'CREATE ...
1
vote
1answer
19 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
25 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
+-------------------------------+...
0
votes
1answer
247 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
16 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 ...
5
votes
2answers
790 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
35 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
102 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
34 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
30 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
18 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
53 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
47 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
132 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
12 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 ...
