This Tag is for SQL Server 2008 R2, major build version 10.50.xxxx
0
votes
0answers
46 views
bad to assign specific processors to SQL Server 2008 R2 cluster instances?
I have a SQL Server 2008 R2 failover cluster with a passive node and an active node. The servers are physically identical. I have two instances of SQL Server, one set to use all the CPUs in the ...
3
votes
2answers
108 views
Find top by values by percents using range in SQL Server
What will be easier way to group records by range of percents.
Here what I try to accomplish
Select top percents between 50 and 75 * from myTable order by PKI
Here is way I did this but I have ...
2
votes
0answers
69 views
Find out who/what logged in via a DAC connection
My SQL Server just died due to a DAC connection being established. I need to find out how/who or what caused this connection? Is this logged anywhere? Can anyone shine any light on this?
1
vote
1answer
130 views
Row_number and wrapping select ?
I have this code :
Select name, age , row_number() over (partition by name order by age desc) rn from my table
(goal : select the oldest guy among same names)
If I want to select all the row ...
1
vote
1answer
52 views
Unique Non Clustered Column in Partitioned table
We have partitioned one of our SQL Server tables on a Clustered Index datetime column, we have a Primary key Non-clustered index column with Identity data type and this column is setup as a ...
1
vote
3answers
108 views
SQL Server BPA 2008R2
I have been using SQL server BPA for getting good information from Microsoft. I was using its 2005 version in which i used to export the results in csv format but recently I got two new servers which ...
2
votes
1answer
182 views
Restore only one datafile in SQL Server 2008 from partitioned database
We have a partitioned database that I'd like to get copied over on a semi-regular basis to another server for testing updates. Since the partitioning is based on a date column, the most current data ...
4
votes
1answer
118 views
Same Query takes 0 seconds on Server A, 7.5 minutes on Server B (same db/hardware/config)
I have a query (sql below) that is nearly instantaneous on 1 server, but takes 7.5 minutes on another virtually (key point) identical server. The estimated query execution plans have one obvious ...
0
votes
0answers
111 views
SQL Server 2008 R2 Database Diagram Error Code
I have sysadmin privileges on a SQL Server 2008 R2 server. I'm working with the development database and want to save a database diagram I created. When I try to save I get the following error ...
2
votes
1answer
171 views
Error while restoring database in SQL Server 2008 R2
This error occurs while I restore database from .bak file
A system assertion check has failed. Check the SQL Server error log
for details. Typically, an assertion failure is caused by a software
...
0
votes
1answer
71 views
How to create an index view?
I have a view which is doing summation of several columns:
Select cola, colb, cola + colb + colc from table tbl_a
Can I know what are the indexes I should create for the view to improve its ...
1
vote
1answer
65 views
SQL Server 2008 - get current LOCK_ESCALATION on particular table
Is there a way to find the current setting for LOCK_ESCALATION on particular table? Can it be found through system views?
2
votes
2answers
75 views
Advice about Backup Solution
Our database Server is being hosted by another company. They provide a file level backup that occurs once per day. They also allow for Log Shipping. My company is taking advantage of their ...
2
votes
1answer
265 views
SQL Server recommended MAXDOP settings for NUMA
I have an OLTP SQL Database running on SQL 2008 R2 Enterprise Edition that may be fairly busy (5000 batches/sec) in next couple of months after going live production. Looking into server configuration ...
1
vote
4answers
303 views
Update many-to-many relationship table
I have three tables: task, taskmembers, members. Each task may have up to five people associated to it. Currently, when someone updates a task I delete all the current members and insert them as a ...
1
vote
1answer
167 views
Right Trimming Binary Data in SQL Server
Scenario:
I am inserting a string into a binary field (CONTEXT_INFO) and then later attempting to pull it out and convert it back to a string. When I do, the resulting string has a length of 128 ...
0
votes
1answer
83 views
Collecting trace info of SQL Server 2005 using SQL Server 2008 R2 profiler
I have a vs2005 application that uses SQL Server 2005. I was trying to collect the SQL trace generated by the application while executing. For collecting the trace I've connected to SQL Server 2005 ...
3
votes
1answer
106 views
Easiest Way to Move 100 Databases
I need to move about 150 databases from one server to another server.
SQL Server 2008 Web Edition
SQL Server 2012 Preview (in a different datacenter - East Coast Azure)
I was planning on moving ...
1
vote
2answers
275 views
Multiple triggers vs a single trigger [closed]
Scenario:
Each time data is inserted/updated/deleted into/in/from a table, multiple unrelated items of business logic need to be executed.
Question
Given that the solution is to use database ...
3
votes
1answer
94 views
Why does SqlSentry Plan Explorer not give the duration of my queries?
I have a large nasty stored procedure in a 13gig database that takes 45 minutes to run when I include the actual execution plan. I save the query plan from SSMS and open in up in SQL Sentry Plan ...
4
votes
1answer
246 views
Safe way to truncate SQL Server Error Log
We are running out of space. What is the safe way to clear the error log?
3
votes
2answers
335 views
Transaction and Try-catch in SQL Server Job
We have DML operations in each step of a SQL Server job. To ensure the update/insert will be rolled back in case something goes wrong, I have wrapped the data modifications of each step in TRY CATCH ...
3
votes
1answer
355 views
Does this mean the 'public' has full select, insert, update, and delete access?
I'm trying to create a new DB user in SQL Server 2008 R2 that can only select from one SQL View. But no matter what I try when I login as this new user I can select from any table. I ran this query: ...
5
votes
2answers
582 views
SQL Server gets slow over time until we have to restart it
We have a database with a mixed OLAP/OLTP workload. The queries are quite ad-hoc and are dynamically created in the mid-tier application server. When we start the server, the performance is quite ...
0
votes
1answer
105 views
SQL Server Job logging best practice
To be able to design a robust Job in SQL Server, we need to have log for each step of the job. I know there are different options available which help us for troubleshooting a job, but what if we want ...
1
vote
1answer
79 views
Restore .mdf data file for a File Group in SQL Server
I have a database with three filegroups and each filegroup hosting one datafile. One of the secondary filegroups XYZ with data file xyz.ndf got updated with wrong/bad information in various tables ...
3
votes
3answers
154 views
SQL Server 2008 R2 Restore a back up to a new database
I have a full back up of a database, I want to update some records in some table in the current database from its old backup without affecting other records.
What is the best way to do this?
...
1
vote
3answers
603 views
How much do foreign keys affect performance?
I'm reviewing an SQL Server 2008R2 installation having large performance problems. Currently I'm looking into its indexes and foreign keys and have found out that none of the tables have any foreign ...
2
votes
2answers
170 views
Calling a SQL Server job within another job
Is it possible to call a SQL Server job to run within another job?
I know we can add all steps of Job 1 to Job 2, but I prefer not to do that. First the Job 2 is already quite big and second I ...
6
votes
1answer
287 views
Restore a Database to a New database (T-SQL)
I have a database backup from a database, say SourceData which needs to be restored on another database, same schema, but different name (SourceData_1) using T-SQL. I don't know why I gets the ...
4
votes
2answers
313 views
I lost my database in SQL server 2008 R2
My database suddenly stopped working and from checking the error logs it says that the master database was corrupted.
So I tried rebuilding the system database with the command:
setup ...
1
vote
2answers
158 views
New login is created but 'sa' cant assign grant/deny permissions to user in SQL Server 2008 R2?
I created users with login option in security tab in SQL Server 2008 R2 running on windows 7 successfully and grant permissions to :
Use dummydb
Grant REFERENCES ON OBJECT::vuUser TO nvp
GO
Use ...
1
vote
0answers
139 views
jTDS + stored procedures + prepareSQL = nesting level error?
Situation
A (Tomcat) Java web application using jTDS to connect to a MSSQL 2008 database. This Java application for 99% executes MSSQL stored procedures using user input.
Problem
The jTDS driver ...
4
votes
1answer
126 views
LOCK error after creating a clustered index
I just created a clustered index on a 1MM row table (page and row locking turned on). I now try to run queries and I get this error:
"The instance of the SQL Server Database Engine cannot obtain a ...
5
votes
1answer
139 views
Inline table-valued function vs inline sql
I'm seeing some odd behavior in trying to tune an inline table-valued function. Specifically, if I take the guts of the function and it with the same parameters as I'd have called the function with, I ...
5
votes
1answer
99 views
Practical application of EXISTS vs. LEFT JOIN to find addresses for delivery
Our end users have many ways of shipping product to our customers. They can use the customer address in one table ([customer]), a specific delivery address in another ([dropship]), or yet another ...
1
vote
0answers
68 views
Distribution Agent running but subscription does not exist?
I have an issue where in my replication topology, the distribution agent is running, and says there are no commands to distribute. However, checking the undistributed commands, it shows there are a ...
3
votes
2answers
192 views
Cannot generate reports from SQL Management Data Warehouse
I'm running SQL Server 2008 R2 and have installed the MDW on one server and have a Data Collector collecting and uploading the server activity, query results, and Disk activity data to the MDW. When I ...
9
votes
1answer
379 views
query optimization: time intervals
In the main, I've got two kinds of time intervals:
presence time and absence time
absence time can be of different types (eg breaks, absences, special day and so on) and time intervals may overlap ...
4
votes
2answers
187 views
SQL Server 2008 R2 database is suspect, tried it all. Final destination?
This night my hosting provider made a "hard reset" on my VPS hosted in their farm. And one of most important databases (SQL Server 2008R2 database) is now in Suspect mode. My provider has backups ...
4
votes
2answers
226 views
Bulk Data Loading and Transaction Log
im currently working on a project which bulk import data from flat files(csv) about 18 differents files each linking to a specific table through some store procedure.
I followed the steps as advised ...
1
vote
0answers
105 views
SQL Server BCP imports, but table still has 0 records
I ran the following command from the command line on SQLServer 2008 R2
BCP C4L.dbo.districts in C:\TEMP\districts.sdf -f C:\TEMP\districts_import.fmt -T'
No errors were reported and it ran as ...
2
votes
1answer
161 views
Error in restore database
I use this query in SQL Server 2008 R2 to restore DB1 database:
RESTORE DATABASE [DB1]
FROM DISK = N'D:\new.bak'
WITH RESTRICTED_USER, FILE = 1, NOUNLOAD, REPLACE, STATS = 10;
The following error ...
11
votes
3answers
459 views
Improve performance of query using IN()
I have the following SQL query:
SELECT
Event.ID,
Event.IATA,
Device.Name,
EventType.Description,
Event.Data1,
Event.Data2
Event.PLCTimeStamp,
Event.EventTypeID
FROM
Event
INNER JOIN ...
2
votes
2answers
52 views
How to prevent SQL Log from being too full
I have a database that generate a huge amount of transaction per seconds, something like 50-100 transactions/second. Because of this, the log files grows very quickly. The thing is though, that even ...
3
votes
2answers
288 views
Procedure cache (plan cache) size
I'm developing an application with many, dinamycally-assembled, complicated SQL queries. I'd like to keep the query plans in the procedure cache for as long as it is possible. To prevent discarding ...
3
votes
0answers
41 views
grant permission [duplicate]
Possible Duplicate:
grant permission
this one is copied from stackoverflow ( original post ), because of advice from Josien
i've got a database-role, -login and user
CREATE LOGIN ...
0
votes
0answers
134 views
Consequences of multiple FK's from same column to multiple tables
I know it's possible to create multiple FK's to different tables using the same column, other than the performance hit caused by having a FK, are there any negative consequences of doing so?
...
4
votes
1answer
178 views
Performance tuning a cascading delete with many foreign keys
I have a delete query that is taking a long time. Looking at the execution plan, I see that most of the estimated cost in the delete query is in a section of the data model that had a lot of data ...
1
vote
1answer
353 views
Connect to SQL Server using OLEDB or ODBC?
We have a desktop application written in Visual Basic .NET. One our clients needs to use ODBC instead of OLEDB to connect to the SQL Server 2008 R2 Database. The company has several databases ...
