This tag is specifially for the R2 version of Microsoft's SQL Server 2008.
2
votes
0answers
6k views
SQL Server named instance - can connect remotely, but not locally
I have an instance of SQL Server on my machine.
Machine: Bert01
SQL Server instance: Bert2008
I connect to it using SSMS putting Bert01\Bert2008 in the Server name box, using sa and the .
When I ...
0
votes
1answer
30 views
Moving one TempdB on 3 instance server
I have a large virtualized SQL Server (Full 2008R2).
I run 3 SQL instances and would like to relocate the TempdB database file to another location, splitting off from the TempdB log file.
The trouble ...
1
vote
1answer
16 views
Table restraints to limit value insertions based off of other values in a row
Using SQL Server 2008 R2
Is it possible to limit what values are allowed in a coulumn based off of other values in the row.
Ex:
myTable:
ID, Test_mode, Active
1 , 1 , Null
2 , 0 , 1
...
2
votes
3answers
47 views
Auditing specific database events in SQL Server
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
3answers
66 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
3answers
29 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. ...
0
votes
2answers
69 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
29 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 ...
0
votes
1answer
24 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
68 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
0answers
42 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 ...
2
votes
1answer
25 views
Full text catalog
I am using SQL Server 2008 R2.
I need to find when and which user created full text catalog.
also, who added/modified/deleted the fields(that were full text index) in that full text catalog.
2
votes
0answers
37 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
48 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?
1
vote
3answers
106 views
SQL Server BPA 2008R2
I have been using SQL server BPA for getting good information from Microsoft. I was using its 2005 version in which i used to export the results in csv format but recently I got two new servers which ...
3
votes
2answers
65 views
Load Testing Crash Course
Can someone point me at a good resource to get a crash course in load testing. I have a sql server machine that I need to load test but I havent been thruogh the process before. Also we do not have ...
4
votes
1answer
38 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 ...
5
votes
1answer
61 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 ...
3
votes
0answers
55 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? ...
-1
votes
1answer
25 views
Why do i get this error…“directory lookup for the file .mdf failed with the operating system error 3” from a log shipping configuration
I have configured log shipping for all our SharePoint 2010. All worked well since Friday and today Monday I ran this query
SELECT *
FROM [msdb].[dbo].[sysjobhistory]
WHERE [message] like '%Operating ...
21
votes
4answers
1k views
Still wrong to start the name of a user stored procedure with sp_?
One of my co-workers named a stored procedure in our SQL Server 2008 R2 database 'sp_something'. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this ...
2
votes
2answers
517 views
Can I add “Included Columns” to an index without affecting performance?
I have a non-clustered index with 63 Million leaf level rows. Currently it does not have any included columns. I would like to add one included column while the site is online. Will this significantly ...
-1
votes
1answer
50 views
Object name 'Clean Up History' in use when trying to complete the maintenance plan wizard in SQL Server 2008 R2
I am trying to create a maintenance plan on a instance running SQL Server 2008 R2 SP1 (no CU's installed).
When completing the wizard I get the following error:
The object name cannot be changed ...
1
vote
1answer
25 views
mdw_purge_data_ long runtime SQL Server 2008 R2
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 ...
0
votes
2answers
92 views
List all permissions for a given role?
I've searched around all over and haven't found a conclusive answer to this question.
I need a script that can give ALL permissions for an associated role.
Any thoughts, or is it even possible?
...
1
vote
1answer
59 views
dead lock when updating
Update Operation.TrTable
Set RecordId = RecordTABLE.newRecordId
From Operation.TrTable tr
Inner Join (
SELECT r.Id AS newRecordId, r.KeyM AS MappingKey
From ...
1
vote
1answer
62 views
SQL Server 2008 R2 Policies, Facets and Conditions
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 ...
0
votes
0answers
41 views
Hierarchical data best practices
I'm looking for a best practicies\databases to solve our problem:
Currently we are using SQL Server 2008 R2 with HierarchyId. It works ok, but we have a lot of performance penalties due to this ...
3
votes
2answers
189 views
Cannot generate reports from SQL Management Data Warehouse
I'm running SQL Server 2008 R2 and have installed the MDW on one server and have a Data Collector collecting and uploading the server activity, query results, and Disk activity data to the MDW. When I ...
3
votes
1answer
162 views
Applying user-defined fields to arbitrary entities in a SQL Server database
Currently we have an old (rather crude) system that has user defined fields, which are mapped against rows in arbitrary tables. This was an after-the-fact modification based on a customer request, and ...
0
votes
2answers
33 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
votes
2answers
106 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 ...
3
votes
1answer
59 views
While restoring copy of SQL Server 2008 backup file in SQL Server 2008 R2 degraded performance
I am trying to upgrade our SQL Server version from SQL Server 2008 to SQL Server 2008 R2. But when I restored a SQL Server 2008 backup file in SQL Server 2008 R2, it's significantly degraded the ...
4
votes
1answer
63 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
...
1
vote
2answers
2k views
Connect to SQL Server Management Studio over VPN (Hamachi)
I just got my Hamachi VPN set up. For anyone familiar with Hamachi, I have it set up as a gateway so I'm part of the network when I'm away. Almost everything seems to be working perfectly. I can ...
1
vote
1answer
82 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
134 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 ...
6
votes
1answer
282 views
Restore a Database to a New database (T-SQL)
I have a database backup from a database, say SourceData which needs to be restored on another database, same schema, but different name (SourceData_1) using T-SQL. I don't know why I gets the ...
3
votes
1answer
25 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 ...
2
votes
2answers
66 views
Trying to write a system function SQL Server 2008 R2
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 ...
3
votes
2answers
125 views
SQl server indexes file damaged, was on ramdisk!
I have put some of my indexes into a file-group that contains one file, that file is on the ramdisk, the performance goes fire!! but the problem is that the file has deleted by wrong (The file ...
9
votes
5answers
246 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
41 views
Disable 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 ...
1
vote
1answer
116 views
error in Execute SQL Task with parameter
I have an Execute SQL Task object in the Error handlers section of a SSIS package. What it does is inserting a record to a table using OLE DB connection. There is a value in the insert statement that ...
1
vote
0answers
208 views
Login failure when running a SSIS package from a SQL Server job
I have a SSIS package that migrates data from a Sybase database to SQL Server. The connection to the source database has been made through an ODBC connection.
If I run the package directly on my ...
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 ...
4
votes
1answer
72 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 ...
3
votes
1answer
51 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 ...
1
vote
1answer
34 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 ...
2
votes
1answer
594 views
How to Convert Horizontal to Vertical Array?
I need to create a query (suitable for Standard Edition) that has data from multiple columns (Columns 1-6 with corresponding Date Started and Date Completed data) displayed vertically, but also has ...
