Query about Pages & Extents - Newbie

HI,
Just got puzzled. Was going through SQL Arch. basics and
encountered about pages (8kb) and extents ( 8pg * 8kb).
somewhere it says *Transaction log space is not measured
in extents* . Could any one give details on how SQL pages
& extents concept is used differently on a database and
transaction log file?
Regards
ChipHi Chip
In the data files, the data is organized into tables and table pages are all
8KB. Space is allocated to a table in units of 8 pages (8 * 8 KB, or one
extent) at a time, so there is room for more rows to be added before more
allocation needs to be done. The new extents allocated to a table can can
from anywhere in the file, although SQL Server will try to find an extent
close by to existing extents for the table, wherever possible.
Log files are not organized into tables. Log records are just written to the
log file sequentially as transactions commit. No preallocation of space is
done for the log. It's just a record of the changes to your database.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:006f01c3d225$d0347eb0$a401280a@.phx.gbl...
> HI,
> Just got puzzled. Was going through SQL Arch. basics and
> encountered about pages (8kb) and extents ( 8pg * 8kb).
> somewhere it says *Transaction log space is not measured
> in extents* . Could any one give details on how SQL pages
> & extents concept is used differently on a database and
> transaction log file?
> Regards
> Chip|||Hi Kalen,
Thanks a Lot for the explanation. It was a gunshot.
Best Regards
Chip
>--Original Message--
>Hi Chip
>In the data files, the data is organized into tables and
table pages are all
>8KB. Space is allocated to a table in units of 8 pages (8
* 8 KB, or one
>extent) at a time, so there is room for more rows to be
added before more
>allocation needs to be done. The new extents allocated to
a table can can
>from anywhere in the file, although SQL Server will try
to find an extent
>close by to existing extents for the table, wherever
possible.
>Log files are not organized into tables. Log records are
just written to the
>log file sequentially as transactions commit. No
preallocation of space is
>done for the log. It's just a record of the changes to
your database.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
>news:006f01c3d225$d0347eb0$a401280a@.phx.gbl...
>> HI,
>> Just got puzzled. Was going through SQL Arch. basics and
>> encountered about pages (8kb) and extents ( 8pg * 8kb).
>> somewhere it says *Transaction log space is not measured
>> in extents* . Could any one give details on how SQL
pages
>> & extents concept is used differently on a database and
>> transaction log file?
>> Regards
>> Chip
>
>.
>

Query about keeping down the size of transaction Logs

Hi I have been struggerling with a problem with the transaction log for a while now and find I'm just getting myself in more of a mess.

My main aim is to get around the problem that the transaction logs just keep getting bigger and bigger.

Now I have look at a few ways around it firstly I tried to use the commands:

BACKUP LOG db_name WITH TRUNCATE_ONLY
DBCC SHRINKFILE (log_name,2)

and this worked really well to reduce the size to a manageable level, however I only just found out the real impact that it has on my recovery ability.

The other solution I have looked at was to backup the database and then to use this command to backup the Transaction Logs and clear them down.

BACKUP LOG db_name
TO Disk = 'C:/example.TRN'
WITH INIT;

However after testing this it doesn't seem to reduce the size of the transaction Logs at all.

It may be a problem with my understand of the way the transaction log works, I persume that when you have a good backup then to get a point in time recovery all you should need is the details of transactions that have happened past the point of the backup and therefore be able to somehow perge the log of all other data. And that is what I persummed the last command I tried would do. However the .LDF file remains the same size.

Am i doing something wrong am I missing something out?

Thanks for any help! EdHowdy

You are on the right track, here are some ideas :

When you take a full backup, it resets all the counters for backups and any tran log backups before the last full backup cant be used after it. OK, so if you want to do a point in time recovery, do a full backup then do tran log backups after that.

What you can do ( during after hours as then you have no activity in the database to cause problems) is this :

(1) start up Query Analyser and connect to the database
(2) execute the command CHECKPOINT
- this will write all inactive entries in the tran log to the database and give you a know starting point for size reduction.

(3) run the command :
DBCC SHRINKFILE('log_file_logical_name',10)

This will attempt to shrink the tran log to 10 MB. This *should* remove all the empty space from the tran log.

****Alternatively ( and this is a big-ish step so make sure no-one is on the database when its happening as while this is going on, you WILL NOT have database recovery capability and will have to go back to your last full backup if problems occur ) :

(1) Take full backup of the database & keep safely to one side ( just in case ) and label it as "BEFORE_<DB_NAME>_SHRINK.BAK" or similar.

(2) Change database into SIMPLE recovery mode. This will allow maximum shrinkage of the tran log. This is the old "truncate log on checkpoint" setting from SQL 6.5/7 .

(3) Open Query Analyser on the database & execute CHECKPOINT .
Then execute :
DBCC SHRINKFILE('<db_data_file_logical_name>',10)
DBCC SHRINKFILE('<db_log_file_logical_name>',10)

(4) Change the database back to FULL recovery mode.

(5) Take a full backup - VERY IMPORTANT. Note that this is the reference point that tran log backups will use if you need to recover the database.

This will be the case untill you do your next normal scheduled full backup, at which point that full backup will be the starting point for any tran logs that follow it, etc.

Post back if anything not clear.

Cheers

SG.|||Thanks SQLguy I think I finally understand now. It all seems to be working okay, that a relieve! Cheers Nixies

Query a Sql Server LOG (LDF) File

Hello guys,
Actually i was thinking is there any way to query the LDF ( sqlserver transaction log file) to get the operations performed during that time span?
Hope we can do it!
Regards
Salim
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...Get LogExplorer from www.lumigent.com , it's the only way AFAIK.
--
Jacco Schalkwijk
SQL Server MVP
"Salim" <salim_belim@.hargreaveslansdown.co.uk> wrote in message
news:%23repm8$kDHA.1096@.TK2MSFTNGP11.phx.gbl...
> Hello guys,
> Actually i was thinking is there any way to query the LDF ( sqlserver
transaction log file) to get the operations performed during that time span?
> Hope we can do it!
> Regards
> Salim
>
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...|||Hi,
You can use this undocumented command:
DBCC log ( {dbid|dbname } [, { -1 | 0 | 1 | 2 | 3 | 4}] )
For example:
DBCC log ('pubs', 2)
I'm sure that you can find more information about this if you search on
google, i don't have the information about what each number means right now.
--
Regards,
Kristofer Gafvert - IIS MVP
http://www.ilopia.com - FAQ & Tutorials for Windows Server 2003, and SQL
Server 2000
Reply to newsgroup only. Remove NEWS if you must reply by email, but please
do not.
Problems with spam and viruses? See
http://www.ilopia.com/security/newsposting.aspx
"Salim" <salim_belim@.hargreaveslansdown.co.uk> wrote in message
news:%23repm8$kDHA.1096@.TK2MSFTNGP11.phx.gbl...
> Hello guys,
> Actually i was thinking is there any way to query the LDF ( sqlserver
transaction log file) to get the operations performed during that time span?
> Hope we can do it!
> Regards
> Salim
>
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

q; Transaction in trigger

Hello,
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transaction
if both insert and update do not succeed?
A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>
|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

q; Transaction in trigger

Hello,
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transactio
n
if both insert and update do not succeed?A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

q; Transaction in trigger

Hello,
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transaction
if both insert and update do not succeed?A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

q; how to improve this transaction

Hello,
I have four different transactions such as below and I do one insert and one
update in each transaction and it seem it is slow and creates deadlock with
the user interface.
These transactions are performed against the tables that users are accessing
with another user interface. I have following two questions:
1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
not put index since only two distinct values. Should I put indexes on these
fields?
2. Can I make this transaction let user interface do its task in case
accessing the same rows, I can start transaction again but I do not want
users get disturbed?
.
BEGIN TRANSACTION pTrans
BEGIN
INSERT INTO T1
(fields)
SELECT (fields)
FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID
WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)
UPDATE T2
SET TextField2 = 'Ok', TextField2Date=@.MyRunDateTime
FROM T2
WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)
IF @.@.ERROR <> 0
BEGIN
rollback transaction pTrans
return(-1)
END
ELSE
BEGIN
commit transaction pTrans
END
ENDadd a composite, non-clustered index on textfield1,textfield2.
otherwise, you will have to do a table scan for each update.
>|||On Wed, 12 Jul 2006 16:19:01 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>I have four different transactions such as below and I do one insert and one
>update in each transaction and it seem it is slow and creates deadlock with
>the user interface.
>These transactions are performed against the tables that users are accessing
>with another user interface. I have following two questions:
>1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
>not put index since only two distinct values. Should I put indexes on these
>fields?
>2. Can I make this transaction let user interface do its task in case
>accessing the same rows, I can start transaction again but I do not want
>users get disturbed?
How long does the transaction take if all alone on the machine?
How much IO does it do (from profiler or "set statistic io on" in
query analyzer)?
Are you familiar with the nolock hint?
Do you have access to the code for the other program accessing the
database? That's the one that might need the nolock.
You might want to do the selects for one or both statements (the
update does an implicit select) and put the results into temp files or
@.tables, then insert and update from them, to minimize locking times.
Depends on times and data volumes and PKs.
Josh

q; how to improve this transaction

Hello,
I have four different transactions such as below and I do one insert and one
update in each transaction and it seem it is slow and creates deadlock with
the user interface.
These transactions are performed against the tables that users are accessing
with another user interface. I have following two questions:
1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
not put index since only two distinct values. Should I put indexes on these
fields?
2. Can I make this transaction let user interface do its task in case
accessing the same rows, I can start transaction again but I do not want
users get disturbed?
.
BEGIN TRANSACTION pTrans
BEGIN
INSERT INTO T1
(fields)
SELECT (fields)
FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID
WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)
UPDATE T2
SET TextField2 = 'Ok', TextField2Date=@.MyRunDateTime
FROM T2
WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)
IF @.@.ERROR <> 0
BEGIN
rollback transaction pTrans
return(-1)
END
ELSE
BEGIN
commit transaction pTrans
END
ENDadd a composite, non-clustered index on textfield1,textfield2.
otherwise, you will have to do a table scan for each update.
>|||On Wed, 12 Jul 2006 16:19:01 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>I have four different transactions such as below and I do one insert and on
e
>update in each transaction and it seem it is slow and creates deadlock with
>the user interface.
>These transactions are performed against the tables that users are accessin
g
>with another user interface. I have following two questions:
>1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
>not put index since only two distinct values. Should I put indexes on these
>fields?
>2. Can I make this transaction let user interface do its task in case
>accessing the same rows, I can start transaction again but I do not want
>users get disturbed?
How long does the transaction take if all alone on the machine?
How much IO does it do (from profiler or "set statistic io on" in
query analyzer)?
Are you familiar with the nolock hint?
Do you have access to the code for the other program accessing the
database? That's the one that might need the nolock.
You might want to do the selects for one or both statements (the
update does an implicit select) and put the results into temp files or
@.tables, then insert and update from them, to minimize locking times.
Depends on times and data volumes and PKs.
Josh

Q: TRN backup files

Hello,
In my backup maintenance, I have set deletion of backup files after 3 days,
it works for complete backup but not for transaction backup. (I see *.BAK are
removed, *.TRN are still there) what is problem?
Thanks,
Jim.Perhaps the plan involves trying to do log backup for databases which are in simple recovery mode?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:E03BBDB2-3ACF-4656-9BDE-1BB596DA74D9@.microsoft.com...
> Hello,
> In my backup maintenance, I have set deletion of backup files after 3 days,
> it works for complete backup but not for transaction backup. (I see *.BAK are
> removed, *.TRN are still there) what is problem?
> Thanks,
> Jim.
>|||where can I check if it is in recovery mode?
"Tibor Karaszi" wrote:
> Perhaps the plan involves trying to do log backup for databases which are in simple recovery mode?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:E03BBDB2-3ACF-4656-9BDE-1BB596DA74D9@.microsoft.com...
> > Hello,
> > In my backup maintenance, I have set deletion of backup files after 3 days,
> > it works for complete backup but not for transaction backup. (I see *.BAK are
> > removed, *.TRN are still there) what is problem?
> > Thanks,
> > Jim.
> >
>
>|||yes you were right, the database is in full mode, other databases are in
simple mode, what is difference? If simple mode, is it not recoverable? I do
not see any TRN files if the database is in simple mode, is this a problem?
"Tibor Karaszi" wrote:
> Perhaps the plan involves trying to do log backup for databases which are in simple recovery mode?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:E03BBDB2-3ACF-4656-9BDE-1BB596DA74D9@.microsoft.com...
> > Hello,
> > In my backup maintenance, I have set deletion of backup files after 3 days,
> > it works for complete backup but not for transaction backup. (I see *.BAK are
> > removed, *.TRN are still there) what is problem?
> > Thanks,
> > Jim.
> >
>
>|||mmm. I was reading it, I guess I need to keep it in full mode, but how far
does this TRN file go, is there a limit, or can we create a limit?
"Tibor Karaszi" wrote:
> Perhaps the plan involves trying to do log backup for databases which are in simple recovery mode?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:E03BBDB2-3ACF-4656-9BDE-1BB596DA74D9@.microsoft.com...
> > Hello,
> > In my backup maintenance, I have set deletion of backup files after 3 days,
> > it works for complete backup but not for transaction backup. (I see *.BAK are
> > removed, *.TRN are still there) what is problem?
> > Thanks,
> > Jim.
> >
>
>|||This is a too big topic for a newsgroup conversation. I suggest you read in Books Online about how
backup and restore work in SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:2734BEE1-0312-45BE-B1F2-5EEFD6450A69@.microsoft.com...
> yes you were right, the database is in full mode, other databases are in
> simple mode, what is difference? If simple mode, is it not recoverable? I do
> not see any TRN files if the database is in simple mode, is this a problem?
> "Tibor Karaszi" wrote:
>> Perhaps the plan involves trying to do log backup for databases which are in simple recovery
>> mode?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:E03BBDB2-3ACF-4656-9BDE-1BB596DA74D9@.microsoft.com...
>> > Hello,
>> > In my backup maintenance, I have set deletion of backup files after 3 days,
>> > it works for complete backup but not for transaction backup. (I see *.BAK are
>> > removed, *.TRN are still there) what is problem?
>> > Thanks,
>> > Jim.
>> >
>>