huge .LDF - SQL Server Forums

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

SQL Server 2005 Forums

SQL Server Administration (2005)

huge .LDF

Author

Topic

daunce
Starting Member

11 Posts

Posted - 2008-11-07 : 23:07:37

Hi,

I have a database of 500meg and an ldf of 30gig.

The db is using full recovery, and i noticed the transaction log backups stopped 2 months ago.

I need to do a transaction log backup to reduce the size of the ldf, but there's not enough space free, and i tried to map a drive to another server, but the mapped drive didn't show up in the backup screen. Do mapped drives work for this?

I've read i can truncate the log file, can I do this in the gui?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-08 : 00:21:51

Truncate the transaction log via BACKLOG LOG and then shrink it with DBCC SHRINKFILE. See SQL Server Books Online for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-08 : 03:39:54

Tara, what will be the best way to avoid such huge expansions of log files in future. Should we use DBCC SHRINKFILE regularly or something else, really effective.

daunce
Starting Member

11 Posts

Posted - 2008-11-08 : 04:54:32

To avoid huge log files, if the database is set to FULL, ensure you do regular transaction log backups.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-08 : 13:50:23

lionofdezert, it sounds like your environment is setup correctly except that someone needs to make sure that jobs aren't failing. You won't have this issue as long as you are taking regular, successful transaction log backups.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

itsonlyme4
Posting Yak Master

109 Posts

Posted - 2008-11-10 : 09:40:19

Shrink Transaction log FILE

--sp_helpdb to verify log size, FileID and recovery Mode of the log
sp_helpdb [database]

--If necessary, change recovery mode to Simple
Use [database]
alter database [database] set recovery simple

--Backup LOG with NO_LOG
Use [database]
BACKUP LOG [database] WITH NO_LOG

--Shrink log file
Use [database]
DBCC SHRINKFILE (2)

--If necessary, change recovery mode back to FULL
Use [database]
alter database [database] set recovery FULL

--be sure to perform a FULL database backup when completed

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-10 : 11:42:48

quote:
Originally posted by itsonlyme4

Shrink Transaction log FILE

--sp_helpdb to verify log size, FileID and recovery Mode of the log
sp_helpdb [database]

--If necessary, change recovery mode to Simple
Use [database]
alter database [database] set recovery simple

You don't need this
--Backup LOG with NO_LOG
Use [database]
BACKUP LOG [database] WITH NO_LOG

--Shrink log file
Use [database]
DBCC SHRINKFILE (2)

--If necessary, change recovery mode back to FULL
Use [database]
alter database [database] set recovery FULL

--be sure to perform a FULL database backup when completed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources