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
0answers
474 views

Create transactional publication error in SQL Server 2014

In attempting to create a new Publication for Transactional Replication in SQL Server, I end up getting the following error, with pretty much no way around it: SQL Server could not create ...
2
votes
0answers
240 views

Receiving error when running DBCC CheckDB - multiple IAM pages

Running DBCC CHECKDB (dbname) WITH NO_INFOMSGS, ALL_ERRORMSGS results in this error: Table error: Multiple IAM pages for object ID 813245952, index ID 1, partition ID 72057594073972736, ...
3
votes
0answers
45 views

SQL Server Disk IO Performace appears different between otherwise identical servers [closed]

I have two identical servers each running Microsoft SQL Server and performing a batch processing job. Server #2 keeps timing out while processing while server #1 appears to be running smoothly. I ...
8
votes
2answers
210 views

Getting a realistic query plan when partitioning with low volumes of data

We're using partitioning to reduce the amount of blocking our OLTP system experiences due to locks, with a partition scheme that splits the working tables into 100 partitions based on a customer id. ...
3
votes
1answer
60 views

When are the unnecessary row versions being removed from the version store

Sorry for my bad English :) I need a help with understanding version store`s cleaning. I have the following situation. 12:00 Start a select statement under the snapshot isolation level and not ...
2
votes
2answers
277 views

History of database restore completion times?

I'm trying to estimate the restore time for a few 1TB+ databases on SQL 2012. Is it possible to look at completed restores from the last year and list the completion time ? It's an odd remote server ...
3
votes
2answers
288 views

Verifying connection pooling server side with CONTEXT_INFO on

I'm working with a 3 tiered application, Microsoft Dynamics AX, where the middle tier maintains connections to a SQL Server. Several clients connect to this middle tier server. The middle tier server ...
2
votes
2answers
45 views

Disabling sql services while performing maintenance on SQL Server 2008 R2 cluster

Our storage team is performing updates on an MPIO driver for our SQL Server 2008 R2 cluster. They suggested disabling the sql server service on the node they are doing the update on - will disabling ...
-1
votes
1answer
28 views

SQL statement for comparing actual products and plan products

I need some helps for writing SQL statements to compare count of actual products and plan by like below 2 Input tables: CREATE TABLE PRODUCT_PLAN ( PRODUCT NVARCHAR(50), START_TIME ...
0
votes
2answers
676 views

OLE DB Driver sql 2008r2 to Oracle failing

I have a SQL Server R2 Installation running on Windows Server 2008 R2. I have installed Oracle Instant Client, registered the OraOLEDB.Oracle driver(orasql12.dll), and set the PATH in environment ...
4
votes
1answer
259 views

Secondary replica indexing

I have a question, about a question, that is part of the 70-462 Microsoft exam. I had this question today, and I'm not completely sure of the correct answer. While I did pass the exam, I would like ...
-1
votes
1answer
36 views

Retrieve Column name as Data [closed]

Based on this code below from adventurework, I would like to display the name of the column as a data in the first row and the remaining rows are data of the column. I do not know how to do it. ...
1
vote
3answers
476 views

Is it possible to change recursion level of a CTE in a function?

I have created a function that takes a string and it returns a table with the position and character of each letter in the string. However as CTE recursion is limited to 100 levels, it fails if a ...
6
votes
3answers
1k views

Get MS SQL Server to release disk space

A poorly managed database table has grown to be enormous. 48+gigs of orphan records. I am trying to clean it up and put my dangerously full hard drive back to a normal state. I will be deleting approx ...
1
vote
0answers
46 views

Statement succeeds, data missing [closed]

I have two reports from the same customer, where they claim that an error occurred that implies a SQL Statement didn't execute. These are very simple statements, setting a value in a single column. I ...
0
votes
0answers
66 views

Deep cloning a hierarchical database structure

I am trying to clone a hierarchical structure in my database, and I almost have it working; however I can't seem to get the output clause to access a column from a select statement. This is generally ...
0
votes
1answer
875 views

Invalid object name 'sys.dm_os_volume_stats'

I am trying to select 15% of disk space available in a server. SELECT DISTINCT s.volume_mount_point [Drive], CAST(s.available_bytes / 1048576.0 as decimal(20,2)) ...
4
votes
1answer
224 views

Tempdb change settings and recommendation

In my production database I have below configuration in TempDb and needless to say I am facing a lot of IO issues on the drive . So, as per recommendation I am planning to change it Change #1 ...
3
votes
1answer
391 views

Pagination performance, with subquery, inner join and where

I am trying to optimize following query (this is most simplified version I could came up with): SELECT tr.Id, StatusDate FROM ( SELECT tr.Id, tr.StatusDate FROM mon.ArchivedTaskResults_201504 ...
0
votes
0answers
550 views

DB_STARTUP blocking on spid 7

I have a sql server 2012 instance that I'm installing a new license on that had previously had an evaluation license. I've did this on two other servers with out incident, but this server is giving me ...
4
votes
1answer
220 views

Large Transaction Backup

On my SQL database I run a full backup at 6am and transactional backups every 15 minutes on week days starting at 8am. Every Monday the first transactional backup is 7 Gb. I want to know why, my ...
0
votes
1answer
46 views

Database Default Locations doesn't persist

When I set the default location for the Data & Log in the Server Properties in SQL Server 2012, and click Ok, it resets back to the previous value immediately - if I reopen the Server Properties, ...
1
vote
2answers
1k views

Naming The SSRS Report Name based on the Parameters Passed

I want to save my SSRS Report Based on the Parameters Passed. i.e. Am passing P1 And P2 as parameters so I want my SSRS Report name as P1_P2_ReportName.Xtension. Any suggestions?
0
votes
1answer
94 views

Why temdb and log file size is not reduced by SQL Server

With a series of question Part1, Part2 and Part3. With the answers and research on internet I came to know that TemDb is use for holding different values (ex.: Temp variable, Temp table, CTE, Hash ...
0
votes
2answers
273 views

Index on Column with only 3 status?

I have a table with millions of rows. This table has a column which we frequently filter(means we use it in WHERE clause frequently), the values are Insert/Update/Ignored. Is it make sense to add ...
0
votes
2answers
75 views

Insert query with a subquery

I want to insert somewhere around 400 rows into a table. All of the column values will be the same fixed value for most of the columns, with exception to one column which I want to pull from a fairly ...
4
votes
2answers
216 views

Have full SQL Server restore but losing 1 day of data

I have SQL 2008 R2 64bit and it is backed up with ASIGRA SQL backup. The issue I have is that we had corruption on the server and the DB got corrupted yet the LDF file is in tact and does have all the ...
0
votes
2answers
92 views

How to create a VIEW in SQL Server [closed]

I have a user table and I need to create a VIEW so a user can only see their own data/row. How could I achieve this? I have been searching online but I can't find anything useful User with user_id, ...
0
votes
0answers
291 views

create two foreign key from one table sql server

i am creating user table like code PK Table CREATE TABLE TblUser (UserId int identity primary key ,Name varchar(20)) AND creating one more table FK Table CREATE TABLE TblAnnouncements (Id int ...
0
votes
1answer
94 views

How to enforce that one row in a parent table MUST be associated with at least a row in a child table [duplicate]

I have a one to many relationship enforced via FK, classic example order and order_item, whereby a fk on the order_item to the order table enforces that one item belongs to one order, and one order ...
0
votes
1answer
53 views

how big of a role does hardware play? [closed]

I'm the developer, not the DBA. I don't know much about setting up databases but i'm concern of the setup at my current work. The server is installed on desktop PC hardware(i7, 16GB RAM, 1 TB SATA ...
1
vote
1answer
30 views

SSIS Package Security

I am writing a Package for one of my client according to his requirements. Package development is totally complete now time is to protect it with some security. Is this possible I encrypt all the data ...
1
vote
2answers
94 views

Backup Error (.bak)

I need to backup the database in SQL Server 2014. When I am trying to do so, I am facing this problem which is not saving backup as a (.bak) extension. And when I try to select (Files and ...
0
votes
1answer
68 views

Index rebuild, data compression generated Large TLog

We are following vendor recommendations for a index rebuild changing the fillfactor and enabling data compression, on the test server the results have us wondering what is going on that would cause ...
3
votes
2answers
249 views

SQL Server 2008 R2 - Unable to delete Login

I've been using this site for a while and this is the first time I've had to ask a question, but just wanted to say "Thank You" to everyone who has helped me in the past (without knowing they did so). ...
5
votes
2answers
466 views

Is it possible to change the default style used by CAST/CONVERT when converting DATETIME? [closed]

When you attempt to CAST a DATETIME as VARCHAR in SQL Server, the default date formatting style is a fairly inaccurate date format that loses the precision of both seconds and milliseconds. For ...
2
votes
3answers
164 views

Order by query optimization for multi select data (enum)

We previously used to store multi select data (enum) using pipe separated characters, as shown below like this - which made ordering on that enum data very simple. ╔═══╦════════════╦═════════════╗ ║ ...
3
votes
1answer
208 views

SQL Agent Jobs Automatically Disabled After Modifying Maintenance Plan

I modified a maintenance plan yesterday, to find out this morning (by using the last modified date from sysjobs) that all jobs related to the maintenance plan are disabled. I can't figure out why ...
1
vote
2answers
1k views

Merge faster than Insert

Are there any advantages/disadvantages of making use of a merge over a insert function? Can an insert statement make use of the `OUTPUT' Note: the ON clause for the merge is set to 1=0 (always ...
0
votes
2answers
242 views

SQL Server : replication between two different locations

I have problem setting my replication between two servers on different locations (different network). Steps I have done: setup aliases in SQL Server Native Client configuration on both servers ...
11
votes
2answers
596 views

Help with PIVOT query

I have a table with below structure : CREATE TABLE [dbo].[AUDIT_SCHEMA_VERSION]( [SCHEMA_VER_MAJOR] [int] NOT NULL, [SCHEMA_VER_MINOR] [int] NOT NULL, [SCHEMA_VER_SUB] [int] NOT NULL, ...
1
vote
1answer
111 views

Open port 1433 to use SSMS securely

I have a SQL Server placed in DMZ and I want to give access to internal users (and VPN clients) to use SSMS to remotely connect to it via port 1433 so server will accept the connection only form ...
0
votes
0answers
17 views

Does splitting to multiple databases has performance impact? [duplicate]

I am scaling an old application which was designed to have ten databases. These databases are related to each other. The database is SQL Server 2008. The databases combined size is less than a GB. I ...
0
votes
0answers
62 views

Best method to implement HA and DR, replication via 1 PUB and 2 SUB or replication and LS both

I need a advise on one of our projects where: We have 3 SQL database servers A, B and C: A is the live production server with around 200 users with a database of size 1.5 TB hosted. B is going to ...
1
vote
1answer
145 views

LSCopy job fails on SQL Server 2005 - Insufficient space

I'm having some trouble getting my LSCopy job of Log-Shipping on my SQL Server 2005 server, to work. The LSBackup job is configured so the backup is made locally (D: drive) The LSCopy job is ...
0
votes
0answers
1k views

Sudden High CPU Usage On SQL Server 2008R2

Our SQL Server is experiencing sudden high CPU usage since 12:30am today. This server is used internally so nobody should be accessing the server at that time. I'm not sure why 12 hours later the CPU ...
2
votes
2answers
122 views

Do valid to/from columns on a fact table make sense?

I recently came across "valid from" and "valid to" columns on a fact table. Obviously this is common for dimensions but I've not seen them on fact tables before and can't find any info suggesting ...
1
vote
2answers
313 views

SQL Server database backup to store only the last 7 backups in a file

I want to create a scheduled task that will run (every night) a SQL script to fully backup my database X to a file. I want that file to store up to 7 full backups. The way I tried doing this is using ...
1
vote
0answers
84 views

SQL Server 2012 Security Best Practices [closed]

I'm looking to compile a good list of SQL Server 2012 security best practices. Do you know of any good articles or lists?
0
votes
1answer
4k views

Connection problem different errors .net application

We have a SQL server with some relational databases. We have another webbserver with applications which communicate with the databases. We have an issue with one of our customers and we get some ...