Questions tagged [sql-server-2014]
SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
2,653
questions
0
votes
1answer
30 views
Is there a way to tell if a GUID column contains sequential or traditional / random GUID's?
Have a table that is populated by Entity Framework 3.1.1 code. The table contains a clustered index / primary key on ID which is defined as of type GUID with no default value. Is there a way in SQL ...
0
votes
2answers
28 views
How to query cached plans that contain a specific missing index?
I see a potential missing index for a table but would like to first query all cached plans that have that specific missing index (so I can later test those queries after the new index is built). How ...
0
votes
1answer
26 views
How can I measure the impact of queries with errors being rejected by SQL Server?
After configuring XE on November 23, 2020 to capture some errors I could count 2,163,665 occurrences of the same error coming from requests of a legacy app (that probably won’t be corrected so soon). ...
0
votes
1answer
39 views
Identify table usage in sql server before we stop the load
I tried browsing this across lot of links but cant get much of help so i am posting this question which sound bit similar.
We have quite some tables which are being planned from our app team that they ...
0
votes
1answer
36 views
Find if and when a Query was run?
I am trying to get to the root of a problem and need to see if and when a specific Delete Query was run on my MS SQL Server.
The command would have come from my .NET website.
My Server is Windows 2012 ...
2
votes
1answer
104 views
Allow user to GRANT certain permissions to others
I have tried googling this but may well be using the wrong terms... permissions and the like aren't my strong point.
What I want to do is first create a role - eg 'Snr_Analyst' - with certain ...
2
votes
1answer
36 views
Why do SQL Server auto-created statistics sometimes include the primary key?
I had always understood that SQL Server auto-created statistics only include one column. In other words, they are not multi-column statistics.
However, today I came across some auto-created ...
0
votes
1answer
33 views
Running select filename from dbo.sysfiles returns short foldernames with tilde
I am running the following statement in SQL Server 2014(in Windows 2012R2):
select filename from dbo.sysfiles where fileid = 1
However it returns:
c:\progra~1\micros~1\mssql1~1.sql\mssql\data\master~...
0
votes
1answer
52 views
Why is the built-in distance function so much slower than my custom one?
I'd like to find the distance between 2 sets of coordinates. First I use the built in function:
declare @lat1 real = 33.1, @lon1 real =-117.1, @lat1 real = 39.6, @lon1 real =-98.7
declare @source ...
1
vote
1answer
126 views
Configure Connection String to Use Target Machine Credentials
I am trying to develop an SSIS package that will be run on several different machines. I am stuck because I do not understand how to create a connection string that doesn't explicitly point to one ...
0
votes
1answer
15 views
Create LINKED SERVER to MYSQL using SSH
Is there any way to create a MS SQL Server Linked Server to connect to MySQL through SSH?
I managed to connect to MySQL through SSH using other database management tools like HeidiSQL or DBeaver, ...
0
votes
0answers
31 views
Is logon trigger supported in sql server 2014 express edition? [duplicate]
I'm using sql server express for developing a multiuser app. I have the following request to implement:
Access to sql server shall be allowed only for the c# app and a specific whitelist of logins ...
0
votes
1answer
47 views
How to avoid implicit conversion when using XQuery value() method in TSQL?
I have the following xml that I am parsing using TSQL - XQuery value method.
<Root>
<FooAudit>
<BarIntField>..</BarIntField>
<BarDateTimeField>..</...
2
votes
2answers
40 views
How to allow a certain domain user to create databases on a remote SQL instance?
I want to allow for a certain domain user to be able to create databases on a remote SQL instance.
What steps are necessary and is it possible to perform those steps programmatically in a C# ...
0
votes
1answer
32 views
Do DB permissions flow from primary server to secondary server in SQL log shipping?
Do DB permissions flow from primary server to secondary server in SQL log shipping?
If someone wants db read permissions on the secondary, do we grant on primary and the permission flows to the ...
2
votes
1answer
62 views
Longer time to bring SQL online after node failover
We had a strange issue post our regular OS patching for SQL servers.
Per best practices, we apply patch on Passive and do node failovers to make current passive, active and vice versa to complete ...
1
vote
3answers
66 views
Handle development issues when dealing with different collation for the instance/database setting
Looking for some guidance on how we can handle the scripts/schemas when being run on different collation in sql server.
We have a process where multiple stored procs are created lets say with schema ...
0
votes
1answer
25 views
How to optimize and estimate DB recovery times?
Would like to optimize recovery time for db's after failover to passive node in high availability cluster.
Assuming full recovery model...
What are the steps to take to optimize DB recovery time for a ...
1
vote
2answers
117 views
Help with tuning this UPDATE Query or possible re-write
I have below SQL query which runs on avg. 40 secs and need some guidance where can i improve it or possibly re-write:
Declare @status_D INT,
Declare @StatusIP INT,
Declare @Status_F INT,...
0
votes
3answers
60 views
Allow user to create databases on server and to see only the databases that are created by him, without creating login first
I want to implement the following use case using C# application and MS SQL Server Express 2014. I have a centralized database server in which there are already created some database (which have the ...
2
votes
1answer
53 views
SQL Server 2014 only using 10 percent of its available memory? [duplicate]
Hoping someone can weigh in here. I have a 2014 developer edition box that doesn't seem to be performing well. I currently have 128 GBs of RAM allocated to the box and 120 GBs maximum allocated inside ...
0
votes
1answer
46 views
Index automation job script
Our Production instance is running SQL Server 2014. There needs to be an automated Index defragmentation job that will either reorganize or rebuild the indexes based on the fragmentation level for a ...
2
votes
1answer
214 views
Drop the last character of string, if the last character is s
Working through an odd issue that the moment and trying to find a solution. If we have a string variable (@string) how might we drop the last character of the string, ONLY if the last character is an ...
3
votes
0answers
90 views
Time to generate execution plan for select into from a view takes significantly more time than select
I have a view, vw_example, that is quite complicated and has multiple joins across multiple databases. This view has been causing significant delays when used as part of other queries, and we've ...
4
votes
3answers
305 views
General Question on sp_recompile
For couple of scenarios, not many a times but few we have seen doing sp_recompile on a stored proc improving the performance.
Being a DBA i understand few of caveats involved when doing sp_recompile ...
0
votes
2answers
29 views
UDF in MS SQL Server returning a query starting with “with” cause
This is a perfectly valid SQL Query and works nicely (for e.g.) in Microsoft SQL Server 2014:
with t as (select 'a' as attr) select * from t
it returns a, surely one can simplify it and drop the with ...
0
votes
1answer
37 views
When should you shrink your transaction log?
We do log backups every 3 minutes but despite that the transaction log has still managed to grow to 200+ gb on disk. Its almost completely empty after log backup. How do you know if your transaction ...
1
vote
1answer
27 views
Steps to apply windows updates to sql failover cluster?
What are the steps to apply windows updates to a active/passive two node sql failover cluster? My initial thought was to apply windows updates to passive node/reboot and then failover and repeat ...
1
vote
1answer
39 views
Will large transaction log file slow db recovery?
Did a manual failover after windows updates to passive node today and recovery was fast for all but one db. The one slow db had a transaction log size of 203200.00 mb. Log Space percent used = 0.25. ...
0
votes
1answer
24 views
Part of statement missing from sys.dm_exec_query_stats
I'm trying to debug the individual parts of a large query; I'm using the statement offsets in sys.dm_exec_query_stats to separate them. There is one particularly troublesome statement that for some ...
0
votes
1answer
39 views
Shrink data file corruption risks?
Are there any risks of data file corruption resulting from shrinking sql data files?
1
vote
1answer
23 views
Pointing the database secondary data file towards a network drive
I have a 2 TB database and the Data Drive has filled up. My network team cannot expand the drive nor provide another drive so that the secondary data file can be pointed towards that. My question is ...
2
votes
1answer
202 views
What does exchangevent indicate in SQL deadlock graph?
Have a deadlock XDL file with several exchange events listed. Not sure if these are good or bad or what causes them. What causes exchange events in SQL deadlock graph?
<exchangeEvent id="...
3
votes
1answer
52 views
deadlock with repeatable read isolation level
Trying to understand deadlock that is occurring with repeatable read isolation level when the stored proc is invoked.
This is the deadlock xml :
<TextData>
<deadlock-list>
<...
0
votes
1answer
47 views
Understanding the load running on my SQL server
We have a project this year to move lot of physical SQL Servers, starting with medium critical servers onto Virtual machines.
Generally on physical boxes we have a large count of logical processors, ...
1
vote
2answers
56 views
How to fix error recording sp_whoisactive results to table?
Have a sql agent job modeled from here that uses sp_whoisactive to capture results to a table. 99% it works fine but every now and then the sql agent job will fail with this error:
Warning: Null value ...
0
votes
1answer
32 views
Query for returning query plans from sql plan cache that contain 1 or more warnings? [closed]
Is there a query I can run to return query plans from sql plan cache that contain 1 or more warnings?
0
votes
2answers
114 views
Finding reason for transaction log 'tempdb' full due to 'ACTIVE_TRANSACTION' [duplicate]
Our application stopped working suddenly due to The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.. Now, it start working again. But how can I know the exact reason for ...
3
votes
2answers
81 views
How to identify the top disk space consuming tables by file?
Is there a query I can run to see the top disk space consuming tables for a given file in a file group?
Need to free up some disk space for a given SAN drive and need to identify which tables are ...
3
votes
2answers
165 views
Remote Scan when updating using functions
Why does using ISNULL() when updating through a linked server use Remote Scan to retrieve all rows and filter locally instead of filtering through Remote Query?
UPDATE LINKEDSERVER1.database1.dbo....
1
vote
1answer
40 views
How to investigate memory grant warnings?
Saw this warning from query in plan cache
<Warnings>
<MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="2751552" GrantedMemory="2751552"...
4
votes
1answer
279 views
How to dispose of large tables with the least impact to log shipping?
Have two semi large related tables (284,6876,609 rows - 156,785,176 KB and 4,2628,162 rows - 2,212,464 KB) in a log shipped db that needs to be either purged or dropped. How can I dispose of these ...
2
votes
2answers
39 views
How do I merge and delete duplicated rows in SQL using UPDATE for SQL server 2014?
For example, I have a table of:
id | code | name | type | deviceType
---+------+------+------+-----------
1 | 23 | xyz | 0 | web
2 | 23 | xyz | 0 | mobile
3 | 24 | xyzc | 0 | web
4 ...
0
votes
1answer
37 views
Log shipping retention question
One of the secondary's is failing log shipping restore due to insufficient drive space. This might not be looked at by IT for several days. Can log shipping primary server purge transaction log ...
1
vote
2answers
45 views
SQL SERVER Detailed Backup Report
I need to create a script that gives the report for the following information:
Backup Job Name
Databases that have been backed up in the last 24 hours
Size of each DB backup
Start time of backup
End ...
0
votes
2answers
59 views
What Is The Best Way To Clone A Database From Production To Development Environment? [closed]
We use SQL Server at our company as DBMS. I have a scheduled job working monthly. The job restores every databases on production server to development server at the end of the month but there is ...
0
votes
2answers
53 views
What causes DBCC CHECKTABLE to return “session is in the kill state”?
A scheduled job step ran DBCC CHECKTABLE on a table but returned the following error:
Cannot continue the execution because the session is in the kill state. [SQLSTATE HY000] (Error 596). The step ...
0
votes
1answer
39 views
Convert to 2 decimals and keep leading zero [closed]
The following piece of code is resulting in a value with 4 decimals.
COALESCE (SUM( CASE WHEN PLL_TRANSACTIONTYPE = 'TAX' and PLL_REVENUETYPE = 'OTHER' then CAST(ROUND(PLL_AMOUNT,2) as decimal (9,2))
...
0
votes
1answer
24 views
Tools for looking at transactions inside of transaction log backup file? [closed]
Are there any tools (commercial or open source) for looking at the contents of a transaction log backup file? Occasionally, get a large transaction log backup file and would like to see the contents /...
1
vote
1answer
18 views
SQL Server - Does adding a subscriber to an existing publication generate a new snapshot or create locks on the publisher DB?
I need to add a new subscriber with snapshot and transactional replication and I'm trying to determine if I need to add the subscriber after-hours or not.
This particular database is already being ...
