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

learn more… | top users | synonyms (2)

0
votes
2answers
347 views

Remote database connection problem?

I have two systems with Windows XP. I have installed SQL Server 2005 on a system and tried to use SQL Server Management Studio Express on both to use the database. I am able to use the database on ...
2
votes
1answer
995 views

what is difference between last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats?

what is meaning of last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats and what is differnce between them? when I fire below query SELECT TOP 20 qs.last_worker_time, ...
1
vote
2answers
365 views

Size of TempDB Microsoft SQL Server 2005

Are there drawbacks to having a large tempdb in a SQL Server database? Assuming the disk is not full. I am creating large temporary tables in a couple of queries. Once I am finished running queries, ...
2
votes
2answers
602 views

Get disk space percentage free with SQL Server 2005?

I am currently using SQL Server 2005 and (undocumented I believe) master..xp_fixeddrives to get free space on my database volumes as part of my monitoring. However, this only gives me an absolute ...
2
votes
2answers
929 views

Securely Send Database Mail via an Insert Trigger

I have spent about 30 hours so far trying to get sp_send_dbmail to work. I finally got SOMETHING to work, but I have serious doubts as to its security. I would like know a method which is better yet ...
6
votes
3answers
7k views

How can I backup a large SQL Server database without impacting performance?

We've had reports of queries running slowly or timing out early in the morning, and the only job I see running that I think could affect this is our database backup job. The database itself is about ...
0
votes
1answer
223 views

Get percentage free space on database volumes

I am currently using SQL Server 2005 and (undocumented I believe) master..xp_fixeddrives to get free space on my database volumes as part of my monitoring. However, this only gives me an absolute ...
4
votes
1answer
353 views

Why does WAITFOR (RECEIVE FROM QUEUE) show up in the monitor incorrectly?

I kick off the following command in my app: WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue) When I run `sp_who2 active', I get Status=SUSPENDED and Command=DELETE Note: ...
13
votes
2answers
312 views

Does firing off an indefinite WAITFOR increase the log file size?

In the last release of my app, I added a command that tells it to wait when something arrives in the Service Broker queue: WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue) The ...
4
votes
1answer
1k views

How to restore replication after server crash

We had two databases on one SQL Server 2005 instance with transactional replication between them (3 tables as articles). This server acted both as publisher and distributor. Then the RAID on the ...
5
votes
2answers
3k views

ETL: extracting from 200 tables - SSIS data flow or custom T-SQL?

Based on my analysis, a complete dimensional model for our data warehouse will require extraction from over 200 source tables. Some of these tables will be extracted as part of an incremental load and ...
3
votes
2answers
173 views

Select blocking issue [closed]

The database was not responding to simple select statements like select * from table I noticed in sys.dm_os_waiting_tasks that one session is blocking, blocking session was pointing to a stored ...
1
vote
0answers
148 views

SQL server Load Balancing

SQL server user access cut down from 8pm - 9am. During this period our SSIS scripts would run to load data(from Oracle/mainframe/Access) to sql, large database(s) & too much data - mortgage ...
1
vote
1answer
456 views

Defrag or Backup+Restore

I have an MDF file that was autogrown from 1MB to 40GB in 10% increment. The file is heavily fragmented now, and I want to defrag it with minimum downtime. I have a plan that I am not sure will work: ...
8
votes
1answer
991 views

SQL Server 2000 to 2008 to 2012

We are migrating from SQL Server 2000 to 2008 now. We are making lot of changes to code, scripts because there has been a lot of change since 2000 to 2008. While making these changes, What I want to ...
2
votes
1answer
126 views

Create MS SQL login for support engineer to access only vendor database

I have sql server 2005, I need to create separate database login to support engineer of my software vendor to access their own database. My only worry is deny him to take backup from our database ...
2
votes
1answer
1k views

Solve conflict between affinity mask and I/O affinity mask?

I executed this on one of our SQL Server machines: EXEC sp_configure 'show advanced options', 1 And then executed RECONFIGURE. This presented me with the following error message: The affinity ...
8
votes
2answers
2k views

Risks of changing to ARITHABORT ON

I'm working with a vendor with the arrangement that they provide the core application, and I can build my own extensions as long as I don't modify the core application. It's built in ColdFusion ...
4
votes
1answer
24k views

ODBC Data Source SQL Server Connection - Login Failed For User

I have moved my database from an SQL 2005 to a server with SQL 2008. I am now trying to create an ODBC Data Source. I am using "With SQL Server authentication using a login ID and password ...
4
votes
3answers
1k views

Logging query text without Profiler

I'm in the process of porting an old, semi-broken ASP.NET/SQL Server application to PHP/PostgreSQL. Among the torments^H^H^H^H^H^H^H^Hchallenges I'm faced with is that the reporting is done by a ...
1
vote
1answer
551 views

How can I stop SQL from validating an OPENROWSET query when its not going to be executed?

I have a problem where a stored procedure is throwing an error because it's trying to run/validate an OPENROWSET query inside it that it doesn't actually have to execute. The stored procedure that ...
2
votes
2answers
312 views

Execution plan flips Filter and Execute Scalar when using the PK, causes cast to fail

I have some data tables that where imported in from a flat file source so everything is varchar(max) except FileRecordID which is a int that hold the row number the data came from in the flat file ...
1
vote
3answers
191 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 ...
4
votes
2answers
122 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 ...
5
votes
3answers
3k views

How can I inspect the security settings of a SQL Server Endpoint?

I am in the process of creating a TEST environment for our SQL Server development staff. In production we have 3 SQL Servers, SQL01 contains several databases that are mirrored to SQL02. SQL03 ...
2
votes
2answers
444 views

SQL Server 2005-2008 Compatibility Check

A few databases were recently migrated from SQL Server 2005 to SQL Server 2008, and I have to check if all the stored procedures, and triggers still work well. With 'checking', I mean that I should ...
0
votes
1answer
68 views

Changing column value [closed]

Normally used to MySQL so need help on syntax check. with SELECT * FROM Bit WHERE CustomerID='3512242' I have a field called Filer. I want to change it from a P to a D. Do I use UPDATE to change the ...
4
votes
2answers
170 views

Display all tables and their columns

Is there a way to display the layouts for all the tables to see their columns? I am trying to find tables with a certain column names. I am using the web app Web Data Administrator Example: table1 ...
4
votes
1answer
506 views

SQL Server Row Count sp_spaceused rows discrepancy

I am researching a performance problem with an application running on SQL Server 2005. I've conducted performance tuning, and I realize that a key table is poorly structured(poorly chosen clustered ...
11
votes
1answer
4k views

Can I query a tab-delimited file from SSMS?

Is it possible to query a tab-delimited file from Sql Server Management Studio to view its data without saving it anywhere? I know you can BULK INSERT from a tab-delimited file using something like: ...
3
votes
2answers
4k 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 ...
2
votes
1answer
203 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 ...
4
votes
1answer
234 views

T-SQL and GPS co-ordinates

I'm looking to store GPS co-ordinates in an MS SQL Server DB for location matching of users with GPS-enabled mobile phones. I've read about STDistance on MSDN, but it only appears to exist in SQL ...
2
votes
2answers
216 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 ...
1
vote
3answers
207 views

Inside a trigger create database is not working

I tried using trigger concept. If any new row inserts in the user table my trigger will fire and try to create a dynamic database for that new user. But Inside a trigger create database is not ...
2
votes
1answer
192 views

clustered and covering index ignored on delete statement. Table scan occurs

Why would SQL Server 2005 find it more efficient to perform a table scan instead of using the available clustered index on the primary key (and only the primary key)? DISCLAIMER: There is also a ...
4
votes
2answers
340 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 ...
5
votes
3answers
1k views

Query performance degrades with time and use

I have been fighting a problem for several weeks now where the performance of my SQL Server queries degrade over a few days of use. In addition every few days, a query will simply not return from my ...
3
votes
1answer
269 views

Restrict record to be update or deleted according to dates inside row for SQL Server 2005

I have transaction replication between two SQL Servers. For testing purposes I deleted a lot of records at the subscriber that still existed at the publisher. I now know that if someone updates, ...
4
votes
2answers
603 views

Integrated Security Success on Master, Fails on Other Database

I'm a bit perplexed as to why my connection string is selectively succeeding on the master database in SQL Server 2005. Here are the details of my app. Any ideas where to look? I'm clearly missing ...
2
votes
2answers
691 views

SQL Server imports data in wrong charset with SSIS

I have a flat file which I am importing into a SQL Server table using a SSIS package. The code page is set to 65001. However, when I run a SELECT * FROM tableName the data appears mangled. For ...
6
votes
1answer
6k views

Why do we need to rebuild and reorganize Indexes in SQL Server

After searching the internet i couldn't find the reason for Why do we need to rebuild and reorganize indexes in SQL Server ? what does internally happens when we rebuild and reorganize ? An ...
6
votes
1answer
617 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 ...
0
votes
1answer
147 views

SQL Server Mirror failover on failing storage

Recently we had an issue with a sql server 2008 r2 HA mirror. I have tried to reproduce this in a lab environment and i came to the conclusion that i am missing something OR something is not possible. ...
7
votes
4answers
19k views

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 ...
1
vote
1answer
705 views

Audit 'sa' login in SQL Server 2005

I have a requirement where I need to audit (and potentially be notified) whenever the 'sa' user account logs into an SQL Server 2005 instance. Additionally, there is a preference that the database ...
2
votes
1answer
495 views

emergency-mode repair failed, nothing to do?

A production server got I/O disk error's I tried to fix it with dbcc but it got unexpected errors and crashed after a few minutes. so I took the file down and copied it over to a new machine. I have ...
7
votes
2answers
1k views

Finding delete or update records

I have had three incidents in recent months where records in a table that have either been deleted or values updated to zero across a whole table. We have a team of four people who have the ...
6
votes
2answers
10k 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
2answers
581 views

Executing query is too slow when accessing SQL Server located in an other country

I'am doing an investigation about a slow running query (see below info) when accessing our production SQL Server located in an other country. Now I'm looking for tweaks on how to solve this and make ...