HELP! LOG file has grown too large

I'm a newbie so please bear with me.
We have a database where the MDF is 2.5 GB in size but the LDF has grown to
45GB (the maintenance plan had included a complete backup but no Transaction
Log Backup and I presume this is why the log file has been bloating out of
proportion during the last months)
Now the disk is approaching full capacity and I'm worried about the
consequences.
In order to remedy this situation I would like to do the following:
Do a full backup of the database, detach the database, delete the log file
and then reattach the database to create a new normal sized logfile.
Can someone tell me if this is the best way to achieve this without
compromising the database?
I've tried DBCC SHRINKFILE (database_log, 2) in query analyser but get the
error message "Incorrect syntax near '_log' " and don't know how to work
round this.
I've also tried selecting 'shrink database...' in Enterprise Manager but
that doesn't make any difference to the log file size.
I know that in MS Exchange, you can get rid of all the log files simply by
performing a full backup of the exchange database. Doesn't it work the same
in SQL server?
Some downtime for the database is permissable on this network.
any help is greatly appreciated
ericBackup Transaction Log. with Tuncate Only param
And try to schedule Log Backup at least once a day
"Eric" <eric@.anon.com> wrote in message
news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm a newbie so please bear with me.
> We have a database where the MDF is 2.5 GB in size but the LDF has grown
to
> 45GB (the maintenance plan had included a complete backup but no
Transaction
> Log Backup and I presume this is why the log file has been bloating out of
> proportion during the last months)
> Now the disk is approaching full capacity and I'm worried about the
> consequences.
> In order to remedy this situation I would like to do the following:
> Do a full backup of the database, detach the database, delete the log file
> and then reattach the database to create a new normal sized logfile.
> Can someone tell me if this is the best way to achieve this without
> compromising the database?
> I've tried DBCC SHRINKFILE (database_log, 2) in query analyser but get the
> error message "Incorrect syntax near '_log' " and don't know how to work
> round this.
> I've also tried selecting 'shrink database...' in Enterprise Manager but
> that doesn't make any difference to the log file size.
> I know that in MS Exchange, you can get rid of all the log files simply by
> performing a full backup of the exchange database. Doesn't it work the
same
> in SQL server?
> Some downtime for the database is permissable on this network.
> any help is greatly appreciated
> eric
>|||If your database in FULL RECOVERY so
perform BACKUP LOG file and then do DBCC SHRINKFILE(...) otherwise detach
the database and remove the log ans re-attach it
"Eric" <eric@.anon.com> wrote in message
news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm a newbie so please bear with me.
> We have a database where the MDF is 2.5 GB in size but the LDF has grown
to
> 45GB (the maintenance plan had included a complete backup but no
Transaction
> Log Backup and I presume this is why the log file has been bloating out of
> proportion during the last months)
> Now the disk is approaching full capacity and I'm worried about the
> consequences.
> In order to remedy this situation I would like to do the following:
> Do a full backup of the database, detach the database, delete the log file
> and then reattach the database to create a new normal sized logfile.
> Can someone tell me if this is the best way to achieve this without
> compromising the database?
> I've tried DBCC SHRINKFILE (database_log, 2) in query analyser but get the
> error message "Incorrect syntax near '_log' " and don't know how to work
> round this.
> I've also tried selecting 'shrink database...' in Enterprise Manager but
> that doesn't make any difference to the log file size.
> I know that in MS Exchange, you can get rid of all the log files simply by
> performing a full backup of the exchange database. Doesn't it work the
same
> in SQL server?
> Some downtime for the database is permissable on this network.
> any help is greatly appreciated
> eric
>|||> Can someone tell me if this is the best way to achieve this without
> compromising the database?
No! You risk that the database is corrupt (suspect) when you start SQL Serve
r. It you aren't
prepared to restore from a backup, don't do this!!!
I have an article about shrink database files. In about the middle of this a
rticle, I have some
links to KB articles etc. Read them. They have all the information you need.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

> I know that in MS Exchange, you can get rid of all the log files simply by
> performing a full backup of the exchange database. Doesn't it work the sam
e
> in SQL server?
No. A database backup does not empty the transaction log file(s).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric" <eric@.anon.com> wrote in message news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...seagreen">
> I'm a newbie so please bear with me.
> We have a database where the MDF is 2.5 GB in size but the LDF has grown t
o
> 45GB (the maintenance plan had included a complete backup but no Transacti
on
> Log Backup and I presume this is why the log file has been bloating out of
> proportion during the last months)
> Now the disk is approaching full capacity and I'm worried about the
> consequences.
> In order to remedy this situation I would like to do the following:
> Do a full backup of the database, detach the database, delete the log file
> and then reattach the database to create a new normal sized logfile.
> Can someone tell me if this is the best way to achieve this without
> compromising the database?
> I've tried DBCC SHRINKFILE (database_log, 2) in query analyser but get the
> error message "Incorrect syntax near '_log' " and don't know how to work
> round this.
> I've also tried selecting 'shrink database...' in Enterprise Manager but
> that doesn't make any difference to the log file size.
> I know that in MS Exchange, you can get rid of all the log files simply by
> performing a full backup of the exchange database. Doesn't it work the sam
e
> in SQL server?
> Some downtime for the database is permissable on this network.
> any help is greatly appreciated
> eric
>|||But if I execute 'BACKUP LOG databasename WITH TRUNCATE_ONLY' in query
analyser, I get a 'incorrect syntax near databasename' error message.
any ideas?
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:udyGj#kwEHA.4028@.TK2MSFTNGP15.phx.gbl...

> Backup Transaction Log. with Tuncate Only param
> And try to schedule Log Backup at least once a day
>
> "Eric" <eric@.anon.com> wrote in message
> news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...|||BACKUP LOG ecatalog WITH TRUNCATE_ONLY
I just Try this and it works fine...
What is ur SQL version.. ( it works fine with SQL 7 and 2000)
"Eric" <eric@.anon.com> wrote in message
news:%23MZFxGlwEHA.1192@.tk2msftngp13.phx.gbl...
> But if I execute 'BACKUP LOG databasename WITH TRUNCATE_ONLY' in query
> analyser, I get a 'incorrect syntax near databasename' error message.
> any ideas?
>
> "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> news:udyGj#kwEHA.4028@.TK2MSFTNGP15.phx.gbl...
>
>|||Thanks Tibor,
but when I perform a DBCC SHRINKFILE I just get an error message 'incorrect
syntax near...'
What am I doing wrong?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OmiAJBlwEHA.3416@.TK2MSFTNGP09.phx.gbl...
> No! You risk that the database is corrupt (suspect) when you start SQL
Server. It you aren't
> prepared to restore from a backup, don't do this!!!
> I have an article about shrink database files. In about the middle of this
article, I have some
> links to KB articles etc. Read them. They have all the information you
need.
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
by[vbcol=seagreen]
same[vbcol=seagreen]
> No. A database backup does not empty the transaction log file(s).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eric" <eric@.anon.com> wrote in message
news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
Transaction[vbcol=seagreen]
of[vbcol=seagreen]
file[vbcol=seagreen]
the[vbcol=seagreen]
by[vbcol=seagreen]
same[vbcol=seagreen]
>|||SQL2000 with SP3
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OjxQsNlwEHA.1976@.TK2MSFTNGP09.phx.gbl...
> BACKUP LOG ecatalog WITH TRUNCATE_ONLY
> I just Try this and it works fine...
> What is ur SQL version.. ( it works fine with SQL 7 and 2000)
>
> "Eric" <eric@.anon.com> wrote in message
> news:%23MZFxGlwEHA.1192@.tk2msftngp13.phx.gbl...
>|||What is your database compatibility level?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric" <eric@.anon.com> wrote in message news:OMxCiPlwEHA.2196@.TK2MSFTNGP14.phx.gbl...seagreen">
> Thanks Tibor,
> but when I perform a DBCC SHRINKFILE I just get an error message 'incorrec
t
> syntax near...'
> What am I doing wrong?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:OmiAJBlwEHA.3416@.TK2MSFTNGP09.phx.gbl...
> Server. It you aren't
> article, I have some
> need.
> by
> same
> news:OGaJ34kwEHA.2676@.TK2MSFTNGP12.phx.gbl...
> to
> Transaction
> of
> file
> the
> by
> same
>|||I agree with Melih,
Please post exact commands you try to execute and exact error message.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric" <eric@.anon.com> wrote in message news:elU3HvlwEHA.4028@.TK2MSFTNGP15.phx.gbl...seagreen">
> 80
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:#pRS$olwEHA.392@.TK2MSFTNGP12.phx.gbl...
> news:OMxCiPlwEHA.2196@.TK2MSFTNGP14.phx.gbl...
> 'incorrect
> in
> this
> simply
> the
> grown
> out
> log
> get
> work
> but
> simply
> the
>sql