Disk filling up

  • 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

  • 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]

  • Thank you for the reply. Could you please explain what is ssis?

    Thanks

    Su

  • 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

  • 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

  • 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]

  • 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