[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

SQL Server 2003 tables hanging up on execute and Cant drop tables

I have a SQL 2003 database, for some reason three tables within the database are hanging up.  If I execute a query against them, it takes a long time to execute and waits for a response from data source, for a simple query such as

Select LoanId
From OptLoan
Where LoanId  = 823

Once it executes after 20 seconds I get SQL Execution Error.
I wanted to just delete the tables and bring them into SQL from another database, but I cant even delete them. Any ideas on solving this?
0
SchmidtZ28
Asked:
SchmidtZ28
  • 9
  • 7
1 Solution
 
Kevin3NFCommented:
Use a combination of sp_lock and sysobjects to see which spid is locking the tables
0
 
SchmidtZ28Author Commented:
Kevin, I'm somewhat new to SQL 2003, and never used sp_lock and sysobjects,
how exactly do I use them, thanks.
0
 
Kevin3NFCommented:
In query analyzer (I assume SQL 2000 here...there is no such thing as SQL 2003):

type:
/**********************/
use myDB --change myDB to your database name
go

select * from sysobjects order by name
--look for the 'id' of the tables you are concerned about and make a note of them

/**********************/


Now:

/**********************/
--type this:
sp_lock

--look in the results for the ObJID that corresponds to the ID you noted earleir.  
--If you find it, some other process is using that table.
--Which process is idetifiable by the number in the SPID column
/**********************/

post back with the results
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
SchmidtZ28Author Commented:
Sorry Meant 2005.

I ran sp_lock and got

ObjId     IndUd      Type          Mode           Status
0           0              DB             S                 Grant

for all rows(55 rows)

Except for 1 row I got

1115151018      0      TAB      IS       GRANT
0
 
SchmidtZ28Author Commented:
Ok, so I looked for 1115151018 in the sysobjects sp such as

use IDS3
go

select * from sysobjects
where id = 1115151018
order by id

and got no results.
What should I be looking for within the sp_locks and systemobjects. The only thing that looked different was the 1115151018 id i mentioned before.
0
 
Kevin3NFCommented:
If all you got was:

ObjId     IndUd      Type          Mode           Status
0           0              DB             S                 Grant

Then locks on the table(s) are not your issue.

What are the exact error messages you are gettnig when you try to drop the tables?
0
 
SchmidtZ28Author Commented:
The real problem here relates to the fact that I have a Vb.net application running on SQL 2005, currently I'm running the app out of my test database IDS3 where the production database is IDS. When running the app it crashes b/c of it timing out when trying to execute a query to fed a dataset.  As it turns out, I noticed that I currently dont have the transaction log being backed up, which I think is causing the problem. Under database properties and Backup I see

Last Database Backup 3/30/3007
Last Database Log Backup   None

Is this the problem and how do I get the log to back up?

I dont think its executing b/c the log is failing.
0
 
Kevin3NFCommented:
If the T-log is full, that is a problem.  If it is not full, that will not cause an issue.

If this is a test database, switch to Simple recovery model and forget the t-log issue altogether
0
 
SchmidtZ28Author Commented:
When trying to shink the database I'm getting

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 35864 pages for database 'IDS3', file 'Policy_Data' on file 4.
100 percent processed.
Processed 1 pages for database 'IDS3', file 'Policy_Log' on file 4.
BACKUP DATABASE successfully processed 35865 pages in 5.332 seconds (55.102 MB/sec).
DBCC SHRINKDATABASE: File ID 1 of database ID 8 was skipped because the file does not have enough free space to reclaim.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Its saying it does not have enough free space to reclaim, any ideas?
0
 
Kevin3NFCommented:
ok...why are you trying to shrink the database?  That rarely accomplishes anything...

Is this a dev database?
0
 
SchmidtZ28Author Commented:
Well, I think the problem may have come from importing a large amount of data from another database. The database was working as expected, I imported 10 tables from access in a 1 minute time span with a total row count of over 250,000 records (all tables included) and blow the database size up, thats why I think the log is failing. I was trying to shink so that the database has room to grow, which I feel is the problem.
0
 
SchmidtZ28Author Commented:
Yes it is a development database, So I can basically do anything I want with it without any fear of losing data.
0
 
Kevin3NFCommented:
Flip it to Simple Recovery Model:

In Management Studio:

Right click the database, go to properties
Click Options
Change the Second Drop-down to Simple

0
 
SchmidtZ28Author Commented:
Sorry to say but I was pretty far off in diagnosing the problem at hand, it was not SQL related, instead it was failing due to my connection string to the database within the application. I have a connection which executing thousands of transactions, then at some point of the process I was executing a query and filling a dataset and trying to pass the dataset back into the transaction, it was unable to handle this thus locking up the database.  Thanks for your input.
0
 
Kevin3NFCommented:
so we're done?
0
 
SchmidtZ28Author Commented:
Yes I fixed it, The problem was a piece of code failing inside my Vb.net application and completely unrelated to any problems within SQL Server. Sorry for not realizing soon and wasting your time. Thanks again. I think I was unable to delete the tables b/c somehow my app was holding a connection with them, it wasnt until I closed all connection to the database was I able to delete them.
0
 
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now