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

why the optimizer doesn't choose the optimal plan [closed]

I know, if the statistic is not updatad, it is possible, that the optimizer doesn't choose the optimal execution plan? Does any other reason/reasons exist?
3
votes
2answers
394 views

Getting all databases which a sql logon can connect

I need to know all databases in a SQL Server on which a user can connect. With sys.databases I get all databases and with sys.server_principals or sys.syslogins I get all server logons. But I can't ...
2
votes
2answers
46 views

Advantage of exposing data model to customers using our product

We have a product through which we provide a standard definition to customer's asset data. The problem currently lies in importing customer's data from different types of data sources to our data ...
0
votes
2answers
243 views

Receiving the following error in step failure of an agent job:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x00040EDA Description: "Warning: Null value is ...
1
vote
0answers
53 views

Create Nested XML in a Sql Server Indexed (Materialized) View

This page https://msdn.microsoft.com/en-us/library/ms188276.aspx indicates that if you wanted nested XML (ie an XML Tree) then you need to setup your query like this: SELECT Col1, Col2, ...
0
votes
1answer
175 views

Analyse and report disk usage growth historically

Is it possible to use historic information to show disk usage growth? I haven't keep much in the way of database setup and disk usage information. So I can't say how much disk growth has occurred in ...
1
vote
1answer
130 views

Another Dynamically alias column question

I might be asking the same question as this, but perhaps if I ask it my way, a different solution may become obvious. I have this: DECLARE @Table1 AS TABLE( Table1ID Int Primary Key ,ColumnName ...
0
votes
1answer
144 views

Switching a partition on a table with one to many relation

There is two main tables in database (SQL Server 2014) table1: ---------------------------------------------- id name datetime image table2: -------------------------------- ...
1
vote
1answer
304 views

SQL Service Broker and AlwaysOn Issue

I'm having an issue setting up my current environment using AlwaysOn and SSB and I'll try to explain as best as I can here: Current environment: Server A Server B database x(Pub) ...
4
votes
1answer
534 views

SQL Server : parsing with XPath Performance

I have a UI that allows a user to select certain data points they want to filter the results by. There are things such as location, department etc. Once they have selected all of the options, I ...
1
vote
2answers
439 views

Dropping person while their connection sleeping in SQL Server

I have a C# application that communicates with SQL Server. When I try to drop a user that recently had a connection with SQL Server, it gives me an error that I can't delete them because they have ...
4
votes
2answers
2k views

SSRS Blocking Processes

We have a busy SSRS Reporting server (SQL Server 2008 R2) on which we see frequent blocking between the below processes: [dbo].[CheckSessionLock] [dbo].[WriteLockSession] We are aware that some ...
5
votes
1answer
167 views

Query looks optimal but logical reads is huge

I run the following (really simple) query in SQL Server: SELECT MAX(PK_Field1) FROM MainTable kh WHERE kh.PK_Field1 >= '2014-12-01T00:00:00' AND kh.PK_Field2 = 1572 AND ...
0
votes
0answers
301 views

How to clean DBMIRROR_EVENTS_QUEUE process on broken mirror?

We setup mirror (on SQL Server 2008 R2), but it fails during the setup. After that the mirror was moved on another server. My problem is that it left some process, because on that server I see active ...
2
votes
3answers
62 views

SQL Server uses only PRIMARY partition by default

My database has 2 partitions files: PRIMARY, DATABASE_ARCHIVED. A lot of tables have a column "archived". When archived is 1 row is in the DATABASE_ARCHIVED partition. When archived is 0 row is in the ...
2
votes
1answer
2k views

Where is SQLCMD.EXE in SQL Server 2014 LocalDB or how to install it?

On a machine with SQL Server LocalDB 2014 installed I am looking for SQLCMD.EXE file but cannot find it anywhere. Is there a way how to install it separately? Unfortunately I am not local admin of ...
5
votes
3answers
10k views

I need the reason why we should not install SQL server on the domain controller

I am a system administrator for a small firm. Now we consider to rollout Windows Server 2008 R2 for the firm. We plan to implement Active Directory. We would also like to upgrade RDMS, from SQL server ...
0
votes
1answer
38 views

Grant Select on some columns for specific user

I have a windows group with 20 people and all 20 people in the group were revoked access for some columns in the table. Now I need to add the select permissions for 2 of the users in this windows ...
2
votes
2answers
249 views

Running a T-SQL job under another user

I cant find my proxy user to "Run as" that user !? I am confused which kind of proxy user is appropriate to run a job with a simple T-SQL command. (I have created a credential and proxy but cant find ...
0
votes
0answers
56 views

mdf files too big in SQL Server 2005 [closed]

I hope someone can advise me on what to do here. I am running my database on SQL Server 2005. I had created a backup maintenance job myself using the SQL Server Management Studio, when using a ...
14
votes
2answers
6k views

Select all records, join with table A if join exists, table B if not

So here's my scenario: I'm working on Localization for a project of mine, and typically I would go about doing this in the C# code, however I want to do this in SQL a bit more since I am trying to ...
0
votes
1answer
27 views

Questions concerning performance/production resources where exporting / importing big data sets

I have a MS SQL Server database that contains data for many customers and I am working on a feature to export a single customers data. I have basically created a script that generates inserts for all ...
2
votes
2answers
205 views

is there a way to configure Azure SQL databases for high-insert scenarios?

I'm working on a legacy application that uses SQL Server as a backing store. There's a portion of the app that does a ridiculous amount of inserts to a single table. Re-architecting this portion of ...
3
votes
1answer
839 views

Get IO stats request sql like activity monitor

Is it possible to get the same value as in the activity monitor (SSMS) for the "Database IO" as a result of a SQL request? click to enlarge
2
votes
1answer
51 views

How to generate an error when backing up to an existing file

I'm using SQL Server's BACKUP command and I want the operation to generate an error if I attempt to write to an existing backup file. By default, SQL Server seems to append to an existing file. ...
0
votes
2answers
214 views

Query to get data in one status not in another status

I have a table with status column with different status values like 'A','C','E','I'. My data is like this: ---------------------------------------------- TagNo TruckNo VerifiedDateTime ...
2
votes
0answers
207 views

SSIS: FTP task - concurrent connections

I have created an SSIS package for downloading file from FTP server. When I debug the package, everything goes fine. But when I shedule the package as Job step, it sometimes fails to connect. The job ...
0
votes
0answers
41 views

SQL Server consuming large amount of RAM [duplicate]

I have Server 2012 with two 2.10 Ghz processors and 64GB of RAM. I have SQL server 2008 installed on this server. but this SQL server is consuming 70-80% of memory. I have limited SQL memory to 24GB ...
0
votes
1answer
50 views

need to find instances which have multiple rows with the same userID and ResourceID and have a location of 1 and also have other locations [closed]

I have a table with User_ID, Resource_ID, Location_ID, INstance_ID 1,1,1,1 1,1,2,2 1,1,2,3 2,2,3,4 2,3,3,5 2,3,5,6 3,1,2,7 3,1,7,8 4,4,1,9 4,4,2,10 5,5,1,11 1,1,3,14 4,4,1,15 1,1,10,18 1,1,1,19 ...
1
vote
4answers
152 views

Time sheet, rates, date dependent

I am trying to figure out the best database design for the following; I have individuals (stored in Agents table) that enter their billing or time worked each day for different projects. Now we have ...
2
votes
0answers
308 views

Transactional Replication in SQL server 2014

We need to set up transactional replication in SQL server 2014, Enterprise edition so that: Database to be replicated from Publisher which is approximately 2 TB in size. This would be used 24*6. Here ...
3
votes
1answer
381 views

Storage size of a bigint table

I am working with a table that has this format : CREATE TABLE dbo.ID_STORE ( WORKING_ID bigint PRIMARY KEY CLUSTERED ) The table stores around 2 millions rows, but the stored ids are not ...
4
votes
1answer
397 views

MS SQL high tempdb disk I/O during merge replication of BLOBs

Having a merge publication for replicating BLOBs (sql type - image), got very high tempdb disk I/O for my size of data. Publication is download-only and has no filters. High disk I/O is caused by ...
1
vote
3answers
2k views

MS SQL Server 2008 Transaction log file back up failure

I've done some reading around this subject here and on StackOverflow but nothing really answers my question enough for me to go forward. I'll explain we have a MS SQL 2008 server running, the ...
6
votes
3answers
3k views

Check if any of the values is in a subquery result

I have a complicated subquery that returns a list of order IDs. I need to get a list of customers who have these orders. The problem is that there are two ways of assigning a customer to an order (one ...
1
vote
1answer
107 views

How do I optimise XPath Querying over a large dataset?

I have a table that has 220 M rows. Also the table has a xml column that has XML data. I have configured single file for data and another ndf for index. Table's primary key is guid column. When am ...
2
votes
2answers
587 views

Sql Agent Jobs executes too slow

I have posted a question like this before but is seems that I did not explain the whole story clear. So I made this one to explain it in details. Here's the stored procedure USE [xxxx] GO /****** ...
0
votes
2answers
45 views

SQL Server 2008 R2 Replication not working - my 1st try

I am having difficulties when I attempt my first shot at SQL Server 2008 R2 Replication. Looks like I have login issues. How do you modify settings without having to start a new plan again? Please be ...
-4
votes
0answers
81 views

Not able to attach database backup file .bak into SQL Server 2008 [closed]

I have a database backup file named iyym.bak. How can I attach the database to my SQL Server 2008? I tried inside SQL Server Management Studio the following: right click on data base folder in ...
-1
votes
2answers
3k views

sending email using database mail

How do I use database mail in SQL Server? I have followed many articles on the forums but I failed with many errors. I read in one book that I must have SMTP server and Microsoft Exchange installed ...
4
votes
0answers
79 views

MS SQL Server IO read increase appear randomly [closed]

The number of read (IOPS) in my MS SQL Server database increased randomly even during the night (low activities) from 0.3 MB/SEC (normal) to 25-30 MB/SEC. After like 30 minutes, it's capped to 6 ...
1
vote
1answer
417 views

Migrating DB, SSIS packages, SSRS Reports from 2008 R2 to 2012

I have been given the task of planning our BI department's infrastructure upgrade from SQL 2008 R2 to SQL 2012. This process will include updating our data warehouse (database), reporting ...
2
votes
2answers
288 views

Backup to local and MIRROR TO Azure Files returns ERROR

I have an Azure VM with SQL Server 2014, with a local drive (X:) for storing backups. Using Ola Hallengren's scripts. I added an Azure Files share and tried to do a mirror backup, but I get the ...
1
vote
1answer
91 views

Do I need a separate server for Quorum?

We are working on setting up AlwaysOn Availability Group for SQL Server 2014. There will be 2 nodes: Primary Replica and Secondary Replica. The question is, do we need a separate server for Quorum? ...
0
votes
0answers
98 views

how to revoke backup and restore permission from database owner

I have SQL server 2008R2. I grant every user who wants to work on the database the database owner privileges, but recently found out that there is a database backup spam on the server, so I thought ...
0
votes
1answer
3k views

How to convert timestamp / rowversion to a date and time in SQL Server?

How to convert timestamp / rowversion to a date and time in SQL Server? I have several tables where I would like to track the last update, with column names like ModifiedAt, UpdatedAt, UpdatedDate. ...
-1
votes
2answers
195 views

SQL server avoid procedures parameter sniffing

I'm getting slow performances from this procedure CREATE PROCEDURE MyProcedure01 @date datetime2(7) AS BEGIN SELECT [Limit1].[C2] AS [C1], [Limit1].[mc_object] AS [mc_object], [Limit1].[C1] ...
2
votes
2answers
112 views

SQL Server total memory less than target memory, lot of extra memory unallocated

The situation is the following: We have a SQL2008r2 server (64bit on 64bit Windows) with SSRS installed on it. The Max Memory is set to 6GB, the server has 12GB total memory. The target server ...
2
votes
1answer
116 views

Use transaction logs multiple times

Every night a full backup is made of all the user databases. Every 15 minutes, transaction logs are created. Today i succesfully restored a database with the use of transaction logs. I set the time ...
2
votes
1answer
83 views

Advice on transaction log based reporting server

I am in need of advice on setting up a remote read-only MSSQL 2008 reporting server using transaction logs. From what I gather, true TLS setup requires both database servers to be aware of each other ...