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

learn more… | top users | synonyms

0
votes
0answers
69 views

why I can't delete old transaction log backup files with an existing scheduled job?

I have a job scheduled in my SQL Server Agent 2008 R2 that runs every 30 minutes, Its part of a maintenance plan that runs: A daily full database backups, differential every hour, and transaction log ...
1
vote
0answers
64 views

Odd Execution Plan

I have a table in SQL Server that is performing very slowly and I have been trying different indexing options to try to speed it up to no avail. I suspect all of the varchar(max) fields may be a ...
3
votes
2answers
49 views

What permissions should the account for MS SQL Server Agent have?

I'm in a situation where I need to change the account for the Server Agent of SQL Server from NETWORK SERVICE to an AD domain account. All I'm doing is making the domain account sysadmin in the ...
1
vote
0answers
36 views

Understanding “Date Last Modified” for “System Stored Procedures”

Whilst reviewing code on a SQL 2008R2 Instance I inspected the Date Last Modified of the System Stored Procedures of the databases. The ones in the DBO schema had a datetime that predates the ...
1
vote
1answer
58 views

Perfmon and SQL Server Clustering

I'll be going onsite to run some performance tests for a client. They have a Windows Failover Cluster, which has a clustered instance of SQL Server. Let's assume for this post that my client's ...
7
votes
1answer
111 views

SQL Server trace based on client TCP port

I have a Windows terminal server with many different users logging in via RDP to run an application. The application makes one or more connections per user to a SQL Server 2008 R2 instance. All users ...
2
votes
1answer
73 views

SQL User with only access to maintain indexes and statistics

I need to have an AD user account with access to all databases on a specific instance of SQL Server 2008 R2, and the only thing that user will be doing is; Reorganize indexes Database integrity ...
2
votes
2answers
81 views

Reviewing all code on a SQL Server Instance

Shortly our development team will be moving an existing SQL Server 2008R2 instance into our Production environment. Ahead of this I have been asked to review all the code on it for anything malacious ...
2
votes
0answers
59 views

SQL Server not starting at Windows startup but starts manually

My SQL Server instance's (SQL Server 2008 R2 Express) service has Startup Type = Automatic. The past couple of times I've restarted my computer the service has failed to start on its own, but it ...
1
vote
2answers
116 views

Disabling certain steps in a job

I have a project coming this weekend where all the jobs has to be stopped but only 1 step in a particular job has to be run. for eg: i have a a 2 step job and the second step must run and the first ...
0
votes
1answer
28 views

SQL Server, firewall and linked servers

I have: SQL Server 2008R2 database server in my LAN and under my control (call it LOCALSQL) remote SQL Server, on which I have much less control (call it REMOTESQL), for which I only have IP, ...
1
vote
1answer
23 views

Separating system databases from MDW SQL Server 2008 R2

We have the Management Datawarehouse feature configure on two of our SQL Servers. Both point to the same central database and upload their results to it. Everything works fine with the built in ...
1
vote
0answers
15 views

update new ID_MergeTo from operation table ID if MergeFlag is true and MergeTo defined?

I have problem I don't know how to solve it. I have two tables Staging and Operation.I create Data Flow Task using SSIS to move data columns from ...
0
votes
1answer
49 views

Missing Database Mail Configuration Wizard in SQL Server 2008 R2

I was able to verify that Database Mail is installed and running but when I expand the management folder there is no Database Mail Configuration Wizard.
0
votes
1answer
33 views

How to delay replication start

I'm creating a new publication for replication using the @allow_initialize_from_backup option to avoid the snapshot immediate creation and therefore locking all objects replicated on articles. The ...
3
votes
1answer
79 views

Total Fragments 99% and I cant get it to stay down [closed]

I have a SQL Server 2008 R2 database it is of size 13000.31 MB and my largest table has around half a million records. It is a database that I have bought. It already has indexes but when I run the ...
1
vote
2answers
139 views

How to make sqlcmd return an ERRORLEVEL other than 0 when the .sql script fails?

I'm running sqlcmd from a batch file and I was wondering how to make it return an ERRORLEVEL other than 0 when something goes wrong with the backup.
0
votes
1answer
32 views

SQL Server: compare names ignoring non-alpha

I have a database table with a text column "title". New rows are inserted all the time, and every time I would like to check for potential duplicates on this column. I define duplicate by "having the ...
1
vote
1answer
73 views

Dynamic Number of Columns in Pivot Table

I have a table to store StudentID, SubjectID, ObtainedMarks. Now I have to create a pivot table to show studentID and obtainedMarks in different subject. Number of subject may differ. I could generate ...
4
votes
2answers
119 views

SQL Server cluster resource won't come online unless its on port 1433

The 2-node SQL Server 2008 R2 cluster works fine until one of the resources got removed. I successfully added it back. However, the cluster is not able to online the SQL Service unless it is on 1433 ...
4
votes
1answer
100 views

Find reason long running stored proc SQL Server

I have a stored proc which is called every 2.5 seconds by a program, 24-7. This proc normally runs under the 10 milliseconds. A few times a day (3 -5 times) it takes around 42953 milliseconds to run ...
2
votes
0answers
35 views

Unable to Update on SQL Server 2008 R2

This is a bit strange so bear with me please. After installing a new SQL Server 2008 R2 on a new server (Windows 2012 Standard) just like the production one, I tried to connect my web aplication ...
2
votes
1answer
179 views

Why dropping all foreign keys from a script is not working? [closed]

I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no ...
2
votes
0answers
107 views

Why does a simple select query produce a deadlock?

I am trying to analyze randomly occurring deadlock problems on an SQL Server 2008 R2 database. The deadlocks occur in very rare cases when SSRS tries to access one of 5 different stored procedures. ...
2
votes
2answers
46 views

Single Virtual Drive Vs Multiple Virtual Drives for SQL Server files

Hi guys I have question about how two different Hard disk setups can possibly affect performance of my database. I have two Options. Option 1: One Physical Harddisk and then Multiple Virtual hard ...
1
vote
1answer
151 views

Removing secondary data files. DBCC SHRINKFILE: Page could not be moved because it is a work table page

I have too many secondary data files (.ndf) created for tempdb. To remove the excess files, I need to empty the file (content will be moved to other files): DBCC SHRINKFILE('tempdbfile8', EMPTYFILE); ...
0
votes
0answers
19 views

Run a script before UnitTesting

The Test Initialize script in the SQL Server Unit Tests runs before each test methods and Test Clean up runs after each test methods. So for example I have this structure UnitTests -- Main ...
1
vote
1answer
169 views

What is the possibility that mirroring a database will damage, corrupt, or erase either the primary or target database? [closed]

The last time my company had tried to mirror some databases, everything worked great until one morning they came in and the primary database in one of the mirroring schemes was just blank. They had ...
2
votes
0answers
79 views

Implementation of SSL ON SQL SERVER DEFAULT INSTANCE

We've purchased an SSL certificate for our Server with a specific CN and implemented the certificate on IIS. We imported the same certificate on the SQL Server machine. However, when trying to ...
4
votes
1answer
81 views

Encoding Debug UTF8 & Latin 1

We have on our DB some international people that got some characters saved incorrectly on our DB (MS SQL-Server 2008 R2). For example we have strings like 'WilcoxonÃ'. We got this data into ...
0
votes
1answer
36 views

SQLCMD - add date to CSV. When run inside Command Prompt it works, when in Job then %date

I've been battling for hours to add a date to a CSV name: For brevity, Im just taking the first character of the date here. In SQL Command, this works: sqlcmd -S localhost -d master -E -W -w 999 -s ...
0
votes
1answer
56 views

Find Without primary key Table in SQL Server 2008 R2? [duplicate]

This is my query to find out primary key, foreign key in SQL Server 2008 R2: select * from information_schema.table_constraints where constraint_type = 'PRIMARY KEY' Know I need to select without ...
1
vote
0answers
40 views

What will do After Press F5? [closed]

select * from Employee; Name id Col1 Col2 Row1 1 11 1 Row2 2 12 3 Row3 3 13 30 Row4 4 14 44 Above is my table After PRESS F5 What process is running in Background ...
9
votes
1answer
173 views

Can you use COUNT DISTINCT with an OVER clause?

I'm trying to improve the performance of the following query: UPDATE [#TempTable] SET Received = r.Number FROM [#TempTable] INNER JOIN (SELECT AgentID, ...
0
votes
1answer
36 views

SSMS 2008 / 2014 showing different values for TempDB file sizes

Here's a fun one. Last week I reconfigured the initial file sizes in our tempdb database to 1GB each. We had an event a few weeks back that locked up our server due to a table with no indexes ...
2
votes
1answer
51 views

SQL Server 2008 R2 - remove line below heading (which has ----,----) - dont have SSIS

I have found this line of code online sqlcmd -S localhost -d master -E -W -w 999 -s "," -Q "SELECT D.* FROM sys.databases D" -o "C:\sqlcmd.csv" This is used in a SQL job, to export to a CSV file. ...
0
votes
1answer
101 views

Duplicate login details per user in SQL Server 2008 R2 Error Log

Being able to view details of who successfully logs into a database and which user's login failed via the error logs is a good thing. However i have a scenario that i need help with - for some reason ...
0
votes
1answer
68 views

How can I reseed all identity columns?

I deleted all rows from 20 tables after disabling the constraints, triggers using the stored proc SP_MsForEachTable. Now before the import happens, I need to reseed all the identity columns back to ...
0
votes
1answer
45 views

I see a constraint in my table thats not in INFORMATION SCHEMA?

When I expand the database "Constraints" object, I see 2 of them named DF_DocumentD_Docum_3F466844 and df_documentdistqty, but when I select to view everything from the "Information Schema.TABLE ...
1
vote
2answers
46 views

Merge Table Row Values to Header of Another Table

Table 1 id | Field | Values ---------------------------------------- 1 | 'Field01' |Name 2 | 'Field02' |Age 3 | 'Field03' |Gender 4 ...
0
votes
0answers
52 views

Why would call to scalar function inside a Table Value Function be slower than outside the TVF?

I am writing a Table Value Function, calling the function takes 10x as long as directly running the code. I traced this to a call to a multi-line scalar function inside the TVF. The call to the ...
1
vote
1answer
33 views

Print, transaction and cursor performance in Transact-sql

I'm having some performance issues when I run a script. I was reading about the problem with print and its performance, but I cannot figure out how to reduce the impact. The algorithm is O(n^2). The ...
1
vote
2answers
49 views

How can I generate an .mdf file containing a subset of my tables and rows?

I need to provide some data to an organisation (lets call them DataCo) that we're working with. Since we're both SQL server shops, my counterpart at DataCo requested an .mdf file. However, our ...
2
votes
2answers
115 views

CHECKDB fails on MSDB and Master system databases

I get the following error within my maintenance plans: Executing the query "DBCC CHECKDB(N'master') WITH NO_INFOMSGS " failed with the following error: "The database could not be exclusively ...
1
vote
0answers
55 views

MYOB ODBC crashes SQL Server

A sister company of ours uses MYOB AccountRight Enterprise and has the read-only ODBC license available. The thing that is annoying them the most is what apparently annoys many a MYOB user: its ...
4
votes
1answer
244 views

Sql Server - Select while insert on another transaction gives unexpected results

I stumbled at situation that changes my knowledge about transactions and locking fundamentally (I don't know much though), and I need help to understand it. Let's say I have a table like this: ...
0
votes
0answers
43 views

Find all possible combinations for each group [duplicate]

I have a table like as: ________________________ |ID| Table1ID| Table2ID| |1 | 1 | 1 | |2 | 2 | 1 | |3 | 2 | 2 | |4 | 3 | 1 | |5 | 3 | 2 | ...
0
votes
1answer
59 views

how to restore?

I am not sure if this has been addressed already. I have been a long time reader but this is my fifth post. I need assistance with a database restore solution. I receive a full BAK and the transaction ...
0
votes
1answer
37 views

Does SQL server 2008 log instance level security permission changes

I just recently logged onto one of our Test environments to find i did not have permissions, turns out my instance security Login had been disabled I have a feeling towards whom may have done this ...
2
votes
3answers
47 views

Monitor SQLAgent

I detected that SQLAgent was not running on one of our servers due to a unexpected memory leak error that caused SQL Agent to shutdown. There were not alerts configured so one main job that was ...