SQL Server

Microsoft SQL Server powers your entire data estate by supporting structured and unstructured data sources. It builds on previous versions of SQL Server, which have been industry leading for four years in a row and a leader in TPC-E. It scales to petabytes of data and allows customers to process big data through PolyBase using T-SQL over any data. SQL Server has also been the least vulnerable database during the last seven years. SQL Server brings data insights with business intelligence capabilities that provide analytics at a fraction of the cost on any device along with advanced analytics with support for R and Python.

More details about SQL Server are available in the SQL Server documentation.
If you have a technical issue, please open a post on the developer forums through Stack Overflow or MSDN.


  • Hot ideas
  • Top ideas
  • New ideas
  • My feedback
  1. SSIS 13.0.4474 does not work in SSMS after v17.2

    When trying to connect to SSIS (13.0.4474) through any version of SSMS past 17.2 we get the following error (see attached image for full error):

    Connecting to the Integration Services service on the computer X failed with the following error: "The specified service does not exist as an installed service."

    Pinal Dave blogged about this but not sure if it is something that the Microsoft SQL Server team would be able to address or why it hasn't been flagged to be fixed before?

    Pinal Dave's blog post:
    https://blog.sqlauthority.com/2017/08/26/sql-server-management-studio-ssms-unable-connect-ssis-specified-service-not-exist-installed-service/

    It would be great to have this fixed.

    12 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  2. filegroup to read_only prevents dbcc checkdb from running

    My database uses a combination of partitions and file groups in a sliding window configuration. I have implemented the VLDB CheckDB pattern and found that all file groups must be in a read-write state for this to work. By placing some older file groups into a read-only state I was hoping to utilize partial backups to reduce backup times. As a workaround we are temporarily placing the file groups back into a readwrite state to allow the checks to continue - however to make sure our FG backups can be used in a restore the FG backups have to be…

    12 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  3. LAST_MODE should map to LCK_M_RX_X in the lock_mode Extended Event

    This is just plain wrong.

    SELECT mv.name, mv.mapvalue, xo.description, xp.description
    FROM sys.dm
    xemapvalues AS mv
    JOIN sys.dmxeobjects AS xo

    ON  mv.object_package_guid = xo.package_guid
    
    AND mv.name = xo.name

    JOIN sys.dmxepackages AS xp

    ON xo.package_guid = xp.guid
    

    WHERE mv.name = 'lockmode'
    ORDER BY mv.map
    key;

    12 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  4. Support Trace Flag 834 with Columnstore

    Trace flag 834 has been marked as unsupported with columnstore by Microsoft since SQL Server 2012, but that trace flag resolves columnstore and batch mode related bottlenecks with the RESERVEDMEMORYALLOCATION_EXT wait when running workloads at high concurrency. In addition, vendors use TF 834 with columnstore when competing in the TPC-H benchmarks.

    Please support TF 834 with columnstore.

    References with reproducible workloads:

    https://orderbyselectnull.com/2018/03/06/large-cci-etls-cannot-scale-without-tf-834/

    12 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  1 comment  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  5. SSRS report builder not working after applying SQL update

    I have noticed that I can no longer use the “Report Builder” functionality in SSRS (while opening it from Report manager URL) after patching SQL Server 2012 Sp4 with recent CU KB4057116.

    Window Server 2008 R2 Standard
    SQL Server 2012 SP4 (11.0.7001.0)
    Getting below error after installing KB 4057116

    https://support.microsoft.com/en-us/help/4057116

    Following errors were detected during this operation.

    * [26/02/2018 10:15:22] System.Deployment.Application.InvalidDeploymentException (HashValidation)
    
    - File, interop.shdocvw.dll, has a different computed hash than specified in manifest.
    - Source: System.Deployment

    12 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  8 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  6. Deploying single SSIS package to SQL 2016 from SSDT 17.0-17.4

    Deploying a single SSIS package with VB script task, targeted for SQL 2016 from SSDT 17.0-17.4 causes the script tasks to not execute but the package deploys and executes reporting success. .

    12 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    5 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  7. Cannot open Extended Event when default database not master

    You might get an error message when looking at Extended Event data (file, etc) if Object Explorer isn't connected to the master database.

    12 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  8. "parameter name already exists" while trying to Process Database on Azure Analysis Services in SSMS

    If I try to Process Database in SQL Management Studio on either of my computers, I get the error:

    "parameter name already exists",
    "Parameter name: name (Microsoft.AnalysisServices.BackEnd).

    It seems to be an error returning from the AAS server itself (it happens in SSMS 2017 version 17.3 two different PCs (Windows 8.1 and Windows 10 1703)).

    Oddly, I can still successfully process the database if I run the refresh XMLA script above in SQL Management Studio or if I choose to process default or full under "Processing Option" in SSDT.

    I have tried deleting the database and redeploying, I have tried…

    12 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    6 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  9. SQL Server 2019 RC1 - Secure Enclaves Bug

    When trying to configure Secure Enclaves within 2019 - Allow enclave computations is not an option for us.

    This was apparently resolved in CPT 3.1 however we are still experiencing this issue.

    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  10. SSMS 18 Remembers password for user, not specific to database

    I have a number of SQL Server connections which use the same login but each has a different password. Before SSMS 18 the remember password function worked correctly, storing the password for individual connections.

    Under SSMS 18 it appears to only be storing the last used password for the login regardless of the server connection.

    Can this be restored to the previous functionality.

    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  11. Specified sample rate is not followed when statistics object is created on an empty table

    A statistics update "WITH RESAMPLE" does not respect specified sample rate if the statistics object was created on an empty table. This behavior is best understood with an example (see the attached script).

    This bug is a bit annoying because it requires the user to take a specific order of operations with their table creation, data population, and stats population to get around it. The workaround we currently have is to just insert a row with default values of each data type, create stats with the desired sample rate, and then delete that row.

    x

    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  12. Azure SQL can't upgrade data-tier application

    Using DacFx 17.4.1 / SSMS 17.8.1, it is not possible to deploy and then upgrade an Azure SQL Server Database as a data-tier application.

    The initial deployment works correctly, but attempting to upgrade using SSMS or sqlpackage (using /p:BlockWhenDriftDetected=true /p:RegisterDataTierApplication=True) results in the following error:

    Databases registered as a DAC database must be hosted by an instance of SQL 2005 SP4, SQL 2008 SP2, SQL 2008 R2, SQL 2012, SQL 2014 or Microsoft Azure SQL Database.
    Unable to proceed with DAC operations on the target instance because it does not support DACs. Specify another instance, or upgrade to a version…

    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  13. SQL SERVER 2016 (SP2-CUI) BUG? MERGE and INSERT with COLUMNSTORE index creates crash dump - script supplied

    Hi

    I have managed to create a script that (on my system) reliably generates the following message

    Location: pageref.cpp:955
    Expression: ISOFF (BUFMINLOGGED, mbuf->bstat) || pageModifyType != PageModifyTypeContents || GetPagePtr ()->IsTextPage ()
    SPID: 59
    Process ID: 5080
    Msg 3624, Level 20, State 1, Line 45
    A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent…

    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  14. Parallel SELECT INTO from sys.messages causes intra-query deadlock

    Loading data into a new table from sys.messages using parallel SELECT INTO causes consistent self-deadlocks. Testing on a machine with default parallelism settings and 4 logical cores. This occurs in SQL Server 2014, 2016 and 2017. Here's a reproduction:

    BEGIN TRANSACTION;
    
    

    SELECT TOP 10000
    m.message_id, m.[text]
    INTO SomeNewTable
    FROM sys.messages m

    For some reason, removing the "BEGIN TRANSACTION" reduces the frequency of deadlocks caused by this query. I've attached a deadlock graph from the issue.

    It's possible that bulk loading from sources like sys.messages is simply not a supported scenario - but, if so, this should be documented (or potentially…

    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  3 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  15. smo script bug cannot drop table #sver

    I tried to script using SMO via following powershell script

    $conn = new-object system.data.sqlclient.sqlconnection
    $conn.connectionstring = "data source=(local);initial catalog=nxdba;integrated security=sspi;"
    $conn.open();

    ## SMO 사용
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server"
    $so = New-Object "Microsoft.SqlServer.Management.Smo.ScriptingOptions"
    $so.AgentJobId=$true;
    $so.ScriptBatchTerminator=$true;
    $so.ToFileOnly=$true;
    $so.FileName="D:\Dba\Job.sql"

        ## Job Scheduler 백업
    
    $srv.JobServer.Jobs |
    foreach {
    if (($_.Name -notlike '*NXDBA*') -and ($_.Name -ne 'syspolicy_purge_history')){
    $_.Script($so);
    }
    }

    and above powershell turns into following t-sql script and causing error "cannot drop the table #sver"

    here is extended event result

    <RingBufferTarget truncated="0" processingTime="0" totalEventsProcessed="1" eventCount="1" droppedCount="0" memoryUsed="5191">
    <event name="error_reported" package="sqlserver" timestamp="2018-06-08T07:19:58.924Z">

    &lt;data name=&quot;error_number&quot;&gt;
    
    &lt;type name=&quot;int32&quot; package=&quot;package0&quot;&gt;&lt;/type&gt;
    &lt;value&gt;3701&lt;/value&gt;
    &lt;/data&gt;
    &lt;data name=&quot;severity&quot;&gt;
    &lt;type name=&quot;int32&quot; package=&quot;package0&quot;&gt;&lt;/type&gt;
    &lt;value&gt;11&lt;/value&gt;
    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    3 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  16. Computed column matching bug with ISNULL on BIT type

    A computed column with bit data type and outermost ISNULL is expanded but not matched back, preventing index matching.

    Workarounds:


    1. Ensure the computed column is not typed as bit; or

    2. Do not use an outermost ISNULL (column marked nullable); or

    3. Persist the computed column and enable trace flag 174.

    Demo:

    -- Uses index
    DROP TABLE IF EXISTS dbo.Example1;

    CREATE TABLE dbo.Example1
    (

    a1 bit NOT NULL,
    
    a2 bit NOT NULL,
    d AS a1 | a2,

    INDEX i (d)

    );

    SELECT COUNT_BIG(*)
    FROM dbo.Example1 AS E
    WHERE E.d = CONVERT(bit, 'true');

    -- Does not use index
    DROP TABLE IF EXISTS dbo.Example2;

    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  17. SSRS Bug: Choose new target for linked report offers no reports

    Install and configure SQL Server Reporting Services 2016SP1CU7 (mine was a Migration from 2008R2SP3, but an empty one should do). Upload a 'master' Report and create a link to it. Delete the 'master' and re-upload it. The link is now orphaned as expected.

    In previous Versions, one could re-map the link to a different master without losing parameter configuration. It had problems with hidden folders, but allowed to enter a path explicitly by hand.

    2016 pops up a selector box (aggravatingly using tiles no matter how often I dell it I want lists), which allows browsing folders, but all folders…

    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  3 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  18. No hash warning on adaptive join

    When an execution plan uses an Adaptive Join, and the actual row count exceeds the estimated row count by sufficient to cause the hash table to spill to tempdb, no hash warning is exposed in the execution plan.

    (The hash warning is visible in an extended events session; and the Batch Mode Adaptive Memory Grant feature does also kick in correctly, so the spill is detected and handled internally; just not exposed in the execution plan).

    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  19. An internal error happened while generating a new DBTS for database "test_db". Please retry the operation.

    This error happens during INSERT/UPDATE queries, even simple ones, if the server is busy for example restoring another database

    The SQL Server is the Enterprise version, it has the SP1 applied.

    SELECT @@VERSION produces: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

    It runs on VMWare 5.5 build 4180647.

    The OS is Windows 2012 R2 Standard, no SP applied. The server is configured with 4 vCPUs and 80 GB of RAM. Disks are handled…

    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    8 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  20. QDS_LOADDB Wait type blocks all queries

    After restoring a database with Query Data Store enabled, all queries will encounter the QDS_LOADDB wait while the query store is starting up. They will not finish and no data can be retrieved from the database during this time. This is on Enterprise V 13.0.4422.0.

    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 6

    <=-=May 27 2017 7:48AM=-=>

    Thank you for taking the time to post this issue! We understand that this could be an important performance improvement.

    This happens because Query Store information is loaded in synchronous mode. In future releases, we will work on changing it to async mode. As a workaround try to apply trace flag number 7752
    Trace flags are described here (https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql)

    <=-=Nov 8 2017 10:13AM=-=>

    We have experienced the same issue. SQL logs showed ramp up in memory waits then a large spike QDS_LOADDB for 3 hours preventing any execution.
    Query Store Disabled for now.

  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base