November 22, 2010 at 10:10 am
Hi guys, I need a bit of help with this one.
We have a job which runs every 15min just after our log backups that zips it up and copies the zip file to another directory. The 7zip process fails every now and again and produces a 22 byte zip file which is then copied to other directory which means the restore process at the other office fails.
My manager has asked me to write a script and add it as an extra step in the job that runs just after the zip process and looks at the directory to find any 22b files, delete them and rerun the zip process.
What I'm looking for is a way to look at files in a directory with a specific size and delete them, anyone ever done this?
November 22, 2010 at 10:16 am
here's a start
IF OBJECT_ID('tempdb..#Out') > 0
DROP TABLE #Out
CREATE TABLE #Out (output VARCHAR(MAX))
INSERT INTO #Out (output)
--exec xp_cmdshell 'dir \\whatever\backup'
exec xp_cmdshell 'dir z:'
--you may test if adding a leading space after the first % helps filter even better.
SELECT * FROM #Out WHERE output like '%22 %filename.7zip%'
--validate if something went wrong
if 1=0
exec xp_cmdshell 'RM filename.7zip'
DROP TABLE #Out
November 23, 2010 at 2:34 am
Awesone, this will work great. Thanks!:-D
It's so simple can't believe I didn't think of it, doh!
November 23, 2010 at 5:23 am
Yup, good old dos. Still works better than windows in some area!!
November 23, 2010 at 8:10 am
Indeed! Thought I'd share the final script...
IF OBJECT_ID('tempdb..#Out') > 0
DROP TABLE #Out
-- Creates temp table and inserts diectory information.
CREATE TABLE #Out (output VARCHAR(1000))
INSERT INTO #Out (output)
exec xp_cmdshell 'dir Z:'
-- Count 22 byte records.
DECLARE @result int
SET @result = (SELECT count(*) FROM #Out WHERE right(output,42) like ' 22 %LOG%.zip%')
IF @result = 0
print 'No 22 byte files'
IF @result = 1
BEGIN
DECLARE @file varchar(150)
DECLARE @del_cmd varchar(150)
-- Gets file name from directory.
SET @file = (SELECT right(output,38) FROM #Out WHERE right(output,42) like ' 22 %LOG%.zip%')
SET @del_cmd = 'Del Z:' + @file
-- Deletes file and re-runs zip step in job
exec xp_cmdshell @del_cmd
exec msdb..sp_start_job 'ZIP SYNC',NULL,NULL,'Zip Logs'
END
IF @result > 1
BEGIN
-- Raises error if more that one 22byte file exists.
RAISERROR ('More than one 22 byte file, DBAs investigate',16, 1 );
END
DROP TABLE #Out
November 23, 2010 at 8:23 am
I'm not a big fan of the RIGHT(output, 38), That script will break if the filename ever changes.
I know it's more complex but I preffer to figuring out the delimiters before and after the filename and using that.
Then it's really fire and forget.
Here's a script where I use that strategy :
IF OBJECT_ID('tempdb..#Out') > 0
DROP TABLE #Out
CREATE TABLE #Out (output VARCHAR(MAX))
INSERT INTO #Out (output)
--exec xp_cmdshell 'dir \\Fordiavcenter41\backup'
exec xp_cmdshell 'dir z:'
--SELECT * FROM #Out
DELETE FROM #Out WHERE (output NOT LIKE '%bytes free%' AND output NOT LIKE '%PROD-FORDIA-FULL%') OR output IS NULL
UPDATE #Out SET output = REPLACE(REPLACE(REPLACE(REPLACE(output, ' ', ''), ',', ''), 'PROD-FORDIA-FULL', 'bytes'), 'AM', ')')
--SELECT * FROM #Out
SELECT LastBackupSize_GB, Free_Space_GB, Free_Space_GB - LastBackupSize_GB * 1 AS Free_Space_After_1_More_Backups FROM (
SELECT MAX(CASE WHEN Data = 'BAK' THEN gigabytes ELSE NULL END) AS LastBackupSize_GB, MAX(CASE WHEN Data = 'FREE' THEN gigabytes ELSE NULL END) AS Free_Space_GB FROM (
SELECT CASE WHEN output LIKE '%.bak%' THEN 'BAK' ELSE 'FREE' END AS Data,
CONVERT(DECIMAL(18,2), CONVERT(BIGINT, SUBSTRING(output, CHARINDEX(')', output) + 1, (CHARINDEX('bytes', output) - 1) - CHARINDEX(')', output))) / 1024.0 / 1024 / 1024) as gigabytes FROM #Out
) dta
) dtFreeSpace
DROP TABLE #Out
--107301889536
--exec xp_fixeddrives
LastBackupSize_GB Free_Space_GB Free_Space_After_1_More_Backups
--------------------------------------- --------------------------------------- ---------------------------------------
14.91 66.04 51.13
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply