Database BackUp

pts.
Tags:
Access
Data analysis
Data mining/analysis
Database
DB2
Desktops
Management
Microsoft Windows
Oracle
OS
Security
Servers
SQL Server
Hi e'rybody! I have recently started my new task as a Database Administrator in SQL 2000 after attending a week course DB administration. Well, we have our back ups running through the DB Maintanance and have noticed that some back ups increase too quick in size and as a result an average database has a back up of about 4 to 5 gig and all these happened in a space of a month after few changes here and there. I noticed that the maintanance is running a full database and transaction log backup 2 diferent files every night and thouhg this could be part of the problem. Other databases with similar settings for the back ups are fine, how do i stop this backup from expanding so quick. I cant even copy these backups cause they are very large in size, how do i clear the log or whatever the solution may be??

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

what is the actual size of the database files? (data & transaction Log)
Do you need to rollback transactions that have been commited?
What is the dB recovery model? (in enterprise manager, right click on the dB, select properties and then option tab)

Gerald

Discuss This Question: 14  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Scottmarch
    Are you doing large data imports frequently. This would cause the log files to grow. You can setup a task to shrink the database. Make sure your backups are successful before you do this. You can add a shrink task in you database maintenance Plan.
    0 pointsBadges:
    report
  • WHDILLON
    Also check and see if the append option is on too. You can check this in the backup job itself. William
    0 pointsBadges:
    report
  • DibyDineo
    The mdf file is 450MB & the data log is 1,035MB; the backup is currently on 1,270MB and the other DB is already on 4,5GB. All our databases use FULL RECOVERY MODEL. How do I know if I need 2 rollback committed transactions?? We have 25 databases that are supposed to be relatively equal in size and only experience this problem with 2 of them. At sum stage one DB doubled the size in just one night (after about months & months of back up), which is unrealistic considering the amount of work done on a given day. With the rest of the databases, the largest site has a back up of 350MB (all the other sites are supposed 2 b smaller than this) Plz HELP!!
    0 pointsBadges:
    report
  • DibyDineo
    We import no data, this is just a back of changes done on the day from that site only, we basically delete older backups after a long while. The databases are backed up through the maintanace plan and i cant find the "append option" - whereabout izit??
    0 pointsBadges:
    report
  • 4921749
    First a question for clarity purposes. The database that you are having problems with, is it a transactional database, online data entry constantly all day or is it primarily a batch load type of database which you perform bulk loads on once or twice a day? If this is a batch or bulk load processing database, I suggest changing you backup method to simple unless it is absolutely neccessary that you maintain the log file for transactions throughout the day. Try shrinking the database and log file. For details on this procedure search on google for shrinking databases. Should be able to find complete description of the function and details related to performing this function.
    0 pointsBadges:
    report
  • KingConan
    As a rule of thumb, the suggested log size should be about 20% of the database size. Your log file should be no larger than roughly 125MB, not over 1GB. At some point a process ran that generated a lot of transactions and since, by default, your database is set to automatically grow with no limit the log will not automatically shrink down because again, by default, the database option shrink db is not checked. You have some options. First try to run DBCC Shrinkfile on the log and set it at 200MB (you must be a multiple of 100MB). If that shrinks the log, fine, you don't have any active or open transactions running. If it can't you need to track down the open transactions - used sp_who and look for high physical_io on a running process - that may be a good indication. You can also try to truncate the log (BACKUP LOG with NO_LOG option). If you do this you need to perform an immediate dataabase backup or future transaction dumps will fail until you do. With this combination of commands you should be able to reclaim some of the space consumed by the log. Kevin
    0 pointsBadges:
    report
  • DibyDineo
    These are all transactional database and i dont really wanna consider shrinking them cause all the other databases are doing fine without any shrinking involved. I felt it must have had to do with the way the maintanance plan was configured but i went through it over and over again and couldnt find anything strange.
    0 pointsBadges:
    report
  • AndyPaul
    Once your LDF file gets so large, you really have no choice but to shrink the file, as mentioned above. As someone else said, your Tx Log should be 20-25% of your MDF, not 200%+. If your backups stay large, I recommend using a 3rd party SQL backup utility with compression, such as Red-Gate SQLBackup. We use this, it provides about a 75-80% compression ratio. It can be scheduled and automated.
    0 pointsBadges:
    report
  • KingConan
    Since you don't want to shrink the log because all your other databases are fine...OK. The reason these databases don't have the problem is they are not generating the transactions to cause the log to grow before you dump it. You need to identify the process or processes that are generating the transactions. Your transaction log should be backing up every hour. A xlog dump will backup the entire log but will only purge inactive or closed xactions. The active transactions will 'carry over' and if they never close, they will never be deleted - causing uncontrollable log growth and ever longer backup times. A backup solution like Red Gate will cost you money without fixing the root cause. It may be a good solution for other things but it will only mask this problem. Kevin
    0 pointsBadges:
    report
  • Squashjunkie
    Depending on what sort of back-up you require and of course how critical the data in the database, you could of course change the recovery model to "Simple" or "Bulk-Logged". This will reduce the amount of records being pumped into the transaction log file. If this is not an option, then you will need to issue more full and transaction log backups throughout the day. This will truncate the transaction log file and reduce the amount of growth of the transaction log file.
    325 pointsBadges:
    report
  • DaveInAZ
    Try this article; http://support.microsoft.com/kb/110139/en-us, particularly point #4 if you're still concerned about removing excess log data.
    0 pointsBadges:
    report
  • DibyDineo
    Hi ya'ol! Since i did mention that i had this similar problem with 2 databases; herez what i did with both of them - i detached the databases and deleted the LOG file and the re-attached the MDF (data) file and as a result the log file was ceated from scratch (@ 504KB). Database 1 seems fine since then but Database 2 has since increased its size to double that of the data file (more than 1GB) in about 3 days. Any idea idea wot cud b causing this, even if i shrink it; that will only b a temporal solution - how do i stop the log file from growing so fast???
    0 pointsBadges:
    report
  • Carosatig
    The only way to fix this is to select Simple recovery. The thing you loose though is the ability to roll back a transaction that has been commited. Using this mode, the Log file will be emptied after every checkpoint. Hope it helps. Gerald
    0 pointsBadges:
    report
  • Lsandhar
    The transaction log file stores the details of all the modifications that you perform on your SQL Server database and the details of the transactions that performed each modification. This will increase over time as transactions get inserted, Modified, deleted. You will need to truncate this tranaction log. Take a backup of your tranaction log and then truncate it. Once truncated peform a full backup.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following