| Author |
Topic |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2012-12-18 : 04:01:23
|
| I have a server that doesn't have enough spaceI want to do full backup type as it's essensial to be able to restore if anything happensis it possible to nightly truncate the log file after the backup so it doesn't keep growing as the backup file is very big in size?Please tell me what i can do ? Hopefully client will get better servers but in the meantime I need to deal with this |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-18 : 04:39:02
|
| I hope you are already taking full backups - otherwise that should be your priority - and by that I mean before anything else.You shouldn't truncate the log file unless some exceptional processing has happened as it wil just grow again.Maybe you need to look at the processing that is carried out or add more frequent log backups - if you aren't using simple recovery model.Another option is to get more disk space.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2012-12-18 : 04:52:25
|
| I am taking full backups -- nightly what can I do to make this take less disk space?we don't have an option to get more disk space - hopefully new servers will be on the way soon |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-12-18 : 20:40:20
|
| It sounds like you have the backup files and the log files on the same drive. I'd caution against this unless you are (quickly) archiving the backups to an external media, such as another server. If the common drive were to go bad, you'd be losing your log files, and potential data loss, and the backup files. If the data files are also on the same drive, you'd be hitting the trifecta of doom.=================================================Hear the sledges with the bells - silver bells! What a world of merriment their melody foretells! |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2012-12-19 : 00:55:49
|
| so what's the best way to do thishow often to backup and how can I truncate this log file? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-19 : 03:46:52
|
| Have you thought about backup compression. Could be available with sql server depending on your version I think - otherwise there are third party products that will do it.By truncate the log file I take it you mean shrink it.First - what recovery model are you using? If it is not simple and you are not taking log backups then you are in trouble - the log will keep growing until ut eventually fills the disk and you crash and may end up with a coorrupt database. If that's the case change it to simple.Then you can shrink the log and see how much it grows to get an estimate of how much space you need.You can then think about the recovery model you need and whether you need log backups.If you are already simple or taking frequent log backups then you can shrink the log file - but as I said earlier you shouldn't do this if it is just going to grow again.To shrink the log right click on the database in enterprise manager, tasks, shrink, files.You also need something in place to regularly test backups.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2012-12-19 : 04:19:30
|
| I realized I wasn't taking log file backupsI'm setting that up now but how do I shrink it nowshould I set it to simple, shrink it and then set it back to full with log file backups? |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2012-12-19 : 04:38:26
|
| I took log file backup and I still can't shrink iteven tried changing to simple recovery and it still won't shrink |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-19 : 05:28:11
|
| Leave it for a while until the active entry cycles round to the beginning of the log.In the worst case you can detach the database, delete the log file and attach again - it will create a new log file.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-21 : 10:40:05
|
quote:
Originally posted by esthera
I took log file backup and I still can't shrink iteven tried changing to simple recovery and it still won't shrink
Do you need point in time restore. If answer is no then you can change your recovery model to simple which will truncate at checkpoint unless you are doing huge DML operation in single transaction. |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2012-12-21 : 17:39:34
|
| A file won't physically shrink unless you explicitly shrink it.If the explicit shrink doesn't work, then the active VLF might be at/near the end of the log file. In that case, you'll need to wait until it recycles to (near) the front of the log file.For example:USE db_nameDBCC SHRINKFILE ( 2 )ALTER DATABASE db_nameMODIFY FILE ( NAME = db_name_log, size = 1GB, filegrowth = 50MB )--Change size to the MAX size the log file will need to be. |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2012-12-22 : 12:07:08
|
| I was shrinking it the problem was it was set to replicate and that was causing me not to be able to shrink itproblem solved now - thanks |
|
|
|
SQLCrazyCertified
Starting Member
6 Posts |
Posted - 2013-01-02 : 11:55:59
|
| "I was shrinking it,the problem was it was set to replicate"By above you mean replication was in place?SueTons. |
|
|
|
|
server without enough space -