February 15, 2008 at 2:59 am
Hai Everybody,
I have an Axapta Database in Sql server 2005 of which the log file is 10 gb.It is growing daily as of which the space in C drive is becoming less.
How do i reduce the log file of this database inorder to make some available space for OS
Request to help me(in detail as i dont have much knowledge in sql server) to resolve this problem ASAP.
Thanks and Regards,
Devi
February 15, 2008 at 3:16 am
Hi,
Please run this script on your DB, And sleep without tention-:)
--******************************************
exec sp_dboption 'DB_NAME','trunc. log on chkpt.','ON'
exec sp_dboption 'DB_NAME','AUTOSHRINK','ON'
checkpoint
BACKUP LOG DB_NAME WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (DB_NAME)
checkpoint
exec sp_spaceused
--******************************************
Best Regards
Faheem latif
Senior Database Architect
Genie Technologies (Pvt.) Ltd.
February 15, 2008 at 3:50 am
Hai ,
Thanks very much for your reply.
I will try that one and get back to you with result.
For any further assistance in doing this can i have contact no of yours so that it will be easy to communicate.
Thanks& Regards,
DEVI.
February 15, 2008 at 4:01 am
faheemlatif (2/15/2008)
Hi,Please run this script on your DB, And sleep without tention-:)
--******************************************
exec sp_dboption 'DB_NAME','trunc. log on chkpt.','ON'
exec sp_dboption 'DB_NAME','AUTOSHRINK','ON'
checkpoint
BACKUP LOG DB_NAME WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (DB_NAME)
checkpoint
exec sp_spaceused
--******************************************
Ouch!!! I would certainly not sleep well after this!
Make a log backup, and make sure you backup your log periodically. if the logfile size is too big, then you may shrink it (!after a log backup! (e.g. DBCC SHRINKFILE )). The above post is useful if you do not care about disaster recovery. But then you may as well change your database to simple recovery mode. It would happen anyway when you execute the above commands and do not perform a full database backup.
Before you do anything, please read on backups (http://technet.microsoft.com/en-us/library/ms175477.aspx, recovery models http://technet.microsoft.com/en-us/library/ms189275.aspx, and dbcc shrinkfile (http://technet.microsoft.com/en-us/library/ms189493.aspx)
Regards,
Andras
February 15, 2008 at 4:10 am
Hi DEVI,
u can mail me ,Please see my ID using profile .
Best Regards
Faheem latif
Senior Database Architect
Genie Technologies (Pvt.) Ltd.
February 15, 2008 at 7:18 am
Need more information. Is this a production OLTP system or a data warehouse. Is the data loaded from another database or do you have various users/systems inserting and updating the data.
The reason these questions are asked, is, if you have total control of the data loaded, then place the database is simple mode as you can always reload the data. If this is a OLTP production system, you need the logs if you want to do point in time recovery.
Never shrink a production log without first backing it up. The option to checkpoint the log prior to backup is up to you. I think it is best to do so but have heard opinions both ways on the subject.
The most important question is the use of the database and will you get fired for not being able to recover to a point in time.
Also, while on the subject, DBAs please remember to test your backups periodically, do the fire drills, a bad backup is like not having a backup as you get the same results
Marvin Dillard
Senior Consultant
Claraview Inc
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply