log file size huge help urgent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

log file size huge help urgent

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-10-23 : 01:38:27

Dear All,
one of our database created un expectedly with 382Gb logfile. all the memory was taken by that database. some data is there in it. but something around 1 gb data.

now how can i solve this issue?

i want to recreate that database with the existing data with 1 gb initial size. please help me in this regard

Arnav
Even you learn 1%, Learn it with 100% confidence.

SimpleSQL
Yak Posting Veteran

85 Posts

Posted - 2008-10-23 : 03:03:24

You can shrink the log file that should bring the file size down.

Gürkan Alkan
Starting Member

7 Posts

Posted - 2008-10-23 : 05:49:10

Shrinking only removes a little(unused) spaces from the log. Maybe it cannot be an ideal solution.Please share with us the result after you shrink, because (like the topic) I work with SQL Server for the first time.(nearly)

-->Gürkan Alkan<--
Istanbul Üniversitesi Bilgisayar Mühendisligi

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-23 : 06:17:59

Look at this :

DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-23 : 06:19:50

truncate_only is to purge the log files.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-10-23 : 07:06:31

i've done like this....
1)taken the fullbackup
2)dropped the database
3)restored the database
4)detached the DB
5)renamed the logfile
6)attached the datafile (here removed the log file)
now default log file created with 1 MB
no data loss is there

Arnav
Even you learn 1%, Learn it with 100% confidence.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-23 : 07:09:01

quote:
Originally posted by sunsanvin

i've done like this....
1)taken the fullbackup
2)dropped the database
3)restored the database
4)detached the DB
5)renamed the logfile
6)attached the datafile (here removed the log file)
now default log file created with 1 MB
no data loss is there

Arnav
Even you learn 1%, Learn it with 100% confidence.

Why these steps????

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-23 : 07:15:36

Taking a full backup before you screw with the log might be a good idea.

-------------
Charlie

ghemant
Starting Member

22 Posts

Posted - 2008-10-23 : 07:59:57

Hi,
For temporary fix you may take a T-Log backup, and shrink it. But then you should re-think why it has grow so much. There may be many reasons like Autogrow option, bcp/bul inserts, frequent reindex and shrinking should not made practise as database requires to grow over periods. Refer below blog entries for better understanding and why I am saying this
http://hemantgirisgoswami.blogspot.com/2006/03/cause-for-t-log-become-full-and-how-to.html and http://hemantgirisgoswami.blogspot.com/2008/09/should-i-shrink-my-database.html

hth

Regards
Hemantgiri S. Goswami
MS SQL Server MVP
http://hemantgirisgoswami.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-10-23 : 10:44:07

What recovery model is the database in? I'm guessing it's in full recovery and you're not doing any log backups. If that is the case, the log will grow until it fills the disk. What you did (which, btw was extremely risky) will temporarily fix things. After the next full backup the log will begin to grow again.

You have two options. If you need to be able to do point-in-time recovery (ie to restore the DB to the point of failure), then you need to set up log backups. Try an initial schedule of every hour. If the log still grows larger than you want, make the log backups more frequent.

If you don't need to be able to restore to pooint-in-time (ie, if the DB fails, restoring to the last full backup is acceptable) then set the database into simple recovery model.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources