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

learn more… | top users | synonyms (3)

5
votes
0answers
48 views

SQL Server Agent initiating self-termination

From past 1 month one of my server is facing issues with SQL Server Agent. It stops automatically with below error and happens randomly. Sql Agent Output file [097] Memory leak detected [204 ...
4
votes
1answer
254 views

CPU Schedulers Offline

I logged onto a new clients systems and ran sp_blitz to see what's shaking. It reports back that "CPU Schedulers Offline" which is a new one for me. Some CPU cores are not accessible to SQL ...
-1
votes
1answer
32 views

AlwaysOn Multi-Subnet Setup

Without using SAN-to-SAN replication, how does data synchronization occur across subnets in a multi-subnet AlwaysOn (AVG) configuration? I want to setup a separate VLAN (replica lan) to synchronize ...
2
votes
1answer
52 views

Is there a query to see which stored procedures are using a specific symmetric key?

Is there a query to see which stored procedures are using a specific symmetric key? Or a built in feature of SQL to see this information. The stored procedures are using: OPEN SYMMETRIC KEY !@#$%^ ...
-2
votes
0answers
42 views

Data file NDF Vs MDF and performance overhead

We have database with multiple data files (*.ndf) and one of the data files we made to have static data tables i.e. not a transnational data (LibraryPrimary.ndf). Here is the load test result with ...
2
votes
2answers
56 views

Patching strategy when Windows and SQL Server patches not applied for over 3 years

The company I'm at hasn't patched their production servers for a while, neither SQL 2012 or Windows 2012. When I say "for a while" I mean, well, actually "never" - no patches or updates since the ...
1
vote
0answers
31 views

Backup creating fork_point

I have a powershell script that is restoring from production into a test availability group database. It copies the full and t-log backups from production to test, takes the target db out of the ...
4
votes
1answer
86 views

ORDER BY and comparison of mixed strings of letters and numbers

We need to do some reporting on values that are usually mixed strings of numbers and letters that need to be sorted 'naturally'. Things like, e.g. "P7B18" or "P12B3". @The strings will mostly be ...
0
votes
1answer
36 views

Unable to connect SQL Server 2012

I am facing an issue in connecting to SQL Server 2012 with a .net application. SQL Server 2012 database and code are deployed on the same server. Connection string: <add key="umbracoDbDSN" ...
4
votes
1answer
44 views

How to read multiple record from xml to insert in table in SQL?

DECLARE @tbl_XMLResult table ( Status varchar(50), Address varchar(100), ListPrice varchar(25), SoldPrice varchar(25), YearBuilt varchar(25), SF varchar(25), Acres ...
0
votes
1answer
31 views

How to read and process xml data from stored procedure in sql server?

I am calling a stored procedure like this: usp_ReportResults @query = '759,905,1048,170,725,80129', @ReportName = 'GenRepot' where usp_ReportResult is defined as CREATE ...
6
votes
1answer
571 views

Why does database recovery take so long?

On my SQL Server 2012 x64 Std instance I had a large transaction fail due to the transaction log filling up yesterday, it couldn't be rolled back and SQL Server has restarted the specific DB to ...
0
votes
1answer
38 views

SSDT Not Dropping all Foreign Keys before Drop Table

SSDT V11.1.50730.0 Using Schema compare to make changes from my development environment to a test environment (a copy of Production). Part of the changes is the addition of columns to an existing ...
3
votes
0answers
35 views

High paging and memory consumption in SQL Server [duplicate]

This is my first question about SQL Server, I'm new to SQL Server and I don't understand few things, so hope you can help me. We got a SQL Server 2008 with 2 instances and also with Analysys Services ...
2
votes
0answers
12 views

Installing SQL Server 2012 Standard on a machine with more than 64GB RAM

I’m in the process of doing the specification for a new server that will host SQL Server 2012 including SSIS, SSRS and SSAS I’m aware SQL Server 2012 Standard has a limit of 64GB. However from what ...
1
vote
1answer
58 views

MS SQL Server 2012 Express - install service pack 3

Is it possible to install service pack 3 to an existing instance of MS SQL Server 2012 Express? The only install files I found are: MS SQL Server 2012 and Service Pack 3 for MS SQL Server 2012 not ...
2
votes
1answer
37 views

SQLCLR assembly throws error when multiple queries run simultaneously

I have been using SimMetrics to implement the QGramsDistance search in SQL Server. The SimMetrics is used as a SQL CLR assembly. The search results are promising and everything was well, until of ...
6
votes
2answers
81 views

list of batch aborting errors in SQL server

In SQL Server, if XACT_ABORT is off then some errors will terminate the current statement (for example supplying the incorrect number of parameters to a stored procedure that takes some parameters) ...
1
vote
1answer
58 views

Why SQL Server Express 2012 take automatically backup in “Virtual Device” without virtual device configuration?

I have noticed that after migration of SQL Server 2012 Enterprise to SQL Server 2012 Express, every day it shows "spurious" virtual backup after taking manual backup of database. When I execute this ...
3
votes
1answer
60 views

Is replication a suitable method to move a SQL Server database to new hardware?

I need to move a live database with about 100 GB of data to a new machine. As I understand the easiest way to do that would be to take it offline, move the files and bring it back online. But: The ...
0
votes
0answers
11 views

Optimizing the UPDATE For MSdistribution_history

One of the major headaches in my replication is the following MS procedure (over 55% usage), updating the distribution history. It currently has one index on columns agent_id, timestamp, runstatus, ...
1
vote
2answers
55 views

Ordering of a dimension in sql server datawarehouse

Built and deployed a simple cube with a date dimension. The dimension has things like day of month. How do i sort it? it always shows in the reports as 1, 10, 11, 12, ... 19, 2, 20, 21 Design. ...
3
votes
1answer
67 views

Kick a single user out of the database

I've seen multiple answers about kicking all of the other users but is there a way to stop the process id and kick just one user out of the database. Eg. user A B and C are connected to the database ...
-1
votes
2answers
37 views

because of Date format im not able to get few records [closed]

For example declare @string varchar(30) = 'dec. 24, 2015' select substring(@string, 10, 4) declare @output date select @output = case when substring(@string, 1, 3) = 'Dec' ...
1
vote
1answer
39 views

SQL Server Management Studio 2012 Application Cannot Start

I need someone's help. I'm trying to install SQL Server Management Studio on my Laptop Operating System: Windows 7 Service Pack 1 But I got an error message saying: The application cannot start. ...
0
votes
1answer
41 views

SQL Agent Proxy - writing to logs is only allowed to jobs that are owned by sysadmin

I have a Windows 2012 RS and SQL Server 2012 Enterprise Edition. I have SQL Server maintenance jobs from ola.hallengren.com and the owner of the jobs were sa. The security team decided to change that ...
3
votes
2answers
239 views

Check if a user exists in a SQL Server database

I'm working with SQL Server 2012. I want to check if a user exists before adding it to a database. This is what I have tested: USE [MyDatabase] GO IF NOT EXISTS (SELECT name FROM ...
4
votes
3answers
56 views

Moving SQL Server Agent Job Categories and Schedules

I want to copy a set of Job Categories and Schedules between MS SQL Servers (2012/2014). select * FROM msdb.dbo.sysschedules Returns all schedules. select * FROM msdb.dbo.syscategories WHERE ...
3
votes
1answer
87 views

Is it possible to extract the multiple variable parts of data into different columns without a procedure?

I'm currently trying to do an analsis of a table containing a couple thousand rows of data. I have managed to restrict the data to what I really need, but I'm not sure if I can split it up further ...
-1
votes
1answer
23 views

SQL Server view query generating too much temp

We have been noticing when a month end job which basically queries a view is generating too much temp data. The database size is 50GB (in simple mode) and the view uses two tables which are not more ...
6
votes
1answer
97 views

How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

I asked a question about validation of XML using XSD schema inside SQL Server 2012 (see link). I understand (as I suspected) that I need to use CLR Function. The function will get XSD schema text and ...
0
votes
1answer
75 views

Instance name is null - SQL Server [closed]

I recently checked this in my SQL server instances: SQL Server 2012 EE: -- get current SQL Server name\instance name SELECT @@SERVERNAME -- get current machine name and instance name SELECT ...
3
votes
1answer
28 views

MAXDOP query for SQL Server 2012

I have a 8 core VM machine running SQL Server 2012. The current setting of MAXDOP is set as 5. There is recommendation from my colleagues to change the MAXDOP to 5 for better performance as there has ...
1
vote
0answers
37 views

Errors while accessing Excel file using openrowset

I'm seeing the below error message while running the following T-SQL statement. select * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=123.xls', 'SELECT * FROM [Sheet1$]') ...
1
vote
1answer
25 views

Why is Log File Auto Grow sometimes performed by my application user and sometimes by sa?

Many database tasks (including Data File Auto Grow, Log File Auto Shrink, and Data File Auto Shrink) are completed only by the sa user, but Log File Auto Grow seems to be associated with both sa and ...
1
vote
0answers
37 views

Collecting DB Used Space (MB) from SCOM on an Always-On DB

My client has asked for database sizing for capacity planning. I am trying to use System Center Operations Manager (SCOM), however when I get to the Always-On Cluster, it doesn't seem to display the ...
3
votes
1answer
165 views

Alternative for WHERE clause in SQL query

We have SQL Server 2012 Enterprise Edition running following query via a stored procedure: declare @TopX int = 1000 declare @stores Table (Store varchar(5), LastDate datetime, LastId int, RangeEnd ...
5
votes
2answers
80 views

Can SQL Server (2012-2014) be configured to run “on-demand?”

I am very, very new to SQL, and am having difficulty configuring the server appropriately. All I have to work with for now is a small copy of the database schema of the company I work for, with ...
0
votes
2answers
53 views

SQL temporary variable query

Not sure what am I doing wrong in this query, there seem to be a syntax error in the subquery AND typeId = (SELECT @id = typeId FROM EmpType, near @id =. I'm looking at getting the typeid in a ...
2
votes
1answer
50 views

What Host Name is my application connecting to SQL Server on

We have this 1 node broken cluster that I have been given the joy of fixing. Wont go into the reasons why its only got one node but I am planning on migrating this off onto a newly built cluster. So ...
3
votes
1answer
63 views

Starting SSIS Package through SSIS Catalog

I've developed an SSIS package to load files but it is behaving very strangely. I can kick off the package running start_execution on the SSISDB only when I log directly onto the server. Kicking it ...
16
votes
2answers
713 views

Max number of user connections

In SQL Server 2012 Standard edition, I know that the max number of user connections is 32,767. What should I do as a DBA if I am heading towards this number? Currently there are 30,000 user ...
7
votes
1answer
86 views

Log Shipping SQL Server 2012

I'm a developer at a small shop that doesn't have a DBA and i'm trying to get log shipping with sql server 2012 working. I'm trying to off load reporting from the transaction system to a new data ...
6
votes
1answer
134 views

How to Start Express SQL Server instance in Single user mode?

I want to start my SQL Server Express instance in single user mode to do some troubleshooting such as restoring the Master database. I am unable to find the "Startup Parameter" options in SQL Server ...
1
vote
1answer
52 views

update statements shows update count but not updating the table

I am facing a strange issue which I have never faced before. I have a update query like below Update A Set A.Col10 = E.Col10, A.Col11 = E.Col11 FROM TableA A INNER JOIN TableB B on A.Col1 = B.Col1 ...
6
votes
2answers
88 views

How to collect NTFS file properties and insert into SQL Server table

I have to provide reports on file system usage. I'm collecting statistics on file server usage down to individual file level so we can see who is using what files/folders, how much storage they're ...
3
votes
3answers
204 views

Selecting only one duplicate

I have a database of purchase orders and stockroom checkouts, and it seems that the stockroom checkouts tends to get duplicates inserted (we source this data from an older system nightly). What would ...
4
votes
1answer
86 views

Validate XML using XSD inside TSQL function in SQL Server 2012?

I need to validate XML using XSD inside TSQL. To make this task semi automated I need to do this inside TSQL function. To make validation I have to set value to XML and if it is not valid I will ...
-1
votes
2answers
61 views

Database BackupSize has Changed after migration from SQL Server 2012 Exterprise to SQL Server 2012 Express [closed]

I would like to say that after migration of MS SQL server 2012 Enterprise to MS SQL Server 2012 Express. I have seen there is some subtle diffence in DB backupsize as well as in "LSN" of all ...
4
votes
1answer
51 views

SQL Server Edition for MS 70-463

I just started the MS 70-463 Implementation of warehouse, on the video course the tutor is directing towards SQL Server 2012 Evaluation Edition, but I have already have a SQL Server 2012 Express. Can ...