This tag is specifically for the R2 version of Microsoft's SQL Server 2008.
2
votes
2answers
36 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 ...
4
votes
3answers
73 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 ...
3
votes
7answers
101 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 ...
5
votes
1answer
131 views
Reindexing plan
I have a reindexing plan failing with error message:
Refresh failed for PhysicalPartition '1'
(Details: SQL Server Enterprise edition running in cluster)
Searching through BOl and other online ...
1
vote
1answer
374 views
SQL Server Alert Based On Severity Level Not Working
I am new to SQL Server alerts, and I am trying to setup an alert That will run a job and send a notification, based on severity level.
In order to test if the alert works correctly I have downloaded ...
1
vote
2answers
51 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 ...
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 ...
2
votes
1answer
39 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
64 views
Is it “legal” TSQL to CREATE TABLE #SomeTable and then DROP TABLE #SomeTable repeatedly in a script?
Is it "legal" TSQL to CREATE TABLE #SomeTable and then DROP TABLE #SomeTable repeatedly in a script?
I've got my code sort of segregated as "coherent blocks" that I can insert into a longer ...
1
vote
3answers
120 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?
3
votes
1answer
169 views
Applying user-defined fields to arbitrary entities
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 ...
1
vote
1answer
29 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
vote
1answer
67 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
54 views
How do I specify a linked server to a remote database over tcp/ip?
I have a remote database running on an 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 ...
-1
votes
1answer
27 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
65 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
172 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 ...
1
vote
4answers
137 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 ...
4
votes
1answer
132 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
47 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 ...
9
votes
5answers
308 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 ...
2
votes
2answers
74 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 ...
0
votes
1answer
95 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 ...
-1
votes
1answer
31 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 ...
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.
2
votes
2answers
52 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
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?
0
votes
3answers
122 views
Merge Replication identity field issues
One of our clients is using our software with merge replication of a database on a SQL Server 2008 R2 machine. There are two production environments in separate geographical locations only one of ...
4
votes
3answers
58 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. ...
1
vote
1answer
305 views
SQL Server Login failed for user Error: 18456, Severity: 14, State: 11
I have an AD group XYZ that I have added to SQL Server security with data_reader permissions.
The XYZ group has around 10 users in there who are successfully able to access the SQL Server database. I ...
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 ...
1
vote
1answer
38 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
votes
3answers
82 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 ...
1
vote
2answers
88 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
42 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
25 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
70 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 ...
2
votes
0answers
41 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?
1
vote
3answers
108 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
69 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
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 ...
5
votes
1answer
67 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
61 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? ...
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
527 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
51 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 ...
0
votes
2answers
103 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
60 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 ...
0
votes
0answers
42 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 ...
