Questions tagged [sql-server-2008-r2]
SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.
3,780
questions
1
vote
1
answer
1k
views
Cursors and concurrent users
I typically avoid cursor operations like the plaque, however, I have come across my first problem where using a cursor outperforms a query. So I am forced to use it.
I have created an elaborate ...
- 505
2
votes
1
answer
1k
views
Change Data Capture missing changes for a specific record
I have enabled CDC in SQL Server and it generally works well but sometimes the changes are not recorded for some reason. And only for a couple of records. But then if I issue an UPDATE from SSMS, the ...
- 131
1
vote
1
answer
2k
views
Linked Server using ODBC too slow
The scenario is that developers used a linked server object on ServerM to reach ServerST. This linked server was setup as a "SQL Server" type using a domain login that exists as a login on ServerST.
...
- 1,453
12
votes
2
answers
38k
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,467
2
votes
1
answer
3k
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 model,...
- 1,467
1
vote
3
answers
15k
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 ...
- 111
1
vote
1
answer
654
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 ...
- 1,467
1
vote
1
answer
321
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 ...
- 33
1
vote
1
answer
7k
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]
( ...
- 13
8
votes
2
answers
2k
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:
http://msdn.microsoft.com/...
30
votes
4
answers
56k
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 ...
- 438
2
votes
2
answers
1k
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,131
4
votes
2
answers
2k
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 ...
- 443
1
vote
1
answer
442
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 ...
11
votes
2
answers
7k
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 ...
ekenman
3
votes
2
answers
3k
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 ...
- 205
0
votes
1
answer
314
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 ...
- 319
1
vote
0
answers
59
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?
- 303
5
votes
1
answer
996
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#
...
- 151
0
votes
3
answers
2k
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 ...
- 3
0
votes
1
answer
2k
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 ...
- 751
2
votes
1
answer
410
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, ...
- 257
1
vote
1
answer
1k
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 ...
- 131
3
votes
2
answers
5k
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 ...
- 133
-6
votes
1
answer
280
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 ...
- 101
4
votes
2
answers
2k
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 (ParentId)...
- 881
0
votes
1
answer
5k
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 ...
- 33
1
vote
1
answer
3k
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 ...
- 111
6
votes
2
answers
8k
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 ...
- 395
1
vote
2
answers
2k
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,702
2
votes
2
answers
8k
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 '...
0
votes
3
answers
3k
views
Whether Logfile Shrinking is good or bad one for Performance in SQL? [duplicate]
I am using SQL Server 2008 R2 database and on our SQL Server instance we have more than 70 databases. In a few databases I can see that the transaction log file size is more than 10 GB. I am shrinking ...
- 19
2
votes
3
answers
2k
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 ...
- 7,244
7
votes
6
answers
189k
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
1
answer
290
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 ...
- 2,826
3
votes
1
answer
1k
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
- 31
4
votes
2
answers
5k
views
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. This is my code:
declare @date datetime ='2014-07-24 11:50:22.033';
SELECT CASE WHEN DATEPART(DD,@date)=DATEPART(DD,GETDATE()) THEN
...
- 85
1
vote
1
answer
208
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.
...
- 325
0
votes
2
answers
2k
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 ...
- 1
8
votes
1
answer
9k
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 ...
- 2,666
0
votes
1
answer
63
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 ...
- 167
1
vote
2
answers
3k
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 ...
- 121
3
votes
1
answer
707
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 ...
- 133
0
votes
2
answers
341
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 ...
- 121
5
votes
3
answers
11k
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 ...
Natalia
0
votes
2
answers
592
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 ...
- 287
0
votes
2
answers
7k
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
, (CL.[...
- 55
1
vote
0
answers
959
views
SQL Server 2000 Named Instance cannot connect remotely [duplicate]
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 ...
- 333
2
votes
2
answers
7k
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 (...
- 131
5
votes
2
answers
3k
views
Is it possible for SQL Server to miss rows on a select * where PK = "value" query
We have just migrated an application to an SQL Server 2008 R2 from SQL Server 2005 and moved from a physical to virtual hosting environment.
I have a very strange error in our application and I was ...
- 179