Post reply

production server is out of Disk space?

  • June 4, 2008 at 7:47 pm

    #208013

    hi,

    if the production server disk is out of space what we have to do to free up some space?

    if i take the backup of the log using BACKUP LOG DB_NAME_LOG WITH NO_LOG is that fine or I need o something else inorder to have no data loss?

    Thanks

  • June 4, 2008 at 8:34 pm

    #824626

    Are we talking about logs or data here? The approach you will want to take depends on which of the two you're dealing with.

    Kendal Van Dyke

    Kendal Van Dyke
    http://kendalvandyke.blogspot.com/[/url]

  • June 4, 2008 at 9:08 pm

    #824629

    You need to identify what causes the disk space to be consumed. Check the database files and its most likely the log file which grows abnormally and consumes the disk space..If thats the case you can perform the following for those db's in full or bulk logged recovery model,

    Backup log dbname to disk='Path\filename.trn'

    Use yourdb

    go

    dbcc shrinkfile('log file name', 500)

    go

    The above command will shrink the log file to 500 MB.

    You can also use the option truncate_only or no_log with backup log command but it will break the LSN and hence should be avoided generally.

    To reclaim the disk space you need to shrink the files..

    [font="Verdana"]- Deepak[/font]

  • June 4, 2008 at 11:35 pm

    #824645

    Check if log files are taking more space and shrink only once dont do shrink every time it causes fragmentation.

    If log files are taking more space,schedule frequent log backups and monitor the log files size through dbcc sqlperf(logspace) and monitor it.

    Any other files are taking more space,remove unneccsary files or take backup of that in cd and save more space.

    Monitor DB growth also.

    Cheers,

    Anjan

    DBA:P

  • June 7, 2008 at 6:25 pm

    #825852

    Gary, what is consuming the space? the datafiles or the logfiles?



    My blog

  • June 8, 2008 at 2:24 am

    #825870

    OK, after you freed some space, consider:

    - buying a monitoring tool to prevent this type of errors. Or develop your own script, which runs regularly (I can give you some scripts if you like)

    - limit datafiles on size. If you have multiple databases on a server, you prevent suspending your whole SQL environment because one database is consuming your diskspace.

    - Log the size of your databases on a daily base. By doing this, you can predict when diskspace will become an issue (and you can impress your manager by creating fancy graphs with this data 😉 )

    Wilfred
    The best things in life are the simple things

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

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