This Tag is for SQL Server 2008 R2, major build version 10.50.xxxx

learn more… | top users | synonyms

0
votes
1answer
53 views

Best practice for archiving old data? [closed]

We are migrating 3 slightly different SQL databases into one (SQL Server 2008R2). We want to keep old databases "alive" for some period of time but once we've gone live we don’t want staff ...
1
vote
1answer
35 views

SQL logins Audit for SQL Server 2005/2008

For the Security Audit of SQL servers 2005/8 R2, We are looking for a method which can list us SQL accounts which have not changed password for last X days. For a single login we can do it via ...
2
votes
0answers
39 views

Change Tracking across several Tables

I'm looking into providing DB level change tracking within my system. I need to be able to track changes at an entity level, rather than just individual tables. Right now, we have triggers on each of ...
0
votes
2answers
35 views

How can I move backups location?

I have a seemingly easy question. How can I move a xxx.bak file to another location with T-sql? I have a job that takes a copy from database a to c:mycopy/copy1/a.bak and I want to create a step that ...
1
vote
1answer
51 views

Can Execute trigger in SQL Server 2008 R2

I'm trying to create a simple trigger to update a table's column after it was inserted. But when I execute the trigger it produces an error: Cannot execute script. Additional information: Unable ...
1
vote
1answer
30 views

SQL server compress backup for lower version databases

Recently our development team migrated couple of sql server 2000 Databases onto SQL2K8R2. Now we creating the backup strategy, and just had a doubt, that: 1) Compatibility level of those DB's still ...
1
vote
3answers
100 views

Recover deleted table from the database

Currently I deleted one table from the database. But now I want some of the information from that table. Please suggest how to restore or get back the table. I am using SQL Server 2008 R2.
1
vote
0answers
15 views

Removing Orphan Pull merge replication subscriptions

I have a merge replication set where I have removed one of the pulled subscriptions. On the subscriber the subscription shows up after deletion. It will not go away. I tried the methods provided on ...
0
votes
0answers
22 views

Adding lot of new role assignment on different folders

On our local SSRS we are currently adding a big quantity of different users on different folders. The usual steps are like the ones detailed here SQL Server Reporting Services 2012 Permissions. Is ...
2
votes
0answers
36 views

Why would a default constraint be missing an index?

I have a query that looks at the sys.dm_db_missing_index_group_stats system view to determine what missing indexes there are for maintenance purposes. If I remove the filter from the query that ...
0
votes
0answers
11 views

View all tables referenced by foreign keys in SSMS [duplicate]

I have a database with a lot of tables, the tables have a lot of foreign keys. Is there an easy way to view the other tables being referenced by the foreign key in a given table? I tried "view ...
0
votes
0answers
24 views

Cannot drop constraint in SQL server 2008

When I try to delete a constraint, it gives me the following message: "The constraint is used by merge replication for identity management and cannot be dropped directly. Execute ...
2
votes
0answers
39 views

Database corrupt after restore and upgrade on another instance

Just wondering, if someone faced the same issue. We have got a SQL Server 2008 R2 (10.50.2789) and SQL Server 2014 (12.0.2000) and there is a process of backup on MSSQL Server 2008 R2 and restore on ...
0
votes
2answers
45 views

Sql server 2008 - server name changed

I have changed the computer name of the server where sql server 2008 r2 is installed. Old name was WIN-OUJEKRMGXXX and new server name is KASURDYYY When i configure replication following error ...
0
votes
2answers
59 views

Efficiencies and pitfalls of a dynamically updated “Uber” View to streamline future code

We have a number of tables (about 40) with approximately 2500 columns between them that all have matching primary keys, but different unique columns. For various procedures and views, we'll draw on ...
1
vote
1answer
116 views

Index file growing endlessly - how to fix it?

I need to do some maintenance on a database on our client's site. Both log and index files were huge (Don't know how they took their backups to end this way) anyway I fixed the log file (.ldf) issue, ...
2
votes
1answer
58 views

Job to check the status of another job

I am looking to create a job which would check a status of another job every 5 minutes. select CASE jh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Success' WHEN 2 ...
0
votes
0answers
43 views

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. for linked servers

I am trying to execute a query to pull data from another server. I added the server as a linked server to the another one but I am getting the following error when I try to execute the query Login ...
1
vote
2answers
49 views

Is there an easy way to update connection strings to a database moved to a different instance

I have created a new instance of SQL Server 2008 r2 and exported my database to the new instance. Then I realized that the connection string for all of my datasets still point to the original ...
2
votes
1answer
112 views

SQL Server: Invalid Log Scan Number (…) passed to log scan in database (…) is not valid

We are getting a large number of the below errors on one of our production databases. CheckDB is completely clean. We thought it was related to replication so we shut it down using ...
0
votes
1answer
48 views

Application is hanging when database upgraded from SQL Server 2000 to SQL Server 2008 R2

Presently we are working with SQL Server 2000, the database is perfectly working with our software for retail sales counter application developed with VB 6.0. But after upgraded to SQL Server 2008 R2, ...
0
votes
1answer
41 views

MDF file full - cannot find the source of its giant size

All, I am completely at a loss here and I'm hoping you guys can help me out on this one. I am experiencing the following situation on an SQL Server 2008 R2 database. MDF-file: Allocated space: ...
1
vote
1answer
45 views

Best practices for selecting partition boundary ranges to maintain empty partitions at both end?

I have partitioned the table based on a date datatype partitioning column. The data for the column starts at "2014-01-01" and ends at "2015-01-01". I know that, the partitions should have empty ...
3
votes
1answer
202 views

Execution Plans Differ for the Same SQL Statement

As a background to this question the details can be found at this link. In a nutshell: I have 2 SQL statements that are almost identical except for the RIGHT table. The LEFT JOIN is the same and the ...
0
votes
0answers
28 views

Adding a second database to the same instance of a server

I already have a multi user software program running on Windows 7 work stations and the server is running Windows Server 2008r2. I am trying to setup another program using the same server. The code is ...
0
votes
0answers
27 views

Transfer data from SQL Server to obiee

I need to transfer data on a daily basis from a SQL Server 2008 R2 database to Oracle Business intelligence. What's the best practice? Db link? External table? Odbc?
0
votes
1answer
21 views

Cleaning disabled SQL agent jobs in SQL Server 2005 and above

For many of our old servers, we have found that there are lot of SQL agent jobs which were either run as an ad-hoc for instant purpose and now no more needed OR Jobs which are not functional/working ...
0
votes
1answer
38 views

Copy of a database with an existing database with schema and data in sql server

I have two databases one is having master kind of tables and other will have transaction data. I should create a new database with a structure of an existing database to my clients. Here is the ...
0
votes
1answer
74 views

Login failed for user - Error: 18456, Severity: 14, State: 38 [duplicate]

I am trying to run and ADODB command in MS Access 2012 to exec a stored proc in our SQL server 2008R2 database, but I keep getting error 38 and SQL profiler shows the following: Error: 18456, ...
0
votes
0answers
37 views

delete millions of rows from multiple tables

Hi we have to delete between 40 and 55% of records from several tables that have between 40 million and 100 million rows. Is it better to copy the rows we want to keep to a new table, drop the source ...
0
votes
1answer
30 views

How to list all past remote login information on client SQL server

Instead of using "Remote Desktop" for connecting to SQL server, I and my coworkers are using SQL Server Management Studio to apply the changes on remote server (each users have their own credentials). ...
2
votes
2answers
55 views

Backup compression in SQL server 2008R2 and above

Recently we got a new server to take care of and found that there are two data drive: S: 1 TB where resides 10 databases(mdfs and ldf's, though they should be on different drives but that's how we ...
1
vote
0answers
22 views

Using the partition function in the Where clause

I've been asked to test out performance of a database (a test system) after implementing horizontal partitioning. The database is on SQL Server 2008 r2. I'm inheriting this from someone else and ...
2
votes
1answer
86 views

SQL Server : Index Seek with very high Scan Count

I have a query that joins 2 tables: Documents table, that has DocID as Unique Clustered Index DocumentsRows table, that has DocID, RowID as Unique Clustered Index When I join those 2 tables, I ...
1
vote
0answers
36 views

Renaming databases vs backing up and restoring

I am creating a copy of Database A, restoring as database b, executing a job on database b. Upon successful execution, I would like to delete the database A and rename the database B to what the ...
-1
votes
1answer
30 views

can exec permissions be granted to a role in SQL Server 2008 / 2012

Can exec permissions (e.g. on a SP) be granted to a role in SQL Server 2008, so that all role members then have the permission; and if the 2008 database were backed up and restored into SQL Server ...
-4
votes
1answer
30 views

Find Longest Running Query [duplicate]

How I can find Longest Running Query and SPID on SQL Server 2008?
0
votes
1answer
40 views

SQL Server Import/Export: cannot copy geo columns

I'm trying to copy data from a SQL Server 2014 Express database to a SQL Server 2008R2 Express database. Compatibility level for the 2008R2 Express database is set to 100. As soon as I hit Next in ...
2
votes
1answer
84 views

Same query sometimes uses a Table Spool

I'm checking out a query which exhibits strangely inconsistent performance. Using SolarWinds DPA, I can see that the query sometimes uses a Table Spool, sometimes it doesn't. When it actually used a ...
5
votes
1answer
177 views

Why can a non-clustered index not be dropped using online=on option

When attempting to drop a non-clustered index using the online option such as DROP INDEX [IX_MYINDEX] ON [dbo].[myTable] WITH ( ONLINE = ON ), I receive the following error message. Msg 3745, Level ...
0
votes
1answer
32 views

SSMS 2008 R2 no longer connects to database [closed]

Before Christmas I had no problem connecting SSMS 2008 R2 to a specific remote database. Since Christmas, I get this error when I try to connect: TITLE: Connect to Server Cannot connect to ...
0
votes
2answers
58 views

SQL Job for Monitoring Blockings on SQL Server [duplicate]

Is it anyway possible to create a SQL Job for monitoring blocking on SQL Instance, if the blocking are still happening after some interval of time? We are using SCOM Monitoring tool to monitor events ...
0
votes
0answers
15 views

Cross Server Transaction takes longer when executed from stored procedure

I have a stored procedure on ServerB which executes an update statement joining with another table on ServerA. Something like.... USE [DatabaseA]; GO SET XACT_ABORT ON; --<-- Since cross ...
0
votes
0answers
35 views

Convert SmallInt to Datetime

Please help me to convert the following values to datetime: DayLastAccessed -23569 -23789 -23629 -23564 -23732 0 0 The above column is smallint type in SQL Server. I'm using the following query to ...
1
vote
1answer
74 views

How do I update and insert specific set of rows to an existing table using Merge in SQL Server 2008 R2?

I have a table in the database that I need to update based on the records being passed to it. I am given a table-valued-parameter that has the same schema as the existing table (for all intensive ...
1
vote
0answers
28 views

Average Waiter Count increasing

I am in a nightmare. We have deployed an application to a UAT environment, which has only 7 users. Since then, we have noticed in the logs, a lot of deadlocks, and processes being victims and things. ...
1
vote
1answer
43 views

Re-Run Backup Job in SQL Agent

My question is regarding re-running backup jobs only for those databases for which the job initially when it ran failed due to some reasons, like server reboot, path not accessible, newly created ...
1
vote
2answers
93 views

Monitor Database Memory usage SQL Server

In our PROD environment, there are two servers on which we have system critical database. The management requirement is to monitor these databases for high memory usage or access or long running ...
0
votes
1answer
68 views

sql join without combining all rows

I've searched questions but can't seem to apply to my scenario. Please see attached code below. I am trying to join two subsets of this data but am getting duplicates/too many rows please see ...
1
vote
2answers
105 views

SQL Server - Slow Database for no apparent reason [closed]

I have a DB Server running SQL Server 2008 R2 SP2 on 24 physical Cores and 32GB of RAM. The database files are on a SSD Raid1 and the TempDB on a SAS 15k (150GB DB) I assume that the server has more ...