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

learn more… | top users | synonyms (2)

2
votes
3answers
730 views

Restoring SQL Server databases - tips and tricks?

Anyone have any tips and tricks when it comes to restoring SQL Server databases? I recently restored a .bak from one server/instance to another, using SQL Server 2005 on both, and have run into the ...
9
votes
2answers
16k views

SQL Server backup failing. Error: 3041, Severity: 16, State: 1

First of all, I'm very new at stackexchange, so please bear with me. I'm running a SQL Server 9.0.4060. My problem is: my SQL Server backup keeps failing on a lot of the databases. The Maintenance ...
2
votes
4answers
5k views

How to find all positions of a string within another string

How can I find all the positions with patindex in a table or variable? declare @name nvarchar(max) set @name ='ali reza dar yek shabe barani ba yek ' + 'dokhtare khoshkel be disco raft va ali ...
2
votes
0answers
24 views

Join duplicates on T-sql

Background: This is largely a learning exercise. I am working on a task scheduling program. Part of the program requires making a report to print out. I am working with the original version of the ...
1
vote
1answer
44 views

microsoft sql server schema [closed]

From blog.sqlauthority.com: Protecting ISV database access: in custom ISV (Independent Software Vendor) applications, the database schemas are usually complex, and in the case of applications such ...
0
votes
2answers
285 views

Bi-directional synchronization between local SQL Server 2005 and a SQL Azure using SQL Data Sync

I need to synchronize an Sql Server 2005 database in an SQL Azure database. In other posts I've read that I can do this using Sql Data Sync but I don't know if SQL Data Sync can make the ...
26
votes
5answers
19k 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 ...
0
votes
1answer
123 views

Risk in evicting node from SQL Server cluster without prior uninstalling of SQL Server binaries

I have to perform upgrade of a SQL Server failover cluster with several SQL Server 2005 instances running on two Windows Server 2003. Since the plan is to have servers running Windows Server 2008 and ...
0
votes
0answers
16 views

Converting Legacy SQL Server 2005 Stored Procedure to Transaction based for parallelism [closed]

My task is to convert a legacy stored procedure written in SQL Server 2005 so that it can be used in parallel (multiple executions from different users). As of now the stored procedure is designed ...
17
votes
7answers
35k views

SQL Server Cannot drop database <dbname> 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. ...
4
votes
1answer
466 views

SQL Server 2005 Replication

I am in the process of creating Replication between 2 Remote Servers, server 1 is the Distributor and Publisher and server 2 is the Subscription. server 1 windows 2003 server 192.168.10.1 connected ...
2
votes
1answer
80 views

Tracking down unused Views and Stored Procedures

I am working on a SQL Server 2005 database that has two Microsoft Access ADP frontends. This database needs a lot of love and attention which I am attempting to give it. There are lots of stored ...
8
votes
2answers
3k views

Turning off auto shrink on all SQL Server databases. Why doesn't this work?

I figured I could use sp_MSforeachdb to solve this problem, but I get an error message. sp_MSforeachdb ' BEGIN USE [?] DECLARE @dbid INT SET @dbid = DB_ID() IF(@dbid > 4) BEGIN --PRINT ''[?]'' ...
7
votes
2answers
4k views

Database size - MDF too large?

I'm maintaining a SQL Server 2005 database which hosts approximately 2.9Tb of data (2 x 1.45Tb - I have a RAW schema and an ANALYSIS schema so basically two copies of the data ingested). The recovery ...
0
votes
0answers
18 views

SYNTAX ERROR: delete from multiple tables with inner join

Sorry, but I need syntax help :( This needs to run on SQL Server 2005 + DELETE SO, ASO, ASOV, AOV FROM SYS_OPTION SO INNER JOIN ADM_SYS_OPTION ASO on SO.OPTION_ID = ASO.OPTION_ID INNER JOIN ...
-2
votes
1answer
3k views

This version of Microsoft SQL Server Management Studio Express can only be used to connect to SQL Server 2000 and SQL Server 2005 servers

I have already SQL Server Management Studio 2008R2 on my local machine. I just installed SQL Server Management Studio 2005 and wanted to connect for the first time. After going through the following ...
1
vote
1answer
19 views

Import .bak into SQL Server 2008 Management Studio

I have a custom program that uses a database. I have little knowledge of servers but the way it works is by using a "local" server in the same computer as the one where the program is installed. I am ...
2
votes
4answers
9k views

Error Authenticating Proxy Account when Executing SSIS job

I have a SQL Server instance that runs 5 scheduled tasks each night, each of which run SSIS packages. These packages have been running for years and the associated steps run via a Proxy Account ...
1
vote
2answers
1k views

SQL Server starts normally from Windows Services screen, but shows error through SQL Server Configuration Manager. Why?

I have a SQL Server 2005 instance that has worked fine for years. There have been no changes that I am aware of. Recently, I found that if I stop and then start the instance in SQL Server ...
-3
votes
0answers
22 views

IN RECOVERY MODE

I used sql server 2005.One of the databases is in recovery mode. This database size is 37GB. I changed to simple recovery and then I can use it that. When I do restart, this database auto change to in ...
1
vote
2answers
44 views

Understanding the usage of version store in tempdb

We are trying to analyze the growth of tempdb on one of our SQL server 2005 Ent SP4. I know tempdb would need the space depending upon the transactions, and how queries are designed to use it. We ...
-3
votes
1answer
47 views

SQL Server questions (for masters) [closed]

How to rename sa to new_sa in SQL Server 2000? How to do and what is the impact SA rename impact in SQL Server 2005, 2008, 2008R2, 2012, 2014, Express Edition How to recreate sa login if deleted How ...
2
votes
1answer
168 views

High I/O for part of query

I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF): In the above part of ...
1
vote
1answer
66 views

Cannot create a row of size XXXX which is greater than the allowable maximum row size of 8060

I have a scenario where some inserts/updates are failing due to maximum row size of 8060 limitation. I have a table with lots of varchar 250 to 500 columns, numerics, datetime columns. I was trying to ...
4
votes
1answer
54 views

What started this SQL Server job?

I'm looking at a SQL Server environment in which jobs start in several different ways. One way is from a job running on a different SQL Server. So, for example, "SQL Server A" runs a job named ...
1
vote
1answer
31 views

What transaction logs are for

I'm confused about tranasaction logs for SQL Server. I was under the impression they can be used to roll the database forward. For example say I made a software release on Sunday, I added three new ...
3
votes
2answers
172 views

Adding around 200 rows in a table grows size of the table by 400kb where avg row size is 0.2KB

We are running into an issue where if we look at the avg row size (0.2kb) x number of rows (200) inserted in one of our table, the size should grow by around 40 KB. However the total used space has ...
1
vote
1answer
189 views

Replication monitoring refresher for distribution job affecting performance

I have a performance problem due to a sql agent job executing dbo.sp_replmonitorrefreshjob. The environment is a 2 node Cluster with SQL Server 2005 SP2 and with transaction replication. The OS ...
1
vote
1answer
1k views

SQL Server 2005: Integration Services Stored Packages is empty. Why?

I am trying to import, export SSIS packages. I know they are there. When I query msdb.dbo.sysdtspackages90 I get 22 packages. But when I connect to Integration Services on the server, the stored ...
3
votes
2answers
271 views

Defrag a HEAP by creating clustered index and immediately dropping it

I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
7
votes
1answer
1k views

Non-yielding IOCP Listener

Does anyone know what `Non-yeilding IOCP Listener' indicates? On of our SQL Servers just had a bugcheck dump: ===================================================================== ...
2
votes
0answers
72 views

No significant performance increase after migrating from SQL Server 2005 32bit 4 Cores 4 GB RAM to SQL Server 2012 64bit 4 Cores 32 GB RAM [closed]

We run regularly a series of data analysis scripts on one of our databases. The MDF File of the database is several gigabytes big, the scripts perform not only selects, but also inserts, updates and ...
23
votes
4answers
58k views

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 ...
3
votes
2answers
4k views

Can I copy the table structure of a #temp table to a new physical table?

I have a #tempTable that was created using SELECT * INTO #tempTable FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;Database=MyFileName.xls', 'SELECT * FROM [Sheet1$]') Is there an easy way ...
2
votes
1answer
97 views

Get employee names on the basis of Date

I want a dynamic query which will fetch the employee name who has completed successfully 6 months and are ready for confirmation. I have written a hardcode query does that. Here is the query SELECT ...
1
vote
2answers
37 views

Role to view SQL server job log

We have a monitoring tool that can check if Microsoft SQL Server jobs have run successfully. What I want to do is create a user that has as little permission as possible but it must be able to watch ...
0
votes
2answers
406 views

SQL Server 2005 to 2012 Replication, cannot access replicated data

I have created publication (snapshot, small table with 10 rows) on MS SQL Server 2005. Then, I created push subscription on MS SQL Server 2012. When I try to select data form table, process takes ...
5
votes
0answers
47 views

Error in partition switching in Sql Server 2014 when migrating a database from Sql Server 2005

I am migrating an old database from SQL Server 2005 to SQL Server 2014. This database has a partitioned table; a scheduled job periodically moves old data from the "online" table to the the historical ...
2
votes
4answers
1k views

SQl Server Password Change after installation

I have installed SQL Server 2005, but at the time of installation I forgot to change my password. Now I can access my database only in Windows authentication mode, and I want to access the database in ...
2
votes
1answer
400 views

SQL Server Linked Server error and Query Problem

I have two servers running SQL Server 2000 and SQL Server 2005. There is a database A,B in SQL Server 2000. Similarly I have the same in A,B in SQL Server 2005. A,B are name of the database. I have a ...
1
vote
2answers
2k views

Unable to connect SQL Server 2008 from SQL Server 2005 DB

I have SQL Server 2005 on my work station and on my Server, we have SQL Server 2008. When I tried to connect to SQL Server 2008 from my SQL Server 2005 edition, I am receiving the following error: ...
1
vote
1answer
72 views

Scheduling a backup in SQL Server 2005

I am trying to schedule back up in SQL Server 2005 with management option. I am unable to find database name while scheduling a backup with full option. Database name is not over there in selected ...
4
votes
2answers
125 views

SQL Server update and database transfer

In my test network there is a database created with SQL Server 2005. I have to update SQL Server 2005 to SQL Server 2008. The data base should be used with SQL Server 2008. So it is to be moved. What ...
0
votes
1answer
328 views

How do you reset the master key in sql server 2005?

Our old DB admin is gone, any ideas? It looks it was set up many years ago.
1
vote
0answers
97 views

MDF/TempDB Growth, SQL Server 2005 Enterprise

Here's my scenario: SQL Server 2005 Enterprise Records from Database A being added to Database B Size of Database A MDF file: 3.15 GB I have separate drives for Data, Logs, and System. I ran a ...
3
votes
2answers
8k views

command or query to ping SQL server

I have 2 windows 7 machines on a very high security domain one acting as a server, the other as a client. Each box has a SQL Server Express 2005 database and application "A", however I'm only using ...
1
vote
0answers
57 views

Will installing WebMatrix (and Consequently SQL Server CE) Break My Current Installation of SQL Server 2005?

We have a server that has (and needs to keep) SQL Server 2005. We are hosting intranet sites on one server (server 1), but have a database on another server (server 2). One of these sites uses AJAX ...
19
votes
2answers
201 views

Optimization issue with user defined function

I have a problem understanding why SQL server decides to call user defined function for every value in the table even though only one row should be fetched. The actual SQL is a lot more complex, but I ...
1
vote
3answers
276 views

Check of SQL Server upgrade success

I have performed SQL Server upgrade from 2005 to SQL Server 2008. Is there any tools/possibilities to check, whether the upgrade from SQL Server 2005 to SQL Server 2008 has been accomplished ...
7
votes
3answers
730 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, ...