This tag is specifically for the 2008 version of Microsoft SQL Server.
0
votes
0answers
4 views
sql database tuning and optimisation using Recommendation Provided by “Database Engine Tuning Advisory”
Our Database has increased very big (SQL 2008 R2), we are trying to optimize performance of DB.
Can we Follow Recommendations provided by "Database Engine Tuning Advisory" by giving .trc file ...
1
vote
0answers
34 views
Search for multiple keywords related to separate columns on a single table
Imagine I have a table with the schema shown. Each of the columns represent a searchable collection that I would like to be picked up in the search. I am looking for a method that would derive the ...
0
votes
2answers
34 views
Dropping and recreating indexes
I most often come in a situation where the users move there database from one server to another and immediately after they move they face performance problems(slowness).
I somehow manage to rebuild ...
7
votes
5answers
2k views
Are there tools to perform data comparison between two different schemas?
We are migrating our database to a new schema but want to validate the data has been moved correctly.
Traditional data comparison tools can compare two databases for differences if the schemas are ...
1
vote
2answers
35 views
When should I use the shrink option
I have seen a lot of blogs stating that shrinking is not a good habit as it will reduce the performance of the system. I agree with all those things it will lead to side effects like fragmentation, ...
3
votes
1answer
221 views
SQL Server 2008 Setup Error 0x80070490
I am trying to install SQL Server 2008 x64 on Windows 2008 R2 and keep getting the following error:
SQL Server Setup has encountered the following error: Element not
found. (Exception from ...
2
votes
1answer
137 views
Will a primary key be added as a clustered index?
I've inherited a database where no primary keys were defined on the tables. There are also no clustered indexes assigned to the tables.
If I perform an alter table to assign a primary key will this ...
0
votes
1answer
202 views
How to override SSIS 2008 package config file path?
I have an SSIS 2008 package with a single configuration named Config. I would like to use the dtexec /SET switch to change the path of the config file that it uses so our developers that build the ...
4
votes
2answers
74 views
Why do linked servers have a limitation of 10 branches in a CASE expression?
Why does this CASE expression:
SELECT CASE column
WHEN 'a' THEN '1'
WHEN 'b' THEN '2'
... c -> i
WHEN 'j' THEN '10'
WHEN 'k' THEN '11'
END [col]
...
1
vote
1answer
69 views
SQL Server replication conflicts after migration from 2000 to 2008
I got a suggestion over at Stackoverflow to post here....greatful for any and all help.
Please bear with me I think this might take a while to explain. For many years now my company has hosted a ...
0
votes
0answers
35 views
Cannot run job NT Authority\System error [duplicate]
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
'domainname\servername$'.
I receive this error when I try to run the job. I have full permissions; I have also tried to run this job under ...
12
votes
1answer
295 views
Best practices for connecting DBs that are in different geographical regions
We are about to set up SQL Servers in different countries. We need to link them, but we don't have to have a direct link (as in linked server). In other words, they can be loosely coupled.
Is it ...
0
votes
1answer
42 views
Migration to databases Domain Users do not have access
I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be ...
-1
votes
0answers
43 views
Unexpected index scan against partitioned table
I have a set of partitioned tables with CIX on (ID, DATE) and partitioned on DATE.
There is another identical set of non-partitioned tables with CIX on ID.
Case A: T1 and T2 are non-partitioned ...
1
vote
0answers
30 views
Job accessing linked server yields error 7391
I created a stored procedure which queries data from a linked server to insert into a local table.
I tested this stored procedure by directly executing it; it works fine.
I have to run this stored ...
2
votes
2answers
59 views
Is there a standard way to use common data and procedures across multiple databases?
We have several independent databases that have data and code in common, not in the sense that it is accessed between the databases but in the sense that the data means the same thing in each ...
0
votes
0answers
37 views
How to improve the performance for SQL Server Count(distinct()) and Sum() functions? [closed]
The below one is my query. It's taking 12 seconds for the execution process. The count(distinct()) and SUM() functions are taking long time for execution. I tried it after create the non-cluster index ...
1
vote
0answers
44 views
Is there a way to generate an execution plan for a stored procedure before executing it?
We have a very large (many millions of rows) table that contains some (few million) rows with invalid values in particular columns.
A stored procedure exists to correct the invalid records but it ...
1
vote
0answers
248 views
SQL Server Database Replication - Master / Slave configuration [closed]
I have a database server, SQL Server 2008, that's inside a private network. Now I need to make a database there available for a web server for my web application so that people don't need to come to ...
3
votes
2answers
83 views
One filegroup, multiple datafiles, how to get list of tables in each file
In one of my production databases, I have a filegroup with 3 data files sitting on 3 different disks. There are over 50 tables on this filegroup.
How do I identify the location of each of the 50 ...
6
votes
1answer
57 views
Dropping Hypothetical Indexes
In the past I thought I'd deleted hypothetical indexes using either a DROP INDEX statement for clustered indexes and DROP STATISTICS statement for non-clustered indexes.
I have a database that is ...
3
votes
1answer
58 views
Backing up file groups with restore to earlier backup
I have a task: I have created a database with a filegroup lamb that has a few tables.
I need to extract a csv into a table in a filegroup named lamb and back up that filegroup.
Then extract another ...
7
votes
1answer
202 views
Hard limitation for merge replication article count?
A bit of background. We developed an app that uses merge replication. Right now, we are currently publishing about 212 articles, and everything is fine, as it appears to fall into the 256 article ...
1
vote
2answers
256 views
Oracle Linked Server query error
We have a problem with a query from a SQL Server 2008R2 - 64bit
to an Oracle Linked Server.
We use the Oracle 64-bit driver for Windows.
When we query a certain table we get the following error:
...
3
votes
1answer
181 views
Schema design: Use of association (aka: bridge/junction) table vs foreign key constraint with composite index containing a non-key field
This is an inventory database for IT assets. The models used are trimmed in order to focus on the problem at hand. Using SQL Server 2008. Thanks for taking the time to read and for any input you can ...
1
vote
2answers
86 views
Database running out of space
My database has 16MB of space left.
I used to just truncate as I was taught but I found these links that advise against truncating:
...
2
votes
4answers
93 views
Trigger Performance : Is Using Trigger is a good way?
We are using SQL Server 2008 for a j2ee web application. I need to maintains the audits for updates for each column with their previous value and new value on one of my table which is very heavy, ...
3
votes
1answer
44 views
Convert SQL Server 2012 SSIS package to 2008
I've created a few SSIS packages in SQL Server 2012 that I need to run on SQL Server 2008. I've been told that it is impossible in their current state.
Is there a way to convert the 2012 packages to ...
7
votes
3answers
345 views
Worse performance on a new server
We've been on a dedicated server (single quad-core, 6 GB RAM) and are moving to a new dedicated server (2x hex-core, 32 GB RAM). Both are Windows Server 2008, SQL Server 2008. The performance on the ...
4
votes
1answer
47 views
Log file growth issues
We're consolidating data from a bunch of databases into four reporting databases each night.
Because the entire dataset is imported each night we do not need to be able to restore the data to a point ...
7
votes
1answer
126 views
Automatic decimal rounding issue
The question is relatively simple. I need to calculate 3 columns where the mid results are huge decimals, and I'm running into a problem early on with SQL Server basically rounding the decimals ...
1
vote
1answer
17 views
Finding oracle physical database files
I'm using oracle 11g and I need to find the database files to copied to another server. The database was created from migrating from SQL Server 2008 to 11g. There doesn't seem to be a counterpart for ...
0
votes
1answer
46 views
Should database files be on different disk(s) than OS?
My very short overview
SQL Server databases are installed as virtual machines on VMWare, there are plugged a few disk areas with the same (or very similair) parameters
versions are SQL Server ...
2
votes
2answers
77 views
Using T-SQL to dynamically create Extended Properties
I would like to add Description and Status in the Extended Properties of a large database to all my tables and columns so that someone can fill in the values.
It would be very laborious to add these ...
1
vote
1answer
48 views
How can we get table name from inside trigger?
I need to pass the table name from trigger to procedure.
ALTER TRIGGER [dbo].[Trg_ProjectCreation]
ON [dbo].[Projects]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
-- procedure call
...
4
votes
2answers
69 views
Should I use a composite or single-column index?
I have the following columns in my database table (Medicines).
ID bigint,
MedicineName nvarchar(50),
BrandName nvarchar(50),
MedicineCode nvarchar(20),
and price,quantity.
I am making a stored ...
0
votes
2answers
137 views
Resume Logshipping after Primary database restored
We have Database (D1) as a primary database and Log shipping setup to the secondary database called (LS_D1). Now what we are doing is taking backup of D1 database and restore it on D2 database. We do ...
0
votes
2answers
83 views
Sync Indexes between two tables
I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database.
I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync ...
1
vote
2answers
54 views
SQL Server Memory Clerk Description
Trying to understand different memory clerks reported by sys.dm_os_memory_clerk however, not able to find description of the each individual memory clerk either on web (google and binged) or in SQL ...
2
votes
2answers
70 views
Linked Server Risks
I'm implementing a new feature which requires data from databases on multiple servers. I just need to union data from all these servers and sort it. The two options that come to mind are:
Use linked ...
5
votes
3answers
110 views
How does “Be made using the login's current security context” pass the users password to remote server
Say you have a SQL Server with a "Linked Server" to another server - said "Linked Server" is set to use "Be made using the login's current security context" as its authentication model.
How does the ...
1
vote
3answers
44 views
How to create new users for restored databases?
I am not too familiar with SQL Server and am facing the following problem: The server was freshly installed and the only existing login is sa.
Then a couple of databases were restored from earlier ...
1
vote
1answer
41 views
Key lookup partition pruning
I have a query inner joining multiple tables all of which are partitioned and have 10 partitions each:
SELECT A.COL1, B.COL2, C.COL3
FROM A
INNER JOIN B ON A.ID = B.ID
INNER JOIN C ON A.ID = C.ID
...
2
votes
1answer
68 views
Visualiser for spatial query results
This question Whats the easiest way to get sql 08 spatial data onto a map? (and many other articles) recommends Craig Dunn's Geoquery, but all the download links appear to be kaput.
SQL Server ...
1
vote
3answers
141 views
Performance issues moving 1 million rows between tables
I want to replicate 1 million rows from table1 to table2. I have written a job with a query like this:
delete from table1 OUTPUT *.delete into table2
I am executing this job every hour, but it ...
4
votes
1answer
134 views
Indexes file damaged, was on ramdisk
I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file ...
1
vote
2answers
103 views
Auto-generate scripts to create tables, indexes, etc
In SSMS, we can right click a database/table/index/... and select the "Script As" option to generate a drop and create script.
Is there a way to automate this script generation and drop the scripts ...
2
votes
1answer
114 views
Cannot connect to SQL Server after setting sa password to blank
I have installed SQL Server 2008 R2 on a Windows 2008 server. I need to set the sa password to blank for some reasons to make an application works; but it seems like the password changes automatically ...
0
votes
1answer
17 views
Is there a system primary key associated with each row?
Does a system primary key or unique identifier for each row in a table exist? Like @@ROWID, @@IDENTIFER, or some other identifier that uniquely identifies each row regardless of how it was created? ...
1
vote
1answer
46 views
Get the 2nd or 3rd occurrence of a value in a delimited string
I have the following table:
==========================================================
| Name_Level_Class_Section | Phone Num |
...
