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

learn more… | top users | synonyms

2
votes
1answer
118 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
3k 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
292 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
395 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
124 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
671 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
143 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
314 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. ...
1
vote
2answers
401 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
293 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 ...
4
votes
2answers
572 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 ...
1
vote
1answer
275 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 ...
9
votes
3answers
4k 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
4k 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 ...
5
votes
2answers
2k 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 ...
4
votes
2answers
2k 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
391 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
17 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
128 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 ...
3
votes
3answers
3k 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
2answers
208 views

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

Inherited control over a database that has this nasty config. It also has large sections of code generated by NHibernate, including the gneeration of GUIDs before they get to the db so no chance of ...
0
votes
1answer
19k 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 ...
3
votes
2answers
654 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
360 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, ...
3
votes
1answer
190 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
110 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
110 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 ...
2
votes
1answer
175 views

How can I compare the server impact of transaction replication

We are developing a system that must extract data from an existing SQL Server 2008 R2 cluster. One of our design goals is to limit the performance impact on that cluster as much as is practical. ...
3
votes
1answer
279 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
89 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
66 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 ...
11
votes
2answers
51k 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
2k 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
2k 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
686 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 ...
7
votes
1answer
21k 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
253 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
385 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 ...
1
vote
0answers
299 views

Disable the SQL profiler to be executed on particular query running from ASP.NET application

I have set of queries executing from an ASP.NET application and I don't want those SQL queries to be traced by SQL Server Profiler. How can I do it?
0
votes
3answers
2k views

Microsoft SQL Server does not start

I have problem with start SQL Server 2008. I needed copy database from one computer to seced. I copy only catalog DATA from my actually working server and past this catalog to my new fresh installed ...
0
votes
0answers
209 views

SQL Server 2008 R2 replication server runs faster than primary

I run a simple query like select * from table and I get the following results: primary database : 90 seconds replication database : 2 seconds The tables are identical and have the same indexes in ...
2
votes
1answer
107 views

Make View Supersede Table in Same Database?

For testing purposes we have a sanitized copy of our production database in our test world. Because of the way it is sanitized, we now have empty fields where encrypted data previously existed. We ...
4
votes
1answer
679 views

Database having only db_datareader shows invalid properties for a index

If a SQL server database user has only public permission in server roles and for a particular database only db_datareader and public permission then while trying script index as CREATE To option in ...
0
votes
1answer
3k views

Sql Server Fix Users

I Create a user in my database with following command : CREATE USER Test WITHOUT LOGIN I created a login with the Test Name. I want to link Test user with Test Login. When I use following command ...
0
votes
0answers
247 views

How to move SQL server transaction replication from one server to another server [closed]

Currently we have SQL Server 2005 as a publisher and SQL 2008 R2 as subscriber for one of our database (about 350 GB in size). I want to move the replication to our different server (Which also has ...
2
votes
1answer
630 views

Can geography datatype Lat/Long properties be returned using a view in SQL Server 2008?

Hopefully the question is fairly self-explanatory. I've been ferreting through the, what appears to be quite minimal/poor microsoft documentation regarding both views and geography datatypes in the ...
3
votes
0answers
45 views

Any checklist/guide when transferring a database to a new server? [duplicate]

I'm looking for steps that I need to follow to transfer a database to a new server. So far I have: I restored the current database that I have in SQL Server 2005 instance to another SQL Server ...
1
vote
1answer
109 views

Create LOG files when creating multiple DATA files for tempdb?

I've a question that I can't seem to find an answer to (and I can't test it out for a few weeks so I'm doing research now during my downtime). Should I be creating corresponding LOG files for every ...
3
votes
1answer
472 views

Get identity value of all rows inserted

I can use Scope_Identity() to get the last identity value inserted into an identity column. But how can i get identity values of all rows which are inserted in a insert statement? Example insert ...
2
votes
1answer
183 views

How does SQL bind rows in filestream-enabled table with their files

Every BLOB stored in a filestream-Enabled table actually stored in a physical file, How can I know the actual filename for a specific row in that table. For example, I have the Images table which is ...