truncate the transaction log & rebuild the index

1)  You really shouldn't truncate your transaction log.  That will cause you performance problems when the log has to grow again.  If you are running SQL Server and your Recovery Model is full, you need to backup your transaction log.  If you don't backup your transaction log it will continue to grow until it fills up all your disk space.  If you are backing up your transaction log and it is still growing, you might want to it up more often.  We backup our transaction log every hour.  The size of your transaction log will depend on your environment and how often you back it up.

2)  There shouldn't be any risk with rebuilding Indexes through LID.  We rebuild our indexes on a bi-weekly basis, but do it through the database.

Hello Derek, you are really my good friend! Thanks for the reply again. I don't quite understand what you said.. where to check the recovery backup setting? I have forgotten how to do in SQL... we are running SQL2005. The transaction log file is like " xxxx_log.ldf " . This file has grown to 170G... I thought we can truncate it?

BTW, I haven't tried the security process levels design we discusssed last time. I do appreciate your thought-provoking suggestions and will let you know how it goes. Thanks again!

Right click on your database and click properties.  On the left where it says select a page, click options.  Your recovery model should be the second box down.  Next run this command:

DBCC

SQLPERF ( LOGSPACE )If your log space used percentage is very high, that probably means your transaction log file is not being backed up.  When you backup the transaction log file, it will commit the transaction to the database and free up that space in the log.  Then you can shrink the log file to a smaller size.  If you reboot your database does it take a long time for your database to come online? 

Sorry, that didn't paste correctly.  DBCC  SQLPERF(LOGSPACE)

Thank you Derek, Our recovery model is set to full. It seems that we didn't backup the transaction log. The precentage is 90%. I also noticed the last log bckup is 10/31/2008. Do we need to backup the transaction log right now? How to set it up? Also, you said it will commit the transaction to the database if we back up the log file. So if I back it up now, will it cause any issue since it will commit the transactions since 10/31/2008, right?

Yes. You need to back up your transaction log. I used the wrong terminology when I said commit. Your data is commited to your database. You just need to run a transaction log backup to free up the space in your log file. Once you free up the space in your log file you can shrink the log. If you right click on the database and go to tasks -> shrink -> files. You can select the transaction log and shrink it to a more reasonable size. You then want to setup a routine that will backup your transaction log on a regular basis.

You might want to check out this article link:
https://www.lawsonguru.co...y-in-SQL-Server.aspx

Derek, thank you for the information. I understand better now but there is still a lot of things I don't know. I guess I need to get a good database administration book. ( We don't have database administrator here and I just start this position this year. ) I have some questions:

(1) What is the difference between "backup set will expire in xx days" and "Set up the maintence for cleanup backup files aged xx weeks"? Are they doing the same things? Do they need to be sync? Like I cleanup backup file for aged 1 week and backup set will expire in 7 days? I guess backup expiration is for overwrite?

(2) One of our transaction log files is 170GB, if I run backup now, it will run out of HD space. I'm thinking to shrink the transaction logs and setup the backup plan afterward. I think it would be okay if I truncate it after we complete the full backup at night and no one is working at night. Right?

(3) I checked all our backup plans, it all append exisiting. I remember a better backup plan should have both overwrite and appended ... is it right?

Again, I appreciate your help very much. Thanks!

Yes you should backup your transaction log as soon as possible. Your transactions are committed to the database, I just used the wrong terminology. When you backup the transaction log, and your run the DBCC command you should see the available log space go down to a much lower number. You can then shrink the log. Right click on your database and click tasks -> shrink -> files and select the log file to shrink. You should also backup your log on a regular basis. This will help you if you ever need to do a point in time recovery.

Thanks John for the information and this forums..

I am having trouble with the forum today. Call me at 410 822-1000 ext 5564 and I can finish answering your SQL questions.