March 26, 2008 at 12:47 am
I am not a DBA but of course I have to work with sql. I've run into something here I've never seen before. I have a DB which I'm trying to backup with intentions of moving to a new server. I backup the file and find its about 5 gigs in size. I then compress it using winrar and it ends up at about 500MB. I then transfer it to the new server, decompress it and go to restore it on the new server. Lo and behold its looking for more space than we've got on the new server. I go back and look at the old server, the .mdf is 6 gigs, and the .ldf is 96 gigs! What the heck would cause my log file to be that huge? And if its so big, how could it possibly compress down to 5 gigs during the backup?
I understand how the log file can be larger but 96 gigs seems huge. Before I go and start acquiring more space for the server, How can i find out what is going on with this log file and if it should be smaller?
Thanks in advance for any help you can offer
March 26, 2008 at 7:55 am
There are a zillion other threads on this site relating to this very topic (the search feature here works wonders)! Any number of things can cause the log file to grow. A common process is DBCC REINDEX. Mass data loads/updates/deletes. You'll need to research what is going on on your server to determine what has caused it to grow so large. To shrink (hopefully) your log, this is the standard recommendation:
1) BACKUP LOG 'DBName' with truncate_only
2) DBCC Shrinkfile ('logfilename', xxx) where xxx is the desired size in Mb
3) BACKUP DATABASE 'DBName'
It is imperative that you perform the full database backup after you truncate the log for DR purposes.
-- You can't be late until you show up.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply