| Author |
Topic |
|
rio.rohit22
Starting Member
11 Posts |
 Posted - 2010-02-12 : 14:21:45
|
| hey frndsI m using sql server 2005, in default instance, there is a database which has ldf of 50gb and mdf of 20gb. now whenever i take full backup of this db, the bak file has size 20 or 21gb. there is full recovery mode for this db. Also sql server is running very slow. My questions are...1) why ldf is too big in size?2) Why i cannot take full backup of this db, which should be almost 70gb?3) why sql server 2005 is running very slow?your help will be appriciatedthanx in advance |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-12 : 14:32:32
|
| Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
 |
|
|
rio.rohit22
Starting Member
11 Posts |
Posted - 2010-02-12 : 14:42:47
|
| Thanx for reply Gail,But i wanna know what should i do now? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-12 : 16:18:24
|
| Did you read the article? If not, do so.Are you taking log backups? How often?A full backup contains just the data and enough of the log to be able to restore the database consistently. It is not usually the same size as data files + log files. That's normal.As for slowness, that'll be another problem. Large log file alone does nt cause slowness.--Gail ShawSQL Server MVP |
|
|
|
rio.rohit22
Starting Member
11 Posts |
Posted - 2010-02-12 : 23:51:04
|
| Thanx for your kind support GailI m taking log backups on every 2 days or so, it's not scheduled.Can u please tell me any link from where i can study about how to make SQL Server run faster. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-13 : 03:22:46
|
| " I m taking log backups on every 2 days or so, it's not scheduled."Are you SURE those are Log Backups and not Full Backups?You should take Log Backups every 15 minutes, otherwise there is very little point having them (i.e. bar a few recovery scenarios you might as well change to Simple recovery Model) - and the LDF file will grow large.A large LDF which is not being backed up may slow performance.If the LDF is large because at one time it was not backed up, and thus continued to grow, or there was a one-time-only huge Delete operation that extended the LDF, then doing a Shrink, once, to bring it down to a reasonable size would be reasonable.Is the data important in your database? or can you, for example, easily just recreate it from another source?if it is important then back it up properly! using a scheduled task so that it is done regularly, and put in place database consistency checks and be sure that if they find any error that the notification is not overlooked.If you don't have proper, scheduled, housekeeping tasks running on your database then:Run UPDATE STATISTICS using WITH FULLSCAN on all your tables.Run ALTER INDEX using the REBUILD option on all tables & indexesDetails of those commands are in the SQL DocumentationReport back to let us know if that improves performance. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-13 : 11:42:19
|
quote:
Originally posted by rio.rohit22
I m taking log backups on every 2 days or so, it's not scheduled.
So your business is happy with losing up to 2 days of data if there's a disk problem? If your log backups are every 2 days, how often are your full backup?--Gail ShawSQL Server MVP |
|
|
|
rio.rohit22
Starting Member
11 Posts |
Posted - 2010-02-14 : 11:37:42
|
| I know it is not good practice for taking backups after such long period of time. Now bcoz of you guys i am goin to take scheduled DB backups. Thanks a lot to you guys. Now as i have LDF of 50gb and MDF of 20gb, please tell me how can i reduce the size of this LDF? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-14 : 11:50:14
|
| Shrink the LDF to 10GB - I expect it will grow back to around 20GB - 30GB, but it may stay around 10GB.If it grows to more than 30GB then there is probably something unusual happening. |
|
|
|
rio.rohit22
Starting Member
11 Posts |
Posted - 2010-02-15 : 07:22:44
|
| Thanks KristenNow i have scheduled the Backups like: Full backup at 11:00 pm per day.T-Log backups at every 30 minutesAlso i want to let u know that the full backup size is now reduced to 22gb. ... which is really great... thanks frndnow I am going to schedule differential backup...please give some link from where i can study abt it... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-15 : 07:30:15
|
| If you are OK with daily Full backup then you probably don't need differential backups.Some people do a DIFFERENTIAL at mid day - so that a restore will be quicker (failure in the after can be restored from Last night's Full, then midday's Diff, then all TLogs after that - but it can also be restore from Last night's Full plus ALL Tlog backups after that)So unless you are sure that you have a need I wouldn't bother with a DIFF backup |
|
|
|
rio.rohit22
Starting Member
11 Posts |
Posted - 2010-02-16 : 08:41:07
|
| great support guys... thanksi have done shrinking of LDF now total DB size is 24 gb .. which is really wonderful...cheersmy problem is resolved now.. n everybody in my work place is happy with that... thanx a lot frnds |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
LDF size is too big