November 13, 2007 at 9:56 pm
Hi,
I am getting the following error while executing a stored procedure
Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
this server is having lot of Db's
and it is taking some time,
could you please help me out
-----------------------------------------------
USE [master]
GO
/****** Object: StoredProcedure [dbo].[RL_ERC_DBA_Grant_DB_INTACT] Script Date: 11/13/2007 01:48:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[RL_ERC_DBA_Grant_DB_INTACT] as
set nocount on
Declare @cmd1 varchar(250),@cmd2 varchar(250)
set @cmd1='osql -SUSXS4001 -E -dmaster'
set @cmd1=@cmd1+' -i"D:\zDBA_sql_scripts\intact_DBA_grant_db_owner.sql"'
set @cmd1=@cmd1+' -o"D:\zDBA_sql_scripts\intact_DBA_grant_db_owner_out.sql"'
set @cmd1=@cmd1+' -w1024 -n'
print @cmd1
--EXEC master..xp_cmdshell @cmd1
set @cmd2='osql -SUSXS4001 -E -dmaster'
set @cmd2=@cmd2+' -i"D:\zDBA_sql_scripts\intact_DBA_grant_db_owner_out.sql"'
print @cmd2
--EXEC master..xp_cmdshell @cmd2
---------------------
delete from [zDBA_Admin_Risk_Link].[dbo].[Grant_DB_Owner_to_New_DB_01]
-----------------------------
DECLARE @v_DB VARCHAR(128),
@v_Start_Date_Hold datetime
SET @v_Start_Date_Hold = getdate()
Insert into [zDBA_Admin_Risk_Link].[dbo].[Grant_DB_Owner_to_New_DB_01]
select db.name, db.crdate
from master.dbo.sysdatabases db
where db.name not in
('master','model','msdb','tempdb','Northwind','Pubs','UWC_ERC')
and DATEDIFF ( mi , crdate , @v_Start_Date_Hold ) < 10
order by 1
------------------------------------------------
delete FROM [zDBA_Admin_Risk_Link].[dbo].[Grant_DB_Owner_to_New_DB_02];
INSERT INTO [zDBA_Admin_Risk_Link].[dbo].[Grant_DB_Owner_to_New_DB_02]([x_seq_num], [x_cmd])
VALUES(0,' go ')
-----------------------------------------------------
exec sp_ERC_DBA_Grant_DB_Owner
-----------------------------------
EXEC master..xp_cmdshell @cmd1,no_output
EXEC master..xp_cmdshell @cmd2,no_output
set nocount off
--------------------------------------------------------------------------
Regards,
Ezhilan
November 13, 2007 at 10:39 pm
It would help if you could post the procedures code. It's hard to understand what is causing the timeout without it.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 13, 2007 at 10:59 pm
Since you saY THAT there are lot of databases. check if they are being heavily used. also chjeck for the RAM and other hardware performances.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 13, 2007 at 11:11 pm
USE [master]
GO
/****** Object: StoredProcedure [dbo].[RL_ERC_DBA_Grant_DB_INTACT] Script Date: 11/13/2007 01:48:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[RL_ERC_DBA_Grant_DB_INTACT] as
set nocount on
Declare @cmd1 varchar(250),@cmd2 varchar(250)
set @cmd1='osql -SUSXS4001 -E -dmaster'
set @cmd1=@cmd1+' -i"D:\zDBA_sql_scripts\intact_DBA_grant_db_owner.sql"'
set @cmd1=@cmd1+' -o"D:\zDBA_sql_scripts\intact_DBA_grant_db_owner_out.sql"'
set @cmd1=@cmd1+' -w1024 -n'
print @cmd1
--EXEC master..xp_cmdshell @cmd1
set @cmd2='osql -SUSXS4001 -E -dmaster'
set @cmd2=@cmd2+' -i"D:\zDBA_sql_scripts\intact_DBA_grant_db_owner_out.sql"'
print @cmd2
--EXEC master..xp_cmdshell @cmd2
---------------------
delete from [zDBA_Admin_Risk_Link].[dbo].[Grant_DB_Owner_to_New_DB_01]
-----------------------------
DECLARE @v_DB VARCHAR(128),
@v_Start_Date_Hold datetime
SET @v_Start_Date_Hold = getdate()
Insert into [zDBA_Admin_Risk_Link].[dbo].[Grant_DB_Owner_to_New_DB_01]
select db.name, db.crdate
from master.dbo.sysdatabases db
where db.name not in
('master','model','msdb','tempdb','Northwind','Pubs','UWC_ERC')
and DATEDIFF ( mi , crdate , @v_Start_Date_Hold ) < 10
order by 1
------------------------------------------------
delete FROM [zDBA_Admin_Risk_Link].[dbo].[Grant_DB_Owner_to_New_DB_02];
INSERT INTO [zDBA_Admin_Risk_Link].[dbo].[Grant_DB_Owner_to_New_DB_02]([x_seq_num], [x_cmd])
VALUES(0,' go ')
-----------------------------------------------------
exec sp_ERC_DBA_Grant_DB_Owner
-----------------------------------
EXEC master..xp_cmdshell @cmd1,no_output
EXEC master..xp_cmdshell @cmd2,no_output
set nocount off
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply