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
1k views

Grant a login of Sql server authentication the same permission of a Windows group login with Windows authentication?

Update: I am trying to make it easier for the login to have the permissions on multiple databases. I already have a Windows group login with Windows authentication. The Windows users in the group can ...
1
vote
1answer
285 views

How to find what prevented in getting a lock?

Sometimes my update statements fail after 3000 milliseconds with 'Lock request time out period exceeded' error. That 3000 limit is coming from my application through SET LOCK_TIMEOUT 3000 setting, ...
0
votes
1answer
754 views

Kerberos Authentication Issue

I'm getting these errors when testing a linked server from SQL Server 2012 to SSAS 2012 (both servers are Windows 2012 and clustered) "An error was encountered in the transport layer" and "The peer ...
3
votes
2answers
206 views

Restore database to a instance in network

We are creating a application which requires sql database as backend to store data. So when user install my application he chooses a sql instance and its details. There we plan to restore the database ...
2
votes
1answer
193 views

Time Boxing SSIS Task

I've an SSIS task which download a couple of files,update various Database tables and archive the files(i have it split in various packages). The average time to completion is about 2.30 min. However,...
1
vote
2answers
355 views

Create users in SQL Server 2012

I created a new login and a new user and I mapped a database in SQL Server 2012. In the login properties in Securables, we selected permissions "deny" for "View any database" for the user to see only ...
19
votes
3answers
2k views

Index on Persisted Computed column needs key lookup to get columns in the computed expression

I have a persisted computed column on a table which is simply made up concatenated columns, e.g. CREATE TABLE dbo.T ( ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY, A ...
2
votes
1answer
146 views

How can I change Referential Integrity from a Unique Key Constraint to a Primary Key?

(without dropping foreign keys) I have migrated a SQL Server CE database to SQL Server 2012, however, referential integrity was enforced via unique key constraints on the surrogate INT identity ...
0
votes
1answer
122 views

In sys.dm_os_memory_cache_entries view, what does original cost mean?

Looking at a couple of DMVs, and trying to see if I can programmatically figure out the cost of the cached plans in my SQL Server. I'm hoping that column represents the same concept of cost that is ...
5
votes
1answer
763 views

Passing a literal ? to sp_MSforeachdb

I am trying to search for the ? character in a string, but across all dbs on a server. Since the ? mark is a placeholder for the database name, how may I format the query so it knows I mean to search ...
1
vote
1answer
318 views

Should I extract / normalize the mailing address despite requirements limiting number / type of addresses?

I currently am working on a contacts application (similar to Outlook contacts combined with eVites and LinkedIn). The requirements for the application are to limit the addresses (and phone / email ...
1
vote
2answers
9k views

SQL Server 2005 Express on Windows 8

I need to install SQL Server 2005 Express on Windows 8 Home Edition x64. But I get a compatibility error! I have tried SP1, SP2, SP3 of SQL Server 2005. Visual Studio 2008 is working fine. Any ...
3
votes
2answers
16k views

How to Fix log_reuse_wait_desc=LOG_BACKUP? LOG Backup not possible

I checked sys.databases to find out which databases need LOG backups. It surprised me that I had quite a few where log_reuse_wait_desc=LOG_BACKUP meaning, the JOB I have set up to run LOG backups on ...
1
vote
1answer
952 views

Securely deleting data from SQL Server logs

Is there a good way to purge data from a particular DB's *.LDF? A little background... I'm a SysAd, not a DBA. I'm running SQL Server 2008 R2 for one of my applications and I'm not particularly ...
0
votes
3answers
540 views

Force spool to avoid creating temp tables?

I have the following query which join pivoted data of several very big tables and it runs very slow. I tried to created the temp table for each pivoting and it runs much faster. Is it a way to force ...
1
vote
1answer
92 views

Can I change the default settings for SQL Server Replication

Is there a way to change the default setting in SQL Server replication? Example when I create an new article I want to have the create Non-Cluster set to true all the time.
3
votes
1answer
5k views

How to figure out what a DTSRun command is doing

I have taken over a SQL 2000 database. I am trying to figure out what a job is doing. Its command is: DTSRun /~....a big hex string.... I have tried to run: DTSRun /~....a big hex string.... /!Y ...
15
votes
3answers
26k views

How do I move SQL Server database files?

I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server. How can I do this?
6
votes
2answers
318 views

How to optimize NOT IN statement

Why does NOT IN performs INDEX SCAN instead of INDEX SEEK? Here's my current index on the table, CREATE NONCLUSTERED INDEX [idx_dmcasarms_CourseList_cDesc] ON [dbo].[courselist] ( [c_desc] ASC,...
0
votes
1answer
70 views

Is there a quantitative method to follow to determine FILLFACTOR for NONCLUSTERED INDEXes?

I've read different ideas to set FILLFACTOR and then monitor fragmentation over time (#1: 5 Things About Fillfactor, #2: Database Maintenance Best Practices Part II – Setting FILLFACTOR). Instead of ...
4
votes
4answers
995 views

Is database partitioning for performance reasons meaningful in a virtualized environment?

When running SQL server on physical hardware, placing database data and log files on separate disks or RAID arrays can benefit performance, resilience, and maintenance. Partitioning the database over ...
0
votes
2answers
264 views

Sql Server Agent fails to run batch from desktop

I’m using SQL Server 2008 and running a batch file from SQL Server Agent. The batch file is located in my desktop. When I try to run the job I get an error: “access is denied.” The batch file runs ...
6
votes
1answer
631 views

DTUTIL performance slower in SQL Server 2012?

We've noticed that since deploying Windows 2012/SQL Server 2012, the performance of DTUTIL has decreased. DTUTIL.exe consumes 50% on each of 2 cores on an 8 GB VM. The same syntax against Win 2008 R2/...
2
votes
0answers
251 views

Is it possible to configure Reporting Services to emit browser compatibility headers?

We have a dashboard that hosts some Reporting Services (2008) reports within frames. The dashboard runs in the browser's standards compliance mode, but this is causing some of the reporting services ...
5
votes
2answers
8k views

Limit CPU usage without Resource Governor

We have an SQL Server 2008R2 Standard Edition with several databases belonging to different applications on a 16 core server. One recently introduced application is regularly executing expensive ...
8
votes
2answers
3k views

Error running Powershell Script in SQL Server Agent

I have a SQL job that runs via SQL Server Agent (Running SQL Server 2012 Enterprise). The last step of the job is to run an application located on a network share. Unfortunately I don't know the name ...
1
vote
3answers
245 views

How to create implicit select statements

I need to create an implicit select statement for each table with all of its coluns - i.e. SELECT COL1, COL2, COL3 FROM TABLE1 instead of SELECT * FROM TABLE1. There are too many tables to use the ...
0
votes
1answer
437 views

Data Sync between servers with different schema in SQL Server [closed]

I have two servers 2008 R2 and 2005. The e-commerce site has been running with 2005 Server. I have migrated 2005 server data with modified data schema to 2008 R2 from yesterday's date of data. My ...
1
vote
1answer
63 views

Adding the domain account to a security group on the SQL Server computer that has sufficient privileges to log on as a service

After reading this article, http://www.red-gate.com/supportcenter/content/knowledgebase/SQL_Backup/KB200710000173 I have some problems configuring point 2) and 3): 2) Create a SQL Server login that ...
0
votes
1answer
1k views

Query that only uses index takes long time to execute [closed]

I have a table with ~69,000 rows. I have this very simple query: -- SET STATISTICS TIME ON SELECT ID, FirstName, LastName FROM Customers -- SQL Server Execution Times: CPU time = 31 ms, elapsed time =...
4
votes
2answers
4k views

Log sizes unexpectedly large: log_reuse_wait_desc == “log_backup”

I've been reading posts and manuals all day, I need help. My problem: transaction log sizes after data and transaction log backups will not shrink, and grew to be unexpectedly large. The system is ...
0
votes
2answers
611 views

SQL Server compact- Query that return 2 constant columns even if query didn't return data

Please look at the following simple query: Select CustomerID From Customers c Where c.CustomerId = "1234" I want to write similar query that does the following: Always return 2 columns with ...
2
votes
2answers
773 views

net start sql service with startup option

From this article, I know to that slash (/) is used instead of a hyphen (-) when a net start is used with startup option, net start mssqlserver /m But if I want to have to use with startup option ...
1
vote
1answer
637 views

SQL Server replication - set PollingInterval in sp_addmergepushsubscription_agent

I am trying to create replication subscriptions to and SQL Server 2008 R2 via a script. There are 3 subscribers so this script creates the subscriptions by calling sp_addmergesubscription and then ...
0
votes
1answer
1k views

Create table from stored procedure result

I am new to this field if you can please tell me if it is possible to do this it will be very help full. I have SQL Server 2008 R2. What I want to do is from my table I want to take first row and ...
2
votes
1answer
465 views

Primary Key Constraint Violation in SSIS with Quotation Mark [closed]

Using SSIS Data Flow Task to transfer records from table in 1 SQL Server database to an IDENTICAL table in another SQL Server (2012) and getting "Violation of PRIMARY KEY constraint 'FLG_APVN_KEY_0'. ...
6
votes
1answer
2k views

Advisability of using STATISTICS_NORECOMPUTE

I've recently become involved in maintaining a set of databases with some interesting index problems. One of the ones that aggravates me the most is the differences in the indexes between development,...
2
votes
0answers
376 views

Problem with Database for survey Questionnaires

Bellow I am showing a sample survey question for woman. I need your help to creating a Database. Description: Top part of the survey Questions are Project Code, Name... etc which is ...
3
votes
1answer
8k views

Nonclustered Index still doing index scan

I have this simple query. SELECT a.RecordID SubmissionID, a.[DateSubmitted], a.[FinalAction], a.[FinalActionReason], a.[FinalActionDate], b.GradeSubmissionListID, b.[Action], b....
3
votes
3answers
6k views

What ports does SQL Server need to communicate with a web server?

If I have a web server trying to communicate to a SQL Server through a firewall, and the firewall is completely closed both ways (inbound AND outbound), I know I have to open port 1433 from the web ...
4
votes
4answers
9k views

Mirroring vs. Log Shipping

The software company I work for is planning to migrating our infrastructure into the cloud. Our current focus is to find the best database replication method. Current Servers: SQL Server 2008 SE - ...
0
votes
1answer
45 views

What Type of Join to Use in this Search?

I have the following query that is bound to a datagrid: select distinct a.Person_ID, a.Person_Last_Name, a.Person_DOB, CASE WHEN a....
5
votes
1answer
268 views

SQL Azure not using available memory

We are using SQL Azure Premium P1 which comes with 8GB ram and 150 IOPS. For a couple of hours, we have been seeing timeouts on SQL queries (even fairly simple queries take time). In logs for SQL ...
3
votes
2answers
353 views

Alert doesn't work?

I've created an alert for long run queries. However, the history is always empty even I set the value to 1(second). The following is the generated script. Is there anything wrong? /****** Object: ...
4
votes
2answers
248 views

Can a single SQL Server instance issue the same SYSDATETIME twice?

I am contemplating using a datetime2(7) as a clustered index. If I were to always populate that column using SYSDATETIME is it possible for the same instance to issue the same value twice (assuming ...
3
votes
4answers
3k views

~2 Hours after a large insert SQL Server gets “I/O requests taking longer than 15 seconds to complete”

This problem has been haunting me for a while but I actually fixed the queries that caused the problems (after I finally found them) but i'm curious if it's an SQL Process that's the underlying ...
2
votes
2answers
1k views

Huge disk space usage increase when altering a column

I hope this question is easy for someone to answer :) I have a table that looks like this (unimportant columns hidden) CREATE TABLE [dbo].[Log]( [LogID] [int] IDENTITY(1,1) NOT NULL, --...
1
vote
1answer
1k views

Getting File not found error doing point in time database restore in SQL Server 2008

So I'm testing out some of our DR stuff and tried to restore a point in time backup from one of our main databases to a new database on the same server. I am admin on the box and I'm RDP'd into the ...
1
vote
1answer
244 views

SQL Server 2008R2 Cluster: Spread tempdb over several files AND shrink original single file

On my SQL 2008 R2 active/passive cluster, I've received an error message regarding I/O requests on tempdb.mdf taking longer than 15seconds. Googled that and it as suggested I spread tempdb over ...
3
votes
0answers
1k views

Once more about SQL Server virtual accounts

Let me reboot my question on SQL Server virtual accounts permissions The (same) MSDN article on the same version 2012 of SQL Server "Configure Windows Service Accounts and Permissions" states: ...