Get Next Number: plan

Showing posts with label plan. Show all posts

Monday, March 26, 2012

FT query plan

The following query:
SELECT PatientGUID
FROM PATIENT_SEARCH PS
WHERE PS.LicenseID = '465f20fc-8bd5-4802-a3f5-2a5f702be128'
AND CONTAINS (PS.SEARCHCOL , ' "patient*" ')
Shows 2500 rows qualified by LicenseID =
'465f20fc-8bd5-4802-a3f5-2a5f702be128', but 5000000 rows for "Remote
Scan". Takes about 30 secs to complete which is a lot slower than even
LIKE '%patient%''
I would expect it to only do FT on those 2500.
Thank you,
Igor
*** Sent via Developersdex http://www.codecomments.com ***
Statistics coming back from the remote scan of the full-text catalog are
frequently not accurate.
The real problem here is that you are doing trimming where the complete
results set that matches the wild card search on patient has to be returned
from full-text to SQL Server and then only rows which contains the licenseid
of '465f20fc-8bd5-4802-a3f5-2a5f702be128' are then returned.
"mEmENT0m0RI" <nospam@.devdex.com> wrote in message
news:uaslhAqhHHA.4064@.TK2MSFTNGP02.phx.gbl...
> The following query:
> SELECT PatientGUID
> FROM PATIENT_SEARCH PS
> WHERE PS.LicenseID = '465f20fc-8bd5-4802-a3f5-2a5f702be128'
> AND CONTAINS (PS.SEARCHCOL , ' "patient*" ')
> Shows 2500 rows qualified by LicenseID =
> '465f20fc-8bd5-4802-a3f5-2a5f702be128', but 5000000 rows for "Remote
> Scan". Takes about 30 secs to complete which is a lot slower than even
> LIKE '%patient%''
> I would expect it to only do FT on those 2500.
>
> Thank you,
> Igor
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Hilary,
Tha is my problem exactly. How can I force it to do the lookup on
LicenseID and only then apply the FT to the remaining rows?
Thank you,
Igor
*** Sent via Developersdex http://www.codecomments.com ***
|||If licenseID is discrete enough you could partition or use a full-text index
on an indexed view.
Otherwise you might be able to store it in the SearchCol column as well and
then search on patient* and your licenseId.
The problem with this approach is the more search terms you have the worse
your search. The performance degradation going from one to two terms is not
that significant however.
"mEmENT0m0RI" <nospam@.devdex.com> wrote in message
news:ObdYRu2hHHA.3960@.TK2MSFTNGP02.phx.gbl...
> Hilary,
> Tha is my problem exactly. How can I force it to do the lookup on
> LicenseID and only then apply the FT to the remaining rows?
> Thank you,
> Igor
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||I don't understand.
LicenseID is on the same table right now as the FT column and LicenseID
is indexed. How would an indexed view help the performance in this
situation?
*** Sent via Developersdex http://www.codecomments.com ***
sql

frying pan to fire - log ship or something else

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

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,
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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

Monday, March 26, 2012

FT query plan

frying pan to fire - log ship or something else

Sunday, February 26, 2012

Free up the transaction log

Free up the transaction log

Free up the transaction log

Get Next Number

Blog Archive

About Me