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

learn more… | top users | synonyms

1
vote
3answers
879 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 ...
18
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
832 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
6k 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
3k 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 ...
5
votes
1answer
401 views

SQL Server index / performance help needed (index scan and a sort taking 40 minutes)

I finally have the logic figured out for a query, now I need to speed it up... dramatically, if possible (it runs for 40 minutes +/-). It's on two tables, one with just a couple hundred rows ...
1
vote
0answers
142 views

hStmt/cursor error caused by faulty data?

Could a busy hStmt and invalid cursor errors be caused by faulty data? ODBC and the software that connects to the database is correct and working - i have ran a test with a clean database (that has ...
2
votes
2answers
633 views

rebuilding indexes and logshipping?

We all know that index rebuilding creates lot of log growth..so what changes would you consider to your logshipping side to accomodate this log growth? Do we increase the frequency of the ...
0
votes
0answers
145 views

How to design the table and its relationship?

I am having a problem designing a Dim and Fact Table for the following problem. I want to display my data in PivotGrid as below : First table shows that if i search a person, anyone related to ...
1
vote
2answers
1k views

Creating a form using SSRS

We have some application forms in our system and want to define them as Reports using SSRS 2008 R2. So when users see the form on the screen and what to print it as a report in a print friendly ...
2
votes
1answer
227 views

Sql Server And Very Hot CPU % of Total Wait Time

I have recently started monitoring a production database with transactional replication. The CPU % of Total Wait time = %93.00. This is horrible. I confirmed all 8 processors are being utilized. This ...
6
votes
2answers
10k views

Sql Server - Best Practices for Growing Database Files

I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 ...
0
votes
2answers
3k views

How do I design a Dimension and Fact Table from a relational database?

I'm new to using SQL Server 2008 R2 SSAS. I have the following tables in my database, that I wish to model using SQL Server Analysis Services: Table1 ServiceTypeID | ServiceTypeName ...
5
votes
2answers
330 views

Correlated subquery and join: still the same exectution plan?

I have a correlated subquery like this (from BOL): SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID FROM Person.Person AS c JOIN HumanResources.Employee AS e ON e.BusinessEntityID = ...
3
votes
2answers
7k views

Why database diagrams are not working when domain account is attached to database owner?

I was trying to use Database diagrams is SQL Server and got following error: Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first ...
2
votes
0answers
103 views

SQL Server and updating windows firewall rules

Any ideas how should I proceed if I would like to update the firewall rules (essentially add ip-s to existing firewall rule). The step of exporting ip-s to text file and then using command line to ...
2
votes
1answer
196 views

SSMS Object Explorer Details causes deadlock while job is running

I have a problem with one of my SQL Server jobs. I've found that while the job is running, SQL Server Management Studio locks up. It always locks up while I'm looking at tables and sprocs within the ...
1
vote
1answer
582 views

Can the default minimum date range for 'datetime' be changed in SQL Server 2008 R2?

The minimum date range is set to somewhere in the 1750's. I need to store dates prior to 1750. One solution that came to mind is to store the dates in a particular format in a 'varchar' field, and ...
3
votes
1answer
251 views

Login failure error log when querying Central Management Server group

I am querying a group under a Central Management Server, which results in the following being logged to the SQL Server Error Log. Error: 18456, Severity: 14, State: 38. Login failed for user ...
5
votes
1answer
749 views

Can NOLOCK in a stored procedure be overridden when executing it?

Our application interfaces with a partner's application by calling one of their stored procedures. They insist on sprinkling NOLOCK hints throughout their code and this is a long stored procedure. ...
7
votes
1answer
614 views

SQL Server considerations when backing up / restoring a database that uses replication

In the event that I need to restore a database involved in replication, what considerations to I need to keep in mind? Do I need to keep backups of both the publisher and the subscriber? Can I ...
2
votes
2answers
397 views

SQL server job deployment

I have a SQL server job which has got different steps, including running a SSIS package. Currently it is on my pc (my local instance). I should deploy it to UAT and assume some steps need to be ...
2
votes
0answers
610 views

SQL Server Database Mirroring : strange client application behavior upon failover

I am putting a HA SQL server environment up based on three SQL Server 2008 R2 machines and database mirroring. I'll name them : principal.company.intra mirror.company.intra witness.company.intra ...
2
votes
1answer
881 views

How to Create Database Audit Specification for a column?

I am creating SQL Server 2008 R2 Database audit Specification for tblPerson.SSN I selected Audit Action Type: Update Object Class: Object Object Name: tblPerson Principal name: Public It captures ...
5
votes
2answers
1k views

Get a record's current partition

In SQL Server 2008 R2, how would I determine what partition a record currently lives in?
5
votes
2answers
560 views

Partition Function vs. Clustered Index

We have a table that is clustered on identity/datetime2. It is partitioned on the same datetime2. Are there any reasons to cluster on datetime2/identity instead? I understand the reasoning behind ...
3
votes
1answer
348 views

Help on designing SSAS Dim and Fact table

I am having a problem designing a Dim and Fact Table for the following problem. I have a Pivot Grid with data as below : USER | RELATED USER | RELATIONSHIP | PHONE NUMBER ...
14
votes
2answers
584 views

Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?

I would like to use code that I developed in C# CLR to be used in all of the databases on the system so that I don't have to set each to trustworthy and turn CLR on and keep a bunch of the same code ...
2
votes
2answers
479 views

SQL Replication Commands

I'm trying to figure out how i can get the number of commands over the last day for a sql server replication. Transactional / Snapshot / Merge. I really want to be able to calculate the average ...
3
votes
1answer
1k views

SQL Server : reasons not to use default database settings for ANSI_NULLS?

I've been given a case where a customer is experiencing this error - once in a while: Msg 8624, Level 16, State 21, Line 1 Internal Query Processor Error: The query processor could not produce ...
2
votes
1answer
1k views

Is the SQLTRACE_FILE_BUFFER wait type documented? What does it indicate?

We run a background task on one of our servers which polls sys.dm_exec_requests for long-running queries, gathering diagnostic information when it finds them, including wait type. I'm seeing a number ...
2
votes
2answers
537 views

UDF in check constraint — downside?

I have a table where all actions affect single rows (insert and update, no delete's but if there were, they would be individual as well). I'm considering using a UDF in a check constraint so I can ...
1
vote
3answers
88 views

Generally speaking, what pecking order should be used when tuning SQL Server?

When tuning SQL Server, what generally is the pecking order to use when trying to decide what to tune first? I'm asking in regards to an existing database that would I would have little ability to ...
3
votes
2answers
293 views

Sudden Query Performance Degradation On A Linked Server

I have an application that uses a view on a SQL Server 2008 R2 instance. This view actually queries a linked server (SQL Server 2008) that has replicated data from our main ERP database. Yesterday, we ...
6
votes
3answers
1k views

Does having an index on a VARCHAR column with a lot of very similar starting values have bad performance

We seem to be having quite unusual bad performance on queries that use an index. for example the table looks like PK BIGINT ID VARCHAR(50) Col1 Col2 etc So we need to insert a row in the ...
3
votes
3answers
2k views

Setting up SQL Server 2008 R2

I need to set up a SQL Server for a development environment for a client. First I installed SQL Server 2008, I was able to connect to it from SQL Server Management Studio fine but when I tried to ...
2
votes
1answer
841 views

How to change SQL Server Service Account using PS

SQL Server runs under AD Account SQLAccount1. How to make it run under another account, SQLAccount2? How to do it un Powershell?
3
votes
2answers
10k views

Cannot access any database in SQL Server 2008 R2 Express after moving workstation from domain to workgroup

My workstation that has SQL Server 2008 R2 Express for development purposes installed has been moved out of a domain into a Windows workgroup. While being part of the domain I used the account ...
6
votes
1answer
2k views

Restore MSDB data to a new server

I'm trying to move everything from a SQL Server 2008 R2 (enterprise) instance to a new instance on a different server. To do this, I thought (after some research) that I'd be able to move the system ...
3
votes
2answers
6k views

What is the difference between SQLEXPRESS and MSSQLSERVER?

I am using SQL Server 2008 R2, when I looked into the services for SQL Server I saw there are two different services with different instances. SQLEXPRESS MSSQLSERVER Can anyone help me to get ...
10
votes
3answers
1k views

Documenting a giant web of inter-related stored procedures in an MS SQL database: What tool or format?

I hope this is a question with a shorter answer than "Read a 1000 page book", but then, if that's the real situation, then hit me with it. I am not a real DBA, I'm a software developer who is ...
6
votes
2answers
2k views

How to find out which host / IP address / program performed a delete?

We are using SQL Server 2005. In our database some rows are deleted; how can I find the system (host name / IP address), program and date & time of deletion?
6
votes
1answer
652 views

Can SQL Express be used as a Log Shipping monitor server?

I'm keen to setup a "Monitor Server". We have SQL 2008 R2 Enterprise edition on both primary and secondary servers. Can we use SQL Express in monitoring server setup for licencing purposes?