October 11, 2010 at 1:50 am
Dear Experts,
About My Database ,
1)Recovery Full,
2)Full text Serach Enabled and Created
3)Logshipping N/A,
4)Mirroring N/A
Am following to Shrink my log file below as
Alter database with Simple Recovery Mode
go
Dbcc Shrinkdatabase(db_name)
go
Alter database with Full Recovery Mode
Is that Correct way or it gives any sideeffect with DB or Front End..
Or please give me the Exact Solutions
October 11, 2010 at 1:56 am
Don't shrink the database. If it grows, this means it needs the space. If you shrink it, it will grow again.
If you don't want your log files to grow, take more frequent log backups.
Don't change recovery model, it messes your backups and makes them useless.
Please read this article, I'm sure you will find it helpful.
http://www.sqlservercentral.com/articles/64582/
Gianluca
-- Gianluca Sartori
October 11, 2010 at 2:03 am
Ow, ow, ow. Why on earth are you doing this to your database?
Setting to simple recovery breaks the log chain. You will not be able to take a log backup and you will have NO point in time recovery capabilities until you take a full or diff backup. Are the users of this system happy with that kind of data loss risk?
Shrinking the DB causes massive fragmentation and unless done carefully and with planning is just going to cause the files to grow again. This will result in everything slowing down during the grow operations. I'll be willing to bet that your autogrow settings are also non-optimal.
If you absolutely insist on shrinking your log file (and have a good reason) then shrink the log alone, not the data file and don't touch the recovery model.
Take a look through this article - http://www.sqlservercentral.com/articles/64582/
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
October 11, 2010 at 3:13 am
Great Help, Here after I wont change the Recovery Mode, But with in a week My LDF grow wtih 90 gb, But In our Datacenter given us only 120 Gb space for Database ,
I need to Shrink only LDF please provide me the Proper Log backup and shrink the LDF file scripts..
Many Thanks Sir .....
October 11, 2010 at 3:21 am
You're treating the symptoms, not the cause.
Don't focus on shrinking the log, focus on finding out why it's growing and resolving whatever that is. Start by checking the size of your log backups (you are doing regular log backups, right?) and seeing which ones are large.
As for proper log management, check the article that both Gianluca and I gave you.
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
October 11, 2010 at 3:23 am
Take more frequent log backup, this will prevent it from growing.
If you still want to shrink it, you can run DBCC SHRINKFILE('yourLDFfileNameGoesHere', 0). This will shrink the LDF file at its minimum possible size.
You should also check your file growth settings and eventually change it to fixed MB growth instead of percentage growth.
-- Gianluca Sartori
October 11, 2010 at 3:29 am
Gianluca Sartori (10/11/2010)
If you still want to shrink it, you can run DBCC SHRINKFILE('yourLDFfileNameGoesHere', 0). This will shrink the LDF file at its minimum possible size.
Generally doing this is a bad idea. Shrinking the log to it's minimum size guarantees that it will grow as almost the next thing that happens. Since the log must be zero-initialised, this could take some time if the growth increment is large and/or the disks slow.
If you absolutely have to shrink the log, don't shrink to 0, shrink to a reasonable size based on your database activity and frequency of 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
October 11, 2010 at 3:37 am
GilaMonster (10/11/2010)
Gianluca Sartori (10/11/2010)
If you still want to shrink it, you can run DBCC SHRINKFILE('yourLDFfileNameGoesHere', 0). This will shrink the LDF file at its minimum possible size.Generally doing this is a bad idea. Shrinking the log to it's minimum size guarantees that it will grow as almost the next thing that happens. Since the log must be zero-initialised, this could take some time if the growth increment is large and/or the disks slow.
If you absolutely have to shrink the log, don't shrink to 0, shrink to a reasonable size based on your database activity and frequency of log backups.
Nice catch, thanks for pointing it out.
-- Gianluca Sartori
October 11, 2010 at 4:01 am
Generally the only time you want to shrink a log to 0 is if you're trying to clean up the VLFs. Shrink to 0 then immediately grow it to a reasonable size. Should be done when the DB is not in use.
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
October 11, 2010 at 4:44 am
Many Thanks, I have changed My DB plan with your Ways,
1)Changed the setting of LDF growth % to MB
2)Take the Full back up of Log frequently
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply