Locks and Blocks: index

Showing posts with label index. Show all posts

Friday, February 24, 2012

Log backup does not truncate the log file

Hi,
My db uses full recovery model. I used maintenance wizard to perform:
1) Index rebuild every night
2) Full backup once a week
3) Log backup every night
4) Integrity check every night
The log file is 1GB now and because of regular index rebuild I expected this
growth. DBCC LOGINFO displays 243 VLFs that status of all are 0 except the
row 240 which is 2.
My problem is that the log backup does not free the space to OS. I checked
shrink file GUI in EM and it says the minimum log file can be 23MB! So why
the log backup does not free the space?
Thanks in advance,
LeilaLeila wrote:
> Hi,
> My db uses full recovery model. I used maintenance wizard to perform:
> 1) Index rebuild every night
> 2) Full backup once a week
> 3) Log backup every night
> 4) Integrity check every night
> The log file is 1GB now and because of regular index rebuild I expected this
> growth. DBCC LOGINFO displays 243 VLFs that status of all are 0 except the
> row 240 which is 2.
> My problem is that the log backup does not free the space to OS. I checked
> shrink file GUI in EM and it says the minimum log file can be 23MB! So why
> the log backup does not free the space?
> Thanks in advance,
> Leila
>
To clear unused virtual files you can try:
DBCC SHRINKFILE(<database name>_log,<size in MB>)
for more details (and if that does not work) see:
http://support.microsoft.com/kb/272318/|||I know this command, but based on explanation in BOL, the BACKUP LOG must
truncate the log file automatically. Is it true or I have misunderstood
this?
Thanks!
"Zero One" <efes_echad@.hotmail.com> wrote in message
news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
> To clear unused virtual files you can try:
> DBCC SHRINKFILE(<database name>_log,<size in MB>)
> for more details (and if that does not work) see:
> http://support.microsoft.com/kb/272318/|||Leila wrote:
> I know this command, but based on explanation in BOL, the BACKUP LOG must
> truncate the log file automatically. Is it true or I have misunderstood
> this?
> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>
The backup only marks unused VLFs as deleted but does not remove them.
The shrink does.|||You mean the shrink does not break the log chain?
"Zero One" <efes_echad@.hotmail.com> wrote in message
news:OyIUXbUJHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Leila wrote:
>> I know this command, but based on explanation in BOL, the BACKUP LOG must
>> truncate the log file automatically. Is it true or I have misunderstood
>> this?
>> Thanks!
>>
>> "Zero One" <efes_echad@.hotmail.com> wrote in message
>> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>>
> The backup only marks unused VLFs as deleted but does not remove them. The
> shrink does.|||Leila wrote:
> You mean the shrink does not break the log chain?
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:OyIUXbUJHHA.4376@.TK2MSFTNGP03.phx.gbl...
>> Leila wrote:
>> I know this command, but based on explanation in BOL, the BACKUP LOG must
>> truncate the log file automatically. Is it true or I have misunderstood
>> this?
>> Thanks!
>>
>> "Zero One" <efes_echad@.hotmail.com> wrote in message
>> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>> The backup only marks unused VLFs as deleted but does not remove them. The
>> shrink does.
>
Quoting from the aforementioned KB:
"Shrinking the log in SQL Server 2000 is no longer a deferred operation."
The shrink only removes unused VLFs that where invalidated during the
backup. You can only break the log chain by invalidating VLFs WITHOUT
backing them up and then shrinking. For example (from the KB):
BACKUP LOG pubs WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(pubs_log,2)
If you use TRUNCATE_ONLY you break the log chain and have to do a full
backup. Otherwise it should be safe (as far as I understand).|||Leila
TRUNCATE is a logical operation, not a physical one. The fact that DBCC
LOGINFO indicates all 0's means that the log has been truncated, and the
VLFs are now reusable. To reduce the physical size of the operating system
file, you have to DBCC SHRINKFILE.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>I know this command, but based on explanation in BOL, the BACKUP LOG must
>truncate the log file automatically. Is it true or I have misunderstood
>this?
> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>I know this command, but based on explanation in BOL, the BACKUP LOG must
>truncate the log file automatically. Is it true or I have misunderstood
>this?
You misunderstood.
Truncate != shrink.
And generally you do NOT want to shrink the log file if it's just going to
grow again. This can lead to performance issues (as it expands each time)
and disk level fragmentation.
> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>|||Thanks every body for clarifications :-)
BTW, I cannot browse your blog Kalen!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ueaCxRVJHHA.3916@.TK2MSFTNGP02.phx.gbl...
> Leila
> TRUNCATE is a logical operation, not a physical one. The fact that DBCC
> LOGINFO indicates all 0's means that the log has been truncated, and the
> VLFs are now reusable. To reduce the physical size of the operating system
> file, you have to DBCC SHRINKFILE.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>>I know this command, but based on explanation in BOL, the BACKUP LOG must
>>truncate the log file automatically. Is it true or I have misunderstood
>>this?
>> Thanks!
>>
>> "Zero One" <efes_echad@.hotmail.com> wrote in message
>> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>>
>|||What do you mean?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:OuDzdfZJHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Thanks every body for clarifications :-)
> BTW, I cannot browse your blog Kalen!

Log backup does not truncate the log file

Monday, February 20, 2012

Locks on a table with nonclustered index

If i have a query such as
delete from table1
where col1 = 200
Say i had a non clustered index on col1 and thats the only index that i
have...
1) Can the execution plan include a non-clustered index scan if the
selectivity is low ?
2) If it does use a nonclustered index scan and maybe start of with row
locks and then escalates to table lock, how do you find out if the lock is
on the index pages or on the data pages or on both although in sp_lock it
would show an exclusive table lock ?
3) Even it it just row locks, what info can we get from the resource column
in sp_lock to tell us whether a row in the data page or index page is being
locked.. Would it just be data pages or would it be holding multiple row
locks ..some to delete its rows in data pages and some to delete the rows in
its index pages1. I doubt it would do an index scan in this case, but if the selectivity
were high enough, it could do an index seek.
2. I'm not sure exactly what you're asking. If there is a table lock X,
other processes cannot access the indexes on that table, so whether the
index pages are locked is irrelevant.
3. The resource column doesn't directly tell us, but if you have a page
number, you can use dbcc page to tell what kind of page it is. Also, you can
use the indid column, and if it is >1, then the page is from a nonclustered
index. You can have key locks (which are row locks in an index) on both
index keys and rows/keys in the table itself. In fact, if the table has nc
indexes, you'll usually see both.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"sql" <sql@.hotmail.com> wrote in message
news:u47QCA9hDHA.460@.TK2MSFTNGP12.phx.gbl...
> If i have a query such as
> delete from table1
> where col1 = 200
> Say i had a non clustered index on col1 and thats the only index that i
> have...
> 1) Can the execution plan include a non-clustered index scan if the
> selectivity is low ?
> 2) If it does use a nonclustered index scan and maybe start of with row
> locks and then escalates to table lock, how do you find out if the lock is
> on the index pages or on the data pages or on both although in sp_lock it
> would show an exclusive table lock ?
> 3) Even it it just row locks, what info can we get from the resource
column
> in sp_lock to tell us whether a row in the data page or index page is
being
> locked.. Would it just be data pages or would it be holding multiple row
> locks ..some to delete its rows in data pages and some to delete the rows
in
> its index pages
>
>

Friday, February 24, 2012

Log backup does not truncate the log file

Log backup does not truncate the log file

Log backup does not truncate the log file

Monday, February 20, 2012

Locks on a table with nonclustered index

Locks and Blocks

Blog Archive

About Me