This tag is for SQL Server 2012, major build version 11.00.xxxx.

learn more… | top users | synonyms (2)

0
votes
1answer
24 views

migrating legacy databases from SQL Server 2000 via SQL Server 2008 to SQL Server 2012: users and logins

I have some legacy SQL Server 2000 databases to migrate to SQL Server 2012. In my dry run, I have done this: I created empty databases of the same names in SQL Server 2008. Then I backed up the SQL ...
0
votes
0answers
21 views

Auditing Specifications for SQL Server 2012 [duplicate]

I work for the State of California. I am working on a project setting up a Data Warehouse sharing data between 3 state agencies. The Data Warehouse will be encrypted. I would like to know what are the ...
1
vote
0answers
27 views

Is It Safe to Remove Local Service Accounts?

I have changed my SQL Server services to use domain service account. The server still has NT Service\MSSQL$INSTANCENAME and NT Service\SQLAgent$INSTANCENAME listed under Security\Logins. They each ...
0
votes
2answers
89 views

Recover data in a SQL Server table without backups [duplicate]

We are having a database without backup to restore. (we didn’t keep backups as client not having enough space in his SQL Server 2012). Unfortunately some data has been lost and we need to recover ...
0
votes
0answers
25 views

Why creating the missing index still shows it in the missing index dmv?

I followed the steps in this blog post to create the missing index and to clear it from the missing index dmv after running a select statement which uses the indicated column in the where clause and ...
0
votes
0answers
30 views

Auditing SQL Server 2012 [closed]

I work for the State of California. I am working on a project setting up a Data Warehouse sharing data between 3 state agencies. The Data Warehouse will be encrypted. I would like to know what are the ...
1
vote
1answer
63 views

How to migrate data of to another sql server

Good day, I have a database running on a virtual machine hosted at our ISP. It is a fairly large database which has about 5GB of data inserted into it every day (network logs). It is partitioned ...
0
votes
2answers
37 views

Will reindexing use the empty space or just grow table?

I have a table which is 170 GB and it has 98 GB of free/empty space. The free space was created by a massive deletion. If I run DBCC REINDEX or ALTER INDEX REBUILD, will the index use that free space ...
2
votes
2answers
79 views

How should I (re) index the table while keeping the transaction log small?

I have a 170 GB table in a database. It has 70% fragmentation on its index (index has multiple columns in it). The last time I tried DBCC REINDEX the transaction log blew up in the hundreds of GBs. ...
0
votes
1answer
21 views

How to order the where clause and join columns in a covering index?

Simple query example: Select t1.A, t1.B, t1.C, t1.CustomerID from table1 t1 join table2 t2 on t1.CustomerID = t2.Id where t1.A = 'x' and t1.B = 'xx' and t1.C = 'xxx' I created a non clustered with ...
0
votes
0answers
68 views

Why does Extended Events server_principal_name return NULL sometimes?

I have an extended event session setup. This is the definition. CREATE EVENT SESSION [Successful-Logins] ON SERVER ADD EVENT sqlserver.connectivity_ring_buffer_recorded ( ACTION ( ...
4
votes
4answers
168 views

Disk Space usage

Is there a way i can get a report or create a custom report in sql server 2012 which can update me with disk space available & actual disk space for listed drives twice or once a day. I need this ...
2
votes
1answer
69 views

If “large value types out of row” table option is enabled, the table size is increased

I have recently learned about sp_tableoption which gives you the ability to store "large value types out of row" for particular table. A colleague of me told me if I enabled it, each field is stored ...
4
votes
1answer
91 views

GZipStream/DeflateStream compressions alternatives

I have created a simple CLR functions for compressing/decompressing NVARCHAR columns: [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)] public static SqlBinary Compress( string ...
4
votes
1answer
167 views

How to optimize: having COUNT(*)>1

Without considering the indexes, Is there a way to re-write this query and get the same result quicker? select fo.OrderNo, fo.OrderItemSeqNo, COUNT(*) from Facts.FactBackOrder fo group by fo.OrderNo, ...
0
votes
1answer
56 views

Distributor latency - slow sp_MSget_repl_commands performance

One of our publishers is subscribed to by three subscribers, each subscriber is hosted on its own physical server. Two of those subscribers are 2008R2 and the third 2012. The 2012 subscriber is ...
1
vote
0answers
54 views

Does partitioning help if you don't query on the partition value?

Say I have an orders.Order table that is partitioned on CreatedWhen (a datetime column set to GetDate() when the row is created). The table has over 10 million rows and the partition is quarterly ...
0
votes
0answers
26 views

Propper way to handle NULL (empty) values for User Definde Table Types passed as params to sproc

My stored procedures accepts table value parameters for querying data. Usually those are custom User Defined Table Types for where clause. Business logic of stored procedures push me to these @tables ...
0
votes
3answers
80 views

Remove duplicates from different columns

I'm trying to remove duplicates from a set, but the duplicates are in different columns, so for example with this table: ColA, ColB, ColC, ColD ---------- 1, 1, 'ABC', 'DEF' ---------- 1, 1, 'DEF', ...
1
vote
0answers
68 views

Multiple IPs registered for listener when using AlwaysOn AGs w/ multi-subnet clustering

We are testing AlwaysOn Availability Groups on a WSFC multi-subnet cluster and using dynamic DNS registration. Our issue is that sometimes the IP for the Availability Group Listener (AGL) in the ...
1
vote
0answers
44 views

How to optimize clustered index rebuild?

I need to rebuild the clustered index of the table that has more then 1.6 millions rows, more then 20 non-clustered indexes and 80 columns in order to reclaim space after two NVARCHAR(MAX) columns ...
2
votes
1answer
40 views

Can I use Backup/Restore to clone a database? Running out of ideas [duplicate]

I need to clone a database in a single instance of SQL Server 2012. I have tried the Copy Database tool in SSMS, but it fails with file access error of which I've spent all day reading around to no ...
0
votes
1answer
80 views

“Uninitialized subscription” on replication monitor when creating a subscription on transactional replication

we have the same question here. However, that does not answer my question below. I'm using SQL Server 2012 and trying to implement transactional replication. I'm using the system stored procedures to ...
1
vote
1answer
108 views

Tempdb files don't exist

I have a strange behaviour on one of my SQL Server's tempdb. It has six tempdb files, according to SSMS and queries, all pointing to S directory. Each of them have ~5gb of initial size. The weird ...
3
votes
1answer
49 views

ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

We have a SQL 2012 AlwaysOn Availibility Group with 2 nodes. We have synchronous commit between the 2 replicas. They are both set to accept ReadOnly connections when acting as secondaries and to ...
1
vote
1answer
115 views

Identity value jumps when restarting SQL Server

We just switched from SQL Server 2008 R2 to SQL Server 2012. I am facing a problem with identity columns: Whenever I restart SQL Server, the seed value for each identity column is increased by 1000 ...
1
vote
1answer
41 views

Difference in number format (comman and dot) on SSMS and SSIS

I have a numeric(5,2) field on my SQL Server database. When I run the query, the number on that field appears with . (dot) separator. However, when I preview the query on SSIS, it appears with ...
0
votes
0answers
39 views

Saving replication latency for monitoring purposes

I have SQL Server 2012 Enterprise edition, transactional replication. many servers, many DBs. This morning I come to the office and been told the latency on some databases was about 30 min behind. I ...
0
votes
1answer
47 views

Registered Server list and groups disappeared from SQL Server 2012 Management Studio

I recently installed SQL Server 2012. Due to some issue, the databases services did not install successfully, but Management Studio did, which was OK to start with, as I usually access remote servers. ...
5
votes
2answers
161 views

Getting ApplicationName (sqlserver.client_app_name) to show up in an xevent

I am trying to examine some T-SQL with extended events like I used to with SQL profiler. I have the following event session: IF EXISTS (SELECT name FROM sys.dm_xe_sessions WHERE Name = 'PySoup ...
1
vote
1answer
83 views

What does a “Buffer cache hit ratio” of 9990 mean?

I got this query from a blog post: SELECT object_name, counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manager%' AND [counter_name] = 'Buffer cache hit ...
1
vote
0answers
58 views

Materialise row-number column

On Microsoft SQL Server 2012, I would like to materialise the result of the row_number function, because I hope it makes queries running faster. I considered to make a view index, or to add a ...
1
vote
0answers
118 views

SQL Agent After installation not able to start

SQL Server 2012 SQL Agent will not start after installation. Checked permissions, on surface configurations, all OK, have tried to run it as another account no Joy. The error i am receiving is the ...
0
votes
0answers
29 views

What is the appropriate value for DBCC CLEANTABLE batch size?

I have removed to NVARCHAR(MAX) columns from a table and what to calculated how exactly the table size was reduced. Usually, I am rebuilding the clustered indexes but the table has more then 1.6 ...
1
vote
0answers
87 views

AlwaysOn Availability Groups go to Resolving status

We have a two-node cluster set up running SQL Server 2012 on Windows Server 2012 R2. The base cluster consists of only those two nodes. The quorum is set up as node + disk majority, with a shared ...
1
vote
0answers
61 views

Renaming a column with sp_rename hangs SQL Server 2012

I was attempting to remove a column from a table within SQL Server 2012 for one of our developers. As a precaution, before dropping the column, we rename them to make sure that everything continues ...
0
votes
0answers
37 views

Scheduled Job Succeeds, Does Nothing; Manual Job Succeeds, Performs Requirements

I have a job in SQL Server Agent (2012 Instance) that deletes rows from a table. When I manually run the job, it succeeds without issue and in reviewing the table, does exactly what I want it to do. ...
8
votes
2answers
222 views

Are “out of the row” fields read when a clustered index is used?

I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row - the data row will have a pointer to another location where the 'large value' is stored. I have the ...
4
votes
2answers
1k views

Join on dm_db_index_physical_stats very slow

I'm running a small query below in a MS Dynamics AX 2012 database and it runs more than 5 minutes which I had to cancel and it is showing PAGEIOLATCH_SH wait type. The database data file is 560GB and ...
10
votes
2answers
183 views

Understanding statistics, execution plans, and 'ascending key problem'

I'm trying to better understand (conceptually) the relationship between statistics, execution plans, stored procedure execution. Am I correct in saying that statistics are only used when creating the ...
2
votes
1answer
102 views

Why can't trigger be dropped from database?

One of the databases I manage is provided by my company's vendor which we restore in order to pull data out for our data warehouse. I'm writing a stored procedure to go through the database users and ...
1
vote
3answers
91 views

Building year to date aggregations using SQL

I need some help with building a monthly aggregation using only SQL. Imagine the following table: TranID DateCode Account Value 1 20140101 1 5 2 20140106 1 -3 3 ...
4
votes
0answers
135 views

What is the cause of this problem with CONVERT()?

Consider the following two statements: PRINT CONVERT(NUMERIC(38, 0), 0x0100000001, 0); PRINT CONVERT(NUMERIC(38, 0), 0x0100010001, 0); Both statements return -1; isn't that incorrect since the ...
2
votes
2answers
220 views

Multiple named Instances with Always On

We have a two-node multi-subnet Windows Server 2012 R2 cluster in two regions. We have three named SQL Server 2012 standalone instances on each node and are in the process of setting up Always On ...
1
vote
1answer
71 views

Best way to document Dwh views [closed]

I works in a company that provide Dwh views as an API for external Customers (We use MS-SQL 2008 & 2012). every once in a while we release new views and updates the existing views. We have ...
1
vote
0answers
29 views

Can I revert to local logging in IIS 7.5 if ODBC fails? [closed]

Environment I have 3 Servers running instances of IIS 7.5 hosting different websites. 1 Server is on Domain A, 2 Servers are on Domain B. I have a one way trust setup from Domain A to Domain B. SQL ...
1
vote
0answers
33 views

How to modify an existing AlwaysOn Availability Group IP

I have an unusual question regarding AlwaysOn Availability Group configuration. I have built out an AOAG cluster on a VM. The build out did not use a Heartbeat network as I had originally requested ...
1
vote
1answer
37 views

Pivoting with NULL Dates

I'm attempting the pivot below: SELECT EmpID as ID, [1] as January, [2] as February, [3] as March, [4] as April, [5] as May, [6] as June, [7] ...
1
vote
1answer
25 views

Remote Access to SQL Server

I have tried everything to be able to connect to my MS SQL Server 2012 remotely. I have read and setup everything like Kyralessa wrote in his answer HERE. Enable TCP/IP done Add 1433 TCP Port ...
0
votes
0answers
18 views

Efficient settings for linked server to SSAS database

We are using OPENQUERY scripts to run MDX from sql-server. We're experiencing efficiency issues when we first run these queries each morning. The linked server objects which link to our SSAS ...