This tag is specifially for the R2 version of Microsoft's SQL Server 2008.
1
vote
2answers
32 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
...
0
votes
1answer
33 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 ...
2
votes
3answers
32 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
26 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.
5
votes
0answers
43 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
67 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
0answers
38 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?
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 ...
2
votes
3answers
49 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
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 ...
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? ...
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
1answer
62 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
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 ...
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 ...
-1
votes
2answers
107 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
64 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
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
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
135 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 ...
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' ...
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 ...
9
votes
5answers
249 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 ...
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
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 ...
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 ...
1
vote
1answer
35 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 ...
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 ...
0
votes
1answer
67 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
104 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
54 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
70 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
125 views
How to speed up creating clustered index on large table on SQL Server 2008 R2?
I have a large SQL Server table, the row count of the table is more than 3 billion, the data space for this table is about 120G.
And Intel Xeon CPU E5645 @2.4GHz(2 processors), 24 CPUs, 64G memory, ...
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 ...
0
votes
3answers
101 views
Logging queries and other T-SQL in SQL Server 2008 R2
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?
1
vote
1answer
151 views
SQL Server Insert Into #temp table 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
87 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
65 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
26 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
33 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
52 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 ...
1
vote
1answer
51 views
Can I drop and recreate a system extended stored procedure?
I am having a problem with one of my servers where I cannot execute xp_cmdshell under certain conditions. The full problem can be found here problem running xp_cmdshell when Application Name set in ...
7
votes
2answers
291 views
Updating a table with more than 850 million rows of data
I have been tasked with writing an update query to update a table with more than 850 million rows of data. Here are the table structures:
Source Tables :
CREATE TABLE [dbo].[SourceTable1](
...
1
vote
1answer
51 views
How do I specify a linked server in SQL Server 2008 R2 to a remote database over tcp/ip?
I have a remote database running on a IAAS provider and I need to be able to execute joined queries from my local workstation. I am having trouble because when I attempt to create the link it is ...
0
votes
1answer
42 views
Accidentally Deleted SQL Server Log Reader Service Account [closed]
Uh, I accidentally deleted the Security Login in my SQL server that allows for the reading of log files.
Now, I can no longer see any of the log files in my SQL Server Logs node of SMSS:(
What ...
1
vote
2answers
118 views
Clustered vs Nonclustered Index
My database currently has a primary Key/Clustered index on the ID column for each table. However, the application that connects to the database is always looking at views defined as:
SELECT * FROM ...
