SQL Server 2005 (major build version 9.00.xxxx). Please also tag sql-server.

learn more… | top users | synonyms (2)

2
votes
1answer
177 views

SQL Server 2012 - Dropping Snapshots takes hours

We recently did a SQL Server upgrade from 2005 to 2012. We have a nightly job that drops a snapshot of an active database. The snapshots gets created again later in the day. Before upgrading, in SQL ...
0
votes
0answers
10 views

sp_procoption seems to be failing on SQL 2005 instance

We have a SQL script that creates a Stored Procedure which is responsible for creating a SQL trace. On SQL Server 2008, the script runs fine, the SP gets created and the SP is configured to ...
14
votes
2answers
29k views

Rebuilding the transaction log

We have a very large database (~6TB), whose transaction log file was deleted (while SQL Server was shut down. We have tried: Detaching and reattaching the database; and Undeleting the transaction ...
4
votes
1answer
130 views

Actual Number of rows is too high even with new tables

I have a query based on two newly created tables in SQL Server 2005. I have index created on these tables. But when I write a query with JOIN condition, the number of actual rows returned is too high ...
4
votes
4answers
7k views

How can I search the full text of a stored procedure for a value?

I use the following script to search the text of all stored procedures when I want to find specific values. SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION ...
9
votes
7answers
2k views

Which is better: identity columns or generated unique 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 ...
0
votes
1answer
48 views

Restore databases from prod to test server with data over X days

We've got a list from app owners to restore say 20 databases from a total of 100 available production server databases onto a test server on weekly basis. Moreover, they require it in a way that ...
12
votes
3answers
15k views

Performance difference between Clustered and Non Clustered Index

I was reading Clustered and Non Clustered Indexes. Clustered Index - It contains Data Pages. That means the complete row information will be present in the Clustered Index Column. Non Clustered ...
0
votes
1answer
173 views

SSRS ReportServer_XXX.log is giving incorrect CPU count

I am currently investigating an issue wherein sometimes the SSRS just suddenly stops accepting requests until we do an IISRESET command. This happens after we added an additional processor on the ...
2
votes
3answers
67 views

TSQL for “Ignore databases were the state is not online”

I have a few custom jobs on a mirrored SQL Server 2005 and they fail from time to time because of automatic failover. Is there some T-SQL code that I can write into my automated jobs or do I just ...
10
votes
1answer
17k views

Why does a query run slower in a Stored Procedure than in the Query window?

I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure? Here's what my query looks ...
21
votes
6answers
14k 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
2answers
1k views

If I fail over one database, do the others that share the same mirror endpoint fail over as well?

We have two databases setup for mirroring on a single SQL Server instance: a test database and a production database. Both get mirrored to another server using the exact same endpoints. If I go into ...
5
votes
1answer
530 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
2answers
62 views

Deleting multiple rows based on the first instance of the value

I have a table with the below structure. I would like to delete all of the records that records that match ColA for any of the ColB's that have a ColC that has an even-numbered value. But only for the ...
1
vote
0answers
16 views

Server error in reports application

I have a server with SQL Server 2005 installed with the latest service packs. SQL Server 2012 express was been installed on same machine and seems to have broken the Reporting services service. When I ...
2
votes
1answer
215 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 ...
15
votes
7answers
53k views

How can I get the correct offset between UTC and local times for a date that is before or after DST?

I currently use the following to get a local datetime from a UTC datetime: SET @offset = DateDiff(minute, GetUTCDate(), GetDate()) SET @localDateTime = DateAdd(minute, @offset, @utcDateTime) My ...
2
votes
0answers
275 views

MS-SQL index not using all the available columns as seek predicates [duplicate]

I have a SQL Server query on a large table (>17 million rows) that sometimes does not populate all the predicates before performing an index seek, which results in not filtering out enough rows and ...
3
votes
0answers
249 views

Merge replication sync fails after ALTER TABLE… NOT NULL

We have a SQL Server 2005 database that uses merge replication to sync to SQL Compact Edition subscriptions. We added a column to a table using: ALTER TABLE Shifts ADD COLUMN StateOrProvince NOT NULL ...
6
votes
2answers
3k views

How do I shrink the physical Transaction Log file when it's the principal in a mirror?

We setup database mirroring over the weekend, and forgot to re-enable the job that backs up the transaction logs. When I came in this morning, the transaction log had ballooned to 58GB, and was taking ...
8
votes
2answers
232 views

Moving Databases to New Datacenters

My company is moving our infrastructure to new data center and I am trying to figure out the best way to keep the database on the new server in sync with the current production database until the new ...
4
votes
2answers
220 views

BACKUP LOG cannot be performed because there is no current database backup

I have a SQL Server 2005 database using the full recovery model. If I execute the following commands: ALTER DATABASE test SET RECOVERY SIMPLE; ALTER DATABASE test SET RECOVERY FULL; or BACKUP LOG ...
-1
votes
1answer
20 views

Jobs scheduling to fetch data from multiple servers using SQL Server Agent [closed]

I have 2 servers - server-1 and server-2, both have an identical database. I have scheduled a job on server-1 thru SQL Server Agent (both servers have SQL Server 2005) which will open table1 on ...
1
vote
1answer
23 views

How to convert different string value to respective 'YYYY' int format?

I'm inserting values into table from xml as: DECLARE @XMLRESULT XML DECLARE @tbl_XMLResult TABLE ( RowID INT IDENTITY(1,1), Status VARCHAR(50), Address VARCHAR(250), ListPrice INT, ...
2
votes
0answers
35 views

how to get column name from cross reference query

I used a script from here: http://stackoverflow.com/questions/13757387/getting-sql-server-cross-database-dependencies that Milica Medic posted and it works GREAT thank you :) my question is how do I ...
3
votes
1answer
63 views

Index fragmentation increased significantly after rebuild

I've googled this and I know that some people say index fragmentation doesn't matter, but they go on to describe scenarios where it might matter. Mine might be such a scenario. I have an index on a ...
0
votes
0answers
31 views

Transactional replication with push subscriptions (No errors but not working)

I have a very simple replication process set up. SQL Server 2005 One publisher, one distributor and two subscriptions Subscriptions are push I created the snapshot, it reinitialized to the ...
2
votes
1answer
52 views

stored procedure can select and update tables in other databases - minimal permissions granted

I recently had to make the stored procedure spPartyOrderAllocation_AllocateItems to select and update tables in other databases, while keeping the pemissions levels to the minimum. This was ...
1
vote
1answer
25 views

Run subquery once / optimize join with subquery without needing to use temporary table

Until today I thought I had the hang of optimizing queries with joins on subqueries. But I've been trying to optimize a query and on a whim I tried an old method that I thought I didn't need to use ...
2
votes
1answer
24 views

ON clause of JOIN specific syntax?

I've been a bit curious about the syntax of the JOIN operation, especifically the ON clause where we set the condition. Let's say we are performing a join on an existing table and a table we have just ...
2
votes
1answer
543 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 ...
4
votes
4answers
11k 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
2answers
630 views

Is it possible 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 ...
42
votes
4answers
32k 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 ...
1
vote
1answer
378 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 ...
5
votes
6answers
13k 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 ...
5
votes
1answer
765 views

How can I design a table for quick versioning of textual data?

Version i am talking about here is not of version control systems. Like in SO i got some textual data(compare them to questions) that could be versioned(in the sense edited, saved, rollback). Content ...
9
votes
4answers
6k views

Do you know an easy way to generate one record for each hour of the past 12 hours?

I have a report that shows the count of events for the past 12 hours, grouped by the hour. Sounds easy enough, but what I am struggling with is how to include records that cover the gaps. Here is an ...
0
votes
0answers
36 views

How to drop partitioned index in SQL Server 2005

I am unable to drop partitioned index due to partition scheme and partition function existence. Table has a Primary Key, a Clustered Index and non clustered indexes. I am running the following ...
2
votes
2answers
55 views

How to create a range of dates

I have a table named CN in SQL Server 2005: ID Startdate Enddate 1417 2015-09-30 09:45:00.000 2015-10-10 09:45:00.000 973 2015-09-14 11:03:00.000 2015-09-24 11:03:00.000 I ...
4
votes
1answer
102 views

Datetime as date only

I understand SQL Server 2005 doesn't support DATE and so casting a datetime as a date is not possible. What I want to do is list all records with a datetime within an entire day of the current week. ...
8
votes
4answers
75k 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 ...
29
votes
5answers
77k 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 ...
12
votes
1answer
15k 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 ...
0
votes
0answers
41 views

SQL server is replicating to wrong subscriber

I have a publication which is intended to push data to a subscriber over the internet from a remote server to our onsite local subscriber but it ends up on a wrong subscriber.Here is the scenario: ...
1
vote
2answers
115 views

Transforming multiple rows into a single row

In my company, I need to join table A to table B: Table A is the sources table Table B is the empty table I want to know that how can I do it and the result like table B? Source table A: ID ...
3
votes
1answer
49 views

Grant Permission to Execute Set Transaction Isolation Level

I'm a part-time accidental SQL Server 2005 DBA whose full-time job is developing business intelligence solutions, but where the BI intersects with the database, it falls in my 'stack'. We have a ...
1
vote
1answer
4k views

Mirroring SQL error log - Error: 8474, State: 11

I'm getting these errors in SQL error logs: An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far ...
6
votes
1answer
61k views

CASE statement with IS NULL and NOT NULL

Is there any better way to write the lines below in SQL Server 2005? CASE WHEN (ID IS NULL) THEN 'YES' WHEN (ID IS NOT NULL) THEN 'NO' END AS ID_Value,