HELP! Transaction log huge, & I'm out of space!

I have several .ldf files for one of my databases and one
of them is taking up 23 gigs. I really dont need to save
the transaction logs so I would like to just delete them
and start with a smaller log file. We created another
log file on c: now just so we can keep things running but
that is going to fill up very soon as well.
Is there any ways I can just delete the all the
transaction logs and just create one that is very small?
When I try to delete them I get an error message that
says I cant delete the file because it is not empty.
When I take the database offline to delete the file I
cant do that either because I cant access the database.
Any suggestions?
Two things:
A) Put your databases into Simple Recovery mode. They're probably in
Full Recovery mode now, which is why your logs are growing so quickly.
B) To truncate the logs, run:
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
Then use sp_helpfile to get the file ID of the log file(s), and for
each, run:
DBCC SHRINKFILE(<FileID>, EMPTYFILE)
This should drop any space available back to the operating system.
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?
|||Hi,
Detach the database. Then delete the .ldf (rename it to be on the safe
side). Then reattach the database. It will create a new .ldf. Once this
is achieved, you can permanently delete that .ldf.
If you never need the .ldf, change the recovery model to simple.
HTH,
Bernie Yaeger
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?
|||You can't attach a database with multiple log files using
sp_attach_single_file_db. The method Adam suggested is the right approach
to remove unneeded log files.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:hsuYc.11778$Es2.6336186@.news4.srv.hcvlny.cv.n et...
> Hi,
> Detach the database. Then delete the .ldf (rename it to be on the safe
> side). Then reattach the database. It will create a new .ldf. Once this
> is achieved, you can permanently delete that .ldf.
> If you never need the .ldf, change the recovery model to simple.
> HTH,
> Bernie Yaeger
> "Groundid" <groundid@.comcast.net> wrote in message
> news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
>
|||Hi Dan,
If you either delete or rename (at the DOS level) the log file, you will not
be attaching a database with multiple log files.
Bernie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O7raWMjjEHA.3608@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> You can't attach a database with multiple log files using
> sp_attach_single_file_db. The method Adam suggested is the right approach
> to remove unneeded log files.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:hsuYc.11778$Es2.6336186@.news4.srv.hcvlny.cv.n et...
this
>
|||"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.n et...
> If you either delete or rename (at the DOS level) the log file, you will
not
> be attaching a database with multiple log files.
Bernie,
To understand what Dan is talking about (and to get a glimpse into how I
once destroyed a weeks' worth of work and decided to never again manually
delete the log file):
Create a database with two log files. Detach it. Delete one (or both)
of the log files. Now try to re-attach it.
|||Hi groundid,
Dont delete, just shrink the file. Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Idetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
4. Check the current file size by execution
DBCC SQLPERF(LOGSPACE)
Have a look into the below article on shrink file,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...b;en-us;272318
Note:
Please perform a full database backup after performing the above steps, as
well as schedule a transaction log backup frequently based on ur
requirement.
Thanks
Hari
MCDBA
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?
|||Bernie, try the script below.
CREATE DATABASE TwoLogFiles ON
(NAME='TwoLogFiles',
FILENAME='C:\TwoLogFiles.mdf')
LOG ON
(NAME='TwoLogFiles_Log1',
FILENAME='C:\TwoLogFiles_Log1.ldf'),
(NAME='TwoLogFiles_Log2',
FILENAME='C:\TwoLogFiles_Log2.ldf')
EXEC sp_detach_db 'TwoLogFiles'
EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
EXEC sp_attach_db
'TwoLogFiles',
'C:\TwoLogFiles.mdf',
'C:\TwoLogFiles_Log1.ldf'
EXEC sp_attach_single_file_db
'TwoLogFiles',
'C:\TwoLogFiles.mdf'
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.n et...
> Hi Dan,
> If you either delete or rename (at the DOS level) the log file, you will
> not
> be attaching a database with multiple log files.
> Bernie
>
|||Hi Dan, Adam,
Why are you guys talking about 2 transaction logs? This guy has one.
Here's a process I've used countless times and haven't experienced any
problem:
1.. right click the database in em;
2.. select all tasks;
3.. select detach database;
4.. proceed to detach;
5.. delete the .ldf at the DOS level;
6.. right click the 'databases' line beneath the server in em; all tasks;
attach the .mdf (browse for it to the appropriate sub);
7.. it will automatically create its own blank transaction log;
I am using the simple recovery model, but I don't know if this makes any
difference.
Now tell me why this doesn't work in your opinion and explain to me why it
always works for me (SQL Server 2000).
Bernie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uvhweXkjEHA.536@.TK2MSFTNGP11.phx.gbl...
> Bernie, try the script below.
> CREATE DATABASE TwoLogFiles ON
> (NAME='TwoLogFiles',
> FILENAME='C:\TwoLogFiles.mdf')
> LOG ON
> (NAME='TwoLogFiles_Log1',
> FILENAME='C:\TwoLogFiles_Log1.ldf'),
> (NAME='TwoLogFiles_Log2',
> FILENAME='C:\TwoLogFiles_Log2.ldf')
> EXEC sp_detach_db 'TwoLogFiles'
> EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
> EXEC sp_attach_db
> 'TwoLogFiles',
> 'C:\TwoLogFiles.mdf',
> 'C:\TwoLogFiles_Log1.ldf'
> EXEC sp_attach_single_file_db
> 'TwoLogFiles',
> 'C:\TwoLogFiles.mdf'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.n et...
>
|||Hi Dan, Adam,
Why are you guys talking about 2 transaction logs? This guy has one.
Here's a process I've used countless times and haven't experienced any
problem:
1.. right click the database in em;
2.. select all tasks;
3.. select detach database;
4.. proceed to detach;
5.. delete the .ldf at the DOS level;
6.. right click the 'databases' line beneath the server in em; all tasks;
attach the .mdf (browse for it to the appropriate sub);
7.. it will automatically create its own blank transaction log;
I am using the simple recovery model, but I don't know if this makes any
difference.
Now tell me why this doesn't work in your opinion and explain to me why it
always works for me (SQL Server 2000).
Bernie
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23k44GyjjEHA.1136@.tk2msftngp13.phx.gbl...
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.n et...
> not
> Bernie,
> To understand what Dan is talking about (and to get a glimpse into how
I
> once destroyed a weeks' worth of work and decided to never again manually
> delete the log file):
> Create a database with two log files. Detach it. Delete one (or
both)
> of the log files. Now try to re-attach it.
>