This blog is subject the DISCLAIMER below.

Tuesday, August 19, 2008

When database log file increases incredibly?

Working as DBA, I face problems daily and for the importance of the organization I work for; problems should be solved immediately.  past days I had log file which its size exceeds 70 GB unreasonably... I've low disk space problem, clients access this database denied from doing any operation since log file can't allocate more free space on storage area, really all solutions weren't applied due to some restrictions, one of the solutions is to copy data from this database and transmit it to another database (which you can control  log file growth) this solutions failed because SQL Server needs the new database has a log file its size like the old one aka 70 GB!!!!!

Another is to detach the database and trying attaching it without log file, fails also, since log file not exists.

While searching on this problem I've found someone has big size log file asks this question and I found the solution is to create a new database from the .mdf file of the old one and you shouldn't put the .ldf (log file(s)) besides. because SQL Server searching for the .ldf and if it didn't it creates another one with small size. and now old log file isn't belong to new created database and you can delete it and save more space :)

Code to create database from .mdf file

sql code

2 comments:

Anonymous said...

mm, I think i know that database, I think it called UnitsDB :P:P

mm, R, is "FOR ATTACH_REBUILD_LOG" an important statement ???

Ramy Mahrous said...

ht7kmna y7'rbeet 32lk.... dih btdy youm mn7a ;)