SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

0
votes
0answers
26 views

How can I check logs/audit files for changes made to a view in SQL Server 2008 R2?

On a SQL Server 2008 R2 database, I have a view which may have been altered by another user. The only change that was authorized to be made was for the addition of a new column to one of the tables ...
2
votes
1answer
84 views

WAIT STATS - high Wait Time on ASYNC_IO_COMPLETION

I currently look at my wait stats. I followed best practices explained by Paul Randal and go through the documents he references. Looking at the statistic slices I take once / day for a while now ...
1
vote
2answers
65 views

Cannot generate SSPI Context

When I try to connect to a SQL Server from my local machine I get the following error: Cannot connect to Computer1\Instance1. The target principal name is incorrect. Cannot generate SSPI ...
0
votes
2answers
81 views

Invalid connection error on SQL Server 2008

I have this third party application that is interfacing with a MS SQL Server and I have some issue migrating its database from a MS SQL Server 2005 to A MS SQL Server 2008. The application was ...
2
votes
2answers
107 views

Get count based on 15 minutes of interval

I have a table containing Call records: tbl_calls cl_Id cl_StartDate cl_endDate I am passing two parameters @StartDate and @EndDate to my stored procedure. My requirement is to get the count of ...
1
vote
2answers
47 views

Change language used to parse decimal number

When my application - which I cannot change - tries to execute the following ad hoc statement: Insert into Payment(idProduct,PaymentTypeId,total,paymentdate,cost) VALUES ...
1
vote
1answer
81 views

Query to get total time logged in per week

I need to find the number of hours each user is logged into an application per week. The table structure is as follows. There can be many logins/logouts per day. Any suggestions where to start? I'll ...
1
vote
1answer
101 views

How to get SQL Server CPU and Memory read and write usage within two weeks?

Is there any a quick way or quick script on how to get the CPU and Memory usage within a time range (at least two weeks)? I did my research earlier and it seems that the procedures available seems ...
0
votes
1answer
19 views

Foreign key not able to get one to many working

I want to have a one to many relation with player points but I need it in such a way that entity framework sees it as a collection so i can add to it. I mean that one player can have multiple points ...
1
vote
1answer
39 views

Active Directory group as a schema in SQL Server

In SQL Server 2008 R2 we have a database that is being maintained by a team of developers. Each of them should have permissions to perform DDL operations such as adding a column or creating a table. ...
1
vote
2answers
54 views

Parameters in Activity Monitor

To view "recent expensive queries" I use Activity Monitor. Some of the queries there are with parameters. Is there a way to know the values?
0
votes
1answer
39 views

How to pivot this unpivot?

This is the pivot I made: SELECT * FROM ( SELECT *-- codigo,ID,Grupo,convert(int,CodOpcao) as CodOpcao FROM ( SELECT * FROM ( SELECT 39 as codigo , ...
0
votes
1answer
38 views

How to get an exclusive connection in AutoRestore job ( sp_RestoreGene)?

I am using Paul Brewer's script for AutoRestore sp_RestoreGene on SQL Server 2008R2 box. I created the stored procedure dbo.sp_RestoreGene on the Server 1 where the backup file is located. I called ...
1
vote
1answer
68 views

SQL Server database has corruption, but displays no errors

We have an extensive database of tables. I moved over a table from our dev db to our production db using a program called SQL Compare. I just copy paste the SQL it produces into Management Studio and ...
2
votes
3answers
100 views

SQL Server Pagination using CTE

I have to apply paging on query as per requirement i need two extra columns other than query result 1. Total Records 2. Total Pages but i am trying to confirm that this the right way to retrieve ...
0
votes
1answer
37 views

Get SQL server instance installation date [duplicate]

I would like to know the date when the sql server installed on the system. I did try to look in %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\ directory but there are so many files and ...
0
votes
1answer
32 views

How to Override a column in table.*

select (column1+column2) AS column1, table1.* from table1 both column1 and column2 are columns in table1. Is there anyway that when I run the query I can get only the new column1 is shown and the ...
1
vote
3answers
94 views

Copy table structure excluding nullability into local temporary table

How can I copy the structure of a table into a temp table (or table variable) excluding the nullability of each column? Mainly I need to copy the column names and data types, but not nullability, ...
4
votes
3answers
724 views

Correct way to stop SQL Server (through automation)

Question Is it safe to start & stop SQL's services using PowerShell's Start-Service and Stop-Service commands? If not, is there a recommended way to start & stop SQL's services through ...
0
votes
0answers
17 views

Temporary migration to a new server for firmware upgrade

I need to temporarily migrate an SSRS deployment to a new server so that I can perform a firmware upgrade on the server. Once the firmware upgrade has been completed, I will move SSRS back to its ...
0
votes
2answers
33 views

Temporarily Archive Databases on SQL Server 2008 R2

I've got a client that has approximately 100 databases across 10 servers. We're attempting to create a standardized process for removing old and unnecessary databases. In the process, we'd like a step ...
3
votes
0answers
35 views

Influence of index rebuild tasks in tempdb on the transaction log?

I have read pretty much about index maintenance and all the pros/cons of reorg, rebuild, online, offline, TEMPDB and timing and right now I am kind of more confused than ever before... The goal is to ...
-1
votes
1answer
58 views

Error with Loop using Cursors - Invalid Object Name with Set = @

Working on a script to Loop through databases to query one table that each Identical Database has with cursors then return result set to table for each database. I think I have the structure for Set ...
0
votes
2answers
47 views

From SQL Server 2005 to SQL Server 2012

I need to convert some databases from SQL Server 2005 to 2012; I know that you must convert it into 2008 before, but I can't rely on a SQL Server 2008 Enterprise installation: is there a faster way ...
6
votes
1answer
143 views

Why is my SP_HELPINDEX showing a lot of indexes?

Using sp_helpindex My_table I have this list: But my table has only these indexes: Why is this happening? I'm using a query to search for "equal indexes" in a particular table. This query is ...
2
votes
2answers
80 views

DDL trigger permission Error

I have a two users, user1 and user2. User1 has sysadmin rights and I have created a database level trigger through sa. Now I am trying to create a table through user2 who has db-ddladmin rights and ...
1
vote
1answer
40 views

How to insert and update bulk data From one DB to another

Two DB on different server , I want to run procedure once in a day so that i can get updated record,if already exists then it should update else insert new So FAR I did like this : if not exists ...
0
votes
0answers
41 views

Issue with multiple execution of sys.sp_addextendedproperty

I'm using SQL Server 2008 R2. I need to store a text as a description of each table of my DB. But I don't want to do this with wizard. I handled to build some code for this purpose. Here's a part of ...
0
votes
2answers
52 views

Create View For All Table Names Listed In Table

I know this data structure is morbidly incorrect, but I am playing with the cards I have been dealt as I can not go back and re-write the structure. I have a table that holds table names, I need to ...
0
votes
0answers
17 views

Why I don't see database engine tuning advisor option in SQL Server 2008 r2?

I see a couple of videos that this feature is available in SQL Server 2008 R2, so I go to management studio, write a query, select the query right click and I suppose to see this option, but I don't. ...
1
vote
0answers
63 views

Prdouction Database went into restoring mode

Yesterday one of my production database went into restoring mode,but fortunately i recovered it using below query USE MASTER EXEC SP_CONFIGURE 'Allow updates',1 RECONFIGURE WITH OVERRIDE GO EXEC ...
1
vote
1answer
50 views

How Merge Replication Works

I'm not DBA at all but I need to do something to synchronize four SQL server databases. I just discover there is a Merge Replication system in SQL Server 2008. At this moment I'm reading the ...
0
votes
2answers
37 views

What would cause an UPDATE statment to hang when executing from an .mdb file to SQL Server 2008 R2 over ODBC?

Is there anything that would cause an UPDATE statement INNER JOINed between a separate MS-Access linked table and an SQL Server 2008 R2 table to hang, when the values are being set on the SQL Server ...
1
vote
1answer
58 views

Really big LOG backups on a DB with Image field

Normally, our log backups has < 10MB size In a big database with a lot of transactions per hour ). But now we have a database, that is storing Images on a Image field. the problem is, the ...
-1
votes
2answers
232 views

Is there a way that I can merge the following update queries into one?

Is it possible to merge the following update queries into a single query? UPDATE tag.ItemTagValue SET [Value] = @firstNode WHERE FkTagID = 6 and TagIndex = 1 ...
2
votes
1answer
72 views

Are there undocumented Linked Server limitations?

I'm working on a SQL Server 2008 R2 SP1 with a Sage product registered as a linked server. There are a few stored procedures that execute a pass-through against the linked server. Once in a while, a ...
1
vote
1answer
37 views

Why does everyone want me to create an alert for severity 025?

Brent Ozar and others suggest to listen to severity 025 SQL-Server events. I searched for the meaning of all different high severity events, looked through different articles and read the most recent ...
0
votes
1answer
25 views

Index Rebuilding Log File Growth

I would like to start running regular index maintenance on a server that has been running for a while without it. The database is running in FULL recover with log shipping. The problem I am having is ...
0
votes
2answers
65 views

SQL Jobs logging only 2 days

When I righ click the job + view history, I can only see 2 days of logs. I'm sure this is a really noob question but I can't find the answer. I've been changing values but with no success. The same ...
2
votes
1answer
39 views

Strange Backup and Alerting Issue

I received alerts from one of my database servers over the weekend that was somewhat disconcerting. There was an alert for each single database within the maintenance plan. A full backup has never ...
0
votes
1answer
47 views

Finding Deleted / Updated record statement from SQL Server transaction Log backup file

One of my production server's data was updated / deleted by someone. I want to find out when the data was deleted or updated, and if it's updated/ deleted then through which statement it was updated ...
0
votes
1answer
30 views

Upgrading a big database inside a transaction

Assuming a big database, where normal upgrade (structural changes and data upgrade) currently takes about 2-3 days (not a issue, is not done during production and about 1-2 times per year. Currently, ...
4
votes
2answers
163 views

Can I restore all tables but not the stored procedures?

I have a testing database that I restore regularly from a production backup file in SQL Server 2008 R2 to get new data. We often edit\change or update the stored procedures in the testing DB to test ...
3
votes
0answers
83 views

Database corruption extends beyond nonclustered index issue in SQL Server

Background I have a server running SQL Server 2008 R2 on the matching MS OS. Over the last few months this server had a RAID drive failure on two separate occasions (which simply required hotswapping ...
2
votes
1answer
31 views

onsite and offsite backup question

We have an sql database of 165GB (includes filestreaming) and we do an onsite full backup every evening using veritas. We would like to also have an online backup in place just so that we don't need ...
4
votes
2answers
70 views

Does a covering nonclustered index help to answer Select * queries?

Our application developers run a lot of SELECT * queries against our database system, unfortunately. The missing index suggestions of sys.dm_db_missing_index contain a lot of cases that indicate to ...
0
votes
1answer
40 views

What are the low hanging fruits of a SQL Server 2014 migration?

I have read about new features and changes of the SQL Server 2014 version. But I still don't know the benefits every server will have by upgrading (from 2008 R2) to 2014? Are there any new features ...
0
votes
1answer
38 views

Moving MSDB database files fails

I'm trying to relocate system databases files. I managed to do that with the tempdb database, but I'm stuck with msdb. When I run: SELECT name, physical_name AS current_file_location FROM ...
-1
votes
1answer
30 views

I cannot add a new Server Role “SQL Server 2008 R2”

I want to add a new user-defined Server Role to my SQL Server instance, but the menu doesn't show all options, it only shows this: Instead of this : and I also tried the T-SQL command to create ...
0
votes
1answer
24 views

Upgrade from SQL Server 2008 R2 SP2 to SQL Server 2014 Error

I am upgrading from SQL Server 2008 R2 SP2 to SQL Server 2014 and receiving an error I cannot get past. Everything checks out before the upgrade runs. The upgrade stops at ...