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

learn more… | top users | synonyms (2)

4
votes
1answer
383 views

Can I decrypt TDE transaction log files for use with 3rd party software?

Is there a way to decrypt TDE log files on disk for use with 3rd party software? I have the original key and certificates. I am trying to use the transaction files with a 3rd party tool in order to ...
2
votes
2answers
950 views

View column has an 8000 character limit?

In SQL Server 2008 R2 we are using an updateable view to update a varchar(max) column. When we update the column with text and then check the number of characters, it is showing a length of 8000 ...
0
votes
1answer
5k views

How to do an in-place upgrade of SQL Server 2005 Express to SQL Server 2008 R2 Express

I would like to upgrade my SQL Server 2005 Express instance which is installed using this command: SQLEXPR.EXE/qs /action=INSTALL /IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQLEngine /INSTANCENAME=...
1
vote
2answers
463 views

Update my local db based on scripts executed in my server database

I need to create an solution that update my local db version based on changes that was made on my server database. First, I need to check the differences that has exists between these databases and ...
1
vote
2answers
4k 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
1k 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 ...
5
votes
1answer
293 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
3k 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 ...
-1
votes
1answer
1k views

Query returns no data [closed]

I am running a query to show LastName and Hire_Date and my results display the columns but do not include any data. This is an assignment for an online class and I have been waiting for my instructor ...
0
votes
1answer
2k 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 ...
5
votes
1answer
4k 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
345 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?
4
votes
2answers
6k 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
71 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
2k 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
51 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
411 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 ...
0
votes
3answers
11k 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
107 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
1k 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
3k 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 ...
4
votes
1answer
31k views

CREATE DATABASE permission denied in database 'master'

I've developed a desktop application using WPF and Entity Framework code-first. In my application the user can create multiple databases at run-time. But Entity Framework can't create database if ...
-1
votes
1answer
951 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
1k 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
224 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
593 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
412 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
231 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 ...
2
votes
1answer
245 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
180 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 ...
7
votes
1answer
1k 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
415 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
12k 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 ...
10
votes
2answers
19k 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 ...
3
votes
4answers
2k 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
2k 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
4k 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 '...
11
votes
5answers
2k 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
293 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 ...
6
votes
1answer
3k 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
1k 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
739 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 ...
3
votes
1answer
19k 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
275 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 ...
3
votes
1answer
13k 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 ...
3
votes
4answers
11k 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 ...
3
votes
2answers
856 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
215 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 ...
5
votes
4answers
15k 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?