| Author |
Topic |
|
ruan4u
Posting Yak Master
132 Posts |
 Posted - 2004-08-27 : 12:29:12
|
| My transaction log file(1.2GB) is bigger than the data file(900MB).How can i reduce this to like 100MB.Shouldnt the transaction log flush all data after the transaction is done.pls help |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-27 : 12:43:53
|
| It flushes it so there is free space in the file, but it does not shrink it. And that's if your recovery model is set to SIMPLE or if it is FULL you are performing tlog backups.Tara |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-08-27 : 13:57:14
|
| are you running out of space on disk? If not, leave the tlog at it's current size. it grew that large for a reason, if you shrink it down to 100MB, it will probably grow to 1.2GB again later.-ec |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-08-27 : 13:58:01
|
| So if the t-log is showing 1.2GB there is no way i can reduce the size? |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-08-27 : 13:58:59
|
| we were running our data conversion.thats all.The conversion is done, but the T-log file is 1.2GB |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-27 : 14:04:10
|
| You can run DBCC SHRINKDATABASE or DBCC SHRINKFILE. Look up the syntax in BOL.Tara |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-08-27 : 14:11:46
|
| i went to shrink database from enterprise.It gave me an option to shrink upto 1.1 GB.How can i reduce it even further.pls help |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-08-27 : 14:21:39
|
quote:
Originally posted by ruan4u
i went to shrink database from enterprise.It gave me an option to shrink upto 1.1 GB.How can i reduce it even further.pls help
DBCC SHRINKDATABASE or DBCC SHRINKFILE |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-27 : 14:29:53
|
| You probably don't have free space in the file. What is your database recovery model set to?Tara |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-08-27 : 14:34:46
|
| i just set it to full.it was bulk load. i did DBCC SHRINKFILE (convert_log, 100) and it returned meuid FileId Cur size Min Size used pg Est Pg20 2 174072 128 174072 128 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-27 : 14:36:16
|
| So are you performing regular tlog backups? You have to with FULL and BULK LOGGED recovery models.Tara |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-08-27 : 14:38:06
|
| no i have never backed this database becuase this is on development database.We created a database called convert and did our conversion.never did backup.should i create a backup using maintenance plan? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-27 : 14:39:54
|
| For development, the recovery model should be set to SIMPLE. And yes you should have full backups setup when using SIMPLE recovery model. For BULK LOGGED and FULL recovery models, you should have full backups setup and tlog backups setup.Tara |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-08-27 : 15:11:55
|
| i ran the backup.Its still running into 1.4Gb.my data log is 900MB. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-27 : 15:13:03
|
| Which command did you run? Did you change the recovery model to SIMPLE?Tara |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-08-27 : 15:18:23
|
| i ran it through maitenance plan.No,it was in full.should i make it simple and run the maintenance plan again? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-27 : 15:19:53
|
| Yes change it to simple. And no you don't need to back it up with a maintenance plan. You should setup a full backup job though in case developers ever need it restored, but this is not relevant to flushing out the tlog and shrinking it.Tara |
|
|
|
|
reduce size for T-log file