Time Out Problem restoring a 2gb database HELP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Time Out Problem restoring a 2gb database HELP

Author

Topic

Capt_Ron
Starting Member

45 Posts

Posted - 2005-08-17 : 10:50:24

I have a Stored Procedure the restores a database. It works fine on smaller databases, but it times out when used on larger databases( 2GB). Is there anything I can do in the SQL that extends the timeout?

THanks
Ron

Kristen
Test

22859 Posts

Posted - 2005-08-17 : 11:13:06

What are you running the SQL from? ASP or somesuch? if so the timeout is a limitation of the ADO layer, or application, on the client.

Query Analyser, for example, won't time out [unless you change the options]

Kristen

Capt_Ron
Starting Member

45 Posts

Posted - 2005-08-17 : 11:15:54

I'm running it from VB.NET. I have a Connect Timeout=600 in my connection string, but it times out after 20 seconds or so.
Ron

Kristen
Test

22859 Posts

Posted - 2005-08-17 : 11:17:33

As best I can judge it the timeout is coming at the VB.NET end.

Perhaps there's something you need to put in your connection object, as well as your connection string.

Mind you ... 20 seconds is pretty short for a default.

Kristen

Capt_Ron
Starting Member

45 Posts

Posted - 2005-08-17 : 11:20:29

Yeah,
I have the same question posted in a VB.NET forum. Maybe someone there can shed some light.

Thank you for your help. I really appreciate it.
Ron

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-08-17 : 11:50:27

Set the timeout to 0....(ie run forever)

Capt_Ron
Starting Member

45 Posts

Posted - 2005-08-17 : 13:24:26

nothing has worked. I keep getting a timeout error. Its like my db connection loses connection during the sql.

Kristen
Test

22859 Posts

Posted - 2005-08-17 : 13:48:18

This is what we use in ASP (in addition to whatever is in the Connection String I suppose)

Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.CommandTimeout = 600

Kristen

Capt_Ron
Starting Member

45 Posts

Posted - 2005-08-17 : 15:05:17

Still no dice. AARGH!!!

BTW. The SP I'm using is based on that SQL you gave me Kristen. I had to make a few adjustments so it would work as a SP and accept parameters, but the core is the same.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52229

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-17 : 15:06:57

Ron,

Do you have a Connection Timeout option set in your connection string? If so, what is its value. If not, you should add one. This one is for connecting to the server. CommandTimeout is for the query.

Tara

Capt_Ron
Starting Member

45 Posts

Posted - 2005-08-17 : 15:08:02

Here is the SP. Maybe it's something I'm doing in here?


CREATE PROCEDURE ws_LY       
@RestoreFile nvarchar(100), @DB nvarchar(50), @MDF nvarchar(30), @LDF nvarchar(30)      
AS      
DECLARE @SQL nvarchar(1000)      
SELECT @SQL = 'RESTORE FILELISTONLY FROM DISK = ' + '''' + @RestoreFile + ''''      
SELECT @SQL = @SQL + ' RESTORE DATABASE  ' + @DB      
SELECT @SQL = @SQL + ' FROM DISK = ''' + @RestoreFile + ''''      
SELECT @SQL = @SQL + ' WITH REPLACE, RECOVERY, STATS = 10, '      
SELECT @SQL = @SQL + ' MOVE '+''''+'database'+''''+' TO '+ '''' +'C:\Program Files\Microsoft SQL Server\MSSQL\Data\' + @MDF + '.mdf''' + ',  '      
SELECT @SQL = @SQL + ' MOVE '+''''+'database_log'+''''+' TO '+'''' +'C:\Program Files\Microsoft SQL Server\MSSQL\Data\' + @LDF + '.ldf'''      
SELECT @SQL = @SQL + ' ALTER DATABASE ' + @DB + ' MODIFY FILE (NAME = ''database'', NEWNAME = ''' + @MDF + ''')'      
SELECT @SQL = @SQL + ' ALTER DATABASE ' + @DB + ' MODIFY FILE (NAME = ''database_log'', NEWNAME = ''' + @LDF +''')'       
EXEC  sp_executesql @SQL      

GO

Ron

Capt_Ron
Starting Member

45 Posts

Posted - 2005-08-17 : 15:09:16

Tara,
I have a Connect Timeout=0 in my connection string. I popped a message box up before the SQL runs to confirm it is set to 0.
Ron

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-17 : 15:12:20

Shouldn't it be Connection Timeout = 0? When you step through the code in Visual Studio or whatever you are using, where is the timeout occurring, what is the code on that line?

Tara

Capt_Ron
Starting Member

45 Posts

Posted - 2005-08-17 : 15:14:56

Here is the whole line:


Dim strConnString As String = "Password=" & strPW & ";User ID=" & strUN & ";Persist Security Info=True;Initial Catalog=master;Data Source=" & strSRV & ";Connect Timeout=0"

Ron

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-17 : 15:18:42

In my app.config (I use that instead of hard coding the information in the app), I have Connection Timeout and not Connect Timeout. Try the word Connection spelled out.

Also, step through your code and post the line of code where the timeout is occurring.

Tara

Capt_Ron
Starting Member

45 Posts

Posted - 2005-08-17 : 15:23:33

Here's the section of code that runs the SP:


        ' Declare SQL Statement to run
        Dim strSQL As String = "EXEC ws_LY "
        strSQL = strSQL & "'" & strFileName & "', "
        strSQL = strSQL & "'" & strDB & "', "
        strSQL = strSQL & "'" & strDataFile & "', "
        strSQL = strSQL & "'" & strLogFile & "'"
        ' Open Master Database and run WS_LY Stored Procedure
        Dim strConnString As String = "Password=" & strPW & ";User ID=" & strUN & ";Persist Security Info=True;Initial Catalog=master;Data Source=" & strSRV & ";Connection Timeout=0"
        objsql.ConnString = strConnString
        'objsql.openDB()
        Dim sqlConn As New SqlConnection(objsql.ConnString)
        'MessageBox.Show("Tmieout= " & sqlConn.ConnectionTimeout)
        '*****
        Dim cmd As New SqlCommand(strSQL, sqlConn)
        Try
            'openDB()
            sqlConn.Open()
            cmd.CommandTimeout = 0
            cmd.ExecuteNonQuery() 'ERROR IS HAPPENING HERE!!!!
            MessageBox.Show("Database has been restored!", "Restore Database", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show("Error Restoring Database." & ex.Source & " : : " & ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            'closeDB()
            sqlConn.Close()
            sqlConn.Dispose()
            sqlConn = Nothing
        End Try

Disregard the OBJSQL references. I have a custom SQL object that I use. I coded it the long way here to make sure it wasn't my object that was causing it.

Thanks
Ron

Capt_Ron
Starting Member

45 Posts

Posted - 2005-08-17 : 15:27:51

OH. I forgot to mention. I changed the Connect Timeout to Connection Timeout

Did not work.
grrrrrrrrrrrrrr........
Ron

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-17 : 15:29:48

Could you post cmd.CommandText?

I don't think that you want RESTORE FILELISTONLY in your stored procedure as that will produce a result set which you aren't getting since you are using cmd.ExecuteNonQuery().

Tara

Capt_Ron
Starting Member

45 Posts

Posted - 2005-08-17 : 15:58:55

Command Text is: EXEC ws_LY 'c:\backup.bak', 'wsly1', 'wsly_data', 'wsly_log'

Which looks to be correct.

"I don't think that you want RESTORE FILELISTONLY in your stored procedure "
What should I use?

Ron

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-17 : 16:02:13

Just remove RESTORE FILELISTONLY as you aren't using the result set. You only need the RESTORE DATABASE part in there as you already know what to put for the WITH MOVE option.


CREATE PROCEDURE ws_LY       
@RestoreFile nvarchar(100), @DB nvarchar(50), @MDF nvarchar(30), @LDF nvarchar(30)      
AS      
DECLARE @SQL nvarchar(1000)      
SELECT @SQL = 'RESTORE DATABASE  ' + @DB      
SELECT @SQL = @SQL + ' FROM DISK = ''' + @RestoreFile + ''''      
SELECT @SQL = @SQL + ' WITH REPLACE, RECOVERY, STATS = 10, '      
SELECT @SQL = @SQL + ' MOVE '+''''+'database'+''''+' TO '+ '''' +'C:\Program Files\Microsoft SQL Server\MSSQL\Data\' + @MDF + '.mdf''' + ',  '      
SELECT @SQL = @SQL + ' MOVE '+''''+'database_log'+''''+' TO '+'''' +'C:\Program Files\Microsoft SQL Server\MSSQL\Data\' + @LDF + '.ldf'''      
SELECT @SQL = @SQL + ' ALTER DATABASE ' + @DB + ' MODIFY FILE (NAME = ''database'', NEWNAME = ''' + @MDF + ''')'      
SELECT @SQL = @SQL + ' ALTER DATABASE ' + @DB + ' MODIFY FILE (NAME = ''database_log'', NEWNAME = ''' + @LDF +''')'       
EXEC  sp_executesql @SQL      

GO

When you run EXEC ws_LY 'c:\backup.bak', 'wsly1', 'wsly_data', 'wsly_log'
does it work without error in Query Analyzer? If so, how long did it take to execute?

Tara

Capt_Ron
Starting Member

45 Posts

Posted - 2005-08-17 : 16:13:35

Tara,
I'm testing it now.
and Yes I've run the command in QA and it works. It only takes a minute or two to restore the 2GB file.
Ron

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources