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

learn more… | top users | synonyms (2)

3
votes
1answer
6k views

Query that displays backup information (successful and failed) SQL Server

I have two jobs that backs up two different databases. Job 1 backs up DB1 Job 2 backs up DB2 DB1 fails to back up due to low space on Drive 1 resulting to a Job 1 failure. To fix that issue, I just ...
3
votes
3answers
355 views

Is there a way to execute sql statement in sql server based on sql server version?

I would like to create computed index on a table if sql server is 2008 or newer and a simple index if sql server is 2005 or older: -- check for sql server version if (select ...
0
votes
0answers
107 views

linked server to a network csv file

I am trying to create linked servers from SQL 2000 and 2005 to a network share csv file. I am using Microsoft Jet 4.0 OLE DB Provider with "text" as the provider string. With regards to security, I ...
0
votes
2answers
878 views

c# Backup sql server database, error Operating system error 5(Access is denied.)

I have been trying to backup an sql server database, and I understand that this error is in regards to drive write protection. I am at the end of my rope with this, as nothing that I have found online ...
0
votes
0answers
171 views

SQL Server Replication do not replicate DELETE statement

We have two servers A and B. Both of them have SQL Server 2005 on them. A is for data collection, just a "lightweight server". B is for archiving, data manipulation etc. From A to B we have ...
2
votes
0answers
103 views

backup history script produces incorrect results

I have a database that is backed up via maintenance plan. The database is scheduled to be backed up at 8:00PM. It usually takes 6 hours to complete. According to this query, SELECT distinct ...
7
votes
1answer
347 views

Necessary to include clustered index columns in non-clustered indexes?

Considering that a non-clustered index is based on the clustered index, would it be necessary for the non-clustered index to list any of the columns contained in the clustered index? In other words ...
4
votes
1answer
2k views

Is ASYNC_NETWORK_IO wait type anything to worry about?

In looking at the list of sprocs that take long time to execute, one stands out as causing the most wait. However, most of that wait (81%) is ASYNC_NETWORK_IO and I know why: the sproc transfers ...
4
votes
1answer
2k views

How to improve Procedure Cache Hit Ratio?

From what I can tell Procedure Cache Hit Ratio below 95% is a problem. On my box, the values hover from 85 to 95%. How do I fix this issue? The server seems to have plenty of RAM so that ...
1
vote
1answer
138 views

How do I find out if Sql server Agent's login has rights to a DB?

How do I find out if the SQL server Agent's login has rights to the target DB, that is the DB on which I will be executing a query ? Also, how do I find the service that's controlling Agent on the ...
5
votes
1answer
122 views

SQL Server 2005 not collecting logins in the log file

I am currently running SQL Server 2005 Standard Edition on a Windows 2003 server machine. I have gone to the properties of the server and confirmed that the Login Auditing is set to both failed and ...
5
votes
2answers
2k views

Issues with TempDB mdf file ever increasing

I have a tempdb growth issue. Let me preface everything by giving my tempdb settings. Even with no queries running on the database/server tempdb keeps on increasing in size, at first rapidly and ...
1
vote
1answer
950 views

'Tempdb' is full warning message in SQL Server 2005

I am using SQL Server 2005. it's a running plant, Everyday large number of data posted into SQL Server 2005 SELECT TOP 5000 [T_idx], [T_TAG], [T_Date] FROM [house].[dbo].[total] ORDER BY T_Date ...
0
votes
1answer
903 views

Help with Transferring Linked Server to another server?

Please advice me as I'm trying to move the linked server (Providers with the tables in Catalogs) from SQL server 2005 to another upgraded sqlserver 2008, I scripted out everything from there, but not ...
0
votes
2answers
49 views

Combine tables only if no results

We have a table FOO that has a huge amount of data in it, most of which is only used infrequently and by (slow) user-interaction. We would like to move all data that is more than 12 months old to an ...
1
vote
1answer
157 views

Tool for identifying stored procs with deprecated syntax?

I have to move a DB running in compatibility mode 80 (SQL Server 2000) on a SQL Server 2005 to a SQL Server 2012. How can I scan it for stored procedures that might break due to deprecated or ...
2
votes
1answer
204 views

SQL Server 2005, non-yielding scheduler Spinloop on Snapshot

I was running dbWarden's procedure for CheckFiles on my server. The job never ended and my logs started to fill up with message at a rate of 1 per minute: 2014-01-07 08:40:04.07 Server Process 58:0:0 ...
13
votes
4answers
10k views

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 ...
3
votes
1answer
52 views

Implement Log Shipping in prod env

We have a 3.5 TB DB which is already involved in log shipping to two different servers, as one will be in restore sate only and one will be in stand by mode which helps for reporting needs. All three ...
0
votes
1answer
141 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
652 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
219 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
858 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
1k 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
2k 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
69 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
599 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
157 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
72 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
597 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
494 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
375 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
101 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
479 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
314 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
304 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
252 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
309 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
569 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
39k 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
43 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
453 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 ...