September 2, 2009 at 11:16 am
Hi ,
I am fairly new to SQL. I use it to collect data from a SCADA system (CIMPLICITY) mainly from industrial powerhouses. My databases are both in full recovery mode. Once a day, the SCADA system performs maintenance actions on both databases. It deletes the data that is over a year old and then it executes the following SQL statement:
USE [CIMPLICITY DATA];DBCC SHRINKDATABASE ('CIMPLICITY DATA'); dump transaction [CIMPLICITY DATA] with no_log;dbcc shrinkfile ('CIMPLICITY DATA_Log',1);backup database [cimplicity data] to disk='D:\SQLBAK\cimplicity data.trn'
My purpose for this (in case if I am doing something wrong) is to get rid of the deleted data by shrinking; keeping the transaction log from growing out of control; and taking a backup. Is this sufficient? Correct? Suggestions?
My question about the ‘backup database’ statement is this: In the case of disaster recovery, will I be able to use the .trn file to fully restore my database?
I have seen so many examples that use .bak and .trn with the ‘backup database’ statement. What are the differences (in rookie terms)?
Thanks for the help,
Scott
September 2, 2009 at 11:36 am
Generally speaking, shrinking your database every day is a bad idea. It results in index and table fragmentation, which causes the whole database to slow down. It also generally means that the database will just have to grow again as more data is added, which means more file fragmentation (again slowing things down), as well as the growth actions themselves causing things to have to stop and wait till they are done.
A better idea is to set the database to the largest size you expect it to reach, and then monitor it to make sure it won't need to grow even more. Take a look at "sp_spaceused" in Books Online or http://www.msdn.com, as that can be very useful in monitoring database file use.
(In case you don't know, Books Online is the help file for SQL Server. You can usually get to it quite easily by opening up the Help menu in Management Studio.)
With regard to backups, the idea is to take daily full backups, and then use log backups to both (a) make sure you can recover to a particular point in time during the day, and (b) keep the transaction log files from growing out of control.
Full backups are usually ".bak" and log backups ".trn" ("trn" is short for "transaction log").
If you do a full backup each day, and then do log backups every few hours (or every hour, it depends on your needs), the log file shouldn't grow out of control. Plus, using those, you can usually recover the database to the point right before a crash, if it crashes. If you have data being updated throughout the day, this is probably an important thing to be able to do.
An understanding of the basics of backup and recovery is usually considered one of the most basic fundamentals of being a database admin. I've covered a very simplified summary here, but what I recommend if you'll be handling those duties, is get a good book on the subject and cover it in some detail. It'll be well worth the time. (Personally, I learned from Paul Nielsen's SQL Server Bible, and can highly recommend that book. Others use other books and recommend them.)
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2009 at 12:24 pm
Thanks, GSquared.
I have implemented a new maintenance plan using the SQL maintenance plan wizard using your recommendations. I am no longer shrinking the database daily.
I will be ordering the book.
I appreciate your help.
September 2, 2009 at 12:32 pm
You're welcome. Glad I could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply