Questions tagged [sql-server-2012]
SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.
4,192
questions
1
vote
1answer
9 views
log_reuse_Wait_desc always return LOG_BACKUP even right after log backup
We are using SQL Server 2012 standard Edition (SP4)- 11.0.700.0(x64) (Build 14393)(HYpervisor).
Datfile Size = 171GB
Log File Size = 106 GB
When I queried sys.databases to see the ...
0
votes
2answers
45 views
How to use IN to compare a single value with a comma separated field in another column
I have a table that looks like this. let's call it table1:
|attributeMask |
|--------------|
| ,300,302,400 |
| ,301,500 |
| ,900,876 |
These are varchar values.
Then I have another ...
0
votes
0answers
28 views
Where to find the installer for SQL Server Native Client 11.0.7462?
I would like the exact version mentioned since my application does not support the 11.0.5058 version of the SQL Server Native Client (sqlncli).
1
vote
1answer
74 views
Adding a clustered index to a heap table
I've noticed that we have a few heap tables on our database, they all have a non clustered index on the primary key (which is an Int). The biggest table has over 2 million rows, a forwarded record ...
1
vote
1answer
29 views
Extending fiat transaction log to support cryptocurrency
Let's assuming that I have an existing database that retaings fiat currency transaction as described below with decimal (18, 4).
Now, I would like to record bitcoin, ethereum and erc20 token ...
3
votes
1answer
46 views
Seting MAXDOP for SQLserver 2016 and above
I was going through MS documentation reg: setting MAXDOP starting SQL2016 as that Automatic soft NUMA enabled by default and getting confused on how to make the changes to current SQ2014 and SQL2012 ...
2
votes
2answers
122 views
Cannot find Database Mail feature in SQL Server Express 2012 SP1
I need to use SQL Server Database Mail feature in my project. However Database Mail seems not available on my development instance.
There should be a 'Database Mail' node in the Object Explorer. In ...
1
vote
1answer
53 views
ETL process causes database to rapidly grow - Shrinkfile reduces size from 140GB to 2GB - Keep shrinking?
I have a database (SQL Server 2012) that is used mainly for ETL of data. Every hour SSIS packages run that truncates/deletes and reloads full datasets. The database keeps growing even though the data ...
1
vote
0answers
16 views
SSRS managed shared subscriptions data from from database
While running an ETL process we need to pause the shared managed SSRS subscriptions and then enable them and run them accordingly when our ETL has progressed. In order to do that we take a backup of ...
0
votes
1answer
67 views
Error 23 on transaction log backup, full backup works fine
I have a database with the database files on the E: drive, and logs files on the D: drive.
I'm getting the following error when trying to run a transaction log backup:
Read on "D:\LOG\AOA_Log.ldf" ...
0
votes
2answers
32 views
Get datetime between start and end datetime
Start_Date End_Date
2019-09-10 17:48:00.000 2019-09-10 18:00:00.000
2019-09-10 17:00:00.000 2019-09-10 17:45:00.000
2019-09-10 17:30:00.000 2019-09-10 18:...
1
vote
2answers
77 views
WHERE in based on multiple columns
I am trying to achieve something like this in sql server 2012. But this syntax does not exists. What are the alternatives to it and why does sql server does not support such syntactic sugar?
Which ...
1
vote
3answers
73 views
Join Comma Separated Same Value
create table dbo.CourseMaster
(
CourseId char(2),
CourseName char(3)
);
create table dbo.StudentMaster
(
ROLLNO char(5),
NAME varchar(10),
ADDRESS varchar(20),
Course varchar(100)
);
...
1
vote
1answer
25 views
Preparing a query to paste into excel to get growth by month
I have a table that looks like this:
|DB | DATA_MB | LOG_MB | |
| DB1 | 4,4375 | 2,25 | 2019-08-06 10:37:49.160 | |
| DB2 | 8 | 0,5 | 2019-08-06 10:37:49....
1
vote
0answers
48 views
Optimising filter with dynamic computed column
I need to implement user risk scoring in model
The user risk scoring must compile dynamically on changes (eg: profile, transaction) - meaning for any changes on profile or any incoming transaction, ...
1
vote
1answer
28 views
Geography Inside a CTE
I have the following schema (simplified):
Locations
Id INT,
StaticLocation GEOGRAPHY
Capacities
LocationId INT
SpareCapacity INT
I'm trying to find, for each location a sum of the SpareCapacity ...
1
vote
2answers
56 views
Trying to identify what process is making a connection to a specific DB in SQL Server
I am attempting to track down... something... that is making a connection to a specific database without (apparently) doing anything with the connection. However, so far I have had no luck.
The data ...
4
votes
2answers
71 views
Does a computed column break 3NF (third normal form)?
From my understanding of 3NF, there should not be any transitive dependency.
However, this is what I found from the AdventureWorks2012 database Production.WorkOrder table. Since StockedQty is ...
5
votes
2answers
569 views
SQL Always On COPY ONLY backups - what's the point if I cant restore the AG from these backups?
Think I already know the answer to this but I'll ask anyway.
Situation is:
SQL 2012 Always On - 2 synchronous replicas.
7 TB worth of Sharepoint DBs in 1 AG.
Before patching - I want a Full DB ...
7
votes
1answer
104 views
FORMAT returns large row size and data size
I am surprise with one of my findings that using a FORMAT () does have very big impact on the row size and data size. It is almost 250x more of the size of not applying FORMAT ().
My question is:
1) ...
-1
votes
1answer
56 views
SQL server login get unwanted high privileges on a db when created
We have a demand to create a new user with the ability to SELECT only 4 specific tables. In order to do that we create the login and map it to the requiered db :
USE [master]
GO
CREATE LOGIN [pos] ...
0
votes
1answer
60 views
Deadlock on partitioned table
we see deadlocks when loading a partitioned table. I wonder if someone can confirm what I think is the reason, or if I am looking at it wrong and we need to fix another thing.
Our setup is like this: ...
0
votes
1answer
37 views
How to loop through all table rows and perform subquery using values of same row?
I have a table structured as follows:
| reference | valA | valB | xml |
1 NULL NULL <xml stuff>
3 NULL NULL <xml stuff>
4 NULL NULL ...
0
votes
0answers
74 views
Optimising pagination with large dataset and offset [duplicate]
Suppose that I have 50 million record of user.
And the business requirement is to allow the admin to select any pageNumber and any pageSize to retrieve the user record
Of course, it will not be a ...
6
votes
2answers
160 views
Eliminate key lookup in execution plan
I have the following query:
DECLARE @p__linq__0 UNIQUEIDENTIFIER
SET @p__linq__0 = '... some guid ...'
SELECT TOP 1
[EventId] AS [EventId],
[DateCreated] AS [DateCreated],
...
-1
votes
1answer
45 views
Creating database with only ldf file created 2 .ldf file
When I run this query without specifying the ldf file it automatically creates the ldf file.
Declare @DBname nvarchar(50) = 'Test'
Declare @path nvarchar(50) = 'D:\DB'
Declare @query nvarchar(max)
...
0
votes
3answers
84 views
AlwaysOn High Availability MDF and LDF file locations
We have 4 replicas with 03 replica in read only mode with manual failover.
We have some drive issues and we need to rearrange the MDF and LDF files.
On the primary we have 2 drives I and J. The I ...
1
vote
2answers
42 views
Microsoft SQL Joins and Where Clause
I am sure a very simple question for people out there but my brain has shutdown.
I have joined two tables with a left join which has worked as hoped as I do need to bring back the NULLs in one of ...
0
votes
1answer
37 views
Performance Tuning
I need input on the below queries in terms of which one is better from performance perspective.
Query#1:
DECLARE @Var1 NVARCHAR(15) = NULL; -- This variable can have NULL or specific value
SELECT ...
3
votes
2answers
80 views
Sql server sleeping state is increasing using ADO.NET?
We have one live ASP.Net application which is used by around 1000 concurrent users. In this application we have used connection pooling. There are 2 connection string defined in web.config with ...
1
vote
1answer
47 views
Incremental backup between now and backup taken one week ago
We do a full backup of our SQL Server 2012 database every night.
A new developer needs an incremental (or differential) backup of a backup we took one week ago. The reason is that for them, ...
1
vote
0answers
32 views
Informatica MD5 function vs SQL Hashbytes MD5 function
I am hoping someone has some crossover experience here. I am attempting to utilize the Hashbytes function within SQL to match a value that is being generated utilizing the MD5 function from ...
2
votes
1answer
39 views
Log Shipping and Daily Backup
My SQL Server 2012 DR setup is a mess. We've had a maintenance plan in place from the beginning that takes a daily full backup, with hourly log backups. Recently, we set up log shipping to an off-site ...
7
votes
2answers
364 views
See details of old sessions
Is there any way of seeing details of sessions from the past (I fully expect the answer to be "no"). I am trying to find out what server a certain application is running on, that application accessing ...
0
votes
1answer
39 views
Passing Variable in json_value in sql server 2016 [duplicate]
declare @id int
set @id = 1754
select
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."1754".value')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"'+...
1
vote
1answer
37 views
What impact on the index storage size does overprovisioning of varchar types have?
Does over-provisioning varchar columns (as in using varchar(100) to store values from 1 to 10 characters long vs using varchar(10)) have any impact on the index storage size and if yes than why?
I ...
0
votes
1answer
28 views
Does Data Migration Advisor migrate system databases?
I would like to know if Data Migration Assistant copies system databases (master, model, msdb) into the new server.
0
votes
0answers
11 views
Report Service stopped unexpectedly
The SSRS service stopped unexpectedly and the workaround was to recycle the app pool and then restart the service, but no clue of what happened.
When looking at the error log below:
<Path>C:\...
3
votes
1answer
141 views
How can I get rid of this Lazy Spool, or otherwise improve this query's performance?
Here is the plan: https://www.brentozar.com/pastetheplan/?id=rkM8d7ONS
I am mostly interested in how to get rid of the lazy spool?
This is the query:
SELECT DISTINCT
SM.Security_ID '...
1
vote
1answer
32 views
When are statistics created?
I know that when we create an index then statistics are automatically created. My question is: Is it the only case when statistics are created? I remember that once I found on the internet that we ...
0
votes
2answers
136 views
How can I use SQL Profiler to capture a single stored procedure
Using SQL Server SQL Profiler, which configuration can be used to monitor a single stored procedure? I'd like to capture all EXEC sprocName to include the parameter list. I'd like to capture this ...
1
vote
1answer
65 views
Is it useful to index a column if it is already the first column in a composite index? [closed]
Suppose I have this table (simplified from my project):
create table BillTax
(
BillID int not null constraint FK_BillTax_Bill foreign key references Bill(BillID),
TaxCode varchar(20) not null,...
0
votes
1answer
25 views
Implementing gMSA account for SQL FCI
I have a question on implementing gMSA account for managing authentication to SQL server.
Our goal is to use gMSA account to manage the auto password change activities for SQl server service and it's ...
-1
votes
1answer
58 views
restart sequence when the year changes
Is it possible to do a sequence with Start at 1 and INCREMENT 1, and when changing the year, restart the sequence?
-- code# Create a sequence
CREATE SEQUENCE SEQ_ALUNO
START WITH 1
INCREMENT BY ...
0
votes
0answers
15 views
Not able to migrate DTS package from SQL Server 2008 to 2012. Getting errored out
Requirement: We need to migrate SSIS/DTS package from SQL Server 2008 to another machine having SQL Server 2012 on it.
Steps performed so far:
1. First I exported the package from Source server to ...
0
votes
1answer
41 views
SQL Full Backup job is taking too long to complete
I am facing a strange issue with daily FULL backup. We have a total of 23 databases. We have 1.21 TB big database in this SQL server 2012 Standard Edition. We are taking daily FULL database backup for ...
4
votes
2answers
80 views
A table with all fields NULLable with important, sensitive data
I have a requirement for a database table to hold sensitive patient information about their lifestyle (alcohol/ drug use / injecting etc).
On screen the information is sectioned into tabs - "alcohol",...
-1
votes
2answers
76 views
Table with 80 foreign key
I have a table with 120 columns and most of them (around 80) are foreign key . We are refactor some of main table in our system . I was thinking of change this table to divide in small table because ...
0
votes
0answers
7 views
SSIS package || Scheduling in SQL Server 2012
We are tring first time to create SSIS package for export the Data from SQL table to Excel.
Using this tutorial in SQL Server 2012.
Now package has been created. But we are stucked in scheduling it....
9
votes
1answer
264 views
Why do MS SQL Server SEQUENCEs not have an ORDER parameter like Oracle?
In the documentation of CREATE SEQUENCE for T-SQL, you can see that the CREATE SEQUENCE command has no ORDER parameter.
For comparison, the Oracle docs for CREATE SEQUENCE show the ORDER/NOORDER ...
