Solved

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

Posted on 2007-03-30
18
301 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
Comment Utility
Use a combination of sp_lock and sysobjects to see which spid is locking the tables
0
 

Author Comment

by:SchmidtZ28
Comment Utility
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
Comment Utility
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
 

Author Comment

by:SchmidtZ28
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:SchmidtZ28
Comment Utility
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
Comment Utility
ok...why are you trying to shrink the database?  That rarely accomplishes anything...

Is this a dev database?
0
 

Author Comment

by:SchmidtZ28
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
so we're done?
0
 

Author Comment

by:SchmidtZ28
Comment Utility
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
Comment Utility
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now