How to shrink 38 gig .ldf file

I have Disk Xtender 2000 which was made by OTG Software , Legato and now EMC. I have an NT 4.0 PC with Microsoft SQL 2000. I have a drive space problem and need to shrink a 38 gig .ldf file called OTG03.ldf I also have a 2 gig .mdf file called OTG03.mdf How can I shrink this .ldf file. I'm not a DBA so being specific is greatly appreciated.I got this from a download from a while back. It says 7.0 in the header, but has been modified for SQL 2000. It hads worked for me many, many times.

1. Create the proc in QA
2. Change to the database in which you want to shrink the log file and then execute the proc.

** WARNING **
Use at your own risk ... no warranty applies ... test it first on a database you can afford to trash and restore (the old liability lawyerese)


use master
go
if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
go
create proc sp_force_shrink_log
------------------------
-- Purpose: Shrink transaction log of the current database in SQL Server 7.0.
-- Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
-- zanevsky@.azdatabases.com
------------------------
@.target_percent tinyint = 0,
@.target_size_MB int = 10,
@.max_iterations int = 1000,
@.backup_log_opt nvarchar(1000) = 'with truncate_only'
as
set nocount on
declare @.db sysname,
@.last_row int,
@.log_size decimal(15,2),
@.unused1 decimal(15,2),
@.unused decimal(15,2),
@.shrinkable decimal(15,2),
@.iteration int,
@.file_max int,
@.file int,
@.fileid varchar(5)
select @.db = db_name(),
@.iteration = 0
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
---- ------- ------- ---- ---- -- --------
2 1245184 8192 925963 0 128 0
*/
create table #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateLSN varchar(32)
)
create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo, StartOffset )
create table #logfiles ( id int identity(1,1), fileid varchar(5) not null )
insert #logfiles ( fileid ) select convert( varchar, fileid ) from sysfiles where status & 0x40 = 0x40
select @.file_max = @.@.rowcount
if object_id( 'table_to_force_shrink_log' ) is null
exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN ) exec ( 'dbcc loginfo' )
select @.last_row = @.@.rowcount
select @.log_size = sum( FileSize ) / 1048576.00,
@.unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@.shrinkable = sum( case when id < @.last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select @.unused1 = @.unused -- save for later
select 'iteration' = @.iteration,
'log size, MB' = @.log_size,
'unused log, MB' = @.unused,
'shrinkable log, MB' = @.shrinkable,
'shrinkable %' = convert( decimal(6,2), @.shrinkable * 100 / @.log_size )
while @.shrinkable * 100 / @.log_size > @.target_percent
and @.shrinkable > @.target_size_MB
and @.iteration < @.max_iterations begin
select @.iteration = @.iteration + 1 -- this is just a precaution
exec( 'insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log')
select @.file = 0
while @.file < @.file_max begin
select @.file = @.file + 1
select @.fileid = fileid from #logfiles where id = @.file
exec( 'dbcc shrinkfile( ' + @.fileid + ' )' )
end
exec( 'backup log [' + @.db + '] ' + @.backup_log_opt )
truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN ) exec ( 'dbcc loginfo' )
select @.last_row = @.@.rowcount
select @.log_size = sum( FileSize ) / 1048576.00,
@.unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@.shrinkable = sum( case when id < @.last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select 'iteration' = @.iteration,
'log size, MB' = @.log_size,
'unused log, MB' = @.unused,
'shrinkable log, MB' = @.shrinkable,
'shrinkable %' = convert( decimal(6,2), @.shrinkable * 100 / @.log_size )
end
if @.unused1 < @.unused
select 'After ' + convert( varchar, @.iteration ) +
' iterations the unused portion of the log has grown from ' +
convert( varchar, @.unused1 ) + ' MB to ' +
convert( varchar, @.unused ) + ' MB.'
union all
select 'Since the remaining unused portion is larger than 10 MB,' where @.unused > 10
union all
select 'you may try running this procedure again with a higher number of iterations.' where @.unused > 10
union all
select 'Sometimes the log would not shrink to a size smaller than several Megabytes.' where @.unused <= 10
else
select 'It took ' + convert( varchar, @.iteration ) +
' iterations to shrink the unused portion of the log from ' +
convert( varchar, @.unused1 ) + ' MB to ' +
convert( varchar, @.unused ) + ' MB'
exec( 'drop table table_to_force_shrink_log' )
go|||Mike,

Backing up your database should shrink your transaction log for you but if you would like to force a shrink on just the ldf file you can use the following:

DBCC SHRINKFILE (OTG03.ldf)

Hope this helps.|||Mike,

Just a clarification, you should replace "OTG03.ldf" with the sql file name. You can find this by right clicking on the database going to properties and on the "Transaction Log" tab.|||I tried DBCC SHRINKFILE (OTG03Log) and got a syntax error.
and also I tried DBCC SHRINKFILE (OTG03.ldf) and got a syntax error.|||I tried DBCC SHRINKFILE (OTG03Log) and got a syntax error.
and also I tried DBCC SHRINKFILE (OTG03.ldf) and got a syntax error.

Use exec sp_helpfile in the database to find the correct value to use with
DBCC SHRINKFILE (?)

Tim S|||Mike,

DBCC SHRINKFILE (OTG03Log) should do the trick for you. Make sure you are running it on your OTG03 database.

Try this:

USE OTG03
GO
DBCC SHRINKFILE (OTG03Log)
GO|||Well ..
If you have no need for the transaction log then you can set the recovery mode to simple and use the following commands
------------
backup log dbname with no_log
go
use OTG03
go
DBCC SHRINKFILE (OTG03Log)
GO

------------

This should do the trick ...
Or for another option ... open the EM ... right click on database name ... select all tasks ... shrink database and then explicitly shrink the log file from there ...

In case you get any error .. revert back with the error text ...

How to 'share drive', SCSI HD's on servers

Rodney is correct, your SAN or DAS solution must be in the
Windows Catalog for use on a cluster, but it's more than
just getting disks. How you carve them up is just as
important, because, for example, if you create one LUN but
plan to put multiple drives on it, that won't work.
You need to plan your disk layout carefully.
I am sorry for my lack of understanding here, but let's say if I have two
"regular" Compaq ML370, can I just add two more disks on each server and
let's say create a Raid1 and use that as the 'quorum' ? I've seen the list
of devices Rod listed OK, but I still don't understand how to configure the
disks on my servers to be used in a cluster (assume they are part of the MS
HCL).
"Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message
news:575601c47470$953e6530$a601280a@.phx.gbl...
> Rodney is correct, your SAN or DAS solution must be in the
> Windows Catalog for use on a cluster, but it's more than
> just getting disks. How you carve them up is just as
> important, because, for example, if you create one LUN but
> plan to put multiple drives on it, that won't work.
> You need to plan your disk layout carefully.
|||I think you need to read this
http://www.microsoft.com/downloads/d...displaylang=en
its a step by step guide on cluster installations
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"Marlon Brown" <marlon_brown@.hotmail.com> wrote in message
news:OaEfxULdEHA.3916@.TK2MSFTNGP11.phx.gbl...
> I am sorry for my lack of understanding here, but let's say if I have two
> "regular" Compaq ML370, can I just add two more disks on each server and
> let's say create a Raid1 and use that as the 'quorum' ? I've seen the
list
> of devices Rod listed OK, but I still don't understand how to configure
the
> disks on my servers to be used in a cluster (assume they are part of the
MS
> HCL).
>
> "Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message
> news:575601c47470$953e6530$a601280a@.phx.gbl...
>
|||(...)
Actually it is what I didn't understand: From the text below I had the
impresson that "external disk storage unit connected to all computers" I
still can't see how my regular SCSI disks existing on both Compaq servers
can be configured as shared disks. It also says that the controller cannot
be the one used by the system drive...
Shared Disk Requirements:
An HCL-approved external disk storage unit connected to all
computers. This will be used as the clustered shared disk. Some type of a
hardware redundant array of independent disks (RAID) is recommended.
All shared disks, including the quorum disk, must be physically
attached to a shared bus.
Note: The requirement above does not hold true for Majority Node Set (MNS)
clusters, which are not covered in this guide.
Shared disks must be on a different controller then the one used
by the system drive.
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:uzdl$BMdEHA.384@.TK2MSFTNGP10.phx.gbl...
> I think you need to read this
>
http://www.microsoft.com/downloads/d...displaylang=en[vbcol=seagreen]
> its a step by step guide on cluster installations
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> "Marlon Brown" <marlon_brown@.hotmail.com> wrote in message
> news:OaEfxULdEHA.3916@.TK2MSFTNGP11.phx.gbl...
two
> list
> the
> MS
>
|||Marlon, you have all the facts already. You need to buy or use an external
storage, on a different controller.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"Marlon Brown" <marlon_brown@.hotmail.com> wrote in message
news:uhsppKOdEHA.1652@.TK2MSFTNGP09.phx.gbl...
> (...)
> Actually it is what I didn't understand: From the text below I had the
> impresson that "external disk storage unit connected to all computers" I
> still can't see how my regular SCSI disks existing on both Compaq servers
> can be configured as shared disks. It also says that the controller cannot
> be the one used by the system drive...
>
> Shared Disk Requirements:
> An HCL-approved external disk storage unit connected to all
> computers. This will be used as the clustered shared disk. Some type of a
> hardware redundant array of independent disks (RAID) is recommended.
> All shared disks, including the quorum disk, must be physically
> attached to a shared bus.
> Note: The requirement above does not hold true for Majority Node Set (MNS)
> clusters, which are not covered in this guide.
> Shared disks must be on a different controller then the one used
> by the system drive.
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:uzdl$BMdEHA.384@.TK2MSFTNGP10.phx.gbl...
>
http://www.microsoft.com/downloads/d...displaylang=en[vbcol=seagreen]
> two
and[vbcol=seagreen]
configure[vbcol=seagreen]
the
>
|||Marlon,
I am considering writing an entire book on disk=20
architecture/design for SQL Server, but I would also=20
suggest you look at the stuff I have in the existing SQL=20
2K HA book to understand what goes into making a cluster.
At a minimum, you need (presented at the OS level) for=20
your cluster:
* 1 quorum disk
* 1 for SQL Server (probably more; I'd say a minimum of=20
two for data and log, but it will depend on your=20
requirements)
* 1 for MS DTC (for W2K3 required, and recommended for W2K)
Each of these should reside on different physical LUNs on=20
an external drive array, whether DAS or SAN (*not* NAS. =20
The quorum LUN must not be on a disk with any other (many=20
find it a waste of space, but that's the way it is). Some=20
vendors allow you to carve up one chunk of disk, and then=20
do partitions, and that is not really what you should do.
Hope this helps.
Allan

>--Original Message--
>Marlon, you have all the facts already. You need to buy=20
or use an external[vbcol=seagreen]
>storage, on a different controller.
>Cheers,
>Rod
>MVP - Windows Server - Clustering
>http://www.nw-america.com - Clustering
>"Marlon Brown" <marlon_brown@.hotmail.com> wrote in message
>news:uhsppKOdEHA.1652@.TK2MSFTNGP09.phx.gbl...
below I had the[vbcol=seagreen]
all computers" I[vbcol=seagreen]
both Compaq servers[vbcol=seagreen]
the controller cannot[vbcol=seagreen]
connected to all[vbcol=seagreen]
disk. Some type of a[vbcol=seagreen]
recommended.[vbcol=seagreen]
must be physically[vbcol=seagreen]
Majority Node Set (MNS)[vbcol=seagreen]
controller then the one used[vbcol=seagreen]
america.com> wrote in
>http://www.microsoft.com/downloads/details.aspx?
FamilyID=3D96f76ed7-9634-4300-9159-
89638f4b4ef7&displaylang=3Den[vbcol=seagreen]
message[vbcol=seagreen]
let's say if I have[vbcol=seagreen]
disks on each server[vbcol=seagreen]
>and
the 'quorum' ? I've seen the[vbcol=seagreen]
understand how to[vbcol=seagreen]
>configure
they are part of[vbcol=seagreen]
>the
wrote in message[vbcol=seagreen]
be in the[vbcol=seagreen]
more than[vbcol=seagreen]
just as[vbcol=seagreen]
one LUN but[vbcol=seagreen]
work.
>
>.
>

How to 'share drive', SCSI HD's on servers

I am not using a SAN to setup Win2003 CLusters, two nodes.
How can I share the hard disk in order to make this work for both nodes ?
All documentation I read states that I must have shared hard disk common to
the two servers and that's not clear to me when all I have is that hard
drive on the respective servers.
You can use SCSI disks. Just make sure whatever you get is on the catalog:
http://www.microsoft.com/windows/cat...5-23e9cd3ae95b
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"Marlon Brown" <marlon_brown@.hotmail.com> wrote in message
news:OEEkMpCdEHA.3016@.tk2msftngp13.phx.gbl...
> I am not using a SAN to setup Win2003 CLusters, two nodes.
> How can I share the hard disk in order to make this work for both nodes ?
> All documentation I read states that I must have shared hard disk common
to
> the two servers and that's not clear to me when all I have is that hard
> drive on the respective servers.
>