Troubleshooting Deadlocks

Troubleshooting
Hi list,

I'd be grateful for a little advice. Running into some problems and not sure where to start. I've looked through some books and articles and Online Help but need some insight into the issue from a person.

I'm running a SQL2K server with about 30 databases, none of them over 20M in size. These db's are driving websites on servers in the same server room and also accessed remotely using Enterprise Manager. For 95% of the time these databases are performing well. Then connections on webpages start timing out. This is a problem that 'fixes' itself very quickly but I need to identify what's causing the problems. I have sa access to the server and am trying to find a way, via perfomance monitoring or enterprise manager, to work out which database, if any, is responsible for this behaviour. There are no blocks visible in Current Activity on Enterprise Manager, I'm getting average LockRequest/sec of about 2000 bit with peaks up to 31000. Processor time is averaging out at about 10%, not too scary.

I can find lots of information on optimising individual databases and queries within them but no too much on identifying processes or queries that might be hitting a whole server installation in this fashion.

Any advice most welcome.

Jo

View Replies !

---

Troubleshooting Help
I need help. I have a SSIS package that connects to a ODBC database, it then does ETL from that database into a SQL 2005 database. I run the package using a Sql Agent job each night. However the package fails randomly with no apparent reason. I have setup logging and after viewing the logs I am no closer to solving the problem. Any Ideas how I can track this down? Thanks.

View Replies !

---

Troubleshooting ADO
This error is triggered by the following code. How do I figure out why?

It works fine on an old server running win2000 but blows up on win2003




Microsoft VBScript runtime error '800a01a8'

Object required: 'Param1.value'

/mmd/_ScriptLibrary/getcons.asp, line 19






set conn = session("conn")
'     Response.write(pg);

set cmdStoredProc = Server.CreateObject("ADODB.Command")
set cmdStoredProc.ActiveConnection = conn
cmdStoredProc.CommandText = "sa.mmd.getConsultantList"
cmdStoredProc.CommandType = adCmdStoredProc

set Param1 = cmdStoredProc.CreateParameter("conam",adVarChar,adParamInput,3)
cmdStoredProc.Parameters.Append Param1
Param1.value = request.querystring("q")
Param1.size = Param1.value.length
set rs = cmdStoredProc.Execute

View Replies !

---

Troubleshooting
Hello,
I have tables/columns exist.How to encrypt database or mask columns if I have to send a copy of DB to microsoft to troubleshoot for a problem.
Thanks.

View Replies !

---

Need Help Troubleshooting Code
Hi. I was wondering if someone could take a look at this script for me. I have it doing everything I want it to, but it won't connect to the database. I can connect to the database using GridView, etc, but I set up this form myself to see if I could get it to work. Any help is much appreciated.
 Here is the code behind
Imports System
Imports System.Data
Imports System.Data.SqlClient
 
Partial Class Prac_Beth_bethCustomers
Inherits System.Web.UI.Page
Protected Sub txtDate_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtDate.Load
txtDate.Text = DateTime.Today.ToShortDateString()
End Sub
Protected Sub customerIDLabel_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles customerIDLabel.Load
customerIDLabel.Text = DropDownList1.SelectedValue
End Sub
Protected Sub UnitPrice_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles UnitPrice.Load
UnitPrice.Text = DropDownList2.SelectedValue
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
'txtOrderTotal.Text =
'sql statements to insert items into database using Transactions table
Dim conn As SqlConnection
Dim trans As SqlTransaction
Dim cmd As SqlCommand
Try
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString)
conn.Open()
trans = conn.BeginTransaction
cmd = New SqlCommand()
cmd.Connection = conn
cmd.Transaction = trans
'set transaction details
cmd.CommandText = "INSERT INTO Transactions(practitionerID, customerID, ItemCode, ItemName, ItemQuantity, ItemRate, TransactionDate, OrderTotal) VALUES (@practitionerID, @customerID, @ItemCode, @ItemName, @ItemQuantity, @ItemRate, @TransactionDate, @OrderTotal)"
cmd.Parameters.Add("@practitionerID, DataSqlDBType.int")
cmd.Parameters.Add("@customerID, DataSqlDBType.int")
cmd.Parameters.Add("@ItemCode, DataSqlDBType.VarChar,50")
cmd.Parameters.Add("@ItemName, DataSqlDBType.VarChar,100")
cmd.Parameters.Add("@ItemQuantity, DataSqlDBType.int")
cmd.Parameters.Add("@ItemRate, DataSqlDBType.money")
cmd.Parameters.Add("@TransactionDate, DataSqlDBType.datetime")
cmd.Parameters.Add("@OrderTotal, DataSqlDBType.money")
cmd.Parameters("@practitionerID").Value = "2"
cmd.Parameters("@customerID").Value = customerIDLabel.Text
cmd.Parameters("@ItemCode").Value = "2"
cmd.Parameters("@ItemName").Value = "Coaching"
cmd.Parameters("@ItemQuantity").Value = txtQuantity.Text
cmd.Parameters("@ItemRate").Value = DropDownList2.SelectedValue
cmd.Parameters("@TransactionDate").Value = txtDate.Text
cmd.Parameters("@OrderTotal").Value = txtOrderTotal.Text
Dim transactionID As Integer
transactionID = Convert.ToInt32(cmd.ExecuteScalar())
trans.Commit()
Catch SqlEx As SqlException
If trans IsNot Nothing Then
trans.Rollback()
End If
'Tools.log("An error occurred while creating the order", SqlEx)
Throw New Exception("An error occurred while creating the order", SqlEx)
Return
If conn IsNot Nothing Then
conn.Close()
End If
Catch ex As Exception
End Try
 
End Sub
 
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
txtOrderTotal.Text = UnitPrice.Text * txtQuantity.Text
End Sub
End Class
----------
Here is the page
 
<%@ Page Language="VB" MasterPageFile="~/admin.master" AutoEventWireup="false" CodeFile="bethCustomers.aspx.vb" Inherits="Prac_Beth_bethCustomers" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="AdminPlaceHolder" Runat="Server">
<table border="0" cellpadding="6" cellspacing="0" style="width: 849px">
<tr>
<td style="width: 235px" valign="top">
Select a Customer:<br />
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"
DataTextField="LastName" DataValueField="customerID">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceTemplate %>"
SelectCommand="SELECT [LastName], [customerID] FROM [Customers] WHERE ([PrimaryPractitioner] = @PrimaryPractitioner)">
<SelectParameters>
<asp:Parameter DefaultValue="Beth" Name="PrimaryPractitioner" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<br />
<strong>Customer Details&nbsp;<br />
</strong>
<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="customerID"
DataSourceID="SqlDataSource3" Height="50px" Width="222px">
<Fields>
<asp:BoundField DataField="FirstName" HeaderText="First Name" SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="Last Name" SortExpression="LastName" />
<asp:BoundField DataField="Street" HeaderText="Street" SortExpression="Street" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
<asp:BoundField DataField="State_Province" HeaderText="State" SortExpression="State_Province" />
<asp:BoundField DataField="Zip" HeaderText="Zip" SortExpression="Zip" />
<asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceTemplate %>"
SelectCommand="SELECT [customerID], [FirstName], [LastName], [Street], [City], [State_Province], [Zip], [Country], FROM [Customers] WHERE ([customerID] = @customerID)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="customerID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</td>
<td valign="top">
&nbsp;<table border="0" cellpadding="9" cellspacing="0" style="width: 572px">
<tr>
<td style="width: 102px" valign="top">
</td>
<td valign="top">
<asp:Label ID="txtDate" runat="server" ForeColor="#000000"></asp:Label></td>
</tr>
<tr>
<td style="border-top: #a9a9a9 1px solid" valign="top">
Customer ID</td>
<td style="border-top: #a9a9a9 1px solid" valign="top">
<asp:Label ID="customerIDLabel" runat="server"></asp:Label>
</td>
</tr>
<tr>
<td style="border-top: #a9a9a9 1px solid" valign="top">
Item</td>
<td style="border-top: #a9a9a9 1px solid" valign="top">
Coaching</td>
</tr>
<tr>
<td style="border-top: #a9a9a9 1px solid" valign="top">
Select Rate:</td>
<td style="border-top: #a9a9a9 1px solid" valign="top">
$
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource1"
DataTextField="RateMinute" DataValueField="RateMinute" AutoPostBack="True">
</asp:DropDownList>
per Minute<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceTemplate %>"
SelectCommand="SELECT [RateMinute] FROM [Practitioner_Rates]"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td style="border-top: #a9a9a9 1px solid" valign="top">
Select Quantity</td>
<td style="border-top: #a9a9a9 1px solid" valign="top">
<asp:TextBox ID="txtQuantity" runat="server" Width="24px"></asp:TextBox>
Minutes x $<asp:Label ID="UnitPrice" runat="server"></asp:Label>&nbsp;&nbsp;<asp:Button ID="Button2"
runat="server" Text="Calculate Order Total" /></td>
</tr>
<tr>
<td style="border-top: #a9a9a9 1px solid" valign="top">
Order Total</td>
<td style="border-top: #a9a9a9 1px solid" valign="top">
$<asp:TextBox ID="txtOrderTotal" runat="server" Width="66px"></asp:TextBox></td>
</tr>
<tr>
<td style="border-top: #a9a9a9 1px solid" valign="top">
</td>
<td style="border-top: #a9a9a9 1px solid" valign="top">
<asp:Button ID="Button1" runat="server" Text="Process Order" /><br />
</td>
</tr>
<tr>
<td valign="top" colspan="2">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="transactionID"
DataSourceID="SqlDataSource4">
<Columns>
<asp:BoundField DataField="ItemName" HeaderText="ItemName" SortExpression="ItemName" />
<asp:BoundField DataField="ItemQuantity" HeaderText="ItemQuantity" SortExpression="ItemQuantity" />
<asp:BoundField DataField="ItemRate" HeaderText="ItemRate" SortExpression="ItemRate" />
<asp:BoundField DataField="TransactionDate" HeaderText="TransactionDate" SortExpression="TransactionDate" />
<asp:BoundField DataField="OrderTotal" HeaderText="OrderTotal" SortExpression="OrderTotal" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceTemplate %>"
SelectCommand="SELECT * FROM [Transactions] WHERE ([practitionerID] = @practitionerID)">
<SelectParameters>
<asp:Parameter DefaultValue="2" Name="practitionerID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</td>
</tr>
<tr>
<td valign="top" colspan="2">
&nbsp;
</td>
</tr>
<tr>
<td style="width: 102px" valign="top">
</td>
<td valign="top">
</td>
</tr>
<tr>
<td style="width: 102px" valign="top">
</td>
<td valign="top">
</td>
</tr>
<tr>
<td style="width: 102px" valign="top">
</td>
<td valign="top">
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="width: 235px" valign="top">
</td>
<td valign="top">
</td>
</tr>
<tr>
<td style="width: 235px; height: 9px;" valign="top">
</td>
<td valign="top" style="height: 9px">
</td>
</tr>
<tr>
<td style="width: 235px" valign="top">
</td>
<td valign="top">
</td>
</tr>
<tr>
<td style="width: 235px" valign="top">
</td>
<td valign="top">
</td>
</tr>
<tr>
<td style="width: 235px" valign="top">
</td>
<td valign="top">
</td>
</tr>
<tr>
<td style="width: 235px" valign="top">
</td>
<td valign="top">
</td>
</tr>
<tr>
<td style="width: 235px" valign="top">
</td>
<td valign="top">
</td>
</tr>
<tr>
<td style="width: 235px" valign="top">
</td>
<td valign="top">
</td>
</tr>
<tr>
<td style="width: 235px" valign="top">
</td>
<td valign="top">
</td>
</tr>
</table>
</asp:Content>
 

View Replies !

---

Troubleshooting Needed
Hi, we just started receiving the following error messages about 3weeks ago. We don't get them all the time, or even on the same pagesnecessarily, but we get them from time to time and our sql serverconnection is slower in the last three weeks also. Our database isremotely hosted, and the programmers are also offsite, so we are tryingto deal with this issue with our database server host, with noknowledge of sql server. They say nothing has changed on the server,but we haven't changed any of our front end files either, so we don'tknow what the problem is. Here's 3 of the most common errors:1. Microsoft Cursor Engine error '80004005'Data provider or other service returned an E_FAIL status..../subview.asp, line 1322. Microsoft OLE DB Provider for SQL Server error '80004005'TDS buffer length too large.../subview.asp, line 283. Microsoft OLE DB Provider for SQL Server error '80004005'Unknown token received from SQL Server.../members.asp, line 182We have *no idea* what the above error messages refer to, or whetherthis is something wrong with the server or the ASP pages mentioned. Anyhelp anyone could provide so we can understand the types of messages weare getting would be nice.Regards,Sage

View Replies !

---

Troubleshooting Failed Job
Can anyone help me troubleshoot this failed job. The error message in eventviewer is:SQL Server Scheduled Job 'Job1' (0xB1D83B7357CF2B4C808AD1CFA34CE8F9) -Status: Failed - Invoked on: 2005-05-12 09:31:38 - Message: The job failed.The Job was invoked by User sa. The last step to run was step 1 (Job1).The job step is:EXEC proc1I can execute the procedure in QA.Proc1 does the following:INSERT values into a table in the database where the job is running fromSELECT records from a seperate database on seperate serverI'm using four part naming in the select against a linked server. I suspectthat the issue is the security between the different servers/databases but Idon't know where to get more information about the failed job or what to trynext.thanks

View Replies !

---

Troubleshooting SOAP
Hi,This is the first time I use SOAP. Dont really know the exact pictureyet. Hope you can shed some lights on me.May I know where should I start troubleshooting if I get the followinerror message when running SOAP on Windows 2003, SQL Server 2000 ?Error Code: 0 Error Source= Microsoft VBScript runtime error ErrorDescription: ActiveX component can't create object:'MSSOAP.SOAPClient' Error on Line 1. The step failed.Thanks.

View Replies !

---

Troubleshooting The Debugger
Would like to hear about any issues anyone has had getting the sql 2000 debugger to work for persons other than admins or sa's.

View Replies !

---

Replication Troubleshooting
Can anyone direct me to Merge replication troubleshooting guide? When I try to PULL a snapshot to a subscriber I get the error message One or tables could not be dropped because one or more tables are in use by other publications. But they aren't in use. I don't know where to look.

View Replies !

---

Deadlock Troubleshooting


View Replies !

---

Asking For Help Troubleshooting Sql Code
Hello guys, i am new to sql so forgive me if dont see something in the code that i should. Anyway the code below comes from the sql script that belongs to "BPBlog asp blogging software" ...its not mine.

Whenever i run this code in query analyzer from sql server 2000 i get an error that says :

Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '('.


*line 16 is the below line in the code.

i have been going through books online since last night to try to get an understanding of the syntax of all those keywords that sorround the error but still havent figured it out yet. any advise on the error would be sincerely appreciated.

Thanx,





USE [Blog]
GO
/****** Object: Table [dbo].[tblAuthor] Script Date: 11/07/2006 10:51:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblAuthor](
[fldAuthorID] [int] IDENTITY(1,1) NOT NULL,
[fldAuthorUsername] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fldAuthorRealName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fldAuthorEmail] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fldAuthorWebsite] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fldAuthorBlurb] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fldAuthorPassword] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approved] [smallint] NULL DEFAULT ((0)),
[fldAdmin] [smallint] NULL DEFAULT ((0)),

CONSTRAINT [aaaaatblAuthor_PK] PRIMARY KEY NONCLUSTERED
(
[fldAuthorID] ASC
)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

View Replies !

---

Replication Troubleshooting
We have some transactional replication jobs from oracle to mssql2005.  I found an error in the replication monitor as below,

============================================

Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax. (Source: MSSQLServer, Error number: 1018)
Get help: http://help/1018

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x00000000000000000D4300000000, Command ID: 26290)
============================================

I found the corresponding table from the command id.  Since some of the tables in our env. are created with triggers which cause the replication error occasionally!  How can I trace the information of the transaction/record which made the replication pending?  Also, there may be 'invalid' input in oracle and replicate to mssql2005 which cause error in trigger and made the replication panding.  Any way to solve this kind of problem?  Thanks in advance

View Replies !

---

Troubleshooting Scenarios
Does anyone have any good places where I can get some practice scenarios for DBA activity? Also any transact sql puzzles to solve for practice purposes. I want to get as much "real world" activity under my belt as possible in a quick time-frame.

Thanks.

View Replies !

---

Troubleshooting Data Updates
I don't know if the title for the subject is appropriate here, anywayhere goes:This process was set up by someone and I have inherited it. I have asql2000 database that has about 13 tables that get populated with datafrom 3 different databases. I have identified where each of this datacomes from, and the stored procedures that do the updates, inserts, anddeletes, and the jobs that run these stored procedure to do theupdates, except for one table. The updates for all the other tablesare done through scheduled jobs. For the one table I know where thedata comes from and the stored procedure that needs to run to do theupdate on the table, however I have not been able to identify theprocess that runs the stored procedure.I am hoping that someone can give me a clue as to how to find out wherea stored procedure is being used - or any other hint as to how I couldgo about finding out how this table gets updated.ThanksKR

View Replies !

---

Replication Troubleshooting Tips
Hi all:

I'm new to replication but I have already set up replication and have seen it
working and failing and have gotten myself out of jams so far but there must
be an easy way to administer it when things don't replicate as expected. I'm
finding that I could easily kill half a day just trying to dig up
information leading to troubleshooting tips. Is there documentation just on
managing this feature. The regular MS Administrator's guide doesn't offer much.


Currently I have a problem that if replication fails on one command I get a
SQL Mail telling me of the problem but does replication continue to the next
command or does it just stop until the problem is fixed? I'm finding that I am
constantly checking the publisher and subscriber databases and verifying if
replication is indeed doing what the msjob_commands table reports. I set the
batch to commit after each transaction instead of every 100.

Help...

View Replies !

---

Deadlock Troubleshooting !Urgent
I have deadlock problem in my application. Set up trace 1204 and restarted the server. How do I get to the report that is generated? Where is the information that is captured as a result of this trace stored? Looked at sqldiag.txt, and error logs, it isn't available
Any help will be appreciated. Thanks

View Replies !

---

Troubleshooting Client Connectivity
Has anyone seen a document on "How to
troubleshoot Client Connectivity for
SqlServer 2000" ? Thanks in advance.

View Replies !

---

Dts Job Failure Troubleshooting Step
I am having one DTS job failed but it has not written much in the history. i wont to know the exact cause of the failure.
please help..

thanks in advance

View Replies !

---

Troubleshooting A New Application Role
Hi all,

This one is a real X FIle, just without Mulder, Scully or the Lone Gunmen!

I have a database, to which access must be restricted via a sole application. So, I have to use an application role. I go in the database and run these statements to add and activate the roles, respectively;

Exec sp_addapprole 'Sirius', 'password'

(The system confirms the role is created.)

Exec sp_setapprole 'Sirius', 'password' 'odbc'

(The system confirms the role is activated.)

Right, now I should not be able to connect using anything but this role, agreed? But here's where things go wrong. I can then successfully connect from another computer by using MS query from Excel, from a login that is not even a member of the Public Role!

I tried again, started and stopped the Server/DTS/Agent services and dropped the old role after each successful login before recreating it. I've checked my syntax exhaustively. I must be doing something wrong, or overlooking something, otherwise MS has a major security problem! (Just hope the Cancer Man doesn't find out!!)

Thanks in advance everyone,


Jaishel.

View Replies !

---

Database Maintenance Troubleshooting
SQL Server 7.0 with SP 1
Database maintenance job is failing
with error about being unable to delete old backup file.
I can manually delete the file with no problem.
This occurs randomly, maybe once a week.
Any ideas on what's wrong, and how to fix it?

View Replies !

---

Troubleshooting Reporting Services
Hi all
My problem is, as I suppose, simple.

I am working on OS XP Professional SP 2, 512 MB RAM, 80 GB HDD. I also installed IIS 5.1

I installed SQL Server 2005 Express edition with Advanced Services including Reporting Services.

In some video I saw that I should configure Reporting Services on my own after instalation, so I did.

Then I started Reporting Services Configuration Manager, and made my own configuration. I called Virtual Directory MyReports.

When I started Internet Explorer with URL:

http:\localhostMyReports I received a message like "HTTP:1.1 500 Internal Server Error"

How to properly configure IIS and Reporting Services?

Reporting Services is working in Reporting Services Configuration Manager


Please help me out

View Replies !

---

Query Performance Troubleshooting Help
Hi all. I'm new to this forum and looking for some assistance.

I've run into a unique (for me) performance problem.

I have a select statement that performs fine ( < 1 second ) using one set of values in the criteria but very poorly ( > 3 minutes )using different values. In both circumstances the query returns zero rows. The query involves a parent-child join with the criteria spread across both tables.

The execution plan looks similar between the two; the difference being a few percentage points difference on some of the operations. The tuning advisor has no recommendation in case 1 but suggests a couple of additional indexes and 4 statistics in case 2.

My gut tells me that the solution is *not* applying the additional indexes/statistics but some other issue. Or it could be the sushi I just ate.

Anyway, I'm hoping someone can point me in the right direction as what to analyze to determine why simply changing a single supplied criteria value would have such a dramatic effect on performance.

View Replies !

---

Need Help Troubleshooting Diff BackUp Job
I have a handful of jobs that I need to troubleshoot, but this one will hopefully give me enough insight to do the rest myself. I need to figure out why a Maintenance Plan that performs a Diff Backup on 4 databases, and then a transaction log back up on the 4 databases is failing. I just get the following error message, and I cannot tell what failed.

Executed as user: <SERVER_NAME>SYSTEM. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:20:05 AM
Progress: 2008-01-03 11:20:05.95
Source: {SOME_STUFF_WAS_HERE}
Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.35
Source: Back Up Database (Differential)
Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.35
Source: Back Up Database (Differential)
Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.37
Source: Back Up Database (Differential)
Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.37
Source: Back Up Database (Differential) ... The package execution fa... The step failed.

How can I find out how to fix this problem? And please let me know if I am doing something wrong. (For instance, perhaps those two tasks shouldn't be together.)

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

View Replies !

---

MSDE Troubleshooting Advice Needed!
I can't connect to any instance of MSDE on my laptop using my web app. I duplicate the whole process on another local machine and have no problems. I'm using DNN, but it's not a problem with a connection string.

Over the last week I've had to rebuild this laptop - reformating and all. I'm running win xp pro, vs'03. I've tried this on msde2000sp3a & msde2000rela. Everthing is fully patched, updated, and current.

I'm working on two pcs at the same time with identical environments, doing one step on one keyboard and then the other:
I take one set of fresh DNN files and install them on each pc.
create a new db, user with dbo rights.
create a new virtual directory in iis.
alter the dotnetnuke.sln, dotnetnuke.vbproj.webinfo to reflect the path to the local site.
alter the web.config file with the connection string.
browse to the website (this is how dnn launches, creating the db from scripts, etc.)

At this point my desktop pc works great and I've got a new instance of DNN.

My laptop does launch dnn, but it can't connect to the db and says there's a problem with the connection string - but the only difference in the strings is the instance name - which isn't spelled wrong!!!!

So I'm led to believe that there is something wrong with the setup on the laptop which was just rebuilt.

It's got .net 1.1, msde2000sp3a, winxppro sp1. I've uninstalled and reinstalled all the components I can think of.

What would you do??? I'm completely out of ideas on this.

Do I format the hd and start again? That would hurt, but I've tried everthing else I can think of.

Oh, here's the other rub. I can create a system dsn, test the connection - and it works! I know that point's toward dnn, but like I've said I believe I've ruled that out.

Thanks for any advice...

View Replies !

---

Troubleshooting SQL Buffer Cache Hit Ratio
This issue just happen recently. The buffer cache ratio went from > 90%to 50% and has slowly been climbing back up over 8 hours or so. Itscurrently @ 76%. Is this something I should take action on immediately?It seems to be coming back to normal...

View Replies !

---

Troubleshooting A Corrupt Uninstall Of SQL Server
Hi --

I am a new user of SQL Server, Enterprise Manager, and Reporting Services (all three are installed on my comptuer) and have a problem. Please bear with me, I am very new at this and don't know all the correct terminology!

Someone in our IT group "discovered" that I had two "instances" of SQL on my machine. The way he came to this was that I was trying to use the "." to connect to my local machine in Query Analyzer and it wasn't pulling the databases I had saved to my computer, it was looking in another folder LOCALDB (a subset of my computer's name). He was unsure how to move all of my databases on my comptuer to that LOCALDB folder so he uninstalled the "LOCALDB" entry from the Add/Remove Programs screen to fix this. He said "No to All" when it prompted him to remove shared entries.

Consequently, when I load Enterprise Manager now, I get the "Snap-In failed to initialize" message. I also do not have any SQL Server options in my Start Menu Anymore. Query Analyzer still exists, though, I had saved a shortcut to my taskbar and I can still launch it from there. He is saying all I need is to re-install the "Client Tools" folder from the CD and that will fix the problem. I am skeptical, since it appears to be a larger issue (SQL isn't in my Start Menu anymore!).

I am sure that my SQL install is probably completely corrupt. I would like to return to full functionality of SQL Server, Enterprise Manager, and MRS as soon as possible. Can anyone begin to advise me on how to do so?

I really do appreciate your help in advance.
jzafereo

View Replies !

---

Transactional Replication - Performance And Troubleshooting
Hi

I have sucessfully set up transactional replication, allowing the subscriber to update the publisher. All works well for a while, but after a couple of weeks or so it fails, but always for a different reason !

My question is, is there anything that can be done to help replication stay healthy. I had thought of doing regular backups of the database and the transaction log, and then truncating the transaction log.

Any advice, or links to other troubleshooting resource much appreciated.

View Replies !

---

Troubleshooting Errors When Using The Reportviewer Control
Is there some way of troubleshooting errors when using the reportviewer control (with no reportserver).?  Specifically, I am dynamically loading supreports and their ascociated datasets.  I get an "Error: Subreport can not be shown." in the report viewer display panel at run time.  In the past when I have gotten them it was a matter of randomly trying things until I hit on the problem.   I can trace it to the point where I load the dataset for the subreport and all seems correct.  There are no report parameters to be passed so I know its not that.  I've double checked the subreportname,datasource and dataset names and they are all okay.

 

Any help would be greatly appreciated.

View Replies !

---

Troubleshooting Transactional Replication Latency
Hello!

We have setup up transactional replication with dedicated distributor in SQL Server 2005 environment. I have noticed that during particular time of the day latency is increasing dramatically. I have been checking Tracer Tokens and Total latency during that time is around 30-40 min (both publisher to distributor and distributor to subscriber is taking much longer that normal). Normally, it is less than 10 sec. I was wondering if there is a way to pinpoint exact cause of the latency. This is pull subscription.
I would appreciate if someone can share (or point to the right direction) best practice on transactional replication setup/maintenance. My understanding is that only committed transactions are replicated, correct? I checked database on publisher and didn't see any outstanding long running transaction.

Any help is greatly appreciated.

Thanks,
Igor

View Replies !

---

Troubleshooting: My Database Has Started To Grow TOO Fast
The primary database i'm responsible for has started to grow super fast. Every couple of days is growing by 10% (which matches with the db settings). But, the recent growth doesn't match with the historical growth. It took a couple of months to grow from 7 to 8 GB, but it has grown to about 24 Gb in the last 2 months. Bottom line - trust my assertion that it's growing alarming fast.

I need help determine what objects are fueling the growth. If I know the objects, I can probably determine the cause. From a flip-side, it might be legit data stored very poorly. I'm open to any ideas...but I need to get ahead of this problem in the next week or so...or I'm going to run out of room on the hard drive and could start to affect my users.

Please send my any ideas you might have.

Thanks,

alex8675

View Replies !

---

Troubleshooting Linked Server Connecting Via ODBC
I have created a linked server to a Visual FoxPro free table directory via ODBC usinng the following commands (The system DSN requires no authentication and I can access the data with Excel using this DSN):

sp_addlinkedserver 'MyDb','','MSDASQL','MySystemDSN'
EXEC sp_addlinkedsrvlogin 'MyDb', 'false', 'sa', NULL, NULL

I tried a simple select from MyTable as below:
SELECT *
FROM MyDb...MyTable

Query analyzer returns:
OLE DB provider 'MyDb' does not contain table 'MyTable'. The table either does not exist or the current user does not have permissions on that table.

What have I done wrong?

View Replies !

---

Troubleshooting Slow Pass Through Query From Access
One of our developers has a Microsoft Access 2000 database that runs queries that compare the Access db data to a SQL Server database.  He uses pass through queries to get the data from SQL Server.

 

We're finding that the Access query runs quickly against our test server, even with copies of production data, but when we try the same query against our production server, the CPU on the local computer running Access is pegged and the query takes up to 10 minutes to run.

 

First I verified that the SQL Server structures between test and production were identical, including indexes.  I checked index fragmentation, and productions indexes are less fragmented than tests.  Again, test and production currently have the identical data.

 

I've run a profiler trace on our production SQL Server 2000 server, and I see the RPC for the query from Access running almost instantaneously.

 

Any ideas on what might be the cause of the difference in speed between test and production SQL Server servers, or any suggestions on other things I could look at/tools I could use to troubleshoot this issue further?

View Replies !

---

Quick Question - Need Some Advice On Troubleshooting SSIS Errors
 

Hi Everyone:

 

I have a SSIS Package which in brief moves data from one SQL data store to another. On my local machine, the package executes fines, and does what it is supposed to do, by moving data from Point A to Point B. I have both Point A(DB) and Point B(DB) on my local machine. But when I deploy this package over to ASSEMBLY which is basically another environment, I get weird intermittent errors. What should I do? Can someone give me some tips on how to do good error logging in SSIS? I am currently writing to Windows Event Log, and I seem to look at some errors that are coming thru. What else can I do on SSIS Package side, to improve error reporting and handling, so troubleshooting is faster and more effective. I just need some stable advice on how to setup my package to do error logging where troubleshooting issues in different environments is more effective. Also I need to know, what could be causing these issues? Thanks.

 

MA

View Replies !

---

Losing Rows From File To Destination Table - Need Troubleshooting Help
I am losing data from time to time and can not figure out where the rows are vanishing to.  In essence, I have 5 files that I process.  The processing occurs on a daily basis by one SQLAgent job that calls 5 individual packages, each doing a different piece of work.

I stage each file to a temp table, then perform some minor transformations, then union the rowsets from each file before landing to another temporary table.  A subsequent package performs additional transformations that apply to the entire dataset, before inserting them to their final destination.  Along the way, I reject some records based on the business rules applied.  Each package in the entire Job is logging(OnError, TaskFailed, Pre and Post execute).  Their are no errors being generated.  No rows are being rejected to my reject tables either.

Without getting into the specific transforms, etc. being used in this complex process, has anyone seen similar unexplained behaviour?  I've not been able to identify any pattern, except that it is usually only 1 or 2 of the record types (specific to a source file), that will ever not be loaded.  No patterns around volumes for specific source files.  There are some lookups and dedupes, however I've seen the records 'drop off' before reaching these transforms.  I have noticed I had my final desination load not using fast load.  However sometimes the records disappear before even getting to my final staging table which is inserting using fast load.  I am going to turn on logging the pipelinerowssent event.  Any other suggestions for troubleshooting/tracking down these disappearing records?

Thanks

View Replies !

---

I/O Congestion Troubleshooting: Memory And Swap File Usage Question
We are hosting a 140 GB database on SQL Server Version 7 and Windows2000 Advanced Server on an 8-cpu box connected to a 15K rpm RAID 5SAN, with 4 GB of RAM (only 2 GB of which seem to be visible to theOS) and a 4 GB swap file. (The PeopleSoft CIS application will notpermit us to upgrade to SQL 2K.) We recently upgraded the server from4 to 8 cpus and the SAN disks from 10K to 15K drives. But we stillhave heavy SAN disk usage, sometimes at 100%, and read queues oftenaveraging 4 and peaking at 12.The CPUs are loaded at only 20-50%. (The politics are such that it iseasier to throw hardware at the problems.)We are looking into archiving, converting from RAID 5 to RAID 10, andat splitting the mdf file into several file groups in an attempt toget more disk heads into play. (We are also looking at rewriting theapplication to reduce the read volume and frequency.) Does anyone haveany other ideas?Incidentally, does swapfile get used when the physical memory equalsthe OS maximum? If the OS can only see 2 GB and we have 2 GB (actually4 GB) of memory, is the 4GB local swap file on the C drive unused?Thanks in advance for any assistance.

View Replies !

---

Deadlocks
Our system is reasonably complex with a lot of non-trivial stored procedures. As the load on our DB increased we're now getting more and more deadlocks (10 per day or so from about a million stored proc executions).

We try to avoid transactions where we can, and we do attempt to optimse stored procs to steer clear of deadlock conditions, but with the sheer number of stored procedures we can't possibly avoid all deadlock conditions.

One solution I'm considering is to re-run stored procs that failed because of a deadlock. In the .net code we'll run the stored proc, check for a deadlock error and if one happened, wait 100ms and try again.

What do you guys think?

View Replies !

---

Deadlocks
Hi EverybodyI am new to sqlserver 2000.I know basics of locks.but i dont know how toresolve deadlock issues.I am cofusing by reading articles with 90%information and remaining 10% missing.Can any one help me which is the goodsite to learn and resolve deadlocks.Note: I create deadlock. when i try to trace deadlock using dbcc traceon(1205,3604,-1).In error log showing nothing about the deadlock.showing created traceon.........Any help would be appreciated.--Message posted via http://www.sqlmonster.com

View Replies !

---

Deadlocks, Why?
We have a problem with a table giving us deadlock issues and we can'tfigure out why.It's a table we write to fairly often perhaps 50 times a minute. Andalso do a select of 200 rows at a time from 4 servers every 5 minutes or so.We are only keeping 48 hours worth of rows in the table which averagesat 30000 a day on a busy day.This table has 1 PK and 2 FKs plus one TEXT column which does notparticipate in the WHERE clause.We are using binded variables.We have applied the latest patch to SQL2003 server running onWindows2003. The patch is supposed to resolve deadlock issues.Anyone have any advice on how to alleviate this problem.Thanks

View Replies !

---

Deadlocks
If an instance of SQL 2005 was in use and was using row versioning,under what circumstances would the below error occur?Transaction (Process ID 56) was deadlocked on lock resources withanother process and has been chosen as the deadlock victim. Rerun thetransactionWe used to get this sort of thing when a large copy process was runningunder a transaction, but all it was doing was reading the records andcreating brand new records yet would still lock the entire table. Oncewe enabled the row versioning, we stopped having this issue, but itseems that there are some circumstances in which it still happens, i.e.the above error.Any ideas how that might occur?

View Replies !

---

Too Many Deadlocks
Hi,

I've got a deadlock problem. The log below has been generated. The problem is that during one day, I have more than 300 deadlocks like it. Before, the were not so many deadlocks.
During past year, the number of users has grow (from 100 before to 500 or 700 now)


*** Deadlock Detected ***
- Requested by: SPID 360 ECID 0 Mode "S"
- Held by: SPID 113 ECID 0 Mode "S"
Index: aaaaa_PK
Table: TABLE_1
Database: MYDB
== Lock: KEY: 22:325576198:1 (ff009ae5078d)
- Requested by: SPID 113 ECID 0 Mode "S"
- Held by: SPID 374 ECID 0 Mode "X"
Index: aaaaa_PK
Table: TABLE_1
Database: MYDB
== Lock: KEY: 22:325576198:1 (ff009ae5078d)
- Requested by: SPID 374 ECID 0 Mode "IX"
- Held by: SPID 360 ECID 0 Mode "S"
Table: TABLE_2
Database: MYDB
== Lock: PAG: 22:1:2428
== Deadlock Lock participant information:
Input Buf: S E L E C T the_rest_of_the_query
SPID: 360 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 1
Input Buf: s p _ e x e c u t e 8
Input Buf: s p _ c u r s o r 8À B 8 8f ç @ Table I
Input Buf: S E L E C T the_rest_of_the_query
SPID: 360 ECID: 0 Statement Type: SELECT Line #: 1
== Session participant information:
== Deadlock Detected at:
==> Process 360 chosen as deadlock victim


I have done :
- rebuild indexes on all tables (fillfactor 90)
- analysed memory activity

Could a lack of memory be at the origin of the problem ? Which counters in perfmon are significant for memory lack ?

Could the index fill factor could be at the origin of the problem ? At time, it is at 90 percent.


Config : Winnt4 Server, MS-SQL 7 SP4 , 2 GB of RAM , 2 x Xeon 700


Thanks for any help.

View Replies !

---

Deadlocks (I Think)
Hi folks,

I have an application built on top of a questionable DB design which requires overcomplicated selects. The application is experiencing deadlocks regularly, in some cases with only one concurrent user.

I set the trace flag 1204 but am not seeing anything in the Error.log and I initiated a trace in profiler which does not seem to show any deadlock.
Despite having recreated the problem which show my browser hanging indefinitely. When I run the following queries:

SELECT spid, waittime, lastwaittype, waitresource
FROM master..sysprocesses
WHERE waittime > 10000
AND spid > 50

SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)

I get:

55860978LCK_M_XPAG: 13:1:2573

54AWAITING COMMANDsleeping sa 11499
55UPDATE sleeping sa 21499


respectively. Any help would be welcome.

Thanks in advance,
Don

View Replies !

---

Deadlocks
This is probably a stupid question, but...
Is there any way to totally avoid deadlocks. In some critical applications
we have removed transactions entirely, counting on other means to maintain
database consistency. We still get deadlocks in this area. These are mainly
inserts, and the only thing I can think is that updates to the indexes are
causing multiple page locks which result in deadlocks. Is this true?

Will deadlocks be eliminated in 7.0 with row level locking for this situation?
Or will index page splits still cause a possibility of deadlock contention?

ben

View Replies !

---

Deadlocks
Hi guys,

View Replies !

---

Deadlocks
Hi guys,
Does SQL Server 6.5 log deadlock errors automatically into the errorlog, or do I need to use traceflags ??

TIA
Ju.DBA

View Replies !

---

DeadLocks
I am getting the following dead lock error message writtent to the Error Log.

How do i interpret this...?


2002-07-10 11:49:52.88 spid3 Node:1
2002-07-10 11:49:52.88 spid3 KEY: 6:1531868524:1 (1e0040209980) CleanCnt:1 Mode: X Flags: 0x0
2002-07-10 11:49:52.88 spid3 Grant List::
2002-07-10 11:49:52.88 spid3 Owner:0x26429de0 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:62 ECID:0
2002-07-10 11:49:52.88 spid3 SPID: 62 ECID: 0 Statement Type: INSERT Line #: 67
2002-07-10 11:49:52.88 spid3 Input Buf: RPC Event: sp_Save;1
2002-07-10 11:49:52.88 spid3 Requested By:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-S-S SPID:58 ECID:0 Ec:(0x29f534f8) Value:0x2649f0c0 Cost:(0/0)
2002-07-10 11:49:52.88 spid3
2002-07-10 11:49:52.88 spid3 Node:2
2002-07-10 11:49:52.88 spid3 KEY: 6:1695345104:1 (ffffffffffff) CleanCnt:1 Mode: Range-S-U Flags: 0x0
2002-07-10 11:49:52.88 spid3 Grant List::
2002-07-10 11:49:52.88 spid3 Owner:0x26450f20 Mode: Range-S-U Flg:0x0 Ref:1 Life:02000000 SPID:58 ECID:0
2002-07-10 11:49:52.88 spid3 SPID: 58 ECID: 0 Statement Type: INSERT Line #: 250
2002-07-10 11:49:52.88 spid3 Input Buf: RPC Event: sp_IPAQManagerFetchFilterDetail;1
2002-07-10 11:49:52.88 spid3 Requested By:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null SPID:62 ECID:0 Ec:(0x3bb5f4f8) Value:0x2649e040 Cost:(0/2340)
2002-07-10 11:49:52.88 spid3 Victim Resource Owner:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-S-S SPID:58 ECID:0 Ec:(0x29f534f8) Value:0x2649f0c0 Cost:(0/0)

View Replies !

---

Deadlocks
We have an application that runs on both Oracle 8.1.7 and SQL Server 2000. Due to some poor design, we end up with a table being inserted/updated/selected by multiple processes, almost simutaniously and randomly. Obviously, this is a recipe for deadlocks. What is a bit strange is that we rarely see deadlocks on Oracle, but lots of deadlocks on SQL 2000. One explanation I heard is that Oracle tends to use row-level locks, while SQL server tends to use page-level locks. In adition, SQL server is inefficient in dealing with index updates (we do update some indexed columns quite often).

I am not very satisfied with the explanation. Can someone please shed some lights on this?

Thanks a lot.

View Replies !

---

Deadlocks
Is there any way to totally avoid deadlocks. In some critical applications
we have removed transactions entirely, counting on other means to maintain
database consistency. We still get deadlocks in this area. These are mainly
inserts, and the only thing I can think is that updates to the indexes are
causing multiple page locks which result in deadlocks. Is this true?

Will deadlocks be eliminated in 7.0 with row level locking for this situation?
Or will index page splits still cause a possibility of deadlock contention?

Thanks!
ben

View Replies !

---