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

learn more… | top users | synonyms (2)

1
vote
1answer
27 views

SQL Server 2012 stored proc runs very slow sometimes

I have a stored proc with a single large query which takes an ID as a parameter and returns some counts. It has quite a few inner joins and left outer joins but they are all on indexes. The sp runs ...
1
vote
0answers
12 views

finding out the Backup set physical name

I created a maintenance plan to back up two user databases and system databases over a month ago. I queried the msdb database to view the size of the backups using the following query. SELECT ...
1
vote
1answer
11 views

Syncing Uncontained Objects with AlwaysOn Availability Groups

In setting up our HA environment (AlwaysOn AG), I came across the add-in ssms_2012_ag_addin_setup.exe that I got from SQLSkills. ...
-1
votes
1answer
14 views

can exec permissions be granted to a role in SQL Server 2008 / 2012

Can exec permissions (e.g. on a SP) be granted to a role in SQL Server 2008, so that all role members then have the permission; and if the 2008 database were backed up and restored into SQL Server ...
1
vote
1answer
46 views

Updating large table (~6.5 million rows) & indexes

I'm trying to update a table with about 6.5 million rows in a reasonable amount of time, but I'm having some issues. We're adding a new column to an existing table and then setting a value for all ...
2
votes
3answers
264 views

Is inserting multiple queries takes same time as that of single query?

I have a requirement where I have to insert a lots of rows.For inserting we use like this insert into emp(name,age) values('abc',12); This will insert only one row.For inserting multiple rows we ...
-1
votes
1answer
29 views

Debugging Remote Connection Failure of SQL Server 2012 [on hold]

I am running SQL server 2012 web edition on Windows Server 2008 R2. I have followed all these steps http://blog.citrix24.com/configure-sql-express-to-accept-remote-connections/. But still unable to ...
1
vote
2answers
23 views

SSMS (2012) limit # of characters on editor window line

Is there a way to enforce a hard limit on the number of characters allowed on a line in the SSMS editor window? I have searched, but am unable to find an option to set this. I'd like to force the ...
0
votes
2answers
39 views

Are some these indexes redundant?

Let's say I have a table of 4 columns: A, B, C, D. A primary key is on A, B, C. Index1: is A, D, B, C in that order Index2: A, B, C, D in that order and the primary key index: A, B, C There are ...
0
votes
1answer
32 views

Server 2012 Linked Server From An Instance

I have 3 SQL Server 2012 Instances, 2 on SiteA on the same server SiteAServer. I have 1 on SiteB on server SiteBServer. SiteADefaultInstance, SiteASecondInstance SiteBDefaultInstance ...
1
vote
0answers
18 views

SSMS login fail to Linked Server

i have one server with SQL server 2012 standard on cloud environment and one linked server with sql express on remote location connected with VPN , im using transactional replication every time when ...
1
vote
0answers
21 views

Update SQL Server .net version

I have used this article to detect the latest install .net version and here is the result: Its .net 4.0 and I want to know if the SQL Server is using the latest installed version? I want to ...
0
votes
2answers
27 views

Linked Servers security context and transaction logging

I have many large logging tables that are populating with data constantly. The information is rarely or never read and I want to move them out of the database. So, the idea is to: create linked ...
1
vote
2answers
25 views

How to configure an instance to listen on port 1433 to allow remote connection

Trying to connect using SQL Server Management Studio 2012 on a Windows 8.1 Client computer from outside the cluster's domain. Cluster is a two node Virtual Cluster made from Windows Server 2012 R2. ...
2
votes
1answer
34 views

io_stall question

I just installed SQL Server on new hardware and ran a test script to show io_stall information by file. Nothing else has been loaded yet, just a fresh install of SQL Server 2012. Why are the ...
0
votes
0answers
29 views

Performing a huge amount of individual INSERTs occasionally causes a lag that triggers the timeout

I have some code that is using entity framework to perform a large number of INSERTs in succession. SQL profiler shows a SELECT, then another SELECT, then an INSERT for each one due to it having to ...
1
vote
1answer
38 views

How to identify where a package execution has spent most of the time?

Team BI has a package deployed as a project on SQL Server 2012. How can I find how long a package takes to run on a daily basis, and where it has spent most of the time? I know that the ...
3
votes
2answers
56 views

How to properly increment an int identity in SQL Server if the Identity Increment isn't set?

When I use SQL Server with an int based identity column, I always use the auto increment. But I'm needing to insert into an existing table that doesn't have the property set, and I'm wondering what ...
0
votes
1answer
14 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
19 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
19 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
79 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
23 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
22 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
57 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
32 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
70 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
17 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
55 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
137 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 ...
0
votes
0answers
35 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
67 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
157 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
35 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
46 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
20 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
64 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
31 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
41 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
33 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
48 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
103 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
32 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
78 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
33 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
36 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
36 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
143 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
61 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
49 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 ...