Making the Log file small

backups shrink the logical space, not physical...
try backup log with the no_log clause, then shrink the file
Tina wrote:
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do
a
> transaction backup that it will make it small. That did give it 90% free
> space. So then I did a shrink (all three types) but that did not shrink i
t
> at all. I did a database shrink and that did nothing.
> Then I read that if I detach it, delete the log file (I just moved it), an
d
> reattach it that a new small log file will be built. But, the reattach
> would not work saying that the log file is missing. So I had to move it
> back and now I still have this huge log file.
> How can I make it small?
> Thanks,
> TI'm using ss2005 and Management Studio...
I have this huge log file and I want it to be small. I read that if I do a
transaction backup that it will make it small. That did give it 90% free
space. So then I did a shrink (all three types) but that did not shrink it
at all. I did a database shrink and that did nothing.
Then I read that if I detach it, delete the log file (I just moved it), and
reattach it that a new small log file will be built. But, the reattach
would not work saying that the log file is missing. So I had to move it
back and now I still have this huge log file.
How can I make it small?
Thanks,
T|||backups shrink the logical space, not physical...
try backup log with the no_log clause, then shrink the file
Tina wrote:
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do
a
> transaction backup that it will make it small. That did give it 90% free
> space. So then I did a shrink (all three types) but that did not shrink i
t
> at all. I did a database shrink and that did nothing.
> Then I read that if I detach it, delete the log file (I just moved it), an
d
> reattach it that a new small log file will be built. But, the reattach
> would not work saying that the log file is missing. So I had to move it
> back and now I still have this huge log file.
> How can I make it small?
> Thanks,
> T|||After you backup your log file, you can shrink the file with DBCC
SHRINKFILE. For example:
DBCC SHRINKFILE('MyDatabase_Log', 100)
See the SQL Server Books Online for more info on DBCC SHRINKFILE.
The most common cause of out-of-control log files is failure to implement a
backup/recovery plan. If you choose to run the database in the
FULL/BULK_LOGGED recovery model, you will need to do regular transaction log
backups to keep the log size reasonable and provide the means to minimize
data loss after a failure. You can also run the database in the SIMPLE
recovery model so that you don't need to bother with log backups. However,
your only recovery recourse in the SIMPLE model is to restore from the last
full backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:Oj5uSqvmGHA.4772@.TK2MSFTNGP04.phx.gbl...
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do
> a transaction backup that it will make it small. That did give it 90%
> free space. So then I did a shrink (all three types) but that did not
> shrink it at all. I did a database shrink and that did nothing.
> Then I read that if I detach it, delete the log file (I just moved it),
> and reattach it that a new small log file will be built. But, the
> reattach would not work saying that the log file is missing. So I had to
> move it back and now I still have this huge log file.
> How can I make it small?
> Thanks,
> T
>|||After you backup your log file, you can shrink the file with DBCC
SHRINKFILE. For example:
DBCC SHRINKFILE('MyDatabase_Log', 100)
See the SQL Server Books Online for more info on DBCC SHRINKFILE.
The most common cause of out-of-control log files is failure to implement a
backup/recovery plan. If you choose to run the database in the
FULL/BULK_LOGGED recovery model, you will need to do regular transaction log
backups to keep the log size reasonable and provide the means to minimize
data loss after a failure. You can also run the database in the SIMPLE
recovery model so that you don't need to bother with log backups. However,
your only recovery recourse in the SIMPLE model is to restore from the last
full backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:Oj5uSqvmGHA.4772@.TK2MSFTNGP04.phx.gbl...
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do
> a transaction backup that it will make it small. That did give it 90%
> free space. So then I did a shrink (all three types) but that did not
> shrink it at all. I did a database shrink and that did nothing.
> Then I read that if I detach it, delete the log file (I just moved it),
> and reattach it that a new small log file will be built. But, the
> reattach would not work saying that the log file is missing. So I had to
> move it back and now I still have this huge log file.
> How can I make it small?
> Thanks,
> T
>|||http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:Oj5uSqvmGHA.4772@.TK2MSFTNGP04.phx.gbl...
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do
a transaction backup
> that it will make it small. That did give it 90% free space. So then I d
id a shrink (all three
> types) but that did not shrink it at all. I did a database shrink and tha
t did nothing.
> Then I read that if I detach it, delete the log file (I just moved it), an
d reattach it that a new
> small log file will be built. But, the reattach would not work saying tha
t the log file is
> missing. So I had to move it back and now I still have this huge log fil
e.
> How can I make it small?
> Thanks,
> T
>|||http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:Oj5uSqvmGHA.4772@.TK2MSFTNGP04.phx.gbl...
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do
a transaction backup
> that it will make it small. That did give it 90% free space. So then I d
id a shrink (all three
> types) but that did not shrink it at all. I did a database shrink and tha
t did nothing.
> Then I read that if I detach it, delete the log file (I just moved it), an
d reattach it that a new
> small log file will be built. But, the reattach would not work saying tha
t the log file is
> missing. So I had to move it back and now I still have this huge log fil
e.
> How can I make it small?
> Thanks,
> T
>sql