?
Solved

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

Posted on 2007-03-30
18
Medium Priority
?
360 Views
Last Modified: 2008-06-28
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
Comment
Question by:SchmidtZ28
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
18 Comments
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18822942
Use a combination of sp_lock and sysobjects to see which spid is locking the tables
0
 

Author Comment

by:SchmidtZ28
ID: 18822959
Kevin, I'm somewhat new to SQL 2003, and never used sp_lock and sysobjects,
how exactly do I use them, thanks.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18823029
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:SchmidtZ28
ID: 18823059
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
 

Author Comment

by:SchmidtZ28
ID: 18823105
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18823143
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
 

Author Comment

by:SchmidtZ28
ID: 18823226
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18823263
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
 

Author Comment

by:SchmidtZ28
ID: 18823376
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18823467
ok...why are you trying to shrink the database?  That rarely accomplishes anything...

Is this a dev database?
0
 

Author Comment

by:SchmidtZ28
ID: 18823516
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
 

Author Comment

by:SchmidtZ28
ID: 18823524
Yes it is a development database, So I can basically do anything I want with it without any fear of losing data.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18823616
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
 

Author Comment

by:SchmidtZ28
ID: 18823818
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18824302
so we're done?
0
 

Author Comment

by:SchmidtZ28
ID: 18824345
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
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 21890442
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question