The sql server 2000 is on its last legs and although we have a new 2005
server, no plan for migration has had time to congeal. Meanwhile the concern
that the older server will die has prompted us to construct a second
duplicate server which we are planning to use to log ship from the old server
to the new. In the event of a disaster, the plan is to switch the plant load
over to the server receiving the logs. Tomorrow is the day to try the plan
but I am having last minute doubts and wonder if a better solution may exist.
Takers?
Regards,
Jamie
Jamie - this is quite a well-used and well-supported methodology and will
ensure that everything is maintained. I have an article which illustrates
the differences between log shipping and transactional replication
(http://www.replicationanswers.com/Standby.asp) which might be useful.
Clustering will give you automatic failover, as will database mirroring when
you move to SQL Server 2005.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||You have two options log shipping and transactional replication. With log
shipping your exposure to data loss is greater - it is the time period since
the last database dump.
With transactional replication your subscriber/destination can be within
seconds of the publisher/source.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:02849BEF-1DBE-447C-823A-972939D09393@.microsoft.com...
> The sql server 2000 is on its last legs and although we have a new 2005
> server, no plan for migration has had time to congeal. Meanwhile the
> concern
> that the older server will die has prompted us to construct a second
> duplicate server which we are planning to use to log ship from the old
> server
> to the new. In the event of a disaster, the plan is to switch the plant
> load
> over to the server receiving the logs. Tomorrow is the day to try the
> plan
> but I am having last minute doubts and wonder if a better solution may
> exist.
> Takers?
> --
> Regards,
> Jamie
|||Log shipping does not appear to be an option when right clicking the database
and selecting properties. (SQLMgr 2005)
Regards,
Jamie
"Hilary Cotter" wrote:
> You have two options log shipping and transactional replication. With log
> shipping your exposure to data loss is greater - it is the time period since
> the last database dump.
> With transactional replication your subscriber/destination can be within
> seconds of the publisher/source.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:02849BEF-1DBE-447C-823A-972939D09393@.microsoft.com...
>
>
|||Are you using Express Edition? Any other edition should have the option
"Transaction Log Shipping" on the database properties screen.
Rgds,
Paul Ibison
|||It specifically states on the screen (SQL Server 2000) that log shipping is
an Enterprise only option. Perhaps SP4 removes this restriction? We would
like to upgrade but fear issues with the production server and the firmware
that cannot yet be applied.
Regards,
Jamie
"Paul Ibison" wrote:
> Are you using Express Edition? Any other edition should have the option
> "Transaction Log Shipping" on the database properties screen.
> Rgds,
> Paul Ibison
>
>
|||I should add, that if it helps, we also have 3 MSDN subscriptions and that
two of us are Partners and two of us have personal MSDN subscriptions.
Unfortunately the company would require two processor licenses to go
Enterprise.
Regards,
Jamie
"Paul Ibison" wrote:
> Are you using Express Edition? Any other edition should have the option
> "Transaction Log Shipping" on the database properties screen.
> Rgds,
> Paul Ibison
>
>
|||OK - I've answered this issue in your other post as there is a big
overlap...
Cheers,
Paul Ibison
sql
Showing posts with label log. Show all posts
Showing posts with label log. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
FrontPage/SharePoint Designer Search and Replace Fills Transaction
When we run a search and replace across all pages in one our web site (104
pages), my transaction log on the SQL Server grows by about 200 MB of space!
This has caused major system headaches - the transaction log fills, causes
IIS to fail, causing my clients using FP / SP Designer not to be able to save
the files they have opened / edited.
That database is 400MB in size while the Transaction Log is now over 6GB.
and the minimum Log is now 6GB too. We can't keep having this log grow
exponential like this.
Also - In the event log, I see that there a couple of 'webparts' that are
failing on the global search and replace: WSS 2.0 Error: Failing in loading
assemble Customer.WebParts, Version=1.0.2.4.
We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
using the latest versions of FrontPage and SharePoint Designer.
Thoughts?
After posting this, I started to search the WSS / Sharepoint forums (instead
of just SQL Server) and see that this is a common issue and found a variety
of ways to correct this.
Iza
"iZa" wrote:
> When we run a search and replace across all pages in one our web site (104
> pages), my transaction log on the SQL Server grows by about 200 MB of space!
> This has caused major system headaches - the transaction log fills, causes
> IIS to fail, causing my clients using FP / SP Designer not to be able to save
> the files they have opened / edited.
> That database is 400MB in size while the Transaction Log is now over 6GB.
> and the minimum Log is now 6GB too. We can't keep having this log grow
> exponential like this.
> Also - In the event log, I see that there a couple of 'webparts' that are
> failing on the global search and replace: WSS 2.0 Error: Failing in loading
> assemble Customer.WebParts, Version=1.0.2.4.
> We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
> using the latest versions of FrontPage and SharePoint Designer.
> Thoughts?
pages), my transaction log on the SQL Server grows by about 200 MB of space!
This has caused major system headaches - the transaction log fills, causes
IIS to fail, causing my clients using FP / SP Designer not to be able to save
the files they have opened / edited.
That database is 400MB in size while the Transaction Log is now over 6GB.
and the minimum Log is now 6GB too. We can't keep having this log grow
exponential like this.
Also - In the event log, I see that there a couple of 'webparts' that are
failing on the global search and replace: WSS 2.0 Error: Failing in loading
assemble Customer.WebParts, Version=1.0.2.4.
We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
using the latest versions of FrontPage and SharePoint Designer.
Thoughts?
After posting this, I started to search the WSS / Sharepoint forums (instead
of just SQL Server) and see that this is a common issue and found a variety
of ways to correct this.
Iza
"iZa" wrote:
> When we run a search and replace across all pages in one our web site (104
> pages), my transaction log on the SQL Server grows by about 200 MB of space!
> This has caused major system headaches - the transaction log fills, causes
> IIS to fail, causing my clients using FP / SP Designer not to be able to save
> the files they have opened / edited.
> That database is 400MB in size while the Transaction Log is now over 6GB.
> and the minimum Log is now 6GB too. We can't keep having this log grow
> exponential like this.
> Also - In the event log, I see that there a couple of 'webparts' that are
> failing on the global search and replace: WSS 2.0 Error: Failing in loading
> assemble Customer.WebParts, Version=1.0.2.4.
> We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
> using the latest versions of FrontPage and SharePoint Designer.
> Thoughts?
FrontPage/SharePoint Designer Search and Replace Fills Transaction
When we run a search and replace across all pages in one our web site (104
pages), my transaction log on the SQL Server grows by about 200 MB of space!
This has caused major system headaches - the transaction log fills, causes
IIS to fail, causing my clients using FP / SP Designer not to be able to save
the files they have opened / edited.
That database is 400MB in size while the Transaction Log is now over 6GB.
and the minimum Log is now 6GB too. We can't keep having this log grow
exponential like this.
Also - In the event log, I see that there a couple of 'webparts' that are
failing on the global search and replace: WSS 2.0 Error: Failing in loading
assemble Customer.WebParts, Version=1.0.2.4.
We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
using the latest versions of FrontPage and SharePoint Designer.
Thoughts?After posting this, I started to search the WSS / Sharepoint forums (instead
of just SQL Server) and see that this is a common issue and found a variety
of ways to correct this.
Iza
"iZa" wrote:
> When we run a search and replace across all pages in one our web site (104
> pages), my transaction log on the SQL Server grows by about 200 MB of space!
> This has caused major system headaches - the transaction log fills, causes
> IIS to fail, causing my clients using FP / SP Designer not to be able to save
> the files they have opened / edited.
> That database is 400MB in size while the Transaction Log is now over 6GB.
> and the minimum Log is now 6GB too. We can't keep having this log grow
> exponential like this.
> Also - In the event log, I see that there a couple of 'webparts' that are
> failing on the global search and replace: WSS 2.0 Error: Failing in loading
> assemble Customer.WebParts, Version=1.0.2.4.
> We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
> using the latest versions of FrontPage and SharePoint Designer.
> Thoughts?sql
pages), my transaction log on the SQL Server grows by about 200 MB of space!
This has caused major system headaches - the transaction log fills, causes
IIS to fail, causing my clients using FP / SP Designer not to be able to save
the files they have opened / edited.
That database is 400MB in size while the Transaction Log is now over 6GB.
and the minimum Log is now 6GB too. We can't keep having this log grow
exponential like this.
Also - In the event log, I see that there a couple of 'webparts' that are
failing on the global search and replace: WSS 2.0 Error: Failing in loading
assemble Customer.WebParts, Version=1.0.2.4.
We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
using the latest versions of FrontPage and SharePoint Designer.
Thoughts?After posting this, I started to search the WSS / Sharepoint forums (instead
of just SQL Server) and see that this is a common issue and found a variety
of ways to correct this.
Iza
"iZa" wrote:
> When we run a search and replace across all pages in one our web site (104
> pages), my transaction log on the SQL Server grows by about 200 MB of space!
> This has caused major system headaches - the transaction log fills, causes
> IIS to fail, causing my clients using FP / SP Designer not to be able to save
> the files they have opened / edited.
> That database is 400MB in size while the Transaction Log is now over 6GB.
> and the minimum Log is now 6GB too. We can't keep having this log grow
> exponential like this.
> Also - In the event log, I see that there a couple of 'webparts' that are
> failing on the global search and replace: WSS 2.0 Error: Failing in loading
> assemble Customer.WebParts, Version=1.0.2.4.
> We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
> using the latest versions of FrontPage and SharePoint Designer.
> Thoughts?sql
FrontPage/SharePoint Designer Search and Replace Fills Transaction
When we run a search and replace across all pages in one our web site (104
pages), my transaction log on the SQL Server grows by about 200 MB of space!
This has caused major system headaches - the transaction log fills, causes
IIS to fail, causing my clients using FP / SP Designer not to be able to sav
e
the files they have opened / edited.
That database is 400MB in size while the Transaction Log is now over 6GB.
and the minimum Log is now 6GB too. We can't keep having this log grow
exponential like this.
Also - In the event log, I see that there a couple of 'webparts' that are
failing on the global search and replace: WSS 2.0 Error: Failing in loading
assemble Customer.WebParts, Version=1.0.2.4.
We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
using the latest versions of FrontPage and SharePoint Designer.
Thoughts?After posting this, I started to search the WSS / Sharepoint forums (instead
of just SQL Server) and see that this is a common issue and found a variety
of ways to correct this.
Iza
"iZa" wrote:
> When we run a search and replace across all pages in one our web site (104
> pages), my transaction log on the SQL Server grows by about 200 MB of spac
e!
> This has caused major system headaches - the transaction log fills, causes
> IIS to fail, causing my clients using FP / SP Designer not to be able to s
ave
> the files they have opened / edited.
> That database is 400MB in size while the Transaction Log is now over 6GB.
> and the minimum Log is now 6GB too. We can't keep having this log grow
> exponential like this.
> Also - In the event log, I see that there a couple of 'webparts' that are
> failing on the global search and replace: WSS 2.0 Error: Failing in loadin
g
> assemble Customer.WebParts, Version=1.0.2.4.
> We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
> using the latest versions of FrontPage and SharePoint Designer.
> Thoughts?
pages), my transaction log on the SQL Server grows by about 200 MB of space!
This has caused major system headaches - the transaction log fills, causes
IIS to fail, causing my clients using FP / SP Designer not to be able to sav
e
the files they have opened / edited.
That database is 400MB in size while the Transaction Log is now over 6GB.
and the minimum Log is now 6GB too. We can't keep having this log grow
exponential like this.
Also - In the event log, I see that there a couple of 'webparts' that are
failing on the global search and replace: WSS 2.0 Error: Failing in loading
assemble Customer.WebParts, Version=1.0.2.4.
We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
using the latest versions of FrontPage and SharePoint Designer.
Thoughts?After posting this, I started to search the WSS / Sharepoint forums (instead
of just SQL Server) and see that this is a common issue and found a variety
of ways to correct this.
Iza
"iZa" wrote:
> When we run a search and replace across all pages in one our web site (104
> pages), my transaction log on the SQL Server grows by about 200 MB of spac
e!
> This has caused major system headaches - the transaction log fills, causes
> IIS to fail, causing my clients using FP / SP Designer not to be able to s
ave
> the files they have opened / edited.
> That database is 400MB in size while the Transaction Log is now over 6GB.
> and the minimum Log is now 6GB too. We can't keep having this log grow
> exponential like this.
> Also - In the event log, I see that there a couple of 'webparts' that are
> failing on the global search and replace: WSS 2.0 Error: Failing in loadin
g
> assemble Customer.WebParts, Version=1.0.2.4.
> We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
> using the latest versions of FrontPage and SharePoint Designer.
> Thoughts?
Monday, March 12, 2012
Frequent Trans Log backups Vs Autogrow
Hi
Im doing some testing on mirroring at the moment, and have found out about
the inherent problems with transaction log size that thie replication
scenario entails. However I have found that frequent trans log backups are a
way around the problem - This sets the vast majority of VLFs in the trans log
to state 0 allowing them to be overwritten by new transactions VLF's.
Accordingly I dont need to autogrow half as often.
The question is which is the most harmful of system performance? frequent
trans log backups or autogrows? if i DO backup frequently i DONT need to
autogrow, and if i DONT back up frequently i DO need to autogrow...
which which people choose?
Cheers
Alastair Jones
Methodology Group
In SQL 2005 autogrows do not cause performance degradation they way they did
in SQL 2000. Backing up your transaction log should not cause performance
problems either. It may cause some performance problems with Full-text
search, but should not cause other problems.
So I normally backup every 15-20 minutes, unless I am log shipping where it
might be more to decrease my exposure to data loss. None of these
considerations should impact database mirroring though.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:83043C4C-1FD2-4F7A-9E9B-9A0C1890908C@.microsoft.com...
> Hi
> Im doing some testing on mirroring at the moment, and have found out about
> the inherent problems with transaction log size that thie replication
> scenario entails. However I have found that frequent trans log backups are
> a
> way around the problem - This sets the vast majority of VLFs in the trans
> log
> to state 0 allowing them to be overwritten by new transactions VLF's.
> Accordingly I dont need to autogrow half as often.
> The question is which is the most harmful of system performance? frequent
> trans log backups or autogrows? if i DO backup frequently i DONT need to
> autogrow, and if i DONT back up frequently i DO need to autogrow...
> which which people choose?
> Cheers
> Alastair Jones
> Methodology Group
Im doing some testing on mirroring at the moment, and have found out about
the inherent problems with transaction log size that thie replication
scenario entails. However I have found that frequent trans log backups are a
way around the problem - This sets the vast majority of VLFs in the trans log
to state 0 allowing them to be overwritten by new transactions VLF's.
Accordingly I dont need to autogrow half as often.
The question is which is the most harmful of system performance? frequent
trans log backups or autogrows? if i DO backup frequently i DONT need to
autogrow, and if i DONT back up frequently i DO need to autogrow...
which which people choose?
Cheers
Alastair Jones
Methodology Group
In SQL 2005 autogrows do not cause performance degradation they way they did
in SQL 2000. Backing up your transaction log should not cause performance
problems either. It may cause some performance problems with Full-text
search, but should not cause other problems.
So I normally backup every 15-20 minutes, unless I am log shipping where it
might be more to decrease my exposure to data loss. None of these
considerations should impact database mirroring though.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:83043C4C-1FD2-4F7A-9E9B-9A0C1890908C@.microsoft.com...
> Hi
> Im doing some testing on mirroring at the moment, and have found out about
> the inherent problems with transaction log size that thie replication
> scenario entails. However I have found that frequent trans log backups are
> a
> way around the problem - This sets the vast majority of VLFs in the trans
> log
> to state 0 allowing them to be overwritten by new transactions VLF's.
> Accordingly I dont need to autogrow half as often.
> The question is which is the most harmful of system performance? frequent
> trans log backups or autogrows? if i DO backup frequently i DONT need to
> autogrow, and if i DONT back up frequently i DO need to autogrow...
> which which people choose?
> Cheers
> Alastair Jones
> Methodology Group
Frequent generated log files takes up the whole system drive (C:\)
This happend twice on two different servers. A 33MB reporting service log was
generated every 3 minutes until it took up the entire C:\ drive. The log file
has a header then repeat some message as in the following exerpt for 30+
thousand times. Then it starts another log file endlessly until the whole C:\
drive is used.
It has caused the interruptions of programs hosted on the server. After
delete the logs the reporting service is back to normal. But we need to
prevent this from happening again.
<Header>
<Product>Microsoft SQL Server Reporting Services Version
8.00.743.00</Product>
<Locale>en-US</Locale>
<TimeZone>Eastern Standard Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\LogFiles\ReportServerService__03_29_2005_18_55_53.log</Path>
<SystemName>WSReport</SystemName>
<OSName>Microsoft Windows NT 5.2.3790.0</OSName>
<OSVersion>5.2.3790.0</OSVersion>
</Header>
ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiring old execution log entries
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiration of old execution log entries is complete. Removed 0 entries.
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO: Cleaned
0 broken snapshots, 0 chunks
......
Thanks,
MichaelCheck out this hotfix:
http://support.microsoft.com/kb/885286/
This problem should be fixed in SP2.
--
Adrian M.
MCP
"Michael" <missisummer@.community.nospam> wrote in message
news:C1FE7688-A420-49E1-A17C-0FC736DECC7D@.microsoft.com...
> This happend twice on two different servers. A 33MB reporting service log
> was
> generated every 3 minutes until it took up the entire C:\ drive. The log
> file
> has a header then repeat some message as in the following exerpt for 30+
> thousand times. Then it starts another log file endlessly until the whole
> C:\
> drive is used.
> It has caused the interruptions of programs hosted on the server. After
> delete the logs the reporting service is back to normal. But we need to
> prevent this from happening again.
> <Header>
> <Product>Microsoft SQL Server Reporting Services Version
> 8.00.743.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>Eastern Standard Time</TimeZone>
> <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\LogFiles\ReportServerService__03_29_2005_18_55_53.log</Path>
> <SystemName>WSReport</SystemName>
> <OSName>Microsoft Windows NT 5.2.3790.0</OSName>
> <OSVersion>5.2.3790.0</OSVersion>
> </Header>
> ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
> Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiring old execution log entries
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiration of old execution log entries is complete. Removed 0 entries.
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Cleaned
> 0 broken snapshots, 0 chunks
> ......
> Thanks,
> Michael|||Hi Michael,
Yes, as Adrian M said this is a known issue for us now. A supported hotfix
is now available from Microsoft, but it is only intended to correct the
problem that is described in this article. Only apply it to systems that
are experiencing this specific problem. This hotfix may receive additional
testing. Therefore, if you are not severely affected by this problem, we
recommend that you wait for the next Microsoft SQL Server 2000 Reporting
Services service pack that contains this hotfix.
To resolve this problem immediately, contact Microsoft Product Support
Services to obtain the hotfix. For a complete list of Microsoft Product
Support Services phone numbers and information about support costs, visit
the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
Note that if will be a *free* incident if you only asking for the hotfix
from PSS :)
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||As a quick fix, you can delete old logs using a scheduled job. Check
http://solidqualitylearning.com/blogs/dejan/archive/2004/12/19/225.aspx.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Michael" <missisummer@.community.nospam> wrote in message
news:C1FE7688-A420-49E1-A17C-0FC736DECC7D@.microsoft.com...
> This happend twice on two different servers. A 33MB reporting service log
was
> generated every 3 minutes until it took up the entire C:\ drive. The log
file
> has a header then repeat some message as in the following exerpt for 30+
> thousand times. Then it starts another log file endlessly until the whole
C:\
> drive is used.
> It has caused the interruptions of programs hosted on the server. After
> delete the logs the reporting service is back to normal. But we need to
> prevent this from happening again.
> <Header>
> <Product>Microsoft SQL Server Reporting Services Version
> 8.00.743.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>Eastern Standard Time</TimeZone>
> <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\LogFiles\ReportServerService__03_29_2005_18_55_53.log</Path>
> <SystemName>WSReport</SystemName>
> <OSName>Microsoft Windows NT 5.2.3790.0</OSName>
> <OSVersion>5.2.3790.0</OSVersion>
> </Header>
> ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
> Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiring old execution log entries
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiration of old execution log entries is complete. Removed 0 entries.
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Cleaned
> 0 broken snapshots, 0 chunks
> ......
> Thanks,
> Michael
generated every 3 minutes until it took up the entire C:\ drive. The log file
has a header then repeat some message as in the following exerpt for 30+
thousand times. Then it starts another log file endlessly until the whole C:\
drive is used.
It has caused the interruptions of programs hosted on the server. After
delete the logs the reporting service is back to normal. But we need to
prevent this from happening again.
<Header>
<Product>Microsoft SQL Server Reporting Services Version
8.00.743.00</Product>
<Locale>en-US</Locale>
<TimeZone>Eastern Standard Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\LogFiles\ReportServerService__03_29_2005_18_55_53.log</Path>
<SystemName>WSReport</SystemName>
<OSName>Microsoft Windows NT 5.2.3790.0</OSName>
<OSVersion>5.2.3790.0</OSVersion>
</Header>
ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiring old execution log entries
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiration of old execution log entries is complete. Removed 0 entries.
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO: Cleaned
0 broken snapshots, 0 chunks
......
Thanks,
MichaelCheck out this hotfix:
http://support.microsoft.com/kb/885286/
This problem should be fixed in SP2.
--
Adrian M.
MCP
"Michael" <missisummer@.community.nospam> wrote in message
news:C1FE7688-A420-49E1-A17C-0FC736DECC7D@.microsoft.com...
> This happend twice on two different servers. A 33MB reporting service log
> was
> generated every 3 minutes until it took up the entire C:\ drive. The log
> file
> has a header then repeat some message as in the following exerpt for 30+
> thousand times. Then it starts another log file endlessly until the whole
> C:\
> drive is used.
> It has caused the interruptions of programs hosted on the server. After
> delete the logs the reporting service is back to normal. But we need to
> prevent this from happening again.
> <Header>
> <Product>Microsoft SQL Server Reporting Services Version
> 8.00.743.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>Eastern Standard Time</TimeZone>
> <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\LogFiles\ReportServerService__03_29_2005_18_55_53.log</Path>
> <SystemName>WSReport</SystemName>
> <OSName>Microsoft Windows NT 5.2.3790.0</OSName>
> <OSVersion>5.2.3790.0</OSVersion>
> </Header>
> ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
> Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiring old execution log entries
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiration of old execution log entries is complete. Removed 0 entries.
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Cleaned
> 0 broken snapshots, 0 chunks
> ......
> Thanks,
> Michael|||Hi Michael,
Yes, as Adrian M said this is a known issue for us now. A supported hotfix
is now available from Microsoft, but it is only intended to correct the
problem that is described in this article. Only apply it to systems that
are experiencing this specific problem. This hotfix may receive additional
testing. Therefore, if you are not severely affected by this problem, we
recommend that you wait for the next Microsoft SQL Server 2000 Reporting
Services service pack that contains this hotfix.
To resolve this problem immediately, contact Microsoft Product Support
Services to obtain the hotfix. For a complete list of Microsoft Product
Support Services phone numbers and information about support costs, visit
the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
Note that if will be a *free* incident if you only asking for the hotfix
from PSS :)
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||As a quick fix, you can delete old logs using a scheduled job. Check
http://solidqualitylearning.com/blogs/dejan/archive/2004/12/19/225.aspx.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Michael" <missisummer@.community.nospam> wrote in message
news:C1FE7688-A420-49E1-A17C-0FC736DECC7D@.microsoft.com...
> This happend twice on two different servers. A 33MB reporting service log
was
> generated every 3 minutes until it took up the entire C:\ drive. The log
file
> has a header then repeat some message as in the following exerpt for 30+
> thousand times. Then it starts another log file endlessly until the whole
C:\
> drive is used.
> It has caused the interruptions of programs hosted on the server. After
> delete the logs the reporting service is back to normal. But we need to
> prevent this from happening again.
> <Header>
> <Product>Microsoft SQL Server Reporting Services Version
> 8.00.743.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>Eastern Standard Time</TimeZone>
> <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\LogFiles\ReportServerService__03_29_2005_18_55_53.log</Path>
> <SystemName>WSReport</SystemName>
> <OSName>Microsoft Windows NT 5.2.3790.0</OSName>
> <OSVersion>5.2.3790.0</OSVersion>
> </Header>
> ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
> Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiring old execution log entries
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiration of old execution log entries is complete. Removed 0 entries.
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Cleaned
> 0 broken snapshots, 0 chunks
> ......
> Thanks,
> Michael
Frequent generated log file takes up the entire system drive
This happend twice on two different servers. A 33MB reporting service log was
generated every 3 minutes until it took up the entire C:\ drive. The log file
has a header then repeat some message as in the following exerpt for 30+
thousand times. Then it starts another log file endlessly until the whole C:\
drive is used.
It has caused the interruptions of programs hosted on the server. After
delete the logs the reporting service is back to normal. But we need to
prevent this from happening again.
<Header>
<Product>Microsoft SQL Server Reporting Services Version
8.00.743.00</Product>
<Locale>en-US</Locale>
<TimeZone>Eastern Standard Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\LogFiles\ReportServerService__03_29_2005_ 18_55_53.log</Path>
<SystemName>WSReport</SystemName>
<OSName>Microsoft Windows NT 5.2.3790.0</OSName>
<OSVersion>5.2.3790.0</OSVersion>
</Header>
ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiring old execution log entries
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiration of old execution log entries is complete. Removed 0 entries.
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO: Cleaned
0 broken snapshots, 0 chunks
.......
Thanks,
Michael
This is a known bug fixed in the imminent SP2 for Reporting Services.
http://support.microsoft.com/kb/885286/
You can get a hotfix, details are in the KB article. What've I've tended to
do is just set up a scheduled task to check the size of the logs folder and
if it gets over a certain size start deleting old logs.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Michael" <missisummer@.community.nospam> wrote in message
news:6A7FD5FA-AA33-4B15-8472-D204B6D8B75A@.microsoft.com...
> This happend twice on two different servers. A 33MB reporting service log
> was
> generated every 3 minutes until it took up the entire C:\ drive. The log
> file
> has a header then repeat some message as in the following exerpt for 30+
> thousand times. Then it starts another log file endlessly until the whole
> C:\
> drive is used.
> It has caused the interruptions of programs hosted on the server. After
> delete the logs the reporting service is back to normal. But we need to
> prevent this from happening again.
> <Header>
> <Product>Microsoft SQL Server Reporting Services Version
> 8.00.743.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>Eastern Standard Time</TimeZone>
> <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\LogFiles\ReportServerService__03_29_2005_ 18_55_53.log</Path>
> <SystemName>WSReport</SystemName>
> <OSName>Microsoft Windows NT 5.2.3790.0</OSName>
> <OSVersion>5.2.3790.0</OSVersion>
> </Header>
> ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
> Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiring old execution log entries
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiration of old execution log entries is complete. Removed 0 entries.
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Cleaned
> 0 broken snapshots, 0 chunks
> ......
> Thanks,
> Michael
|||Jasper,
My DBA is looking for this. Would you be able to send me the code for this
scheduled task. I'd apprciate it.
Bill..
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%238P0SFuNFHA.604@.TK2MSFTNGP10.phx.gbl...
> This is a known bug fixed in the imminent SP2 for Reporting Services.
> http://support.microsoft.com/kb/885286/
> You can get a hotfix, details are in the KB article. What've I've tended
> to do is just set up a scheduled task to check the size of the logs folder
> and if it gets over a certain size start deleting old logs.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Michael" <missisummer@.community.nospam> wrote in message
> news:6A7FD5FA-AA33-4B15-8472-D204B6D8B75A@.microsoft.com...
>
|||> Jasper,
> My DBA is looking for this. Would you be able to send me the code for
this
> scheduled task. I'd apprciate it.
> Bill..
You can use something I create couple of months ago for diferent purposes,
but can apply to this problem as well:
http://solidqualitylearning.com/blog...2/19/225.aspx.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
generated every 3 minutes until it took up the entire C:\ drive. The log file
has a header then repeat some message as in the following exerpt for 30+
thousand times. Then it starts another log file endlessly until the whole C:\
drive is used.
It has caused the interruptions of programs hosted on the server. After
delete the logs the reporting service is back to normal. But we need to
prevent this from happening again.
<Header>
<Product>Microsoft SQL Server Reporting Services Version
8.00.743.00</Product>
<Locale>en-US</Locale>
<TimeZone>Eastern Standard Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\LogFiles\ReportServerService__03_29_2005_ 18_55_53.log</Path>
<SystemName>WSReport</SystemName>
<OSName>Microsoft Windows NT 5.2.3790.0</OSName>
<OSVersion>5.2.3790.0</OSVersion>
</Header>
ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiring old execution log entries
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiration of old execution log entries is complete. Removed 0 entries.
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO: Cleaned
0 broken snapshots, 0 chunks
.......
Thanks,
Michael
This is a known bug fixed in the imminent SP2 for Reporting Services.
http://support.microsoft.com/kb/885286/
You can get a hotfix, details are in the KB article. What've I've tended to
do is just set up a scheduled task to check the size of the logs folder and
if it gets over a certain size start deleting old logs.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Michael" <missisummer@.community.nospam> wrote in message
news:6A7FD5FA-AA33-4B15-8472-D204B6D8B75A@.microsoft.com...
> This happend twice on two different servers. A 33MB reporting service log
> was
> generated every 3 minutes until it took up the entire C:\ drive. The log
> file
> has a header then repeat some message as in the following exerpt for 30+
> thousand times. Then it starts another log file endlessly until the whole
> C:\
> drive is used.
> It has caused the interruptions of programs hosted on the server. After
> delete the logs the reporting service is back to normal. But we need to
> prevent this from happening again.
> <Header>
> <Product>Microsoft SQL Server Reporting Services Version
> 8.00.743.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>Eastern Standard Time</TimeZone>
> <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\LogFiles\ReportServerService__03_29_2005_ 18_55_53.log</Path>
> <SystemName>WSReport</SystemName>
> <OSName>Microsoft Windows NT 5.2.3790.0</OSName>
> <OSVersion>5.2.3790.0</OSVersion>
> </Header>
> ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
> Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiring old execution log entries
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiration of old execution log entries is complete. Removed 0 entries.
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Cleaned
> 0 broken snapshots, 0 chunks
> ......
> Thanks,
> Michael
|||Jasper,
My DBA is looking for this. Would you be able to send me the code for this
scheduled task. I'd apprciate it.
Bill..
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%238P0SFuNFHA.604@.TK2MSFTNGP10.phx.gbl...
> This is a known bug fixed in the imminent SP2 for Reporting Services.
> http://support.microsoft.com/kb/885286/
> You can get a hotfix, details are in the KB article. What've I've tended
> to do is just set up a scheduled task to check the size of the logs folder
> and if it gets over a certain size start deleting old logs.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Michael" <missisummer@.community.nospam> wrote in message
> news:6A7FD5FA-AA33-4B15-8472-D204B6D8B75A@.microsoft.com...
>
|||> Jasper,
> My DBA is looking for this. Would you be able to send me the code for
this
> scheduled task. I'd apprciate it.
> Bill..
You can use something I create couple of months ago for diferent purposes,
but can apply to this problem as well:
http://solidqualitylearning.com/blog...2/19/225.aspx.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Freqent generated log files took up the entire system drive
This happend twice on two different servers. A 33MB reporting service log was
generated every 3 minutes until it took up the entire C:\ drive. The log file
has a header then repeat some message as in the following exerpt for 30+
thousand times. Then it starts another log file endlessly until the whole C:\
drive is used.
It has caused the interruptions of programs hosted on the server. After
delete the logs the reporting service is back to normal. But we need to
prevent this from happening again.
<Header>
<Product>Microsoft SQL Server Reporting Services Version
8.00.743.00</Product>
<Locale>en-US</Locale>
<TimeZone>Eastern Standard Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\LogFiles\ReportServerService__03_29_2005_ 18_55_53.log</Path>
<SystemName>WSReport</SystemName>
<OSName>Microsoft Windows NT 5.2.3790.0</OSName>
<OSVersion>5.2.3790.0</OSVersion>
</Header>
ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiring old execution log entries
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiration of old execution log entries is complete. Removed 0 entries.
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO: Cleaned
0 broken snapshots, 0 chunks
.......
Thanks,
Michael
Hi Michael,
I found you have another two posts with the same topic in
microsoft.public.sqlserver.reportingsvcs and
microsoft.public.sqlserver.tools, both has response from MVP and community
members. To keep the integrity of newsgroup and benefit others. I will
follwo up the questions in microsoft.public.sqlserver.reportingsvcs
instead of these two.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
generated every 3 minutes until it took up the entire C:\ drive. The log file
has a header then repeat some message as in the following exerpt for 30+
thousand times. Then it starts another log file endlessly until the whole C:\
drive is used.
It has caused the interruptions of programs hosted on the server. After
delete the logs the reporting service is back to normal. But we need to
prevent this from happening again.
<Header>
<Product>Microsoft SQL Server Reporting Services Version
8.00.743.00</Product>
<Locale>en-US</Locale>
<TimeZone>Eastern Standard Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\LogFiles\ReportServerService__03_29_2005_ 18_55_53.log</Path>
<SystemName>WSReport</SystemName>
<OSName>Microsoft Windows NT 5.2.3790.0</OSName>
<OSVersion>5.2.3790.0</OSVersion>
</Header>
ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiring old execution log entries
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiration of old execution log entries is complete. Removed 0 entries.
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO: Cleaned
0 broken snapshots, 0 chunks
.......
Thanks,
Michael
Hi Michael,
I found you have another two posts with the same topic in
microsoft.public.sqlserver.reportingsvcs and
microsoft.public.sqlserver.tools, both has response from MVP and community
members. To keep the integrity of newsgroup and benefit others. I will
follwo up the questions in microsoft.public.sqlserver.reportingsvcs
instead of these two.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
Wednesday, March 7, 2012
Freeing up Log Free Space
We are running SQL 2005 SP1.
We have a T-Log that is set for 10gb, set to auto grow by 200mb.
This database should really be set to SIMPLE, but until we can get the
approval and window of time we have to keep it in FULL recovery mode.
We would like to keep the T-Log at 10gb, but free up space, with the hope
that the T-Log should have to grow but very little, if any at all.
We currently have 1.6gb space available.
After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
available.
Any idea how to reclaim some space?
Message posted via http://www.droptable.com
Check "dbcc shrinkfile" in BOL. Backing up (in your case WITH TRUNCATE_ONLY)
the transaction log more frecuently could help it to reuse the space from
inactive transactions.
AMB
"cbrichards via droptable.com" wrote:
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.droptable.com
>
|||cbrichards,
This article and the articles to which it links can be a help to you in
shrinking the log file and in thinking about what you want to do:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
One way to free up space in the log, of course, is to do BACKUP LOG (without
TRUNCATE) periodically and then save the backups for as long as you need
them, even if just a day or so.
However, if you are truncating the log, then what is the point of FULL
recovery mode? This provides no more transactional integrity or rollback
ability than SIMPLE mode. If you are always truncating, then go ahead and
set the database to SIMPLE mode, since that is essentially what you have.
RLF
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:749f8542dc410@.uwe...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.droptable.com
>
|||Thanks Russell.
One thing is for sure, I do not want to shrink the file.
In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
Russell Fields wrote:[vbcol=seagreen]
>cbrichards,
>This article and the articles to which it links can be a help to you in
>shrinking the log file and in thinking about what you want to do:
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>One way to free up space in the log, of course, is to do BACKUP LOG (without
>TRUNCATE) periodically and then save the backups for as long as you need
>them, even if just a day or so.
>However, if you are truncating the log, then what is the point of FULL
>recovery mode? This provides no more transactional integrity or rollback
>ability than SIMPLE mode. If you are always truncating, then go ahead and
>set the database to SIMPLE mode, since that is essentially what you have.
>RLF
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1
|||> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
Only if there are no active transactions in the last virtual log file. You
can check for open transactions using DBCC OPENTRAN.
A
|||Both of these commands do the same thing to your log. The both free up space
from inactive virtual log files so that it can be reused.
Neither will change the physical size of the log file.
The difference between these commands is that the second one will save the
transactions that it is clearing out, but the first one won't.
The only way to reclaim physical space is to physically shrink the log file.
Note that physically shrinking the log file is very different from physical
shrinking a datafile. Most of the warnings in Tibor's article have to do
with shrinking data files.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:74a00a55f6cf9@.uwe...
> Thanks Russell.
> One thing is for sure, I do not want to shrink the file.
> In attempting to understand your comment as it relates to mine:
> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
> 2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
> Russell Fields wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1
>
|||Now I am really confused. In repeating my earlier thread:
"In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?"
So you are saying that in both 1 and 2 that space is freed "Only if there are
no active transactions in the last virtual log file."?
Aaron Bertrand [SQL Server MVP] wrote:
>Only if there are no active transactions in the last virtual log file. You
>can check for open transactions using DBCC OPENTRAN.
>A
Message posted via http://www.droptable.com
|||Thanks Kalen.
I think we are getting closer to a final answer on my inquiry.
Since
1. we cannot set our system to SIMPLE recovery for a couple more weeks, and
2. we do not care to keep TLOG backup files, and
3. we want to keep our TLOG file at 10gb and reuse its space, then
we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY to
reuse the 10gb space, correct?
Kalen Delaney wrote:[vbcol=seagreen]
>Both of these commands do the same thing to your log. The both free up space
>from inactive virtual log files so that it can be reused.
>Neither will change the physical size of the log file.
>The difference between these commands is that the second one will save the
>transactions that it is clearing out, but the first one won't.
>The only way to reclaim physical space is to physically shrink the log file.
>Note that physically shrinking the log file is very different from physical
>shrinking a datafile. Most of the warnings in Tibor's article have to do
>with shrinking data files.
>[quoted text clipped - 28 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1
|||Correct. - RLF
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:74a08b9896a7f@.uwe...
> Thanks Kalen.
> I think we are getting closer to a final answer on my inquiry.
> Since
> 1. we cannot set our system to SIMPLE recovery for a couple more weeks,
> and
> 2. we do not care to keep TLOG backup files, and
> 3. we want to keep our TLOG file at 10gb and reuse its space, then
> we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY
> to
> reuse the 10gb space, correct?
> Kalen Delaney wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1
>
|||In article <749f8542dc410@.uwe>, u3288@.uwe says...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
>
as others have said it is a 2-step process
(1) backup the log w/ Truncate_only to clear out as many of the VLF's as
are not in use or hung up on some long-rinning transaction
(2) DBCC Shrinkfile being sure to point to the correct file number --
probably 2.
I just did this to reclaim space from a 6G logfile that had never been
backed up and allowed to just grow. Of the 500M I sized it at -- about
25% of the current DB size -- only 14.3M was actually in use.
Graham (Pete) Berry
PeteBerry@.Caltech.edu
We have a T-Log that is set for 10gb, set to auto grow by 200mb.
This database should really be set to SIMPLE, but until we can get the
approval and window of time we have to keep it in FULL recovery mode.
We would like to keep the T-Log at 10gb, but free up space, with the hope
that the T-Log should have to grow but very little, if any at all.
We currently have 1.6gb space available.
After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
available.
Any idea how to reclaim some space?
Message posted via http://www.droptable.com
Check "dbcc shrinkfile" in BOL. Backing up (in your case WITH TRUNCATE_ONLY)
the transaction log more frecuently could help it to reuse the space from
inactive transactions.
AMB
"cbrichards via droptable.com" wrote:
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.droptable.com
>
|||cbrichards,
This article and the articles to which it links can be a help to you in
shrinking the log file and in thinking about what you want to do:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
One way to free up space in the log, of course, is to do BACKUP LOG (without
TRUNCATE) periodically and then save the backups for as long as you need
them, even if just a day or so.
However, if you are truncating the log, then what is the point of FULL
recovery mode? This provides no more transactional integrity or rollback
ability than SIMPLE mode. If you are always truncating, then go ahead and
set the database to SIMPLE mode, since that is essentially what you have.
RLF
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:749f8542dc410@.uwe...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.droptable.com
>
|||Thanks Russell.
One thing is for sure, I do not want to shrink the file.
In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
Russell Fields wrote:[vbcol=seagreen]
>cbrichards,
>This article and the articles to which it links can be a help to you in
>shrinking the log file and in thinking about what you want to do:
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>One way to free up space in the log, of course, is to do BACKUP LOG (without
>TRUNCATE) periodically and then save the backups for as long as you need
>them, even if just a day or so.
>However, if you are truncating the log, then what is the point of FULL
>recovery mode? This provides no more transactional integrity or rollback
>ability than SIMPLE mode. If you are always truncating, then go ahead and
>set the database to SIMPLE mode, since that is essentially what you have.
>RLF
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1
|||> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
Only if there are no active transactions in the last virtual log file. You
can check for open transactions using DBCC OPENTRAN.
A
|||Both of these commands do the same thing to your log. The both free up space
from inactive virtual log files so that it can be reused.
Neither will change the physical size of the log file.
The difference between these commands is that the second one will save the
transactions that it is clearing out, but the first one won't.
The only way to reclaim physical space is to physically shrink the log file.
Note that physically shrinking the log file is very different from physical
shrinking a datafile. Most of the warnings in Tibor's article have to do
with shrinking data files.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:74a00a55f6cf9@.uwe...
> Thanks Russell.
> One thing is for sure, I do not want to shrink the file.
> In attempting to understand your comment as it relates to mine:
> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
> 2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
> Russell Fields wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1
>
|||Now I am really confused. In repeating my earlier thread:
"In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?"
So you are saying that in both 1 and 2 that space is freed "Only if there are
no active transactions in the last virtual log file."?
Aaron Bertrand [SQL Server MVP] wrote:
>Only if there are no active transactions in the last virtual log file. You
>can check for open transactions using DBCC OPENTRAN.
>A
Message posted via http://www.droptable.com
|||Thanks Kalen.
I think we are getting closer to a final answer on my inquiry.
Since
1. we cannot set our system to SIMPLE recovery for a couple more weeks, and
2. we do not care to keep TLOG backup files, and
3. we want to keep our TLOG file at 10gb and reuse its space, then
we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY to
reuse the 10gb space, correct?
Kalen Delaney wrote:[vbcol=seagreen]
>Both of these commands do the same thing to your log. The both free up space
>from inactive virtual log files so that it can be reused.
>Neither will change the physical size of the log file.
>The difference between these commands is that the second one will save the
>transactions that it is clearing out, but the first one won't.
>The only way to reclaim physical space is to physically shrink the log file.
>Note that physically shrinking the log file is very different from physical
>shrinking a datafile. Most of the warnings in Tibor's article have to do
>with shrinking data files.
>[quoted text clipped - 28 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1
|||Correct. - RLF
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:74a08b9896a7f@.uwe...
> Thanks Kalen.
> I think we are getting closer to a final answer on my inquiry.
> Since
> 1. we cannot set our system to SIMPLE recovery for a couple more weeks,
> and
> 2. we do not care to keep TLOG backup files, and
> 3. we want to keep our TLOG file at 10gb and reuse its space, then
> we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY
> to
> reuse the 10gb space, correct?
> Kalen Delaney wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1
>
|||In article <749f8542dc410@.uwe>, u3288@.uwe says...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
>
as others have said it is a 2-step process
(1) backup the log w/ Truncate_only to clear out as many of the VLF's as
are not in use or hung up on some long-rinning transaction
(2) DBCC Shrinkfile being sure to point to the correct file number --
probably 2.
I just did this to reclaim space from a 6G logfile that had never been
backed up and allowed to just grow. Of the 500M I sized it at -- about
25% of the current DB size -- only 14.3M was actually in use.
Graham (Pete) Berry
PeteBerry@.Caltech.edu
Freeing up Log Free Space
We are running SQL 2005 SP1.
We have a T-Log that is set for 10gb, set to auto grow by 200mb.
This database should really be set to SIMPLE, but until we can get the
approval and window of time we have to keep it in FULL recovery mode.
We would like to keep the T-Log at 10gb, but free up space, with the hope
that the T-Log should have to grow but very little, if any at all.
We currently have 1.6gb space available.
After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
available.
Any idea how to reclaim some space?
Message posted via http://www.droptable.comCheck "dbcc shrinkfile" in BOL. Backing up (in your case WITH TRUNCATE_ONLY)
the transaction log more frecuently could help it to reuse the space from
inactive transactions.
AMB
"cbrichards via droptable.com" wrote:
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.droptable.com
>|||cbrichards,
This article and the articles to which it links can be a help to you in
shrinking the log file and in thinking about what you want to do:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
One way to free up space in the log, of course, is to do BACKUP LOG (without
TRUNCATE) periodically and then save the backups for as long as you need
them, even if just a day or so.
However, if you are truncating the log, then what is the point of FULL
recovery mode? This provides no more transactional integrity or rollback
ability than SIMPLE mode. If you are always truncating, then go ahead and
set the database to SIMPLE mode, since that is essentially what you have.
RLF
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:749f8542dc410@.uwe...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.droptable.com
>|||Thanks Russell.
One thing is for sure, I do not want to shrink the file.
In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
Russell Fields wrote:[vbcol=seagreen]
>cbrichards,
>This article and the articles to which it links can be a help to you in
>shrinking the log file and in thinking about what you want to do:
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>One way to free up space in the log, of course, is to do BACKUP LOG (withou
t
>TRUNCATE) periodically and then save the backups for as long as you need
>them, even if just a day or so.
>However, if you are truncating the log, then what is the point of FULL
>recovery mode? This provides no more transactional integrity or rollback
>ability than SIMPLE mode. If you are always truncating, then go ahead and
>set the database to SIMPLE mode, since that is essentially what you have.
>RLF
>
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200707/1|||> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
Only if there are no active transactions in the last virtual log file. You
can check for open transactions using DBCC OPENTRAN.
A|||Both of these commands do the same thing to your log. The both free up space
from inactive virtual log files so that it can be reused.
Neither will change the physical size of the log file.
The difference between these commands is that the second one will save the
transactions that it is clearing out, but the first one won't.
The only way to reclaim physical space is to physically shrink the log file.
Note that physically shrinking the log file is very different from physical
shrinking a datafile. Most of the warnings in Tibor's article have to do
with shrinking data files.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:74a00a55f6cf9@.uwe...
> Thanks Russell.
> One thing is for sure, I do not want to shrink the file.
> In attempting to understand your comment as it relates to mine:
> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
> 2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
> Russell Fields wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200707/1
>|||Now I am really confused. In repeating my earlier thread:
"In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?"
So you are saying that in both 1 and 2 that space is freed "Only if there ar
e
no active transactions in the last virtual log file."?
Aaron Bertrand [SQL Server MVP] wrote:
>Only if there are no active transactions in the last virtual log file. You
>can check for open transactions using DBCC OPENTRAN.
>A
Message posted via http://www.droptable.com|||Thanks Kalen.
I think we are getting closer to a final answer on my inquiry.
Since
1. we cannot set our system to SIMPLE recovery for a couple more weeks, and
2. we do not care to keep TLOG backup files, and
3. we want to keep our TLOG file at 10gb and reuse its space, then
we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY to
reuse the 10gb space, correct?
Kalen Delaney wrote:[vbcol=seagreen]
>Both of these commands do the same thing to your log. The both free up spac
e
>from inactive virtual log files so that it can be reused.
>Neither will change the physical size of the log file.
>The difference between these commands is that the second one will save the
>transactions that it is clearing out, but the first one won't.
>The only way to reclaim physical space is to physically shrink the log file
.
>Note that physically shrinking the log file is very different from physical
>shrinking a datafile. Most of the warnings in Tibor's article have to do
>with shrinking data files.
>
>[quoted text clipped - 28 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200707/1|||Correct. - RLF
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:74a08b9896a7f@.uwe...
> Thanks Kalen.
> I think we are getting closer to a final answer on my inquiry.
> Since
> 1. we cannot set our system to SIMPLE recovery for a couple more weeks,
> and
> 2. we do not care to keep TLOG backup files, and
> 3. we want to keep our TLOG file at 10gb and reuse its space, then
> we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY
> to
> reuse the 10gb space, correct?
> Kalen Delaney wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200707/1
>|||In article <749f8542dc410@.uwe>, u3288@.uwe says...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
>
as others have said it is a 2-step process
(1) backup the log w/ Truncate_only to clear out as many of the VLF's as
are not in use or hung up on some long-rinning transaction
(2) DBCC Shrinkfile being sure to point to the correct file number --
probably 2.
I just did this to reclaim space from a 6G logfile that had never been
backed up and allowed to just grow. Of the 500M I sized it at -- about
25% of the current DB size -- only 14.3M was actually in use.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu
We have a T-Log that is set for 10gb, set to auto grow by 200mb.
This database should really be set to SIMPLE, but until we can get the
approval and window of time we have to keep it in FULL recovery mode.
We would like to keep the T-Log at 10gb, but free up space, with the hope
that the T-Log should have to grow but very little, if any at all.
We currently have 1.6gb space available.
After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
available.
Any idea how to reclaim some space?
Message posted via http://www.droptable.comCheck "dbcc shrinkfile" in BOL. Backing up (in your case WITH TRUNCATE_ONLY)
the transaction log more frecuently could help it to reuse the space from
inactive transactions.
AMB
"cbrichards via droptable.com" wrote:
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.droptable.com
>|||cbrichards,
This article and the articles to which it links can be a help to you in
shrinking the log file and in thinking about what you want to do:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
One way to free up space in the log, of course, is to do BACKUP LOG (without
TRUNCATE) periodically and then save the backups for as long as you need
them, even if just a day or so.
However, if you are truncating the log, then what is the point of FULL
recovery mode? This provides no more transactional integrity or rollback
ability than SIMPLE mode. If you are always truncating, then go ahead and
set the database to SIMPLE mode, since that is essentially what you have.
RLF
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:749f8542dc410@.uwe...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.droptable.com
>|||Thanks Russell.
One thing is for sure, I do not want to shrink the file.
In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
Russell Fields wrote:[vbcol=seagreen]
>cbrichards,
>This article and the articles to which it links can be a help to you in
>shrinking the log file and in thinking about what you want to do:
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>One way to free up space in the log, of course, is to do BACKUP LOG (withou
t
>TRUNCATE) periodically and then save the backups for as long as you need
>them, even if just a day or so.
>However, if you are truncating the log, then what is the point of FULL
>recovery mode? This provides no more transactional integrity or rollback
>ability than SIMPLE mode. If you are always truncating, then go ahead and
>set the database to SIMPLE mode, since that is essentially what you have.
>RLF
>
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200707/1|||> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
Only if there are no active transactions in the last virtual log file. You
can check for open transactions using DBCC OPENTRAN.
A|||Both of these commands do the same thing to your log. The both free up space
from inactive virtual log files so that it can be reused.
Neither will change the physical size of the log file.
The difference between these commands is that the second one will save the
transactions that it is clearing out, but the first one won't.
The only way to reclaim physical space is to physically shrink the log file.
Note that physically shrinking the log file is very different from physical
shrinking a datafile. Most of the warnings in Tibor's article have to do
with shrinking data files.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:74a00a55f6cf9@.uwe...
> Thanks Russell.
> One thing is for sure, I do not want to shrink the file.
> In attempting to understand your comment as it relates to mine:
> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
> 2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
> Russell Fields wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200707/1
>|||Now I am really confused. In repeating my earlier thread:
"In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?"
So you are saying that in both 1 and 2 that space is freed "Only if there ar
e
no active transactions in the last virtual log file."?
Aaron Bertrand [SQL Server MVP] wrote:
>Only if there are no active transactions in the last virtual log file. You
>can check for open transactions using DBCC OPENTRAN.
>A
Message posted via http://www.droptable.com|||Thanks Kalen.
I think we are getting closer to a final answer on my inquiry.
Since
1. we cannot set our system to SIMPLE recovery for a couple more weeks, and
2. we do not care to keep TLOG backup files, and
3. we want to keep our TLOG file at 10gb and reuse its space, then
we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY to
reuse the 10gb space, correct?
Kalen Delaney wrote:[vbcol=seagreen]
>Both of these commands do the same thing to your log. The both free up spac
e
>from inactive virtual log files so that it can be reused.
>Neither will change the physical size of the log file.
>The difference between these commands is that the second one will save the
>transactions that it is clearing out, but the first one won't.
>The only way to reclaim physical space is to physically shrink the log file
.
>Note that physically shrinking the log file is very different from physical
>shrinking a datafile. Most of the warnings in Tibor's article have to do
>with shrinking data files.
>
>[quoted text clipped - 28 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200707/1|||Correct. - RLF
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:74a08b9896a7f@.uwe...
> Thanks Kalen.
> I think we are getting closer to a final answer on my inquiry.
> Since
> 1. we cannot set our system to SIMPLE recovery for a couple more weeks,
> and
> 2. we do not care to keep TLOG backup files, and
> 3. we want to keep our TLOG file at 10gb and reuse its space, then
> we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY
> to
> reuse the 10gb space, correct?
> Kalen Delaney wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200707/1
>|||In article <749f8542dc410@.uwe>, u3288@.uwe says...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
>
as others have said it is a 2-step process
(1) backup the log w/ Truncate_only to clear out as many of the VLF's as
are not in use or hung up on some long-rinning transaction
(2) DBCC Shrinkfile being sure to point to the correct file number --
probably 2.
I just did this to reclaim space from a 6G logfile that had never been
backed up and allowed to just grow. Of the 500M I sized it at -- about
25% of the current DB size -- only 14.3M was actually in use.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu
Freeing up Log Free Space
We are running SQL 2005 SP1.
We have a T-Log that is set for 10gb, set to auto grow by 200mb.
This database should really be set to SIMPLE, but until we can get the
approval and window of time we have to keep it in FULL recovery mode.
We would like to keep the T-Log at 10gb, but free up space, with the hope
that the T-Log should have to grow but very little, if any at all.
We currently have 1.6gb space available.
After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
available.
Any idea how to reclaim some space?
--
Message posted via http://www.sqlmonster.comCheck "dbcc shrinkfile" in BOL. Backing up (in your case WITH TRUNCATE_ONLY)
the transaction log more frecuently could help it to reuse the space from
inactive transactions.
AMB
"cbrichards via SQLMonster.com" wrote:
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.sqlmonster.com
>|||cbrichards,
This article and the articles to which it links can be a help to you in
shrinking the log file and in thinking about what you want to do:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
One way to free up space in the log, of course, is to do BACKUP LOG (without
TRUNCATE) periodically and then save the backups for as long as you need
them, even if just a day or so.
However, if you are truncating the log, then what is the point of FULL
recovery mode? This provides no more transactional integrity or rollback
ability than SIMPLE mode. If you are always truncating, then go ahead and
set the database to SIMPLE mode, since that is essentially what you have.
RLF
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:749f8542dc410@.uwe...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.sqlmonster.com
>|||Thanks Russell.
One thing is for sure, I do not want to shrink the file.
In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
Russell Fields wrote:
>cbrichards,
>This article and the articles to which it links can be a help to you in
>shrinking the log file and in thinking about what you want to do:
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>One way to free up space in the log, of course, is to do BACKUP LOG (without
>TRUNCATE) periodically and then save the backups for as long as you need
>them, even if just a day or so.
>However, if you are truncating the log, then what is the point of FULL
>recovery mode? This provides no more transactional integrity or rollback
>ability than SIMPLE mode. If you are always truncating, then go ahead and
>set the database to SIMPLE mode, since that is essentially what you have.
>RLF
>> We are running SQL 2005 SP1.
>[quoted text clipped - 12 lines]
>> Any idea how to reclaim some space?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1|||> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
Only if there are no active transactions in the last virtual log file. You
can check for open transactions using DBCC OPENTRAN.
A|||Both of these commands do the same thing to your log. The both free up space
from inactive virtual log files so that it can be reused.
Neither will change the physical size of the log file.
The difference between these commands is that the second one will save the
transactions that it is clearing out, but the first one won't.
The only way to reclaim physical space is to physically shrink the log file.
Note that physically shrinking the log file is very different from physical
shrinking a datafile. Most of the warnings in Tibor's article have to do
with shrinking data files.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:74a00a55f6cf9@.uwe...
> Thanks Russell.
> One thing is for sure, I do not want to shrink the file.
> In attempting to understand your comment as it relates to mine:
> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
> 2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
> Russell Fields wrote:
>>cbrichards,
>>This article and the articles to which it links can be a help to you in
>>shrinking the log file and in thinking about what you want to do:
>>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>One way to free up space in the log, of course, is to do BACKUP LOG
>>(without
>>TRUNCATE) periodically and then save the backups for as long as you need
>>them, even if just a day or so.
>>However, if you are truncating the log, then what is the point of FULL
>>recovery mode? This provides no more transactional integrity or rollback
>>ability than SIMPLE mode. If you are always truncating, then go ahead and
>>set the database to SIMPLE mode, since that is essentially what you have.
>>RLF
>> We are running SQL 2005 SP1.
>>[quoted text clipped - 12 lines]
>> Any idea how to reclaim some space?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1
>|||Now I am really confused. In repeating my earlier thread:
"In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?"
So you are saying that in both 1 and 2 that space is freed "Only if there are
no active transactions in the last virtual log file."?
Aaron Bertrand [SQL Server MVP] wrote:
>> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
>> TLog?
>Only if there are no active transactions in the last virtual log file. You
>can check for open transactions using DBCC OPENTRAN.
>A
--
Message posted via http://www.sqlmonster.com|||Thanks Kalen.
I think we are getting closer to a final answer on my inquiry.
Since
1. we cannot set our system to SIMPLE recovery for a couple more weeks, and
2. we do not care to keep TLOG backup files, and
3. we want to keep our TLOG file at 10gb and reuse its space, then
we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY to
reuse the 10gb space, correct?
Kalen Delaney wrote:
>Both of these commands do the same thing to your log. The both free up space
>from inactive virtual log files so that it can be reused.
>Neither will change the physical size of the log file.
>The difference between these commands is that the second one will save the
>transactions that it is clearing out, but the first one won't.
>The only way to reclaim physical space is to physically shrink the log file.
>Note that physically shrinking the log file is very different from physical
>shrinking a datafile. Most of the warnings in Tibor's article have to do
>with shrinking data files.
>> Thanks Russell.
>[quoted text clipped - 28 lines]
>> Any idea how to reclaim some space?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1|||Correct. - RLF
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:74a08b9896a7f@.uwe...
> Thanks Kalen.
> I think we are getting closer to a final answer on my inquiry.
> Since
> 1. we cannot set our system to SIMPLE recovery for a couple more weeks,
> and
> 2. we do not care to keep TLOG backup files, and
> 3. we want to keep our TLOG file at 10gb and reuse its space, then
> we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY
> to
> reuse the 10gb space, correct?
> Kalen Delaney wrote:
>>Both of these commands do the same thing to your log. The both free up
>>space
>>from inactive virtual log files so that it can be reused.
>>Neither will change the physical size of the log file.
>>The difference between these commands is that the second one will save the
>>transactions that it is clearing out, but the first one won't.
>>The only way to reclaim physical space is to physically shrink the log
>>file.
>>Note that physically shrinking the log file is very different from
>>physical
>>shrinking a datafile. Most of the warnings in Tibor's article have to do
>>with shrinking data files.
>> Thanks Russell.
>>[quoted text clipped - 28 lines]
>> Any idea how to reclaim some space?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1
>|||In article <749f8542dc410@.uwe>, u3288@.uwe says...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
>
as others have said it is a 2-step process
(1) backup the log w/ Truncate_only to clear out as many of the VLF's as
are not in use or hung up on some long-rinning transaction
(2) DBCC Shrinkfile being sure to point to the correct file number --
probably 2.
I just did this to reclaim space from a 6G logfile that had never been
backed up and allowed to just grow. Of the 500M I sized it at -- about
25% of the current DB size -- only 14.3M was actually in use.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu
We have a T-Log that is set for 10gb, set to auto grow by 200mb.
This database should really be set to SIMPLE, but until we can get the
approval and window of time we have to keep it in FULL recovery mode.
We would like to keep the T-Log at 10gb, but free up space, with the hope
that the T-Log should have to grow but very little, if any at all.
We currently have 1.6gb space available.
After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
available.
Any idea how to reclaim some space?
--
Message posted via http://www.sqlmonster.comCheck "dbcc shrinkfile" in BOL. Backing up (in your case WITH TRUNCATE_ONLY)
the transaction log more frecuently could help it to reuse the space from
inactive transactions.
AMB
"cbrichards via SQLMonster.com" wrote:
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.sqlmonster.com
>|||cbrichards,
This article and the articles to which it links can be a help to you in
shrinking the log file and in thinking about what you want to do:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
One way to free up space in the log, of course, is to do BACKUP LOG (without
TRUNCATE) periodically and then save the backups for as long as you need
them, even if just a day or so.
However, if you are truncating the log, then what is the point of FULL
recovery mode? This provides no more transactional integrity or rollback
ability than SIMPLE mode. If you are always truncating, then go ahead and
set the database to SIMPLE mode, since that is essentially what you have.
RLF
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:749f8542dc410@.uwe...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.sqlmonster.com
>|||Thanks Russell.
One thing is for sure, I do not want to shrink the file.
In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
Russell Fields wrote:
>cbrichards,
>This article and the articles to which it links can be a help to you in
>shrinking the log file and in thinking about what you want to do:
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>One way to free up space in the log, of course, is to do BACKUP LOG (without
>TRUNCATE) periodically and then save the backups for as long as you need
>them, even if just a day or so.
>However, if you are truncating the log, then what is the point of FULL
>recovery mode? This provides no more transactional integrity or rollback
>ability than SIMPLE mode. If you are always truncating, then go ahead and
>set the database to SIMPLE mode, since that is essentially what you have.
>RLF
>> We are running SQL 2005 SP1.
>[quoted text clipped - 12 lines]
>> Any idea how to reclaim some space?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1|||> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
Only if there are no active transactions in the last virtual log file. You
can check for open transactions using DBCC OPENTRAN.
A|||Both of these commands do the same thing to your log. The both free up space
from inactive virtual log files so that it can be reused.
Neither will change the physical size of the log file.
The difference between these commands is that the second one will save the
transactions that it is clearing out, but the first one won't.
The only way to reclaim physical space is to physically shrink the log file.
Note that physically shrinking the log file is very different from physical
shrinking a datafile. Most of the warnings in Tibor's article have to do
with shrinking data files.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:74a00a55f6cf9@.uwe...
> Thanks Russell.
> One thing is for sure, I do not want to shrink the file.
> In attempting to understand your comment as it relates to mine:
> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
> 2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
> Russell Fields wrote:
>>cbrichards,
>>This article and the articles to which it links can be a help to you in
>>shrinking the log file and in thinking about what you want to do:
>>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>One way to free up space in the log, of course, is to do BACKUP LOG
>>(without
>>TRUNCATE) periodically and then save the backups for as long as you need
>>them, even if just a day or so.
>>However, if you are truncating the log, then what is the point of FULL
>>recovery mode? This provides no more transactional integrity or rollback
>>ability than SIMPLE mode. If you are always truncating, then go ahead and
>>set the database to SIMPLE mode, since that is essentially what you have.
>>RLF
>> We are running SQL 2005 SP1.
>>[quoted text clipped - 12 lines]
>> Any idea how to reclaim some space?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1
>|||Now I am really confused. In repeating my earlier thread:
"In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?"
So you are saying that in both 1 and 2 that space is freed "Only if there are
no active transactions in the last virtual log file."?
Aaron Bertrand [SQL Server MVP] wrote:
>> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
>> TLog?
>Only if there are no active transactions in the last virtual log file. You
>can check for open transactions using DBCC OPENTRAN.
>A
--
Message posted via http://www.sqlmonster.com|||Thanks Kalen.
I think we are getting closer to a final answer on my inquiry.
Since
1. we cannot set our system to SIMPLE recovery for a couple more weeks, and
2. we do not care to keep TLOG backup files, and
3. we want to keep our TLOG file at 10gb and reuse its space, then
we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY to
reuse the 10gb space, correct?
Kalen Delaney wrote:
>Both of these commands do the same thing to your log. The both free up space
>from inactive virtual log files so that it can be reused.
>Neither will change the physical size of the log file.
>The difference between these commands is that the second one will save the
>transactions that it is clearing out, but the first one won't.
>The only way to reclaim physical space is to physically shrink the log file.
>Note that physically shrinking the log file is very different from physical
>shrinking a datafile. Most of the warnings in Tibor's article have to do
>with shrinking data files.
>> Thanks Russell.
>[quoted text clipped - 28 lines]
>> Any idea how to reclaim some space?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1|||Correct. - RLF
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:74a08b9896a7f@.uwe...
> Thanks Kalen.
> I think we are getting closer to a final answer on my inquiry.
> Since
> 1. we cannot set our system to SIMPLE recovery for a couple more weeks,
> and
> 2. we do not care to keep TLOG backup files, and
> 3. we want to keep our TLOG file at 10gb and reuse its space, then
> we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY
> to
> reuse the 10gb space, correct?
> Kalen Delaney wrote:
>>Both of these commands do the same thing to your log. The both free up
>>space
>>from inactive virtual log files so that it can be reused.
>>Neither will change the physical size of the log file.
>>The difference between these commands is that the second one will save the
>>transactions that it is clearing out, but the first one won't.
>>The only way to reclaim physical space is to physically shrink the log
>>file.
>>Note that physically shrinking the log file is very different from
>>physical
>>shrinking a datafile. Most of the warnings in Tibor's article have to do
>>with shrinking data files.
>> Thanks Russell.
>>[quoted text clipped - 28 lines]
>> Any idea how to reclaim some space?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1
>|||In article <749f8542dc410@.uwe>, u3288@.uwe says...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
>
as others have said it is a 2-step process
(1) backup the log w/ Truncate_only to clear out as many of the VLF's as
are not in use or hung up on some long-rinning transaction
(2) DBCC Shrinkfile being sure to point to the correct file number --
probably 2.
I just did this to reclaim space from a 6G logfile that had never been
backed up and allowed to just grow. Of the 500M I sized it at -- about
25% of the current DB size -- only 14.3M was actually in use.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu
Free/Used space of a transaction log file
Hello,
I am looking for a sp or view which shows the size of used/free space of a transaction log file
in SQL Server (2k), like the view 'Taskpad' in Enterprise Manager does.
How can I get this logfile information?
Thank You
Joachim
1. This example summarizes space used in the current database and uses the
optional parameter @.updateusage.
USE pubs
sp_spaceused @.updateusage = 'TRUE'
2. sp_tempdbspace
This stored procedure can be used to get the total size and the space used
by the tempdb database. It is used without parameters.
3. Further you can use the following queries :-
Select * from master..sysaltfiles
Select * from sysfiles
Let me know if it served your purpose.
"Joachim Hofmann" wrote:
> Hello,
> I am looking for a sp or view which shows the size of used/free space of a transaction log file
> in SQL Server (2k), like the view 'Taskpad' in Enterprise Manager does.
> How can I get this logfile information?
> Thank You
> Joachim
>
|||Joachim Hofmann wrote:
> Hello,
> I am looking for a sp or view which shows the size of used/free space
> of a transaction log file in SQL Server (2k), like the view 'Taskpad'
> in Enterprise Manager does.
> How can I get this logfile information?
> Thank You
> Joachim
I used profiler to trace what EM does:
DBCC sqlperf(logspace)
DBCC showfilestats
--
remove RmEaMkOnViEoTHvIoS from my email
svi komentari i razmisljanja su moja kao pojedinca i nikako se ne smiju
povezivati sa tvrtkom u kojoj radim
|||Yes, DBCC SQLPERF(LOGSPACE) shows what I wanted to know.
The other sp's / tables only show the database files, objects like tables or only the absolute
sizes.
Thank You
Joachim
I am looking for a sp or view which shows the size of used/free space of a transaction log file
in SQL Server (2k), like the view 'Taskpad' in Enterprise Manager does.
How can I get this logfile information?
Thank You
Joachim
1. This example summarizes space used in the current database and uses the
optional parameter @.updateusage.
USE pubs
sp_spaceused @.updateusage = 'TRUE'
2. sp_tempdbspace
This stored procedure can be used to get the total size and the space used
by the tempdb database. It is used without parameters.
3. Further you can use the following queries :-
Select * from master..sysaltfiles
Select * from sysfiles
Let me know if it served your purpose.
"Joachim Hofmann" wrote:
> Hello,
> I am looking for a sp or view which shows the size of used/free space of a transaction log file
> in SQL Server (2k), like the view 'Taskpad' in Enterprise Manager does.
> How can I get this logfile information?
> Thank You
> Joachim
>
|||Joachim Hofmann wrote:
> Hello,
> I am looking for a sp or view which shows the size of used/free space
> of a transaction log file in SQL Server (2k), like the view 'Taskpad'
> in Enterprise Manager does.
> How can I get this logfile information?
> Thank You
> Joachim
I used profiler to trace what EM does:
DBCC sqlperf(logspace)
DBCC showfilestats
--
remove RmEaMkOnViEoTHvIoS from my email
svi komentari i razmisljanja su moja kao pojedinca i nikako se ne smiju
povezivati sa tvrtkom u kojoj radim
|||Yes, DBCC SQLPERF(LOGSPACE) shows what I wanted to know.
The other sp's / tables only show the database files, objects like tables or only the absolute
sizes.
Thank You
Joachim
Free/Used space of a transaction log file
Hello,
I am looking for a sp or view which shows the size of used/free space of a t
ransaction log file
in SQL Server (2k), like the view 'Taskpad' in Enterprise Manager does.
How can I get this logfile information?
Thank You
Joachim1. This example summarizes space used in the current database and uses the
optional parameter @.updateusage.
USE pubs
sp_spaceused @.updateusage = 'TRUE'
2. sp_tempdbspace
This stored procedure can be used to get the total size and the space used
by the tempdb database. It is used without parameters.
3. Further you can use the following queries :-
Select * from master..sysaltfiles
Select * from sysfiles
Let me know if it served your purpose.
"Joachim Hofmann" wrote:
> Hello,
> I am looking for a sp or view which shows the size of used/free space of a
transaction log file
> in SQL Server (2k), like the view 'Taskpad' in Enterprise Manager does.
> How can I get this logfile information?
> Thank You
> Joachim
>|||Joachim Hofmann wrote:
> Hello,
> I am looking for a sp or view which shows the size of used/free space
> of a transaction log file in SQL Server (2k), like the view 'Taskpad'
> in Enterprise Manager does.
> How can I get this logfile information?
> Thank You
> Joachim
I used profiler to trace what EM does:
DBCC sqlperf(logspace)
DBCC showfilestats
--
remove RmEaMkOnViEoTHvIoS from my email
svi komentari i razmisljanja su moja kao pojedinca i nikako se ne smiju
povezivati sa tvrtkom u kojoj radim|||Yes, DBCC SQLPERF(LOGSPACE) shows what I wanted to know.
The other sp's / tables only show the database files, objects like tables or
only the absolute
sizes.
Thank You
Joachim
I am looking for a sp or view which shows the size of used/free space of a t
ransaction log file
in SQL Server (2k), like the view 'Taskpad' in Enterprise Manager does.
How can I get this logfile information?
Thank You
Joachim1. This example summarizes space used in the current database and uses the
optional parameter @.updateusage.
USE pubs
sp_spaceused @.updateusage = 'TRUE'
2. sp_tempdbspace
This stored procedure can be used to get the total size and the space used
by the tempdb database. It is used without parameters.
3. Further you can use the following queries :-
Select * from master..sysaltfiles
Select * from sysfiles
Let me know if it served your purpose.
"Joachim Hofmann" wrote:
> Hello,
> I am looking for a sp or view which shows the size of used/free space of a
transaction log file
> in SQL Server (2k), like the view 'Taskpad' in Enterprise Manager does.
> How can I get this logfile information?
> Thank You
> Joachim
>|||Joachim Hofmann wrote:
> Hello,
> I am looking for a sp or view which shows the size of used/free space
> of a transaction log file in SQL Server (2k), like the view 'Taskpad'
> in Enterprise Manager does.
> How can I get this logfile information?
> Thank You
> Joachim
I used profiler to trace what EM does:
DBCC sqlperf(logspace)
DBCC showfilestats
--
remove RmEaMkOnViEoTHvIoS from my email
svi komentari i razmisljanja su moja kao pojedinca i nikako se ne smiju
povezivati sa tvrtkom u kojoj radim|||Yes, DBCC SQLPERF(LOGSPACE) shows what I wanted to know.
The other sp's / tables only show the database files, objects like tables or
only the absolute
sizes.
Thank You
Joachim
Free/Used space of a transaction log file
Hello,
I am looking for a sp or view which shows the size of used/free space of a transaction log file
in SQL Server (2k), like the view 'Taskpad' in Enterprise Manager does.
How can I get this logfile information?
Thank You
Joachim1. This example summarizes space used in the current database and uses the
optional parameter @.updateusage.
USE pubs
sp_spaceused @.updateusage = 'TRUE'
2. sp_tempdbspace
This stored procedure can be used to get the total size and the space used
by the tempdb database. It is used without parameters.
3. Further you can use the following queries :-
Select * from master..sysaltfiles
Select * from sysfiles
Let me know if it served your purpose.
"Joachim Hofmann" wrote:
> Hello,
> I am looking for a sp or view which shows the size of used/free space of a transaction log file
> in SQL Server (2k), like the view 'Taskpad' in Enterprise Manager does.
> How can I get this logfile information?
> Thank You
> Joachim
>|||Joachim Hofmann wrote:
> Hello,
> I am looking for a sp or view which shows the size of used/free space
> of a transaction log file in SQL Server (2k), like the view 'Taskpad'
> in Enterprise Manager does.
> How can I get this logfile information?
> Thank You
> Joachim
I used profiler to trace what EM does:
DBCC sqlperf(logspace)
DBCC showfilestats
--
remove RmEaMkOnViEoTHvIoS from my email
svi komentari i razmisljanja su moja kao pojedinca i nikako se ne smiju
povezivati sa tvrtkom u kojoj radim|||Yes, DBCC SQLPERF(LOGSPACE) shows what I wanted to know.
The other sp's / tables only show the database files, objects like tables or only the absolute
sizes.
Thank You
Joachim
I am looking for a sp or view which shows the size of used/free space of a transaction log file
in SQL Server (2k), like the view 'Taskpad' in Enterprise Manager does.
How can I get this logfile information?
Thank You
Joachim1. This example summarizes space used in the current database and uses the
optional parameter @.updateusage.
USE pubs
sp_spaceused @.updateusage = 'TRUE'
2. sp_tempdbspace
This stored procedure can be used to get the total size and the space used
by the tempdb database. It is used without parameters.
3. Further you can use the following queries :-
Select * from master..sysaltfiles
Select * from sysfiles
Let me know if it served your purpose.
"Joachim Hofmann" wrote:
> Hello,
> I am looking for a sp or view which shows the size of used/free space of a transaction log file
> in SQL Server (2k), like the view 'Taskpad' in Enterprise Manager does.
> How can I get this logfile information?
> Thank You
> Joachim
>|||Joachim Hofmann wrote:
> Hello,
> I am looking for a sp or view which shows the size of used/free space
> of a transaction log file in SQL Server (2k), like the view 'Taskpad'
> in Enterprise Manager does.
> How can I get this logfile information?
> Thank You
> Joachim
I used profiler to trace what EM does:
DBCC sqlperf(logspace)
DBCC showfilestats
--
remove RmEaMkOnViEoTHvIoS from my email
svi komentari i razmisljanja su moja kao pojedinca i nikako se ne smiju
povezivati sa tvrtkom u kojoj radim|||Yes, DBCC SQLPERF(LOGSPACE) shows what I wanted to know.
The other sp's / tables only show the database files, objects like tables or only the absolute
sizes.
Thank You
Joachim
Sunday, February 26, 2012
Free up the transaction log
Hi ,
When full db is backed up (using maintenance plan) , does transaction
log get shrunk (inactive transactions are dropped from log) . Also does this
space get released to OS .
Can you pl. let me know answer for same question , when log is backed up
explicity (using backup log without using truncate_only option ) .
What could be the reasons , why my log is not getting shrunk after DB and
log backup . What are the other options available apart from using
truncate_only option and dbcc shrinkfile .
Thanks,
Prabhu
Shrinking is NOT part of a backup, log or db. You must use either DBCC
SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
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
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
> Hi ,
> When full db is backed up (using maintenance plan) , does transaction
> log get shrunk (inactive transactions are dropped from log) . Also does
> this
> space get released to OS .
> Can you pl. let me know answer for same question , when log is backed up
> explicity (using backup log without using truncate_only option ) .
> What could be the reasons , why my log is not getting shrunk after DB and
> log backup . What are the other options available apart from using
> truncate_only option and dbcc shrinkfile .
> Thanks,
> Prabhu
|||Thanks for your reply Kelly .
I understand shrinking the physical file(s) is not part of backup .
Truncation does not reduce the size of a physical log file, it reduces the
size of the logical log file
But I have 2 weird situations .
1. My tempdb log just grows out of control . Only way we could reuse the
space is after explicitly using backup log with truncate option and dbcc
shrinkfile .
But , Log truncation should occur ,every time a checkpoint is processed,
provided the database is using the simple recovery model (tempdb is in
recovery model).
2. Other user DBs log files also grow , they are in full or Bulk_Logged
recovery model . We have daily fulldb maintenance plan to take only full DB
backup . Does it truncate the log ? But looks like unused space in log files
are not being reused and log files are just extending .
I have also used backup log command (without truncate_only) but still unused
space is not reused but log file grows .( log truncation should occur after
BACKUP LOG statement , right . )
Thanks,
Prabhu
"Andrew J. Kelly" wrote:
> Shrinking is NOT part of a backup, log or db. You must use either DBCC
> SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
> 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
>
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
>
>
|||Do you have the latest service pack? I have heard of other postings in
which the tempdb log grows but I can't find any KB on it. I think it was
fixed with a service pack. This KB might be helpful:
http://support.microsoft.com/Default.aspx?id=110139
As for # 2 if you are in Full or BulkLogged recovery mode you must do
regular log backups otherwise the log will continue to grow. A full backup
does not truncate the log only a proper Log backup will do that as long as
there are no open transactions.
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply Kelly .
> I understand shrinking the physical file(s) is not part of backup .
> Truncation does not reduce the size of a physical log file, it reduces the
> size of the logical log file
> But I have 2 weird situations .
> 1. My tempdb log just grows out of control . Only way we could reuse the
> space is after explicitly using backup log with truncate option and dbcc
> shrinkfile .
> But , Log truncation should occur ,every time a checkpoint is processed,
> provided the database is using the simple recovery model (tempdb is in
> recovery model).
> 2. Other user DBs log files also grow , they are in full or Bulk_Logged
> recovery model . We have daily fulldb maintenance plan to take only full
> DB
> backup . Does it truncate the log ? But looks like unused space in log
> files
> are not being reused and log files are just extending .
> I have also used backup log command (without truncate_only) but still
> unused
> space is not reused but log file grows .( log truncation should occur
> after
> BACKUP LOG statement , right . )
> Thanks,
> Prabhu
> "Andrew J. Kelly" wrote:
|||Hi Andrew ,
1. I have SP4 ( 8.00.2040 ) .
2. If DB is in simple recovery mode , log is getting truncated (but space
not released to OS , as expected) but for other recovery models backup log
command sometimes truncates log and sometimes it won't .
Thanks,
Prabhu
"Andrew J. Kelly" wrote:
> Do you have the latest service pack? I have heard of other postings in
> which the tempdb log grows but I can't find any KB on it. I think it was
> fixed with a service pack. This KB might be helpful:
> http://support.microsoft.com/Default.aspx?id=110139
> As for # 2 if you are in Full or BulkLogged recovery mode you must do
> regular log backups otherwise the log will continue to grow. A full backup
> does not truncate the log only a proper Log backup will do that as long as
> there are no open transactions.
> --
> Andrew J. Kelly SQL MVP
>
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...
>
>
|||If there are any long running open transactions it can not truncate that
portion of the log.
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:14032F27-857D-4F34-9864-00A4D9514E67@.microsoft.com...[vbcol=seagreen]
> Hi Andrew ,
> 1. I have SP4 ( 8.00.2040 ) .
> 2. If DB is in simple recovery mode , log is getting truncated (but space
> not released to OS , as expected) but for other recovery models backup log
> command sometimes truncates log and sometimes it won't .
> Thanks,
> Prabhu
>
> "Andrew J. Kelly" wrote:
|||Andrew ,
I have SP4 but still have issues with tempdb log (issue #1) , as I described
below . Can you pl. suggest me any solution . I also read from MS support
site ,using dbcc shrinkdatabase (and/or dbcc shrinkfile) could currept
tempdb . Right .
Pl. let me know .
--Prabhu
"Andrew J. Kelly" wrote:
> If there are any long running open transactions it can not truncate that
> portion of the log.
> --
> Andrew J. Kelly SQL MVP
>
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:14032F27-857D-4F34-9864-00A4D9514E67@.microsoft.com...
>
>
|||As I stated if you have long running open transactions in the db the log
file may not be able to be truncated. Try running DBCC OPENTRAN() in the
affected db's.
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:B04001DD-4233-4E2B-B62C-F4E6C960D77A@.microsoft.com...[vbcol=seagreen]
> Andrew ,
> I have SP4 but still have issues with tempdb log (issue #1) , as I
> described
> below . Can you pl. suggest me any solution . I also read from MS support
> site ,using dbcc shrinkdatabase (and/or dbcc shrinkfile) could currept
> tempdb . Right .
> Pl. let me know .
> --Prabhu
> "Andrew J. Kelly" wrote:
When full db is backed up (using maintenance plan) , does transaction
log get shrunk (inactive transactions are dropped from log) . Also does this
space get released to OS .
Can you pl. let me know answer for same question , when log is backed up
explicity (using backup log without using truncate_only option ) .
What could be the reasons , why my log is not getting shrunk after DB and
log backup . What are the other options available apart from using
truncate_only option and dbcc shrinkfile .
Thanks,
Prabhu
Shrinking is NOT part of a backup, log or db. You must use either DBCC
SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
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
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
> Hi ,
> When full db is backed up (using maintenance plan) , does transaction
> log get shrunk (inactive transactions are dropped from log) . Also does
> this
> space get released to OS .
> Can you pl. let me know answer for same question , when log is backed up
> explicity (using backup log without using truncate_only option ) .
> What could be the reasons , why my log is not getting shrunk after DB and
> log backup . What are the other options available apart from using
> truncate_only option and dbcc shrinkfile .
> Thanks,
> Prabhu
|||Thanks for your reply Kelly .
I understand shrinking the physical file(s) is not part of backup .
Truncation does not reduce the size of a physical log file, it reduces the
size of the logical log file
But I have 2 weird situations .
1. My tempdb log just grows out of control . Only way we could reuse the
space is after explicitly using backup log with truncate option and dbcc
shrinkfile .
But , Log truncation should occur ,every time a checkpoint is processed,
provided the database is using the simple recovery model (tempdb is in
recovery model).
2. Other user DBs log files also grow , they are in full or Bulk_Logged
recovery model . We have daily fulldb maintenance plan to take only full DB
backup . Does it truncate the log ? But looks like unused space in log files
are not being reused and log files are just extending .
I have also used backup log command (without truncate_only) but still unused
space is not reused but log file grows .( log truncation should occur after
BACKUP LOG statement , right . )
Thanks,
Prabhu
"Andrew J. Kelly" wrote:
> Shrinking is NOT part of a backup, log or db. You must use either DBCC
> SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
> 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
>
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
>
>
|||Do you have the latest service pack? I have heard of other postings in
which the tempdb log grows but I can't find any KB on it. I think it was
fixed with a service pack. This KB might be helpful:
http://support.microsoft.com/Default.aspx?id=110139
As for # 2 if you are in Full or BulkLogged recovery mode you must do
regular log backups otherwise the log will continue to grow. A full backup
does not truncate the log only a proper Log backup will do that as long as
there are no open transactions.
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply Kelly .
> I understand shrinking the physical file(s) is not part of backup .
> Truncation does not reduce the size of a physical log file, it reduces the
> size of the logical log file
> But I have 2 weird situations .
> 1. My tempdb log just grows out of control . Only way we could reuse the
> space is after explicitly using backup log with truncate option and dbcc
> shrinkfile .
> But , Log truncation should occur ,every time a checkpoint is processed,
> provided the database is using the simple recovery model (tempdb is in
> recovery model).
> 2. Other user DBs log files also grow , they are in full or Bulk_Logged
> recovery model . We have daily fulldb maintenance plan to take only full
> DB
> backup . Does it truncate the log ? But looks like unused space in log
> files
> are not being reused and log files are just extending .
> I have also used backup log command (without truncate_only) but still
> unused
> space is not reused but log file grows .( log truncation should occur
> after
> BACKUP LOG statement , right . )
> Thanks,
> Prabhu
> "Andrew J. Kelly" wrote:
|||Hi Andrew ,
1. I have SP4 ( 8.00.2040 ) .
2. If DB is in simple recovery mode , log is getting truncated (but space
not released to OS , as expected) but for other recovery models backup log
command sometimes truncates log and sometimes it won't .
Thanks,
Prabhu
"Andrew J. Kelly" wrote:
> Do you have the latest service pack? I have heard of other postings in
> which the tempdb log grows but I can't find any KB on it. I think it was
> fixed with a service pack. This KB might be helpful:
> http://support.microsoft.com/Default.aspx?id=110139
> As for # 2 if you are in Full or BulkLogged recovery mode you must do
> regular log backups otherwise the log will continue to grow. A full backup
> does not truncate the log only a proper Log backup will do that as long as
> there are no open transactions.
> --
> Andrew J. Kelly SQL MVP
>
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...
>
>
|||If there are any long running open transactions it can not truncate that
portion of the log.
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:14032F27-857D-4F34-9864-00A4D9514E67@.microsoft.com...[vbcol=seagreen]
> Hi Andrew ,
> 1. I have SP4 ( 8.00.2040 ) .
> 2. If DB is in simple recovery mode , log is getting truncated (but space
> not released to OS , as expected) but for other recovery models backup log
> command sometimes truncates log and sometimes it won't .
> Thanks,
> Prabhu
>
> "Andrew J. Kelly" wrote:
|||Andrew ,
I have SP4 but still have issues with tempdb log (issue #1) , as I described
below . Can you pl. suggest me any solution . I also read from MS support
site ,using dbcc shrinkdatabase (and/or dbcc shrinkfile) could currept
tempdb . Right .
Pl. let me know .
--Prabhu
"Andrew J. Kelly" wrote:
> If there are any long running open transactions it can not truncate that
> portion of the log.
> --
> Andrew J. Kelly SQL MVP
>
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:14032F27-857D-4F34-9864-00A4D9514E67@.microsoft.com...
>
>
|||As I stated if you have long running open transactions in the db the log
file may not be able to be truncated. Try running DBCC OPENTRAN() in the
affected db's.
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:B04001DD-4233-4E2B-B62C-F4E6C960D77A@.microsoft.com...[vbcol=seagreen]
> Andrew ,
> I have SP4 but still have issues with tempdb log (issue #1) , as I
> described
> below . Can you pl. suggest me any solution . I also read from MS support
> site ,using dbcc shrinkdatabase (and/or dbcc shrinkfile) could currept
> tempdb . Right .
> Pl. let me know .
> --Prabhu
> "Andrew J. Kelly" wrote:
Free up the transaction log
Hi ,
When full db is backed up (using maintenance plan) , does transaction
log get shrunk (inactive transactions are dropped from log) . Also does this
space get released to OS .
Can you pl. let me know answer for same question , when log is backed up
explicity (using backup log without using truncate_only option ) .
What could be the reasons , why my log is not getting shrunk after DB and
log backup . What are the other options available apart from using
truncate_only option and dbcc shrinkfile .
Thanks,
PrabhuShrinking is NOT part of a backup, log or db. You must use either DBCC
SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
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
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
> Hi ,
> When full db is backed up (using maintenance plan) , does transaction
> log get shrunk (inactive transactions are dropped from log) . Also does
> this
> space get released to OS .
> Can you pl. let me know answer for same question , when log is backed up
> explicity (using backup log without using truncate_only option ) .
> What could be the reasons , why my log is not getting shrunk after DB and
> log backup . What are the other options available apart from using
> truncate_only option and dbcc shrinkfile .
> Thanks,
> Prabhu|||Thanks for your reply Kelly .
I understand shrinking the physical file(s) is not part of backup .
Truncation does not reduce the size of a physical log file, it reduces the
size of the logical log file
But I have 2 weird situations .
1. My tempdb log just grows out of control . Only way we could reuse the
space is after explicitly using backup log with truncate option and dbcc
shrinkfile .
But , Log truncation should occur ,every time a checkpoint is processed,
provided the database is using the simple recovery model (tempdb is in
recovery model).
2. Other user DBs log files also grow , they are in full or Bulk_Logged
recovery model . We have daily fulldb maintenance plan to take only full DB
backup . Does it truncate the log ? But looks like unused space in log files
are not being reused and log files are just extending .
I have also used backup log command (without truncate_only) but still unused
space is not reused but log file grows .( log truncation should occur after
BACKUP LOG statement , right . )
Thanks,
Prabhu
"Andrew J. Kelly" wrote:
> Shrinking is NOT part of a backup, log or db. You must use either DBCC
> SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
> 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
>
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
> > Hi ,
> > When full db is backed up (using maintenance plan) , does transaction
> > log get shrunk (inactive transactions are dropped from log) . Also does
> > this
> > space get released to OS .
> > Can you pl. let me know answer for same question , when log is backed up
> > explicity (using backup log without using truncate_only option ) .
> > What could be the reasons , why my log is not getting shrunk after DB and
> > log backup . What are the other options available apart from using
> > truncate_only option and dbcc shrinkfile .
> >
> > Thanks,
> > Prabhu
>
>|||Do you have the latest service pack? I have heard of other postings in
which the tempdb log grows but I can't find any KB on it. I think it was
fixed with a service pack. This KB might be helpful:
http://support.microsoft.com/Default.aspx?id=110139
As for # 2 if you are in Full or BulkLogged recovery mode you must do
regular log backups otherwise the log will continue to grow. A full backup
does not truncate the log only a proper Log backup will do that as long as
there are no open transactions.
--
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...
> Thanks for your reply Kelly .
> I understand shrinking the physical file(s) is not part of backup .
> Truncation does not reduce the size of a physical log file, it reduces the
> size of the logical log file
> But I have 2 weird situations .
> 1. My tempdb log just grows out of control . Only way we could reuse the
> space is after explicitly using backup log with truncate option and dbcc
> shrinkfile .
> But , Log truncation should occur ,every time a checkpoint is processed,
> provided the database is using the simple recovery model (tempdb is in
> recovery model).
> 2. Other user DBs log files also grow , they are in full or Bulk_Logged
> recovery model . We have daily fulldb maintenance plan to take only full
> DB
> backup . Does it truncate the log ? But looks like unused space in log
> files
> are not being reused and log files are just extending .
> I have also used backup log command (without truncate_only) but still
> unused
> space is not reused but log file grows .( log truncation should occur
> after
> BACKUP LOG statement , right . )
> Thanks,
> Prabhu
> "Andrew J. Kelly" wrote:
>> Shrinking is NOT part of a backup, log or db. You must use either DBCC
>> SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
>> 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
>>
>> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
>> news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
>> > Hi ,
>> > When full db is backed up (using maintenance plan) , does
>> > transaction
>> > log get shrunk (inactive transactions are dropped from log) . Also
>> > does
>> > this
>> > space get released to OS .
>> > Can you pl. let me know answer for same question , when log is backed
>> > up
>> > explicity (using backup log without using truncate_only option ) .
>> > What could be the reasons , why my log is not getting shrunk after DB
>> > and
>> > log backup . What are the other options available apart from using
>> > truncate_only option and dbcc shrinkfile .
>> >
>> > Thanks,
>> > Prabhu
>>|||Hi Andrew ,
1. I have SP4 ( 8.00.2040 ) .
2. If DB is in simple recovery mode , log is getting truncated (but space
not released to OS , as expected) but for other recovery models backup log
command sometimes truncates log and sometimes it won't .
Thanks,
Prabhu
"Andrew J. Kelly" wrote:
> Do you have the latest service pack? I have heard of other postings in
> which the tempdb log grows but I can't find any KB on it. I think it was
> fixed with a service pack. This KB might be helpful:
> http://support.microsoft.com/Default.aspx?id=110139
> As for # 2 if you are in Full or BulkLogged recovery mode you must do
> regular log backups otherwise the log will continue to grow. A full backup
> does not truncate the log only a proper Log backup will do that as long as
> there are no open transactions.
> --
> Andrew J. Kelly SQL MVP
>
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...
> > Thanks for your reply Kelly .
> >
> > I understand shrinking the physical file(s) is not part of backup .
> > Truncation does not reduce the size of a physical log file, it reduces the
> > size of the logical log file
> >
> > But I have 2 weird situations .
> >
> > 1. My tempdb log just grows out of control . Only way we could reuse the
> > space is after explicitly using backup log with truncate option and dbcc
> > shrinkfile .
> > But , Log truncation should occur ,every time a checkpoint is processed,
> > provided the database is using the simple recovery model (tempdb is in
> > recovery model).
> > 2. Other user DBs log files also grow , they are in full or Bulk_Logged
> > recovery model . We have daily fulldb maintenance plan to take only full
> > DB
> > backup . Does it truncate the log ? But looks like unused space in log
> > files
> > are not being reused and log files are just extending .
> > I have also used backup log command (without truncate_only) but still
> > unused
> > space is not reused but log file grows .( log truncation should occur
> > after
> > BACKUP LOG statement , right . )
> >
> > Thanks,
> > Prabhu
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Shrinking is NOT part of a backup, log or db. You must use either DBCC
> >> SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
> >>
> >> 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
> >>
> >>
> >> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> >> news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
> >> > Hi ,
> >> > When full db is backed up (using maintenance plan) , does
> >> > transaction
> >> > log get shrunk (inactive transactions are dropped from log) . Also
> >> > does
> >> > this
> >> > space get released to OS .
> >> > Can you pl. let me know answer for same question , when log is backed
> >> > up
> >> > explicity (using backup log without using truncate_only option ) .
> >> > What could be the reasons , why my log is not getting shrunk after DB
> >> > and
> >> > log backup . What are the other options available apart from using
> >> > truncate_only option and dbcc shrinkfile .
> >> >
> >> > Thanks,
> >> > Prabhu
> >>
> >>
> >>
>
>|||If there are any long running open transactions it can not truncate that
portion of the log.
--
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:14032F27-857D-4F34-9864-00A4D9514E67@.microsoft.com...
> Hi Andrew ,
> 1. I have SP4 ( 8.00.2040 ) .
> 2. If DB is in simple recovery mode , log is getting truncated (but space
> not released to OS , as expected) but for other recovery models backup log
> command sometimes truncates log and sometimes it won't .
> Thanks,
> Prabhu
>
> "Andrew J. Kelly" wrote:
>> Do you have the latest service pack? I have heard of other postings in
>> which the tempdb log grows but I can't find any KB on it. I think it was
>> fixed with a service pack. This KB might be helpful:
>> http://support.microsoft.com/Default.aspx?id=110139
>> As for # 2 if you are in Full or BulkLogged recovery mode you must do
>> regular log backups otherwise the log will continue to grow. A full
>> backup
>> does not truncate the log only a proper Log backup will do that as long
>> as
>> there are no open transactions.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
>> news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...
>> > Thanks for your reply Kelly .
>> >
>> > I understand shrinking the physical file(s) is not part of backup .
>> > Truncation does not reduce the size of a physical log file, it reduces
>> > the
>> > size of the logical log file
>> >
>> > But I have 2 weird situations .
>> >
>> > 1. My tempdb log just grows out of control . Only way we could reuse
>> > the
>> > space is after explicitly using backup log with truncate option and
>> > dbcc
>> > shrinkfile .
>> > But , Log truncation should occur ,every time a checkpoint is
>> > processed,
>> > provided the database is using the simple recovery model (tempdb is in
>> > recovery model).
>> > 2. Other user DBs log files also grow , they are in full or
>> > Bulk_Logged
>> > recovery model . We have daily fulldb maintenance plan to take only
>> > full
>> > DB
>> > backup . Does it truncate the log ? But looks like unused space in log
>> > files
>> > are not being reused and log files are just extending .
>> > I have also used backup log command (without truncate_only) but still
>> > unused
>> > space is not reused but log file grows .( log truncation should occur
>> > after
>> > BACKUP LOG statement , right . )
>> >
>> > Thanks,
>> > Prabhu
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Shrinking is NOT part of a backup, log or db. You must use either
>> >> DBCC
>> >> SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
>> >>
>> >> 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
>> >>
>> >>
>> >> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
>> >> news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
>> >> > Hi ,
>> >> > When full db is backed up (using maintenance plan) , does
>> >> > transaction
>> >> > log get shrunk (inactive transactions are dropped from log) . Also
>> >> > does
>> >> > this
>> >> > space get released to OS .
>> >> > Can you pl. let me know answer for same question , when log is
>> >> > backed
>> >> > up
>> >> > explicity (using backup log without using truncate_only option ) .
>> >> > What could be the reasons , why my log is not getting shrunk after
>> >> > DB
>> >> > and
>> >> > log backup . What are the other options available apart from using
>> >> > truncate_only option and dbcc shrinkfile .
>> >> >
>> >> > Thanks,
>> >> > Prabhu
>> >>
>> >>
>> >>
>>|||Andrew ,
I have SP4 but still have issues with tempdb log (issue #1) , as I described
below . Can you pl. suggest me any solution . I also read from MS support
site ,using dbcc shrinkdatabase (and/or dbcc shrinkfile) could currept
tempdb . Right .
Pl. let me know .
--Prabhu
"Andrew J. Kelly" wrote:
> If there are any long running open transactions it can not truncate that
> portion of the log.
> --
> Andrew J. Kelly SQL MVP
>
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:14032F27-857D-4F34-9864-00A4D9514E67@.microsoft.com...
> > Hi Andrew ,
> >
> > 1. I have SP4 ( 8.00.2040 ) .
> > 2. If DB is in simple recovery mode , log is getting truncated (but space
> > not released to OS , as expected) but for other recovery models backup log
> > command sometimes truncates log and sometimes it won't .
> >
> > Thanks,
> > Prabhu
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Do you have the latest service pack? I have heard of other postings in
> >> which the tempdb log grows but I can't find any KB on it. I think it was
> >> fixed with a service pack. This KB might be helpful:
> >>
> >> http://support.microsoft.com/Default.aspx?id=110139
> >>
> >> As for # 2 if you are in Full or BulkLogged recovery mode you must do
> >> regular log backups otherwise the log will continue to grow. A full
> >> backup
> >> does not truncate the log only a proper Log backup will do that as long
> >> as
> >> there are no open transactions.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> >> news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...
> >> > Thanks for your reply Kelly .
> >> >
> >> > I understand shrinking the physical file(s) is not part of backup .
> >> > Truncation does not reduce the size of a physical log file, it reduces
> >> > the
> >> > size of the logical log file
> >> >
> >> > But I have 2 weird situations .
> >> >
> >> > 1. My tempdb log just grows out of control . Only way we could reuse
> >> > the
> >> > space is after explicitly using backup log with truncate option and
> >> > dbcc
> >> > shrinkfile .
> >> > But , Log truncation should occur ,every time a checkpoint is
> >> > processed,
> >> > provided the database is using the simple recovery model (tempdb is in
> >> > recovery model).
> >> > 2. Other user DBs log files also grow , they are in full or
> >> > Bulk_Logged
> >> > recovery model . We have daily fulldb maintenance plan to take only
> >> > full
> >> > DB
> >> > backup . Does it truncate the log ? But looks like unused space in log
> >> > files
> >> > are not being reused and log files are just extending .
> >> > I have also used backup log command (without truncate_only) but still
> >> > unused
> >> > space is not reused but log file grows .( log truncation should occur
> >> > after
> >> > BACKUP LOG statement , right . )
> >> >
> >> > Thanks,
> >> > Prabhu
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> Shrinking is NOT part of a backup, log or db. You must use either
> >> >> DBCC
> >> >> SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
> >> >>
> >> >> 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
> >> >>
> >> >>
> >> >> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> >> >> news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
> >> >> > Hi ,
> >> >> > When full db is backed up (using maintenance plan) , does
> >> >> > transaction
> >> >> > log get shrunk (inactive transactions are dropped from log) . Also
> >> >> > does
> >> >> > this
> >> >> > space get released to OS .
> >> >> > Can you pl. let me know answer for same question , when log is
> >> >> > backed
> >> >> > up
> >> >> > explicity (using backup log without using truncate_only option ) .
> >> >> > What could be the reasons , why my log is not getting shrunk after
> >> >> > DB
> >> >> > and
> >> >> > log backup . What are the other options available apart from using
> >> >> > truncate_only option and dbcc shrinkfile .
> >> >> >
> >> >> > Thanks,
> >> >> > Prabhu
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||As I stated if you have long running open transactions in the db the log
file may not be able to be truncated. Try running DBCC OPENTRAN() in the
affected db's.
--
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:B04001DD-4233-4E2B-B62C-F4E6C960D77A@.microsoft.com...
> Andrew ,
> I have SP4 but still have issues with tempdb log (issue #1) , as I
> described
> below . Can you pl. suggest me any solution . I also read from MS support
> site ,using dbcc shrinkdatabase (and/or dbcc shrinkfile) could currept
> tempdb . Right .
> Pl. let me know .
> --Prabhu
> "Andrew J. Kelly" wrote:
>> If there are any long running open transactions it can not truncate that
>> portion of the log.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
>> news:14032F27-857D-4F34-9864-00A4D9514E67@.microsoft.com...
>> > Hi Andrew ,
>> >
>> > 1. I have SP4 ( 8.00.2040 ) .
>> > 2. If DB is in simple recovery mode , log is getting truncated (but
>> > space
>> > not released to OS , as expected) but for other recovery models backup
>> > log
>> > command sometimes truncates log and sometimes it won't .
>> >
>> > Thanks,
>> > Prabhu
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Do you have the latest service pack? I have heard of other postings
>> >> in
>> >> which the tempdb log grows but I can't find any KB on it. I think it
>> >> was
>> >> fixed with a service pack. This KB might be helpful:
>> >>
>> >> http://support.microsoft.com/Default.aspx?id=110139
>> >>
>> >> As for # 2 if you are in Full or BulkLogged recovery mode you must do
>> >> regular log backups otherwise the log will continue to grow. A full
>> >> backup
>> >> does not truncate the log only a proper Log backup will do that as
>> >> long
>> >> as
>> >> there are no open transactions.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
>> >> news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...
>> >> > Thanks for your reply Kelly .
>> >> >
>> >> > I understand shrinking the physical file(s) is not part of backup .
>> >> > Truncation does not reduce the size of a physical log file, it
>> >> > reduces
>> >> > the
>> >> > size of the logical log file
>> >> >
>> >> > But I have 2 weird situations .
>> >> >
>> >> > 1. My tempdb log just grows out of control . Only way we could
>> >> > reuse
>> >> > the
>> >> > space is after explicitly using backup log with truncate option and
>> >> > dbcc
>> >> > shrinkfile .
>> >> > But , Log truncation should occur ,every time a checkpoint is
>> >> > processed,
>> >> > provided the database is using the simple recovery model (tempdb is
>> >> > in
>> >> > recovery model).
>> >> > 2. Other user DBs log files also grow , they are in full or
>> >> > Bulk_Logged
>> >> > recovery model . We have daily fulldb maintenance plan to take only
>> >> > full
>> >> > DB
>> >> > backup . Does it truncate the log ? But looks like unused space in
>> >> > log
>> >> > files
>> >> > are not being reused and log files are just extending .
>> >> > I have also used backup log command (without truncate_only) but
>> >> > still
>> >> > unused
>> >> > space is not reused but log file grows .( log truncation should
>> >> > occur
>> >> > after
>> >> > BACKUP LOG statement , right . )
>> >> >
>> >> > Thanks,
>> >> > Prabhu
>> >> >
>> >> > "Andrew J. Kelly" wrote:
>> >> >
>> >> >> Shrinking is NOT part of a backup, log or db. You must use either
>> >> >> DBCC
>> >> >> SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
>> >> >>
>> >> >> 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
>> >> >>
>> >> >>
>> >> >> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
>> >> >> news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
>> >> >> > Hi ,
>> >> >> > When full db is backed up (using maintenance plan) , does
>> >> >> > transaction
>> >> >> > log get shrunk (inactive transactions are dropped from log) .
>> >> >> > Also
>> >> >> > does
>> >> >> > this
>> >> >> > space get released to OS .
>> >> >> > Can you pl. let me know answer for same question , when log is
>> >> >> > backed
>> >> >> > up
>> >> >> > explicity (using backup log without using truncate_only option )
>> >> >> > .
>> >> >> > What could be the reasons , why my log is not getting shrunk
>> >> >> > after
>> >> >> > DB
>> >> >> > and
>> >> >> > log backup . What are the other options available apart from
>> >> >> > using
>> >> >> > truncate_only option and dbcc shrinkfile .
>> >> >> >
>> >> >> > Thanks,
>> >> >> > Prabhu
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
When full db is backed up (using maintenance plan) , does transaction
log get shrunk (inactive transactions are dropped from log) . Also does this
space get released to OS .
Can you pl. let me know answer for same question , when log is backed up
explicity (using backup log without using truncate_only option ) .
What could be the reasons , why my log is not getting shrunk after DB and
log backup . What are the other options available apart from using
truncate_only option and dbcc shrinkfile .
Thanks,
PrabhuShrinking is NOT part of a backup, log or db. You must use either DBCC
SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
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
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
> Hi ,
> When full db is backed up (using maintenance plan) , does transaction
> log get shrunk (inactive transactions are dropped from log) . Also does
> this
> space get released to OS .
> Can you pl. let me know answer for same question , when log is backed up
> explicity (using backup log without using truncate_only option ) .
> What could be the reasons , why my log is not getting shrunk after DB and
> log backup . What are the other options available apart from using
> truncate_only option and dbcc shrinkfile .
> Thanks,
> Prabhu|||Thanks for your reply Kelly .
I understand shrinking the physical file(s) is not part of backup .
Truncation does not reduce the size of a physical log file, it reduces the
size of the logical log file
But I have 2 weird situations .
1. My tempdb log just grows out of control . Only way we could reuse the
space is after explicitly using backup log with truncate option and dbcc
shrinkfile .
But , Log truncation should occur ,every time a checkpoint is processed,
provided the database is using the simple recovery model (tempdb is in
recovery model).
2. Other user DBs log files also grow , they are in full or Bulk_Logged
recovery model . We have daily fulldb maintenance plan to take only full DB
backup . Does it truncate the log ? But looks like unused space in log files
are not being reused and log files are just extending .
I have also used backup log command (without truncate_only) but still unused
space is not reused but log file grows .( log truncation should occur after
BACKUP LOG statement , right . )
Thanks,
Prabhu
"Andrew J. Kelly" wrote:
> Shrinking is NOT part of a backup, log or db. You must use either DBCC
> SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
> 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
>
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
> > Hi ,
> > When full db is backed up (using maintenance plan) , does transaction
> > log get shrunk (inactive transactions are dropped from log) . Also does
> > this
> > space get released to OS .
> > Can you pl. let me know answer for same question , when log is backed up
> > explicity (using backup log without using truncate_only option ) .
> > What could be the reasons , why my log is not getting shrunk after DB and
> > log backup . What are the other options available apart from using
> > truncate_only option and dbcc shrinkfile .
> >
> > Thanks,
> > Prabhu
>
>|||Do you have the latest service pack? I have heard of other postings in
which the tempdb log grows but I can't find any KB on it. I think it was
fixed with a service pack. This KB might be helpful:
http://support.microsoft.com/Default.aspx?id=110139
As for # 2 if you are in Full or BulkLogged recovery mode you must do
regular log backups otherwise the log will continue to grow. A full backup
does not truncate the log only a proper Log backup will do that as long as
there are no open transactions.
--
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...
> Thanks for your reply Kelly .
> I understand shrinking the physical file(s) is not part of backup .
> Truncation does not reduce the size of a physical log file, it reduces the
> size of the logical log file
> But I have 2 weird situations .
> 1. My tempdb log just grows out of control . Only way we could reuse the
> space is after explicitly using backup log with truncate option and dbcc
> shrinkfile .
> But , Log truncation should occur ,every time a checkpoint is processed,
> provided the database is using the simple recovery model (tempdb is in
> recovery model).
> 2. Other user DBs log files also grow , they are in full or Bulk_Logged
> recovery model . We have daily fulldb maintenance plan to take only full
> DB
> backup . Does it truncate the log ? But looks like unused space in log
> files
> are not being reused and log files are just extending .
> I have also used backup log command (without truncate_only) but still
> unused
> space is not reused but log file grows .( log truncation should occur
> after
> BACKUP LOG statement , right . )
> Thanks,
> Prabhu
> "Andrew J. Kelly" wrote:
>> Shrinking is NOT part of a backup, log or db. You must use either DBCC
>> SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
>> 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
>>
>> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
>> news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
>> > Hi ,
>> > When full db is backed up (using maintenance plan) , does
>> > transaction
>> > log get shrunk (inactive transactions are dropped from log) . Also
>> > does
>> > this
>> > space get released to OS .
>> > Can you pl. let me know answer for same question , when log is backed
>> > up
>> > explicity (using backup log without using truncate_only option ) .
>> > What could be the reasons , why my log is not getting shrunk after DB
>> > and
>> > log backup . What are the other options available apart from using
>> > truncate_only option and dbcc shrinkfile .
>> >
>> > Thanks,
>> > Prabhu
>>|||Hi Andrew ,
1. I have SP4 ( 8.00.2040 ) .
2. If DB is in simple recovery mode , log is getting truncated (but space
not released to OS , as expected) but for other recovery models backup log
command sometimes truncates log and sometimes it won't .
Thanks,
Prabhu
"Andrew J. Kelly" wrote:
> Do you have the latest service pack? I have heard of other postings in
> which the tempdb log grows but I can't find any KB on it. I think it was
> fixed with a service pack. This KB might be helpful:
> http://support.microsoft.com/Default.aspx?id=110139
> As for # 2 if you are in Full or BulkLogged recovery mode you must do
> regular log backups otherwise the log will continue to grow. A full backup
> does not truncate the log only a proper Log backup will do that as long as
> there are no open transactions.
> --
> Andrew J. Kelly SQL MVP
>
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...
> > Thanks for your reply Kelly .
> >
> > I understand shrinking the physical file(s) is not part of backup .
> > Truncation does not reduce the size of a physical log file, it reduces the
> > size of the logical log file
> >
> > But I have 2 weird situations .
> >
> > 1. My tempdb log just grows out of control . Only way we could reuse the
> > space is after explicitly using backup log with truncate option and dbcc
> > shrinkfile .
> > But , Log truncation should occur ,every time a checkpoint is processed,
> > provided the database is using the simple recovery model (tempdb is in
> > recovery model).
> > 2. Other user DBs log files also grow , they are in full or Bulk_Logged
> > recovery model . We have daily fulldb maintenance plan to take only full
> > DB
> > backup . Does it truncate the log ? But looks like unused space in log
> > files
> > are not being reused and log files are just extending .
> > I have also used backup log command (without truncate_only) but still
> > unused
> > space is not reused but log file grows .( log truncation should occur
> > after
> > BACKUP LOG statement , right . )
> >
> > Thanks,
> > Prabhu
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Shrinking is NOT part of a backup, log or db. You must use either DBCC
> >> SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
> >>
> >> 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
> >>
> >>
> >> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> >> news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
> >> > Hi ,
> >> > When full db is backed up (using maintenance plan) , does
> >> > transaction
> >> > log get shrunk (inactive transactions are dropped from log) . Also
> >> > does
> >> > this
> >> > space get released to OS .
> >> > Can you pl. let me know answer for same question , when log is backed
> >> > up
> >> > explicity (using backup log without using truncate_only option ) .
> >> > What could be the reasons , why my log is not getting shrunk after DB
> >> > and
> >> > log backup . What are the other options available apart from using
> >> > truncate_only option and dbcc shrinkfile .
> >> >
> >> > Thanks,
> >> > Prabhu
> >>
> >>
> >>
>
>|||If there are any long running open transactions it can not truncate that
portion of the log.
--
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:14032F27-857D-4F34-9864-00A4D9514E67@.microsoft.com...
> Hi Andrew ,
> 1. I have SP4 ( 8.00.2040 ) .
> 2. If DB is in simple recovery mode , log is getting truncated (but space
> not released to OS , as expected) but for other recovery models backup log
> command sometimes truncates log and sometimes it won't .
> Thanks,
> Prabhu
>
> "Andrew J. Kelly" wrote:
>> Do you have the latest service pack? I have heard of other postings in
>> which the tempdb log grows but I can't find any KB on it. I think it was
>> fixed with a service pack. This KB might be helpful:
>> http://support.microsoft.com/Default.aspx?id=110139
>> As for # 2 if you are in Full or BulkLogged recovery mode you must do
>> regular log backups otherwise the log will continue to grow. A full
>> backup
>> does not truncate the log only a proper Log backup will do that as long
>> as
>> there are no open transactions.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
>> news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...
>> > Thanks for your reply Kelly .
>> >
>> > I understand shrinking the physical file(s) is not part of backup .
>> > Truncation does not reduce the size of a physical log file, it reduces
>> > the
>> > size of the logical log file
>> >
>> > But I have 2 weird situations .
>> >
>> > 1. My tempdb log just grows out of control . Only way we could reuse
>> > the
>> > space is after explicitly using backup log with truncate option and
>> > dbcc
>> > shrinkfile .
>> > But , Log truncation should occur ,every time a checkpoint is
>> > processed,
>> > provided the database is using the simple recovery model (tempdb is in
>> > recovery model).
>> > 2. Other user DBs log files also grow , they are in full or
>> > Bulk_Logged
>> > recovery model . We have daily fulldb maintenance plan to take only
>> > full
>> > DB
>> > backup . Does it truncate the log ? But looks like unused space in log
>> > files
>> > are not being reused and log files are just extending .
>> > I have also used backup log command (without truncate_only) but still
>> > unused
>> > space is not reused but log file grows .( log truncation should occur
>> > after
>> > BACKUP LOG statement , right . )
>> >
>> > Thanks,
>> > Prabhu
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Shrinking is NOT part of a backup, log or db. You must use either
>> >> DBCC
>> >> SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
>> >>
>> >> 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
>> >>
>> >>
>> >> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
>> >> news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
>> >> > Hi ,
>> >> > When full db is backed up (using maintenance plan) , does
>> >> > transaction
>> >> > log get shrunk (inactive transactions are dropped from log) . Also
>> >> > does
>> >> > this
>> >> > space get released to OS .
>> >> > Can you pl. let me know answer for same question , when log is
>> >> > backed
>> >> > up
>> >> > explicity (using backup log without using truncate_only option ) .
>> >> > What could be the reasons , why my log is not getting shrunk after
>> >> > DB
>> >> > and
>> >> > log backup . What are the other options available apart from using
>> >> > truncate_only option and dbcc shrinkfile .
>> >> >
>> >> > Thanks,
>> >> > Prabhu
>> >>
>> >>
>> >>
>>|||Andrew ,
I have SP4 but still have issues with tempdb log (issue #1) , as I described
below . Can you pl. suggest me any solution . I also read from MS support
site ,using dbcc shrinkdatabase (and/or dbcc shrinkfile) could currept
tempdb . Right .
Pl. let me know .
--Prabhu
"Andrew J. Kelly" wrote:
> If there are any long running open transactions it can not truncate that
> portion of the log.
> --
> Andrew J. Kelly SQL MVP
>
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:14032F27-857D-4F34-9864-00A4D9514E67@.microsoft.com...
> > Hi Andrew ,
> >
> > 1. I have SP4 ( 8.00.2040 ) .
> > 2. If DB is in simple recovery mode , log is getting truncated (but space
> > not released to OS , as expected) but for other recovery models backup log
> > command sometimes truncates log and sometimes it won't .
> >
> > Thanks,
> > Prabhu
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Do you have the latest service pack? I have heard of other postings in
> >> which the tempdb log grows but I can't find any KB on it. I think it was
> >> fixed with a service pack. This KB might be helpful:
> >>
> >> http://support.microsoft.com/Default.aspx?id=110139
> >>
> >> As for # 2 if you are in Full or BulkLogged recovery mode you must do
> >> regular log backups otherwise the log will continue to grow. A full
> >> backup
> >> does not truncate the log only a proper Log backup will do that as long
> >> as
> >> there are no open transactions.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> >> news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...
> >> > Thanks for your reply Kelly .
> >> >
> >> > I understand shrinking the physical file(s) is not part of backup .
> >> > Truncation does not reduce the size of a physical log file, it reduces
> >> > the
> >> > size of the logical log file
> >> >
> >> > But I have 2 weird situations .
> >> >
> >> > 1. My tempdb log just grows out of control . Only way we could reuse
> >> > the
> >> > space is after explicitly using backup log with truncate option and
> >> > dbcc
> >> > shrinkfile .
> >> > But , Log truncation should occur ,every time a checkpoint is
> >> > processed,
> >> > provided the database is using the simple recovery model (tempdb is in
> >> > recovery model).
> >> > 2. Other user DBs log files also grow , they are in full or
> >> > Bulk_Logged
> >> > recovery model . We have daily fulldb maintenance plan to take only
> >> > full
> >> > DB
> >> > backup . Does it truncate the log ? But looks like unused space in log
> >> > files
> >> > are not being reused and log files are just extending .
> >> > I have also used backup log command (without truncate_only) but still
> >> > unused
> >> > space is not reused but log file grows .( log truncation should occur
> >> > after
> >> > BACKUP LOG statement , right . )
> >> >
> >> > Thanks,
> >> > Prabhu
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> Shrinking is NOT part of a backup, log or db. You must use either
> >> >> DBCC
> >> >> SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
> >> >>
> >> >> 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
> >> >>
> >> >>
> >> >> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> >> >> news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
> >> >> > Hi ,
> >> >> > When full db is backed up (using maintenance plan) , does
> >> >> > transaction
> >> >> > log get shrunk (inactive transactions are dropped from log) . Also
> >> >> > does
> >> >> > this
> >> >> > space get released to OS .
> >> >> > Can you pl. let me know answer for same question , when log is
> >> >> > backed
> >> >> > up
> >> >> > explicity (using backup log without using truncate_only option ) .
> >> >> > What could be the reasons , why my log is not getting shrunk after
> >> >> > DB
> >> >> > and
> >> >> > log backup . What are the other options available apart from using
> >> >> > truncate_only option and dbcc shrinkfile .
> >> >> >
> >> >> > Thanks,
> >> >> > Prabhu
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||As I stated if you have long running open transactions in the db the log
file may not be able to be truncated. Try running DBCC OPENTRAN() in the
affected db's.
--
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:B04001DD-4233-4E2B-B62C-F4E6C960D77A@.microsoft.com...
> Andrew ,
> I have SP4 but still have issues with tempdb log (issue #1) , as I
> described
> below . Can you pl. suggest me any solution . I also read from MS support
> site ,using dbcc shrinkdatabase (and/or dbcc shrinkfile) could currept
> tempdb . Right .
> Pl. let me know .
> --Prabhu
> "Andrew J. Kelly" wrote:
>> If there are any long running open transactions it can not truncate that
>> portion of the log.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
>> news:14032F27-857D-4F34-9864-00A4D9514E67@.microsoft.com...
>> > Hi Andrew ,
>> >
>> > 1. I have SP4 ( 8.00.2040 ) .
>> > 2. If DB is in simple recovery mode , log is getting truncated (but
>> > space
>> > not released to OS , as expected) but for other recovery models backup
>> > log
>> > command sometimes truncates log and sometimes it won't .
>> >
>> > Thanks,
>> > Prabhu
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Do you have the latest service pack? I have heard of other postings
>> >> in
>> >> which the tempdb log grows but I can't find any KB on it. I think it
>> >> was
>> >> fixed with a service pack. This KB might be helpful:
>> >>
>> >> http://support.microsoft.com/Default.aspx?id=110139
>> >>
>> >> As for # 2 if you are in Full or BulkLogged recovery mode you must do
>> >> regular log backups otherwise the log will continue to grow. A full
>> >> backup
>> >> does not truncate the log only a proper Log backup will do that as
>> >> long
>> >> as
>> >> there are no open transactions.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
>> >> news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...
>> >> > Thanks for your reply Kelly .
>> >> >
>> >> > I understand shrinking the physical file(s) is not part of backup .
>> >> > Truncation does not reduce the size of a physical log file, it
>> >> > reduces
>> >> > the
>> >> > size of the logical log file
>> >> >
>> >> > But I have 2 weird situations .
>> >> >
>> >> > 1. My tempdb log just grows out of control . Only way we could
>> >> > reuse
>> >> > the
>> >> > space is after explicitly using backup log with truncate option and
>> >> > dbcc
>> >> > shrinkfile .
>> >> > But , Log truncation should occur ,every time a checkpoint is
>> >> > processed,
>> >> > provided the database is using the simple recovery model (tempdb is
>> >> > in
>> >> > recovery model).
>> >> > 2. Other user DBs log files also grow , they are in full or
>> >> > Bulk_Logged
>> >> > recovery model . We have daily fulldb maintenance plan to take only
>> >> > full
>> >> > DB
>> >> > backup . Does it truncate the log ? But looks like unused space in
>> >> > log
>> >> > files
>> >> > are not being reused and log files are just extending .
>> >> > I have also used backup log command (without truncate_only) but
>> >> > still
>> >> > unused
>> >> > space is not reused but log file grows .( log truncation should
>> >> > occur
>> >> > after
>> >> > BACKUP LOG statement , right . )
>> >> >
>> >> > Thanks,
>> >> > Prabhu
>> >> >
>> >> > "Andrew J. Kelly" wrote:
>> >> >
>> >> >> Shrinking is NOT part of a backup, log or db. You must use either
>> >> >> DBCC
>> >> >> SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
>> >> >>
>> >> >> 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
>> >> >>
>> >> >>
>> >> >> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
>> >> >> news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
>> >> >> > Hi ,
>> >> >> > When full db is backed up (using maintenance plan) , does
>> >> >> > transaction
>> >> >> > log get shrunk (inactive transactions are dropped from log) .
>> >> >> > Also
>> >> >> > does
>> >> >> > this
>> >> >> > space get released to OS .
>> >> >> > Can you pl. let me know answer for same question , when log is
>> >> >> > backed
>> >> >> > up
>> >> >> > explicity (using backup log without using truncate_only option )
>> >> >> > .
>> >> >> > What could be the reasons , why my log is not getting shrunk
>> >> >> > after
>> >> >> > DB
>> >> >> > and
>> >> >> > log backup . What are the other options available apart from
>> >> >> > using
>> >> >> > truncate_only option and dbcc shrinkfile .
>> >> >> >
>> >> >> > Thanks,
>> >> >> > Prabhu
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
Subscribe to:
Posts (Atom)