Solved

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

Posted on 2007-03-30
18
325 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
  • 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

776 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