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

learn more… | top users | synonyms

0
votes
1answer
291 views

Affinity Mask and Affinity I/O Mask

Using: SQL Server 2008 R2 Under what circumstances would you set the Affinity Mask and Affinity I/O Mask options in sp_configure in SQL Server? We are currently running on a machine with 16 cores ...
5
votes
3answers
11k views

SQL Server 2008 R2: Problems after computer name change

I'm having a confusing problem after changing the computer name of a remote server hosting a local SQL Server instance. Basically, a remote server was moved from one site to another. In order to ...
3
votes
1answer
1k views

Clustered Index Update on Nonclustered Columns

Using: SQL Server 2008 R2 I am currently stepping through a query execution plan, and have come across an instance of a clustered index update on a table. The issue here is that the columns that are ...
1
vote
1answer
384 views

Giving selective permissions in a database

I have a user [AD\dbusersgroup_limitedrights] that should have on two schema's [ICD] and [XCD] within a database only SELECT and EXECUTE rights, however for each of the users in the ...
2
votes
2answers
367 views

Using Central Management Server to provide convincing performance statistics

Central Management Server The company I work offers dedicated vmware hosted servers to its Windows clients. Among those Windows clients, there are essentially 60 single instance MSSQL servers ...
0
votes
1answer
2k views

Install SQL Server Express r2 2008 using a configuration file

I need to deploy my application with SQL Server Express. There is a way of installing sql express using a configuration file so that when deploying my application it appears as if I am only installing ...
3
votes
3answers
354 views

Semi-Annual archiving of SQL Server database

Twice a year we create a static, archived subset of our production database. The newly created database contains roughly half of the tables as the production db (log/audit tables, etc. are not copied ...
0
votes
1answer
89 views

Checking for ApplicationName in a transaction through a Before Trigger

I would like to be able to build a trigger that checks the ApplicationName coming across on a transaction, and if the ApplicationName is of a certain value, then I want to throw an error, saying the ...
1
vote
2answers
5k views

Single User mode can't be turned off and no users are connected

I have a database that is in Single User Mode. When I run this: exec sp_dboption 'MyDb', 'single user', 'FALSE' It fails, telling me that there is still a user connected. But when I run both of ...
1
vote
1answer
667 views

T-SQL, show a set of rows (specific column) as a field, for a better desc, please see the table relationship

I have been having a problem for quite a while now, that I can show with a sample of 5 tables (I'm actually dealing with more than 5!): -------------------------------- shipments ...
1
vote
1answer
120 views

Pointers to multiple index allocation mappings

In sql server 2008, the sysindexes table contains a pointer to the first page of the index allocation mapping. If a table consists of multiple IAM pages, will the sysindexes table contain a pointer to ...
0
votes
1answer
1k views

sp_dts_getfolder isn't in my MSDB database, any ideas why?

We're currently moving our applications from a SQL 2005 to a SQL 2008 R2 database and everything has gone well(ish) but now one of our applications calls a stored proc called sp_dts_getfolder which is ...
1
vote
1answer
64 views

Reading and writing the common data

I have a couple tables and application service that intensively inserts, updates its. From the other hand some client application have to read data using those tables. Sometimes client application ...
14
votes
3answers
3k views

Painless way to create a clustered index on a huge table?

So we have a customer site that is complaining about some seriously slow performance. I took one look and it's obvious that the problem is because Somebody Else (grrrr) designed a table holding some ...
16
votes
1answer
8k views

SLEEP_TASK Wait Type in SQL Server - What does it indicate?

I haven't seen the SLEEP_TASK wait type before, and today I seem to be getting a ton of them. I'm not the official DBA, just a SQL Server developer who knows some DBA stuff. We upgraded our servers ...
1
vote
1answer
254 views

SQL Server 2008 Analysis Management objects work with SQL Server 2005?

Can SQL Server 2008 Analysis Management objects API work with SQL Server 2005 install?
1
vote
1answer
516 views

Insert 4 tinyint values into varbinary(4)

I'm working on a function to convert the string representation of an IP into its varbinary(4/16) form. Right now, I have this: @stringIP = '192.168.0.3' select CAST(PARSENAME(@stringIP, 4) as ...
4
votes
2answers
711 views

Scrubbing Names via SQL Query/Batch

We have sensitive information (names of people) and we're looking to put into place a script to "scrub" of the names. Has anybody come up with a good algorithm to do this, something other than just ...
0
votes
1answer
273 views

Why is SQL Server 2008 R2 Management Studio making me login again?

I login to SQL Server 2008 management studio each morning, it works all day just fine. When I come in the next morning and try to run a query, it asks me to login again. Where is the setting defined ...
2
votes
2answers
833 views

Running a number of .sql files using SQLCMD

I'm trying to put together a batch file to run a large number of .sql files without doing so individually. So far I have: @echo off echo "Enter Server" set /p SERVER= echo "Enter Database" set /p ...
12
votes
8answers
2k views

Version control for database objects

The database our developers are working on is too large (have a lot of database objects). We have to control db objects changes (change management). Our company cannot have a person who would be ...
2
votes
2answers
1k views

SET LOCK_TIMEOUT, is it session or statement based?

For what period does the LOCK_TIMEOUT LAST? I do a "SET LOCK_TIMEOUT 10" and a "SELECT @@LOCK_TIMEOUT" in one command after a login and get 10 returned. Immediately after this I do a "SELECT ...
3
votes
2answers
283 views

Is it important to release memory back to the server?

I have a SQL Server 2008 R2 instance running on a production system. I'm not a DBA; I'm more a developer, but my client is currently asking me: How important is it to release memory from SQL Server ...
0
votes
1answer
846 views

SQL Server 2008 R2 SQL Server Profiler and “exec sp_reset_connection”

Yesterday i ran the "SQL Server Profiler" tool and outputted the data to a table, now when i look in it i find many "exec sp_reset_connection" rows and thats expected with our data access layer! But ...
2
votes
1answer
1k views

SQL Server 2008 R2 sys.dm_exec_sql_text question

If i run the following query on our production server: SELECT DISTINCT TOP 10 t.TEXT QueryName, s.execution_count AS ExecutionCount, s.max_elapsed_time / 100000 AS MaxElapsedTime, ...
1
vote
2answers
7k views

Exporting table records to an INSERT script

I'm setting up a test environment for development and need to export some static data from tables in Production. Ideally the exported product would take the form of a script with all the required ...
2
votes
2answers
1k views

Can I add “Included Columns” to an index without affecting performance?

I have a non-clustered index with 63 Million leaf level rows. Currently it does not have any included columns. I would like to add one included column while the site is online. Will this significantly ...
1
vote
1answer
649 views

SQL Server 2008R2 Logon failed

The SS error log indicates the following at a frequency of 2 to 3 times per hour: Logon failed for login 'NT AUTHORITY\NETWORK SERVICE' due to trigger execution. [CLIENT: ] There is no logon ...
3
votes
2answers
1k views

TdsInputBufferError not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers

I've got an issue with SQL Server. The value of TdsInputBufferError is not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers. This happens randomly, once or twice a week, ...
0
votes
3answers
6k views

SQL Server 2008 R2 Query Last Successful Database Backup For All DBs

Is it possible to query the last successful backup date (and perhaps the type of backup) of each database in SQL Sever 2008 R2?
2
votes
1answer
2k views

SQL Server 2008 R2 64bits repair error

I had to reinstall the default server instance on one Windows 2008 R2 server. I checked that the sql groups were cleaned and the registry keys cleaned before reinstalling. After reinstalling, the ...
24
votes
2answers
8k views

I/O requests taking longer than 15 seconds

Usually our weekly full backups finish in about 35 minutes, with daily diff backups finishing in ~5 minutes. Since tuesday the dailies have taken almost 4 hours to complete, way more than should be ...
4
votes
2answers
2k views

ROWLOCK in sql server

I have a table to update with no indexing in the where clause so I use ROWLOCK in the update script, hoping to get row lock instead of table lock but no luck.. so what is the function of the ROWLOCK ...
4
votes
2answers
11k views

SQL Server Transaction Timeout

Is there a way in SQL Server 2008 R2 to cause a timeout for a database modification involving a transaction? We have a scenario where our application code hangs or throws an exception and fails to ...
1
vote
4answers
626 views

Maintenance Plan for all Online databases doesn't execute for all databases

Using SQL Server 2008 R2, I have a maintenance plan that has a basic Backup All Databases (ignore offline) task, and clean up after 7 days. However, I note that sometimes I lack backups for ...
1
vote
1answer
200 views

Send a trigger to an application upon a specific SQL Server server usage pattern

I've been dealing with a vendor's unconfigurable application which is, unfortunately, the most feature-complete in its niche. I'm looking to augment its capabilities with on-top screen display. Its ...
3
votes
2answers
688 views

How do you migrate a database from SQL Server 2008 to MySQL?

I'm just getting into MySQL, so forgive me if this is a basic question. For my application I need to migrate my database from SQL Server 2008 R2 to MySQL. Is there any mechanism or technique or ...
2
votes
1answer
2k views

How long will an index take to create?

I have a table with over 70 million rows in and I have viewed the execution plan which has identified a missing index. I took a snapshot of data a short while ago and created this index on a local ...
2
votes
2answers
4k views

SQL Server Management Studio generates error, can't start

On my development machine, SQL server 2008 R2 was running fine previously. But after formatting OS and re-installing the OS and SQL server, it generates the following error when I want to start SQL ...
4
votes
1answer
211 views

DB Master Key Encrypted By *Correct* Service Master Key?

My client has frequently run process where we move databases from a prod environment to lower environments (refresh QA with prod database, etc). These DBs have database master keys on them. We have ...
2
votes
1answer
558 views

SQL Server 2008 r2: What events/queries hit tempdb

We had a slowdown on a db server this morning. Checking the logs, the only thing that happened during the 2-minute slowdown worth noting was an object being created and deleted in tempdb over the span ...
2
votes
2answers
472 views

SQL Server 2008 R2 Query For Failed Email Notifications

Is there a SQL query I can run to see if there has been any failed attempts to send database mail in the last X Minutes?
-1
votes
1answer
935 views

DBCC Checkident doesn't work for reseeding tables

I want to reseed all of my tables. I delete all the values from them and then run this query: But then when I populate this database again, id starts from the maximal id of the deleted entries. Why ...
4
votes
3answers
1k views

Query the Owner of SQL Server 2008 R2 Jobs

Is there a way to query the owner of all jobs in SQL Server 2008 R2? I discovered when maintenance plans are edited the owner gets changed, so I want to make sure they are all owned by sa.
6
votes
4answers
225 views

Query Performance depending on parameter 21seconds vs. > 14hours

I have the following query: SELECT DISTINCT DATEADD(ms,DATEDIFF(ms,0,[Time]),DATEADD(dd,0,DATEDIFF(dd,0,[DATE]))), [DATE], DATEPART(YY,[DATE]), ...
1
vote
3answers
235 views

Backup a portion of the database?

I have a database that is over 50Gb. I'd like to take a backup of this database, but I don't want a full backup. I need to extract some of the data for a development test db. Is this possible?
0
votes
1answer
1k views

Microsoft SQL Server, Error: 3205

I am getting this error while restoring the backup. Too many backup devices specified for backup or restore; only 64 are allowed. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL ...
8
votes
2answers
3k views

How to create an Event Notification that runs a Job/procedure when mirroring state changes

I'm asking this question in the sequence of this one Can I send a string over TCP using T-SQL? Remus Rusanu exposes what it seems to be an optimal solution for my problem, but... I'm too immature to ...
2
votes
2answers
959 views

Best practices for creating partition schemes and functions

I'm implementing partitioning for the first time in SQL Server 2008 R2. I've several large tables that I am partitioning. My main question is: Should I use the same Partition Scheme and Function for ...
2
votes
2answers
1k views

SQL Server 2008R2 SP1 instance not available for adding features

I need to add fulltext capabilities to an existing SQL Server 2008R2 instance but the one I need to upgrade is not actually available. The combobox shows only the one selected, as in the picture. It ...