SQL Connection
-
Hi,I am using my own SP to backup our database. This Sp runs every day at 1 AM. Recently I have noticed that it is not closing the connection even after completing the job successfully. I can see this SPin EM’s process info screen. To investigate this I have used Narayana Vyas Kondreddi’s ShowCodeLine (http://vyaskn.tripod.com/fn_get_sql.htm) Sp. Executing this ShowCodeLine sp, out puts ‘xp_cmdshell’. Which tell me that sql server executing xp_cmdshell under that SPID. But my job history show that the job completed successfully.If it is still executing how come the job completed successfully? . I tried to kill that process, but still I can see it in the Process info screen, but the command column shows killed/rollbackI have searched the BOL to learn more about process info screen, its column and their values.How can I kill that SPID, where can I get more details about the process info screen and the columns?My SP--EXEC FULL_BACKUP_SPALTER PROC FULL_BACKUP_SPASBEGIN/************************************************************************//* NAME:FULL_BACKUP_SP *//* AUTHOR:VINU THOMAS *//* DATE:25 AUG 2006 *//**//* DISCRIPTION: *//* THIS SP IS RESPONSABLE FOR DOING THE FULL BACKUPS OF THE *//* DATABASE ON THIS SERVER *//************************************************************************/DECLARE @DB_TO_BACKUP_CNT INTDECLARE @LOOP_COUNT_I INTDECLARE @BKUP_STRING VARCHAR(500)DECLARE@VCHCOMMAND VARCHAR(500)DECLARE @IRESULT INTDECLARE @DB_BACKUP_NAME VARCHAR(200)DECLARE @MSG VARCHAR(8000)DECLARE @BACKUP_START_TIME DATETIMEDECLARE @DID INTDECLARE @TOT_FILE_2_SEC INTDECLARE @FILE_TO_DELETE VARCHAR(300)DECLARE @BACKUP_STATUS INTDECLARE @EMAL_QUERY VARCHAR(1000)DECLARE @MEDIA_SET_ID BIGINTDECLARE @BACKUP_NAME VARCHAR(100)DECLARE @DB_NAME VARCHAR(100)DECLARE @PRIMARY_LOCATION VARCHAR(200)DECLARE @SECONDARY_LOCATION VARCHAR(200)DECLARE @SCHEDULE INTIF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[TEMPDB].[DBO].[##DB_BACKUP_LOG_T]') )DROP TABLE [DBO].[##DB_BACKUP_LOG_T]CREATE TABLE ##DB_BACKUP_LOG_T(DBNAME VARCHAR(100),STATUS INT)SET @LOOP_COUNT_I=1SET @MSG=''/*DISCONNECT THE Z DRIVE FIRST*/SELECT @VCHCOMMAND = 'NET USE Z: /DELETE 'EXEC @IRESULT = MASTER..XP_CMDSHELL @VCHCOMMAND, NO_OUTPUT/*SELECT @VCHCOMMAND = 'NET USE Y: /DELETE 'EXEC @IRESULT = MASTER..XP_CMDSHELL @VCHCOMMAND, NO_OUTPUT*//*MAP THE Z DRIVE */SELECT @VCHCOMMAND = 'NET USE Z: \\SRSQL\L$ 'EXEC @IRESULT = MASTER..XP_CMDSHELL @VCHCOMMAND/*MAP THE Z DRIVE(SECONDARY LOCATION) *//*SELECT @VCHCOMMAND = 'NET USE Y: \\srexch\l$ 'EXEC @IRESULT = MASTER..XP_CMDSHELL @VCHCOMMAND**//**STEP 1: GET THE COUNT OF RECOEDS IN DB_BACKUP_SETTINGS_T TABLE**/SELECT @DB_TO_BACKUP_CNT=MAX(DBBACKUPSCHEDULEID) FROM DB_BACKUP_SETTINGS_T/**STEP 2: LOOP THROUGH DB_BACKUP_SETTINGS_T TABLE*/WHILE @LOOP_COUNT_I <= @DB_TO_BACKUP_CNTBEGIN/**SETP 2.1 : CHECK WHETHER VALUE OF @LOOP_COUNT_I EXIST IN TABLE OR NOT**/IF EXISTS(SELECT DBBACKUPSCHEDULEID FROM DB_BACKUP_SETTINGS_T WHERE DBBACKUPSCHEDULEID=@LOOP_COUNT_I)BEGINSELECT @DB_NAME=BSET.DBNAME,@SCHEDULE=SCHEDULE,@PRIMARY_LOCATION=PRIMARYLOCATION,@SECONDARY_LOCATION=SECONDARYLOCATIONFROM DB_BACKUP_SETTINGS_T BSET INNER JOIN MASTER..SYSDATABASES SYSDB ON SYSDB.DBID=BSET.DBIDWHERE DBBACKUPSCHEDULEID= @LOOP_COUNT_I/**SETP 2.1.1 MAKE SURE THE PRIMARY BACKUP DIRECTORY EXIST OR NOT**/SELECT @VCHCOMMAND = 'DIR ' + @PRIMARY_LOCATION + '\' + @DB_NAMEEXEC @IRESULT = MASTER..XP_CMDSHELL @VCHCOMMAND, NO_OUTPUT IF @IRESULT <> 0 BEGIN SELECT @VCHCOMMAND = 'MKDIR ' + @PRIMARY_LOCATION + '\' + @DB_NAME EXEC MASTER.DBO.XP_CMDSHELL @VCHCOMMAND, NO_OUTPUT ENDSET @DB_BACKUP_NAME=@DB_NAME/**SETP 2.1.3 : CHECK WHETHER THE DATABASE SCHEDULE FOR WEEKLY SUNDAY BACKUP OR NOT**/IF @SCHEDULE=0 AND DATENAME(DW,GETDATE())='SUNDAY'BEGINSET @DB_BACKUP_NAME= @DB_BACKUP_NAME + '_WEEKLY_FULL_' ENDIF @SCHEDULE=1 AND DATENAME(DW,GETDATE())<>'SUNDAY'BEGINSET @DB_BACKUP_NAME= @DB_BACKUP_NAME + '_FULL_' END/**SETP 2.1.4 : MAKE THE BACKUP NAME**/SET @DB_BACKUP_NAME= @DB_BACKUP_NAME + REPLACE(CONVERT(VARCHAR(12),GETDATE(),113),' ','_' )SET @DB_BACKUP_NAME=@DB_BACKUP_NAME + REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),':','') +'_'+ RIGHT(CONVERT(VARCHAR,GETDATE(),109),2)/**SETP 2.1.4 : BACKUP SQL STRING**/SET @BKUP_STRING='BACKUP DATABASE ['SET @BKUP_STRING=@BKUP_STRING + @DB_NAME + '] TO DISK=''' + @PRIMARY_LOCATION SET @BKUP_STRING=@BKUP_STRING + '\' + @DB_NAME + '\' + @DB_BACKUP_NAME + '.BAK' +'''' + ' WITH NOINIT'SET @BKUP_STRING=@BKUP_STRING + ',Name=''' + @DB_BACKUP_NAME + ''''/**SETP 2.1.3 : CHECK WHETHER THE DATABASE SCHEDULE FOR WEEKLY SUNDAY BACKUP OR NOT**/IF @SCHEDULE=0 AND DATENAME(DW,GETDATE())='SUNDAY'BEGINSET @BACKUP_START_TIME=GETDATE()EXEC(@BKUP_STRING)IF @@ERROR<>0BEGINSET @BACKUP_STATUS=0ENDELSEBEGINSET @BACKUP_STATUS=1ENDPRINT @BKUP_STRINGINSERT INTO ##DB_BACKUP_LOG_T(DBNAME ,STATUS )VALUES(@DB_NAME,@BACKUP_STATUS)ENDIF @SCHEDULE=1 BEGINSET @BACKUP_START_TIME=GETDATE()EXEC(@BKUP_STRING)IF @@ERROR<>0BEGINSET @BACKUP_STATUS=0 --FAILEDENDELSEBEGINSET @BACKUP_STATUS=1ENDPRINT @BKUP_STRINGINSERT INTO ##DB_BACKUP_LOG_T(DBNAME ,STATUS )VALUES(@DB_NAME,@BACKUP_STATUS)END/**SETP 2.1.4 : DELETE OLD BACKUPS**/SELECT TOP 1 @FILE_TO_DELETE ='DEL ' + BFM.PHYSICAL_DEVICE_NAMEFROM MSDB.DBO.BACKUPSET BSETINNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY BFM ON BSET.MEDIA_SET_ID = BFM.MEDIA_SET_IDWHERE BSET.DATABASE_NAME =@DB_NAME AND CONVERT(VARCHAR(10),BACKUP_FINISH_DATE,103) <= CONVERT(VARCHAR(10),DATEADD(D,-3,GETDATE()),103)AND DEVICE_TYPE=2 AND TYPE='D'AND BACKUP_START_DATE > '13 SEP 2006 07:30:00'--LIVE DATEORDER BY BFM.MEDIA_SET_ID DESCIF LEN(@FILE_TO_DELETE)>0BEGINprint @FILE_TO_DELETE /*DELETE FROM PRIMARY LOCATION*/EXEC MASTER.DBO.XP_CMDSHELL @FILE_TO_DELETE /*DELETE FROM SECONDARY LOCATION*/--SELECT @FILE_TO_DELETE = 'DEL ' + @PRIMARY_LOCATION + '\' + @DB_NAME + '\' + @BACKUP_NAME --EXEC MASTER.DBO.XP_CMDSHELL @FILE_TO_DELETE ENDSET @FILE_TO_DELETE = ''SET @BACKUP_NAME=''END /**SETP 2.1**/SET @LOOP_COUNT_I=@LOOP_COUNT_I+1END/**SETP 2**//**STEP 3: SEND EMAIL*/SET @EMAL_QUERY='SELECT CAST(DBNAME AS VARCHAR(20))DBNAME, 'SET @EMAL_QUERY=@EMAL_QUERY + ' CAST(CAST((BSET.backup_SIZE/ 1048576.00) AS NUMERIC(7,2)) AS VARCHAR) + '' MB'' SIZE, 'SET @EMAL_QUERY=@EMAL_QUERY + ' CASE WHEN STATUS=0 THEN ''FAILED'' 'SET @EMAL_QUERY=@EMAL_QUERY + ' WHEN STATUS=1 THEN ''SUCCESSFUL '' 'SET @EMAL_QUERY=@EMAL_QUERY + ' END AS STATUS 'SET @EMAL_QUERY=@EMAL_QUERY + ' FROM ##DB_BACKUP_LOG_T DBL 'SET @EMAL_QUERY=@EMAL_QUERY + ' left JOIN 'SET @EMAL_QUERY=@EMAL_QUERY + 'MSDB.DBO.BACKUPSET BSET ON DBL.DBNAME=BSET.DATABASE_NAME 'SET @EMAL_QUERY=@EMAL_QUERY + ' WHERE CONVERT(VARCHAR(10),backup_start_date,103)=CONVERT(VARCHAR(10),GETDATE(),103) AND BSET.TYPE=''D'' 'EXEC MASTER..XP_SENDMAIL @RECIPIENTS = 'VINU.THOMAS;Darren.Kenny', @query=@EMAL_QUERY, @SUBJECT = 'MEDICAL DATABASE BACKUP',@WIDTH=500DROP TABLE ##DB_BACKUP_LOG_T/*DISCONNECT THE Z DRIVE FIRST*/SELECT @VCHCOMMAND = 'NET USE Z: /DELETE 'EXEC @IRESULT = MASTER..XP_CMDSHELL @VCHCOMMAND, NO_OUTPUT/*SELECT @VCHCOMMAND = 'NET USE Y: /DELETE 'EXEC @IRESULT = MASTER..XP_CMDSHELL @VCHCOMMAND, NO_OUTPUT*/ENDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
-
now this one is nice to read
imo switch your sql-agent service account to a windows account (grant the account the needed rights to the fileshares)
and search SSC for a simple "generate backups for all db" script.
Then create a job with in the first step the execute of the "generate.."
and in a second step put an x-copy to your fileshare (you can use unc ! )
You can also have your mails sent using separate jobsteps.
(btw I'd use smtp-mail ... find the prod at sqldev.net)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply