SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.

learn more… | top users | synonyms (3)

1
vote
0answers
166 views

Problem to understand a 70-462 exam question

This is the question: You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN). The financial database has the following ...
3
votes
1answer
37 views

SQL Server Replication

We had an issue with replication, where a large (7.5 million row) update was done on a 33 million row table. Replication converted that to 7.5 million individual update statements. When my alerting ...
6
votes
1answer
106 views

Retrieve logon trigger from backup

A logon trigger was added and dropped from our production database. I have full backup of master after the trigger was added, and before it was dropped. I am trying to recover from the backup but I ...
1
vote
0answers
30 views

Is it possible to query the avg time in ms per read/write

I was wondering if it was possible to obtain the avg block read and avg block write in ms with a query in SQL Server. If SQL Server knows how many IO requests are made for a given wait type and how ...
3
votes
1answer
81 views

Error: “The certificate cannot be dropped because one or more entities are either signed or encrypted using it.”

I have a certificate I would like to remove from the database. If I issue the command DROP CERTIFICATE <FooCert> I get the error The certificate cannot be dropped because one or more ...
0
votes
0answers
31 views

Failed to flush the commit table to disk

We recently had this error come up for one our databases https://support.microsoft.com/en-ca/kb/2603910 In order to fix it we patched the server to the latest SP and Security Update. Current version ...
2
votes
1answer
162 views

By Mistake Deleted AlwaysOn Availability Database from live Production Server

Today by mistake deleted The AlwaysOn Availability Database from live server. Let I am explaining the Details. In AlwaysON Availability Groups, there is 2 Availability Replica. That is Primary ...
15
votes
3answers
546 views

Implementing subtype of a subtype in type/subtype design pattern with mutually exclusive subclasses

Introduction In order for this question to be useful for future readers I will use the generic data model to illustrate the problem I face. Our data model consists of 3 entities, which shall be ...
1
vote
1answer
33 views

Notify on SQL trace event starting?

I was wondering if there was a method of receiving some sort of notification when a SQL trace begins - be it e-mail or some other method? The circumstances of needing this we currently have a ...
3
votes
2answers
206 views

Adding 4th table to join jumps processing time from seconds to over 20 minutes

I am trying to join a total of 4 tables together. When I only join 3 of the tables together I get a result set of about 220,000 records in about 2 seconds, but as soon as I throw the 4th table in, the ...
1
vote
1answer
20 views

Changing Key (PK, FK) properties in DROP/Add column statements

I want to change a table by adding a column. So far nothing to see here, BUT I would like to make this column part of a composite key, i.e., I now have a layout table_name( Field_1 datatype PK, ...
1
vote
2answers
71 views

SQL Server 2012 cannot use file server for log shipping

I have a problem with log shipping in SQL Server 2012. I have two servers for log shipping, let's say server A and server B. For the first experiment, when I use a shared folder from my computer ...
1
vote
1answer
79 views

Tables not displaying after creating database by script. Do I need to insert?

I successfully created (scripted) a database in SQL Server 2012. My problem is that while the database itself is listed in the object explorer, the tables from the database are not. The database I ...
1
vote
1answer
55 views

WSFC Quorum query

I have an three node AlwaysOn AG setup, running on SQL Server 2012 SP3 in Windows Server 2012 R2. Node 1 and Node 2 are in the same physical location. Node 2 is the secondary replica. Third node acts ...
0
votes
1answer
39 views

SQL Server 2012 SP1 to SP3 with Mirror

I have a Production server and DR server where the databases are mirrored, both on SQL Server 2012 SP1. I want to apply SP3. I can do this on a weekend and I don't want a failover. Should these be ...
1
vote
1answer
36 views

Copying Partitioned table data to another table on different SQL Server

We have a partition table on Production environment with 2338117620 rows and 206 partitions. Now we have to copy only last 2 year data in Test environment. But the problem is that , Import Export ...
1
vote
2answers
36 views

Linked Server works on SSMS and isn't found on SSDT

My Microsoft SQL Server has a linked server to another SQL Server. I have a simple query that runs on mine, deleting a remote table (truncate doesn't work) and inserting data to it. This query works ...
0
votes
0answers
50 views

Running internal.cleanup_server_retention_window on SSISDB causes t-log to fill up

I have a SQL Agent job that runs internal.cleanup_server_retention_window on my SSISDB to keep it's size in check. The retention period is one day and it's scheduled to run each day. The problem is ...
4
votes
1answer
34 views

MSDTC and its relation to SQL Server

So I'm doing some research on MSDTC and its role it does or does not play in various types of calls within SQL Server, and it got me thinking on the following questions: Are there any 3rd party ...
2
votes
1answer
44 views

Execute Stored Procedure with Account that has only read access

I created a stored procedure that has TRUNCATE and INSERT statements in it that I use for an SSRS report. I typically only use an account that is a member of db_datareader for SSRS reports, however ...
2
votes
0answers
52 views

Procedure runs slower over time; fixed by cache clear or reboot

We are experiencing a strange issue where an application which was fixed by creating missing index requests, updating stale stats, and removing lookups and other scans, was running fine once fixed, ...
17
votes
1answer
102 views

Sequence - NO CACHE vs CACHE 1

Is there any difference between a SEQUENCE declared using NO CACHE and one declared using CACHE 1 in SQL Server 2012+? Sequence #1: CREATE SEQUENCE dbo.MySeqCache1 AS INT START WITH 1 ...
1
vote
1answer
37 views

Best solution to keep a big table synched in 2 DB

I manage a few tables with more than 1 billion records. Daily, their ETLs run, reading data from flat files and inserting a few thousand records on each of them. Now I need to keep these same tables ...
6
votes
1answer
175 views

Create a function to generate a column value in a specific format

I am new to databases or, in fact, programming. I want to create a function or maybe a stored procedure (if it works) to insert/create default values for a column of the users table. In this table, ...
1
vote
1answer
85 views

Why is it faster to do four simple selects instead of one select with left joins?

I need to find a single value from a table A containing three foreign keys to two other tables B and C. For the sake of experiment, I tested two ways to query the value: Multiple queries: declare @...
4
votes
2answers
92 views

Why is .bak file size increasing/doubling with each consecutive backup on SQL Server?

I'm running a simple DB on SQL Server 2012 Express. Just today, when I backup the database, the .bak file size doubles to what it was from the previous backup just minutes before. I have done ...
1
vote
1answer
44 views

Log shipping secondaries in STANDBY stuck in 'Restoring…' - SQL Server 2012 SP3

Yesterday, I configured Log shipping between 2 SQL Server 2012 SP3 servers for a total of 13 databases. Some specifics: I'm using 'Azure files' to transfer the log files (CIFS accessible BLOB ...
5
votes
2answers
79 views

QUERY - join two tables based on date and filter results

My apologies for the cryptic title. I guess my inability to condense my question into a title is indicative of my inability to come up with an answer to the following: I have data in two tables (MS ...
1
vote
1answer
38 views

Login in Always On Availability Groups

I am new to Always On Availability Groups and I am little bit confused with creation of logins. Let's assume an environment NODE1(Primary) & NODE2(secondary) & NODE3(Stand by) & VAEWI(...
0
votes
1answer
46 views

Would SQL Replication prove to be a FASTER option than downloading compressed DB files every time from servers at different geographic locations?

My requirement of synchronising two DB servers in different geographic locations is as follows synchronise some DBs on 2 servers in regular intervals (manual). One in US is an intermediate release ...
0
votes
1answer
31 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 ...
1
vote
1answer
26 views

Why does updating a large VARBINARY break replication (MSSQL_REPL21100)?

We have replication set up on a VARBINARY(MAX) column that stores image data (yes, yes, I know - don't blame me, I didn't design it). Recently, we started inserting larger images (around 200kb) and ...
1
vote
1answer
29 views

Why are Op Stats all 0's and yet Usage Stats show 29 seeks

Why are Op Stats all 0's and yet Usage Stats show 29 seeks. This was returned from Brent Ozar's sp_blitzindex. Usage Stats Op Stats Reads: 29 (29 seek) Writes:14 0 ...
0
votes
1answer
33 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 ...
0
votes
1answer
51 views

Index Reorganize/ Rebuild Time

I have maintenance job on SQL Server 2012 Enterprise Edition that runs daily to check index fragmentation and reorganize or rebuild the index based on the percentage of fragmentation. I used this ...
3
votes
2answers
175 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
80 views
+100

Move SQL Server cluster from one location to other

I am looking for ideas on how to move SQL Server 2012(standard) two node cluster installed on Windows server 2012 standard to other location which is far away from current location. The two locations ...
2
votes
1answer
18 views

How is Activity Monitor Pulling processes specific memory information?

How is SQL Server Management Studio's Activity Monitor pulling the following memory by processes information? The screen I am talking about is... Log into the database, right click on the server in ...
1
vote
1answer
33 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 ...
1
vote
1answer
50 views

Very slow insert performance on large table (EF)

One of our systems has a table that receives around 20k inserts per day - it currently contains ~10m rows. We've just pushed out a system upgrade and I'm intermittently getting some shockingly slow ...
0
votes
1answer
47 views

which version SQL server can Install Windows 10? [closed]

I have Os Windows 10. I am trying to install SQL Server 2012. After installing I try to connect to the server, the connection to the server returns the following error message: A network-related ...
0
votes
1answer
48 views

SQL Server 2012 on a Windows 2008 R2 cluster, would like to upgrade Windows to 2012 R2

I need someone to help validate a strategy. I have a pair of Windows 2008 R2 server in a active/passive failover cluster with SQL Server 2012 installed. I would like to upgrade the Windows OS to ...
1
vote
0answers
41 views

Replacement for transactional replication

My colleagues and I have to migrate a database from SQL Server 2008 R2 to SQL Server 2012. Schema, triggers, stored procedures etc. are working fine. But we also have to "migrate" the replication ...
0
votes
2answers
67 views

A tail-log backup of the source database will be taken ? Why I am seeing this warning

I took a backup of a database from 1st server (which runs SQL Server 2014). When I try to restore the backup to a 2nd server (which runs SQL Server 2012) I get this warning message: Warning ...
1
vote
2answers
116 views

Consideration for a little bit heavy database

Environment information OS : Windows Server 2012 R2 ( 64bit ) RAM : 16.00GB CPU : Intel(R) Xeon(R) CPU E5-2609 @ 2.40GHz SQL : Windows SQL Server 2012 Standard Brief Database Data Information ...
14
votes
1answer
495 views

Plan cache size and reserved memory

When running a query including the Actual Execution Plan, the root operator (SELECT) tells me that the Cached Plan Size is 32KB. A query that joins sys.dm_exec_cached_plans and sys....
2
votes
1answer
208 views

Plan cache behaviour

Can someone please explain me why the following is no getting cached use AdventureWorks2014 go DECLARE @id INT=43865 SELECT sp.[BusinessEntityID] , so.salesorderid FROM sales.salesorderheader so JOIN ...
6
votes
2answers
234 views

Is there a T-SQL equivalent for punctuation as [0-9] is for numbers and [a-z] is for letters?

Is there a T-SQL equivalent of the [0-9] and [a-z] patterns that will let me pull values from a column that contains punctuation? For example: Create Table #Test ( Value VarChar(10) ) Insert Into ...
6
votes
1answer
84 views

Is my implementation of type/subtype design pattern (for mutually exclusive subclasses) correct?

Introduction In order for this question to be useful for future readers I will use the generic data model to illustrate the problems I face. Our data model consists of two entities, which shall be ...
3
votes
2answers
63 views

Copy one database to another (existing) database on the same server

I have a "Live" database and a "Development" database and occasionally, I want to replace the entire contents of the development database with the live data. Ideally, without taking the live database ...