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

learn more… | top users | synonyms

0
votes
1answer
45 views

How to determine consumption of data for archiving in sql server?

I was told that it could be possible to analyze the consumption of a given row. I already know I can use sys.dm_db_index_usage_stats to see the reads / writes on a given index (thus the table) but I ...
0
votes
0answers
19 views

Help understanding SQL Server Developer License when users test via webserver [duplicate]

I'm trying to understand how many licenses I may need if we decide to pursue a SQL Server Developer license for our Testing environment. We would have 5 actual developer and DBA's physically ...
1
vote
0answers
30 views

Performance Metrics for TempDB [duplicate]

I've been tasked with gathering performance metrics on a few database servers that have one TempDB data file. It seems prevailing wisdom is to have one TempDB data file per core up to a certain ...
2
votes
1answer
111 views

Move log file without taking database offline

I need to move a database log file to a new partition without taking the database offline. The normal way of doing this would be to detach the DB, move the log file then reattach the db. Is it ...
-3
votes
1answer
926 views

What is the Difference between SQL Server 2008R2 and SQL Server 2014?

I want to take my project from sql server 2008 R2 to Sql Server 2014. But I want to learn what is T-Sql the difference between 2008 R2 and 2014 for Database Engine? I mean is there any end of life ...
1
vote
1answer
85 views

How to speed up looped INSERT INTO statements?

I currently use the following statement, for 10,000 rows it takes about 150 seconds. I tried removing the index on the target table, but this didn't help. Running the loop without the INSERT INTO ...
2
votes
0answers
138 views

Exporting SSRS report to Excel

We have a number of SSRS reports and want to show the report in Excel format to the users. SSRS has provided an option to save the report in Excel format (.xls), however when the report is saved as ...
2
votes
1answer
43 views

Is there a way to check when a user was mapped as DB Owner?

I have a user that was mapped to a database as DB Owner. Is there a way to see when this mapping was granted?
1
vote
1answer
45 views

Cannot login using SA with SQL Authentication

I can not log in to SQL Server 2008 in sa mode. I can only log in using Windows Authentication Mode. I chose only defaults while installing SQL Server 2008 R2.
3
votes
1answer
55 views

Impact of Log backups restore process after file\file group restore on a database

When we restore file\file group(backup1) backup on a database is it necessary to restore all log backups that were taken after backup1 to get database in consistent state?? and also what will be the ...
2
votes
2answers
783 views

A transport-level error has occurred when sending the request to the server

In our Vb.net application(userd within a local network connection inside a office), which is used from long time now some of the users get the errors only sometimes a day A transport-level error ...
0
votes
2answers
211 views

SQL Log Messages - FILESTREAM and xp_cmdshell

I was perusing the SQL logs today and found the following: FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'. Configuration option ...
0
votes
1answer
50 views

DDL statements being commited within transaction

I am working on a script that involves a lot of structural changes, and like to return to a "base" condition while I work on what all needs to be changed, so I have my changes wrapped in a ...
1
vote
2answers
74 views

Restoring database from a server that uses data compression

In our environment our production server is using MS SQL Server 2008 Enterprise Edition. We have a development and test server that runs MS SQL Server Standard Edition and the table structure between ...
-1
votes
1answer
118 views

Stack overflow when parsing a lengthy query

I have a lengthy T-SQL Script. When SQL Server attempts to parse the script, it raises this error: Msg 565, Level 18, State 1, Line 106620 A stack overflow occurred in the server while compiling the ...
1
vote
0answers
32 views

Any instance where DMV can cause blocking? [duplicate]

So we had some slow performance with timeouts this morning. When looking for the cause, our blocking query (uses DMVs) showed up as the blocking query. I think this was just a fluke but the job we ...
5
votes
1answer
343 views

Optimize where clause using dates

I'm trying to optimize several queries that all use a similar pattern on one of the WHERE clauses: AND (DATEADD(DAY , ISNULL(a.[due_days], 30) + 30 , [dbo].[CalcDate]([type], ...
0
votes
0answers
20 views

Is there a way to shrink a Log File in SQL Server without affecting Point in Time Recovery? [duplicate]

My friend (who doesn't use stackoverflow) asked me a peculiar and interesting question just now. Is there a way to "disconnect a Log file, or shrink a log file without affecting Point in Time ...
-1
votes
1answer
692 views

Sql Server Backup Job failed

I have a sql agent job setup for taking full backup every night. Last night the job failed with the following error message. Sql Server Error Log Message BackupIoRequest::ReportIoError: write ...
0
votes
1answer
917 views

Declaring a scalar variable that its a table valued parameter?

I get error 137 that I must declare a scalar variable (@facilities), although I'm declaring it in the Create stored procedure as a readonly variable, so I wasn't sure what I'm missing here. any advice ...
2
votes
0answers
69 views

Which parameters was a stored procedure or command executed with?

We are using nHibernate which generates queries from .NET code. Occasionally, some queries get stuck in the sp_whoisactive list and we cannot find out why. I have a feeling that it has to do with a ...
4
votes
1answer
129 views

How to disable or delete an index

I created a non-clustered index on a live server (SQL Server 2008 R2) and it turned out not to give the performance benefits that I was hoping for. So I want to delete the index again. Unfortunately ...
1
vote
1answer
110 views

List of Expired Merge Subscribers

I have a setup of merge replication. Sometimes, subscribers will not be able to sync within the retention period set in the publication. How can I get a list of expired subscribers to a merge ...
3
votes
1answer
64 views

SQL Server SSL connection

Is it possible if I wanted to set up SQL Server 2008 R2 instance so that it only accepts SSL Connection? I am getting different answers and based on my testing, it will accept non-SSL connection even ...
0
votes
2answers
107 views

Hidden user rights

I'm running SQL Server 2008 R2 SP2. I have a SQL Server user, that the application uses to connect to my database. I've removed the user from all roles except public and the login from all database ...
1
vote
1answer
217 views

Help with out-of-range value

I have this query that is giving me an error The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. Query: select COUNT(*) from dbo.patient ...
1
vote
1answer
271 views

Why do connections to my database fail, but only sometimes?

I am having some trouble diagnosing the cause of a problem I am having with Microsoft SQL Server 2008 R2. There is an application written in C#.net that connects to the database. Occasionally the ...
1
vote
2answers
100 views

How to Move All tables from one Database into Different Database?

I am using Oracle SQL Developer and I have ten tables. Now I want to move all 10 tables from Oracle into a SQL Server 2008 R2 database. Is there any easy solution for this task?
0
votes
0answers
39 views

SQL Server 2008 R2 installation device error

I am trying to install SQL Server 2008 R2 to use along with Visual Studio for a website I am creating. However when I am installing SQL Server I am constantly getting the following error: The ...
3
votes
2answers
392 views

SysAdmin server role and Active Directory Groups

I've been struggling to find best practices information or any advice regarding Active Directory Groups that provide group members with the SysAdmin server role. I'm at a new job where I'm the only ...
0
votes
1answer
216 views

Fixing a corrupt Log File in small downtime window

I am working with a client who has been receiving the following error from their Log backups of one of their SharePoint databases: Backup detected log corruption in database FOOPORTAL_SP2010_Config. ...
2
votes
1answer
95 views

SQL Server - Data and Log Separation

We have a SQL Server 2008 R2 which has, among others, a 12K RPM HDD and a 7.2K RPM HDD. The original intention was to store the data files on the 12K drive and the log files on the 7.2K drive, as is ...
3
votes
3answers
320 views

Track changes in database structure

There has been a change in our DB and one of the SPs has been deleted. We have no idea who did it or when. Is there any way to find it out? Or enabling an option to be able to track this in future?
2
votes
1answer
309 views

How do I transfer logins from 2008R2 to 2014 with passwords?

I've tried Tasks>Generate Scripts... and set 'Script Logins' in the Advanced Scripting Options, but I get this in the output: /* For security reasons the login is created disabled and with a random ...
1
vote
1answer
936 views

Possibly tempdb out of space or a system table is inconsistent

While running DBCC CHECKDB I got this error Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system ...
2
votes
0answers
95 views

Phantom Transactions using Version Store in tempDB

We are seeing version store space increasing on one of our servers, and there are ~20 sessions that are showing as actively accessing version store rows for over 24 hours. The problem is, when I try ...
2
votes
2answers
252 views

What is the best way to get all data for a date range, plus the last event just before the range?

For this question assume the following: There is a table of events [Event] There is a table of employees [Employee] There is a table that relates the two [EventEmployee] (EventID + EmployeeID) Each ...
1
vote
2answers
72 views

Oprhaned entries in msdb..sysjobactivity

In this question: http://stackoverflow.com/questions/18445825/how-to-know-status-of-currently-running-jobs This answer was proposed: SELECT sj.name , sja.* FROM msdb.dbo.sysjobactivity AS sja ...
0
votes
1answer
47 views

Which is faster in process time?

I have a script that uses aggregate functions many times, and that might cause the slowdown of my script. And I thought to simplify my script and do all the summation/addition/concatenation on the ...
2
votes
1answer
95 views

Can comments in stored procedures cause problems

We're running a webserver that calls stored procedures to compute complex queries. Using sp_who2, we've discovered a peculiar issue where an abnormal number of processes would be stuck on SELECT state ...
0
votes
0answers
107 views

Randomly failing SQL Server 2008R2 used by Access 2010 front-end via ODBC

From time to time (sometimes days apart, sometimes several times a day) users of a multi-user Access front-end, which has ODBC connections to a SQL Server back-end, see a variety of problems. Most ...
0
votes
1answer
56 views

Help me improve my query

Help me improve this query. It took 8 minutes to generate 16k rows only. SELECT TPG.group_id INTO #tempgroups FROM [sqlcdb-h05].[cosmocallv6].[dbo].tpersonnel AS TP INNER JOIN ...
1
vote
0answers
91 views

Link up an ms access database to SQL Server 2008 R2?

I've installed the OLE DB Provider for JET by downloading this and installing it. I closed and reopened Microsoft SQL Studio, and connected to my SQL Server Instance. In <instance-name> ...
0
votes
0answers
76 views

Database mirroring partner cannot connect to witness

So, I am not sure when this started but at some point in my database mirroring configuration, one of the partners (currently the mirror) stopped being able to connect to the witness. This is a ...
0
votes
1answer
63 views

SQL Server distrb.exe & Logread.exe using huge SQL.txt file in C:\Users\sqlsrvacct\AppData\Local\Temp folder

I have the following situation: SQL server 2008 R2 SP2 I have both the Distribution and Subscription database in this server. LogReader agent and Distribution agent are running in continuous mode the ...
1
vote
1answer
71 views

Sql Server 2008 R2 Memory Caching , why doesn't it release the memory?

i am working on SQL server 2008 R2. It has been allowed to take a maximum memory which is around 45 GB. In couple of days it caches everything and the limit is reached. We have other processing as ...
0
votes
0answers
190 views

SQL Server Replication Monitor Throwing “Specified cast is not valid. (ReplicationMonitor)” Error

The publisher is running SQL Server 2008 R2 on a Server 2008 R2 box. The subscriber is running SQL Server 2012 on a Server 2012 box. I have tried launching the replication monitor from both servers ...
1
vote
2answers
54 views

Should I include as much information as possbile to help the optimiser?

Reading the article "Dynamic Search Conditions in T‑SQL" I saw the following statement in the "Using sp_executesql" section. Double Feature You may wonder why there are two conditions on the ...
0
votes
1answer
455 views

Finding login or user name of last person to use a DB

The boss has asked for a list of DB names along with size and name of last person to use that DB. I've found resources for when a DB was last accessed but for by whom. How would I solve this? ...
1
vote
1answer
78 views

SQL Server 2008 R2 - Limit viewing on database list

In SQL Server 2008 R2 I have user1, he owns 3 db (db1 , db2, db3) and I want he can see only these 3 db when he login by using SSMS. I did REVOKE VIEW ANY DATABASE FROM PUBLIC, but then he cannot see ...