log file shrink not releasing the space on disk

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)

log file shrink not releasing the space on disk

Author

Topic

venkath
Posting Yak Master

202 Posts

Posted - 2007-10-04 : 14:39:12

Hi all

My Transactional log size increased to 39GB, it is in full recovery mode,

To regain the space, i have done the following
BACKUP LOG DB_NAME WITH TRUNCATE_ONLY
DBCC SHRINK_FILE (LOG_FILE_NAME,500)
But not able to regain the space in the hard disk.

No Transactional backups to truncate the log file were setup.

Can you please tell me why the space was released and what should i do further to clean up the sapce

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-04 : 15:06:29

Try shrink it in ssms gui. You have to set scheduled log backup job.

venkath
Posting Yak Master

202 Posts

Posted - 2007-10-04 : 15:30:21

when i tried from GUI, I got the following error

TITLE: Microsoft SQL Server Management Studio
------------------------------

Shrink failed for LogFile 'Z1LT01_LOG'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+LogFile&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

venkath
Posting Yak Master

202 Posts

Posted - 2007-10-04 : 15:43:27

I have made the recovery model of the database to SIMPLE (since we don't have any backup stragtegy for this db)and issued the following command
dbcc shrinkfile ('Z1LT01_LOG',100)

It gives this result set
dbid Field currentsize Minsize Used pages estimatedpages
5 2 4855808 128 4855808 128

but still could not release the space in the disk.

Thanks..

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-04 : 16:02:27

Does ssms used have same sp level as on server?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources