Questions tagged [sql-server-2012]
SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.
4,371
questions
0
votes
0answers
7 views
Availability Group Performance
If I give a user a right to read on my secondary replica which is in sync (not async)with primary replica ; and if that user runs a query with a lot of Select into ##Temptable statement on Database (...
2
votes
1answer
48 views
Reason why SQL Max memory expansion seems slow when migrated to newer version of SQL server
We are trying to understand below behavior happening in SQL Server and need some guidance:-
After migrating from older version SQL2012 (running on bare metal P Server) to newer SQL2017 running inside ...
0
votes
2answers
48 views
When a transaction truncate a global temporary table, is that logged in transaction log or error log?
When a transaction truncate a global temporary table, is that logged in transaction log or error log ? I have inherited bunch of old SQL 2012 servers from my senior dbas who left the organisation. I'm ...
0
votes
1answer
20 views
TempDB Version Store used by DB STARTUP background process
I have two availability group on the same SQL Server 2012 Instance (synchronous commit).
Trace flags 1118 and 1117 are both enabled.
TempDB has been growing for about two weeks: it starts from 36GB ...
-2
votes
0answers
13 views
Calculating percentiles of a FROM command [closed]
I have a query which generates a table of trials and values from a database (example pic below)
SELECT * FROM [ReMetrica].[ReMetrica_Result].[GetTrialValuesAsDouble] ('BD5E92F3-3A3B-43B4-BC22-...
-1
votes
1answer
30 views
SQL alwayson AG Disaster Recovery
Need advise on below:
I have 2 node alwayson AG on Primary Sync mode in the current configuration and fsw is in Primary DC only
Actually during Switchover the entire DC 2 node and fsw will be down
...
1
vote
0answers
25 views
sp_prepare vs sp_execeute PHP MSSQL
This question is regarding the PDO_SQLSVR driver for PHP and Microsoft SQL Server and how it interacts with the database, whether via sp_prepare (prepare statement execution) or sp_execute (direct ...
0
votes
1answer
31 views
Joining Additional Table Column
I need to add another column from another table i.e. CusSorMaster+.SalesOrder.The join is on where dbo.SorMaster.SalesOrder = dbo.CusSorMaster+.SalesOrder. This wasn't written by me and I need to ...
-1
votes
0answers
14 views
How to compare two data rows and column values in SQL with a custom output
I have a table called product and need to compare two rows with a custom output. Please see the details below.
Any quick help is very much appreciated!
Thanks
Have a good day!
0
votes
2answers
29 views
Grant an AD Group permission to edit all MSSQL Agent jobs
We have a team of people, we all log in MSSQL Server from SSMS using our Active Directory accounts, by Windows Authentication.
Our AD accounts are added to an AD group, which is used to grant all ...
3
votes
1answer
145 views
Why does SSMS's line counting often get messed up?
I'm trying to get some code to run with the time tested 'execute, google the red text, do what SO says, repeat' method and I've noticed that the error handler's line counting is often wrong.
For ...
2
votes
1answer
23 views
User on SQLAgentOperatorRole unable to edit jobs
My Active Directory account is on an AD group that is on msdb's SQLAgentOperatorRole. I'm able to list existing MSSQL Agent jobs, but I can only view their steps and schedules, I'm unable to edit them ...
1
vote
2answers
35 views
SQL Server 2017 Backup strategy
RTO is 8 hours
RPO is 24 hours
Retention Period: 5 days
Based on above values I have plan to configure below backup stratgey for Production. Please suggest if there is any changes required in below ...
1
vote
1answer
39 views
How to find the sql jobs that have not run for more than two months?
I would like to get a query to ascertain which sql jobs have not run for more than 60 days?
0
votes
0answers
16 views
LIQUIBASE Locking our DB with duplicate IDs post migration - handling changesets
I apologize in advance if this is the wrong thread category but we recently migrated a DB and our liquibase configuration is seemingly not recognizing that there is a new path so Liquibase seems to be ...
0
votes
0answers
21 views
Indexing for encrypted data in MS SQL
Our customer has some data in varchar columns. fullname per say. There is an index on it.
Now they want this data to be encrypted and using symmetric keys is the solution proposed.
I was able to ...
0
votes
0answers
17 views
Sql Server Fulltext catalog gets truncated
We're facing an interesting issue which we can't reproduce.
On our Sql 2012 from time to time our Fulltext catalog gets truncated, We read MSDN but we didn't find any information if there is such an ...
7
votes
1answer
109 views
FORMAT 'G17' vs 'G'
I have a question about this fiddle:
DECLARE @TestVal AS float = 8.88;
SELECT flt = @TestVal
, xml = (SELECT Value = @TestVal FOR XML PATH(''), TYPE)
, fmt17 = FORMAT(@TestVal, 'G17')
, ...
-1
votes
1answer
33 views
is it a good idea to migrate a SQL Server database that uses a lot of linked servers and external conenctions with different databases to Azure?
We have a 1tb database that use information from other 4 or 5 databases. It has hundreds of procedures using external databases to calculate and etc. we have 0 problems inside SQL Server.
I would like ...
0
votes
1answer
16 views
Find Unique Index that does not have Unique Constraint
I need to find list of unique indices that does not have unique constraint on that column. How to achieve this?
I know, following query will give me unique constraints.
SELECT *
FROM ...
4
votes
2answers
497 views
what is the best approach to send report by Email?
I've been requested to create some sql server reports ( create a report and send them by email ) for some poeple. I thought it would be like 2 or 3 but now that they know I can do it, every week I ...
2
votes
1answer
33 views
Is it OK to restore master db backup, after changing WSFC clustered instance into non-clustered instance
I'm converting WSFC fail-over clustered SQL Server 2012 instance into non-clustered one.
My question in short: Is it OK to restore a master database backup taken from the clustered instance, into the ...
3
votes
3answers
430 views
Need help to understand tuning up a slow SQL server query [closed]
For one of our databases the query like below is extremely slow.
Due to security reasons I am not able to share the actual query or plan but just to give an idea how query is written is below
SELECT [...
-3
votes
1answer
83 views
SQL Server Integrity Check error
I’ve Implemented Ola's SQL Server Integrity Check on one of my SQL 2012 Servers and created a SQL Agent job to run it.
The job fails every time I run it, but when I run the code below manually it ...
0
votes
2answers
19 views
SQL server alwayson 3 Node Primary Down
I am using three node alwayson Availability Group SQL 2012 Enterprise Edition.
2 Node in DC with File Share Witness quorum also in DC and third node to be setup in DR
I need to perform a DR switchover ...
0
votes
0answers
34 views
List of databases present in specific maintenance plan
I have two maintenance plans and also I have two user-defined databases demo1 and demo2. The first maintenance plan is used to take the backup of all the databases. the second maintenance plan is used ...
0
votes
1answer
62 views
“Cannot continue the execution because the session is in the kill state” when querying system tables
This is a snippet from some code I run on a 2012 (SP3-CU9) (KB4016762) - 11.0.6598.0 (X64) system that I just inherited and have not yet had a chance to fully patch. AlwaysOn AG are not enabled, and ...
0
votes
1answer
35 views
Need to upgrade SQL Server from 2012 to 2017 on a Server 2008 box
I have been asked to upgrade SQL Server from 2012 to 2017, it is currently installed on a server with Windows 2008 OS.
My thinking from what I've researched is this:
Take a snapshot of the server
...
0
votes
1answer
26 views
Collecting VLF information on all versions of SQL server
Based on my monitoring collection, i am having an issue while collecting info regarding how many VLF' i have for # of databases in SQL instance.
1st issue - Is there a single script which can be used ...
1
vote
1answer
41 views
What would be the best way to show table/database growth?
I've created a table that stores top 10 biggest tables on a database. it's schema is like this:
(example with 3 tables only)
| Table | size_mb | Date |
|-------|---------|------------|
| tb1 | ...
0
votes
0answers
16 views
Using SQL Agent Job to execute a script from a UNC path
I need to execute a script located on an UNC share. I've setup my SQL job to use CMDExec and to execute the following command:
PowerShell -NOPROFILE -ExecutionPolicy Bypass -File \\Server\...
0
votes
1answer
27 views
How do get correct instance name on Multi instance cluster
Just a quick question and somehow unable to figure it out.
We have a Active Active cluster or should i say Multi instance cluster as below. node A and B are part of same window cluster
Node A --> ...
0
votes
0answers
34 views
Strange wait types after enabling CDC
We recently enabled CDC on four tables in a busy database and since then we are capturing a lot of sessions that are waiting on XE_BUFFERMGR_FREEBUF_EVENT. When we see this wait type CDC is trying to ...
5
votes
1answer
152 views
Why does my server have thousands of “HIDDEN ONLINE” schedulers?
We have a SQL Server 2012 instance with 16 CPU cores. When looking in sys.dm_os_schedulers we see the expected 16 rows that are "VISIBLE ONLINE", one per core. The error log shows the ...
1
vote
1answer
65 views
Query cycles between suspended and runnable without ever completing
I have an insert statement, extracting data through multiple inner joins from around 8 tables. It is getting into suspended state then runnable and vice versa forever; never coming back to running ...
0
votes
1answer
57 views
SQL SERVER auditing SELECT of a single column
Is this possible, or is table-level the most granular access?
So far, Google search has shown nothing, and this screenshot does not mention column, but I thought I'd ask here.
SQL Server version is ...
1
vote
1answer
41 views
Bulk Insert Permissions Issues
I'm experiencing the dreaded Msg 4861 error message when attempting to use Bulk Insert on a 2012 SP4 SQL Server. "Cannot bulk load because the file "\PathToFileShare.d1.xxx.com\folder\file.dat" could ...
0
votes
2answers
75 views
Vastly different query run-times on different servers [duplicate]
I've got two databases setup on two different SQL Servers. One is 2012 and one is 2016, but aside from that, they have the same CPUs, same core count, and same RAM. They're both VMs in the same ...
0
votes
1answer
44 views
Increase TempDB Files, Shrink VLF's with FCI
I am planning to resize and increase the number of tempDB files and shrink the VLF's on an FCI. In this case, the tempdb files are stored on the network drive, rather than the local disk. I couldn't ...
-2
votes
1answer
16 views
ALTER DATABASE MODIFY FILE fails - I want to alter data file sizes using size = @variable - not working
Example:
DECLARE @sql NVARCHAR(255)
DECLARE @DBname NVARCHAR(128), @FileName NVARCHAR(128), @NewSizeMB INT
SET @DBname = 'AGTestDB'
SET @FileName = 'AGTestDB'
SET @NewSizeMB = 1024
SET @sql = N'...
0
votes
0answers
42 views
Why is for some sessions count of deallocated pages in tempdb bigger than allocated?
Why is for some sessions count of deallocated pages in tempdb bigger than allocated?
I used query
select session_id,
user_objects_alloc_page_count,
user_objects_dealloc_page_count,
...
0
votes
1answer
89 views
The OLE DB provider “MSDASQL” for linked server “(null)” reported an error
I created a linked server to MySQL database server on my SQL Server database server. I tested the connection. It succeeded successfully for MySQL connection. I can see the databases on MySQL instance ...
0
votes
1answer
38 views
Permission to select sys tables in stored procedure
I have implemented the stored procedure to check if a specific [schema].[procedure] exists.
CREATE PROCEDURE [BlobCheckExisting].[usp_DoesStoredProcedureExist]
@schemaName varchar(128),
@...
1
vote
1answer
30 views
Where Database Alerts & Operators Exist In SQL Server?
My SQL Server database server has crashed. I have system databases backup. I will reinstall the sql server but i don't know how to find my operators, alerts and config settings. Is there a way to find ...
1
vote
1answer
33 views
Improve querying of Extended Events target file
As part of our server estate monitoring, I am adding extended events to pick up warnings, blocking etc and I'd like to periodically (Every couple of minutes) query the event file to collect the data. ...
1
vote
1answer
44 views
SQL Server: calculating memory requirements for Full db backups
In 2018, we inherited a production SQL Server 2012 FCI running on Windows 2012 with 32 GB RAM. SQL Server max server memory was set at 23.6 GB, and things were running fine.
However, in 2019, we ...
-1
votes
1answer
23 views
SQL Server scheduled scripting of DDL (2020 edition) [duplicate]
I'm currently in need of a way to pragmatically generate DDL scripts for a single database in SQL Server. My current need is to do this in 2012, but it's likely I'll need to be able to do this with ...
1
vote
2answers
99 views
How can I optimize and speed up a query [closed]
Below query never completes and runs for over 14 hours:
SELECT
U.Age, U.CreationDate, U.DisplayName,
P.AcceptedAnswerId, P.ClosedDate, p .Title, P.ViewCount,
C.CreationDate,C.Score ,C.UserId
FROM ...
0
votes
1answer
18 views
Is it possible to shrink mdf file in replication server
Is it possible to shrink the data file of a SQL Server replication target server and continue the replication operation?
Ideally I'm trying to avoid shrinking the data file to reduce down time, then ...
1
vote
0answers
53 views
Why are column store indexes disabled automatically when a database is restored?
I have recently done a server migration where we backed up and restored all the databases from a SQL Server 2012 version to SQL Server 2019. However the restored databases appear to all have their ...
