Out of control log file

To all:
I have a database that's about 2.5 GB in size, but the log file has grown to
over 55GB and has eaten up almost all of my drive space. How can I
remove/replace/compress that log file to recover my hard drive space back?
I've tried recreating the log file by running sp_detach_db, creating a new
log file, and then reattaching the db but it fails. Any suggestions are
greatly appreciated. Thanks in advance.
Steve
Steven Johnston wrote:
> To all:
> I have a database that's about 2.5 GB in size, but the log file has
> grown to over 55GB and has eaten up almost all of my drive space. How
> can I remove/replace/compress that log file to recover my hard drive
> space back? I've tried recreating the log file by running
> sp_detach_db, creating a new log file, and then reattaching the db
> but it fails. Any suggestions are greatly appreciated. Thanks in
> advance.
Read BOL about backup of your database. You can only shrink your tx log
if you do a backup (or do a log backup for truncation only). HTH
robert
|||Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=873235 How to stop the log file from
growing
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
http://www.support.microsoft.com/?id=307487 Shrinking TempDB
Andrew J. Kelly SQL MVP
"Steven Johnston" <saj3@.jasper.he.net> wrote in message
news:%23BT4PoUOGHA.2472@.TK2MSFTNGP11.phx.gbl...
> To all:
> I have a database that's about 2.5 GB in size, but the log file has grown
> to over 55GB and has eaten up almost all of my drive space. How can I
> remove/replace/compress that log file to recover my hard drive space back?
> I've tried recreating the log file by running sp_detach_db, creating a new
> log file, and then reattaching the db but it fails. Any suggestions are
> greatly appreciated. Thanks in advance.
> Steve
>

Out of control log file

To all:
I have a database that's about 2.5 GB in size, but the log file has grown to
over 55GB and has eaten up almost all of my drive space. How can I
remove/replace/compress that log file to recover my hard drive space back?
I've tried recreating the log file by running sp_detach_db, creating a new
log file, and then reattaching the db but it fails. Any suggestions are
greatly appreciated. Thanks in advance.
SteveSteven Johnston wrote:
> To all:
> I have a database that's about 2.5 GB in size, but the log file has
> grown to over 55GB and has eaten up almost all of my drive space. How
> can I remove/replace/compress that log file to recover my hard drive
> space back? I've tried recreating the log file by running
> sp_detach_db, creating a new log file, and then reattaching the db
> but it fails. Any suggestions are greatly appreciated. Thanks in
> advance.
Read BOL about backup of your database. You can only shrink your tx log
if you do a backup (or do a log backup for truncation only). HTH
robert|||Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/Transact...ileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=873235 How to stop the log file from
growing
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
http://www.support.microsoft.com/?id=307487 Shrinking TempDB
Andrew J. Kelly SQL MVP
"Steven Johnston" <saj3@.jasper.he.net> wrote in message
news:%23BT4PoUOGHA.2472@.TK2MSFTNGP11.phx.gbl...
> To all:
> I have a database that's about 2.5 GB in size, but the log file has grown
> to over 55GB and has eaten up almost all of my drive space. How can I
> remove/replace/compress that log file to recover my hard drive space back?
> I've tried recreating the log file by running sp_detach_db, creating a new
> log file, and then reattaching the db but it fails. Any suggestions are
> greatly appreciated. Thanks in advance.
> Steve
>

Out of control log file

To all:
I have a database that's about 2.5 GB in size, but the log file has grown to
over 55GB and has eaten up almost all of my drive space. How can I
remove/replace/compress that log file to recover my hard drive space back?
I've tried recreating the log file by running sp_detach_db, creating a new
log file, and then reattaching the db but it fails. Any suggestions are
greatly appreciated. Thanks in advance.
SteveSteven Johnston wrote:
> To all:
> I have a database that's about 2.5 GB in size, but the log file has
> grown to over 55GB and has eaten up almost all of my drive space. How
> can I remove/replace/compress that log file to recover my hard drive
> space back? I've tried recreating the log file by running
> sp_detach_db, creating a new log file, and then reattaching the db
> but it fails. Any suggestions are greatly appreciated. Thanks in
> advance.
Read BOL about backup of your database. You can only shrink your tx log
if you do a backup (or do a log backup for truncation only). HTH
robert|||Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=873235 How to stop the log file from
growing
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
http://www.support.microsoft.com/?id=307487 Shrinking TempDB
--
Andrew J. Kelly SQL MVP
"Steven Johnston" <saj3@.jasper.he.net> wrote in message
news:%23BT4PoUOGHA.2472@.TK2MSFTNGP11.phx.gbl...
> To all:
> I have a database that's about 2.5 GB in size, but the log file has grown
> to over 55GB and has eaten up almost all of my drive space. How can I
> remove/replace/compress that log file to recover my hard drive space back?
> I've tried recreating the log file by running sp_detach_db, creating a new
> log file, and then reattaching the db but it fails. Any suggestions are
> greatly appreciated. Thanks in advance.
> Steve
>sql

OS Defrag?

My 220GB DB resides mostly on one RAID 5 logical drive. This drive reports
fragmentation. Will I see any potential improvements in disk access if I
choose to defrag? Due to text columns and fairly large/wide rows,
performance is rock bottom. As a for instance, I have a 2.2GB file and
shrinking 1MB ran overnight without completing.
Yes, it can help. see
http://www.microsoft.com/technet/abo...ps_083104.mspx
for more details.
Why are you trying to shrink your data files btw?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
> My 220GB DB resides mostly on one RAID 5 logical drive. This drive
> reports
> fragmentation. Will I see any potential improvements in disk access if I
> choose to defrag? Due to text columns and fairly large/wide rows,
> performance is rock bottom. As a for instance, I have a 2.2GB file and
> shrinking 1MB ran overnight without completing.
|||The original problem was the disk performance, specifically disk reads, was
slow enough to make the system unusable. I since discoved that the purging
of some historical tables had a dramatic effect on what objects where in
cache which virtually eliminated the disk reads. The 220GB database was in 4
files on one logical drive(80GB, 40GB, 40GB, 40GB). I had created an
additional file on the same logical drive before creating files on other
logical drives in hopes of spreading out this and other DBs across 3 logical
drives each on their own channel. For the smaller database, this worked out
OK. For the 220GB DB, there are several GB free in each file. Neither DBCC
shrinkfile(emptyfile) or truncate_only finish and eventually deadlock. For
the smaller DB, I wrote a loop that shrunk the files a few MB at a time. I'm
trying to close the large files so no more data gets written to them. At 80
and 40GB, the files are too large to manage given my current level of
performance.
"Paul S Randal [MS]" wrote:

> Yes, it can help. see
> http://www.microsoft.com/technet/abo...ps_083104.mspx
> for more details.
> Why are you trying to shrink your data files btw?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
>
>

OS Defrag?

My 220GB DB resides mostly on one RAID 5 logical drive. This drive reports
fragmentation. Will I see any potential improvements in disk access if I
choose to defrag? Due to text columns and fairly large/wide rows,
performance is rock bottom. As a for instance, I have a 2.2GB file and
shrinking 1MB ran overnight without completing.Yes, it can help. see
http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_083104.mspx
for more details.
Why are you trying to shrink your data files btw?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
> My 220GB DB resides mostly on one RAID 5 logical drive. This drive
> reports
> fragmentation. Will I see any potential improvements in disk access if I
> choose to defrag? Due to text columns and fairly large/wide rows,
> performance is rock bottom. As a for instance, I have a 2.2GB file and
> shrinking 1MB ran overnight without completing.|||The original problem was the disk performance, specifically disk reads, was
slow enough to make the system unusable. I since discoved that the purging
of some historical tables had a dramatic effect on what objects where in
cache which virtually eliminated the disk reads. The 220GB database was in 4
files on one logical drive(80GB, 40GB, 40GB, 40GB). I had created an
additional file on the same logical drive before creating files on other
logical drives in hopes of spreading out this and other DBs across 3 logical
drives each on their own channel. For the smaller database, this worked out
OK. For the 220GB DB, there are several GB free in each file. Neither DBCC
shrinkfile(emptyfile) or truncate_only finish and eventually deadlock. For
the smaller DB, I wrote a loop that shrunk the files a few MB at a time. I'm
trying to close the large files so no more data gets written to them. At 80
and 40GB, the files are too large to manage given my current level of
performance.
"Paul S Randal [MS]" wrote:
> Yes, it can help. see
> http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_083104.mspx
> for more details.
> Why are you trying to shrink your data files btw?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
> > My 220GB DB resides mostly on one RAID 5 logical drive. This drive
> > reports
> > fragmentation. Will I see any potential improvements in disk access if I
> > choose to defrag? Due to text columns and fairly large/wide rows,
> > performance is rock bottom. As a for instance, I have a 2.2GB file and
> > shrinking 1MB ran overnight without completing.
>
>

OS Defrag?

My 220GB DB resides mostly on one RAID 5 logical drive. This drive reports
fragmentation. Will I see any potential improvements in disk access if I
choose to defrag? Due to text columns and fairly large/wide rows,
performance is rock bottom. As a for instance, I have a 2.2GB file and
shrinking 1MB ran overnight without completing.Yes, it can help. see
http://www.microsoft.com/technet/ab...04.mspx

for more details.
Why are you trying to shrink your data files btw?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
> My 220GB DB resides mostly on one RAID 5 logical drive. This drive
> reports
> fragmentation. Will I see any potential improvements in disk access if I
> choose to defrag? Due to text columns and fairly large/wide rows,
> performance is rock bottom. As a for instance, I have a 2.2GB file and
> shrinking 1MB ran overnight without completing.|||The original problem was the disk performance, specifically disk reads, was
slow enough to make the system unusable. I since discoved that the purging
of some historical tables had a dramatic effect on what objects where in
cache which virtually eliminated the disk reads. The 220GB database was in
4
files on one logical drive(80GB, 40GB, 40GB, 40GB). I had created an
additional file on the same logical drive before creating files on other
logical drives in hopes of spreading out this and other DBs across 3 logical
drives each on their own channel. For the smaller database, this worked out
OK. For the 220GB DB, there are several GB free in each file. Neither DBCC
shrinkfile(emptyfile) or truncate_only finish and eventually deadlock. For
the smaller DB, I wrote a loop that shrunk the files a few MB at a time. I'
m
trying to close the large files so no more data gets written to them. At 80
and 40GB, the files are too large to manage given my current level of
performance.
"Paul S Randal [MS]" wrote:

> Yes, it can help. see
> http://www.microsoft.com/technet/ab...04.ms
px
> for more details.
> Why are you trying to shrink your data files btw?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
>
>

OS and SQL Server installed on seperate drives.

We have a server with one IDE drive which is where the OS
in located. We have recently purchased a SCSI drive and
intend on installing SQL Server 2000 on it. Is anyone
aware of any problems that could arise from having the OS
and SQL server on seperate drives? Thanks a lot!No issues that I know of. In fact, having more drives to spread out the I/O
is strongly encouraged.
--Brian
(Please reply to the newsgroups only.)
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:398b01c4014b$1477d870$a601280a@.phx.gbl...
> We have a server with one IDE drive which is where the OS
> in located. We have recently purchased a SCSI drive and
> intend on installing SQL Server 2000 on it. Is anyone
> aware of any problems that could arise from having the OS
> and SQL server on seperate drives? Thanks a lot!|||Hi Mark.
There's no problem with separating the drives that the o/s & SQL Server are
on, but you should be aware that by far the most important components to
separate onto different physical disks are SQL Server's .mdf & .ldf files.
There are significant performance and recoverability issues that stem from
this separation and it's the thing that most inexperienced people get wrong.
Regards,
Greg Linwood
SQL Server MVP
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:398b01c4014b$1477d870$a601280a@.phx.gbl...
> We have a server with one IDE drive which is where the OS
> in located. We have recently purchased a SCSI drive and
> intend on installing SQL Server 2000 on it. Is anyone
> aware of any problems that could arise from having the OS
> and SQL server on seperate drives? Thanks a lot!

OS and SQL Server installed on seperate drives.

We have a server with one IDE drive which is where the OS
in located. We have recently purchased a SCSI drive and
intend on installing SQL Server 2000 on it. Is anyone
aware of any problems that could arise from having the OS
and SQL server on seperate drives? Thanks a lot!No issues that I know of. In fact, having more drives to spread out the I/O
is strongly encouraged.
--
--Brian
(Please reply to the newsgroups only.)
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:398b01c4014b$1477d870$a601280a@.phx.gbl...
> We have a server with one IDE drive which is where the OS
> in located. We have recently purchased a SCSI drive and
> intend on installing SQL Server 2000 on it. Is anyone
> aware of any problems that could arise from having the OS
> and SQL server on seperate drives? Thanks a lot!|||Hi Mark.
There's no problem with separating the drives that the o/s & SQL Server are
on, but you should be aware that by far the most important components to
separate onto different physical disks are SQL Server's .mdf & .ldf files.
There are significant performance and recoverability issues that stem from
this separation and it's the thing that most inexperienced people get wrong.
Regards,
Greg Linwood
SQL Server MVP
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:398b01c4014b$1477d870$a601280a@.phx.gbl...
> We have a server with one IDE drive which is where the OS
> in located. We have recently purchased a SCSI drive and
> intend on installing SQL Server 2000 on it. Is anyone
> aware of any problems that could arise from having the OS
> and SQL server on seperate drives? Thanks a lot!