| Author |
Topic |
|
sqlserverdbam
Yak Posting Veteran
54 Posts |
 Posted - 2008-10-30 : 11:06:30
|
| Hi,Currently one of database transaction log file is about 30 Gb, Can I shrinking and bring back to 10Gb or any thoughts. What would be impact of shrinking of transactional log file?Thanks |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-30 : 12:13:38
|
| What's your DB recovery model? Are you backing up tran log frequently? |
 |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-10-30 : 12:17:22
|
| you can get better answer onhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112767 |
|
|
|
sqlserverdbam
Yak Posting Veteran
54 Posts |
Posted - 2008-10-30 : 12:18:46
|
| I'm taking FULL BACKUP everyday not taking transactional log backup.What's your DB recovery model? :FULL RECOVERY MODELAre you backing up tran log frequently?:Nope |
|
|
|
sqlserverdbam
Yak Posting Veteran
54 Posts |
Posted - 2008-10-30 : 13:19:16
|
| Question:When I created DB the size of log was 10mb now its grown to 40Gb.I'm having FULL recovery model.Every night I'm taking backup.Pl correct me what I'm planning to do.-take a backup of log and db-DBCC SHRINKFILE (db_dbname, 5GB)thanks |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-10-30 : 14:12:35
|
| If you have the database in Full Recovery mode, you should be taking frequent transaction log backups. That will keep the transaction log from growing so large. The transaction log will just keep growing until you do a transaction log backup.I recommend that you schedule a transaction log backup job to run every 15 minutes. If you do not know how to do that, use the SQL Server Maintenance Plan Wizard to set it up.CODO ERGO SUM |
|
|
|
sqlserverdbam
Yak Posting Veteran
54 Posts |
Posted - 2008-10-30 : 16:08:42
|
| Can I've transaction log backup with diffrent name?Like at 10am prod.trn<DDMMYYY_MISS>at 11am prod.trn<DDMMYYY_MISS>at 12Noon prod.trn<DDMMYYY_MISS> like that.Please let me know.thanks |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-10-30 : 16:24:57
|
quote:
Originally posted by sqlserverdbam
Can I've transaction log backup with diffrent name?Like at 10am prod.trn<DDMMYYY_MISS>at 11am prod.trn<DDMMYYY_MISS>at 12Noon prod.trn<DDMMYYY_MISS> like that.Please let me know.thanks
The maintenance plan takes care of that for both backups and transaction log backups.CODO ERGO SUM |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-10-31 : 02:20:59
|
| You can shrink back your log file, after taking full backup, by detaching your database and removing operating system log file (ldf file) and then re attaching your mdf file only, it will create a new ldf file. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-01 : 12:21:41
|
quote:
Originally posted by lionofdezert
You can shrink back your log file, after taking full backup, by detaching your database and removing operating system log file (ldf file) and then re attaching your mdf file only, it will create a new ldf file.
Donot do this as it might lead your database corrupt. |
|
|
|
|
Transaction log truncation