Can't shrink log file

I have an SQL Server 2000 database where the data file is 120 MB and the log
file is 330 MB. For some reason I am now unable to shrink the log file,
which has never been a problem before. I am doing this in Enterprise
Manager, selecting Shrink Database then selecting the log file and the
"compress pages and truncate free space" option. It says that the entire 330
MB is being used and won't shrink it. How can I fix this? Is it OK to close
down SQL Server and just delete the log file?
GlennDon't delete the file. Check out http://www.karaszi.com/SQLServer/in...ink
.asp where you
find some information about shrinking log file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message news:47616ac1$0$2353$4c368faf@.roadrunne
r.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the lo
g
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire 3
30
> MB is being used and won't shrink it. How can I fix this? Is it OK to clos
e
> down SQL Server and just delete the log file?
> Glenn
>|||You most likely have an open transaction preventing the log from reusing the
space. Run DBCC OPENTRAN() in that db to see. If not you probably have the
recovery set to FULL but are not doing log backups.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner
.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the
>log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>|||Glenn,
You can always shrink the log using t-sql as well.
First you need to backup the log but in this case its for the purpose of
reducing the size and not actually saving the log.
BACKUP LOG WITH TRUNCATE_ONLY
then you need to use the dbcc shrink file command
DBCC SHRINKFILE (LogicalFilename, size you would like to reduce it to);
http://msdn2.microsoft.com/en-us/library/ms189493.aspx
that should take care of it.
--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner
.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the
>log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>|||Try to Dump Tran Databasename with no_log command then selecting Shrink
Database
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner
.com...
> I have an SQL Server 2000 database where the data file is 120 MB and the
> log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>