All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

0
votes
2answers
443 views

SQL Server 2005 Replication Stops after a minute without an error

Background: I have a SQL Server 2005 setup with master, slave1, slave2 replication set up as a pull replication from slaves. The distribution database resides on the slave1 machine, both slaves pull. ...
1
vote
1answer
388 views

SQL Server “Max Worker Threads” Setting

I am helping troubleshoot some problems on a SQL Server box. An application that used to run without problem is now failing with connection pool errors, and no changes have been made to the ...
1
vote
2answers
635 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 ...
3
votes
1answer
474 views

Can SQL Server prevent the pagefile from being moved?

On a production server of ours the page file has grown to 30gb+. The Sys Admin has moved the Page File to a different drive, but for some reason the old one won't be removed. He thinks that SQL ...
2
votes
2answers
107 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 ...
2
votes
4answers
547 views

Which of these queries is best for performance?

Which of these queries are best for performance ? Sometimes I wonder if SHORT scripts really is the best thing to focus on. These scripts performs the same task. With left joins i can achieve what i ...
3
votes
3answers
7k views

Is replication possible on two different domains?

I am implementing a database replication between different geograhical locations where each one is on a different domain. After going through all the process, when I start the agent it runs and stops. ...
1
vote
1answer
5k 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
241 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 "...
2
votes
1answer
416 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
1k 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
675 views

Data Warehouse - Slowly Changing Dimensions with Many to Many Relationships

As an example, let's say I have a fact table with two dimensions and one measure FactMoney table ProjectKey int PersonKey int CashAmount money The two dimensions are defined like this: ...
1
vote
2answers
603 views

SP or query to dump “password policy” checkbox for all logins

sp_helplogins doesn't show a column for this. I need a listing of all logins, with the status of their "Enforce password policy" checkbox. This is for an auditor, and the standard practice is for ...
0
votes
1answer
76 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?
2
votes
2answers
172 views

Best way to explain the importance of Raid 10 to SysAdmins [closed]

Hi I'm trying to impress upon a group of administrators as to why the extra money should be spent on raid 10 vs say raid 5. Other then just pointing at MS Best Practices does anyone know of any good ...
7
votes
3answers
8k views

Indexing a PK GUID in SQL Server 2012

My developers have setup their application to use GUID's as PK for pretty much all of their tables and by default SQL Server has setup the clustered index on these PK's. The system is relatively ...
1
vote
0answers
684 views

SQL Server FileTables (File Stream) file Corruption

We're running an implementation of SQL Server 2012 FileTables with several scientific instruments with PCs that write data directly to a mapped drive of a FileTables share. The size of this share is ...
1
vote
1answer
729 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
76 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 ...
1
vote
1answer
740 views

Approaches for Real time copy of database on same SQL Server instance

I have a draft of requirements that look like this: Production database should be copied in real-time to "Pre-prod" database The Pre-prod database should be available to read and update It should be ...
0
votes
1answer
626 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
2k 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, ...
4
votes
1answer
2k 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 ...
2
votes
1answer
3k 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 ...
0
votes
2answers
17k views

To Join two tables without using Primary key

Table1: Order; Column Name: Id (PK), SecurutyId, UserId. ** But no foreign key in this table. Table2: Security; Column Name: Id (PK), SecurutyId (FK), Symbol. Can I do That? Select o.SecurityId, ...
0
votes
1answer
215 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 ...
1
vote
2answers
2k views

Foreign key constraint based on conditional column

I'm trying to figure out the best way to handle this situation: I have a table set up as follows: LEARNING CONTEXT ================ LearningContextID - INT - Surrogate Primary Key EntityID -...
3
votes
2answers
2k views

How to transfer database from SQL Server to DB2?

I have a database file on disk called project.mdf. I want to import this into IBM DB2. How do I import this database?
0
votes
0answers
150 views

SQL Server JOIN with COUNT [closed]

I have two tables I'm trying to query across. One contains an entry for every time a user tunes to a particular channel in our middleware. The second is normalized table containing station information....
1
vote
1answer
303 views

Unique constraint on multiple nullable columns Sql Server

We have a PriceCombo table with the following columns: id (not null), a_id(null),b_id(null),c_id(null), discount(not null). We are using this table to figure out which price to assign various combos ...
23
votes
2answers
6k views

Index not making execution faster, and in some cases is slowing down the query. Why is it so?

I was experimenting with indexes to speed up things, but in case of a join, the index is not improving the query execution time and in some cases it is slowing things down. The query to create test ...
2
votes
1answer
567 views

Need to understand output of sqlcmd.exe

I was advised to make another question, from here - http://stackoverflow.com/questions/19674045/need-help-to-make-sqlcmd-run-as-per-requirements My sqlcmd.exe is invoked with a command of the form - ...
2
votes
1answer
966 views

SQL Server Error - HRESULT E_FAIL has been returned from a call to a COM component

I have a huge 200MB .SQL file. When I try to open this in SQL server management studio 2008, I get the error -Error HRESULT E_FAIL has been returned from a call to a COM component. What could the ...
3
votes
1answer
130 views

Restoring with replace going incredibly quickly [duplicate]

So I have a database (on a SQL Server 2008 R2 SP2 instance) that is 2TB uncompressed, with a .bak file that is 500GB compressed. As I have been toying with the fastest way to restore this database ...
1
vote
2answers
8k views

Best Possible Solution for Reporting Mirroring or Replication?

Hi I have SQL SERVER 2008R2 Data Centre Edition and I use SSRS and a Vendor application for reporting. I have a Reporting Server where we backup and restore everyday from Live Server to have Data ...
3
votes
1answer
129 views

DBCC show_statistics: density doesn't match what I calculate

I've read in several articles that the density of a column is (1.0 / count(distinct columnname)). I have a column with roughly 2.6 million distinct values over 2.7 million rows, but the column ...
7
votes
1answer
21k 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 ...
1
vote
1answer
380 views

Product quantity bound to colors and sizes table structures

I've been thinking about how I'll be able to create products with sizes and colors which will match the max quantity of a product (or their total will be the max.quantity ). Let's say I got a product ...
1
vote
0answers
47 views

SQL Server log Size increasing while in Simple recovery mode [duplicate]

Is it possible to locate the active transaction, it was not listed in DBCC opentran, the log file keeps getting bigger. Database is in Simple recovery tried shrinking the database - no luck Also ...
0
votes
1answer
626 views

Multibyte replace not working in SQL Server

Why does one work and the other doesn't? SELECT REPLACE(N'IAșI' ,N'ș','x') -- doesn't match (returns 'IAșI') SELECT REPLACE('IAșI' ,'ș','x') -- works (returns 'IAxI') My guess would be that in the ...
4
votes
1answer
5k views

What's the impact of running sp_updatestats in production server?

Is it safe to run sp_updatestats on an SQL Server in the production environment? Or rather, what is the impact of updating all statistics on a sql server? Can it "choke" the sql server while it runs ...
4
votes
1answer
479 views

Large difference in disk space used by partitions

I have a partitioned table that contains about 155 million records total. It's partitioned by year and the partitions for both the current and previous years contain about 25 million records each. ...
3
votes
1answer
242 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 ...
2
votes
4answers
428 views

Benefits of droping a temporary table

What are the benefits (if any) of dropping a temporary table in SQL Server? eg: DROP TABLE #temp; Since temporary tables are automatically dropped by the server this statement would seem to be non-...
1
vote
1answer
5k views

Row locking within stored procedure [duplicate]

I have a SQL Server Stored Procedure for getting the next number in sequence stored in a configuration table: declare @val as int SELECT @val = LongValue from tblSystem WHERE [Key] = @Key; SET @val =...
1
vote
2answers
2k views

Why would a long running query remain sleeping and with null wait_info

The following query was run on SQL Server 2008 R2 and took over 1 hour to run: select log_id, type, instance_id from ts_cache_log where log_id > 146400 order by log_id It produced 0 rows ...
1
vote
2answers
4k views

Protect SQL Server from hackers

Today, my SQL Server 2008 is attacked by hacker from IP address in China [4300 Login failure in last 7 hours]. I contacted GoDaddy support and below is what they told me. EXEC sp_readerrorlog 0, 1, '...
6
votes
1answer
442 views

Minimal Logging Conditions in SQL

I have written a script to test out the claims made on this page http://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx in the table titled Summarizing Minimal Logging Conditions about ...
1
vote
3answers
722 views

How to determine if SQL Server will not execute parts of the script in parallel?

Are there any general rules or maybe easy way to tell if SQL script will execute in batches instead of "all at once"? USE UserDB; GO DBCC SHRINKFILE (DataFile1, 5); GO DBCC SHRINKFILE (DataFile2, 5);...
1
vote
2answers
1k views

Can't restore Azure bacpac into localdb with SqlPackage

I have made a backup of my SQL Azure in a bacpac file and when I try to import the bacpac in localdb it always fail. Here is the command I use: PS C:\Program Files (x86)\Microsoft SQL Server\110\DAC\...