April 9, 2008 at 12:25 pm
Hey guys,
Need a little help.
I need to re engineer my companies database maintenance procedures.
Currently we backup every night and backup the tran log with truncate_only on a daily basis.
Once a week we check the db for consitancy using DBCC checkdb with all_errormesgs
we then do DBCC shrinkdatabase and DBCC shrinkfile with truncateonly option.
This is all scripted in batchfiles which are controlled by an external application.
There is nothing i can do about the application and batchfile use, but I want to revamp the SQL commands that the batchfiles call in since I feel that what we have right now is not very efficient.
Any sugestions and help would be appreciated.
April 9, 2008 at 12:30 pm
Can we see the commands you're using at the moment, please?
Thanks
John
April 9, 2008 at 12:36 pm
These commdans are run once a week:
sqlcmd -E -d "master" -Q "DBCC CHECKDB ('db_name') WITH ALL_ERRORMSGS" -o "output directory\check_db.txt"
sqlcmd -E -d "master" -Q "DBCC SHRINKDATABASE ('db_name', 10)" -o "output directory\shrink_db.txt"
sqlcmd -E -d "db_name" -Q "DBCC SHRINKFILE (db_name_Log, TRUNCATEONLY)" -o "output directory\shrink_log.txt"
These are run every day
qlcmd -E -d "master" -Q "BACKUP DATABASE db_name TO DISK = 'output directory\db_backup'" -o "d:\celerant\db\backup\db_backup.txt"
sqlcmd -E -d "master" -Q "BACKUP LOG db_name WITH TRUNCATE_ONLY" -o "output directory\log_backup.txt"
The aplication that controlls these 2 batchfiles also will compress the db_backup file and will keep 5 copies of it.
Currently the databases are all set to Full Recovery.
April 9, 2008 at 12:55 pm
Looks fine for what you're trying to do. But if I were you I wouldn't shrink the transaction log on a regular basis. Let it grow as large as it needs to be and leave it there. If you shrink it, it will only have to grow again, wasting server resources, and causing fragmentation on your disk. Also, if you are truncating your transaction log without actually backing it up, you may as well put your database in simple recovery mode. This will dispense with the need to back up and shrink the transaction log.
John
April 9, 2008 at 1:42 pm
I have read that shrinking the database is not the best practice.
Also backing up the tran log with truncate_only is on its way out and is not really good to use.
My main goal is to have a good backup script that will run every night.
I was thinking of
1. Check DB for consistancy
2. Backup DB
Then once a week:
1. Reindex all tables
Now you say that once the DB is set to simple recovery the tran log should not grow as fast or at all?
Some of my dbs grow fast, they have alot of activity on them.
Would you recomend backing up the tran log every night without using truncation?
Also the other thing I would need help is how to reindex more then one table in script format, ie a procedure that will reindex tables or something that i can see to get the general idea and write something up for our setup.
April 9, 2008 at 1:50 pm
I Will Agree with SSCommited. the only change I would do is the retirement of the schinkage of the database. if this is the case, you need to redesign the database.
"We never plan to Fail, We just fail to plan":)
April 9, 2008 at 2:07 pm
I think that you should avoid shrinking, not only the transaction log, but also the database.
Paul Randal wrote about this in his blog some time ago.
http://www.sqlskills.com/blogs/paul/2007/11/13/AutoshrinkTurnItOFF.aspx
About recovery models and transaction log backups, I think that it's all about your restore requirements and the data loss that you can take. If it's good enough to do a restore of a full backup then you can use the simple recovery model and no transaction log backups.
If you have higher requirements on restore then you should use full recovery model and transaction log backups. You could for example let the transaction log backups run hourly. (You should not use the TRUNCATE_ONLY option.)
About index fragmentation Microsoft has some recommendations about fragmentation levels and when to do a reorganize and when to do a rebuild.
http://technet.microsoft.com/en-us/library/ms189858.aspx
I have a stored procedure that could help you with this. The stored procedure does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level, LOB existence and index size.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
April 10, 2008 at 1:56 am
Marcin Wilczek (4/9/2008)
Now you say that once the DB is set to simple recovery the tran log should not grow as fast or at all?
This is oversimplifying it slightly, but if you set your database to simple recovery mode, it means your log will be truncated regularly, perhaps after each transaction. Therefore your log isn't likely to grow much larger than your largest transaction.
Some of my dbs grow fast, they have alot of activity on them.
Would you recomend backing up the tran log every night without using truncation?
Yes, if it's important that you be able to recover your database to a point in time. If it's sufficient just to be able to recover to the last full backup, then simple mode will be enough for you.
Also the other thing I would need help is how to reindex more then one table in script format, ie a procedure that will reindex tables or something that i can see to get the general idea and write something up for our setup.
You can write a script that will loop through the tables in your database, check the fragmentation on them, and rebuild/rearrange the indexes depending on the level of fragmentation. I'd be surprised if there weren't an example of such a script on this site somewhere. Or you can use a Database Maintenance Plan.
John
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply