This Tag is for SQL Server 2008 R2, major build version 10.50.xxxx
-3
votes
1answer
23 views
What should be Performance Tuning Approach?
As a DBA when the client reports a performance slowness issue what should be the Approach in solving the issue.
Meaning that we can not always suspect the Database for performance slowness, many a ...
1
vote
1answer
35 views
Can you grant an Active Directory Group rights on a linked server?
I am trying to grant rights to an Active Directory Group on a Linked Server. I am unable to do it; it only allows me to grant rights to individual active directory users. Does anyone know how to ...
1
vote
0answers
34 views
Record versioning and promotion
Let's say we have this hierarchy:
-World
--USA
---WA
----Seattle
-----Downtown
------1st Ave
-------945 1st ave
------3rd Ave
...
1
vote
2answers
47 views
Can I trust the Database Engine Tuning Advisor recommendations?
Our database has become large, and we are trying to optimize performance.
Can we follow the recommendations provided by the Database Engine Tuning Advisor by supplying it a .trc file generated with ...
-1
votes
4answers
71 views
Dropping and recreating indexes [closed]
I most often come in a situation where the users move there database from one server to another and immediately after they move they face performance problems(slowness).
I somehow manage to rebuild ...
2
votes
1answer
41 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 ...
1
vote
2answers
58 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 ...
1
vote
2answers
55 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 ...
2
votes
2answers
45 views
Restore database created in SQL Server 2008 R2 to SQL Server 2008 R2 SP2
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 ...
2
votes
1answer
54 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
76 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
...
3
votes
6answers
107 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
29 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 ...
2
votes
1answer
70 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), ...
0
votes
1answer
31 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?
2
votes
2answers
55 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
43 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 ...
4
votes
3answers
64 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
30 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
66 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
92 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 ...
2
votes
1answer
48 views
The Login is from an untrusted domain - domain setup with computer level login
I have a Sql Server 2008 R2 database I'm attempting to connect to using ADO.NET using integrated security in the connection string.
The database resides on a 64 bit Windows Server 2003 machine. The ...
0
votes
1answer
49 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?
4
votes
1answer
43 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
77 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
33 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 ...
3
votes
0answers
62 views
When SQL Server uses Multi Page Allocation
In SQL Server 2008 R2
I always wonder what can be consumer of MPA (multi page allocation) in SQL Server? I know data page is always 8K so is there a situation where data/index page make use of MPA? ...
0
votes
1answer
29 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
3answers
71 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 ...
5
votes
1answer
68 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
1answer
70 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
30 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
2answers
111 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 ...
4
votes
1answer
78 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
35 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 ...
1
vote
1answer
84 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 ...
5
votes
2answers
149 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 ...
1
vote
2answers
91 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
99 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
78 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
374 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 ...
0
votes
1answer
48 views
Disabled trigger still fired
I've just run the following t-sql to disable a trigger (with a deploy script wrapper around osql):
disable trigger dbo.trExampleTableInsert ON dbo.ExampleTable
In SSMS I could see the trigger was ...
0
votes
0answers
29 views
Deleted a file accidentally, I need to remove it's entry, I don't need it's data because it contains only indexes [duplicate]
I have a C# program to deal with a big database, I need the databas not to be the buttle-nick of my program, in the database, to increase performance, I have created a filegroup with a single data ...
3
votes
1answer
52 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 ...
4
votes
1answer
78 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
36 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
134 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 ...
0
votes
1answer
76 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
126 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
56 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 ...
