SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 reduce size for T-log file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ruan4u
Posting Yak Master

USA
132 Posts

Posted - 08/27/2004 :  12:29:12  Show Profile  Reply with Quote
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

USA
37287 Posts

Posted - 08/27/2004 :  12:43:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/27/2004 :  13:57:14  Show Profile  Reply with Quote
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
Go to Top of Page

ruan4u
Posting Yak Master

USA
132 Posts

Posted - 08/27/2004 :  13:58:01  Show Profile  Reply with Quote
So if the t-log is showing 1.2GB there is no way i can reduce the size?
Go to Top of Page

ruan4u
Posting Yak Master

USA
132 Posts

Posted - 08/27/2004 :  13:58:59  Show Profile  Reply with Quote
we were running our data conversion.thats all.The conversion is done, but the T-log file is 1.2GB
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 08/27/2004 :  14:04:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
You can run DBCC SHRINKDATABASE or DBCC SHRINKFILE. Look up the syntax in BOL.

Tara
Go to Top of Page

ruan4u
Posting Yak Master

USA
132 Posts

Posted - 08/27/2004 :  14:11:46  Show Profile  Reply with Quote
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
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/27/2004 :  14:21:39  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 08/27/2004 :  14:29:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
You probably don't have free space in the file. What is your database recovery model set to?

Tara
Go to Top of Page

ruan4u
Posting Yak Master

USA
132 Posts

Posted - 08/27/2004 :  14:34:46  Show Profile  Reply with Quote
i just set it to full.it was bulk load. i did DBCC SHRINKFILE (convert_log, 100) and it returned me
uid FileId Cur size Min Size used pg Est Pg
20 2 174072 128 174072 128
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 08/27/2004 :  14:36:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
So are you performing regular tlog backups? You have to with FULL and BULK LOGGED recovery models.

Tara
Go to Top of Page

ruan4u
Posting Yak Master

USA
132 Posts

Posted - 08/27/2004 :  14:38:06  Show Profile  Reply with Quote
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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 08/27/2004 :  14:39:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

ruan4u
Posting Yak Master

USA
132 Posts

Posted - 08/27/2004 :  15:11:55  Show Profile  Reply with Quote
i ran the backup.Its still running into 1.4Gb.my data log is 900MB.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 08/27/2004 :  15:13:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
Which command did you run? Did you change the recovery model to SIMPLE?

Tara
Go to Top of Page

ruan4u
Posting Yak Master

USA
132 Posts

Posted - 08/27/2004 :  15:18:23  Show Profile  Reply with Quote
i ran it through maitenance plan.No,it was in full.should i make it simple and run the maintenance plan again?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 08/27/2004 :  15:19:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000