Tagged Questions
All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.
1
vote
0answers
5 views
Multiple Filters for Databases on SQL Server Server Level Audit
I am attempting to create a Server Level Audit to track Database Level events in SQL Server 2014. I know I can create them at the database level but I want the audits all databases at the server ...
0
votes
0answers
23 views
Unable to remove the database name
One of the databases is getting to suspect mode. Since I have backup, I restored the same database with different name and then take the SQL SERVER and remove the data and the log file of the ...
1
vote
1answer
33 views
Trace flag 2861 and what a 'zero-cost' plan actually means
I'm trying to get my head around trace flag 2861 and what it actually does with trivial queries?
The blurb says:
SQL Server typically won't cache plans for these trivial queries because the cost ...
3
votes
1answer
42 views
What are the “GDR” vs “QFE” hotfixes for SQL 2008 R2 SP3?
I'd always assumed that higher patch numbers were necessarily later releases, but now I'm not so sure.
Looking at the SQL Server Build List for SQL 2008 R2 SP3, there are two possible ways to apply ...
2
votes
1answer
26 views
Count instances of value in multiple tables
I'm attempting to write a query that returns the count of a given value across multiple tables.
Right now, I'm using the following query
SELECT COUNT(*) AS SL,
(
SELECT COUNT(*)
FROM [Sales Line ...
0
votes
1answer
28 views
Querying a date range
i query this SQL query:
SELECT * FROM [DB].[dbo].[Table]
WHERE [DATE] BETWEEN '01-01-2016' AND '31-03-2016'
AND ([TIME] >= '00:00:00' OR [DATE] > '01-01-2016')
AND ([TIME] <= '00:00:00' OR ...
2
votes
1answer
15 views
PowerShell Pipeline Binding examples on SQL server?
I am learning PowerShell, my training machine (VM) is a Windows Server 2012 R2, with SQL 2014 and PS 4.0.
I am try get a handle on the two Pipeline Binding methods ByValue & ByPropertyName. I am ...
0
votes
0answers
11 views
How to set up repl_distributor for data access
I need to run queries on the distributor server from the publisher during deployment in a transactional replication setup. I see repl_distributor is set up as a linked server, but am having trouble ...
1
vote
1answer
59 views
Scheduling index rebuild jobs
I have a MS-SQL 2014 SP1 - two node AlwaysOn AG (One Primary and an read-only secondary) with about 10 databases sizing 20-30GB in total. I have some queries regarding scheduling index rebuild and ...
0
votes
2answers
31 views
Update Grouped records
I have the following query that returns duplicate values:
SELECT
[t].[Field1], [t].[Field2], COUNT([t].[Field2])
FROM [dbo].[Table1] AS [t]
WHERE [t].[Field2] <> ''
GROUP BY ...
1
vote
1answer
22 views
Login in Alwayon Availability
I am new to always on availability groups. I am little bit confused with creation of logins. Let assume an environment NODE1(Primary) & NODE2(secondary) & NODE3(Stand by) & ...
1
vote
1answer
67 views
Troubleshooting extremely long running query (SQL Server 2012)
We have a purchased reporting application that executes a query that just recently is taking an extremely long time (>2 hours). A week ago, this same query completed in less than a minute. I've ...
0
votes
1answer
27 views
Accessing computed column in a nested query
I am using nested sets to represent tree-type date in SQL-Server and basing what I'm doing on this article:
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ by Mike Hillyer.
In ...
-2
votes
0answers
18 views
Get the output of a stored procedure into a table [on hold]
Can anyone help me with a table that takes the output from a stored procedure. It should not be a temp table or table valued parameter. I am able to insert values to the table, but when I use select ...
0
votes
1answer
26 views
Database Log 3 times Data File [duplicate]
I have a set of database on full recovery model, my backup schedule looks like this:
1 Weekly Full Backup
1 Daily Log Backup
Full backups are about 10GB, log backups are about 500mb per day.
What ...
0
votes
1answer
20 views
SQL Server Msg 2812, level 16 state 62 line 30
i want to make automatic backup and restore my database on SQL Server 2012.
i have made automatic backup with script in my windows server.
now, i want to make auto restore with script, i have found ...
0
votes
1answer
21 views
Upgrading SQL Server 2008 failover cluster node to SQL Server 2012 failover cluster node [on hold]
Please I want someone to help me with step by step method of upgrading SQL Server 2008 with failover cluster node on two different machines to SQL Server 2012 without losing the existing ...
2
votes
1answer
22 views
Extended events vs SQL Audit - performance implications
I would like to setup a audit trial kind of system on my database to monitor for UPDATE/INSERT statements on a specific table. I have two options infront of me i.e. Using SQL Server Built in audit ...
2
votes
1answer
19 views
Fail to start sql cluster role
I have installed SQL Server using Failover Clustering on 2 nodes, running Windows Server 2012 R2 Enterprise Edition and SQL Server 2008R2 Standard SP1. Shared storage is provided by an iSCSI NAS ...
-3
votes
1answer
36 views
How to make a DBADB? [on hold]
I recently seen a database server and it had a DBADB database. I asked the dba what it was for and he said he uses it to hold scripts so he has a central spot for them instead of using a network share ...
0
votes
0answers
13 views
DBCC DBINFO and linked servers [on hold]
I have 2 servers A, B. A has a linked server connection to B. B has a stored procedure that runs DBCC DBINFO WITH TABLERESULTS for a database name passed into it. From server A I can execute the ...
2
votes
0answers
39 views
Do Large Hash Joins Contribute to Stolen Pages?
On a SQL Server 2014 SP1 Enterprise Instance, I'm performing an outer join between two partitioned tables on a numeric(16,0) + char(1) composite key.
100 GB is the max server memory setting on a 128 ...
1
vote
0answers
16 views
The call to LOADLIBRARY for the XA resource manager DLL failed (PostgreSQL & SQL Server)
I am attempting to setup a Linked Server from MS SQL Server 2012 to PostgreSQL 9.3 via Linked Servers & ODBC driver from PostgreSQL. Everything works, until a given query invokes MSDTC, at which ...
4
votes
1answer
66 views
How do I set a Unicode string variable to an emoji in SQL Server?
I want to set a Unicode string variable to particular character based on its Unicode code point.
I want to use a code point beyond 65535, but the SQL Server 2008 R2 database has a collation of ...
0
votes
0answers
32 views
SQL Server Join the same table twice with different filters [on hold]
I'm working on a query right now that requires me to filter joined data differently for multiple aggregate columns, so I'm joining the same table twice and applying a filter only one one of the joins. ...
5
votes
1answer
160 views
sp_who returns more rows than the value of “max degree of parallelism”
sp_configure returns the following values on a server.
name minimum maximum config_value run_value
max degree of parallelism 0 32767 8 8
However, ...
0
votes
1answer
19 views
sql auth vs nt auth in SQL server
Ok, so everything I read says best practice is to us NT authentication in SQL server. And I can see some advantages to that but using NT auth how do I force users to access the data using only my ...
1
vote
0answers
33 views
Is there a cheap way to bulk select/insert data between SQL Server instances?
In my office, our employees use applications that are consistently writing transaction-type data to a number of databases on a SQL Server 2012. The traffic consists mainly of inserts and updates but ...
3
votes
1answer
45 views
Create a hierarchy chain (reverse path) for each node in a tree
Given a typical closure table, how would I write a query to get a list of ids back to the root? Is there a way to do that for all the [unique] ids? I'm using Microsoft SQL Server.
Given:
parent ...
1
vote
0answers
36 views
Why is my server with memory pressure when I have 4.3GB available memory? [duplicate]
I'm doing a couple of tests in my local computer and I noticed that the plan cache was being wiped every couple of minutes.
After researching a bit I noticed on my error log that my instance was with ...
0
votes
1answer
28 views
dropping SYNONYM is blocking in sql server?
I have synonyms which points to a TestDB1, by the next refresh synonyms point to TestDB2. while re-pointing from TestDB1 to TestDB2 it will drop all synonyms and re-creates them for TestDB2.
Now my ...
6
votes
1answer
638 views
Simulate deadlock with more than 2 processes
How to Simulate deadlock with more than 2 processes in SQL Server test environment? Can someone help me with the code? Just to clarify my question again, I don't want deadlock simulation with only 2 ...
1
vote
0answers
8 views
How to prevent SSDT publishing from dropping columns
I want to create a publishing profile that DOES a complete schema comparison and publishing BUT does not drop any tables or any columns that have been removed between the old and new version.
I am ...
8
votes
1answer
278 views
Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong?
I am trying to improve this (sub-) query being part of a larger query:
select SUM(isnull(IP.Q, 0)) as Q,
IP.OPID
from IP
inner join I
on I.ID = IP.IID
where
...
0
votes
0answers
55 views
Dropping PLE on query
In company we are working in project on very big database. It uses 100GB RAM. What's weird before first running a query PLE is 11k~, after running it drops to about 70, anyway when after 15 mins I ...
2
votes
0answers
79 views
Best procedure to measure query optimization effects?
I could not find valuable answers or explanations to the following question:
How should a procedure to test query improvements effects look like? Using SSMS I am not absolutely sure about the steps.
...
-1
votes
0answers
13 views
Installing a Failover Cluster Instance on a Cluster already in use for Instances hosting Always On
I have been asked to install a SQL Server FCI on a 3 nodes cluster already hosting 4 different instances with Always On Availability Groups.
While I am building the lab on my machine, I am wondering ...
0
votes
1answer
38 views
How to delete duplicate records in my database? [duplicate]
I joined about 12 databases into 1 db and in each database I have some duplicate records.
How can I delete all duplicate records in my joined database?
For example, I want to delete duplicate ...
0
votes
3answers
54 views
Problem generating restore scripts for multiple ola backups
I am using Ola for maintenance solution. I have 300 databases I want restore those databases using scripts rather than going through one by one. I prepare the following script to generate restore ...
1
vote
1answer
42 views
Query to extract all SQL database roles with INSERT and/or UPDATE privileges
I've taken over the support for a SQL 2008 database and there are a multitude of custom roles (25+) associated within. I'm trying to figure out which of the custom roles have the ability to perform ...
1
vote
1answer
28 views
Clearing Log Shipping config Secondary server inaccessible [duplicate]
We are using SQL 2012
Our Secondary server in a log shipping configuration went offline never to come back (the VM was permanently deleted before log shipping configuration was updated).
On the ...
2
votes
1answer
35 views
Dynamic Parameters In Pivot
I know the premise of a PIVOT() this syntax produces it perfectly for me:
Select *
FROM
(
Select
field1
,annual
,dv As Amt
FROM Testing
) x
pivot
(
SUM(Amt)
,for annual IN ...
3
votes
1answer
78 views
Most useful stored procedures for a new DBA? [on hold]
I am a new DBA working with several more experienced DBA's. When I am scratching my head one of them says "oh just run SP_xx_xx", and sure enough that begins the path to solution.
Of course they all ...
-4
votes
0answers
30 views
if increase table in a database (20 to 20000) on there database performance is effected or not in sql [on hold]
in that time in my database having only 20 table but now our business model change
and there need 2000 table in future. so i cant understand that database performance is effected or not.if effected ...
1
vote
3answers
43 views
DBCC CHECKDB consistency-based I/O error
Running:
DBCC CHECKDB(DatabaseName) with NO_INFOMSGS
gives me the following error:
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical
consistency-based I/O error: ...
0
votes
0answers
32 views
SSMS Error - Parameter is not valid
I have both SQL server 2005 and SQL server 2008 management studio installed on a windows server 2003 32 Bit. My issues is that both the SSMS opens fine but i face this below error messages when i ...
1
vote
1answer
18 views
SQL Server Integration Services 2012 - Legacy deployment
I'm currently reading a lot about SSIS and I have read that there are now two models for deploying a solution : the one using "Projects" and the one using "Packages".
Starting SQL Server 2012, a ...
8
votes
2answers
400 views
Create a “INTO” table with primary key
maybe for this community my problem is easy, but for me (a simple java programmer) it's a BIG problem.
I have a Big DB with more and more data. So, the external db admin had create a job that show me ...
2
votes
1answer
73 views
Is it worth to optimize queries with EarlyTermination Timeout?
Right after running Ola's IndexOptimize to rebuild all indexes and statistics, I cleared the data and plan cache and ran a very complex ugly query to get a baseline of it's performance. The query ...
-1
votes
0answers
16 views
Mirrored Backup or Disk to Disk Replicated Backup
What would be preferable?
Performing a database backup and mirroring the backup to two separate locations as part of the backup process.
or
Backing up to disk to disk backup and replicating that ...
