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)

4
votes
2answers
6k views

Why is SQL Server memory showing in task manager

I have worked with SQL Server for a while now, and I understand that SQL memory is inaccurately reported by the task manager. However I have recently been doing some testing on SQL Server that ...
7
votes
4answers
8k views

SQL Server - Separating data, log, and TempDB files on a SAN

I have a SQL Server connected to a SAN. Our new storage vendor recommends that all LUNs span the entire disk array, which is RAID5. I would normally request 3 separate LUNs (data, log, and TempDB) ...
0
votes
2answers
95 views

How to organize a single query to several sql servers?

I need to make an inner join sql query to several SQL Servers (one is 2000 and another is 2008 R2). The query is like the following: SELECT k.ID, k.NAME, k.BIRTH, l.PROFILEID FROM CUSTOMERS.....
2
votes
3answers
6k views

How to import table's data in MySql from Sql Server?

I am trying to export table from SQL Server 2008 R2 TO MySql 5.5. For this I am using SQL Server Import and Export Wizard of Sql Server 2008, but it is giving this error. Here this error may be ...
4
votes
2answers
3k views

Subquery in the VALUES clause

Here's a real-world schema: CREATE TABLE [dbo].[Setting] ( [ID] [BIGINT] NOT NULL, [Module] [NVARCHAR](400) NOT NULL, [Property] [NVARCHAR](400) NOT NULL, [Value] [...
4
votes
1answer
1k views

How do I create a CLR UDF with external name specifying namespace, class, and subclass?

I am creating a CLR UDF using external name and second level nested class names seem not to be permitted. Is this the case? When using the following syntax and including a first level class name and ...
2
votes
1answer
489 views

Named Pipe Provider Error code 40 [closed]

I have literally tried everything, from enabling named pipe to adding exception to ports in the firewall, to everything possible in surface configuration. I can connect to the SQL instance(using TCP ...
3
votes
1answer
79 views

Locating row in index page

In clustered index, we have root, intermediate and leaf level pages. Every page has few records where indicating range of pages from level below. How SQL check whether some index key belong to ...
1
vote
1answer
1k views

Add Check Constraint On Varchar Column to enforce no duplicates

I have a varchar(max) column where users will populate a pre-given key from another system. I want to enable that the users are forced to enter a unique key which means no duplicate values on that ...
0
votes
1answer
370 views

What is the significance of root and page level fragmentation in a clustered b-tree index?

In investigating fragmentation on some of our large tables in a SQL Server 2005 database, I was using a query based on sys.dm_db_index_physical_stats. The query returns a row for each level in the b-...
7
votes
2answers
7k views

Transaction Log maintenance on Mirror database

SQL Server Version: 2008 R2 Enterprise SP2 I am trying to get a handle on our SQL Server maintenance and I came across something I think is incorrect. We have a single production instance with 3 ...
4
votes
1answer
328 views

What are the best practices for backing up a secured SQL Server 2012?

I want to state right off the bat that I'm not looking for information on how to backup a SQL Server. I've got that taken care of. I'm looking for some form of documentation that will tell me the ...
2
votes
1answer
5k views

How to include the value of the query parameter in the email body using sp_send_dbmail

I am calling sp_send_dbmail and setting the query parameter. The resulting email includes the results of the query. I want the actual query (the value of the query parameter) to be appended to the ...
7
votes
2answers
1k views

Does SQL Server use pointers instead of storing duplicate rows?

I'm using the built in sp_spaceused stored procedure before and after performing a operation in our software to see which tables have row inserts and how the size of each table changes. What I'm ...
3
votes
2answers
3k views

Database mirroring - link failure between principal and witness

I am using synchronous database mirroring with a witness and am testing the various failure scenarios and the impact on the mirroring session, such as whether failover occurs. Starting with the ...
11
votes
1answer
11k views

Difference between Owned Schemas and Role Membership for Microsoft SQL Server

Are there differences between Owned Schemas and Role Members? From my understanding, the Role Members are compulsory - user need to have at least one of the check box under Role Members checked. ...
4
votes
1answer
310 views

Understanding WAIT_TIME_MS

Test scenario: I am only one user on database. I reset sys.dm_os_wait_stats. I run a SELECT query (table scan). Result: Execution time = 20 minutes, but sys.dm_os_wait_stats -> PAGEIOLATCH_SH ->...
12
votes
1answer
22k views

The login already has an account under a different user name

When I execute this SQL: USE ASPState GO IF NOT EXISTS(SELECT * FROM sys.sysusers WHERE NAME = 'R2Server\AAOUser') CREATE USER [R2Server\AAOUser] FOR LOGIN [R2Server\AAOUser]; GO I get the ...
6
votes
1answer
11k views

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself

I encounter the following error: Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. when I was trying these commands USE ASPState GO GRANT ...
11
votes
3answers
7k views

Choosing the right algorithm in HashBytes function

We need to create hash value of nvarchar data for comparison purposes. There are multiple hash algorithms available in T-SQL, but which one the best to choose from in this scenario? We want to ensure ...
4
votes
1answer
997 views

Query records that chain together based on date gaps

I have a table in SQL Server 2012 with the below simplified schema and I need to define a query that grabs the most recent record and then returns the chain of records that have less than 30 days of a ...
2
votes
2answers
6k views

SQL Server - backup / restore schema only

We have a DB on over 100 servers of which we want to backup / restore the structure of just 1 at a time to a specific UAT database. They're all called the same thing but vary in terms of schema - ...
5
votes
2answers
13k views

Automatically restart SQL Server 2008

Is there a "best" way to automatically restart SQL Server on a regular basis? I read that I could create a batch file to net start, net stop the agent and service in a batch file and schedule that, ...
3
votes
2answers
4k views

Partitioning on a single filegroup

I have some very large tables in my database, but a substantial chunk of this data is "old". Due to circumstances beyond my control, I am not allowed to remove this "old" data. The other ...
2
votes
1answer
61 views

Advanced column in MS-BIDS

Because we are having the same problems as in http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/e51b775a-8601-491b-8ad4-a71b0f0d59ce we have the following question: That is when ...
4
votes
2answers
303 views

How can FLOOR(3) equal 2?

I'm trying to find a reliable, efficient expression to calculate how many decimal digits it takes to write a positive integer. Mathematically, the number of decimal digits in an integer n is 1 + ...
3
votes
1answer
826 views

SQL Server 2012 defrag job runs interactively, but not as a job - why?

I have recently started using the dba_indexDefrag_sp stored procedure from SQL Fool (http://sqlfool.com/2011/06/index-defrag-script-v4-1/) against my production databases running on SQL Server 2012 (...
4
votes
3answers
1k views

Twice job results in sql server, last one is left in progress

I have the job MP - Create CSV which execute a package SSIS in sql server 2008, when I check for the job history I see the result at step 0, I expand and I have 2 results for the step 1. Fist result ...
7
votes
1answer
6k views

How can I query for existing database snapshots in SQL server?

I'm trying to create a t-sql query that can determine whether or not a given database has any database snapshots that were created from it. For example, if I were to create a snapshot like this: ...
6
votes
2answers
3k views

SQL Server 2005 database corruption. Advise

I have encounter some database corruptions in the past but I knew which object has hit the road. In this situation I run a DBCC CHECKDB against a problematic SQL Server 2005 database and I get the ...
2
votes
1answer
478 views

SQL Data Collector - SSIS required on source server?

For SQL Data Collector: Do you need SSIS (SQL Server Integration Services) installed on the server that you are collecting data from. Or can the packages be run from another server -- like the one ...
0
votes
2answers
4k views

How to set the timeoffset in a Datetimeoffset column without changing the date part

Is there any way to set the timeoffset of a datetimeoffset column directly without having to use SWITCHOFFSET which changes the date as well? In the post below, we can see the SWITCHOFFSET will ...
2
votes
1answer
117 views

Is it a best practice to contrast table and dependent view metadata to target which views to refresh?

We have non-indexed views created on vendor tables. When they change table structure or column definitions we run sp_refreshview as needed. What is the viability of a script to contrast the table ...
2
votes
1answer
158 views

Are there differences in performance of getting data between FullText Search and indexed columns

In SQL Server 2008 R2 I have a query which looks like create stored procedure give_me_art @filter varchar(15),@skl_id int begin select id,naziv,sifra,isnull(lager.kolicina,0) as lager from art ...
2
votes
2answers
3k views

how does SQL Server 2008 handle joins internally?

for example, I have tables: User and Order User Table: UserId, Name, Address, Phone, Age Order Table: OrderId, UserId, Date, Item, Quantity SQL: select User.Name, Order.Date From Order JOIN User ...
0
votes
1answer
1k views

Query notifications and connection options

I'm investigating a problem with query notifications. The platform is SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Standard Edition. .NET errors show a problem reported from SqlNotificationInfo ...
16
votes
1answer
13k views

Trigger in combination with transaction

Suppose we have the following situation: We have a table (let's say Table_A), wich has a trigger on INSERT. The trigger job is to update some rows in table_B based on the inserted values in table_A. ...
3
votes
2answers
4k views

Log all errors in queries

I have a database Microsoft SQL Server 2008r2 I have some applications in different programming languages. Some of them are legacy and I would not like to modify. Is there a way to log at server level ...
4
votes
2answers
966 views

Are there problems with using SQL Server AlwaysOn Availability Groups with Windows 2008R2?

[Cross post from StackOverflow] Brent Ozar published this webcast (slides, video with free registration) entitled "Real-Life SQL Server 2012: AlwaysOn Lessons Learned", in which he repeatedly states, ...
3
votes
1answer
797 views

What are the implications of changing the SQL Server Service Logon Account?

What are the implications of changing the SQL Server Service Logon Account? My default instance currently uses Network Service. I want to change this to a domain account. Are there any implications?
1
vote
2answers
460 views

Move named instance to another server

To be honest I'm not a DBA expert. I get asked by one of my clients in terms of moving a SQL Server development instance that is stored in the production server (source) to another server (destination)...
1
vote
3answers
130 views

Live monitoring SQL statements on SQL Server

How can I monitor running SQL Statements for my SQL Server, is it possible using only SQL Server without 3rd party tools? As an alternative can anybody recommend any free tool which can monitor ...
7
votes
2answers
10k views

Resetting a SQL Server 2012 sequence

I'm in the process of testing and populating a specific table that leverages the SEQUENCE object. In this process I'm testing populating the table with tens of thousands of insert lines (as I'm ...
5
votes
2answers
12k views

Find orphaned users

In SQL Server 2005, is there a way to find users that either don't exist at the server level (an account that was deleted at server level but wasn't disassociated from databases before it was deleted) ...
6
votes
1answer
1k views

dbo.DTA_* tables in MSDB

I ran Brent Ozar's sp_blitz on one of my production server. IT tagged a few tables in the MSDB database as being user created. They all follow the pattern dbo.DTA_*. Do these table come with MSDB, ...
3
votes
2answers
14k views

ORDER BY clause is allowed over column that is not in SELECT list?

In theory, every statement in SQL Server create virtual table passed to next statement. In other words, when FROM and Where clause are finished, next step is SELECt columns from virtual group created ...
20
votes
4answers
15k views

Can I launch a stored procedure and immediately return without waiting for it to finish?

We have a stored procedure that users can run manually to get some updated numbers for a report that's used constantly throughout the day. I have a second stored procedure that should be run after ...
6
votes
3answers
307 views

Everyone warns against shrinking a database, but will it work in my case? 1 GB db with tables removed now 100 MB

Everyone is warning against shrinking a database, e.g. SQLAuthority But will it be a valid option in my case? The database was originally more than 1 GB, but with a table removed from it (moved to ...
5
votes
1answer
167 views

Safe to kill SPID as long as its a SELECT

We just ran into a problem, where a server process running a select was blocking our entire public website. What we ended up doing was killing the server process. My question is: Is it considered ...
3
votes
1answer
756 views

SQl Server 2008 R2 - sa can login but not access database

Up until this morning I had no problems accessing our database. This morning, something has changed and I can no longer access our database (called EMS_Main) using the sa login. I can log into the ...