Shrinking the log file

Question

Shrinking the log file

Chand00

SSCertifiable

Points: 6466
More actions
October 7, 2008 at 1:25 pm

#193983

Hi,
My current log file size is 98816.94 MB in sp_helpdb and if i goto shrink file in the management studio currently alloted space is 54556.69 MB and available free space is 2113.64 MB (3%). Can you tell me how to release the free space to the os since dbcc shrinkfile also does not work here.
Thanks
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Well, there's only 3% free space in the log, so I think the first thing that we need to do is figure out why the log is full.

Let me guess. The database is in full recovery mode and there are no log backups running? If so, read through this - http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/

If that is the case, the switch the DB to simple recovery model. Run a checkpoint. Use DBCC ShrinkFile to get the log to a reasonable size. Switch the DB back to full recovery mode. Take a full database backup and set up regular log backups.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736193 More actions · Answer 2

If you have been running log backups, you need to add more space. Your log needs it.

Chand00 SSCertifiable Points: 6466 More actions · Answer 3

Thanks for the reply. I have one more problem I have another db which is in full recovery model and in the shrink file dialog Currently alloted space is 57049.19 MB and available free space is 56710.64 MB and dbcc shrinkfile with truncateonly option also does not work and when i had checked the properties of the log file the inital size is somewhere around 50000 MB. Can you help me to shrink the log file. I got the backup from other team and restored on to my box. Now i want to shrink the log file without affecting the data in it . No users are accessing the db right now. will changing the recovery model to simple and back to full helps

[font="Verdana"]Thanks
Chandra Mohan[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

On that one you'll have to wait until the portion of the log that's in use is at the beginning of the file. You can force that by creating a table, putting a lot of rows in it and then dropping the table.

The log is a circular file and if the active portion is towards the end of the file, you won;t be able to shrink it down.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 5

Im just curious as to why don't you just backup the log and shrink the file since this is in full recovery mode? this is under the understanding that this database has had a full backup previously. When I have a log that has grown too big and I don't want to lose any data, I just do a log backup and shrink the file. Is there something else I'm missing? 🙂

Isabelle

Thanks!
Bea Isabelle

Ray Mond SSCertifiable Points: 7159 More actions · Answer 6

You can shrink a transaction log file only up to the boundary of a virtual log file. See this link for details.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 7

Hi,

Thanks for the link. I see that even though the log had most of the space free that the active portion of the log must have been at the end and that is why Gail was suggesting to insert dummy records so that it wraps that active portion to the beginning.

Thanks!

Isabelle 😀

Thanks!
Bea Isabelle

anjan.ashok SSCrazy Points: 2783 More actions · Answer 8

Hi,

In order to shrink the log file ,from Sql server managment studo

1. Take the log backup of the db

2. then right clk db ,select tasks--> shrink--> their select Log its showing current occupied space and if u want set size to be shrinked or else just run shrink it will work.

Ray Mond SSCertifiable Points: 7159 More actions · Answer 9

Inserting dummy records is no longer necessary in SQL Server 2005, as shown in the example. SQL Server handles that for you when you attempt to shrink the log. Also see the 'Shrinking the Transaction Log' topic in SQL Server 2005 Books Online.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 10

Well that's why I was asking my first question about backing up the log and shrinking it? If SQL 2005 automatically moves the active portion then why was it being suggested the need to insert records in order to move the active portion? I've always been able to backup and shrink my logs when necessary with no problems. Now I run frequent backups of the log so it is no longer necessary unless a big bulk load is done or when i rebuild a bunch of indexes or something.

I was just confused with Gail's suggestions and wasn't sure why it was being done...that's why I thought I was missing something.:hehe:

Thanks,

Isabelle

Thanks!
Bea Isabelle

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Ray Mond (10/8/2008)
Inserting dummy records is no longer necessary in SQL Server 2005, as shown in the example. SQL Server handles that for you when you attempt to shrink the log. Also see the 'Shrinking the Transaction Log' topic in SQL Server 2005 Books Online.

Obviously I'm a little behind the times...

Is that still the case if doing a shrink of the log file with truncate_only, as the OP was doing?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chand00 SSCertifiable Points: 6466 More actions · Answer 12

I think there's a confusion. I got a full backup of a db from another team and I had restored onto one of my servers. After restoration space on one of the drives came to 10% of the total size alloted to that drive. When I had checked up the log file was there on this drive and the log file size was almost 60gb and most of it had freespace (when i went for shrink file from management studio). But even shrinking the file did not release the space to the O/S. I would like to know what are the options I have to release the free space from the log file to the disk without any loss of data.

Thanks

Chandra Mohan N

[font="Verdana"]Thanks
Chandra Mohan[/font]

Ray Mond SSCertifiable Points: 7159 More actions · Answer 13

Is that still the case if doing a shrink of the log file with truncate_only, as the OP was doing?

Yes.

I would like to know what are the options I have to release the free space from the log file to the disk without any loss of data.

Run DBCC SHRINKFILE on the transaction log. Back up the transaction log. Run DBCC SHRINKFILE again. If you want to understand why this needs to be done, refer here.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

Leo-696352 SSCrazy Points: 2708 More actions · Answer 14

Hi Guys,

Sorry, I want to understand something clearly about shrinking the backup log.

What is difference between -

right clk db ,select tasks--> shrink--> their select Log

AND

Backup Log with Truncate_Only

then right clk db --> tasks--> shrink--> their select Log

Thanks.