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
28 views

How many log files does a database usually have? [duplicate]

I'm studying SQL Server. I have read that a database can have several data files and one log file, but recently I was heard that a database can have more than one log file. Is this true?
3
votes
0answers
27 views

How to optimize this tricky SQL self-join?

I want to perform a complex self-join on a table. I know that this could in theory be done very efficiently (see below), but I have trouble getting SQL (on Microsoft SQL Server) to do so. My question ...
0
votes
0answers
15 views

SQLAgent Job Step- Database field blank, but not

In looking at a historical SQL Agent job on my employers server I noted a job with 4 steps. The Databasefield was set as master for the step 1 and not set at all for the other 3 steps. Yet each of ...
1
vote
1answer
16 views

Synchronous SQL Server Replication only for specified subscribers

I would like to create database replication between multiple localisation. There will be one publisher and multiple subscribers. Is it possible to define that synchronisation is synchronous only for ...
-2
votes
0answers
33 views

Want to setup full database in sql server 2012 [on hold]

I need to setup full database let say want to create tables, stored-procedures, cursors, functions etc. I have one xml file and all the related scripts. I need to know is there any way that I can ...
0
votes
1answer
22 views

What is the difference between unique, alternate and secondary keys in SQL Server? [on hold]

What is the difference between unique, alternate key and secondary keys in SQL Server?
3
votes
1answer
31 views

Why does Veritas backup take longer through CMDEXEC than cmd?

"c:\...\dbbackex.exe" -f "C:\...\backup.bch" -p policy_name -s server_name -np The above command, when run from command line takes something like 30sec to finish for a small db or transaction logs. ...
2
votes
1answer
22 views

Getting the referenced object from SQL Trace

I am able to query the default trace or any SQL trace for the event "Audit Add Role Event" to see if anyone has received any instance level access escalation. What I am trying to figure out is how to ...
-2
votes
1answer
26 views

I can't save Database Diagrams

I'm trying to save a database diagram but I'm getting an error. How can I fix this? Cannot insert the value NULL into column 'diagram_id', table 'SOFT.dbo.sysdiagrams'; column does not allow ...
3
votes
3answers
219 views

The fastest way to get the most recent records

I am looking for the fastest way to return the most recent record for each reference number. I quite liked the solution from BrentOzar.com which can be found on ...
1
vote
1answer
20 views

High Log Space Used for model System database

I was running DBCC SQLPERF(LOGSPACE) and noticed that while my user databases have nice, low Log Space Used percentages, my model system database is currently at 98%. I have it set to the default Full ...
0
votes
1answer
20 views

Partitioned view with dedicated “routing” column

I have an application (Microsoft Dynamics NAV to be exact) that splits up data for different companies in tables with the exact same structure. I can not change that behavior, so I built VIEWs that ...
0
votes
0answers
23 views

2012, 2014 and 2016 SQL Server Licensing Costs [on hold]

I've looked for this information on various blogs because Microsoft's pages are unhelpful and very "selly". Does anyone have a side-by-side comparison of the licensing costs for 2012, 2014 and 2016 ...
1
vote
0answers
28 views

Change login name for all objects on server

Example, I have a create a windows login DOMAIN\GROUP01, and mapped that login to a database DATABASE01 with lets say DB_OWNER permissions I want to change the login name, I went into Active ...
-1
votes
3answers
176 views

Speeding up insert in SQL Server

I have a stored procedure which inserts some records in a few tables. In couple of tables at least , the inserted records number 10000+. Not more than 15K though. Takes 3-5 mins for this procedure to ...
1
vote
1answer
39 views

why sql server has high Worker threads?

Product : SQL Server 2008 (SP4) CPU : 64 64-Bit system Worker thread configuration = 0 SCOM Alerts : SQL DB 2008 Engine Thread Count is too high After receiving this alert from SCOM i have ...
0
votes
0answers
15 views

Connecting database ISSUE to remote database in mssql [duplicate]

May be This question seems repeated but please read whole question carefully. I have Microsoft SQL Sever in one PC (ex. machine A) and I wants to connect and get data from that database from another ...
0
votes
0answers
18 views

How to implement SQL Server Service Broker with external activator

I am trying to execute an external app using SQL Server Service Broker and the External Activator provided by Microsoft. I have created the following script: IF EXISTS (SELECT * FROM sys.databases ...
0
votes
0answers
22 views

Query to Group By for a Specific Substring returning Only One Result [on hold]

I have the following query: Select count(*) as countForType, SUM(typeTable.amount) as totalForType, case when ( charindex('[', typeTable.source) > 0 and ...
3
votes
2answers
29 views

SSIS - How to parse extensions from path-filename strings

I'm importing a large set (126M rows) of file data from CSVs. Import works fine, except I'm trying to derive a column as I import rather than trying to script it out afterward (due to the number of ...
-1
votes
0answers
42 views

How do we convert SQL Server query to MYSQL? [on hold]

How do we implement this SQL Server syntax: IF(SELECT MAX(DateAdded) FROM RestoreDetectors " + " WHERE CONVERT(VARCHAR,DateAdded,101) = CONVERT(VARCHAR,GETDATE(), 101) " + " AND DATEDIFF(SECOND, ...
-2
votes
0answers
24 views

SQL server Database Consolidation [on hold]

What are the best practices for consolidatig multiple SQL Server databases into a single SQL Server machine? I have gone through many of different solutions but I'm unable to find the best solution. ...
0
votes
1answer
33 views

DBCC CHECKIDENT behaving inconsistently

I have a script that sometimes needs to be run on a fresh database that's just had all the tables created and sometimes needs to be run on an existing database. I have a script to wipe out the tables ...
2
votes
0answers
44 views

TSQL Generic Row number theoretical question

To start, We have a pretty complicated system that's been a compilation of over 20 years of code... And the code needs to work with SQL2005 all the way to SQL2014 (can't use the offset Fetch Next) ...
0
votes
1answer
29 views

Altering failed job email

I'm looking for a simple solution: if you have a job with multiple steps and one step fails but the job is set to continue, is there a way for the SQL Agent email to say which step(s) in the process ...
1
vote
1answer
42 views

Partitioning a table on a column in another table

I have a fact table called FactAccount which has the following columns: AccountId (identity) AccountMonthId - FK referring to DimMonth AccountYear The DBA has done the partitioning on the ...
4
votes
1answer
50 views

Can't get databases or its objects with powershell

I have Powershell installed both in my server and development pc. In my dev machine apparently have a most recent version of PowerShell installed or some its components of SQL Server 'sqlps'. My ...
1
vote
2answers
56 views

SQL Server Concurrency Issue [on hold]

We have a SQL server concurrency issue we are facing and would like some ideas on how to solve it. There are several procedures we have written the contain INSERT/UPDATE statements on tables in a Set ...
2
votes
0answers
40 views

How to Replicate data between a hosted database and a local database

I have a Cloud hosted Database, maintained by a different company. Here is my scenario: Cloud Hosted Database SQL2012 - Need to connect through a VPN. We setup a local Server with with Dual NIC ...
0
votes
2answers
46 views

SQL With Multiple Condition and Column

I have 3 tables: Employee +----+--------------+--------------------+----------------+ | Id | EmployeeName | EmployeePermission | Position | ...
7
votes
6answers
857 views

Fast reads without paying the price on writes

We have a large append-only table containing financial transactions. On average 1000 transactions are inserted per minute. Since there are now more and more usecases where we actually want to read, ...
2
votes
2answers
33 views

Modify linked server definition (Oracle) in SQL Server 2012

I have created a linked Oracle server in SQL Server 2012 using Oracle Provider for OLE DB and TNSNAMES.ORA (the Oracle database is clustered and, as far as I know, no other connection method works ...
0
votes
0answers
43 views

Slow application performance [on hold]

We are experiencing slow application performance on a couple of apps. We are on SQL Server 2012 Standard Edition 64 GB. This is installed on a Windows 2012 R2 server. The database is without mirroring ...
4
votes
4answers
544 views

Delete into new table (like select into)

The select into syntax will create a new table to insert data into (I know that types may not be consistent etc. but it gives you a rough copy). Is it possible for a delete output into statement to ...
0
votes
0answers
22 views

Normalizing a database [on hold]

I have been developing a social network . I am in the part of normalizing the database.. All I need is to know what transitive dependency really means. Most sources do not explain it as well as they ...
7
votes
3answers
208 views

In what cases a transaction can be committed from inside the CATCH block when XACT_ABORT is set to ON?

Edit After getting first answers to this question I realised that I should narrow it down and changed the title of the question. Sorry for confusion. I've been reading MSDN about TRY...CATCH and ...
3
votes
1answer
60 views

Having hard time understanding expressions in below query plan

I was going through Temporal tables new features in SQL 2016,one of the examples was to query tables with as of clause which returned below query plan Rows Executes StmtText StmtId NodeId ...
3
votes
1answer
41 views

Fill Factor: Will the fill factor change automatically after it detects the changes in the index pages?

So lets say that the default fill factor is left at 0 initially. Data has been loaded up into the server and one fine weekend, the index rebuild job is run. Now if it detects any new data which must ...
4
votes
1answer
42 views

Blocking Extended events

I have very basic table CREATE TABLE [dbo].[Table_1]( [Test] [int] NULL) INSERT INTO [dbo].[Table_1]([Test]) Select 1 sp_configure'block process threshold', 1 --> 1 sec go To create ...
3
votes
0answers
36 views

How to deal with Users Needing to Enter Mixed Data Types in MS SQL Server

I have a form in a C# application that I can't change with a data table bound to a query. The user enters a mark for a student's task or 'A' for absent. However, the person who designed this had the ...
0
votes
0answers
40 views

Retrieving the latest forecast

I am writing an application which stores weather forecasts for different cities, and each day there is a new forecast for each city. I want to be able to provide daily forecasts, and also the latest ...
3
votes
1answer
47 views

Error 909 restoring a database on Standard Edition

I ran into a problem with SQL Server 2012 Standard Edition (64-bit). I have rebuilt a system with the exact same specifications for software as it had been built previously (Windows 2012, SQL Server ...
0
votes
1answer
43 views

Select Into removes IDENTITY property from target table

I am doing an exercise which removes the IDENTITY property from the target table when using a select into statement. source table name: Sales.Orders column name: orderid column property: int ...
3
votes
1answer
37 views

Installation of SQL Server 2014 fails on Windows Server 2012 R2

I am attempting to run a silent installation of SQL Server 2014 with SP1 on Windows Server 2012 R2 core install, although it also fails when using UIMODE="EnableUIOnServerCore": (01) 2016-02-05 ...
5
votes
2answers
687 views

Maximum SQL Server memory set to 12 GB, but it still goes over 12 GB. How do I prevent SQL Server going over max memory limit?

User 'mrdenny' wrote that after you set SQL Server maximum memory... Now this isn't going to limit all aspects of SQL Server to that amount of memory. This only controls the buffer pool and the ...
18
votes
1answer
138 views

Setting BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE for BACKUP command

I am looking for practical guidance for setting the values for the BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE of the BACKUP command. I have done a bit of research (see below), I have done a bit of ...
4
votes
3answers
192 views

Add autoincrement to existing PK

I created a table in a DB that already exists in another DB. It was initially populated with the old DB data. The table's PK had to receive the values that already exist on those records, so it ...
5
votes
2answers
65 views

Transaction Log Backups Serial or Parallel?

We happen to be using SQL Server 2012 Standard Edition. I also happen to use Ola Hallengren's scripts to provide an easy, more flexible framework for doing backups and maintenance. This question ...
0
votes
0answers
15 views

Check for impersonated users using Policy Management

All: I apologize in advance for my poorly worded question, but I'm very new to using policy management and was wondering if there is a way that you can check for impersonated users using Policy-based ...
4
votes
1answer
35 views

SQL Server 2008 R2 services not coming online

I am working for a critical account and yesterday I got a weird issue after applying cumulative update package 14 on top of SQL Server 2008 R2 Standard edition. Before applying CU-14 package on the ...