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

learn more… | top users | synonyms (3)

3
votes
2answers
210 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 ...
3
votes
1answer
40 views

Replication for Mirrored server

I have 3 SQL servers running SQL Server 2012 Standard Edition, called A , B and C. Server B is a mirror of server A. There is transactional replication between server A and Server C. How can I ...
3
votes
2answers
76 views

Strange Database Mirroring Problem

I am noticing a strange problem with DB mirroring on SQL Server 2012 (standard edition). The mirroring is set up, and successfully established, and working. There is no witness. Once principal is ...
0
votes
1answer
32 views

Accessing a Linked server [closed]

I am fairly new to the whole DBA scene. With that said, i was able to get an ODBC connection established within Microsoft SQL Server 2012 to be able to access a database for a library. Now what I am ...
0
votes
0answers
24 views

How do I keep all roles, users and permission on a database being dropped then restored with a different database with different permissions [duplicate]

The DBA at my job every week drops my test database and restores it with a copy of the live environment. The permissions on the live database are different then the test database(and need to be). ...
3
votes
1answer
131 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 ...
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
42 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 ...
17
votes
5answers
11k views

SQL Server Agent Jobs and Availability Groups

I'm looking for best practice in dealing with scheduled SQL Server Agent jobs in SQL Server 2012 availability groups. Maybe I missed something, however at the current state I feel that SQL Server ...
0
votes
0answers
35 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 ...
0
votes
1answer
45 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
2answers
74 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
91 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
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 ...
6
votes
1answer
186 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
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, ...
2
votes
1answer
137 views

What is the role of eager writer?

What is the role of eager writer? How this process works internally in SQL server? What is the major difference between eager writer and lazy writer/checkpoint? My understanding here is, that both ...
3
votes
3answers
292 views

9 digit zip codes are reporting LEN of 12

I am attempting to resolve some difficulties in getting zip codes to display properly. The original spreadsheet has zip codes of mixed 5 and 9 digit formats. After the import process, these 9 digit ...
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 ...
1
vote
1answer
59 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 ...
3
votes
2answers
182 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 ...
4
votes
1answer
44 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 ...
18
votes
1answer
124 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 ...
0
votes
0answers
65 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 ...
2
votes
1answer
1k views

SQLNCLI10.x vs 11.x when connecting SSIS 2008R2 to a SQL Server 2012 database

Starting from the question and working backwards. My question: is it OK to use SQLNCLI10.1 as a provider for SSIS 2008 R2 to connect to SQL Server 2012 database(s)? I have a SSIS box running SQL ...
2
votes
0answers
53 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, ...
3
votes
1answer
56 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 ...
1
vote
1answer
42 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
184 views

How do I move or redistribute FILESTREAM files between containers?

We have a FILESTREAM container with a few million files, and we believe it's the cause of our performance issues we're experiencing (abundant timeouts). According to this blog about FILESTREAM best ...
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 @...
2
votes
2answers
640 views

Registered Server window does not show Database Engine or Local Server Groups

I am running SQL Server Management Studio 2012 on a Windows 7 machine. After a failed attempt to import my registred servers from SSMS 2008, the icons for Database Engine and Local Server Groups are ...
5
votes
2answers
82 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 ...
5
votes
2answers
122 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 ...
0
votes
1answer
73 views

Extracting data from SQL Server linked server

I am using MS SQL Server 2012, and I am trying to extract data from a Linked Server database. In SQL Server management studio, I can see the linked server. I can browse the database on the server. I ...
3
votes
1answer
105 views

MEMORYCLERK_XE Memory Usage Will not Clear

We've been having an issue where memory usage from Extended Events grows to over 1 GB and then will not clear - even when we stop all XEvent session on the instance. This hasn't been an issue yet on ...
0
votes
2answers
104 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 ...
0
votes
1answer
57 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 ...
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 ...
1
vote
1answer
41 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(...
1
vote
1answer
33 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

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
37 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
0answers
47 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
1answer
42 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 ...
5
votes
1answer
454 views

SQL Server passing variable to a string in a stored proc

I am trying to concatenate a custom string with a variable that is an int. Investigation is pending for ['+ @investigationidout +']. When I hover the mouse pointer over the first + sign it says: ...
2
votes
1answer
62 views

Bulk Insert into view and find missing values

I have 2 tables, Categories, and SubCategories: CREATE TABLE [dbo].[tbCategories] ( [CategoryID] [int] IDENTITY(1,1) NOT NULL, [CategoryName] [varchar](250) NULL, ) CREATE TABLE [dbo].[...
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
42 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
79 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
50 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 ...