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

learn more… | top users | synonyms (3)

1
vote
0answers
17 views

Compare Traces with RML Utilities

I am new to RML and am trying to use it to capture a trace, replay it on another server and then compare the two. I have managed to capture a trace using the server side script that comes with it and ...
2
votes
1answer
67 views

How to Retrieve value from a User-Defined Table Type (UDTT)

Goal: I want to reach and display the value of @data inside of [wwtest]. Problem: The UDTT is from the database [test] and I receive an error message: The type name 'test.dbo.tvf_id' contains ...
6
votes
2answers
144 views

SharePoint 2013 Database encryption

I'm using SharePoint 2013 with SQL Server 2012 as persistence layer. I need to encrypt the data stored in the database. I stumbled upon the SQL Server feature Transparent Data Encryption (TDE). What ...
3
votes
1answer
30 views

A Customized Table with Chronological order

Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id. Problem: When I ...
2
votes
1answer
42 views

Is there a TSQL command or another way to rename AOAG (Always On Availability Group)?

We have an AOAG named with "space" and I need to rename it to not have "spaces". It was created before my time. The 3rd party vendor tool does not work with AOAG that has spaces. There are about 40 ...
2
votes
1answer
45 views

Partitioned Database restore is very slow on SQL Server 2012 with SP2

Team, We have a Partitioned database which is very small in size(112MB backup size). This is partitioned with 15000 files for one table and 7500 files for other table. Currently we are running into ...
0
votes
0answers
31 views

How can I correct my untrusted foreign key situation?

In our scenario, we have a number of situations in which foreign keys or check constraints are not trusted. Here's some example situations: A "claim" is an entity that is the primary purpose of the ...
1
vote
1answer
18 views

Order of DROP_EXISTING and ONLINE for CREATE INDEX

If I call a CREATE NONCLUSTERED INDEX using DROP_EXISTING = ON and ONLINE = ON what order are things done it? What I want is: New index is made in the TempDB Existing Index is dropped New index ...
8
votes
3answers
157 views

IF statement not skipping TempDB when Looping through Databases with sp_MSForEachDB

[SQL Server 2012 SP2 EE] Why does the following script gives me an error relating to tempdb? exec sp_MSForEachDB ' IF ( (select database_id from sys.databases where name = ''?'') > 4) ...
9
votes
3answers
320 views

Backup internals - What happens when a backup job is running - in terms of locking and performance overhead in SQL Server?

For MySQL I know the database is backed up table by table in SQL Statements, this results in locking and if you update columns while backing up you might end up in integrity problems. To my ...
6
votes
2answers
330 views

SQL Server Management studio slow connection or timeout when using Windows Authentication

I'm getting extremely long delays (10~30 seconds) in SQL Server Management Studio 2014 when attempting to connect to a SQL Server 2012 instance over TCP using Windows Authentication. This happens when ...
0
votes
1answer
63 views

Exporting tables from SQL Server

I have to export some data from SQL Server 2012. I'm interested in flat file export (i.e.: .csv) but my database has about 500 tables. Here is an example for you to better understand the situation: ...
0
votes
1answer
54 views

Sql Server thinks I have different Data Compression when swaping partitions

I am setting partition switching for my database. I created a script using SqlCmd variable so that the same script would make both my source and target tables for partition swapping. Here is an ...
1
vote
2answers
61 views

Different way to do a LIKE WHERE condition? [closed]

I have a query which works fine: SELECT month_date= month(SendTime) ,year_date =year(sendtime) ,MessageType ,COUNT(MessageType) AS MESSAGE_COUNT FROM Message WHERE ...
3
votes
2answers
37 views

Shreded system_health ExEvent not returning all node information

SQL Server is not returning all node information when I shred the deadlock XML from the 2012 system_health Extended event. When I run the following query SELECT ...
4
votes
2answers
73 views

Rebinding a SQL Server user to its login after a restore with this user

After performing a restore of a SQL Server (v12.0.2000) database, I figured that a user needs to be remapped to its login (this issue seems to be known as orphan users). And one of the simplest way to ...
3
votes
2answers
47 views

Prevent logins loss when restoring a database

I'm backuping and restoring databases accross two sql server (v12.0.2000). But it looks like whenever I restore a .bak file, the login I created on the database that was backuped is no more existent ...
8
votes
3answers
207 views

Configure RAM for SQL Server and NUMA

Accidental DB admin here. Question: Would you still manually limit max RAM for SQL Server even though the sole purpose of that server is to serve SQL Server database engine only? I have Microsoft ...
1
vote
1answer
32 views

Indexed view doesn't exist on the subscriber

I am configuring transactional replication in SQL Server. The subscription is configured as push from 2008R2 publisher (distributor is the same server) to 2012 subscriber. The object I want to ...
0
votes
3answers
55 views

Query for total downtime of site using datestamps and status columns

what want to find the total downtime for a site. I'm thinking I can subtract the 'down' DateStamp from the following 'up' DateStamp. then add the totals together for all downtime. I want to get a ...
0
votes
1answer
42 views

Optimal SQL Server 2012 Disk Configuration

I will be installing SQL Server 2012 on a new Dell T430 server containing four (4) physical 15K SAS 6Gbps disk drives using a PERC H730P RAID Controller and RAID 1. Since this will be the first time ...
0
votes
0answers
37 views

Sync two SQL Server databases

So I've got a hosting package from hostgator with an MS SQL database and a local SQL Express 2012 database running. I'm trying to investigate syncing the two together, two-way syncing, and I'm unable ...
1
vote
2answers
49 views

With LocalAdmin become sysadmin SQL Server

I am a local Admin on my machine. Same old thing, my account wasnt added when SQL Server was installed, and I don't want to have to reinstall SQL Server. I went through this blog and it worked until ...
2
votes
1answer
42 views

Create Database and LCK_M_S

We are experiencing issues when creating a database in the SSMS GUI in that sometimes it times out complaining that it could not get an exclusive lock on the Model database. Other times the database ...
3
votes
1answer
56 views

Why is the secondary selective index not used when the where clause filters on `value()`?

Setup: create table dbo.T ( ID int identity primary key, XMLDoc xml not null ); insert into dbo.T(XMLDoc) select ( select N.Number for xml path(''), type ) from ( ...
5
votes
2answers
78 views

Does SA account need to be called “SA” or can I rename it safely?

We have some applications on our network (from before my time) that use SA account in their SQL Server connection string. It's hard-coded in the source code, and for whatever reason, we can't change ...
1
vote
1answer
60 views

Full backup at 1am, not started by my maintenace plan

I set up a complete set of Maintenance Plan. Full backup of all databases at 3AM. Differential backup at 6h30, 12h30, 18h30 and 00h30. Log backup at each hour, at 45 minutes (1h45, 2h45...) In my ...
2
votes
0answers
64 views

MEMORYCLERK_SQLOPTIMIZER in SQL 2012 Memory usage extremely high and causes server to crash

I have been having issues with our SQL server for a while now. It seems to run out of memory every few days and when I look at the memory dump, the MEMORYCLERK_SQLOPTIMIZER seem to take over memory ...
2
votes
1answer
35 views

Difference between clustered index and partitioning column

As I understand partitioning, the column that you partition on is how things are divided into partitions. But does the partitioning column have anything to do with order on the disk? (Aside from ...
3
votes
1answer
60 views

Column Store Index over an clustered index (created when primary key is declared) - SQL Server

When a clustered index is created, the table itself becomes the index structure sorted by the index key. Imagine now that we have 5 columns in a table XPTO (a,b,c,d,e) and "a" is the primary key and ...
3
votes
1answer
44 views

Partially contained databases and Windows Logins - best practices?

In versions of SQL Server prior to 2012, in order for anyone to log in to a database using Windows credentials, you first had to create a server-level login, them map that login to a user for each ...
2
votes
1answer
16 views

Retreive a Customized List with input data

I have a database that has about 223 tables and I need to locate the name of the constraint. My request is to add a parameter that is the name of the constraint and you retrieve a list that shows the ...
4
votes
1answer
67 views

SQL Server 2012 Crashes and service doesn't restart

We've been having an issue with our SQL server lately. It seems that the server will run out of memory and then crash. For some reason, the sql service wouldn't restart. I would have to go and ...
0
votes
1answer
71 views

SQL Server Data File/Log File poor performance

I've been asked to look at a database/application server that's "performing slow". No one could really tell me what was performing slow (except for the database backups) so this has been a discovery ...
3
votes
1answer
223 views

SQL Server DOUBLE keyword

There is a reserved keyword DOUBLE in SQL Server, but I know that it's not a data type (other languages' double is float or real in SQL Server); does anyone know why this is a keyword? I've tried to ...
0
votes
1answer
57 views

fn_dblog not showing up deleted table data

I unintentionally deleted 4 rows from a table. I tried to fetch back the deleted data using a stored procedure (got from internet). However, I was able to see my data in the result of stored ...
0
votes
1answer
89 views

CLR Assemblies and SQL Server Availability Groups

I have a SQL 2012 Availability group. On that I also have a database with a .Net assembly that is using PERMISSION_SET = UNSAFE. CREATE ASSEMBLY drawing FROM ...
0
votes
0answers
54 views

Can't connect to an Active Node in an Active/Passive cluster

While being logged into an active node SQL Server 2012 node, I tried connecting to it from within the SQL Server Management Studio but was unable to do so. I tried the fully qualified name and the IP, ...
9
votes
1answer
294 views

What is the different between compression on the PK vs. on the Table?

Data compression can be set on the table: CREATE TABLE dbo.SomeTable( SomeId [bigint] NOT NULL, OtherId [bigint] NOT NULL, IsActive [bit] NOT NULL, CONSTRAINT [PK_Some] PRIMARY KEY ...
2
votes
1answer
49 views

Controlling how tempdb activity is split across drives (tempdb on both solid state and spinning drives)

I'm seeing the following tempdb I/O behavior over a one hour period: There is a fair amount of disk I/O generated by multiple DW workloads executing on the machine, some of which cannot fit in the ...
0
votes
0answers
22 views

2012 Always on (AG only) Where should the Windows Cluster SIT?

2012 Always On and the Windows Cluster, shouldn't the Windows Cluster be on a separate server like the Domain controller/BackupDomainController? With Windows clustering Services on each node ...
0
votes
1answer
34 views

Using * to enforce the need to update both tables together

I am a Java Programmer and definitely NOT a DBA. I am just an "accidental helper" to a DBA as we try to troubleshot an inherited VB.NET application situation. The guy that wrote this MS SQL got fired ...
0
votes
1answer
57 views

Do I need to rebuild indexes when upgrading a DB from 2008 to 2012?

I can't find anything on BOL that indicates I need to rebuild indexes or statistics after the migration and compatibility level upgrade of a database. But I have read a few articles/answers around the ...
1
vote
0answers
114 views

INSERT INTO with TABLOCK taking longer than without TABLOCK [closed]

I'm doing something like below to ensure the insert is minimally logged: insert into AnotherDatabase.dbo.HeapTable with (Tablock) ( <column fields ) select <column fields> from ...
2
votes
2answers
54 views

Why Does Partition Range Right Skip Deletes/Inserts?

I have two example Queries (shown below). Both (drop and) create a database and a partitioned table in it. The table has several partitions, with data in each of them (including the last, unbounded, ...
5
votes
1answer
138 views

Is there any way to monitor execution of xp_cmdshell in SQL Server 2012?

I've been tasked with assessing the security risk involved with allowing a third party vendor to debug their queries remotely within SSMS. This means that the user's account must be in the sysadmin ...
6
votes
0answers
108 views

How to terminate a suspended SQL Server transaction waiting for IO_COMPLETION?

We have a transaction that has been running for more than 5 hours. We are running out of disk space. The session has been killed but it is still waiting for IO_COMPLETION. Actually the wait_type just ...
0
votes
1answer
51 views

Check for duplicates in column and determine which row to keep

I've found myself running in to this on multiple occasions now where I got a query and would need too further refine it. Where one column might contain duplicates and I need to evaluate which row to ...
3
votes
1answer
89 views

Decode and aggregate a BitMask in t-sql

I have a table that contains a bitmask field that stores permissions, where each bit denotes whether a particular permission is granted or not. Here's a simplified example: DECLARE @T TABLE (id ...
0
votes
1answer
55 views

Attaching Database Incorrect name in sys.master_files

I have been moving some databases from SQL 2008 to SQL 2012 using detach and attach method. I correctly map up all the files and the physical and logical names appear correctly in the properties of ...