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

learn more… | top users | synonyms

2
votes
1answer
122 views

Scripting a maintenance plan

I created a maintenance plan for my SQL Server 2008 R2 using SQL Server Management Studio (SSMS). However, I am unable to export/convert it to a script, in case I need to redo it on another server. ...
1
vote
1answer
118 views

Restoring a database, changing recovery model

I regularly make a backup of my production database, then restore the same onto a test database, so developers can always have recent data for testing. The production database uses full recovery ...
0
votes
1answer
640 views

Why straight SQL query run faster than stored procedure?

I am facing-out a very surprising problem in SQL Server. The problem is when the query run in SSMS window it will return result in ~1 sec and when the query run through a stored procedure it will ...
1
vote
1answer
54 views

Maintenance plan backup and reload the same database

I want in my maintenance plan every night to copy the production database to a backup, and reload the newly-created backup into a test database. By reading here: Restore database - maintenance plan ...
0
votes
1answer
42 views

SSIS unpivot returns different results when run via a SQL agent job

I hope someone can help me. I have an SSIS package which grabs data from an excel (.xls) file, unpivots it from a rubbish format into a 'proper' format, transfers it to a SQL server 2008 R2 db, and ...
1
vote
1answer
163 views

SQL Server Multiple JOINS with Table Value Function - query never ends

I have a query with 4 joins using a table value function to get the data and when I execute it the query never ends. Issue Details Table valued function: CREATE FUNCTION [dbo].[GetIndicator] ( ...
2
votes
2answers
87 views

Why do I need to backup the Database Master Key?

When I make a Database Master Key for a database: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Some Long Password' This page from Microsoft recommends I back it up offsite: ...
4
votes
3answers
894 views

Why does transaction log continue to grow in Simple recovery mode with nightly backups

Before immediately marking as duplicate, I have read Mike Walsh's Why Does the Transaction Log Keep Growing or Run Out of Space?, but I don't think it gave an answer to my situation. I looked through ...
2
votes
2answers
86 views

Database events that will cause deletion of index

In SQL server if we try to change a data type of a column having index ,then it deletes the indexes associated with that column and changes the data type . I was not aware of it and was faced problem ...
3
votes
2answers
194 views

Alert when Job isn't triggered

I have a Sql Job on my server that is triggered nightly from a remote server (once it has completed its backups). I have successfully configured the job to notify me when it fails, but I would like ...
1
vote
1answer
30 views

Backup Certificate SQL Server and return bytes with a Select Statement

I have a certificate in a database in Microsoft SQL Server 2008 R2. I want to get a copy of the certificate so I can burn it onto a USB device and put it in a safe. I don't want to have to go onto the ...
2
votes
2answers
204 views

Is Information in sys.dm_db_index_usage_stats reliable

I'm archiving data from an old system that has no documentation. Lucky me... I'd like to find out when the tables were created, last accessed etc. Can I trust that this query will give me the correct ...
0
votes
0answers
40 views

I am trying to implement Scott Chamberlain's code in SQL Server 2008 R2 . Getting Assembly not present errors

I am trying to use Scott Chamberlain's code, here: http://dba.stackexchange.com/a/17466/45380 I was able to compile it and create the DLL, but now when I try to run SQL CREATE ASSEMBLY I get: Msg ...
3
votes
2answers
249 views

Can I update every datetime in bulk?

The current database assumes all times are local to the user. Now we're changing this so the database stores everything in UTC and our application converts the UTC time in the database to the correct ...
0
votes
1answer
64 views

User defined data types causing implicit conversions

user defined datatypes, i'm looking at some execution plans and seeing implicit conversions happening in the background when a var is declared of type user defined data type and pulling a value into ...
0
votes
0answers
29 views

Run SSIS task regardless of checkpoint

Is it possible to make a task always run, regardless of whether the package starts from scratch or from a checkpoint restart?
5
votes
1answer
91 views

Full Text Search Term Casing Affects Results

I have been experiencing a full text problem when searching using CONTAINS. I have the full text catalog accent sensitive setting set to false. The table contains following rows: Microsoft C# ...
0
votes
3answers
58 views

List all tables that a specific login has permissions [duplicate]

This might be repetitive, but I haven't found a solution that serves my purpose 100%. What I'd like to achieve is to have a script (or SP) that'll list all tables that a user has permissions on. Any ...
0
votes
1answer
52 views

How to find when change tracking was enabled?

How can I determine when change tracking was enabled on a table in a SQL database? Assume that this table has a regular cleanup routine, which means that looking at the oldest record in the change ...
2
votes
1answer
72 views

Reporting services scheduled reports are clogging up sql job history log

This is a bit of a cross-disciplinary question - Hopefully I’ve posted in the right place. SQL server 2008 R2 Question: How can I limit the number of rows in [sysjobhistory] for specific jobs, ...
0
votes
1answer
142 views

SQL Server 2008 R2, error creating file for FileStream

I'm trying to create a file to add to a filestream file group and I get the error: Msg 5120, Level 16, State 106, Line 1 Unable to open the physical file "d:\data\filestreamstore.mdf". Operating ...
3
votes
2answers
70 views

How to tell what invoked a trigger in SQL Server?

I'm working on a legacy SQL Server application, with close to 3,000 tables and more than 10,000 stored procedures. One of those stored procedures is deleting some needed data from a table. I don't ...
-4
votes
1answer
70 views

How to run mssql server dependent application to mysql workbench [closed]

I am very new in mysql workbench. I have created an application in c# which uses sql server 2008 r2 database. Now i have migrated my database on mysql workbench. i want to run my application now. My ...
4
votes
2answers
176 views

How to write a query which finds all circular references when a table references itself? (II)

I have the following schema (names changed), which I cannot change: CREATE TABLE MyTable ( Id INT NOT NULL PRIMARY KEY, ParentId INT NOT NULL ); ALTER TABLE MyTable ADD FOREIGN KEY ...
0
votes
1answer
248 views

How to deploy SSIS package with XML config, using SQL agent

I wonder if anyone can help me with a question. Context I've created a database which needs a front end in MS Excel. When the user presses upload button I want it to start an SSIS package which moves ...
0
votes
0answers
53 views

How to avoid copy of audit trace files

My database is copying all of SQL-Server trace data file everyday. The result is I have to delete them manually everyday because hard disk gets filled. How can I stop this?
1
vote
1answer
315 views

SQL Server database Mirroring on different servers

I tried to perform SQL Server database Mirroring(with SQL Server 2008R2). And I got success to perform mirroring with "High Safety" mode. And for that I have used three instances of SQL Server ...
0
votes
0answers
35 views

Sql Server Agent Job

In my Local SQL Server 2008 I created a scheduled job (like daily email will send to particular user from the database based on condition). Now I decided to host my schedules in hosting server so can ...
4
votes
2answers
150 views

What are the risks (if any) of sharing a service account on more than one server?

I use domain accounts for SQL Server service accounts. Sometimes, when I have multiple servers that are logically or thematically related, I'll use the same set of domain accounts for the service ...
1
vote
2answers
283 views

Joins between linked servers are dangerous, so can we just load the data into a local table before doing the join?

I was just reading the following article which states that it's a worst practice to do JOINs between two linked servers. The only alternative I can see is to get the necessary data from both linked ...
1
vote
2answers
233 views

Fixing an error 9004 without taking the database offline

My database (SQL Server 2008 r2) is running fine but I’m getting the following error in my event log. Error: 9004, Severity: 23, State: 6. An error occurred while processing the log for database ...
2
votes
3answers
118 views

Should I shrink my database?

I have a 1.2Tb SQL Server 2008 R2 database which is more than 50% empty, because I migrated a lot of functionality to PostgreSql. The database is still in use, and still somewhat growing, but I do not ...
3
votes
4answers
959 views

How do I place leading zeros for numbers less than 10 without affecting those 10+?

Probably a newb question, but... I have a table with a VARCHAR field containing numbers 1 through 300. I need to place a leading zero in front of any numbers less than 10 while retaining the original ...
1
vote
1answer
48 views

It is mandatory to create a route when creating an event notification?

I'm starting with service broker. Already managed to setup an event notification for blocked processes based on this answer and it's working flawlessly. Then I wanted to setup another one to notify ...
3
votes
1answer
139 views

difference between index REBUILD and REORGANIZE in relation to recompile in SQL Server 2008 R2

Does the INDEX REORGANIZE index cause recompilation of the stored procs the same way as INDEX REBUILD
0
votes
3answers
130 views

How to display posted on date and time like1 hr ago or 30 min ago

i want to show the posted date like 1 hr ago.. or 30 min ago.. declare @date datetime ='2014-07-24 11:50:22.033'; SELECT CASE WHEN DATEPART(DD,@date)=DATEPART(DD,GETDATE()) THEN CASE WHEN ...
0
votes
1answer
59 views

Securing an Internet Database Connection

I am looking to setup a website and database that are connected via the internet, and am looking to secure the connection. The connection will probably not be over a VPN, so shall be openly exposed. ...
0
votes
2answers
244 views

SQL Server 2008 R2 memory issue

I have read many posts regarding the memory but still its not clear. I have dedicated server SQL Server 2008 R2 with Windows 2008 R2, 32 GB RAM, 2 6 core processor and a database of around 60 GB on ...
2
votes
0answers
248 views

CREATE DATABASE defaulting to wrong log path

I have a script that automates creating a database across multiple servers and on one of my servers a, SQL Server 2008 R2 SP 2 instance, the script failed with: CREATE FILE encountered operating ...
3
votes
1answer
750 views

SQL Server 2008R2 convert collation

I have an existing database that uses the Arabic_CI_AS collation, but I need to convert it to SQL_Latin1_General_CP1_CI_AS the default collation for the server, but the problem is when I use the ...
0
votes
0answers
70 views

full text index not being updated

I have a table that has a full text index defined on it. Change tracking is set to 'Automatic' but I can see that 'Table Full-Text Pending Changes' is set to '58429', and recently added rows (for the ...
0
votes
1answer
38 views

Merge Replication - Merge initial data contained on subscriber

I have two identically structured databases on two separate servers. Both have different data within them. I have set one of them up with merge replication as a publisher and I want the other to be ...
0
votes
2answers
134 views

How to provision the SQL Server Agent service after SQL Server Edition upgrade

I have upgraded SQL Server 2008 R2 Express to SQL Server Standard Edition. After I have upgraded it, the SQL Server Agent was stopped. I have tried to start but it fails. Only when I have changed ...
3
votes
1answer
73 views

Use of Trigger After a Transaction

I have a scenario where there is a transaction being executed to update a few tables. On one of these tables there is a trigger that accesses the data on those tables, but is only planned to be on ...
0
votes
2answers
118 views

Upgraded from SQL Server Express to standard but CPU usage is maximum 50% on dual core [closed]

I have upgraded SQL Server Express 2008 r2 to SQL Standard Edition on amazon EC2 with a Intel Xeon E5-2670 v2 (Ivy Bridge) Processors with 2 vCPU. I have verified the edition by running this: SELECT ...
2
votes
2answers
298 views

Table with 4 million records - different size and performance in development and production

At my company we use SQL Express 2008 R2, which supports databases up to 10GB. We have 2 databases with almost the same number of rows in all tables. But, one of them is 4.5 GB in size (which is in ...
0
votes
2answers
73 views

Analyse what caused a pagesplit afterwards?

Ok normally we have 0-20 page splits per second, but last night it spiked to ~500. Now is there any way to figure out what caused it? I know i can run the following to find mid page splits but it ...
0
votes
2answers
157 views

Query Optimization required - Subquery using Inner Join

Could anybody advise how can I optimize this query below please? SELECT IT.[iInternalTransactionID] , IT.[iInternalAccountID] , C.[iContractID] , IT.[dtTransactionDate] AS TransactionDate , ...
0
votes
0answers
119 views

SQL Server 2000 Named Instance cannot connect remotely

To facilitate a migration I installed SQL Server 2000 Standard Edition and Service Pack 4 as a named instance on server with an existing instance of SQL Server 2008R2 RTM. The issue I have is ...
1
vote
2answers
289 views

Rapidly increasing TempDB write latency

We have two servers that were set very similarly, but not identically. These two servers have hugely different performance running the same application. On server A the application runs successfully ...