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?
SchmidtZ28Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin HillSr. SQL Server DBACommented:
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
Kevin HillSr. SQL Server DBACommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
Kevin HillSr. SQL Server DBACommented:
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
Kevin HillSr. SQL Server DBACommented:
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
Kevin HillSr. SQL Server DBACommented:
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
Kevin HillSr. SQL Server DBACommented:
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
Kevin HillSr. SQL Server DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.