Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    49

    Unanswered: SQL Backup continuosly grows and fills up drive issue

    I currently have a SQL backup process that backs up my databases via the network to a backup hard drive on a separate system. I recently began getting strange issues with my backup process in which it continually writes to the backup drive until the drive fills up and then the job fails. I also noticed that when I kill the job on the host server, the backup file drops to the normal file size. The normal file size is 300 GB but it has grown to over 400GB. I looked at various logs and even performed several backup tests with success.

    I am trying to figure out if this is a known SQL Server issue or an issue with the OS?

    specs:

    windows 64bit/sql server 2k5 64bit

    Thanks in advance for the help.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    please post the sql script that you are using to backup the database. IT sounds like the backup is appending to the file and not initializing the disk space.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Sep 2003
    Posts
    49
    -- This deletes any backup files in the backup directory.
    DECLARE @dt DATETIME;
    SELECT @dt = GETDATE();
    EXECUTE master.dbo.xp_delete_file 0,N'\\test\Backups$\User\Test',N'bak',@dt

    -----

    -- This backs up the database.
    BACKUP DATABASE test TO DISK = '\\test\Backups$\User\Test\test.bak' WITH STATS

    Thanks for the help.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    xp_delete_file is not a sql server standard xp. what vendor's product are you using?

    have you execute each step to ensure that all the backup files have been deleted?


    Will this work:

    Code:
    BACKUP DATABASE test TO DISK = '\\test\Backups$\User\Test\test.bak' WITH STATS, INIT

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    if you want to overwrite the file each time, you should add INIT to the options. If you run this command:
    Code:
    restore headeronly from DISK = '\\test\Backups$\User\Test\test.bak'
    you will probably see every backup you have taken since the dawn of time.

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by svanati
    -- This deletes any backup files in the backup directory.
    DECLARE @dt DATETIME;
    SELECT @dt = GETDATE();
    EXECUTE master.dbo.xp_delete_file 0,N'\\test\Backups$\User\Test',N'bak',@dt

    -----

    -- This backs up the database.
    BACKUP DATABASE test TO DISK = '\\test\Backups$\User\Test\test.bak' WITH STATS

    Thanks for the help.
    and a ,INIT to overwrite.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •