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

learn more… | top users | synonyms (3)

1
vote
1answer
49 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 ...
0
votes
1answer
249 views

Breaking up long running query into batches runs fine for a while and then produces fatal error

I'm using a variation of this post which details using "careful batching" to delete records in batches from a table that contains 38MM records. The script below runs for a while and shows results ...
2
votes
1answer
33 views

How to Load Balance Availability Group

I have 2 Servers under SQL Server Cluster (Server 1, Server 2) 2 Databases configured with Availability Group (Database 1, Database 2) Server 1: Database 1 PRIMARY REPLICA Database 2 PRIMARY ...
1
vote
1answer
69 views

Why am I seeing OPTION (MAXDOP 1) in my Actual query plan even though I've not set it in the stored proc?

SQL Server is set with MAXDOP 0 by default. In the stored procedure I'm testing I keep seeing WITH (MAXDOP 1) in the Actual Execution Plans even though I've not specified any hints other than ...
1
vote
1answer
41 views

Update all tables containing column name

In SQL Server, I can find all tables containing a COLUMN_NAME: SELECT * FROM INFORMATION_SCHEMA.columns WHERE COLUMN_NAME = 'COLUMN_NAME' How can I update all of these columns like: UPDATE <...
0
votes
0answers
26 views

How to minimize locking / maximize update throughput on small table

I have problem on SQL Server. I have a very, very small table which has a very high update frequency. CREATE TABLE [dbo].[counters]( [id] [int] NOT NULL, [val] [bigint] NOT NULL) Table has ...
0
votes
1answer
26 views

SSIS slow performance to insert short rows

(Edit: I changed the post after some more explicative tests) I'm experiencing a lack of performance between a SSIS server (name: Server1) and MSSQL server (name: 7551) when my ETL inserts rows of ...
-1
votes
0answers
25 views

SQL Server database full & differential on split files [on hold]

I read this post with a T-SQL script to create full database and split to three files Normal backup SET STATISTICS IO ON SET STATISTICS TIME ON BACKUP DATABASE DBNAME TO DISK = 'D:\DBNAME.bak' ...
7
votes
0answers
39 views

SSIS proxy/credentials not working from within SQL Agent job step

I have been looking around and found similar questions but nothing specific to what I am having trouble with. Problem: I cannot get the SSIS proxy and SQL credentials configured correctly to ...
0
votes
0answers
28 views

Calculate the current storage of a row [duplicate]

Is there any way to calculate the current storage of a row automatically ? I have calculated the average storage of a row doing: table size / number of rows. But this is the average. I need ...
3
votes
2answers
27k views

How to uninstall SQL server 2012 not showing in control panel's “Programs and features”

I am frustrated to the core. While fixing my Sql Server I've made the situation worse. Here is the problem: initially my master database was corrupted, so the MSSql service was not starting. I tried ...
1
vote
0answers
32 views

SQL Server 2012 database stuck restoring

I'm trying to restore my development database DEV with a backup of my production database PROD. Every time I go through this process, the DEV database gets stuck in a Restoring state. Using the SQL "...
2
votes
1answer
26 views

Insert if something changed other than specific column

I have a table with many columns but my snippet is simplified. I have the trigger shown below but how can I have it so that nothing is inserted if only the created_at column has changed? ALTER ...
3
votes
2answers
117 views

Automating an SQL task on a monthly basis

At the start of each month I have to give a team a copy of the live database which is held in a backup from 1am each morning (D:\Backup) - We then copy this database elsewhere onto a dev server and ...
0
votes
0answers
10 views

How to import one varbinary(max) column with bcp [on hold]

bcp reacts with oversize message on importing a jpg-file. azure does not allow to use bulk operations. how to import a varbinary(max) with bcp?
0
votes
1answer
352 views

Prevent gaps while using Identity/sequence object in sql server

I have a table and I am trying to insert values in it. I want to prevent gaps in the primary key column. For primary column I have tried using Identity / sequence objects. I know that this is ...
0
votes
0answers
19 views

ERROR: Arithmetic overflow error converting float to data type numeric

I unfortunately have some bad data and need to work around it. I have a Parcel column in a spreadsheet with some parcels that have inadvertently been converted to floating point numbers. I posted a ...
1
vote
1answer
32 views

Copy primary keys/clustered indexes from Prod to DEv/Test env

We followed the best practices of creating Primary Keys/Clustered indexes on tables which are actively queried on prod. Now there is a scenario where we need to get the similar above changes done ...
0
votes
0answers
18 views

What is the max number of characters of a string column can be returned by a Stored Procedure in SQL Server 2012?

My SQL Server's version is Microsoft SQL Server 2012 - 11.0.5058.0 (X64). I have a table with a column "Token" which stores string of about 152 characters. I need a procedure which returns all the ...
-1
votes
1answer
24 views
5
votes
5answers
370 views

SQL unused logins

is there a way to find out logins that have not been logged into the SQL Server or accessed the databases in the past 90 days ?
1
vote
1answer
54 views

Contradiction in book related to variable scope in Stored Procedures

I was reading the 70-461 TSQL training book and the part about stored procedures said this: Variables declared in Proc1 and Proc1's parameters are not visible to any of the procedures called by ...
1
vote
1answer
38 views

How to search by text in SSIS packages? [duplicate]

Is there a way to search by text in SQL Server Integration Services packages using T-SQL statement (or other language)? I want to check if certain table is used in any package as I am going to drop ...
7
votes
3answers
949 views

What is the effect of having an opened transaction in MSSQL for too long?

I'm just wondering what happens if you begin a transaction in a DB and forgot to commit or rollback it. Will the server be down? Lets say you left it for 3 days. There are also users who are using it ...
1
vote
1answer
305 views

Multi-server administration - force poll on target fails, server needs to be registered locally without FQDN

I'm experimenting with multi=server administration on my SQL lab. Running SQL 2012 Enterprise SP1 on Windows 2008 R2 on a domain. I've created a domain account for my sql server agent user on each ...
1
vote
1answer
621 views

“Skipping the default startup of database” SQL notification for AlwaysOn Cluster

So I set up our SQL servers to notify us of alerts at severity 17 or higher (as advised by various SQL Server articles). I've done this to each node of a SQL Server Always On Availability Cluster, ...
-3
votes
0answers
14 views

Collect statistics for whole SQL

I am quite new to SQL Server. I would like to collect some SQL statistics for the SQL Server, including Compilation/sec, Recompilations/sec and Batch Requests/sec. However, I have no idea how to ...
1
vote
2answers
278 views

SQL Server hangs during backup on EC2

Once and then our SQL Server will "hang" during its weekly full backup. When this happens, external connections start to timeout. The symptoms are: Connections timeout The sqlservr.exe process sits ...
0
votes
1answer
84 views

Update single table with mutually exclusive threads

I have 2 threads (for example 2 different sql sessions) reading rows of the same table in parallel. They will do some processing and update a column to inform the row has been processed. Thread id:...
6
votes
1answer
144 views

SQL Server Agent initiating self-termination

For the past month, one of my servers is facing issues with SQL Server Agent. It stops automatically with below error, which happens randomly: Sql Agent Output file [097] Memory leak detected [...
0
votes
2answers
25 views

KILLED/ROLLBACK query doesn't stop running

I've tried to kill a query on ms sql server management studio. Now it has the status killed/rollback. When I use: kill 74 with statusonly it doesn't make any progress. It is still at 0%. How can I ...
1
vote
2answers
774 views

After Restore Log_reuse_wait_desc of Replication

I'm wondering if anybody has ran into this issue before and has any suggestions on what's causing it/how to fix it. We are restoring a database into lower Dev environments and after the restore the ...
1
vote
0answers
28 views

SQL Server 2012 Performance issue while data load from Biz Talk Server

All of sudden BizTalk load has stated failing in SQL Server which was running for years without any issues. BizTalk will compile an insert SQL statement from flat file & insert data into SQL ...
2
votes
1answer
35 views

Grant sysadmin permissions to 'NT AUTHORITY\SYSTEM'

As you all know, Microsoft SQL Server version 2012 and above are not enabled any more sysadmin role flag to Local System (NT AUTHORITY\SYSTEM) account. In my case it's becomes a problem: I have a WiX ...
1
vote
0answers
27 views

SQL Server Maintenance Plan Help

SQL Server Maintenance Plan Help I'm using SQL Server Maintenance and found a way to selectively turn on and off various features of the plan via it's associated job shown in Figure A. This can be ...
7
votes
2answers
230 views

Microsoft SQL Server 2012 Mirrors Go Into Disconnected

I have some Microsoft SQL server 2012 mirrors, without a witness in asynchronous mode, that are sporadically going into disconnected. I am running SQL Server 2012 11.0.5058. Looking at the logs on ...
1
vote
1answer
17 views

Will Installation of Service Pack 3 on SQL Server 2012 Web Edition (existing production) require database restore activity?

We are currently running our Database Server on a RTM version: Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Standard Edition (...
-5
votes
0answers
51 views

Is it possible to take MS SQL Server 2012 backup in particular date range? [closed]

Is it possible to take MS SQL Server 2012 backup in particular date range?
0
votes
0answers
13 views

Restoring a backup of 1 database to another puts both databases into Restoring mode

We have a 2 databases- Staging Test At times, we refresh out Test environment with a copy of Staging. We do this by taking a full backup of the Staging database and restoring it over the Test ...
4
votes
2answers
2k views

SQL Agent - PowerShell step “syntax error”

I have the following code setup in SQL Agent Job step that is not running. The message received is simply: Unable to start execution of step 1 (reason: line(46): Syntax error). The step failed. ...
0
votes
0answers
26 views

SQL Server - Copy Database fails due to duplicate user

In the "model" system database, I have a user (UserA). I have another database with UserA in its Security > Users that I'd like to make a copy of using the Tasks > Copy Database. When I try this I get ...
0
votes
2answers
72 views

Convert Function

I am working on converting a few dates to make them easier to read and when I call upon the convert function to do so, they simply don't convert. When I run the queries they look the same. The code is ...
1
vote
1answer
58 views

SQL Server TempDB size issue

SQL Server reported that our tempdb is out of space. So I had a quick google search and found this on technet https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx Determining the ...
14
votes
1answer
498 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....
12
votes
1answer
256 views

Clustered index not used in a delete statement

I have a SQL Server table defined as follow CREATE TABLE [dbo].[Production_Detail] ( [Id] [bigint] NOT NULL DEFAULT (NEXT VALUE FOR [dbo].[Production_Detail_Seq]), [Meta_Data_ID] INT NOT NULL ...
5
votes
2answers
5k views

SQL Server 2012 is putting [brackets] around Table and Column Names

Is there anyway to turn that off? I'm unable to find anything in options, and they're really a distraction.
7
votes
1answer
971 views

Using Two LEFT JOIN or use AND with single LEFT JOIN?

Using two LEFT JOINs i.e. SELECT <some columns> FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON <condition1> LEFT JOIN Table2 AS t3 ON <condition2> is this same as using AND in ...
1
vote
2answers
64 views

Applying single quotes across multiple lines using ssms code snippet

further to Applying Quotes Across Multiple Lines Is it possible to create a "surround with" ssms code snippet to turn a list of values across multiple lines into an IN clause, so I could: select the ...
3
votes
0answers
115 views

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 ...
5
votes
3answers
8k views

SQL Server 2012 Can't log in with machine account

We're migrating an IIS/SQL Server site from 2008 to 2012, and I'm having a problem with authentication. We use Windows Authentication to authenticate as DOMAIN\COMPUTER$, but this isn't working, ...