how to shrink the size of a logfile?

[ryansupak]

hi,

i have a logfile which is about 2.5GB and i'd like to restrit the size to 50MB. however, under the enterprise manager, it won't seem to let me alter the logfile size "ceiling" to be smaller than the current logfile size.

i also tried deleting the logfile altogetherm but that didn't work either; it said deleting the primary logfile was not allowed...

now that the logfile is already larger than i want it to be, how can i shrink it?

thanks for your time,
rs

 

[TheBugSlayer]

In EM right-click the database you're interested in. In All Tasks choose 1- SQL Server 7: Truncate LOg, 2- SQL Server 2000: Shrink Database and then choose Shrink files at the bottom to select the log file. Either way make sure you backup the DB right after.

The size of the log files are also physically reduced when a DBCC SHRINKDATABASE or a DBCC SHRINKFILE statement referencing a log file is executed.

Hope this helps...

 

[alistairpaul]

If SQL Server 7.0, it can sometimes be buggy. It may help to sp_detach_db the database, rename the log file (windows explorer), then sp_attach_db the database, specifying only the name of the data file. It will then automatically create a blank log file (small size) for you.

 

[tlbroadbent]

Read faq183-1534 - "Shrinking Databases and Logs - SQL 7 and Higher."
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.