February 11, 2010 at 11:42 pm
Hi,
I am developing a small app in vb.net which takes the backup of database and then drops it. App works very fine when the database size is small... but if the database size is big (5 GB+) it does not generates backup file. Everything works well, at the end db is dropped.. but I cant see the backup file.. it generats backup file for some time.. but could not complete backup process.. looks like it times out after 10 min (checked in logs)
Below is the code of my SP.. which is running backup command on linked server.
-------------------------------CODE------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spu_gdms_archive_backup]
@BackupType char(1) = 'F'
,@DBName sysname = '*'
,@BackupDir varchar(1024)
,@bkp_file varchar(500)
,@server_name varchar(500)
AS
SET NOCOUNT ON
declare @STR varchar(400)
insert into dbo.t_gdms_archive_tool_logs (log_txt)values ('Starting Backup DB: '+@DBName)
--set @STR= 'backup database ' +@DBname +' to disk = '''+@BackupDir+'\'+@bkp_file+''''
--set @exec_sql = 'Exec ['+@server_name+'].master..sp_defaultdb @loginame='''+@loginname+''', @defdb=''master'''
set @STR = 'Exec ['+@server_name+'].admin.dbo.spu_gdms_archive_start_sls_backup ''F'','''+@dbName+''','''+@BackupDir+''','''+@bkp_file+''''
exec (@str)
--print @STR
insert into dbo.t_gdms_archive_tool_logs (log_txt)values ('Ending Backup DB: '+@DBName)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
-----------------------------------------------
---dbo.spu_gdms_archive_start_sls_backup is alrdy there on the remove machine-------
-----------------------------------------------
Please help me if there is any way to keep this alive.. or is there any way if I call this SP and overwrite the SQL server's configuration for the time being... my SQL server has 600 Sec set for time out of connection...
I dont want to change this number to 0(default)... but want to by pass it till the time my backup sp runs...
Any help will be appriciated... thanks in advance..
February 13, 2010 at 10:54 pm
Why are you using a Linked Server?
Why not use maintenance task or SSIS?
Does it time out when you run your code from Query Analyze ar as a Stored Procedure?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 14, 2010 at 6:45 am
Why don't you use a registered server as opposed to a Linked Server?
Also backing up over the network is very slow.
Are the Database Servers in question on the same Network Segment?
Is there a lot of network traffic?
Do you not have a SAN Attached to the Server or enogh Local Storage to back up the file?
Can't you use disk compression on the backup & then copy & Restore, etc in SSIS, etc?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 14, 2010 at 10:46 am
Thanks Guys for replying..
Here are the answers..
Why are you using a Linked Server?
---because, I have all SPs/Litespeed installed on target server. So, I just call them and it works...
Why not use maintenance task or SSIS?
---In my current setup, SSIS & maintenace tasks are not used. And also, if I call SSIS & MT, it would be tuf for me to write SSIS or MT for every server.. my current setup has more thn 1000+ server.
--------------------------
Why don't you use a registered server as opposed to a Linked Server?
----I havnt try this, will try
Also backing up over the network is very slow.
----yes, but have no choice
Are the Database Servers in question on the same Network Segment?
----yes, the servers are on same network and domain.
Is there a lot of network traffic?
-----No, I did try it in NON Production hrs
Do you not have a SAN Attached to the Server or enogh Local Storage to back up the file?
----Yes SAN is there.. but it fails even when I run on local.
Can't you use disk compression on the backup & then copy & Restore, etc in SSIS, etc?
the SP that I am calling for Backup already performs compression... but I cant use it on local server b'cos database may differ in size...
February 14, 2010 at 1:23 pm
You can use package variables for ech SSIS Package or you could read thru a collection of SQL Servers.
You are using Lightspeed? That has a never nice compression capability.
You can also Zip & Unzip the file.
I have used SSIS extensively to store backups as welll as reshresh Development & QAS Servers from the Production Server.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 15, 2010 at 10:38 pm
-----------
OLE DB provider "SQLNCLI" for linked server "server\instance" returned message "Query timeout expired".
-----------
When I run my SP from QA/Studio Mgt. I get the above error. Now I am sure its because of timeout. I get this error after 10 mins (which is setting on my target linked server).
Now, can some help me and tell me how can I by pass this setting? I want to keep my connection live till the backup finishes... is there anyway to do so?
thanks guys!
February 16, 2010 at 3:46 am
I think the most likely cause is that you have a query timeout set on the linked server properties.
February 17, 2010 at 12:39 am
yes thats true.. this is the cause.. but I dont know the solution of it..
February 17, 2010 at 1:45 am
Please see the below from BOL:
http://msdn.microsoft.com/en-us/library/ms178532.aspx
0 doesn't mean unlimited in the case of query timeout, it means that it's set to the same as the remote query timeout server option - run sp_configure on the server to see what this value is set as (600 seconds by default).
Either amend the serverwide value, or set the individual linked server remote timeout to a suitable value.
February 17, 2010 at 6:30 am
I cant use sp_configure because it will change the server setting which I dont want. Though I can use it and I have full sysadmin access on all servers. Any other way?
Thanks.
February 17, 2010 at 6:38 am
Yes, read the BOL article I referenced to set the query timeout to the required value for each of the linked servers.
February 17, 2010 at 6:45 am
I will try this out. Thanks, will update you...
February 17, 2010 at 9:18 am
set @exec_sql = 'sp_serveroption '''+@server_name+''', ''query timeout '',2147483647'
I made a change in my code and it working fine now. I am assuming that no backup will run for such a long time... 🙂
Thanks for your help guys!!!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply