SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

5
votes
2answers
3k views

Setting up filtering for server-side tracing

I'm trying to set up a server-side trace in SQL Server 2008 R2. While I have no trouble selecting events and data columns, the filtering seems a bit odd... I was trying to set up a filtering on both ...
4
votes
1answer
2k views

SSIS Lookup Component Irregularities

I'm seeing odd behaviour in a dataflow with a lookup operation. If I select 'full cache' mode, some portion of the lookups are failing and I have no explanation why. 'No Cache' works correctly, as ...
2
votes
1answer
14k views

What is different between SQL-Server versions 2008R2 and 2012

Dear SQL Server experts, Can you tell me the different between SQL-Server 2008 R2 and SQL-Server 2012 versions? I designed my product with version 2008 but when I tried to buy a licence, Microsoft ...
5
votes
1answer
1k views

How does SQL Server choose an index key for a foreign key reference?

I am working with a legacy database that was imported from MS Access. There are about twenty tables with non-clustered, unique primary keys that were created during the MS Access > SQL Server upgrade. ...
3
votes
1answer
1k views

Can I Rebuild indexes in a DB except for one table?

I have a table in my database that is constantly monitored by an application server for changes. Unfortunately rebuilding the indexes connected with this table breaks the app server's connection. This ...
4
votes
2answers
8k views

SQL Server 2008R2 - Why is my index not used

I have a table defined in the following way: CREATE TABLE [dbo].[MyTable] ( [MyTable_ID] [int] IDENTITY(1,1) NOT NULL, [COLUMN_WITH_DATA] [varchar](128) NOT NULL, [COLUMN_A] [varchar](128) ...
2
votes
3answers
2k views

How to get SQLCMD to output errors and warnings only?

How can you get SQLCMD, when executing a SQL script file, to just output any errors or warnings it encounters? I essentially dont want information based messages to be output.
1
vote
1answer
318 views

syscacheobjects is cleared randomly

Microsoft SQL Server Web Edition (64-bit) Version 10.50.1600.1 SQL Server 2008 R2 I have noticed that we are getting a lot of execution plan re-complilcations - first executions quite slow, ...
1
vote
0answers
650 views

Create stored procedure to select the data?

I have four tables user, shops, offer, merchant in that id is unique and present in all table shop ------ id shops 1 shop1 1 shop2 1 shop3 user ----- id name 1 abc merchant ...
2
votes
1answer
5k views

Reconcile users with logins using 'sp_change_users_login'

Sql Server 2008 (and 2005): I have a Sql Server instance, let's say, InstA, in a Domain (called AD). In that I have a domain Windows Authentication Login, called AD\Log1 that has an associated user, ...
-2
votes
1answer
4k views

This version of Microsoft SQL Server Management Studio Express can only be used to connect to SQL Server 2000 and SQL Server 2005 servers

I have already SQL Server Management Studio 2008R2 on my local machine. I just installed SQL Server Management Studio 2005 and wanted to connect for the first time. After going through the following ...
1
vote
1answer
887 views

Return a percentage of the full record set

I'm pulling back a list of orders but I need to only display a percentage of the records. for example we want the top 30 or top 50. The percentagbe is variable so I'd like to not use TOP. I tried ...
5
votes
4answers
12k views

Copy Database option not found in SQL Server Management Studio 2008 R2

I am unable to see the Copy Database option in SQL Server Management Studio 2008 R2. Can anyone tell me whats going on please?
2
votes
2answers
813 views

Plan Cache Pages by Database in SQL Server 2008R2

Is it possible to get a break down of how many plan cache pages are being used per database? Perfmon seems to only have the total for the server, but maybe there is a DMV with more detail? Also, does ...
4
votes
1answer
4k views

Database file size from sys.master_files and sys.dm_io_virtual_file_stats are different

Im using query to find out database file size. I'm using system view and DMV, because i do want to find out both- actual file size and theoretical (in case of sparse files) file size. Select ...
5
votes
2answers
1k views

Massive peer-to-peer replication topology?

We have a client that's geographically distributed in remote communities, with somewhat unreliable network/internet connectivity between each of the physical locations. There is a single central ...
1
vote
1answer
2k views

Self blocking session

we are experiencing unresponsive Sql Server 2008 R2 Web Edition. The only way how to connect to the server is via DAC. The dm_os_tasks return 539 tasks almost all SUSPENDED or PENDING. There is one ...
1
vote
2answers
1k views

Copy Database wizard fails but leaves no text file log

I've been using the copy database wizard to create a development copy of my database. Unfortunately, the copy database wizard has repeatedly failed. I suspect the problem is that the sql server ...
3
votes
2answers
1k views

Design best practices for last tracking with indexed view

My tables structure is below : TbDoc (ID int , ...) TbDocActions( ID Int, DocID Int, Date DateTime, col1 int, col2 int, ...) I want to have indexed view to get last TbDocActions columns for ...
2
votes
1answer
2k views

User in multiple windows groups mapped to sql logins

I have two C# apps with Windows Auth to Microsoft SQL Server 2008 R2. For every app: SQL login based on Windows Group is created SQL server login is mapped to SQL database user user is member of a ...
1
vote
2answers
610 views

SQL Server 2008 R2 recognizes a domain account as a SQL Server account

After migrating from SQL Server 2000 to SQL Server 2008 R2, we have a few domain accounts that appear to the new server as SQL Server accounts. These accounts can SELECT from tables in the database ...
7
votes
1answer
15k views

SQL Server 2012 performance v SQL Server 2008 R2

Does anyone have any links to performance benchmarking of the database engine, or any anecdotal reports of the performance improvements in SQL Server 2012. We are only using the DB engine,not ...
2
votes
2answers
5k views

Recover registered servers in SQL Server Management Studio 2012

My computer died so it's be reinstalled. Previously I had SQL Server 2008 R2 installed. On the new system I have SQL Server 2012. I have the old RegSrvr.xml file (that contains the list of ...
2
votes
1answer
3k views

Prevent a single query from hogging all resources

I have a production database server running SQL Server 2008 R2. How can I keep a user query on one database from hogging all the system resources and bogging down all the databases on the server? The ...
2
votes
1answer
582 views

SQL 2012: Import DB changes original data scheme

I'm migrating data from SQL 2008 R2 Servers to SQL 2012. Therefore I'm using SSMS Import Data Task on the SQL 2012 machines. As a test I copied the data files of one of the DBs, did an "Attach ...
3
votes
2answers
305 views

SQL database backups methods-tools-procedures

Currently, I am investigating some way to more efficiently backup 4 Production SQL Databases (3 different servers/instances) and would like to know if anyone else has had the problems we are having ...
5
votes
1answer
832 views

Custom aggregation for non-leaf level nodes to avoid double counting

I have a fact table with the following columns: WorkerName OrderId NumberOfPackagesPerOrder Note that neither WorkerName nor OrderId is unique in this fact table. Multiple users could work on a ...
1
vote
1answer
1k views

SQL Server 2008 R2 : multiple files and filegroup

I am getting bit confused regarding advantages of multiple files and filegroups in SQL Server Using SQL Server 2008 R2, I have following filegroups and files configuration Drive D: Primary Filegroup ...
8
votes
2answers
13k views

Create new function by code if it doesn't exist

I want to create new function by script in my database. The script code is below: IF Exists(Select * From sys.sysobjects A Where A.name =N'fn_myfunc' and xtype=N'FN') return; CREATE FUNCTION ...
5
votes
1answer
333 views

difference of left join and left outer join

In SQL Server join syntax we can use LEFT JOIN or LEFT OUTER JOIN and query result with each of above join not difference. this is a question for me that which are more useful.
2
votes
1answer
2k views

Cannot CAST '33.33' to INT

I have encountered a problem where the WHERE clause is causing a problem. The varcharColumn is of datatype VARCHAR and contains numbers as well as text. The query looks to be averaging this column, ...
3
votes
2answers
1k views

Tools for Identifying Needed Indexes

I want to create the best indexes for each table in my database. Is there a query or tool in SQL Server to help with this process?
2
votes
1answer
325 views

Calculate number for each row

My table structure is below: CREATE TABLE [ACC].[Document]( [DocumentID] [int] IDENTITY(1,1) NOT NULL, [Date] [date] NOT NULL, [SalesCompanyFinancialPeriodID] [int] NOT NULL, ...
5
votes
2answers
832 views

Release SQL Server Memory usage

How can release memory usage of sql server by command. my system have 16GB RAM, but when get huge query by sql server, memory usage become full. if I restart SQL Server service memory usage become ...
8
votes
1answer
2k views

Recursive CTE performance

Need help with recursive CTE performance. Below CTE is running very slow as it is trying to pull heirarchical data recusively. Table is big with every root id having upto 3 recursive itemid. There ...
2
votes
1answer
814 views

Ordering with custom paging when using pivot to convert a query

This is a follow up to my question on changing a flat result set into a different column set using Pivot. Scripts for test DB can be found in that link Ordering works just fine like this: SELECT ...
1
vote
1answer
704 views

How many files should I create for one filegroup and 9 weekly partitions?

I have 9 weekly partitions on one filegroup. How many files do I need to create? There are 3 tables containing 46 million, 16 million and 10 million rows.
5
votes
1answer
248 views

Electricity off while shrinking database, how can I recover my database?

While i execute shrink database, the electricity has been gone down, I can't access my database now, it is (In Recovery), how can i recover it?? Note: it is a very huge database: 25 GB. EDIT: My ...
32
votes
3answers
3k views

Unexpected scans during delete operation using WHERE IN

I've got a query like the following: DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN ( SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL ) ...
2
votes
1answer
2k views

SSIS PL/SQL task hangs with message “Multiple-step OLE DB operation generated errors.”

I've configured a Microsoft SQL Server 2008 R2 SSIS package with an Execute SQL Task to run a PL/SQL query on an Oracle 10.2 database. The Execute SQL Task (named "SQL Update MY_STATUS flag") is ...
2
votes
0answers
379 views

What is causing all of the msrepl_tran_version columns to become out-of-sync between locations in transactional replication setup?

I have been having a recurring issue with transactional replication setups at the firm I work for. We use SQL 2008 R2. Seemingly without warning or any replication errors, all of the ...
9
votes
2answers
10k views

View delete statements in the Transaction log

Is it possible to view delete statements that have recently occurred in the Transaction Log?
4
votes
1answer
1k views

Does SQL Server evaluate functions once for every row?

I have a query like this: SELECT col1 FROM MyTable WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) BETWEEN col2 AND col3 ; This gives a tooltip on the execution plan ...
7
votes
1answer
7k views

Why does SQL Browser service get disabled?

I have an intermittent problem with a software installation package that installs our product (written using InstallShield/InstallScript). During the process of the installation, we restart the SQL ...
6
votes
1answer
530 views

Is there a way to DENY CRUD operations on connections from linked server?

I have a SQL Server instance, RPT1 (2008 R2), that was set up for "power users" to run their own queries. This server is linked to our production reporting server, PRD1 (SQL Server 2008 instance), ...
2
votes
1answer
4k views

Windows could not start the SQL Server service error

I have a problem when I run my web project. I get this error : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was ...
16
votes
2answers
6k views

Restore database excluding FILESTREAM data

Context We are developing a system with a large-ish database in the bottom. It is an MS SQL database running on SQL Server 2008 R2. The total size of the database is about 12 GB. Out of these, ...
2
votes
2answers
3k views

Replace cursor with set-based approach

I am looking to replace my cursor-based solution if possible in a particular stored procedure. If it makes any difference, this is running on SQL Server 2008 R2. I am looking more for an algorithm ...
2
votes
1answer
3k views

Connection to localhost dramatically slower than to network server

I have an Excel VBA application that connects to a SQL Server 2008 R2 database through ADODB. When I'm on the network at work, the connection is almost instantaneous. When I am at home working with ...
4
votes
1answer
843 views

Transaction log growing on a mirrored principal

I have a pair of SQL Server 2008 R2 servers hosting a synchronously mirrored database with no witness. This weekend, we had to do some network maintenance so I paused the mirroring. Upon resuming, the ...