Showing posts with label ldf. Show all posts
Showing posts with label ldf. Show all posts

Friday, March 9, 2012

Large Transaction Logs

I have some databases that are keeping log files (.ldf) that are 3 times the
size of the db and very large. What is the best way to shrink the size of
these .ldf files? I have made backups of the db's and transaction logs
using SQL database maintenance (as some have suggested), but this does not
shrink the .ldf files. What else can I do?
Brandon
Presentations Direct - "Document Finishing Solutions"
http://www.presentationsdirect.comBrandon,
Backing up a transaction log will clear it but not shrink it. Use DBCC
SHRINKFILE on the t-log. Tibor has some more info here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Brandon" <bsmith@.presentationsdirect.nospam.com> wrote in message
news:eO5MHFOgGHA.1320@.TK2MSFTNGP04.phx.gbl...
>I have some databases that are keeping log files (.ldf) that are 3 times
>the size of the db and very large. What is the best way to shrink the size
>of these .ldf files? I have made backups of the db's and transaction logs
>using SQL database maintenance (as some have suggested), but this does not
>shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>|||You might want to investigate what is or has inflated them.
Reindexing, bulk loads of data, replication, etc, can all grow your
logs and there are ways of addressing each.|||Logs can get "locked" if something is hitting them. Example: I have an
Indexdefrag job that fills up a log. I have to stop the job before I can
shrink it. (I am using Simple recovery mode on this database.) You may wan
t
to use View/Textpad to see if the log is filled with active or not. If
you're in full mode, after you backup the log, you should see the empty spac
e
increase and the active space decrease.
"Brandon" wrote:

> I have some databases that are keeping log files (.ldf) that are 3 times t
he
> size of the db and very large. What is the best way to shrink the size of
> these .ldf files? I have made backups of the db's and transaction logs
> using SQL database maintenance (as some have suggested), but this does not
> shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>
>|||Specific to Transaction logs, here are a few articles you might like to
look at:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;EN-US;272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;EN-US;256650
INF: Considerations for Autogrow and Autoshrink Configuration
http://support.microsoft.com/defaul...kb;EN-US;315512
INF: Incomplete Transaction May Hold Large Number of Locks and Cause
Blocking
http://support.microsoft.com/defaul...kb;EN-US;295108
Taking a backup of the log will not shrink the size of log , also its
important to consider the recovery model of the database. Consider using
DBCC SHRINKFILE.
Hope This Helps
Vishal Gandhi

Large Transaction Logs

I have some databases that are keeping log files (.ldf) that are 3 times the
size of the db and very large. What is the best way to shrink the size of
these .ldf files? I have made backups of the db's and transaction logs
using SQL database maintenance (as some have suggested), but this does not
shrink the .ldf files. What else can I do?
--
Brandon
Presentations Direct - "Document Finishing Solutions"
http://www.presentationsdirect.comBrandon,
Backing up a transaction log will clear it but not shrink it. Use DBCC
SHRINKFILE on the t-log. Tibor has some more info here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Brandon" <bsmith@.presentationsdirect.nospam.com> wrote in message
news:eO5MHFOgGHA.1320@.TK2MSFTNGP04.phx.gbl...
>I have some databases that are keeping log files (.ldf) that are 3 times
>the size of the db and very large. What is the best way to shrink the size
>of these .ldf files? I have made backups of the db's and transaction logs
>using SQL database maintenance (as some have suggested), but this does not
>shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>|||You might want to investigate what is or has inflated them.
Reindexing, bulk loads of data, replication, etc, can all grow your
logs and there are ways of addressing each.|||Logs can get "locked" if something is hitting them. Example: I have an
Indexdefrag job that fills up a log. I have to stop the job before I can
shrink it. (I am using Simple recovery mode on this database.) You may want
to use View/Textpad to see if the log is filled with active or not. If
you're in full mode, after you backup the log, you should see the empty space
increase and the active space decrease.
"Brandon" wrote:
> I have some databases that are keeping log files (.ldf) that are 3 times the
> size of the db and very large. What is the best way to shrink the size of
> these .ldf files? I have made backups of the db's and transaction logs
> using SQL database maintenance (as some have suggested), but this does not
> shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>
>|||Specific to Transaction logs, here are a few articles you might like to
look at:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;EN-US;256650
INF: Considerations for Autogrow and Autoshrink Configuration
http://support.microsoft.com/default.aspx?scid=kb;EN-US;315512
INF: Incomplete Transaction May Hold Large Number of Locks and Cause
Blocking
http://support.microsoft.com/default.aspx?scid=kb;EN-US;295108
Taking a backup of the log will not shrink the size of log , also its
important to consider the recovery model of the database. Consider using
DBCC SHRINKFILE.
Hope This Helps
Vishal Gandhi

Monday, February 20, 2012

Large Log Files

I have several log files, .ldf, that keep growing and growing. I have a
database maintenance plan for these in which I am backing up the transaction
logs. How would I prevent these logs from continually getting larger? I
have SQL 2000 running on a Windows 2003 standard server.
I have other databases that the .ldf files don't grow...
Any help would be appreciated.
Thank you.
--
Mike StevensAlso, do you have some old open transaction? (DBCC OPENTRAN)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stevens" <Stevens@.discussions.microsoft.com> wrote in message
news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
>I have several log files, .ldf, that keep growing and growing. I have a
> database maintenance plan for these in which I am backing up the transaction
> logs. How would I prevent these logs from continually getting larger? I
> have SQL 2000 running on a Windows 2003 standard server.
> I have other databases that the .ldf files don't grow...
> Any help would be appreciated.
> Thank you.
> --
> Mike Stevens|||Are you 100% certain that you do perform log backup on this database? What does the virtual file
structure look like? (See http://www.karaszi.com/SQLServer/info_dont_shrink.asp)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stevens" <Stevens@.discussions.microsoft.com> wrote in message
news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
>I have several log files, .ldf, that keep growing and growing. I have a
> database maintenance plan for these in which I am backing up the transaction
> logs. How would I prevent these logs from continually getting larger? I
> have SQL 2000 running on a Windows 2003 standard server.
> I have other databases that the .ldf files don't grow...
> Any help would be appreciated.
> Thank you.
> --
> Mike Stevens|||As I said, I have a database maintenance plan for these in which I am backing
up the transaction logs. They create a .trn file. The .trn file is
considerable smaller than the .ldf. The .ldf file just keeps growing.
Mike Stevens
"Tibor Karaszi" wrote:
> Are you 100% certain that you do perform log backup on this database? What does the virtual file
> structure look like? (See http://www.karaszi.com/SQLServer/info_dont_shrink.asp)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stevens" <Stevens@.discussions.microsoft.com> wrote in message
> news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
> >I have several log files, .ldf, that keep growing and growing. I have a
> > database maintenance plan for these in which I am backing up the transaction
> > logs. How would I prevent these logs from continually getting larger? I
> > have SQL 2000 running on a Windows 2003 standard server.
> >
> > I have other databases that the .ldf files don't grow...
> >
> > Any help would be appreciated.
> >
> > Thank you.
> > --
> > Mike Stevens
>|||On Feb 9, 8:25 am, Stevens <Stev...@.discussions.microsoft.com> wrote:
> I have several log files, .ldf, that keep growing and growing. I have a
> database maintenance plan for these in which I am backing up the transaction
> logs. How would I prevent these logs from continually getting larger? I
> have SQL 2000 running on a Windows 2003 standard server.
> I have other databases that the .ldf files don't grow...
> Any help would be appreciated.
> Thank you.
> --
> Mike Stevens
Is the database in question enabled for replication? Define "keeps
growing and growing". How fast is it growing? Does it grow every
day, or only on weekends? Are you running a reindex job or something
that might be producing large transactions?|||What about the virtual log file structure, and result from DBCC OPENTRAN?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stevens" <Stevens@.discussions.microsoft.com> wrote in message
news:CA33D6C4-850B-4E77-AADB-11EF06A11BBA@.microsoft.com...
> As I said, I have a database maintenance plan for these in which I am backing
> up the transaction logs. They create a .trn file. The .trn file is
> considerable smaller than the .ldf. The .ldf file just keeps growing.
>
> --
> Mike Stevens
>
> "Tibor Karaszi" wrote:
>> Are you 100% certain that you do perform log backup on this database? What does the virtual file
>> structure look like? (See http://www.karaszi.com/SQLServer/info_dont_shrink.asp)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Stevens" <Stevens@.discussions.microsoft.com> wrote in message
>> news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
>> >I have several log files, .ldf, that keep growing and growing. I have a
>> > database maintenance plan for these in which I am backing up the transaction
>> > logs. How would I prevent these logs from continually getting larger? I
>> > have SQL 2000 running on a Windows 2003 standard server.
>> >
>> > I have other databases that the .ldf files don't grow...
>> >
>> > Any help would be appreciated.
>> >
>> > Thank you.
>> > --
>> > Mike Stevens
>>|||I'm quite a rookie at SQL. I'm not sure how to tell if the database is
enabled for replication. The .ldf file grows about 20-30 meg. a day. I
don't think I'm running any reindexing job.
Thanks for your help.
--
Mike Stevens
"Tracy McKibben" wrote:
> On Feb 9, 8:25 am, Stevens <Stev...@.discussions.microsoft.com> wrote:
> > I have several log files, .ldf, that keep growing and growing. I have a
> > database maintenance plan for these in which I am backing up the transaction
> > logs. How would I prevent these logs from continually getting larger? I
> > have SQL 2000 running on a Windows 2003 standard server.
> >
> > I have other databases that the .ldf files don't grow...
> >
> > Any help would be appreciated.
> >
> > Thank you.
> > --
> > Mike Stevens
> Is the database in question enabled for replication? Define "keeps
> growing and growing". How fast is it growing? Does it grow every
> day, or only on weekends? Are you running a reindex job or something
> that might be producing large transactions?
>

Large Log Files

I have several log files, .ldf, that keep growing and growing. I have a
database maintenance plan for these in which I am backing up the transaction
logs. How would I prevent these logs from continually getting larger? I
have SQL 2000 running on a Windows 2003 standard server.
I have other databases that the .ldf files don't grow...
Any help would be appreciated.
Thank you.
Mike Stevens
As I said, I have a database maintenance plan for these in which I am backing
up the transaction logs. They create a .trn file. The .trn file is
considerable smaller than the .ldf. The .ldf file just keeps growing.
Mike Stevens
"Tibor Karaszi" wrote:

> Are you 100% certain that you do perform log backup on this database? What does the virtual file
> structure look like? (See http://www.karaszi.com/SQLServer/info_dont_shrink.asp)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stevens" <Stevens@.discussions.microsoft.com> wrote in message
> news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
>
|||On Feb 9, 8:25 am, Stevens <Stev...@.discussions.microsoft.com> wrote:
> I have several log files, .ldf, that keep growing and growing. I have a
> database maintenance plan for these in which I am backing up the transaction
> logs. How would I prevent these logs from continually getting larger? I
> have SQL 2000 running on a Windows 2003 standard server.
> I have other databases that the .ldf files don't grow...
> Any help would be appreciated.
> Thank you.
> --
> Mike Stevens
Is the database in question enabled for replication? Define "keeps
growing and growing". How fast is it growing? Does it grow every
day, or only on weekends? Are you running a reindex job or something
that might be producing large transactions?
|||I'm quite a rookie at SQL. I'm not sure how to tell if the database is
enabled for replication. The .ldf file grows about 20-30 meg. a day. I
don't think I'm running any reindexing job.
Thanks for your help.
Mike Stevens
"Tracy McKibben" wrote:

> On Feb 9, 8:25 am, Stevens <Stev...@.discussions.microsoft.com> wrote:
> Is the database in question enabled for replication? Define "keeps
> growing and growing". How fast is it growing? Does it grow every
> day, or only on weekends? Are you running a reindex job or something
> that might be producing large transactions?
>

Large Log Files

I have several log files, .ldf, that keep growing and growing. I have a
database maintenance plan for these in which I am backing up the transaction
logs. How would I prevent these logs from continually getting larger? I
have SQL 2000 running on a Windows 2003 standard server.
I have other databases that the .ldf files don't grow...
Any help would be appreciated.
Thank you.
--
Mike StevensAlso, do you have some old open transaction? (DBCC OPENTRAN)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stevens" <Stevens@.discussions.microsoft.com> wrote in message
news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
>I have several log files, .ldf, that keep growing and growing. I have a
> database maintenance plan for these in which I am backing up the transacti
on
> logs. How would I prevent these logs from continually getting larger? I
> have SQL 2000 running on a Windows 2003 standard server.
> I have other databases that the .ldf files don't grow...
> Any help would be appreciated.
> Thank you.
> --
> Mike Stevens|||Are you 100% certain that you do perform log backup on this database? What d
oes the virtual file
structure look like? (See http://www.karaszi.com/SQLServer/in...ver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stevens" <Stevens@.discussions.microsoft.com> wrote in message
news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
>I have several log files, .ldf, that keep growing and growing. I have a
> database maintenance plan for these in which I am backing up the transacti
on
> logs. How would I prevent these logs from continually getting larger? I
> have SQL 2000 running on a Windows 2003 standard server.
> I have other databases that the .ldf files don't grow...
> Any help would be appreciated.
> Thank you.
> --
> Mike Stevens|||As I said, I have a database maintenance plan for these in which I am backin
g
up the transaction logs. They create a .trn file. The .trn file is
considerable smaller than the .ldf. The .ldf file just keeps growing.
Mike Stevens
"Tibor Karaszi" wrote:

> Are you 100% certain that you do perform log backup on this database? What
does the virtual file
> structure look like? (See http://www.karaszi.com/SQLServer/in...ver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stevens" <Stevens@.discussions.microsoft.com> wrote in message
> news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
>|||On Feb 9, 8:25 am, Stevens <Stev...@.discussions.microsoft.com> wrote:
> I have several log files, .ldf, that keep growing and growing. I have a
> database maintenance plan for these in which I am backing up the transacti
on
> logs. How would I prevent these logs from continually getting larger? I
> have SQL 2000 running on a Windows 2003 standard server.
> I have other databases that the .ldf files don't grow...
> Any help would be appreciated.
> Thank you.
> --
> Mike Stevens
Is the database in question enabled for replication? Define "keeps
growing and growing". How fast is it growing? Does it grow every
day, or only on weekends? Are you running a reindex job or something
that might be producing large transactions?|||What about the virtual log file structure, and result from DBCC OPENTRAN?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stevens" <Stevens@.discussions.microsoft.com> wrote in message
news:CA33D6C4-850B-4E77-AADB-11EF06A11BBA@.microsoft.com...[vbcol=seagreen]
> As I said, I have a database maintenance plan for these in which I am back
ing
> up the transaction logs. They create a .trn file. The .trn file is
> considerable smaller than the .ldf. The .ldf file just keeps growing.
>
> --
> Mike Stevens
>
> "Tibor Karaszi" wrote:
>|||I'm quite a rookie at SQL. I'm not sure how to tell if the database is
enabled for replication. The .ldf file grows about 20-30 meg. a day. I
don't think I'm running any reindexing job.
Thanks for your help.
--
Mike Stevens
"Tracy McKibben" wrote:

> On Feb 9, 8:25 am, Stevens <Stev...@.discussions.microsoft.com> wrote:
> Is the database in question enabled for replication? Define "keeps
> growing and growing". How fast is it growing? Does it grow every
> day, or only on weekends? Are you running a reindex job or something
> that might be producing large transactions?
>

Large LDF File

My LDF file is 1.8GB big and my data file is only about 100mb big. I have had
the recovery model set to full until recently which is probably why it has
ended up so big. I have now changed that to simple. Now no matter what I do
the LDF file stays at exactly 1,785,856 KB in size. I have tried backing up
the database and backing up the transaction log but neither have made any
difference to the size of the file. Even using the database doesn't appear to
make any difference to the file size. Can you please advise me as to what I
can do to shrink this file and get it working again.
Many Thanks.
Look up DBCC SHRINKFILE in BOL.
"Boxwell" <Boxwell@.discussions.microsoft.com> wrote in message
news:25DB49E4-46C3-48E2-9B2F-1984B11E0216@.microsoft.com...
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have
> had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I
> do
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing
> up
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear
> to
> make any difference to the file size. Can you please advise me as to what
> I
> can do to shrink this file and get it working again.
> Many Thanks.
|||Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
Run => DBCC sqlperf(logspace)
To Add on to this, to save disk space you need to run,
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Boxwell" wrote:

> My LDF file is 1.8GB big and my data file is only about 100mb big. I have had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I do
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing up
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear to
> make any difference to the file size. Can you please advise me as to what I
> can do to shrink this file and get it working again.
> Many Thanks.

Large LDF File

My LDF file is 1.8GB big and my data file is only about 100mb big. I have ha
d
the recovery model set to full until recently which is probably why it has
ended up so big. I have now changed that to simple. Now no matter what I do
the LDF file stays at exactly 1,785,856 KB in size. I have tried backing up
the database and backing up the transaction log but neither have made any
difference to the size of the file. Even using the database doesn't appear t
o
make any difference to the file size. Can you please advise me as to what I
can do to shrink this file and get it working again.
Many Thanks.Look up DBCC SHRINKFILE in BOL.
"Boxwell" <Boxwell@.discussions.microsoft.com> wrote in message
news:25DB49E4-46C3-48E2-9B2F-1984B11E0216@.microsoft.com...
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have
> had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I
> do
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing
> up
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear
> to
> make any difference to the file size. Can you please advise me as to what
> I
> can do to shrink this file and get it working again.
> Many Thanks.|||Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
Run => DBCC sqlperf(logspace)
To Add on to this, to save disk space you need to run,
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Boxwell" wrote:

> My LDF file is 1.8GB big and my data file is only about 100mb big. I have
had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I d
o
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing u
p
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear
to
> make any difference to the file size. Can you please advise me as to what
I
> can do to shrink this file and get it working again.
> Many Thanks.

Large LDF File

My LDF file is 1.8GB big and my data file is only about 100mb big. I have had
the recovery model set to full until recently which is probably why it has
ended up so big. I have now changed that to simple. Now no matter what I do
the LDF file stays at exactly 1,785,856 KB in size. I have tried backing up
the database and backing up the transaction log but neither have made any
difference to the size of the file. Even using the database doesn't appear to
make any difference to the file size. Can you please advise me as to what I
can do to shrink this file and get it working again.
Many Thanks.Look up DBCC SHRINKFILE in BOL.
"Boxwell" <Boxwell@.discussions.microsoft.com> wrote in message
news:25DB49E4-46C3-48E2-9B2F-1984B11E0216@.microsoft.com...
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have
> had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I
> do
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing
> up
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear
> to
> make any difference to the file size. Can you please advise me as to what
> I
> can do to shrink this file and get it working again.
> Many Thanks.|||Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
Run => DBCC sqlperf(logspace)
To Add on to this, to save disk space you need to run,
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Boxwell" wrote:
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I do
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing up
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear to
> make any difference to the file size. Can you please advise me as to what I
> can do to shrink this file and get it working again.
> Many Thanks.