x

Problem With Database

One of the main applications in the company i work for is not letting users update data to one of our databases and I start running DBCC on the database. The database in question is 5GB and the full backup which was automated to be take of all databases in the server failed last night. tHE BACK UP took 14 hrs and I killed thE PROCESS THIS MORNING. The DBCC is running for one hour? is that normal? what should i do? Please help

more ▼

asked Jan 28, 2010 at 02:53 PM in Default

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

I forgot to post the error we get when we try to update data to one of the databases:

Server Error in '/' Application. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Jan 28, 2010 at 02:54 PM DaniSQL
UPDATE: I reboot the box and the SQL Server Problem is still there and the application is not working. Here is the alerts I get now "Changing the status to FLUSH for full-text catalog "GM5_Proto_Catalog" (5) in database "Database_1" (13). This is an informational message only. No user action is required." also this error "Warning: Failed to change the status to FLUSH for full-text catalog "MyDatabase_1" (5) in database "MyDatabase_1" (13). Error: 0x8001010e(The application called an interface that was marshalled for a different thread.). Please HELP ! Thank you
Jan 28, 2010 at 09:13 PM DaniSQL
I've never heard of or seen this error before. Microsoft has: http://support.microsoft.com/kb/290424
Jan 28, 2010 at 10:59 PM Grant Fritchey ♦♦
All my investigation so far points toward Full text search. Only applications that use databases with full text search have trouble updating. And when we take one of the database to another server the application that had the same problem started to work fine. Any suggestions? BTW SQL Server is 2005 (9.00.1406.00) RTM+ version.
Jan 29, 2010 at 03:59 PM DaniSQL
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

First of, Many thanks for FatherJack and Grant for helping me out. I finally solved the problem by restarting SQL Server and removing the FullText catalog from all the databases that have it and I was able to take backup with out a problem. However, I still dont know what causes the corruption on the FullText Catalogue in the first place.

more ▼

answered Feb 07, 2010 at 11:03 PM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

(comments are locked)
10|1200 characters needed characters left

It sounds like you may have blocking processes preventing things from working. The classic, easy way, to check this is to run sp_who2 on the server. You should be able to see your process and what is preventing it from executing. A better way to drill down on what's going on is to query the DMV sys.dm_exec_requests. This will show what is waiting and what kind of wait state it is in. This will help you troubleshoot what the root cause is.

more ▼

answered Jan 28, 2010 at 03:44 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.5k 19 21 74

Thanks Grant. There are no locks but there are 171 insest statments waiting along with other quieries. do u thing that might be the problem? Also Management is leaning towards restarting the box after 5 today. Any problem with that? How about if i restart the sql server now? How Can I kill all the processes without restarting SQL? Please help. Thanks!
Jan 28, 2010 at 05:17 PM DaniSQL
The processes that are waiting should have a blocking process ID, trace that to another row in the results and see if that is blocked. Keep going until you find the process that is blocking but not blocked. Where does this come from - can you identify the application or its source in any way. If you are sure it wont cause problems you can use the KILL command (followed by the SPID) to remove that process and hopefully the others will work their way through without too much drama
Jan 28, 2010 at 05:37 PM Fatherjack ♦♦
Thanks FatherJack. If I understand what you said correctly...I checked Blocking_session_id and it is 0 for all the processes. I know which applications are unable to update/insert data to one specific database. Applications which are using these database are having difficulty all day. BTW I tried to take a backup copy of the database in question and it failed again. Is there any chance the whole thing might not comeback if i restarted now?
Jan 28, 2010 at 06:08 PM DaniSQL
there isnt any point trying a backup until the blocks are resolved. SP_WHO2 returns a result set. The SPID column is the process ID. The BLKBY column is the SPID that is blocking the SPID in that row. Find a SPID with a number in BLKBY. Find that SPID, if that in turn is blocked repeat process until there is a SPID that has no value in BLKBY. Its Command should allude to the fact its trying something, CPU may be VHigh, DISKIO too and the LastBatch will be sometime back when the system was working. If it looks reasonable to KILL this process
Jan 28, 2010 at 06:21 PM Fatherjack ♦♦
You have 171 active insert statements but no blocks? How long have they been running? Are there any other actively running processes such as DBCC or log backups? Rebooting the machine might cause the problem to go away, but it's not fixing it. It's just removing it until next time. But, if you have active inserts & stuff going on and you try to shut down the server, you could get stuck waiting for those transactions to clear or rollback, or, you might find that you have to commit those transactions as part of recovery when it starts back up. I wouldn't recommend rebooting until you know more.
Jan 28, 2010 at 07:18 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

In addition to Grants 'gold standard' solution do you know if the backup actually completed successfully? Are there any SQL Agent (scheduled) jobs that are still showing as executing?

DBCC can take a long time on a large database but you will need to resolve the other issue before you get DBCC to complete successfully and give you an idea of how long it takes for your system.

more ▼

answered Jan 28, 2010 at 04:32 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

No SQL Agent Jobs are running now and the last good back up I have is the day before tomorrow. Yesterdays backup did not completed successfuly.
Jan 28, 2010 at 05:06 PM DaniSQL
I hope you mean the day before yesterday or we may have identified the problem...
Jan 28, 2010 at 07:19 PM Grant Fritchey ♦♦
5gb is pretty trivial. I'd expect DBCC & backups to be a matter of minutes, probably less than 20 minutes to do both.
Jan 28, 2010 at 07:20 PM Grant Fritchey ♦♦
Im sorry ...I meant the day before yesterday. and YOu are right...backup usually takes 14 min together with other databases.
Jan 28, 2010 at 07:42 PM DaniSQL
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x47

asked: Jan 28, 2010 at 02:53 PM

Seen: 2179 times

Last Updated: Jan 28, 2010 at 02:53 PM