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
1answer
28 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
213 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
878 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
56 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
240 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
224 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
338 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
391 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
406 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
112 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
624 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
84 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
80 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
451 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
294 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
92 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
103 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
209 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
117 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
117 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 ...
1
vote
2answers
4k views

Whats SQL Server NVARCHAR(max) equivalent in MySQL?

Using SQL Server you just have to give the "MAX" parameter to the length of a text data type, but in MySQL there's no such a thing. According to Ispirer: "n" is the maximum number of characters, ...
0
votes
0answers
25 views

Why is the transaction log file not truncated though its simple recovery model? [duplicate]

My database is simple recovery model and when I view the free space in log file it shows 99%. Why doesn't my log file truncate the committed data automatically to free space in ldf file? When I shrink ...
1
vote
1answer
562 views

What is stored in a transaction log file?

What does the transaction log file store? Is it the blocks of transactions to be executed, is it the snapshot of records before beginning the execution of a transaction or is it just the statements ...
0
votes
1answer
45 views

Limit the number of votes

I have a table of content. Users can vote on a content row. They can vote up to three times. I'm looking for the most efficient way to make sure they don't go over three votes. I can think of two ...
1
vote
0answers
326 views

Distributed Replay DCOM issue

I have 2 SQL Servers in a Domain. Windows Firewall is disabled on both servers and there is no hardware firewall. Both servers are in the same subnet. One server, called Controller is configured as a ...
0
votes
2answers
97 views

When doing an update on a set of records, how do i get previous updates to show on current row to update in UDF [closed]

SO i have a rowset of 10 UIDs. They are all dupes, lets say DUPEUID. I have a UDF that will take the UID, check it against the table to see if there is a dupe, and update the UID to DUPEUID01. The ...
2
votes
1answer
62 views

Can't seem to understand why STUFF isn't working

I've got two tables that store questions and possible answers. The questions table can have multiple possible answers to it. I want to display the possible answers it can have per question.. For ...
2
votes
0answers
46 views

SQL Server 2005 Datawarehouse DB - Integrity failure caused by NonClustered indexes

Last year I've been working with a data warehouse database (SQL Server 2005) loaded on a daily basis during the nights. Overall database performance is acceptable. However, there is a "known error" ...
4
votes
3answers
2k views

Error 3203 restoring SQL Server backup: The data is invalid

I am trying to restore a .bak file (backup taken on SQL Server 2008 R2, compatibility level 100). I am restoring the backup on SQL Server 2012 edition. I get the below error. Unfortunately the ...
0
votes
1answer
49 views

Setting Key Constraints using a Diagram

I been using SSMS for diagramming my databases and I got used to create the key constraints with this tool. Now I started thinking, while going through related chapter of a book, "...maybe this is not ...
0
votes
1answer
444 views

Restoring SQL Server 2012 DB - OPEN SYMMETRIC KEY SKey DECRYPTION BY CERTIFICATE NAME Not working

Database Admins, can you please help me out and let me know why my script is not allowing me to DECRYPTBYKEY once I restore my DB from a PRODUCTION BACKUP.... -- SET Staging to Single User Mode to be ...
10
votes
1answer
853 views

Determine the SQL Server version and edition from the available set up, before actually installing it?

We are going through a downgrade process from SQL Server 2008 R2 Enterprise to Standard edition. To kick off the above activity, we were looking for available Standard Edition of SQL Server. We found ...
1
vote
0answers
70 views

Best practices to segment people and attributes for advertising database

we have a database where we store people some attributes per person in the following tables: people (id, name, email, phone) people_attributes (id, person_id, attribute_name, attribute_value) ...
4
votes
1answer
861 views

How to define the logical name in a backup ? ( Sql Server 2008 )

I saw : This question but it didnt help me. In a simple way, Is there a way to do : Backup database SomeDatabase to disk ='c:\teste\SomeDatabase.bkp' configuring the Logical Name? Because we ...
0
votes
1answer
86 views

SQL Server: query for getting TOP X groups of records which have highest subsequent values

I'm querying each SQL Server DB CPU usage (based on query from here -- Get CPU utilization by database (Query 29) (CPU Usage by Database)) every 60 seconds and storing this information in dedicated ...
7
votes
1answer
385 views

Composite Primary Key in multi-tenant SQL Server database

I'm building a multi-tenant app (single database, single schema) using ASP Web API, Entity Framework, and SQL Server/Azure database. This app will be used by 1000-5000 customers. All the tables will ...
0
votes
0answers
71 views

Why are transactions long running?

My stored procedure has a transaction blog. I pick up a record from a database and process it in that transaction block. I noticed that my transaction log has grown in gigabytes. But my transaction ...
1
vote
0answers
87 views

SQL Script - SSMS Good, Job Bad

long time lurker and this is my first post. I have a SQL Server 2005 pro box running a handful of databases. I also have a few scripts I run once a week through SSMS to update data, so I thought I ...
8
votes
3answers
758 views

SSMS Registered Servers with ReadOnly Application Intent

We are working on SQL Server 2014 POC testing with AlwaysOn and one of the user asked about saving the configuration of SSMS with ReadOnly Intent using the registered servers in Local server group. ...
0
votes
1answer
92 views

DB Permission: User can drop only their created db's?

I want to set permission against a user, which can create database and drop on their created databases neither any database. Any idea/suggestion ?
0
votes
2answers
123 views

How can I replace the following query with ISNULL function in SQL?

I have the following query. For performance optimization reasons I have been asked to replace the logic with ISNULL function in SQL. How can I achieve this? please advice. SELECT ...