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.
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 .ldfgrow 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 modeUSE [master]GOALTER DATABASE [Database_Name] SET RECOVERY SIMPLE WITH NO_WAITGOALTER DATABASE [Database_Name] SET RECOVERY SIMPLE GOAnd Next To Set Full Recovery tableUSE [master]GOALTER DATABASE [Database_Name] SET RECOVERY FULL WITH NO_WAITGOALTER DATABASE [Database_Name] SET RECOVERY FULL GOIn 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 rememberWhen 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 |
 |
|
|
|
|