how to shrink the log file - 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

General SQL Server Forums

New to SQL Server Programming

how to shrink the log file

Author

Topic

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2008-12-16 : 04:59:59

if any one can help me to shrink th elog file, its in GBs. i am using shrinkfile command but getting error that
file doesnt exists in sysfile system.

Rahul Arora
07 Batch
NCCE Israna,

######################
IMPOSSIBLE = I+M+POSSIBLE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 05:07:09

http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2008-12-16 : 05:21:47

quote:
Originally posted by visakh16

http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/

thanks for reply

I tried that code but still geting the same error:

Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'F:\Program Files\Microsoft SQL Server\MSSQL\Data\A_PROD_LOG.LDF' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator. :(

Rahul Arora
07 Batch
NCCE Israna,

######################
IMPOSSIBLE = I+M+POSSIBLE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 05:32:13

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/3c901c06-05cc-48d6-9397-8b2438efd1e1/

kote_alex
Posting Yak Master

112 Posts

Posted - 2008-12-16 : 05:33:27

try this

backup log name_of_db with truncate_only

and then rightclick on your database and TASK > SHRINK > DATABASE ... it worked for me... :)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-16 : 09:11:45

quote:
Originally posted by kote_alex

try this

backup log name_of_db with truncate_only

and then rightclick on your database and TASK > SHRINK > DATABASE ... it worked for me... :)

Why shrink whole database when OP is asking just to shrink log file?

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-12-17 : 14:32:46

BACKUP LOG <mydb> WITH TRUNCATE_ONLY will be deprecated in the near future of SQL

Why..it breaks your log chain?

instead use

alter database mydb set recovery simple
go
checkpoint
go
alter database mydb set recovery full
go
backup database mydb to disk = 'c:\mydb.bak' with init
go

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-17 : 21:37:59

quote:
Originally posted by cshah1

BACKUP LOG <mydb> WITH TRUNCATE_ONLY will be deprecated in the near future of SQL

Why..it breaks your log chain?

instead use

alter database mydb set recovery simple
go
checkpoint
go
alter database mydb set recovery full
go
backup database mydb to disk = 'c:\mydb.bak' with init
go

This won't shrink log file. You have to fire shrink statement after you change to Simple Recovery model.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources