production server is out of Disk space?

Question

production server is out of Disk space?

Mani-584606

SSChampion

Points: 11788
More actions
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

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

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

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 1

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]

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 2

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]

anjan.ashok SSCrazy Points: 2783 More actions · Answer 3

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

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 4

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

My blog

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 5

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