| Author |
Topic |
|
khunkao
Starting Member
8 Posts |
 Posted - 2009-05-01 : 11:58:51
|
| I don't have much space left on this drive and I'm always on the brink of running out of space because of the growing log files.This is a SQL2000 database that is over 5GB and the transaction logs are constantly growing and filling the space up (lots of transactions during the day).I seem to have a basic understanding of dbcc shrinkfile but what about shrinkdatabase and this truncate_only?What is a simple script I can run to maintain this so it doesn't get out of hand? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
khunkao
Starting Member
8 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
khunkao
Starting Member
8 Posts |
Posted - 2009-05-01 : 16:01:42
|
I would like to definitely have the entire database structure and log file if possible. Rolling back too far isn't necessary but it should be recent. I use Veritas to backup the mdf and log though it doesn't do any maintenance on the db itself. quote:
Originally posted by tkizer
It depends. What kind of recovery do you require in case of a failure?And most importantly, how often do you backup the transaction log?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
|
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-05-01 : 16:43:49
|
| Do not use Veritas to backup the databse files. It is pointless to do this as you may not be able to recover from these. Have Veritas sweep the backup file (.bak) to tape. It doesn't sound like you require BULK_LOGGED or FULL recovery model, since it sounds like you fall back to the last full backup. I'd recommend switching your recovery model to SIMPLE and then shrinking the log file down to about 25% of the size of the mdf file (DBCC SHRINKFILE for this task).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
khunkao
Starting Member
8 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-01 : 17:34:45
|
quote:
Well I thought the full backup included the trans-logs?
Beginning with SQL Server 2000, it does. However a full or differential backup DOES NOT clear the transaction log. Those transactions will remain unless you truncate or back up the log. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-01 : 19:32:37
|
Ugh, sorry, didn't see that this was a 2000 database. Never mind, pretend I wasn't here.  |
|
|
|
khunkao
Starting Member
8 Posts |
Posted - 2009-05-04 : 09:25:31
|
So do you recommend I just switch to SIMPLE instead? Or do a regular tlog backup (scheduled through Maintenance?). I guess the easiest way is to go into SQL Enterprise Manager, pull up my Database and change the recovery model to SIMPLE instead of Bulk-Logged. Can I do this while things are running or whenever. What exactly does switching to SIMPLE do in terms of trans-logs.quote:
Originally posted by tkizer
Well you can't use the full backup to restore to a point in time, so that's why I said it doesn't include the tlog information.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-05-04 : 12:45:20
|
| We can't recommend to you which one to pick as it depends on your business requirements which should include recovery capabilities. In the case of a failure, can you afford to revert back to the last good full backup or do you need more up to date data? We require a maximum of 15 minutes of data loss, we that's why we use FULL recovery model and backup our transaction logs every 15 minutes.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
khunkao
Starting Member
8 Posts |
Posted - 2009-05-04 : 13:20:06
|
quote:
Originally posted by tkizer
We can't recommend to you which one to pick as it depends on your business requirements which should include recovery capabilities. In the case of a failure, can you afford to revert back to the last good full backup or do you need more up to date data? We require a maximum of 15 minutes of data loss, we that's why we use FULL recovery model and backup our transaction logs every 15 minutes.I can afford to revert back to the last good backup. If i need to revert back to something during the day I would say a full backup every night would be reasonable. I don't need to have to roll back 15 minutes ago.Is there a simple script I can run to do this? Also the 5GB database is huge, anyway to shrink that down as well or is that not recommended?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
khunkao
Starting Member
8 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
khunkao
Starting Member
8 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Next Page
|
help with large db and log files
Tara Kizer