This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (1)

18
votes
3answers
18k 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, ...
6
votes
4answers
14k 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) ...
26
votes
9answers
37k 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 ...
30
votes
5answers
22k 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 ...
9
votes
3answers
6k 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 ' + ...
10
votes
2answers
368 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 ...
4
votes
2answers
2k views

How to refresh AD security group on Sql Server permissions

I am using Sql Server 2008 on Small Business Server 2003; client is using WinXP. I added a user to my Active Directory Security Group; Why can't this user immediately access the database? It seems ...
31
votes
4answers
11k 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 ...
14
votes
1answer
24k views

How to identify which query is filling up the tempdb transaction log?

I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
29
votes
7answers
2k 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 ...
11
votes
3answers
3k 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 ...
4
votes
1answer
6k 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
23
votes
7answers
7k 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 ...
11
votes
4answers
19k views

How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist

I have a SQL server 2008 instance running. Unfortunately during testing I deselected the sysadmin rights for my login and now can't readd it (because I don't have sysadmin rights). There are no other ...
27
votes
5answers
3k 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?
13
votes
4answers
10k 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 ...
7
votes
1answer
2k 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
299 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
5k 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 ...
1
vote
3answers
225 views

Copy database to local from server [duplicate]

As I show , I have server database version-10.50.1600 and local database version-10.0.1600 . How can I copy database (with all tables and data(s)) from server to local which has different version ? ...
1
vote
1answer
1k views

gain admin access to sql express 2008

I have inherited a Windows 7 Professional pc with an existing instance of SQL Express 2008. I have local admin rights on the machine. I do not know the SA password. I am unable to create databases or ...
17
votes
3answers
4k 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 ...
21
votes
4answers
1k views

Does the order of columns in a table's definition matter?

When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an ...
7
votes
5answers
22k 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.
7
votes
3answers
252 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 ...
7
votes
3answers
282 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
626 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 ...
3
votes
1answer
272 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 ...
-4
votes
1answer
423 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 ...
24
votes
4answers
911 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
990 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 ...
14
votes
2answers
13k views

How to find the SQL statements which caused the tempdb grew huge?

The tempdb of an server (SQL Server 2008) increases to 500+G several times every month. Is it possilbe to find out which SQL statements cause the problems? (The problem is usually not caused by create ...
15
votes
3answers
17k 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 ...
12
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 ...
8
votes
2answers
2k views

SQL Disk Setup Advice - TempDB, Log DB, Data file placement question

We have a very active database server with a ecclectic collection of applications running on it. Two of the busiest are a Laserfiche database that does document scanning and workflow processing all ...
13
votes
3answers
6k views

How does shrinking a SQL Server log file affect performance?

I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is ...
7
votes
1answer
2k 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 ...
6
votes
4answers
9k views

Replicating a remote MySQL database to MS SQL Server 2008

I want to replicate the contents of a MySQL database to a MS SQL Server 2008 database. Is this possible? Can anyone outline the steps required in order to achieve this? Thanks.
2
votes
3answers
1k 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
6k 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
2answers
403 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 ...
8
votes
2answers
2k views

SQL Server VARCHAR Column Width

Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255) when VARCHAR(30) will probably do. I ...
8
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 ...
8
votes
1answer
527 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 ...
6
votes
1answer
832 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 ...
6
votes
3answers
1k 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 ...
5
votes
1answer
2k views

How and when does SQL Agent update next_run_date/next_run_time values?

I've been working on code in T-SQL to add new schedules to a SQL Agent job using the sp_add_jobschedule proc in the msdb database. When I add a new schedule (typically a run-once at a specific ...
5
votes
2answers
454 views

Window functions cause awful execution plan when called from a view with external parametrized 'where' clause

I had this issue long time ago, I found a workaround which suited me and forgot about it. But now there's that question on SO so I'm willing to bring this problem up. There's a view that joins few ...
5
votes
5answers
4k 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 ...