• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 799
  • Last Modified:

Shrink a 2008 SQL ldf file

In SQL 2005 we used the following code to shrink a ldf file.  In  2008 sql, do you use the same command or is there another command for 2008 that shrinks the ldf file?

Thanks

Switch your recovery model on the database to SIMPLE, then do a full database backup.  Then, run the following:
backup log yourdbname with truncate_only
then:
dbcc shrinkfile ('logfilename', 0)
where logfilename is the name field returned from select * from sys.database_files
0
nstd-sts
Asked:
nstd-sts
  • 3
  • 3
  • 2
  • +2
1 Solution
 
Marten RuneCommented:
I belive the backup log yourdbname with truncate_only
is depricated.

Resolution, set SIMPLE MODE, shrinke, set FULL MODE, and do a backup

USE [master]
GO
ALTER DATABASE [YourDBName] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [YourDBName] SET RECOVERY SIMPLE
GO

USE [YourDBName]
GO
DBCC SHRINKFILE (YourDBNameLogfilLocicalName)
GO

USE [master]
GO
ALTER DATABASE [YourDBName] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [YourDBName] SET RECOVERY FULL
GO

Regards Marten
0
 
AhmedHERMICommented:
Well Like Marten said, just you need to be carefull, don't run this on a production database. You are erasing the transaction log file and will loose the possibility to do point in time restores.

Best Regards
Ahmed HERMI
0
 
nstd-stsAuthor Commented:
It is production, what the best thing to do.  The log gets over 100 gigs and the system runs out of space.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
AhmedHERMICommented:
i think in this case you can follow this link :
http://nwds-ak.com/WebResources/Database/TruncateTransactionLog.aspx

Well let us know about it.

Regards
Ahmed
0
 
AhmedHERMICommented:
something else to help you dealing with this :
http://www.mssqltips.com/tip.asp?tip=2097

it's a complete code to do the shrink, you can follow it :).
0
 
jimtpowersCommented:
The article @AhmedHERMI linked to includes the following paragraph:

Before going out and shrinking the logs in all your databases because it's so easy now, step back and reconsider why you'd ever shrink a log file.  The log file grew for a reason.  Logs grow because transactions modify the database and there's more transaction activity then transaction log backups.  If the logs grew to that size once, isn't it pretty likely that they'll grow again?  In most cases the answer is yes.  If there was something unusual that caused the logs to grow, you might have reason to shrink the log.  However, it's often the case that the logs will just grow back to their previous size.  

Take this carefully to heart before you shrink your logs.
0
 
jimtpowersCommented:
One more thought - you may need to do log backups more frequently to keep them from growning so large. By the way, how large is your database?
0
 
Alpesh PatelAssistant ConsultantCommented:
It is better to take backup of Log file and truncate it.
0
 
nstd-stsAuthor Commented:
The database is about 150GB.
0
 
jimtpowersCommented:
With a database that large, a 100+ GB log file is not out of line. Remember that when you rebuild your indexes, it uses the log file to accomplish the rebuild. I would suggest adding more space to your SQL server and leaving the log file alone.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now