This Tag is for SQL Server 2008 R2, major build version 10.50.xxxx

learn more… | top users | synonyms

1
vote
2answers
3k views

Restore database created in SQL Server 2008 R2 to SQL Server 2008 R2 SP2 [closed]

I have a .bak file created by some other user in (I think) SQL Server 2008 R2 (10.50) and I need to restore it in SQL Server R2 SP2 (10.52). I used a hex viewer to look into the .bak file and found ...
3
votes
2answers
875 views

Is this a good strategy for importing a large amount of data and decomposing as an ETL?

I have a set of five tables (a highly decomposed schema for an ETL if I understand the nomenclature) that I'm going to load via bulk import, then run some inserts from those five tables into a SLEW of ...
4
votes
1answer
217 views

Is it “legal” to CREATE and DROP #SomeTable more than once?

I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this: CREATE TABLE #WidgetSetting ...
5
votes
6answers
2k views

Backup very large table

I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We ...
0
votes
1answer
1k views

VarChar to NVarChar causing truncate error

I am importing some data from a temporary table which has a column defined as VARCHAR(150). The destination tables column is defined as NVARCHAR(200). I'm using an INSERT INTO statement, but when I ...
3
votes
1answer
2k views

Attaching a database in SSMS gives error

I am accessing SQL Server using SSMS, and a user with adequate permissions. When this user tries to attach a database by using SSMS (by right clicking on database node and then clicking on attach), ...
1
vote
1answer
197 views

Database size not reduced after compressing tables

Using Data Compression, my table size was reduced from 1770 MB to 250 MB, but the database size was not reduced. Why?
3
votes
2answers
3k views

Constraints based on other columns

Is it possible to limit what values are allowed in a coulumn based off of other values in the row? For example, my table: ID Test_mode Active -- --------- ------ 1 1 Null 2 0 ...
0
votes
0answers
64 views

Copy tables from a stand-by database

I want to explain the following. I have a database that is a recovery db from our main environment. This database is kept up-to-date through log shipping. However, I want to copy all tables from this ...
5
votes
3answers
1k views

How to reduce network latency during mirroring?

We are using SQL Server 2008 R2 Standard edition for mirroring. But we found out that network latency is too high. It takes nearly four times time to complete a transaction while mirroring is active. ...
2
votes
1answer
47 views

Audit full text activity

I need to find when and which user created full text catalog, and who added/modified/deleted the columns (that were full text indexed) in that full text catalog.
5
votes
1answer
246 views

SA permissions issues with many nested objects

I have a broker application that's relatively complicated. Today, after I made some changes, I started getting the error: The server principal 'sa' is not able to access the database 'XYZ' under ...
-1
votes
3answers
7k views

How to restore SQL Server 2008 R2 backup to SQL Server 2008 [duplicate]

I am unable to restore a backup from a 64-bit instance onto a 32-bit instance. Source Server: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Destination server: Microsoft SQL Server ...
0
votes
1answer
102 views

Can I use the database and query it while full-text indexing?

I need to index my database, but I need to query it while it is indexing, it may take a long time, can I do that?
6
votes
1answer
645 views

Recommendations on how to organize Queues in Service Broker

I have a service broker application that currently has 5 or 6 queues on two servers. The general workflow is: Server A User populates some tables User fires stored proc which puts a message into ...
4
votes
3answers
2k views

Auditing specific database events

For auditing purposes, we have a requirement in which we have to enable Auditing for theses events: DBA Logins Changes made by DBAs LIKE INSERT, DELETE, UPDATE, ALTER (etc.) Changes made by our ...
-1
votes
1answer
732 views

Directory lookup failed during log shipping

I have configured log shipping for all our SharePoint 2010 databases. All worked well since Friday and today Monday I ran this query: SELECT * FROM [msdb].[dbo].[sysjobhistory] WHERE [message] like ...
4
votes
1answer
871 views

When SQL Server uses Multi Page Allocations

What are the consumers of MPA (multi page allocations) in SQL Server? I know data pages are always 8K. Is there a situation where data/index page make use of MPA? It makes sense that execution plans ...
0
votes
1answer
165 views

distribution.dbo.MSdistribution_history comments explanation

The comment column of distributor.dbo.MSdistribution_history contains comments such as <stats state="2" fetch="11554" wait="214007" cmds="17898546" callstogetreplcmds="106880"> ...
1
vote
5answers
429 views

Document database structure for auditors

I am DBA for a Microsoft SQL Server 2008r2. Due to an audition, I need to provide data structure of my database, to people without an explicit IT background. I guess they would like the traditional ...
6
votes
1answer
293 views

BM25 (full-text search) implementation in SQL Server

I've hit a stump, while trying to implement the BM25 algorithm in SQL Server 2008 R2. I know that SQL Server includes the Full-Text Search option, which already implements a variant of BM25, but I ...
1
vote
2answers
163 views

Use a condition to limit the databases a policy checks

I'd like to create a Policy that checks, on demand, one database in an instance for tables that have no index. I can see how to create a Condition, linked to the 'Database' Facet and checks for my ...
1
vote
1answer
160 views

mdw_purge_data_ long runtime

I have a SQL Server 2008 R2 that is running several databases and has also been configured as our central MDW. When the management data warehouse is created it creates a number of agent jobs one of ...
-1
votes
1answer
152 views

Retrieve 5th highest salary in table [closed]

How to get the 5th highest salary from the table. Suppose, I have 100 rows and I want to display details of 5th highest salary of the individual then how to write that query in T-SQL using SQL Server ...
6
votes
1answer
691 views

When performing a cross-database transaction, in which transaction log(s) is the information stored?

Given the following snippet: -- error checking omitted for brevity begin tran exec database1..my_stored_procedure exec database2..my_other_stored_procedure if (@@error <> 0) rollback ...
0
votes
2answers
254 views

Can I recover CDC tables from transaction logs?

Is there anyway to restore CDC tables from transaction logs? My basic understanding is that CDC reads these logs, and entries that are specially marked, are grabbed by CDC. Once this has happened ...
3
votes
2answers
7k views

Total Memory used by SQL Server (64 bit)

My knowledge on the subject suggests that perf counter SQL Server:Memory Manager: Total Server Memory only gives you buffer pool memory. There is a column called physical_memory_in_use in a DMV named ...
8
votes
2answers
11k views

Maximum memory setting in SQL Server

I'm running SQL Server 2008 and a web based application, on a single dedicated server, with only 2Gb of memory available. As is noted elsewhere, SQL Server regularly takes up to 98% of physical ...
2
votes
3answers
1k views

Working Linked Server Query fails in sp_send_dbmail

Take the following example: EXEC msdb.dbo.sp_send_dbmail @recipients = 'me@whatever.co.uk' , @query = 'SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE' , @attach_query_result_as_file = N'True' ...
0
votes
1answer
1k views

Real-time merge of two databases on different servers

One of the requirements our customer has given is to merge two databases running on two different machines in real time. This means that we have to build some sync policy (or use SQL Server tools to ...
2
votes
2answers
2k views

Trying to write a function that doesn't require dbo. prefix

I am trying to write a simple system function so that the dbo. prefix is NOT required to access the function. I have found an example of this for SQL Server 2000 but when I run EXEC SP_CONFIGURE ...
9
votes
5answers
1k views

Force sql server to run query conditions as written?

Im using Sql server 2008 R2 And I have this pseudo query (SP) : select ... from ... WHERE @LinkMode IS NULL AND (myColumn IN (...very long time exeted query...)) ... ... The ...
4
votes
1answer
272 views

Is there a behavior change between SQL Server 2000 and SQL Server 2008 R2 OPTION (FAST 1)?

This is question 2 of 2 related to the FAST query hint. Background Information (as an aside, I'm not trying to garner more rep, I'm trying to be true to only asking one question per question). Our ...
5
votes
1answer
1k views

How does OPTION (FAST 1) actually interact with a client?

This is question 1 of 2 related to OPTION (FAST 1); We've just upgraded our ERP database from SQL 2000 EE to 2008 R2 EE and we've been noticing increased blocking in the database. I've narrowed it ...
1
vote
1answer
644 views

Publishing stored proc execution in transactional replication

In SQL 2008 R2, I am going to update a table which will affect 25 million rows, table is currently replicated (transactional replication). To minimize the impact on replication can I create a stored ...
4
votes
1answer
564 views

Indexes file damaged, was on ramdisk

I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file ...
1
vote
1answer
10k views

How to find which tables and views a user has access to?

We have a SQL Server 2008 database and have restricted all tables and views away from a specific user ID. Over the years we have granted back tables and views one at a time based on user need. We ...
1
vote
1answer
1k views

Upgrade SQL Server 2005 Enterprise to SQL Server 2008 R2 Standard

I understand that this is not a supported path, but does anyone have any insight about how to accomplish this? I can't seem to find any documents on the subject. I need my QA (2005) to match my ...
5
votes
1answer
214 views

Transactional replication creating a fixup index that's sleeping, but blocking other processes

I've just migrated my ERP database server from SQL Server 2000 to SQL Server 2008 R2 (both Enterprise Edition) and that migration went fairly smoothly. However, now that I'm trying to recreate ...
1
vote
1answer
8k views

Unable to connect to SQL server 2008 R2 via Instance name?

I don't understand : I've installed sql server 2008 R2. the installation process asked me for the desired instance name , so I wrote: MSSQLSERVER. after the installation , running Setup Discovery ...
1
vote
4answers
5k views

Speed up large clustered index creation?

I have a large table, the row count of the table is more than 3 billion, the data space for this table is about 120 GB. And Intel Xeon CPU E5645 @2.4GHz(2 processors), 24 CPUs, 64G memory, 64bit ...
2
votes
2answers
541 views

limit the number of rows returned when a condition is met?

Is it possible to limit the number of rows returned when a condition is met? I am working on a query to check if a student is ready to graduate, and they need to meet a certain number of credits per ...
5
votes
1answer
190 views

Fulltext stoplist replication

In MS SQL Server 2008 R2 there is replication of table with fulltext index on it. But stoplist that is associated with replicated fulltext index doesn't replicate. Is there any possibility to ...
3
votes
4answers
7k views

Logging queries and other T-SQL

I would like to know if SQL Server 2008 R2 has a default logging scheme for SELECT statements (or any other T-SQL for that matter). If yes, where can I see it? If not, how could I set it up?
2
votes
2answers
3k views

INSERT INTO #temp taking 1000 times longer than SELECT INTO #temp

I am shredding an xml blob that is being passed to me and loading a temp table for further processing. One of the things I noticed while working through the process is that performing an INSERT INTO ...
2
votes
2answers
452 views

Can view adapt to implicitly reference user's default schema?

A bit of background. I have a base application and most clients use it as standard. However some clients have small code and database customisations. Each of these clients has their own branch and ...
0
votes
1answer
257 views

How to store a sequence of events related to a transaction

I am using Sql Server 2008 R2. I have two tables that describe import processes and export processes. Import table has these columns with others that just have details about each type of import ...
1
vote
1answer
117 views

Tips for catching changes in table data over time interval for export to Data Warehouse

I've recently been tasked with exporting data from one of our databases to a central Data Warehouse. The export is supposed to be incremental since a full load every 24 hours would be a massive waste ...
1
vote
0answers
57 views

Slow Response: Initially it's slow and on subsequent restart of the host it works normally

We have SQL Server 2008 R2 32 bit installed on the following hardware: CPU: Intel Xeon E5310 @ 1.60 GHz RAM: 4 GB HDD: Raid Server: HP Proliant 120 OS: Microsoft Windows Server 2003 R2, Standard ...
1
vote
2answers
132 views

Rebuilding, truncating etc SQL Server 2008 maintenance

About a month or so ago I inherited this somewhat-new database. I was lead to believe that everything was set up and all maintenance tasks were running without a hitch. SQL Server 2008 R2 I was ...