October 15, 2008 at 1:07 pm
Hi,
I am trying to backup the 65GB of Database {MDF is about 40GB, LDF is about 25GB}. Some tables are contain 6millions of records. Currently Database is set to Simple Recovery Mode and I had never backup the Transaction Log.
Want I want to know is, Before the backup run at midnight, I want to rebuild the index/ Defrag Index. Also want to Shrink the Database as well. But I am not 100% sure which is the right order to do BEFORE the backup.
Anyone can suggest the best way to maintain the Database?
Thanks.
October 15, 2008 at 1:15 pm
First piece of advice, don't shrink the database. If you do it before the index rebuild/defrag, you'll most likely fragment the physical data file as it will grow as a result of the rebuild/defrag. If you do it before the rebuild/defrag, you'll just undo what you did.
😎
October 15, 2008 at 1:17 pm
Shrinking the database is not a recommended practice. If you shrink it then you will likely go right back and grow it again if autogrow is not on and if autogrow is on then you will probably go right back through the growth process again. Since your recovery model is simple the tx log should be maintaining it's size unless you have open transactions in it.
I'd backup before the index operations and if I have time after as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 15, 2008 at 1:31 pm
Hi Lynn and Jack,
So I shouldn't shrink or backup log ..... with tuncate_only for my Databases :w00t: . 35GB for Data and 20GB for Log when I look at the DB Initial Size. Transaction log is keep growing as well. at the moment transaction log is about 30GB.
So you mean, Just 'Index Rebuild' then Backup the DB? No Defrag Or Shrink?
Thanks
October 15, 2008 at 1:33 pm
I would definitely backup prior to any other operation.
Since you are running in simple mode the log file will not stay smaller than it is and will waste a lot of time resizing back if you shrink it.
October 15, 2008 at 1:59 pm
The log doesn't hurt anything at 20GB. It might be a bit large, or it might not, depending on the load of your largest transaction.
In any case, I'd run the backup, then the rebuild. Do not shrink.
Also, if you are running in simple mode, you can't easily recover your database except from the last full backup. If you lost your database at 5pm, is that a problem? If it is, then change to full mode and run log backups.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 15, 2008 at 2:02 pm
My normal flow is:
Backup
CheckDB
Rebuild/Reorg Indexes
Update Stats - I know this is somewhat redundant with auto stats on but I still do it
Clean Up backup history
The sizes you are listing are consistent with a moderately busy databases of that size and shouldn't be that shocking.
October 15, 2008 at 2:12 pm
Alan (10/15/2008)
Rebuild/Reorg IndexesUpdate Stats - I know this is somewhat redundant with auto stats on but I still do it
Not just that, but the rebuild of an index updates stats of that index with fullscan. If you do a sampled stats update after an index rebuild you're actually reducing the accuracy of your stats.
If you want to do both, update the stats first, then rebuild the indexes.
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 15, 2008 at 2:17 pm
Alan and Steve,
What do you mean? -
*** Also, if you are running in simple mode, you can't easily recover your database except from the last full backup. If you lost your database at 5pm, is that a problem? If it is, then change to full mode and run log backups. ****
1. Does Simple Recovery Model not backup the log? If do so, I will ignore the shrink.
But database performance will be slow if transaction log is growing and become too big.... Are you sure 'I shouldn't trancate the log or shrink' ?
2. What is the best command for DBCC CHECKDB (REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD) ??
Thanks.
October 15, 2008 at 2:18 pm
Leo (10/15/2008)
So you mean, Just 'Index Rebuild' then Backup the DB? No Defrag Or Shrink?
Rebuild does everything that defrag does and more.
Shrink, as Lynn and Jack said, is not a good idea. See here for what it does to indexes - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
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 15, 2008 at 2:20 pm
Hi Gila,
Have you got a code for Index Rebuild with full scan? I am using SQL 2000.
Thanks.
October 15, 2008 at 2:22 pm
Leo (10/15/2008)
1. Does Simple Recovery Model not backup the log? If do so, I will ignore the shrink.
No. In simple log records are discarded at regular intervals. It's not possible to backup the log. Trying to do so will give you an error. Hence you can only restore to the latest full/diff backup
But database performance will be slow if transaction log is growing and become too big.... Are you sure 'I shouldn't trancate the log or shrink' ?
In simple the log shouldn't grow too much as log records are not retained.
Log size doesn't affect performance. If it has to grow however it will slow things down. Hence you should size the log appropriately and leave it alone
2. What is the best command for DBCC CHECKDB (REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD) ??
None of the above.
Run checkDB with no options to check the database for corruption. If you do have corruption it's better to restore from backup. Using any of checkDB's repair options is a last resort when there is no clean backup.
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 15, 2008 at 2:27 pm
Leo (10/15/2008)
Hi Gila,Have you got a code for Index Rebuild with full scan? I am using SQL 2000.
Thanks.
No such command. Full scan applies to statistics updates, not index rebuilds.
See DBCC DBREINDEX and UPDATE STATISTICS in Books Online
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 15, 2008 at 2:34 pm
Hi Gila,
Thanks for helping me out. As you said -
***Run checkDB with no options to check the database for corruption. If you do have corruption it's better to restore from backup. Using any of checkDB's repair options is a last resort when there is no clean backup. **
Can I set up as a job and send an email if database is corrupt? Do you have any code written for send email if database is corrupt?
Code below is how I will going to check my production databases -
@@@@@@@@@@@@@@'
declare database_cursor CURSOR for select name from master..sysdatabases
declare @database_name sysname
open database_cursor
fetch next from database_cursor into @database_name
while @@FETCH_STATUS=0
begin
print @database_name
dbcc checkdb(@database_name) with no_infomsgs
fetch next from database_cursor into @database_name
end
close database_cursor
deallocate database_cursor
@@@@@@@@@@@@@@'
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply