Disk filling up

Question

Post reply

Disk filling up

SusV08

Newbie

Points: 3
More actions
April 3, 2008 at 11:21 am

#123273

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

shivaram challa SSCrazy Points: 2415 More actions · Answer 1

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]

SusV08 Newbie Points: 3 More actions · Answer 2

SusV08

Newbie

Points: 3

April 3, 2008 at 11:43 am

#797985

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

Thanks

Su

SusV08 Newbie Points: 3 More actions · Answer 3

SusV08

Newbie

Points: 3

April 3, 2008 at 11:47 am

#797986

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

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 4

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

shivaram challa SSCrazy Points: 2415 More actions · Answer 5

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]

krammana Mr or Mrs. 500 Points: 546 More actions · Answer 6

krammana

Mr or Mrs. 500

Points: 546

April 4, 2008 at 7:19 am

#798414

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.