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

learn more… | top users | synonyms

8
votes
1answer
1k views

Does SQL Server Snapshot replication completely copy the data every time or does it issue deltas?

I am looking at snapshot replication between two servers. Here is what I have: 500GB database ~500MB nightly bcp loads ~50MB daily transactions I was asking other DBAs in the company about which ...
5
votes
2answers
3k views

Automate daily backup of database

I am trying to create automate/daily backup of database in SQL Server 2008 R2. I did bit of research and I get to know that as I am using Express edition; which doesn't come with Maintenance Plan, I ...
2
votes
2answers
1k views

renaming a sql server with installed named instances

We have a hosted server that is named, by the hosting company, as something like WinEnt99. We would like to rename it to something more meaning like OurMonitor1. We have already installed a few ...
3
votes
1answer
242 views

SQL Server Snapshot Replication and SRDF compatability

Is there anything that breaks down if you use snapshot replication and then use SRDF for disaster recovery (DR) on the snapshot publisher? Specifically I am asking is: On the DR system, would the DR ...
5
votes
2answers
4k views

Running SSIS from a different server than the primary database instance

We currently have a single SQL Server Integration Services (SSIS) package that performs an extract-transfer-load (ETL) process to move some data from SQL Server to DB2. On the current production ...
9
votes
2answers
3k views

Impact of changing the DB compatibility level for a Published replicated DB from 90 to 100

I have a SQL Server 2008 R2 server with a bunch of published databases that are currently operating under compatibility level 90 (2005). The subscription databases are also SQL Server 2008 R2, ...
4
votes
2answers
8k views

How to debug deadlocks in SQL Server 2008 R2?

I'm using SQL Server 2008 R2 that automatically lock database items based on my queries. Sometimes this deadlocks - two threads with different queries never ends. Is there any way to debug how SQL ...
2
votes
1answer
1k views

Remove an IP address from server config

What is the safe way to remove an old IP address from SQL Server configuration? The scenario is, I have a server with 2 NIC's that should have been configured for teaming but weren't at the time SQL ...
1
vote
1answer
131 views

Upgrading to SSMS with SQL Web Edition turned off Code Outlining : How do I get it back?

I upgraded to SQL Server 2008 R2 Web from Express with Advanced Services recently and code outlining went away. How do I get my collapsible code back? Thanks
1
vote
1answer
595 views

how to capture the data using DMV's in sql server 2008 r2?

Hi i am working on sql server 2008 r2, i have a situation where i wanted to capture the DMV's data into a table, i would like to know when DMV's tables get refreshed or data flushed out. because as ...
3
votes
0answers
508 views

SQL Server 2008 R2 clustered index issue

Last week, we came up with a strange issue on Clustered index. We are using SQL Server 2008 R2 in our organization. A stored procedure is written for our application which contains a cursor in it. ...
3
votes
1answer
480 views

Why does Management Studio add the second alter table “CHECK CONSTRAINT” for a foreign key constraint

ALTER TABLE [dbo].[REMINDER_EMAIL] WITH CHECK ADD CONSTRAINT [FK__REMINDER_EMAIL__FACILITY_ID] FOREIGN KEY ( [FACILITY_ID] ) REFERENCES [dbo].[FACILITY] ( [FACILITY_ID] ) ALTER TABLE ...
3
votes
2answers
6k views

Disabling Checking of Schema On Function/Stored Procedure Creation

I'm trying to automate the process that executes changes to the SQL Server 2008 R2 database. The process I put in place drops and recreates my stored procedures and functions, as well as run scripts ...
2
votes
2answers
720 views

SQL Log Shipping Firewall settings [closed]

I'm attempting to setup log shipping between two differnet domains. When I attempt to connect to the secondary server instance the primary server fails to connect. If I turn off the windows firewall ...
4
votes
2answers
921 views

Restore all applicable files in a backup device in T-SQL

I'm using SQL Server (2008 R2 in this case) and I am backing up my databases into backup devices (one per database). Every Sunday the device is overwritten with a new full backup, every night a ...
0
votes
0answers
677 views

Unable to view “Maintenance Plan” folder under Maintenance in SQL Server 2008 R2 Standard Edition

SQL Server version is as follows : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Copyright (c) Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service ...
2
votes
1answer
435 views

SQL Agent job does not show up in the jobs folder in SSMS although they are in sysjobs table

I tried to copy some SQL Agent Jobs from server1 to server2 by selecting rows from server1.msdb.dbo.sysjobs and inserting to server2.msdb.dbo.sysjobs. I did the same with sysjobsteps. This seemed to ...
2
votes
3answers
1k views

How to find out what caused a schema change?

I am investigating some performance issues on my SQL Server 2008 R2 server. Some queries are being recompiled due to a "schema change". I am pretty sure no one is changing the schema. All databases on ...
3
votes
1answer
470 views

SSMS login to different domain

Our production database is hosted by another company on their domain. I'm able to RDP into the box and login with the credentials they provided me but when I try to login from my local computer on my ...
2
votes
1answer
344 views

Does a change in a query executed with sp_executesql cause recompilations?

I am experiencing a lot of re-compilations in my database. Most of the queries being executed are simple queries and all of them are being executed using the sp_executesql stored procedure. ...
4
votes
1answer
96 views

How to tell when DBO was changed (and if possible, by whom)

I am looking for a way to track when the DBO account is changed. For example, if someone uses sp_changedbowner is there a way, after the fact, to determine when this occurred, and even better, what ...
6
votes
2answers
161 views

Most effective way to determine the minimum set of permissions for user

I have a database that an application connects to using db_owner permissions. How do I effectively determine the minimum set of requirements actually needed by this user (application) to run without ...
9
votes
1answer
500 views

Initializing Transactional Replication From Backup

There is an option when setting up a publication for replication to Allow Initialization From Backup. We have been creating replicated databases for several years now and have always initialized from ...
7
votes
3answers
1k views

MDF and LDF contents in SQL Server

Let's say I have a database with a single .mdf and .ldf file. The database is detached, there are no transactions and SQL Server is stopped. I am presuming that all the data is already in the .mdf ...
3
votes
1answer
984 views

Log shipping setup across domains

I'm running SQL Server 2008 R2 on a Windows machine. I've been tasked with setting up log shipping between our production site (managed by a service provider) and our QA environment which is hosted ...
1
vote
0answers
172 views

Configuring SQL Server 2008 R2 Change Data Capture with SSAS

I'm completely new to SQL Server Analysis Servers (SSAS) so you'll have to excuse my ignorance. I've been running through the MSDN SSAS tutorial get a handle on how to use SSAS. We have SQL Server ...
2
votes
1answer
960 views

SSMS 32bit can't connect SSIS 64bit

I can't find the solution and searching for info doesn't give desired results Environment: Server: W2K8 EE 64 bit + MSSQL SE 2K8 R2 64 bit Client: Windows XP Prof 32 bit Using SSMS on client ...
2
votes
0answers
324 views

Auditing - parameter values not being written to log

I am using auditing to track usage of some procs in the database. In the log I can see a record for the Procedure being run and, immediately afterwards,the SQL statement that is being run. The ...
4
votes
1answer
823 views

Replicating multiple databases into one database

I would like to replicate multiple databases on different servers into one master database. I have 5 different instances on separate servers all running SQL 2008 R2. The schemas and structure are all ...
-4
votes
1answer
583 views

Why does a VIEW with a CLUSTERED index not use the index?

I have a view that has a clustered index along with other non-clustered indexes. A simple SELECT query does not use any of the indexes even when I select an indexed column. What could be the ...
2
votes
2answers
6k views

SQL Server: Should we use TCP or Named Pipes or use the default?

When connecting to a SQL Server 2008 R2 from a .NET 4 client application on a different server in the same LAN, one can set three different network protocols: TCP Named Pipes Don't set anything in ...
6
votes
1answer
12k views

SQL Server 2008 R2 Setup Client Tools Connectivity

During SQL Server 2008 R2 setup I see a checkbox for "Client Tools Connectivity" and "Client Tools Backward Compatibility" installation options. It says these are component for client server ...
3
votes
1answer
2k views

Sql server 2008 R2 installation fails with Error: 17204

Sql server 2008 R2 installation fails with the below error message in the log message, This is a part of the sql server log Starting up database 'msdb'. Error: 17204, Severity: 16, State: 1. ...
5
votes
2answers
2k views

SQL Server Agent not observing “execute as” permissions

I have a curious permissions problem. Something recently happened that caused existing SQL Server Agent Jobs that were configured to run as a particular User to stop working. They previously worked; ...
4
votes
1answer
2k views

Retrieve XML of large plan from plan cache

I'm trying to view (graphically) a large query plan that is in the plan cache and am running into problems where the XML is truncated in the query results. This is the query I'm using: SELECT TOP 1 ...
1
vote
1answer
861 views

Can operations in a loop cause table locks?

I know a bit about locking etc, that when a transaction is working on a table, if it is altering a certain amount of data it might try and lock the entire table (I'm sure that is over simplified, but ...
2
votes
2answers
788 views

Issues with uninstalling and re-installing SQL Server 2008 R2

I have installed SQL Server 2008 R2, and was able to login and also create a database and everything. When I was facing some problems with connecting my .NET project with SQL Server, I created some ...
0
votes
1answer
1k views

Error restoring backup in sql server. Old server version

When trying to restore a backup file (.bak) to SQL Server installed on my machine, I get the following error (steps that I follow to restore are: Right Click Database ---> Tasks ---> Restore) Error ...
1
vote
3answers
819 views

How do I copy a view from one server to another?

I want to copy a view from server S1 --> Database D1 to Server S2 --> Database D2. I generated the script by right clicking the database(D1)--> tasks--> generate script. However, when I execute this ...
17
votes
3answers
5k views

Data obfuscation in SQL Server

What is the best practice for Data Obfuscation in SQL Server? We'd like to use masked Production data in our UAT system. If we want to do it quickly, and with a higher level of obsfucation, what ...
3
votes
3answers
1k views

Set Backup Compression on SQL Server 2008R2 on by default

I can't seem to find the option to set the server property that will enable compression on SQL database backup to be ON by default, so I don't have to manually set it every time. Ca anyone point me ...
6
votes
1answer
814 views

Login to SQL Server 2k8 R2 SP1 via Active Directory Group

I added an Active Directory group as a Windows Authentication Login to our SQL Server 2008 R2 database. When a user who is a member of that group tries to connect to the server he is given a login ...
14
votes
3answers
2k views

Is there any benefit to defragmenting SQL indexes in a SAN environment?

Our SQL server lives on a SAN. It contains dozens of OLTP databases, some with several tables containing over 1m records. We have been running Ola Hallengren's index maintenance scripts weekly, and ...
2
votes
2answers
5k views

SQL Server : how to drop all constraints on a table in T-SQL

I´d like to drop all constraints on all tables in my database, because I need to change the relationships of many of the tables. Is there any way to do that with T-SQL? I cannot find any solution ...
2
votes
1answer
2k views

How to schedule a weekly backup of a database only if it has been changed since last backup

I´m new to SQL Server administration. I´d like to create automatically backup database every Sunday night, but only if there was any change in previous week (if not, backup is unnecessary). Is there ...
5
votes
2answers
2k views

Best value for fill factor in index

I use SQL Server 2008 R2 and want to set value for fill factor property in each index. I look for recipe to calculate best value for this property (have better performance when insert record) . Also ...
5
votes
2answers
1k views

Create index view with self join

I use SQL Server 2008 R2 and know that in Indexed View cant have self join. I have a tree table that have ID and ParentID Column and I need create indexed view on this table with self join between ...
3
votes
1answer
6k views

User can't see database in SQL Server 2008 R2

I took a backup of a SQL Server database and restored it as a new database on a different server. On this new instance I have done the following: recreated the login, associated the login with the ...
2
votes
1answer
2k views

What are the most compelling reasons to upgrade from SQL Server 2005 to SQL Server 2012?

We currently have 2 SQL Server 2005 Enterprise machines in a mirrored environment running on Dell R815 servers. These machines consist of: Four AMD Opteron 6176 Processors (12 cores at 2.3Ghz), for ...
7
votes
2answers
6k views

Why is this query not using my nonclustered index, and how can I make it?

As follow up to this question about increasing query performance, I'd like to know if there is a way to make my index used by default. This query runs in about 2.5 seconds: SELECT TOP 1000 * FROM ...