SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 server without enough space -
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

esthera
Flowing Fount of Yak Knowledge

1379 Posts

Posted - 12/18/2012 :  04:01:23  Show Profile  Reply with Quote
I have a server that doesn't have enough space

I want to do full backup type as it's essensial to be able to restore if anything happens

is 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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/18/2012 :  04:39:02  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1379 Posts

Posted - 12/18/2012 :  04:52:25  Show Profile  Reply with Quote
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
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1600 Posts

Posted - 12/18/2012 :  20:40:20  Show Profile  Reply with Quote
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!
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1379 Posts

Posted - 12/19/2012 :  00:55:49  Show Profile  Reply with Quote
so what's the best way to do this
how often to backup and how can I truncate this log file?
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/19/2012 :  03:46:52  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1379 Posts

Posted - 12/19/2012 :  04:19:30  Show Profile  Reply with Quote
I realized I wasn't taking log file backups

I'm setting that up now but how do I shrink it now
should I set it to simple, shrink it and then set it back to full with log file backups?
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1379 Posts

Posted - 12/19/2012 :  04:38:26  Show Profile  Reply with Quote
I took log file backup and I still can't shrink it

even tried changing to simple recovery and it still won't shrink
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/19/2012 :  05:28:11  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/21/2012 :  10:40:05  Show Profile  Reply with Quote
quote:
Originally posted by esthera

I took log file backup and I still can't shrink it

even 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.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
279 Posts

Posted - 12/21/2012 :  17:39:34  Show Profile  Reply with Quote
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_name
DBCC SHRINKFILE ( 2 )
ALTER DATABASE db_name
MODIFY FILE ( NAME = db_name_log, size = 1GB, filegrowth = 50MB )
--Change size to the MAX size the log file will need to be.
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1379 Posts

Posted - 12/22/2012 :  12:07:08  Show Profile  Reply with Quote
I was shrinking it
the problem was it was set to replicate and that was causing me not to be able to shrink it
problem solved now - thanks
Go to Top of Page

SQLCrazyCertified
Starting Member

6 Posts

Posted - 01/02/2013 :  11:55:59  Show Profile  Reply with Quote
"I was shrinking it,the problem was it was set to replicate"

By above you mean replication was in place?

SueTons.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000