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

learn more… | top users | synonyms

3
votes
3answers
130 views

Is there a way to execute sql statement in sql server based on sql server version?

I would like to create computed index on a table if sql server is 2008 or newer and a simple index if sql server is 2005 or older: -- check for sql server version if (select ...
2
votes
2answers
85 views

Scripting all of available databases to .sql files containing schema+data+users with single command

I have a SQL 2008 Express with 80 databases in it. I want to move these databases completely to another server which is hosting a SQL 2008R2 Enterprise instance. As i'm told it is better to script ...
0
votes
0answers
38 views

unclear SQL Server Issue

I'm attempting to determine root cause for an issue that caused an outage for my company. Essentially the site connections to our database server were timing out after not receiving a response for ...
5
votes
1answer
79 views

2008R2 Cannot Disable Non-Clustered Index

We have identified a handful of superfluous non-clustered indexes and I would like to disable them for a few days before straight dropping them. However when using the GUI in SSMS (right click > ...
1
vote
0answers
64 views

Differential backup job is working but not the Full daily backup

I have a full backup job for my database that runs once daily, I see it posted daily in the folder that it should go to, but when I check the "View History" for this job in SQL Server Agent, there are ...
1
vote
0answers
71 views

SSRS 2008 report fast in test, slow in production: parameter sniffing problem?

We recently had an issue with reports running in an integrated SharePoint environment where the report runs fine in development, test and staging, but very slow (times out) in production. Running ...
2
votes
1answer
39 views

Can we tell dbcc to ignore subnormal real values?

Currently dbcc DATA_PURITY complains about subnormal real values in some of my columns, such as 7.561407E-42. I would rather keep them in my tables, and ask dbcc to ignore them. I am OK with the ...
0
votes
2answers
145 views

Slow insert - 300+ Million Records

The following query takes over an hour on the table to insert. There are no indexes. There was one which was deleted but still takes an hour after deletion. Any pointers on improving this ...
1
vote
1answer
20 views

Single user mode when was configured

I'm trying to find when a database on our instance was configured as SINGLE USER MODE but can't find that information. Either using tsql or the GUI. Maybe it is not possible to know that information? ...
4
votes
1answer
143 views

SQL Server 2008 R2 Restore constantly hanging

I have a SQL Server 2008 R2 SP2 test server that I have to refresh the databases on fairly often, but every time I have to do a restore on, it will hang on me. I use a custom script to check the ...
2
votes
1answer
146 views

“Database is in transition” Error

Today I was trying to restore a database over an already existing database, I simply right clicked the database in SSMS --> Tasks --> Take Offline so I could restore the database. A small pop up ...
3
votes
1answer
37 views

Searching all Stored Procedures for an update of a specific column

I'm looking at an audit table created by a trigger, and we were smart enough to save the SUSER_NAME(); we have a column called LastUpdateRef, but not all old code has been updated to use it (it's a ...
2
votes
1answer
65 views

Database Design guidance required

I have scenario where there is Table T with columns PK,A, B, C,D TABLE t ( PK, A, B, C, D ) i have to insert data from different sources ie columns A,B,C,D are calculated separately.. and they same ...
2
votes
1answer
71 views

Error Logs various questions

I'm slowly collecting and trying to analyze lot of information on all of our instances (2008R2 SP1). I'm now going through the Error Logs and I've couple of question. There is a lot about error log ...
3
votes
1answer
146 views

Why does SQL Server run a subquery for each row of the table it's qualifying?

This query runs in ~21 seconds (execution plan): select a.month , count(*) from SubqueryTest a where a.year = (select max(b.year) from SubqueryTest b) group by a.month When the subquery ...
4
votes
2answers
36 views

Easily give role in one database access to tables in another database on the same server

I have a database server (Sql Server 2008 R2 Enterprise) supporting two separate but related vendor products, each with it's own database (call them X and Y) In database X I a have a lot of users ...
2
votes
2answers
96 views

Autogrowth, shrink and database performance

I have already asked some questions about Autogrowth and shrink. But I have some doubts related to it. 1)I have a database which grows about 300 MB every month. So is it best to set Autogrowth of 300 ...
2
votes
0answers
45 views

Multi-Statement Template Plan Guide

A piece of, um, software seems to be issuing ~20k queries of this form every few seconds: DELETE FROM macVendor WHERE companyId=N' 08-00-'; INSERT INTO macVendor (companyId,organization) VALUES (N' ...
5
votes
2answers
149 views

Error using triggers with SQL Server Profiler Tables

After noticing 100's of sort_warning errors in a recent analysis of the default trace output, I am trying get the text of the queries causing those warnings. SQL Server Profiler has an event ...
1
vote
0answers
45 views

Changing Quoted_Identifier on existing Constraints / Rules / Procedures

I am currently working with an old-ish database on a 2008-r2 Server which uses a lot of objects that were created with Quoted Identifier set to off. I am mainly looking at these types: ...
0
votes
1answer
72 views

Need some new solution to shrink my SQL Server database log file [duplicate]

I am new bee for SQL Server dba. I need some suggestion to shrink the database log file. I have tried and found some solution to shrink the file. But i think this is not opted solution for this. ...
0
votes
2answers
9 views

One of the Database Ownerhas been Removed and corresponding property changes in sql server

A database has been created on SQL 2008 by a user named user1 ,on dec 2013.Today ,we are going to remove that user(/owner) from the server.What will be the owner property of the database show then?
5
votes
1answer
92 views

Capture Complex Actual Execution Plans

I have a stored procedure that among other things builds and executes a large number of Dynamic SQL statements. (Don't hate me, I didn't write it.) I would like to retrieve the actual execution plan ...
3
votes
2answers
142 views

T-SQL - Enforce Atomic Operations x Locks

I have a database model that works similar to a banking account (one table for operations, and a trigger to update the balance). I'm currently using SQL Server 2008 R2. Table OPERATIONS: VL_CREDIT ...
0
votes
1answer
55 views

Raise error from Stored PROC in SQL Server PDW

I need to do some validation inside a stored procedure before I continue processing but SQL Server PDW (SQL Server 2008 R2) does not support RAISEERROR inside stored procedures. Is there any other ...
8
votes
3answers
506 views

Is there a way to find out who changed the password for a login?

I am trying to find out who changed the password for a login in SQL Server 2008 R2. I have already checked default trace - and it does not log that event. The default trace will include these ...
3
votes
2answers
392 views

Shrink the database only up to its initial size which is set after creating database

In SQL server every database has a property Initial Size (MB) which can be seen in properties of database in SSMS. By default it will be 3 MB for mdf and 1 MB for ldf file. So now if create a new ...
4
votes
2answers
194 views

Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data?

Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data? I need to nullify the data in all encrypted columns in a development server (according to our ...
3
votes
2answers
141 views

Converting Tables to Ansi Padding On

I am currently working with a MSSQL 2008R2 Server and a database which uses a lot of tables that are a mixed bag of ANSI_PADDED Settings. 90% of the tables habe Columns with Ansi Padding on AND ...
5
votes
1answer
114 views

Disabling SET Statements / Forcing SET Options in SQL Server 2008R2/2012

I am currently working with a MSSQL 2008R2 Database which uses the following SET-Commands frequently: SET ANSI_NULLS { ON | OFF } SET QUOTED_IDENTIFIER { ON | OFF } SET ANSI_PADDING { ON | OFF } ...
2
votes
0answers
16 views

Disabling SET Statements / Forcing SET Options in SQL Server 2008R2/2012 [duplicate]

I am currently working with a SQL Server 2008 R2 database which uses the following SET commands frequently: SET ANSI_NULLS { ON | OFF } SET QUOTED_IDENTIFIER { ON | OFF } SET ANSI_PADDING { ON | OFF ...
3
votes
1answer
65 views

MS SQL Server using SSL Connection over the Web?

I'm looking to have an external SQL Server 2008R2 Express instance and be able to connect to this via SSL using a 'sql.my-domain.com' address. So far, I've looked at a few Microsoft links on the ...
2
votes
3answers
88 views

Allow update access to only 2 tables in a database schema

I would like to have a login which is capable of accessing a specific schema but should be having only update access to 2/3 tables. Is it possible? The Database runs on SQL Server 2008 R2. Kindly ...
4
votes
1answer
138 views

removal of GUIDS used for all PK's, FKs & Clustered indexes

Inherited control over a database that has this nasty config, seems some poor decisions were made long before anyone who knew anything about databases got involved. It also has large sections of code ...
0
votes
1answer
168 views

Float datatype with 2 digits after decimal point

In SQL server float datatype does not display leading zeros after decimal point declare @num as float set @num=5.20 select @num will dispaly only 5.2 But i want it to display as 5.20 After a ...
2
votes
1answer
140 views

General access denied error while using import data in SSMS

I am using SQL Server Management Studio with the following version information below: Microsoft SQL Server Management Studio 10.50.4000.0 Microsoft Data Access Components (MDAC) ...
1
vote
1answer
98 views

Slow running query with inner SELECT in where clause

I have a query that looks like the following: USE DATABASE3; GO SELECT t1.COL1 AS COLUMN1, t2.COL2 AS COLUMN2, t1.COL3, t1.COL4 AS COLUMN4, t1.COL5 AS COLUMN5, ...
2
votes
1answer
53 views

Why am I getting blocked when I call OBJECT_NAME(object_id, database_id) from sys.dm_tran_locks?

I'm working on a query to tell what objects have locks from a given session and I'm running into some blocking issues. Here is my basic query: SELECT request_session_id AS session_id, ...
3
votes
1answer
58 views

Log Shipping in lieu of replication

I have two SQL Server 2008 R2 systems that, due to network restrictions, cannot use SQL Server replication. However, data needs to be shared between these systems. It may be possible to send a file ...
3
votes
1answer
58 views

Should I align my SQL Server file and growth sizes with the block sizes on my SAN?

It was brought to my attention that our (Hitachi) SAN handles data in 42MB blocks. It is tiered storage so each block is evaluated when the director makes decisions as to when it should promote/demote ...
3
votes
1answer
96 views

SQL Server Backup and Restore Incrementally

I am investigating a reporting solution for a production database. We currently have a production db which backs up fully at 01:00 each day. This backup is then copied using a Robocopy job to a ...
1
vote
0answers
76 views

How to set a 'real' field to accept an out-of-scale value?

I have an SQL Server database and one particular field that is a real. The program that feeds it was modified and is now sending a value too small to be considered a real. Is there a setting for ...
0
votes
1answer
61 views

Comparing Environments [closed]

I am a SQL Server dba. I have been asked to compare our production environment to test from a number of different perspectives. These are windows patch level,sql service pack, to database size and ...
1
vote
1answer
378 views

SQL server databases stuck in restoring state

I have a Sharepoint server. I guess our network administrator has miss configured his back up tool (I guess he uses Symantec backup exec) and now some of my databases are stuck in restoring state! ...
0
votes
1answer
98 views

Archival strategy for a table with 25 million records in SQL Server 2008 R2 Enterprise [duplicate]

I have a table that stores some audit information and currently has 25 million records. There is a process that inserts to this table every 30 minutes. This is historical data and is not used for any ...
3
votes
2answers
250 views

What can cause slow queries with low resource usage?

I am writing an application which builds a database on a SQL server. The application loads data into some staging tables, then executes a series of stored procedures which merge the staging tables ...
0
votes
1answer
77 views

Get the job scripts out of a MSDB Backup [duplicate]

Long Story Short... my Database server died a month ago. I restored everything but the MSDB as the tape backup failed. I reconstructed some jobs but some essential ones are missing. I finally found ...
3
votes
1answer
1k views

SQL Server 2008 R2 error: 15023, user, group, or role already exists

I have a test database that I'm having permissions issues with. I can't access the reporting database, and the application's help documentation says to do the following: Resolution: 1. Launch the ...
0
votes
1answer
58 views

Do I need to explicitly grant permissions for a login under the sysadmin role for it to access a custom schema?

So I have a login that has sysadmin rights on the db server. However, it's not able to access database objects under a custom schema that was created (eg. it cannot select from tables with ...
0
votes
1answer
60 views

SQL Server 2008 Script Changes from Database Diagram

I have a database diagram which I have made several changes to using SSMS, however instead of committing the changes I want to generate a SQL script to apply them instead. Is there a simple way to do ...