This tag is for the 2005 version of Microsoft's SQL Server.
15
votes
4answers
996 views
Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?
Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns.
The database development started in ...
9
votes
3answers
584 views
Need Help Troubleshooting Sql Server 2005 Deadlock Scenario
I am running into a deadlock scenario where the only participants in the deadlock appear to be a single table and a single stored procedure that deletes from that table. I drew that conclusion based ...
9
votes
3answers
2k views
Is it possible to restore sql-server bak and shrink the log at the same time?
We have a bak file from a client that we have transferred to our developer offices for problem investigation. The backup is currently 25GB and the restored database is about the same size however it ...
-6
votes
1answer
723 views
How do I convert this Access query for SQL Server? [closed]
I have a Query used in Access which does not work in SQL Server as it stands:
SELECT
mstPatient.PatientID,
mstPatient.PatientID AS PKNO,
...
7
votes
3answers
456 views
More Databases Per Sql Server 2005 Instance
Our company currently hosts about 400 databases per SQL Server 2005 instance. 500-600 databases and we see performance start to deteriorate.
Database size ranges from 50mb to 40-60gb in the extreme, ...
11
votes
2answers
7k 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?
7
votes
3answers
2k views
SQL Server performance sudden degradation
I have a SQL Server 2005 that has become unpredictable of late, and I'm scratching my head as to why. Queries that executed in seconds are changing plans and taking minutes (taking the time in full ...
0
votes
1answer
99 views
Restoring a differential backup in SQL server 2005
I have created a full back up of my database by executing the line below in my application:
Dim Query As String = "backup database Ari to disk='G:\Documents and Settings\i\Desktop\BKAR\" & ...
17
votes
5answers
27k views
How to the get 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
3
votes
1answer
316 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 ...
7
votes
3answers
2k views
What is the best method to add error handling in SQL 2005 stored procs?
What’s a good way to make stored procs robust enough that they can scale very well and also contain error handling?
Additionally, whats the best way to handle multiple error scenarios in a stored ...
24
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?
7
votes
5answers
381 views
sql server concurrency performance
We have encountered some performance issue on our production environment.
we found that when active sessions go up above 25, the usage of CPU reach to 100%
and it takes long time to go down.
The ...
9
votes
3answers
3k views
What can cause a mirroring session to timeout then failover? SQL Server 2005
We have two production SQL Servers running SQL Server 2005 SP4 with cumulative update 3. Both servers run on physical machines that are identical. DELL PowerEdge R815 with 4 x 12 core CPUs and 512GB ...
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
376 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 ...
4
votes
2answers
649 views
Out-of-sync Replication in Sql Server
Okay, a little complex:
One local DB table is being replicated to another local DB with same table. Worked for years. User account on replication job is gone, so job was failing. I updated it to a ...
8
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 ...
7
votes
1answer
199 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 ...
2
votes
1answer
133 views
SQL database restore failed
I have a c# application that is connected to my sql server 2005 database, i am working on the application to insert some entries in the database and due to power failure system got down and from that ...
2
votes
3answers
2k views
SQL Server: How to call stored procedure without schema name?
SQL Server 2005std/2008web edition
db=user=schema=owner='John'
I'm moving website from one server to another. This piece of code works well on old server with SQL Server 2005.
Dim sqlCmdVehicle ...
1
vote
2answers
1k views
BACKUP failed to complete - with spaces, for no reason
Every day my database backup fails for some databases and does fine for others. I'm calling the backup through a Stored Procedure that runs scheduled in a job. Tonight I'm going to run the procedure ...
1
vote
2answers
528 views
Solving high page load time using SQL Profiler
I'm looking into a performance of a login page taking 10-13 seconds to load. I'm using a DEMO database to test the performance and tweak to see any improvement. I'm using SQL Profiler to capture what ...
1
vote
1answer
922 views
SQL Server 2005 collation issue during installation
Every time I try to install SQL Server 2005 on to a Windows 7 machine it installs properly but the collation installed is sql_latin1_general_as instead of sql_latin1_general_cp1_ci_as. I have read ...
1
vote
3answers
609 views
SQL Server 2005 database engine login failed error?
I installed SQL Server 2005 Enterprise Edition on Windows xp.
This account exists in administrators role.But I can't login to SQL Server database engine using Windows authentication method or sql ...
0
votes
3answers
1k views
Max connections SQL Server Express could have?
I have SQL Server 2005 Express edition installed on one of my client's machine, and lots of other users are also connected to that SQL Server through the C# application that I have provided them as an ...
0
votes
1answer
78 views
Converting SQL Server 2005 schema to PostegreSQL 9
I have a fairly simple SQL Server 2005 schema that I exported to a tSQL file.
I am looking for a tool, preferably open source and runs on Win 7 (Ubuntu 2nd choice) that will convert the transact SQL ...
6
votes
1answer
9k 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.
11
votes
3answers
7k views
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 ...
11
votes
2answers
807 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?
7
votes
3answers
986 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 ...
12
votes
3answers
4k 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 ...
10
votes
4answers
3k 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 ...
10
votes
4answers
1k 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
1answer
975 views
SQL Server TempDB behaviour in large memory environment
Reading over In-memory SQL Server log file reminded me of a question I had a short while ago.
We have a SQL Server that has 512GB of RAM, the main database is (only) 450GB. We see quite a lot of ...
8
votes
1answer
205 views
Most efficient way to get stats on an entire SQL Server database
What I'm looking to do is review our databases and lock down any auto-shrink settings, as well as get a handle on which databases/tables are highly fragmented.
Is there a particular script I can run ...
6
votes
1answer
1k views
Recover accidentally deleted rows from a table
My client has accidentally deleted all the records of a table from a test database. This test database is in SQL Server 2005 Express and we don't have a backup.
Is it possible to restore the deleted ...
6
votes
6answers
1k views
SQL Server 2005 Huge DB issue
My DB files are growing very fast ( designer is not me and just a weekly report makes 7 GB growth ). And finally, I don't have enough space in the disk. And number of un-reported weeks counting up.
...
6
votes
1answer
437 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) ...
5
votes
1answer
208 views
How can I fix “Cannot perform a differential backup … a current database backup doesn't exist”?
We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day.
The problem is, the differential backups don't always seem to ...
5
votes
3answers
11k views
Combine column from multiple rows into single row
I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something ...
5
votes
2answers
970 views
Data type for storing an array of flags (a bitmap/bit array)
I need to store a bit array for each record of a table, supporting the following operations:
Testing if a bit is set, and setting a bit (using SQL)
Querying and setting the value using ADO 2.8 (not ...
5
votes
2answers
12k 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 ...
5
votes
3answers
5k views
Why “Begin Transaction” before “Insert Query” locks the entire table?
I am using SQL Server 2005 Express.
In a scenario, I added Begin Transaction command just before an INSERT statement in a stored procedure. When I executed this stored procedure, it locked the entire ...
5
votes
5answers
471 views
SQL Server Backup Process Tuning
I am working for a small company that has 20 full time web sites. And another 20-30 micro sites. We are having issues with the sites freezing every day after the SQL Server Agent backup is running. ...
5
votes
6answers
888 views
Which is better: identity columns or generated uinique id values?
I am in middle of a debate about whether it is better to use identity columns as a primary key on a DB table or have a generated value manually created.
I am arguing for the sql managed identity ...
4
votes
3answers
529 views
How can I write a proper query to JOIN all of these tables without duplicates?
Let's say I have four tables:
TABLE: players
COLUMNS: id, first_name, last_name
TABLE: passing_stats
COLUMNS: id, year, passing_yards (several other passing columns)
TABLE: rushing_stats
...
4
votes
1answer
807 views
SQL Profiler - Scripting a trace with HostName filter
I have a stock of scripted trace definitions that I use for different levels of diagnostics, none of which filter by HostName. I needed to filter traffic by host today so:
Fired up Profiler
Created ...
13
votes
5answers
2k views
Why are periodic restarts required to keep my instance performing well?
We've got a production DB server on SQL 2005. Everything runs normally for a while, but after a couple weeks we see a notable performance drop. Only restarting SQL Server brings performance back to ...
7
votes
2answers
222 views
Why does this cursor produce results in the incorrect order?
I am writing some dynamic SQL to identify and, perhaps if I'm feeling crazy enough, automatically convert my NONCLUSTERED indexes into CLUSTERED indexes.
The line ORDER BY 1,2,3 DESC; in the SQL ...
