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

learn more… | top users | synonyms (2)

1
vote
1answer
204 views

table partition in order to solve frequent deadlock issues - how would I know if it will work?

I use sql server 2005 enterprise edition and have a subscriber DB - part of a transactional replication - that is used for Business Intelligence purposes - they run reports out of it, and SSIS ...
1
vote
1answer
746 views

Replication Monitor Information using T-SQL

The image below shows a transaction replication problem that I am currently investigating. The image comes from the Replication Monitor. How can I get this information using T-SQL?
0
votes
0answers
56 views

Tran Log file growing even after log backups and Log shipping configured

I have log shipping configured for a database in sql server 2005 with below details: A) Database X hosted on primary: Log backup every 1 hour as scheduled in LS Backup job: Data file: 40 GB and Log ...
2
votes
1answer
175 views

Listing expired SQL logins

For audit purposes, we need to list all SQL logins with their creation and expiration dates. Is there a way to script this for all of my SQL Servers, rather than performing the task manually through ...
0
votes
2answers
198 views

How can i automate the process of log shipping monitoring

I have log shipping configured between my Prod SQL server 2005 and QA SQL server 2005. Per the requirement from Business we need to provide them with a report at the end of the day for the LOG ...
5
votes
1answer
102 views

SQL Native backup MIRROR TO performance

We are moving to SQL Server 2014 Expensive Edition Q1 next year. Right now we are on SQL Server 2005 Standard Edition, so we don't have access to the oh-so-wonderful MIRROR TO option for our backups ...
-3
votes
1answer
58 views

Finding users with database owner role in sql server 2005 or so [duplicate]

I need a script which will list out all the users with DataBase Owner persmission for a specific database. Thanks
0
votes
0answers
20 views

Usage of temporary tables in my tempdb database on sql server 2005 [duplicate]

I am trying to resolve the disk space issue on one of my drives where tempdb residing occupies more than 100 GB: Stats shown below I read some similar post on the same and i found few queries using ...
6
votes
2answers
2k views

SQL Profiler - Scripting a trace with HostName filter

I have a stock of scripted trace definitions that I use for different levels of diagnostics, none of which filter by HostName. I needed to filter traffic by host today so: Fired up Profiler Created ...
2
votes
4answers
478 views

Help understanding SQL Server 2005 execution plan

One of my stored procedure has long execution time (average around 4 to 7 minutes). Now I trying to tweak it an make it run faster. I am looking at execution plan and two things I see that using ...
10
votes
5answers
2k views

Using IF in T-SQL weakens or breaks execution plan caching?

It has been suggested to me that the use of IF statements in t-SQL batches is detrimental to performance. I'm trying to find some confirmation or validate this assertion. I'm using SQL Server 2005 ...
4
votes
1answer
13k views

trying to turn XML into heirarchical SQL tables, can anyone provide a good explanation of two parts of this code example?

I am trying to learn how to turn XML into heirarchical SQL tables. I have found an old code snippet from a Microsoft forum which is basically what I am trying to do, but I was wondering if someone ...
2
votes
1answer
149 views

XML Whitespaces/Unwanted Characters [closed]

Could anyone help on how to remove white spaces from an XML file? I used the following code that i got from Mikael Eriksson on Stack Exchange to extract the site id: cast(msg as ...
2
votes
0answers
40 views

Can I convert SQL Server 2008 full db to SQL Server 2005?

Recently I converted my SQL Server 2005 database to SQL Server 2008. Now I can't attach that db to SQL Server 2005. I do not have any backup of that db file as well. I have tried to generate scripts ...
0
votes
1answer
100 views

Is my SQL Job causing the High Disk usage?

I have a concern regarding High Average disk queue length for one of my server with below configuration: Version: SQL 2005 with SP3, standard edition. windows: version 2003 r2 SP2 No Processors= 4 ...
0
votes
1answer
245 views

How to add an automatically-populated custom sequence number?

I have table something like this: CREATE TABLE [dbo].[ado_test_table]( [id] [int] IDENTITY(1,1) NOT NULL, [tip] [varchar](10) NOT NULL, [datum] [datetime] NOT NULL, [doc_number] [int] ...
1
vote
1answer
131 views

reusing space after deleting lots of rows [duplicate]

I have a database which is 20 GB and it has history information since year 2001. Now I started deleting data prior to year 2008. after completion of deletion how can i reuse the space that is cleared ...
0
votes
0answers
49 views

Physical Table being used a Temp Table in a warehosue. Any advantage?

As I stated in previous posts I've inherited a warehouse based on SQL Server framework. In my continuous review of existing components and the practices adopted by the predecessors I found something ...
1
vote
1answer
1k 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 ...
1
vote
1answer
602 views

Select into existing table where ID is in another table [closed]

I have one table containing ~35k IDs (TableC), one table that features multiple columns (TableA), amongst others the ID from the before mentioned table and a third empty table, featuring the same ...
0
votes
1answer
21 views

Copy every entry from TableA to TableB where the ID is in a textfile containing a list of IDs

I have a text file (or an xls) with a 35k lines, each containing a single ID. I'm required to copy every entry of TableA to TableB where the ID is in said list. Would it be best if I'd create a new ...
3
votes
3answers
685 views

A way to clear/restart virtual memory creep automatically?

I know it's not good practice to do this, but my company cannot afford me trying to find a proper solution to our problem. I've read this and I could do the same with SQL Server 2005, but I was hoping ...
2
votes
2answers
92 views

Deleting rows from a table with a very small amount of expansion space

I have multiple tables that I have backed up the data in, and now am deleting the old data for the time, archiving is not an option due to space constraints at this time. I have tried running the ...
3
votes
1answer
93 views

How can I create notification on user's connecting to database?

I have users that have access to several databases. I used to have an alert and notification sent to me when a specific user connects to a specific database, but I accidentally clobbered it. Now I ...
1
vote
1answer
50 views

Linked server in SQL Server 2005 using NT authentication impersonation

I am trying to Link a server using an NT authentication as the impersonated connection, but this connection will be static to a specific user instead of passing the currently logged-in user's ...
0
votes
1answer
27 views

Removing Components from a backup

Ok, I will start this off by: Yes, I know I should have paid more attention. That being said, I was trying to create a full backup of my DB prior to doing truncation on a number of the tables. When ...
5
votes
2answers
5k views

Find which session is holding which temporary table

We have a SQL Server 2005 database the temp database has become full. By going into SQL Server Management Studio I can see all the temporary tables in the tempdb. Is it possible to tell which session ...
3
votes
1answer
89 views

Discrepancy between system catalog view and sp_change_users_login?

I want to check for orphaned users on a database. To that end, I use the following query on my SQL Server 2005 instance to get all users and their accompanying logins for a specific database (say ...
0
votes
1answer
928 views

EXCEPT operator vs NOT IN

The EXCEPT operator was introduced in SQL Server 2005 but what is the difference between NOT IN and EXCEPT ? Does it do the same? I would like a simple explanation with an example.
3
votes
2answers
330 views

Reinitializing Subscription Performance

I have 4 Servers, Servers A, B, C, D. Server A publishes a Replication instance, and Servers B, C, D have Pull Subscriptions to it. Server B's subscription needs to be Reinitialized, but I am ...
4
votes
1answer
2k 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, ...
0
votes
1answer
41 views

Move/Copy unique entries only from TableA to TableB, if it's not in the destination table

I'm required to move every unique entry from TableA to TableB, it the unique entry from TableA is not already in TableB. Both tables look the same, they don't have any keys, but the combination of to ...
1
vote
2answers
118 views

Delete duplicates with specific columns as identifier

I'm required to delete all duplicate entries of a table. The duplicates are identified by two non-key values num (int) and com (varbinary(512)).. My amateur-ish SQLfoo lead me to this non-functioning ...
1
vote
2answers
254 views

Granting permission on a particular job to execute on SQL Server 2005

An user is requesting permission on a particular job. How can I give him access on job activity monitor which shows only that requested job?
2
votes
0answers
90 views

Quirk update for cumulative sum of chances for a raffle. What can go wrong?

I need to make a raffle where users have different chances. | USER | CHANCES | |------|---------| | A | 10 | | B | 25 | | Z | 15 | Looking in this answer: drawing a riffle ...
11
votes
3answers
13k views

What is the difference between Shrink Database and File?

DBCC ShrinkDatabase() DBCC ShrinkFile() Do I need to run both DBCC commands in order to shrink the database? What is the difference between these two above?
0
votes
2answers
297 views

SQL agent jobs appear as job id only

SQL Server 2005, I'm seeing about 100 'jobs' in the SQL Agent>jobs node in SSMS. They appear as hexadecimal strings not with their real names: If I query msdb..sysjobs, I see a much smaller list of ...
0
votes
1answer
71 views

Data Files Outgrowing Hardware - Split the File?

Where I'm currently working, the primary business database is located on a virtual server (SQL Server 2005 standard). The temp DB and logs are on one disk, the database itself on another. The SAN is ...
1
vote
3answers
729 views

Select only rows with minimum values from union table

SQL Server 2005 Here is a sampling of the data from a union of two tables (named "vProducts_All"): DBID UPC Price1 Price2 1 023614025559 689.0000 649.0000 2 ...
-1
votes
1answer
425 views

Foreign keys can't be added because they exist but they don't exist!

I have a conundrum. I am re-engineering a sqlserver 2005 database, and in the course of doing so have attempted to correct a simple lookup table flaw between tblUsers, tblUserRoles, and tblRoles ...
1
vote
1answer
147 views

Join select queries on an id in SQL Server

I have three tables that SELECT from and I'd need to join the results. It feels like this should be a rather easy task but I struggle with the syntax. Working on Microsoft SQL Server Enterprise ...
1
vote
3answers
97 views

Clustered Index improvemens for particular queries

I found a table that looks like this: CREATE TABLE [dbo].Table1 ( id INT primary key IDENTITY (1, 1), [idUser] INT NOT NULL , [Amount] INT NOT NULL , [Attempts] INT NOT NULL , ...
3
votes
4answers
575 views

Keep track of time intensive stored procedure with prints

I am no database admin nor is my SQL-foo any good (yet), but have a few tasks to get done in MSSQL, and one is a rather time intensive procedure where I fetch from a CURSOR and WHILE @@FETCH_STATUS = ...
0
votes
1answer
705 views

Store the resulting list of a SELECT in a variable to count AND use the result

I have stored procedure that uses a cursor and for every fetch a duplicate SELECT, once to determine the number of IDs found (SELECT COUNT(ID) FROM TableA WHERE something.. @cur_var) and if the ...
1
vote
0answers
100 views

Can you speed up this query to aggregate millions of rows?

We store our website traffic data in SQL Server 2005, like so: | Date | IPAddress | GalleryUserID | ThumbView | LargeView | ZoomView | | 2014-11-11 | 192.168.0.1 | 22 | true ...
0
votes
1answer
235 views

How to know the Database user Login History from begining? [closed]

I need to know one particular user's login and logout database history from beginning in SQL SERVER 2005. Can anyone help?
5
votes
1answer
3k views

How can I fix “Cannot perform a differential backup … a current database backup doesn't exist”?

We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day. The problem is, the differential backups don't always seem to ...
1
vote
1answer
113 views

Refactoring sql for tables with similar columns but different table names

I have the task of re-factoring a SQL script that performs a lot of unions from different tables. These tables have similar information, so the script queries the same columns as in each table, except ...
2
votes
4answers
5k views

How do I determine if a database is the principal in a mirroring setup?

I have two database servers Server1 and Server2, configured with mirroring. A single database, MirrorDB, is mirrored. There is another database on Server1 named OtherDB which is only present on ...
1
vote
2answers
772 views

Transaction Log Shipping across multiple SQL Server versions

I'm lead to believe that TLS is only appropriate/supported across the same versions of SQL Server. What I'm trying to do is to maintain a reporting (read only) version of a production transactional ...