Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
limone
Starting Member
4 Posts |
 Posted - 2011-08-09 : 05:46:59
|
| Hi Everyone,I am new to SQL Server 2008. Please give me a advice for Administration of MSSQL.In my MS SQL Server, there are 50 databases. I want to backup these 50 databases, and to delete the T-log, and to compress it by the task.The backup and compression were able to be achieved by the task of the maintenance plan. I set to do the backup of the databases and compression every week. However, I do not understand a way of the deletion of the T-log. How should I do this? The restoration model of the database is Full. The database can be created or deleted by users every day. So, the query of using database name can not support it all.Can I make query for "All databases"?Thanks, |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
limone
Starting Member
4 Posts |
Posted - 2011-08-09 : 20:40:40
|
| Hi Gail,Thank you for your advice! But I do not know what to do even after reading your links. In addition to the full database backup, should I back up the T-log by task? Please accept my apologies for the amateur's question. |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-08-09 : 23:07:15
|
| Whether or not you backup the transaction log will be determined by the business requirements for that database. If the business can afford to recover to the latest full backup and lose up to 24 hours (or more, depending on where the backup file is stored) - then you would use the simple recovery model and schedule daily full backups.If the business cannot afford that amount of data loss, then you would backup the transaction logs on a frequent enough basis to allow point in time recovery. For example, if the business decides that a loss of 15 minutes worth of data is acceptable - you would schedule transaction log backups at least every 15 minutes.If your databases are set to either full or bulk_logged recovery model, you must perform frequent transaction log backups. If you do not, then the transaction logs will continually grow until they fill the drive and your system comes to a halt.In addition to Gail's warning - you should not schedule a shrinkdatabase or shrinkfile operation on any database. Doing so will just cause the files to grow again and will also cause performance issues.If you have users that will be creating and dropping databases (why????), then I would recommend changing the recovery model of the 'model' database to simple so those databases will be created by default in simple recovery. Then, you can schedule a full database backup on all user databases daily. Note: the default task may not pick up new databases, so test it out. If it doesn't you will need to write a script to loop through all user databases and perform the backup.Jeff |
|
|
|
limone
Starting Member
4 Posts |
Posted - 2011-08-10 : 01:34:40
|
| Hi Jeff,I am using MSSQL for the business. Each team in my company is using a database different in each project. The project newly is started, and ends at any time.I understood that I should backup the transaction log by the task. However, I still have one doubt.Suppose I have a database with the ldf file size of 200MB.Now, I backed up the transaction log for the database. But the ldf file for the database size does not decrease.When does this become small? Does it become small at some timing? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-10 : 02:46:48
|
| Only a shrinkfile shrinks the log, but that is not something you want to run. The space in the log will be reused.--Gail ShawSQL Server MVP |
|
|
|
limone
Starting Member
4 Posts |
Posted - 2011-08-10 : 04:19:07
|
| Hi Gail,I finally understood!! I will firmly back up the transaction log.Many many thx!! |
|
|
|
|
|
|
|
|
Bulk Transaction log Flush