This tag is for SQL Server 2008, major build version 10.00.xxxx.
16
votes
3answers
12k views
How do I get back some deleted records?
I mistakenly deleted around 2,000,000 records from a remote SQL Server 2008 table. The server is not granting me access to the backup files on the server side.
Is there any way to get back these ...
11
votes
3answers
2k views
Schema design - best practices?
I understand the capabilities of SQL Server Schemas, but what are the best practices? Sure they give another layer of security, and provide logical grouping of database objects within the database, ...
5
votes
4answers
10k views
Restoring a backup to an older version of SQL Server
When trying to restore a backup to a SQL Server Express 2008 database I got the following error:
Restore failed for Server '...\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended)
...
21
votes
9answers
20k views
Passing array parameters to a stored procedure
I've got a process that grabs a bunch of records (1000's) and operates on them, and when I'm done, I need to mark a large number of them as processed. I can indicate this with a big list of IDs. I'm ...
9
votes
2answers
283 views
Decision criteria on when to use a non-dbo schema vs a new Database
I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out ...
11
votes
3answers
2k views
SQL Server 2008 R2 “Ghost Memory”?
We have a dedicated SQL Server 2008 R2 machine that is experiencing some strange memory issues.. The machine itself has plenty of resources including two quad-core processors, 16gb of RAM and 64bit ...
27
votes
5answers
16k views
What are the main causes of deadlocks and can they be prevented?
Recently one of our ASP.NET applications displayed a database deadlock error and I was requested to check and fix the error. I managed to find the cause of the deadlock was a stored procedure that ...
27
votes
4answers
8k views
What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?
My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other.
Our ...
19
votes
7answers
4k views
Writing a simple bank schema: How should I keep my balances in sync with their transaction history?
I am writing the schema for a simple bank database. Here are the basic specifications:
The database will store transactions against a user and currency.
Every user has one balance per currency, so ...
13
votes
7answers
25k views
Need to migrate SQL Server to MySQL
I have a database on SQL Server 2008 on a Windows server and I want to move all of the data to a MySQL database on a Ubuntu server. I have tried using the SQL Server Import and Export Wizard with the ...
6
votes
3answers
4k views
Why is my database still fragmented after I rebuilt and reindexed everything?
I have a database which I tried to defragment all the tables at once by running this T-SQL:
SELECT
'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) +
'ALTER INDEX all ON ' + ...
3
votes
1answer
4k views
Performance implications of using OPENQUERY in a view
Please see this http://stackoverflow.com/questions/7753380/performance-implications-for-using-openquery-in-a-view question on stackoverflow, it's a cross over subject and would love a dba's POV
6
votes
1answer
1k views
Deadlock error isn't returning the deadlock SQL
Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I am getting this error randomly when one of my ...
3
votes
1answer
185 views
Limiting database security
A number of texts signify that the most important aspects offered by a DBMS are availability, integrity and secrecy. As part of a homework assignment I have been tasked with mentioning attacks which ...
3
votes
5answers
2k views
Convert varchar column to datetime
In SQL Server, I have a varchar column with value such as:
07/May/2012:00:06:22
07/May/2012:00:06:22
07/May/2012:00:06:22
07/May/2012:00:06:22
07/May/2012:00:06:22
QUESTIONS
How do I convert ...
26
votes
6answers
1k views
The smallest backup possible … with SQL Server
Daily we ship our SQL Server backups across the WAN. We need to minimize the size of these backups so it does not take forever.
We don't mind if our backup process takes a bit longer; as it stands ...
10
votes
2answers
1k views
Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN
So we had a long running proc causing problems this morning (30 sec + run time). We decided to check to see if parameter sniffing was to blame. So, we rewrote the proc and set the incoming ...
4
votes
1answer
163 views
How can I know which store procedure or trigger is using a table on SQL Server 2008 R2?
This is the case that in the DB I'm checking, there is an archive table which keeps the user history, and there is a trigger or store procedure that after some time delete rows from this table, in ...
6
votes
5answers
9k views
Restore SQL Server 2012 backup to a SQL Server 2008 database?
Is there a way to restore a SQL Server 2012 database backup to a SQL Server 2008?
I tried to attach file, it does not work.
6
votes
3answers
231 views
Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server
On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: ...
4
votes
1answer
534 views
How to get text for default language
I have so tables:
and so data at Language table:
and so data at Text table:
I have to return text for requested language if it exists and text for default language if it does not exist. Is it ...
2
votes
1answer
226 views
Shrinking log file
Some of the sql log files (idf) are hugh like 5gb. Found a article (http://extended64.com/blogs/rhoffman/archive/2009/06/11/shrink-sql-server-2008-database-log-file-script.aspx) but not sure why he ...
-3
votes
1answer
234 views
Why does a VIEW with a CLUSTERED index not use the index?
I have a view that has a clustered index along with other non-clustered indexes.
A simple SELECT query does not use any of the indexes even when I select an indexed column.
What could be the ...
22
votes
4answers
813 views
Is there a “best-practices” type process for developers to follow for database changes?
What is a good way to migrate DB changes from Development to QA to Production environments? Currently we:
Script the change in a sql file and attach that to a TFS work item.
The work is ...
3
votes
1answer
351 views
Who changed/modified stored procedure in SSMS 2005 or 2008? [duplicate]
Possible Duplicate:
Determining how a schema change occurred?
I have a stored procedure that was modified late last night and just spent the majority of the morning tracing issues back to ...
11
votes
2answers
1k views
Why do wildcards in GROUP BY statements not work?
I am trying to make the following SQL statement work, but I get a syntax error:
SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.*
Here, A is a wide table ...
25
votes
5answers
2k views
Downgrading from SQL Server 2008 to 2005
Database files that are built using SQL 2008 are not compatable with 2005. Is there a work around?
4
votes
1answer
1k views
How to use transactions with SQL Server DDL?
I have a login table into which all inserts are done by a single stored procedure.
CREATE TABLE dbo.LogTable(
LogRefnr int IDENTITY(1, 1) NOT NULL,
LogQuery varchar(255) NOT NULL,
LogTime ...
1
vote
3answers
442 views
SQL Subquery , One to Multiple rows
Following query I have written returns the result as expected, example 121350 rows.
SELECT dbo.Articles_in_Consignment.Consignment_id,
dbo.ORDER_D.Orders_boxsize,
...
17
votes
7answers
5k views
How do long columns impact performance and disk usage?
In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on.
In reality, how much does it really matter? How ...
9
votes
4answers
5k views
Logical operators OR AND in condition and order of conditions in WHERE
Let's examine these two statements:
IF (CONDITION 1) OR (CONDITION 2)
...
IF (CONDITION 3) AND (CONDITION 4)
...
If CONDITION 1 is TRUE, will CONDITION 2 be checked?
If CONDITION 3 is FALSE, will ...
9
votes
3answers
12k views
How do I assign an entire Active Directory group security access in SQL Server 2008?
I would like to use integrated security with my internal application which is all on a domain. Unfortunately, I've never been able to get this to work well. I would like to assign an entire Exchange ...
7
votes
5answers
2k views
Query Running Differently on SQL 2005 vs SQL 2008R2
At my office, we have a query that is pretty ugly, but runs pretty well in production and in the development environment (20sec, and 4sec respectively). However in our testing environment it takes ...
7
votes
1answer
396 views
Is there a reliable way to determine when you should run DBCC CLEANTABLE to reclaim space?
Lately, instead of just growing files when they near 80% file utilization, I've been more proactive at reclaiming space via the usual tricks like defragmenting heaps, adding & dropping clustered ...
5
votes
3answers
694 views
Passing in the table into a stored procedure
Is it possible to pass in the name of a table into a stored procedure?
For example, suppose you have several views of the same table. They all have the exact same structure.
You want a store ...
4
votes
1answer
159 views
Transaction Log maintenance on Mirror database
SQL Server Version: 2008 R2 Enterprise SP2
I am trying to get a handle on our SQL Server maintenance and I came across something I think is incorrect. We have a single production instance with 3 ...
4
votes
5answers
3k views
Pros/Cons Using multiple databases vs using single database
Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented.
While I don't agree I'm having trouble collecting good ...
3
votes
3answers
942 views
Test if any fields are NULL
I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column.
I need something like
SELECT * FROM ...
2
votes
2answers
1k views
Problem Reaching VM SQL Server Instance
Here's what I'm working with right now. I have a host OS which includes a SQL Server 2008 Enterprise instance. I have a virtual machine running on this host which has a SQL Server 2008 R2 Express ...
0
votes
1answer
59 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 ...
13
votes
2answers
335 views
Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?
I would like to use code that I developed in C# CLR to be used in all of the databases on the system so that I don't have to set each to trustworthy and turn CLR on and keep a bunch of the same code ...
8
votes
3answers
514 views
Does SSRS lock the table when querying?
My senior DBA told me that SQL Query execution by default doesn't lock the table.
I was having some issues with my SQL Server Reporting Services (SSRS) report which seems to be getting some issues ...
7
votes
2answers
147 views
Capturing datetime of change in SQL Server CDC
So we've started exploring using change data capture on one of our production databases. We'd like to know the datetime of each change. Reading through walkthrough's and tutorials etc it seems that ...
7
votes
2answers
219 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 ...
7
votes
2answers
1k views
Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?
I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns.
As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
7
votes
2answers
219 views
Mirroring the Distributor
Has anyone had success with mirroring the distribution database? We have a dedicated server as a Distributor. It handles all our push replication from prod to reporting. We'd like to build an ...
7
votes
6answers
421 views
Scheduled backup task doesn't always backup all databases despite always saying job successful
I have a job in SQL 2008 that runs a stored proc to backup all databases. This runs daily via sql server agent job.
It quits with success every day but some days it quits with success only after ...
5
votes
1answer
472 views
How to back up a small database in SQL Server 2008 R2 Express Edition
I will shortly be migrating a database from SQL Server 2000 (part of SBS 2003) to SQL Server 2008 R2 Express Edition
The database is small, and there are only a few hundred short transactions per day ...
4
votes
1answer
500 views
Using MSDTC in SQL Server 2008
I used Hyper-v to install one windows 2008 server machine, and copy the image to create another one and change the computer name, both of them are in the same domain, and I use a domain user as ...
3
votes
1answer
50 views
grant permission
i've got a database-role, -login and user
CREATE LOGIN dummyLogIn WITH PASSWORD = 'dummyPassword', CHECK_POLICY = OFF;
CREATE USER dummyUser FOR LOGIN dummyLogIn;
CREATE ROLE "dummyRole";
with ...
