This tag is for SQL Server 2005, major build version 9.00.xxxx.

learn more… | top users | synonyms (2)

15
votes
5answers
6k views

How do I copy a table with SELECT INTO but ignore the IDENTITY property?

I have a table with identity column say: create table with_id ( id int identity(1,1), val varchar(30) ); It's well known, that this select * into copy_from_with_id_1 from with_id; results in ...
4
votes
2answers
938 views

SQL Server 2005 : How to use pivot instead of a bunch of case statements?

Please show me how I can modify this SQL select to use pivot instead of all the case statements. SELECT t.ProjectId, COALESCE(MAX(ExecutiveChampion), 'n/a'), ...
11
votes
3answers
14k views

Rebuilding the transaction log

We have a very large database (~6TB), whose transaction log file was deleted. We have tried: Detaching and reattaching the database; and Undeleting the transaction log file but nothing has worked ...
8
votes
3answers
1k views

Is there a way to programmatically script all objects associated with a given table?

I know that in SQL management studio I can right click a table/trigger/key and script object as.... Is there a way to do this programmatically, given an object's name? If so, is there a way to find ...
15
votes
2answers
15k views

What are database statistics, and how can I benefit from them?

I've heard mention of statistics that SQL Server keeps by default. What are they tracking, and how can I use this information to improve my database?
12
votes
2answers
1k views

Is there any tangible difference between a unique clustered index and a clustered primary key?

I understand that there may be a difference in meaning or intent between the two, but are there any behavioral or performance differences between a clustered primary key and a clustered unique index?
16
votes
3answers
2k views

Why would someone put `where 1=1` in a query?

I came across a view in our database today where the first statement in the where clause was where 1 = 1. Shouldn't this return true for every record? Why would someone write this if it isn't ...
10
votes
4answers
3k views

Is there any benefit of a primary key that comprises all columns of the table?

I have a table with four columns that are all non-nullable, and the data is such that all four are needed to distinguish a unique record. This means that if I were to make a primary key, it would need ...
8
votes
2answers
1k views

Possible to run two DBCC INDEXDEFRAG commands simultaneously, each on a different table?

I am currently running a script which performs a DBCC INDEXDEFRAG on every table in a SQL Server 2005 database, one table at a time. Using DBCC DBREINDEX instead of INDEXDEFRAG is not an option, due ...
8
votes
1answer
631 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
525 views

How does MS SQL Server decide when it can use “Parallelism” when executing queries?

How does MS SQL Server decide when it can use "Parallelism" when executing queries? I recently posted another question here that, when looking at the actual execution plan used parallelism when I had ...
6
votes
1answer
696 views

adding an order by to this query returns faster than without, why?

Any ideas on why adding a sort to this query returns considerably faster than without the order by? I would expect the opposite so what could make this happen? SELECT TOP (500) ...
4
votes
1answer
368 views

Execution plan issue requires reset on SQL Server 2005, how to determine cause?

We have a web application that delivers training to thousands of corporate students running on top of SQL Server 2005. Recently, we started seeing that a single specific query in the application went ...
7
votes
1answer
400 views

Full Plan Cache Behavior

In SQL Server 2005, what happens when the plan cache fills up? I have done some research, and from bits and pieces gathered it seems that cached plans are given an "age" that consists of it's weight, ...
9
votes
2answers
2k views

Unique Constraints on Nullable Columns in SQL Server 2005

In this one project I am working on, I need to set a particular field to be unique (not a problem!) but if the field is null I want the constraint to be ignored. In Sql Server 2008 I use filtered ...
9
votes
3answers
2k views

How to change the firing order of Triggers?

Realy I rarely use triggers. So I met a problem at first time. I have a lot of tables with triggers (2 or more for every table). I would like to know and change the order of firing triggers for every ...
10
votes
6answers
723 views

Did anybody use sqlcmd mode in practice? [closed]

SQLServer 2005 introduced something called SQLCMD Mode msdn link. At first glance this mode adds variable subsitution from command line batch files, and some escaping to OS commands. Is this ...
11
votes
2answers
4k views

How do I change the instance name in MS SQL 2005 without installing a new instance?

I have an instance that really has nothing on it, it's merely been installed (it was intended for one project, but that never actually got done on this server, the project was done in duplicate on ...
27
votes
5answers
4k 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?
1
vote
1answer
774 views

SQL 2005: logical consistency-based I/O error: incorrect checksum

I got this error this morning during the execution of a SSIS package: SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x5fb84eb5; actual: 0x7fb84eb5). It ...
13
votes
2answers
34k views

Sql Server database in recovery: How to stop, Take ofline or delete?

I have a development database which has entered recovery mode on restart, and has been recovering for the last hour or so. I need to either stop the recovery, or kill it in some way. I dont care one ...
1
vote
1answer
768 views

SQL Server 2005 query erroring with message 7105, slot for LOB data type node does not exist

SQL Server 2005 sp3 Standard on 64-bit Windows Server 2008 inconsistently receiving an error that seems to indicate corruption select * from some_table where text_field like ‘%the fox did not go ...
0
votes
1answer
141 views

SQL Server Assertion Error

Does anyone have a solution to this problem? SQL Server Assertion: File: , line=10850 Failed Assertion = 'GetLocalLockPartition () == xactLockInfo->GetLocalLockPartition ()'. This error may be ...
9
votes
3answers
8k views

What is the difference between Shrink Database and File?

DBCC ShrinkDatabase() DBCC ShrinkFile() Do I need to run both DBCC commands in order to shrink the database? What is the difference between these two above?
8
votes
10answers
8k views

Shrink a database below its initial size

I've got a dev database that is a copy of live 30gb, we have deleted some data that is not needed in dev that brings the data file used to 20gb. Its SQL server 2005 So we have about 33% unused. I ...
8
votes
2answers
459 views

SQL 2005: Can we determine how much the rebuild index maintenance job can grow database log files?

On SQL server 2005, if all the databases are in Full mode (with hourly transaction log backups), is it possible to determine if rebuilding all indexes of a database can grow log file of a database? ...
1
vote
1answer
136 views

SQL Server DB Restore Clustered to Single Node?

Can you restore a DB from a SQL Server 2005 Clustered Environment (Prod) to a single server (single node) (SQL Server 2005) in Dev/Sys? Cheers, Conor
1
vote
1answer
56 views

tsql to know when a database was last shrunk

In SQL 2000, 2005 and 2008, how can I know when databases were last shrunk on a MS SQL server? I want to do this using TSQL. Regards Manjot
8
votes
5answers
1k views

Using IF in T-SQL weakens or breaks execution plan caching?

It has been suggested to me that the use of IF statements in t-SQL batches is detrimental to performance. I'm trying to find some confirmation or validate this assertion. I'm using SQL Server 2005 ...
1
vote
3answers
282 views

Is there an equivalent of “OPTION(RECOMPILE)” or “WITH RECOMPILE” for an entire connection?

I'm curious. Is there any way to prevent SQL query execution plans from being cached for an entire connection's duration? Can I run a command that basically says, "use OPTION(RECOMPILE) for all ...
3
votes
2answers
165 views

Database Mirroring of SQL server

I have two databases that are mirrored to another server using database mirroring. The mirror server has to be down for some reason for few days. Now, the production server is having principal ...
2
votes
1answer
37 views

How do I list the database files contained in a SQL Server 2005 backup file using T-SQL?

How do I list the database files contained in a SQL Server 2005 backup file using T-SQL? For instance: The restore process will create: bob.mdf bob.ldf etc...
4
votes
3answers
4k views

SQL Server 2008 Table Maintenance - Rebuild, Reorganize, Update Stats, Check Integrity etc

I'm migrating a ~15GB database from SQL Server 2005 to a new server running SQL Server 2008, and along with that I need to create all the new Maintenance Plans. I can take care of all the backup ...
1
vote
4answers
443 views

Help understanding SQL Server 2005 execution plan

One of my stored procedure has long execution time (average around 4 to 7 minutes). Now I trying to tweak it an make it run faster. I am looking at execution plan and two things I see that using ...
5
votes
8answers
885 views

Granting SA privileges for Developers on the development box

In spite of our vehement protests, our management has decided that the development team must be granted 'sa' rights on the development server. The catch is that we, the DB support group are still ...
3
votes
4answers
476 views

SQL Server restore from transaction log

In SQL Server 2005, if you have a database with no backup - but it is running in full recovery mode - is it possible to "roll back" some deletes? If yes - how?
2
votes
1answer
1k views

SQL Server Migration restore backup vs copy data and log files

Hey guys, I'm tasked with doing a SQL Server 2000 to 2005 migration. I will be doing a side-by-side migration. I've read from a few different sources that I can either: perform a backup of the ...
4
votes
3answers
42 views

What is not persisted in a database backup / restore? (SQL Server 2005)

If I take a backup of a database on a SQL 2005 Server, and then restore that backup to a different (new) database on the same database server instance, what things will not be persisted (things like ...
3
votes
5answers
2k views

Can I restore a database to a point of time without backup?

I have an SQL 2005 database with logging set to FULL but without any backups. Is it possible to restore it to a point of time?
8
votes
4answers
167 views

SQL Server 2005/8 Query Optimisation Hints

I'm looking at educating a team on writing better SQL Server queries and was wondering what people's best hints were for improving performance. For instance I once had a DBA who insisted that ...
2
votes
1answer
365 views

SQL Server 2005 Express in VMware causing very high CPU load

I'm having the problems described in KB937745 - very high CPU usage and the Application Log is reporting something like this: The client was unable to reuse a session with SPID SPID, which had been ...
3
votes
1answer
144 views

Change SQL Server 2005 from Standard Edition to Workgroup

I have been tasked with performing an edition change from SQL Server 2005 Standard Edition to SQL Server 2005 Workgroup Edition, on Windows Server 2003 Standard Edition. Due to a previous failed ...
4
votes
5answers
430 views

Improving SQL Server performance on VMware under Linux

I have a Linux server in a small company (3 users) acting as a domain server and running two VMs under VMware: one has SQL Server 2000 on it and one has SQL Server 2005, in each case running on ...