Post reply

Data Drive is full. how to resolve it?

  • December 30, 2014 at 11:45 am

    #296265

    Hello,

    three days back I find that my disk space is getting full and now the data drive is completely full

    what can I do for this?

  • December 30, 2014 at 11:53 am

    #1767057

    Are you serious?

  • December 30, 2014 at 12:24 pm

    #1767060

    Don't tell anyone you knew about this for 3 days and didn't do anything about it earlier?

  • December 30, 2014 at 12:36 pm

    #1767062

    Hard to believe you don't have alerts set up so that this doesn't happen. Scary. Maybe you need to update your resume....

    Maybe read this: http://www.sqlserver-expert.com/2012/05/manage-database-files-and-file-groups.html

    See the section that starts "Add a datafile to an existing group", and put the file on another drive.

  • December 30, 2014 at 1:11 pm

    #1767069

    as others have implied, this is one of those basic items that a DBA handles.

    very often, it's because you've got databases in FULL recovery mode that have had a full backup but never a transaction log , so their logs fill the drive with pending transactions.

    if you have zero space on the drive, you'll need to do some ugly magic most likely.

    lets assume you see one specific database, that is in FULL recovery, that you are nottaking any log backups of:

    select db.name,

    db.recovery_model_desc,

    bk.FullBackupDate,

    lg.LogBackupDate

    from sys.databases db

    INNER JOIN

    (SELECT database_name,max(backup_start_date) As FullBackupDate FROM msdb.dbo.backupset where [type]='D' group by database_name) bk --full database backups

    on db.name = bk.database_name

    LEFT OUTER JOIN (SELECT database_name ,max(backup_start_date) AS LogBackupDate FROM msdb.dbo.backupset where [type]='L' group by database_name) lg --full database backups

    on db.name = lg.database_name

    where db.recovery_model_desc='FULL'

    and isnull(LogBackupDate,'1900-01-01') < FullBackupDate

    my guess is that any of those databases need to have a log backup done immediately, and then shrink the log file to recover space.

    then they need to be regualry schedueld to hav elog backups, or toggled to simple recovery mode.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 30, 2014 at 2:21 pm

    #1767084

    Aside from what Lowell wrote, if you have truly filled up all the space with data, you'll need to archive data and then delete it to free up space.

  • December 30, 2014 at 6:48 pm

    #1767119

    This sounds like an interview question.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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