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

learn more… | top users | synonyms (3)

1
vote
1answer
31 views
0
votes
1answer
29 views

sql agent job timeout auto restart

we have a DWH load job that sometimes hangs. We have not been able to determine why this is happening it seems to be random and network/hardware related (which we have no control over). We are ...
1
vote
1answer
31 views

Difference between Generate Script option at database level and backup and restore option

When doing that generate scripts on the database level what difference does it do from doing a backup and restore on the database? It seems to me that they both provide the same purpose. What would be ...
2
votes
2answers
32 views

Trying to drop database only if it does not exist by using the db_id function fails

When the database foo is not yet created and I I run this IF db_id(N'foo') IS NOT NULL BEGIN ALTER DATABASE [foo] SET OFFLINE GO DROP DATABASE [foo] GO END I get a message Cannot drop ...
-2
votes
1answer
108 views

Error 35338: Clustered columnstore index is not supported [closed]

On SQL Server 2012, I want to index a table with a columnstore index. But an error message apears: Msg 35338, Level 16, State 1, Line 1 Clustered columnstore index is not supported. My code ...
2
votes
1answer
59 views

How to automatically fix Orphaned Users with PowerShell and SQLCMD

I'm trying to properly capture output from SQLCMD in SQL Server 2012. Here is the PS script: $dbname = "MyDB" $InstanceTarget = "localhost\SQLEXPRESS" $FixOrphansSQL=@" WITH login_CTE (name, type, ...
7
votes
4answers
464 views

How to get response from stored procedure before it finishes?

I need to return partial result (as simple select) from a Stored procedure before it is finished. Is it possible to do that? If yes, how to do that? If not, any workaround? EDIT: I have several ...
0
votes
1answer
16 views

Problem with Processing Dimensions with Multiple Facts

I have a cube built in SSAS that uses 3 facts and 8 dimensions. Some of the dimensions are shared across 3 facts. When I process one of the dimension, it tries to process the underlying measure ...
2
votes
1answer
65 views

Hold exclusive lock on a whole DB

As I explained in Set a filegroup to readonly, I wanna set some tables readonly, to have better performance. These tables are only written by an ETL that runs daily, then they are only read. ...
2
votes
1answer
48 views

Set a filegroup to readonly

I have a set of dimensions which are updated only during specific times. I have moved these tables to a specific filegroup named Dimensions. I wanna keep this filegroup as readonly. When ETL runs, it ...
9
votes
1answer
96 views

Why is the MSDB database TRUSTWORTHY?

The TRUSTWORTHY setting can be rather dangerous if you aren't careful and except for specific circumstances the recommendation is to keep it turned off. However by default the MSDB database has ...
8
votes
3answers
3k views

Keep getting message: Failed allocate pages: FAIL_PAGE_ALLOCATION 540

I'd appreciate some help with the following, I've done some Googling, but haven't managed to resolve this one. I keep receiving a message in the SQL Log 'Failed allocate pages: FAIL_PAGE_ALLOCATION ...
7
votes
2answers
279 views

How to configure SSRS (SQL Server 2012) to run on a different instance, but same server as SharePoint

I have an instance of SQL Server 2012 running on Windows 2008R2 along side another instance of SQL Server 2012 that is running SharePoint (I think it is SharePoint 2010). When I try to access the ...
0
votes
1answer
24 views

SQL Query for searching table 'C' records only if records from table 'A' has specific values

I Have Three Tables, TBL-A, TBL-B, TBL-C, TBL-D TBL A has records for userstatus RECID USRID USERSTAT USRNAME 1 1 1 JOHN 2 5 1 HARRY 3 ...
3
votes
1answer
65 views

Can this SQL Query be improved

Related to one of my previous questions: How to select a subset from a query result I now have this Query: CREATE TABLE #JourneyLogs ( JourneyStartId INT, JourneyEndId INT, ...
3
votes
2answers
46 views

Is there a way to validate XML using variable as schema?

Is there a way to declare XSD schema as variable inside a construct: DECLARE @xml XML(Books)? I need to make quick validation of XML using XSD schema which is declared as variable. I suppose there is ...
4
votes
1answer
107 views

SQL 2012 CLR error: “The context transaction which was active before entering …”?

I am experience strange error with SQL 2012 and CLR: The context transaction which was active before entering user defined routine, trigger or aggregate "My routine" has been ended inside of ...
2
votes
2answers
62 views

Why am I able to query for more than 1024 columns but I can not make a view of the same query?

My boss is requesting a view with greater than 1024 columns where in he will only select a few columns he wants when he queries the view so that the actual returned columns are less than 1024. Why am ...
1
vote
1answer
56 views

Can you provide rights to a snapshot without providing rights to the database?

Is it possible to give a user or group rights to a snapshot without giving them rights to the database if the database and snapshot both reside on the same instance?
2
votes
1answer
179 views

SQL Server AG databases went into 'Synchronizing/In recovery' after upgrading to 2014

We have 3 replica AG setup: 2 replicas are in sync/automatic failover the other(DR Server, different subnet) in asynchronous/manual mode All these replicas were on SQL Server 2012. Recently we ...
2
votes
1answer
168 views

Query Time is doubled because of a column in select

I have a Stored Procedure which is calculating some data and inserting it into a Temp Table. Then I am selecting the Data from the Temp Table. Select AssetId = iAssetId, ..... SpeedKM = ...
1
vote
0answers
79 views

how to minimize the effect of updating statistics on the plan cache?

The issue I am trying to address: After updating statistics there are slow downs in running many of our processes here. then following the scary dba on this link below: ...
4
votes
1answer
117 views

transaction across multiple databases - what is the overhead?

I run the following update, which is one single transaction across 2 databases. Please note I have not committed the transaction. --================================================================ ...
1
vote
1answer
38 views

putting together contents of XML column - from all rows of a table - how to get it done without a “while”?

I have a table called sizeguide with the definition you can see below. I am interested in the SizeGuideXML column, which is type XML. I had a mission the objective was, for each SizeGuideDesc, get ...
0
votes
1answer
138 views

how to calculate the next time a job will run - by each schedule

I have a script that shows me when it is the next time a job(s) will run, and other things, the dates are formatted, etc. the script is here, and it works fine: USE msdb Go set transaction ...
-1
votes
1answer
41 views

Data warehouse backup strategy

I have a data warehouse development environment. Just as the production environment. it loads data every night from test OLTP databases. I want a backup every time the load process finishes. What ...
-1
votes
1answer
61 views

Filter first then join or join first then filter?

Consider 5 tables with more than 1 billion records each. A query needs to join them. And I know that less than 10% of their records will be require. Say they all have a Date dimension, and only data ...
0
votes
2answers
57 views

Linked Server Error on SQL Agent Job

I get the below error from a SQL Server Agent Job. However, if I run the query in the job manually it works fine. Why is this? Is it just SQL Agent that requires MSDTC? I don't believe it is ...
7
votes
1answer
186 views

Does SQL Server 2012/2014 Standard support Hot Add memory on a Hyper-V guest?

In this support article: https://support.microsoft.com/en-us/kb/956893 The answer to Q7 says: Hyper-V Dynamic Memory is fully supported with SQL Server. Only SQL Server versions and editions ...
1
vote
1answer
20 views

Checks to be done, before/after changing the windows login password? [duplicate]

Here we have a scenario where a Service account , added under security as sysadmins for windows login, going to have its password change as per windows policy. Since windows team, server admins are ...
2
votes
2answers
104 views

Using Powershell Variables in a File-Stored Query with SQL Server

I'm attempting to run some SQL through Powershell, using a wrapper function in PS to run the SQL command. As well, my SQL is in its own independent file and not hard-coded inline to the Powershell ...
0
votes
0answers
34 views

SP Behaves Differently on Production In Case Of Joins

I have this stored procedure in my database SELECT up.[Id] AS UserId, up.[DisplayName], up.[IsPublic], details.[Notes] AS LastNotes, details.[OriginDt] AS LastDate, ...
2
votes
3answers
67 views

How can I configure maximum number of concurrent connections WITHOUT SSMS?

I'm having an issue in a production environment and I was trying to simulate it in my local SQL Server Express instance. I used SSMS to change the "maximum number of concurrent connections" from "0" ...
1
vote
1answer
37 views

SQL Server 2012 AlwaysOn Availability Group active/passive

I have one Primary and one secondary replica on WSFC and AAG configuration. Both nodes are physical machines and sync with automatic fail over. The secondary replica is readable and we run ...
1
vote
0answers
57 views

A significant part of SQL Server process memory has been paged out

Around 7:30pm every evening we receive the following message and the server experiences an outage of 2 minutes. A significant part of sql server process memory has been paged out. This may result ...
0
votes
2answers
42 views

Notify when database is accessed in any manner

I'm using SQL Server 2012. Is there a way to receive a notification (email/log etc..) if a database or table in a database is touched in anyway (e.g. CRUD via Stored Procs or tables opened via ...
8
votes
2answers
153 views

Backing up multiple databases to the same point in time

Usually when we start the backup we do not allow changes to commit or database will be not be accessible. I mean database will be in single user mode but I want to start the backup and release the db ...
1
vote
1answer
59 views

How to find statement executed by sp_executesql in [sys].[dm_exec_query_stats]?

I have a function which is generating dynamic T-SQL statement. The generated code is executed then in a stored procedure. I want to log some statistics about how long the execution of the generated ...
3
votes
1answer
32 views

Separate differential backup from one restored baseline

I work on a team that customises software, part of this customisation is stored as records in a database, part is customisations to the schema itself and there are often some specific records in the ...
0
votes
1answer
27 views

Primary server 'XYZ' can not write to shared drive during availability group configuration

I am in a process of adding databases to availability group. In one of the steps it was asking "data synchronization prefferences" as shown below I have put the mapped drive location path, which ...
5
votes
1answer
30 views

Statistics on filestream column

When I try to create a statistics on a filestream column, I get the following error message: Column 'MyColumn' in table 'MyTable' is of a type that is invalid for use as a key column in an index ...
7
votes
2answers
3k views

Should I check for existence of temp tables in Stored Procedures?

Is there any edge case in which it would be recommended to explicitly check for ,drop and create temp tables at the start of a Stored Procedure instead of just creating them? Similarly, is there a ...
3
votes
4answers
9k views

Detecting changes in a SQL Server table

In my application, with a DB running on SQL Server 2012, I've got a job (scheduled task) that periodically executes an expensive query and writes the results to a table that can later be queried by ...
0
votes
1answer
24 views

I do not see SQL Server instance name under roles section of Fail over cluster manager screen! How to setup?

I have created 2 Virtual machines in azure and configured Windows Cluster using fail over feature. I have enabled the "AlwaysOn" feature in SQL Server Configuration Manager. But I do not anything ...
0
votes
1answer
11 views

Catalog of LinkedServer2 from Server1 shows databases on Server1

I'm on Server1 and set up a linked server to LinkedServer2, but the catalog shows the databases on Server1! I am sure I've done something bone-headed or am overlooking something horrendously obvious, ...
0
votes
1answer
13 views

Select records that fit criteria in another record

I have a procedure that I want to return only when a second procedure was selected. Id Name Procedure 1 Jim Smith Procedure1 2 Jim Smith Procedure2 I would like the first record ...
0
votes
2answers
23 views

Source of network_name for distributor

So, I have a really weird case. Was doing a migration from sql server 2005 to 2012. Built the server, and at the last minute we found that the server was called abc/xyz. We just wanted abc, so the DBA ...
4
votes
1answer
56 views

How to create a 'keywords' table from a column's values

I have a table with 8 million+ rows in it. It stores jobs and their characteristics. There is a column in it that holds job titles. I am planning to create a keyword table based on the job titles from ...
1
vote
1answer
81 views

Restore accidentally truncated table

I truncated the wrong table, but I have a backup made an hour before the incident. I am trying to restore but at 30% I get the error: Restore of database 'msdb1740' failed. ...
0
votes
1answer
190 views

SQL Server Express 2012 Repair

I have a copy of SQL server Express 2012 database which may be corrupt. When I tried to run a repair on it I was asked for the original installation media. The Options were for an MSI or ISO image. ...