Downsize the .ldf even after deleting bulkly

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

Downsize the .ldf even after deleting bulkly

Author

Topic

aravindt77
Posting Yak Master

120 Posts

Posted - 2011-05-20 : 03:58:06

Hi,

I have created a sample database with a small table.
Just inserted 2 billion of data into it and .mdf and .ldf
grow about 5 to 6 GB of size.

Took log back of that db amount to some what same size.

After deleting all the records in that table .mdf went down size , but log still have the same size.

Then took two times the log backup which are comparitively small in size.

But still the .ldf file shows the same 6 GB Size , even after
shrinking the database.

Read from the msdn is that after taking log backup of database which is of full recovery model will tend to down size the .ldf file.

What to do inroder to shrink those fatty .ldf file

Thanks in advance !!

ARv

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-20 : 04:37:14

Ok set your database recovery model simple and bring it back to full recovery mode.
Before you do this first take full backup of your database.

To set simple recovery mode
USE [master]
GO
ALTER DATABASE [Database_Name] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [Database_Name] SET RECOVERY SIMPLE
GO

And Next To Set Full Recovery table

USE [master]
GO
ALTER DATABASE [Database_Name] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [Database_Name] SET RECOVERY FULL
GO

In Love... With Me!

aravindt77
Posting Yak Master

120 Posts

Posted - 2011-05-20 : 05:29:52

Thanks Raghuveer ... but this is not at all practical in real scenario ...any other ways to downsize the .ldf file

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-20 : 05:38:38

See this is last option.
If taking log backup is not reducing .LDF size and DBCC shrinkdatabase not reducing log size.
Ok One thing remember
When you upload large size of data you should have to bring your database in Bulk_log recovery mode and after finishing upload you need to again change recovery mode to full mode.

In Love... With Me!

aravindt77
Posting Yak Master

120 Posts

Posted - 2011-05-20 : 06:52:01

Thanks Reghuveer ... Thanks for the info

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources