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

learn more… | top users | synonyms (2)

0
votes
1answer
138 views

Shrink Database Did Not Reclaim Much Space

I have a SQL Server (2005 Developer Edition) database that has grown too large for it's drive. Yesterday evening I dropped a large table that was no longer needed and last night I set up a ...
7
votes
1answer
638 views

TempDB Contention with 24 physical tempdb files on 48 core AMD Opteron box

I just received the following notification about an instance of SQL Server 2005. The instance has a core-to-tempdb-file ratio of 2:1 for a total tempdb file count of 24 files. Contention should not ...
1
vote
1answer
3k views

Schannell: Certificate received from the remote server was issued by an untrusted certificate authority

i'm not sure whether to ask this on ServerFault or here. Periodically following error is logged in Windows Server 2008 Administrative events: The certificate received from the remote server was ...
2
votes
1answer
216 views

Is it normal for tempdb TPS to be higher than all other databases combined?

I'm investigating some performance issues on a production DB here, and I've noticed a slightly odd thing that I'm not sure is weird or not. If I run a quick Performance Monitor trace for the ...
3
votes
1answer
1k views

PAGELOCK Conversion Deadlock From Update to Intent Exclusive

A customer of ours is experiencing frequent deadlocks. The deadlocks are mostly on the same UPDATE statement. The deadlocks follow a pattern in which both SPIDs have acquired an update (U) lock on a ...
3
votes
0answers
45 views

Any checklist/guide when transferring a database to a new server? [duplicate]

I'm looking for steps that I need to follow to transfer a database to a new server. So far I have: I restored the current database that I have in SQL Server 2005 instance to another SQL Server ...
37
votes
3answers
799 views

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 ...
7
votes
7answers
956 views

Main Considerations When Moving From MS Access Programming to SQL Server

I am a 100% self taught MS Access programmer (main part of my job is programming), I am now building larger databases; still using MS Access as the UI but SQL Server to store all data and do more of ...
2
votes
3answers
1k views

Attempting to restore SQL Server 2005 database to a SQL Server 2008 R2 instance gives reason: 15105 error

I have a SQL Server 2005 database I am attempting to restore a SQL Server 2008 R2 SP2 machine using this statement: RESTORE DATABASE [Db] FROM DISK = N'C:\temp\sql\DB.bak' WITH FILE = 1, MOVE ...
0
votes
0answers
67 views

SQL Server Reporting Services Upgrade/Migration

I couldn't find any documentation on this. I'm currently doing a dry run of SSRS 2005 to SSRS 2008 R2 upgrade/migration. I've backed everything up (encryption key and configuration files) and did a ...
8
votes
1answer
542 views

How to move a database from SQL Server 2012 to SQL Server 2005

What are my options if I need to move a database from SQL Server 2012(32bit) to SQL Server 2005(64bit)? I know I cannot: restore a backup of the database on SQL Server 2005 detach & attach I ...
6
votes
2answers
1k views

TempDB Version Store cleanup

From what I can find the version store will only clean up versions that are older than the oldest active transaction. Question: Is the oldest transaction database specific or will SQL Server keep all ...
1
vote
2answers
155 views

How can I run this SQL query across all of my databases at once?

I have a query to let me know basic info on backups. I have several SQL servers that I'd like to be able to query at once. Is it possible to run the following query across several servers at one time? ...
1
vote
1answer
70 views

How to disable data type check while pushing data to Linked Server

I have two SQL serves one is SQL server 2005 and other SQL server 2008.I push data from SQL server 2005 to SQL server 2008 using Linked Server.I want to improve performance of data transfer. Few days ...
0
votes
0answers
570 views

How to determine if SQL Server Reporting Services database is being used?

I have a SQL 2005 server that needs to be decommissioned. For regular databases, I have a couple scripts that look at sys.dm_db_index_usage_stats to determine last access. My normal process is to make ...
1
vote
1answer
465 views

Why isn't SQL Server using the Max Server Memory setting?

I have a SQL Server machine with the following: Windows 2003 Enterprise SP2 (32-Bit) SQL Server 2005 (32-Bit) 12GB RAM /PAE switch set in boot.ini 6 SQL instances, all with AWE enabled No other ...
4
votes
2answers
359 views

Audit user queries on SQL Server 2005

I'm in an unpleasant situation where I have a requirement to audit any queries executed by users via SSMS, SQLCMD, etc. on a SQL Server 2005 database. The situation is made worse by the inability to ...
1
vote
1answer
99 views

SQL Server Login attempts fail when using SSMS but succeed when using SQLCMD

I have my SQLServer 2005 instance configured for mixed mode fine. When using SSMS, none of the sql server logins seem to work. Just "Login failed for user '..'" message. But when I use SQLCMD, it ...
2
votes
1answer
443 views

Performance issue with TOP (1) on a nullable column

I need help to identify the reason for a performance issue. Actually following query sits in a scalar-valued-function but that is not the reason for the issue since it needs the same time as query: ...
2
votes
2answers
296 views

Is it possable to do a ALL SERVER DML Trigger?

My department works on converting a client's database when a client switches from another company to us. We have a RDP session they can connect to and see our software with their data. Once they ...
0
votes
0answers
60 views

SQL Server 2005 Create a SQL Server failover cluster check button is not enabled

I try to install SQL Server failover cluster under windows 2003 or windows 2008. In both installing time I found the checkbox enable error. I don't know any changes want to do to windows server before ...
2
votes
1answer
286 views

Double queue reader or is it still busy?

I came across this line in the verbose log on replication QueueReader Agent job startup. The Queue Reader Agent has encountered the error 'Another queuereader agent for the subscription or ...
6
votes
1answer
249 views

Failed copy job deletes all users

Since the progression of this was apparently somewhat difficult to follow the first time around: I attempted a completely boring, been-done-a-thousand-times-before copy of a database using the copy ...
3
votes
1answer
1k views

Cannot rename the table because it is published for replication

I need to rename some tables in my DB - SQL Server 2005, with this: EXEC sp_rename 'dbo.TableName', 'Tmp_TableName', 'OBJECT' but I can't do this - I get this exception - Cannot rename the ...
0
votes
1answer
39 views

Joining 2 tables- in Select statement show how many fields matched directly (A1 = B1), vs matched on isnull(A1,B1) = B1

I have 2 tables which I need to join, but sometimes there are 1) direct joins e.g. t1.FieldA = t2.FieldA OR 2) when t2.FieldA is null then it must join as well then t2.FieldA is null t1.FieldA = ...
2
votes
1answer
292 views

SQL Server Linked Server error and Query Problem

I am having two servers running SQL2000 and SQL2005. There is a database A,B in SQL2000. Similarly i have the same in A,B in SQL2005. A,B are name of the database. I have a linked server pointing ...
2
votes
2answers
563 views

SQL Server Replication Agent Profile -Output Parameter

I'm trying to add the -Output parameter to my log reader agent for transactional replication and getting this error: Msg 21805, Level 16, State 1, Procedure sp_MSvalidate_agent_parameter, Line 92 The ...
8
votes
3answers
37k views

SELECT DISTINCT on one column, while returning the other columns?

I have a query which uses three lookup tables to get all the information I need. I need to have DISTINCT values for one column, however I also need the rest of the data associated with it. My SQL ...
0
votes
1answer
42 views

Linked SQL Servers

I'm trying to link 2 SQL Servers (2008 and 2005). I have tried it via the GUI and scripts, and having no luck. I have problems trying to to query through it with access. As I'm not the Admin of the ...
0
votes
0answers
427 views

SQL Server: Batch Requests/sec showed increase in performance, but performance actually decreased

We are currently on SQL Server 2005, with a plan to move to SQL 2012. To make this move we have to test new database drivers for the application (Java). We performed 2 load tests using the 2 different ...
-2
votes
2answers
135 views

SQL Server Transaction logs [closed]

Could someone tell me the role of transaction logs in SQL Server ? I have a transaction log file, after making a lot of transactions for testing purpose I take the backup of it via command. Sometimes ...
1
vote
1answer
144 views

User switching accounts, can't log into MS SQL Server anymore [closed]

First a bit of background, for good or bad, we don't keep one user account attached to one person. The user accounts are matched to their position. For instance the CEO would have a userid of CEO. ...
3
votes
1answer
90 views

Advice on setting up a Reporting server

I'd like to ask advice on setting up a reporting server please. We have a SQL Server (on a VM) that handles operations. (SQLOps) This primary SQL server instance has a secondary Log Shipping ...
0
votes
2answers
136 views

SQL Server 2005 Replication Stops after a minute without an error

Background: I have a SQL Server 2005 setup with master, slave1, slave2 replication set up as a pull replication from slaves. The distribution database resides on the slave1 machine, both slaves pull. ...
5
votes
1answer
4k views

SQL Server has encountered 1 occurrence(s) of cachestore flush

On one of our production server log files, the following message is being observed on daily basis. What does it mean? Is it a serious issue? SQL Server has encountered 1 occurrence(s) of ...
3
votes
1answer
103 views

DBCC show_statistics: density doesn't match what I calculate

I've read in several articles that the density of a column is (1.0 / count(distinct columnname)). I have a column with roughly 2.6 million distinct values over 2.7 million rows, but the column ...
1
vote
3answers
381 views

How to determine if SQL Server will not execute parts of the script in parallel?

Are there any general rules or maybe easy way to tell if SQL script will execute in batches instead of "all at once"? USE UserDB; GO DBCC SHRINKFILE (DataFile1, 5); GO DBCC SHRINKFILE (DataFile2, ...
2
votes
2answers
167 views

Delete corrupt empty filegroup

We have a large database (1.8TB) with eight filegroups created on SQL 2000 and now running on SQL Server 2005 9.0.5057 since early 2012. Simple recovery. On September 27, we got a torn page error in ...
1
vote
2answers
5k views

SQL Server 2005 Express on Windows 8

I need to install SQL Server 2005 Express on Windows 8 Home Edition x64. But I get a compatibility error! I have tried SP1, SP2, SP3 of SQL Server 2005. Visual Studio 2008 is working fine. Any ...
6
votes
4answers
45k 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 ...
3
votes
2answers
140 views

Query has where (a = a) or (b = b) - the 2nd part causing index scan. Full example included

I've been trying to figure out why this query (on integers) is using an index scan when both values (the original table, and the table its looking up in) are integers. The first part is used to fill ...
1
vote
1answer
1k views

How to insert in table from remote stored procedure without creating a distributed transaction?

How can I insert in a local table from a remote stored procedure without creating a distributed transaction? I want to run a stored procedure on a remote server and use the output as the source for an ...
3
votes
1answer
438 views

Selecting the highest value in a column?

I am dealing with a table that has 3 columns and I need a way to select the record(s) that matches a certain PrntP_PstnGrpID(s) and is the highest ChldLevel ( The PrntP_PstnGrpID could have multiple ...
4
votes
1answer
367 views

After 5 years of working, SQL Server 2k5 Table now locking without any changes made to DB

I have a table: OrderDetails. For 5 years, only the odd Deadlock has occured. If it happened, it happened on indexes which was on OrderDetails table Now all of a sudden, deadlocks are everywhere - ...
0
votes
1answer
320 views

dbcc checktable/checkdatabase errors after database restore

Following on from this question Error when creating an index in SQL Server 2012 we have been having problems restoring our live database back to test. Our current production database is SQL 2005 ...
10
votes
4answers
3k views

Table partitioning for archiving data

Scenario: two databases: DB_A and DB_Archive with one very big table called tableA. every day, records older than 60 days are deleted from DB_A and moved to DB_Archive mainly to leave thing ...
1
vote
0answers
47 views

Integration Service cannot be upgraded/removed

I have Sql Server 2005 Integration Services installed in our server whose version is 5292. It needs to be upgraded to version 5324. After running the patch the summary report says it is upgraded ...
1
vote
1answer
416 views

Retrieving data speed tweaks SQL Server 2005

I have a database in Microsoft SQL Server 2005. I have table with 3 columns, namely HASHKEY (BIGINT), NOTE_ID (INT) and TIME_OFFSET (INT). Columns HASHKEY has 19 digit BIGINT values, which can ...
1
vote
0answers
165 views

Way to make differential backup from full different but from same database

I have the following situation: Our sql server is running in a cloud service. The database to be backed up have 60gb. We need to make a local copy of the database for some tests, and it need to be up ...
0
votes
1answer
116 views

Need to create combinations of Parents/Children/Dogs, but can't get past 2nd level of CTE

I've hit a brick wall and please ask help. I'm building a table up for (in this example) a ticketing system where e.g. Ticket 1 will allow Parent: Either parent Peter or Sally, Child: to come without ...