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

learn more… | top users | synonyms (2)

6
votes
1answer
678 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
363 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
392 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
710 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
747 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
32k 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
729 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
136 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 ...
8
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
457 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
130 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
53 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
275 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
161 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
440 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
853 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
445 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
41 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
356 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
142 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
426 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 ...