Shrinking Transaction log - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Administration

Shrinking Transaction log

Author

Topic

hari_sql
Starting Member

22 Posts

Posted - 2010-03-09 : 02:20:01

Hi all,

Will Shrinking the Transaction log adversely affect the Mirroring or Log Shipping implemented?

Thanks & Regards,
Hari.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-09 : 02:29:13

No, but why are you shrinking the log?

--
Gail Shaw
SQL Server MVP

hari_sql
Starting Member

22 Posts

Posted - 2010-03-09 : 04:39:55

Hi Gail,

Current DB size is around 30 GB and the size of T-log file is around 15GB and we are running short of HDD space and experiencing performance hit due to huge amount of older data. So now we need to delete older mass data from different tables in the DB, so I believe this would further increase the size of the T-log. In this context, I need to shrink the T-log (after the deletion). If you have any better ideas, always welcome :)

Thanks & Regards,
Hari.

Kristen
Test

22859 Posts

Posted - 2010-03-09 : 05:28:44

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=141065#553313

Delete in batches, force Tlog backup in between each batch, then you won't extend the size of Tlog exceptionally. But you will then want to shrink MDF file to reclaim space from the deleted records.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-09 : 07:11:47

Then rebuild your indexes to fix the fragmentation that the shrink database caused.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-03-09 : 07:14:57

Which will expand the database again?!!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-09 : 10:11:22

Yup.

--
Gail Shaw
SQL Server MVP