SQL Server time outs

SQL Server time outs

Post by Kaushal Sha » Wed, 20 Nov 2002 10:48:25



Hi,

My name is Kaushal,

I have a strange problem going on with my SQL server.

I have a stored procedure as described below.

Now the real problem is : When I run my application on my
local machine or on on local network, I do not have a
failure message (I delete some folders) even I delete big
folder having 100 sub folders and files of 20Mb.

But If I run the same operation on Internet (production
web site) with a folder having only a single sub-folder
and few Kb files, it fails. Again, It does not fail with
1Mb of files and not have a sub-folder, it failes only it
has a sub-folder.

Can anybody tell me what could be a situation here?

The SP is:

CREATE proc DeleteFileAllRevsInternal


as







--BEGIN TRAN tranDeleteFileAllRevsInternal

-- Get the storage path and prepend it to the server file
name


begin
        raiserror(60017, 1,1) with seterror     --we need
a better error code

end

-- does anyone have it checked out already?

        from FILES

-- If it's checked out, policy is to NOT delete ANY
revisions, and raise
-- an error so DeleteFolder will NOT delete the parent
folder.


begin
        raiserror(60002, 1,1) with seterror     --already
checked-out

end

-- get the total filesize of all revisions


        from FILE_REVISIONS

-- Loop over FILE_REVISIONS to delete them physically
from the server...

declare FileCursor cursor
local
fast_forward
read_only
for select ServerPath
                from FILE_REVISIONS

open FileCursor



begin
--      Delete the file physically on the server.



end

close FileCursor
deallocate FileCursor

delete FILE_AUDITS
        where RevisionID in
        (select FILE_AUDITS.RevisionID from FILE_AUDITS,
FILE_REVISIONS

        and FILE_AUDITS.RevisionID =
FILE_REVISIONS.RevisionID)

-- Delete all FILE_REVISIONS records
delete FILE_REVISIONS


-- Reduce USERS storage by the total deleted amount

exec UpdateStorageUsed


-- delete the FILES record also
delete FILES

COMMIT TRAN tranDeleteFileAllRevsInternal

return 0
GO

 
 
 

SQL Server time outs

Post by Trevor Benedict » Wed, 20 Nov 2002 16:41:37


If I read it correct, you are trying this on a Production WEB SITE. The
resources could be locked by IIS. IF you suspect this is the case then try
to unload the IIS Process that is holding the locks and then try again.

--
Best Regards,

Trevor Benedict R
Microsoft Certified Professional

 
 
 

1. MS SQL Server 2000 time-outs periodicly over the internet

Hi All,

I have recently upgraded to MS SQL 2000 previous version MS SQL 7.0
My previous installation worked fine for about a year.

Now having seemingly the same set-up and copy of database I'm experiencing
periodic time-outs when accessing pages through IIS. IIS uses ODBC to
access SQL server.

I can connect to the server through other means using native drivers
without any problems and ODBC tests are passing also through
ODBC panel in Windows.

I don't know if server will eventually start working without time-outs,
but restarting the server removes the problem for some time, whereas
rebooting the machine fixes it usually for longer.

Any ideas what can be changed , or possible fixes for this.

Regards

Bogdan Fiedur

----------------------------------------------
AdlandPro World's Classifieds
Get Linked from over 7,600 Classifieds with one click.
http://www.adlandpro.com
-------------------------------------------------------

2. DBLibrary problems

3. Looking for Foxpro 2.6 for DOS Professional edition

4. SQL Server ODBC connection time-outs?

5. Multiple results sets

6. Continual time-outs (time is not on my side)

7. RDB info needed --> URGENT

8. Problems with Time Outs

9. Time-outs on only 2 of many stored procedures

10. Time Outs in JDBC

11. Time Outs When running a query

12. Unexpected proxy dial-outs w/ SQL Server 7 EM and MSP 2.0