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

learn more… | top users | synonyms (2)

1
vote
2answers
4k views

DBCC SHRINKDATABASE TRUNCATEONLY 2008R2 vs 2012

Im a bit confused was the TRUNCATEONLY parameter changed in 2012 or is the documentation wrong in 2008R2? 2008R2 Releases all free space at the end of the file to the operating system but does ...
4
votes
2answers
120 views

Central Management Server - can it manage itself?

I'm setting up a SQL Server 2012 lab on Windows 2008 R2 which has 5 SQL Servers. I registered SQL1 as my central management server and created a group under this server that includes all the servers ...
0
votes
1answer
49 views

SQL Server 2012 Intellisense Support for Extended Events

Using SQL Server 2012 with SP1 for some reason when try to use extended events programmatically using T-SQL. I am not able to see intellisense working? Just trying to check is this something on my end ...
0
votes
1answer
145 views

In Extended Events what is the difference between rpc_completed and sql_batch_completed? [duplicate]

I understand that rpc_completed is defined as the completion of a remote procedure call and sql_batch_completed is the completion of a t-sql batch. However can anyone explain in concrete terms what ...
8
votes
3answers
2k views

Can I create a user defined table type and use it in the same transaction?

When I execute the following (in management studio, GO will separate the commands into batches) use tempdb begin tran go CREATE TYPE dbo.IntIntSet AS TABLE( Value0 Int NOT NULL, Value1 Int ...
2
votes
3answers
3k views

What permissions are necessary for truncating a table?

I have a sql account with the following permissions on a database: The db_executor role you see this account being a member of was created by this script: CREATE ROLE [db_executor] AUTHORIZATION ...
1
vote
2answers
282 views

SQL Server and SharePoint time problem

I have created a SharePoint application which has a specific time zone. The Windows server where SQL Server and SharePoint are installed are using same time zone. When I create a new item in list ...
2
votes
1answer
98 views

Confirmation on query optimization and performance understanding (seek and scan)

I am following along with this video on SQL Server query optimization and I would like to check my understanding of the results I am seeing. I am using SQL Server 2012 with the AdventureWorks 2012 ...
2
votes
0answers
969 views

SQL Server intermittent error 40 Could not open a connection to SQL Server or The network path was not found

I am getting intermittent errors as per the below stack trace. I am using C# Entity Framework with SQL Server 2012 enterprise running on a separate server computer. I can connect and query the DB ...
1
vote
2answers
347 views

Cannot add an account [non domain account ] to SQL Server 2012

Background: I have a application pool [ABCDEMO] in IIS. The identity of the appool is "NetworkService". I am trying to add IIS APPPOOL\ABCDEMO to my SQL SERVER 2012 on a Windows SERVER 2012. When I ...
2
votes
2answers
69 views

How to revert multiple impersonations?

When there are nested impersonations (as in exec as user = 'user1'; exec as user = 'user2'; exec as user = 'user1';), is there a way to revert all of them without having to use the revert command ...
0
votes
1answer
992 views

SQL Server 2012 Service Master Key Regenerate

I have a new SQL Server 2012 instance and when I run the following command I get an error. ALTER SERVICE MASTER KEY REGENERATE Error: Msg 15329, Level 16, State 1, Line 1 The current master ...
0
votes
3answers
93 views

SQL Server Facets: “Linked Server” exist but “Registered Server” doesn't - why?

I'd like to monitor multiple SQL Server instances. Something similar to what is described in "Monitor Your SQL Server Linked Server Configurations with Policy Based Management" but instead of using ...
0
votes
1answer
227 views

Is the [NT Service\SQLSERVERAGENT] a supported type for SQL Server Agent service?

Reading and re-reading MSDN "Select an Account for the SQL Server Agent Service". NT Service\SQLSERVERAGENT is not listed in the "Supported Service Account Types" section. And, if not, what is it ...
4
votes
1answer
430 views

Any way to send notifications to different Emails on success/failure of SqlServerAgent job?

SQL Server 2012 R2 on Windows Server 2008R2 Is there any way to send notifications to different Emails: on success of an Sql Server Agent job to one Email address and on failure - to another ? ...
0
votes
1answer
73 views

Compiled version of stored procedure

If I have administrator rights, would it possible to view details on the compiled version of a stored procedure or a view? Is there a visual representation available?
0
votes
0answers
244 views

How i can create backup script from diagram in SQL Server?

I use SQL Server 2012 and SQL Server 2008 R2. I create a script from all object (tables / trigger / stored procedure / function ...) in my database. I generated this script from SQL Server ...
0
votes
0answers
57 views

All databases restoring in Suspect Mode in SQL Server 2012

I've just re-installed SQL Server 2012 (removed the evaluation copy and installed a licenced version) and now every database I try to restore, it fails to completely restore and the database sits in ...
0
votes
1answer
277 views

Setting Audit Events in SQL Server 2012

We have to do information assurance remediation on our SQL Server 2012 and came across the following finding: Check to see that all required events are being audited. So I ran these queries: select ...
3
votes
2answers
388 views

What's the point in SQL Server Central Management Server?

Online MSDN article "Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio)" (SQL Server 2012) states: "Note Before you execute the following procedures, ...
3
votes
1answer
631 views

How to specify a task in a SQL Server maintenance plan conditionally?

I have a weekly and nightly SQL Server Agent jobs executing a weekly and nightly maintenance plans, resp. These weekly (on Sunday) and nightly (on other 6 nights of the week) maintenance plans ...
1
vote
1answer
677 views

How to launch SQL Server Agent job on completion of another one?

An SQL Server Agent job (SSA) have to be executed after a few other SSA jobs. Is there any way to start SSA jobs on completion (or success, or failure) of another one? Currently, I do this on ...
4
votes
1answer
400 views

create linked server to vertica

I'm attempting to create a linked server from SQL Server to Vertica to pull over some data. I've attempted using the following ODBC found here. The code I'm currently trying is: EXEC ...
0
votes
1answer
192 views

Not exists/Where NOT in

Doing a pie chart in crystal. Items are Inquiries Referrals Followups A referral has to have an inquiry, A followup has to have a referral. I want to show pie chart of referrals to ...
4
votes
1answer
4k views

Syntax of INNER JOIN nested inside OUTER JOIN vs. query results

TLDR; If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening. Following up on my previous ...
3
votes
2answers
114 views

join syntax / style performance consideration

Edit: I've added a follow up question based on comments by @ypercube about semantic equivalence and my findings of the query behavior changes when you have a mix of outer/inner nested joins. We ...
8
votes
5answers
4k views

Freeing Unused Space SQL Server Table

I have a table in SQL Server Express with a lot of unused space. I need to free up space in the database. | NAME | ROWS | RESERVED | DATA | INDEX_SIZE | UNUSED | ...
0
votes
2answers
330 views

Copy SQL Server database to same server & decrypt by symmetric key return null

I have a SQL Server 2012 database that I backed up and restored to the same server and instance. When I attempt to use DecryptByKey it always returns null. Since it is the same server, do I need to ...
0
votes
4answers
2k views

How to delete the remote files older than N days on schedule from SQL Server 2012 (with SqlServerAgent job)?

Struggling to execute PowerShell commands in SqlServer Agent job of MS SQL Server 2012 R2 (Windows Server 2008R2, Windows 7 Prof) I need to delete (.bak files older, than, say, 7 days in remote ...
3
votes
1answer
2k views

SQL Server Service Account login or password is not valid error

I am trying to install SQL Server 2012 Express on a Windows 2012 Server for test purposes. This machine is part of our company domain. During installation I am trying to use one of domain account ...
0
votes
1answer
74 views

In sys.dm_os_memory_cache_entries view, what does original cost mean?

Looking at a couple of DMVs, and trying to see if I can programmatically figure out the cost of the cached plans in my SQL Server. I'm hoping that column represents the same concept of cost that is ...
1
vote
1answer
60 views

Configuring transactional replication with updatable subscription via SSMS 2012

Can I configure transactional replication with updatable subscription via SSMS 2012 (SQL Server 2012)? Is this removed from SSMS 2012 or I'm just missing something here?
1
vote
0answers
139 views

Alternative method of viewing a database diagram in SQL Server to see what tables have gone missing? [closed]

I have a database diagram for my database, but when I open it in SQL Server, I almost immediately get a message saying some permissions changed or tables in the diagram were dropped or renamed, and ...
2
votes
3answers
935 views

How do I move SQL Server database files?

I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server. How can I do this?
5
votes
1answer
805 views

Error running Powershell Script in SQL Server Agent

I have a SQL job that runs via SQL Server Agent (Running SQL Server 2012 Enterprise). The last step of the job is to run an application located on a network share. Unfortunately I don't know the name ...
0
votes
1answer
502 views

Query that only uses index takes long time to execute [closed]

I have a table with ~69,000 rows. I have this very simple query: -- SET STATISTICS TIME ON SELECT ID, FirstName, LastName FROM Customers -- SQL Server Execution Times: CPU time = 31 ms, elapsed time ...
4
votes
2answers
1k views

Log sizes unexpectedly large: log_reuse_wait_desc == “log_backup”

I've been reading posts and manuals all day, I need help. My problem: transaction log sizes after data and transaction log backups will not shrink, and grew to be unexpectedly large. The system is ...
2
votes
1answer
943 views

SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with “ReportWrongProviderType” error

We've recently upgraded to SQL Server 2012 (from SQL Server 2008 R2) on Windows Server 2012 (from Windows Server 2008 R2), and a SQL Server Job Agent job that was previously running without error is ...
8
votes
2answers
582 views

Handling time zones in data mart/warehouse

We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and ...
0
votes
1answer
280 views

dbcc checktable/checkdatabase errors after database restore

Following on from this question Error when creating an index in SQL Server 2012 we have been having problems restoring our live database back to test. Our current production database is SQL 2005 ...
2
votes
3answers
834 views

~2 Hours after a large insert SQL Server gets “I/O requests taking longer than 15 seconds to complete”

This problem has been haunting me for a while but I actually fixed the queries that caused the problems (after I finally found them) but i'm curious if it's an SQL Process that's the underlying ...
2
votes
0answers
403 views

Once more about SQL Server virtual accounts

Let me reboot my question on SQL Server virtual accounts permissions The (same) MSDN article on the same version 2012 of SQL Server "Configure Windows Service Accounts and Permissions" states: ...
2
votes
1answer
154 views

What are Objective Business Reasons to Prefer SQL Server 2008R2 over 2012?

Basically, I want to take this discussion -- What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2? -- and turn it on its head. We are a small business, and we are struggling ...
0
votes
0answers
103 views

What are the possible reasons for SQL Server errors on reading operations upon switching ALLOW_SNAPSHOT_ISOLATION` and `READ_COMMITTED_SNAPSHOT ON?

I have a client-server app running in Windows Server 2008R2 using/interacting SQL Server 2012. After reading app's documentation as well as feedbacks on internet from those who already accomplished ...
0
votes
2answers
217 views

Licensing SQL Server 2012 Standard Edition for development and test severs [closed]

Is there an economic way to license SQL Server standard edition on a test server? Given the following environment: live: SQL Server 2012 Standard Edition test: SQL Server 2012 Developer edition ...
2
votes
1answer
274 views

Error when creating an index in SQL Server 2012

I'm trying to create the following index on our test environment (SQL Server 2012): CREATE NONCLUSTERED INDEX [iTransaction-OriginatingTransaction] ON [dbo].[Transaction] ( ...
1
vote
1answer
7k views

SQL Server 2012 Restore backup to new DB Name

I seem to remember that, in 2008, you could restore a backup to a new copy of a DB, by changing the name in the "Destination Database" field from the restore-wizard. It would create a brand new DB, ...
0
votes
2answers
2k views

Is it viable to attribute permissions to SQL Server virtual accounts (NT Service\SQLSERVERAgent, etc)?

Using MS SQL Server 2012 in Microsoft Windows Server 2008. I am somewhat confused by the New Account Types Available with Windows 7 and Windows Server 2008 R2 virtual Windows accounts [NT ...
0
votes
0answers
113 views

Error Handling in SSDT (incl. Pre & Post Deployment scripts)

I'm struggling a bit how I should do proper error handling in SSDT, particularly in a pre deployment script. I noticed that for what ever reasons the SQLCMD syntax :on error exit is not working. ...
0
votes
0answers
40 views

Permissions different between test and live databases

We have 2 database Servers - Test and Live. We restore the live database back to test periodically. We have noticed that a SQL User has access to execute a stored proc on live but not on the test ...