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

learn more… | top users | synonyms

5
votes
1answer
442 views

Best way to index a table with two datetime columns that are equally queried

Sql Server 2008 R2. I have a table with ~70m records, about 10 inserts per second. It's currently clustered on a CreatedAt datetime column, which always increases. 50% of queries involve this ...
2
votes
1answer
788 views

SQL Server Error 833 On LDF File

I'm getting error 833 on my ldf file and i'm getting it a lot. Please see the following. When this happens, my application is hang already. I know error 833 has been reported a lot, but what does it ...
1
vote
1answer
59 views

Does implicit casting negatively affect index usage?

I have a query similar to the following: SELECT id FROM MyTable WHERE eventCount > eventCutoff * eventFactor eventCount and eventCutoff are defined as a smallint, and eventFactor as a float. I ...
3
votes
2answers
2k views

SQL Server Agent - Run Query at the end of each month with date variable

I am running SQL Server 2008 R2, and I need to create a new job that will basically run a query at the start of each month (at the 1st of each month at 01:00 AM). Here is the query: INSERT INTO ...
5
votes
1answer
1k views

SSIS package with BULK data load

I analyzed a C# program over the weekend for a customer that basically does the following: Truncate Table Fetch Data (source on these is either remote database or local database where job runs) ...
10
votes
1answer
729 views

Why don't SQL Server queries use more than 7MB/sec of disk I/O

I have an SSD that, using IOmeter test, shows performance over 200MB/s. However when I run any SQL query from local machine the windows resource monitor never shows disk IO above 7MB/sec. This holds ...
1
vote
1answer
3k views

Read only/Standby database - Set user permissions

Example database: WarehouseDB Example user: reporting Database state: Read only/Standby mode How do I give the user 'reporting' access to only WarehouseDB without giving them sysadmin privileges? ...
1
vote
2answers
1k views

How to identify the db orphan mdf and ldf files

I want to identify the orphan mdf and ldf files on the disk and remove them. After searching on the internet, found the script below on MSDN forum. But when I run the script it returns many ...
1
vote
1answer
2k views

Is it 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
287 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 ...
1
vote
1answer
403 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 ...
2
votes
1answer
245 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 ...
2
votes
4answers
408 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
1k 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
190 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 ...
2
votes
1answer
523 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 ...
3
votes
1answer
1k 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
162 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
176 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
105 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
1k 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
1k 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 ...
4
votes
2answers
898 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
110 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 ...
4
votes
3answers
223 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
5k 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
175 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 ...
6
votes
1answer
10k 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?
6
votes
2answers
7k 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
4k 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: ...
7
votes
2answers
12k 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 ...
1
vote
1answer
606 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
726 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
888 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? ...
3
votes
2answers
4k 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 ...
3
votes
2answers
5k 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
3k 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 ...
5
votes
2answers
3k 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
806 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
484 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 ...
7
votes
1answer
934 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
763 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
169 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
230 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 ...
4
votes
2answers
1k 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 ...
10
votes
1answer
693 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 ...
5
votes
2answers
298 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 ...
5
votes
2answers
1k 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
177 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 ...
4
votes
1answer
4k 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 ...