Questions tagged [sql-server-2005]

SQL Server 2005 (major build version 9.00.xxxx). Please also tag sql-server.

SQL Server 2005 was introduced in November 2005. Current build versions include:

  1. 9.00.1399 – RTM
  2. 9.00.2047 – Service Pack 1, released April 2006
  3. 9.00.3042 – Service Pack 2, released February 2007
  4. 9.00.4035 – Service Pack 3, released December 2008
  5. 9.00.5000 – Service Pack 4, released December 2010

See tag wiki of for more details.

1331 questions
90
votes
6 answers

SQL Server Cannot drop database because it is currently in use... but no sessions displayed

When I try to drop a database I get the error "Cannot drop database "dbname" because it is currently in use". However, when I run sp_who2, there are definitely no sessions connected to this database. I've also set the database to single_user mode…
tuseau
  • 1,835
  • 5
  • 18
  • 18
90
votes
2 answers

How to get the current date without the time part

In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE() but would like it to have a time of 00:00:00.0
Piers Myers
  • 1,013
  • 1
  • 8
  • 9
80
votes
5 answers

What risks are there if we enable read committed snapshot in sql-server?

I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there? eg. Will this affect recovery of the database? Is there anything else we need to do to take advantage of this? I…
Adam Butler
  • 1,531
  • 4
  • 16
  • 16
80
votes
4 answers

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.
69
votes
4 answers

Index Seek vs Index Scan

Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan. What is the difference between and index seek and an index scan? Which performs better? How does SQL choose…
Greg
  • 3,202
  • 5
  • 29
  • 55
53
votes
3 answers

Huge slowdown to SQL Server query on adding wildcard (or top)

I've got a zoo of 20 million animals which I track on my SQL Server 2005 database. About 1% of them are black and about 1% of them are swans. I wanted to get details of all the black swans and so, not wanting to swamp the results page I did: select…
stovroz
  • 631
  • 5
  • 6
51
votes
5 answers

Sql Server Maintenance Plan - Best Practices on Tasks and Scheduling

I am tasked with devising a maintenance plan for our Sql Server 2005 databases. I know for backups I want to do a daily full database backup and transactional log backups every 15 minutes. My problem comes to figuring out which other tasks I want…
Josh
  • 673
  • 1
  • 6
  • 7
48
votes
11 answers

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 copy_from_with_id_1 with identity on id too. The…
bernd_k
  • 12,001
  • 23
  • 74
  • 109
48
votes
5 answers

Can I launch a stored procedure and immediately return without waiting for it to finish?

We have a stored procedure that users can run manually to get some updated numbers for a report that's used constantly throughout the day. I have a second stored procedure that should be run after the first stored procedure runs since it is based on…
Rachel
  • 8,367
  • 20
  • 48
  • 74
39
votes
4 answers

Hard and Fast rule for include columns in index

Is there any hard and fast rule to decide what columns and in which order it should be put in Included in non clustered index. I was just reading this post…
Rocky Singh
39
votes
3 answers

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?
Jango
39
votes
2 answers

How do you stop, take offline, and delete a SQL Server database in recovery?

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 don't care one bit about the database or any of the data, I have…
CrapHands
36
votes
7 answers

SQL Server database size didn't decrease after deleting large number of rows.

I'm no good in SQL, but I've got a database to maintain. There's almost no place left for it, so I've decided to delete all the data for, let's say, year 2008. After executing delete query (had about 10 000 000 rows cleaned) and cleaning…
Marat
  • 463
  • 1
  • 4
  • 7
34
votes
11 answers

How can I get the correct offset between UTC and local times for a date that is before or after DST?

I currently use the following to get a local datetime from a UTC datetime: SET @offset = DateDiff(minute, GetUTCDate(), GetDate()) SET @localDateTime = DateAdd(minute, @offset, @utcDateTime) My problem is that if daylight savings time occurs…
Rachel
  • 8,367
  • 20
  • 48
  • 74
34
votes
3 answers

Are Views optimized when I add a WHERE clause to them?

Does it make a difference if you filter a View inside or outside the View? For example, is there any difference between these two queries? SELECT Id FROM MyTable WHERE SomeColumn = 1 Or SELECT Id FROM MyView WHERE SomeColumn = 1 And MyView is…
Rachel
  • 8,367
  • 20
  • 48
  • 74
1
2 3
88 89