how to reduse the size of .ldf file

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 Administration

how to reduse the size of .ldf file

Author

Topic

amolbawake777
Starting Member

3 Posts

Posted - 2014-04-24 : 05:53:32

Hello,

I have a database in which the size of ldf file has increased to 460GB
How can i reduce it?
Or is there any way to attach new ldf file without downtime.

Also let my mdf and ldf file are on different drives.
can i change the path of ldf file.

Regards,
Amol B

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-24 : 08:18:18

More likely than not, your recovery model is full or bulk-logged, and you do not have scheduled transaction log backups. In full or bulk-logged recovery models, the only way transaction log will be prevented from monotonically growing is by taking backups of the log. So you can do one of two things:

a) change the recover model to simple. I don't recommend this, in most cases, but may be appropriate in some.
b) Take a backup of the log, shrink the log file, and then schedule regular backups.

Gail Shaw's article on this topic is very good. Before you do anything, go through that article: http://www.sqlservercentral.com/articles/Administration/64582/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-04-28 : 01:36:40

amolbawake777 - yes , it is possible to add an extra transaction log file, without downtime. Read these instructions
http://www.sqlserver-dba.com/2013/02/add-an-extra-sql-transaction-log-file-alter-database-add-log.html

It is also to change the location of the primary transaction log file - but this requires downtime

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

amolbawake777
Starting Member

3 Posts

Posted - 2014-04-29 : 00:04:47

Hello,

How can i decrease the size of LDF file without any downtime.

AmolB

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-04-29 : 01:42:08

To modify the transaction log file use these instructions http://www.sqlserver-dba.com/2013/02/modify-sql-transaction-log-file-size-increase-or-decrease.html
Test the process on a test environment before applying to a production environment

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

amolbawake777
Starting Member

3 Posts

Posted - 2014-04-29 : 04:12:16

Hello Jack,

Thank you for your help.
Can you please elaborate how can i run the below query to decrease the size.
==========
01.-- check there are no active transactions
02.USE sizetest
03.GO
04.dbcc shrinkfile(sizetest_log,TRUNCATEONLY)
05.GO
06.ALTER DATABASE sizetest
07.MODIFY FILE
08.(NAME = sizetest_log,
09.SIZE = 1MB);
10.GO
==========
I am bit new to SQL database.please help me to this task

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-04-30 : 01:28:47

Hi, You can use SQL Server Management Studio. Just to mention , you shouldn't include the numbers at the start, so it should be :
-- check there are no active transactions
USE sizetest
GO
dbcc shrinkfile(sizetest_log,TRUNCATEONLY)
GO
ALTER DATABASE sizetest
MODIFY FILE
(NAME = sizetest_log,
SIZE = 1MB);
GO

Of course , you must also replace "sizetest" with relevant name of files you are attempting to modify

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-04-30 : 01:28:52

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources