DBCC SHRINK (Reallocating unused space)

[oldduffer]

All

I have a small database of just 400Mb -the log file has been allowed to grow constantly over the past year and has now reached 4500Mb!!! I have therefore performed a backup of all the files and executed a checkpoint. I then ran DBCC SHRINKDATABASE (With Truncate Only). The log file has just 40Mb of used space now but is still maintaining a size of 4500Mb. The space is just not being allocated back to the operating system. I am a little stuck with this, any suggestions would be appreciated

Kind Regards

An Old Duffer

 

[SQLBill]

Go to the FAQ section of this forum and there is a section for Shrinking Databases and Logs. Check out FAQ 183-345 for 'Why won't my transaction log shrink'.

-SQLBill

 

[tlbroadbent]

I recommend faq183-1534 - "Shrinking Databases and Logs - SQL 7 and Higher."

SQLBill,

Note that you can create a link to the FAQ by posting it with the space after FAQ.
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[jschaddock]

To set a target size for the log to shrink down to execute the dbcc shrinkfile command using the following syntax

dbcc shrinkfile(2,'Your database name here')

This in itself will not truncate the log, it will just set a target size to be shrunk to. To actually truncate the log run the command

DUMP TRANSACTION 'Your database name' WITH TRUNCATE_ONLY

This will truncate the log down to the size that you set in the first step. Note that the log may not always truncate down to the exact size that you wish if it still has any uncommitted transactions on it (such as undistributed replication transactions) but it will shrink down to the lowest size allowable i.e. if you try and shrink to 1000MB but the lowest space allowable is 2000MB it will shrink to 2000MB.

Hope this helps

JC

 

[tlbroadbent]

dbcc shrinkfile(2,<target size>) will shrink the file if the log has been backed up and the transactions in the log have been committed. Truncating the log will also work but all transactions will be lost thus eliminating ability to recover transactions. If you truncate the log you should perfrom a full backup immediately to provide a recovery point.

I recommend creating an appropriate backup plan with scheduled backups - full and transactional - to maintain the log file size. Truncating the log is OK for a qiuick fix but is poor procedure in a production transaction environment. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[SQLBill]

Terry,

Thanks for the tip on how to link to a FAQ. I've been trying to figure that out for a while and was just getting ready to post a question on how to do that. While I knew about the FAQ you pointed OLDDUFFER to, I chose the one I used because I felt it answered his specific issue - which was, why wasn't the system reclaiming the unused space.

SQLBill

 

[tlbroadbent]

Bill,

I agree that the FAQ you recommended best answered the specfic question. I just wanted to help increase understanding of log growth and shrinking. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.