April 3, 2008 at 11:21 am
Hello,
I have the following backup plan for a small database 2GB.
Recovery model - full
Full DB backup every day at specified time
Transaction log backup every hour.
Transactiong log file is set to auto grow.
I keep to backupsets say, Backup1 and backup2.
Full database backup uses WITH FORMAT option.
log backup doesn't use any init.
At the end of 24 hour I switch to the other backup set with a full DB backup.
I do delete the backuphistory every 30 days using sp_delete_backuphistory.
Now, I see that the disk is filling up fast.
The system as well as application mdf and ldf file size seem to be okay.
I did dbcc sqlperf(logspace).
That seems to be okay.
What could be the problem. Please let me know if anyone has any ideas.
Thanks
Su
April 3, 2008 at 11:36 am
sp_delete_backuphistory deletes the history in the systables but doesnt the physical files.
you have to use maintenance plan (ssis provides an option to delete old back up files).
Other wise, you might have create a batch script that will run everytime you need the clean up done.
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 3, 2008 at 11:43 am
Thank you for the reply. Could you please explain what is ssis?
Thanks
Su
April 3, 2008 at 11:47 am
Shiva,
I forgot to mention that I am using msde2000. Can I use DBCC, or any other stored procedures to delete the backupfiles?
When you say backupfiles, did you mean the backupset? I write to the same file repeatedly.
Thanks
Su
April 3, 2008 at 11:49 am
You said you don't INIT the transaction log backups so I'd suspect that that is what's continuing to grow.
Or, maybe the problems outside of SQL and files are being copied to your server.
Logparser is a handy utility for getting a list of files in a SQL like way.
For example, to find the top 10 largest files on your drive:
LogParser "SELECT TOP 10 Path, Name, Size FROM C:\*.* ORDER BY Size DESC" -i:FS
April 3, 2008 at 12:07 pm
ssis = SQL Server integration services, available in SQL Server 2005.
this wouldnt help you for msde.
This is the method I use in situations I cant use SSIS or any other tool. There could be some other way of doing this, but suits my needs perfectly.
I have downloaded this below script from web and modified it to my needs a while ago. you can use it to delete older files
' VBScript source code
' Author: SChalla
' Path: C:\VBScript\DeleteBackupFilesByAge.vbs
' Date: 01/24/2007
' Purpose: To delete all the files (backup) older than 14 days from the Archive disk
'==================================================================================='
Option Explicit
' All these commented out echos are for debugging'
WScript.Echo "->>FileDeletingscriptstarted(" & NOW() & ")..."
Dim objFSO, objFile, objFolder
Dim LastModified, File
dim NumFilesDeleted
NumFilesDeleted = 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const Path = "R:\SQLBackups\"
Const AgeLimit = 14 'How old the file should be, before its Deleted?
'------------------------------------------------------
' WScript.Echo ("Check if the directory exists.")
'------------------------------------------------------
If Not objFSO.FolderExists(Path) Then
WScript.Echo " - The folder """ & Path & """ does not exist", 0, "Missing folder."
WScript.quit
else
WScript.Echo " - """ & Path & """ directory exists."
End If
'--------------------------------
' WScript.Echo "Delete old files"
'--------------------------------
Set objFolder = objFSO.GetFolder(Path)
'wscript.echo "FolderName: " & objFolder.name
WScript.Echo (">>DELETING:")
For Each File In objFolder.Files
LastModified = File.DateLastModified
If DateDiff ("D", LastModified, Now) > AgeLimit Then
WScript.Echo ("-" & Path & File.Name)
objFSO.DeleteFile(Path & File.Name)
NumFilesDeleted = NumFilesDeleted + 1
' Else
' WScript.Echo " - Files " & File.Name & " is not older than " & AgeLimit & " days."
End If
Next
WScript.Echo " - Total Number of Files Deleted:" & NumFilesDeleted &"."
WScript.Echo " - FileDeletingScriptEnded("& NOW() &")<<- "
to use it, you have to create a .vbs file with this script and call it like this in the command prompt.
CScript.exe C:\VBScript\DeleteBackupFilesByAge.vbs
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 4, 2008 at 7:19 am
Goto Management Studio, Management folder....New Maintenance Plan...Give name as "Cleanup Backup" Choose "Maintenance Cleanup Task" and configure that task by providing "backup folder path" and "file extension"..
That will do it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply