What are the SQL Server commands that will shrink or release database
transaction log used disk space afer the database backup has completed?
Thank You,These two things are not related. A database backup will not empty the log.
A log backup will,
however. To then shrink the log file, use DBCC SHRINKFILE. But read
http://www.karaszi.com/SQLServer/info_dont_shrink.asp first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:85D71BCF-F22C-4035-8A02-16771C713B58@.microsoft.com...
> What are the SQL Server commands that will shrink or release database
> transaction log used disk space afer the database backup has completed?
> Thank You,
Showing posts with label log. Show all posts
Showing posts with label log. Show all posts
Friday, March 30, 2012
Release Database Transaction Log Used Space After Database Backup
What are the SQL Server commands that will shrink or release database
transaction log used disk space afer the database backup has completed?
Thank You,These two things are not related. A database backup will not empty the log. A log backup will,
however. To then shrink the log file, use DBCC SHRINKFILE. But read
http://www.karaszi.com/SQLServer/info_dont_shrink.asp first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:85D71BCF-F22C-4035-8A02-16771C713B58@.microsoft.com...
> What are the SQL Server commands that will shrink or release database
> transaction log used disk space afer the database backup has completed?
> Thank You,
transaction log used disk space afer the database backup has completed?
Thank You,These two things are not related. A database backup will not empty the log. A log backup will,
however. To then shrink the log file, use DBCC SHRINKFILE. But read
http://www.karaszi.com/SQLServer/info_dont_shrink.asp first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:85D71BCF-F22C-4035-8A02-16771C713B58@.microsoft.com...
> What are the SQL Server commands that will shrink or release database
> transaction log used disk space afer the database backup has completed?
> Thank You,
Monday, March 12, 2012
Re-indexing required?
Hi, I'm developing a database driven application that, besides everything
else, it keeps a log file of all the actions a user has taken during his use
of the app.
This log file is stored in a database table that has a primary key of type
"bigint" that auto increments (1, 1).
If ~100 to ~500 actions (insertions, deletions) are made to this table per
day, how long before I need to re-index the table? Do I need to re-index it
at all?
Thanks in advance,
Peter
pnp,
When are your maintenance windows? Do you have ANY maintenance windows? If you get a chance it would be good to recreate your indexes using the CREATE INDEX statement and the DROP_EXISTING clause - however test this for performance against DBCC DBREINDEX.
Remember that these are OFFLINE operations and will lock tables.
If you don't have a maintenance window, then measure your defragmentation using DBCC SHOWCONTIG. Based on a value acceptable to you, you can rebuild your index with DBCC INDEXDEFRAG - this is an ONLINE operation and will not lock tables, however it is not
as thorough as the other methods.
My advice would be to run DBCC SHOWCONTIG first before doing a rebuild, and then decide when to do it based on your maintenance windows. From the activity you describe it sounds like you may need to monitor it daily with DBCC SHOWCONTIG.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Hi,
Execute the below command with in the database to identify the
fragmentation,
DBCC SHOWCONTIG ('table_name') WITH FAST
DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
fragmentation occurs through the process of data modifications (INSERT,
UPDATE, and DELETE statements) made against the table. This
will cause additional page reads results in slow performance.
How to over come the Fragmentation:
1. Drop and re-create a clustered index.
2. DBCC INDEXDEFRAG (Refer books online)
Have a look into DBCC SHOWCONTIG in books online for more information.
Thanks
Hari
MCDBA
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
|||On a slighly different thread.
I'd be curious to know how SQL Server indexes deal with incremental keys.
Other RDBMS implemented hash indexes as btrees can become lopsided with
these keys.
Paul Cahill
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eoOuuoJGEHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Execute the below command with in the database to identify the
> fragmentation,
> DBCC SHOWCONTIG ('table_name') WITH FAST
> DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
> fragmentation occurs through the process of data modifications (INSERT,
> UPDATE, and DELETE statements) made against the table. This
> will cause additional page reads results in slow performance.
> How to over come the Fragmentation:
> 1. Drop and re-create a clustered index.
> 2. DBCC INDEXDEFRAG (Refer books online)
> Have a look into DBCC SHOWCONTIG in books online for more information.
> Thanks
> Hari
> MCDBA
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
everything
> use
type
per
> it
>
|||To add to all the other (sound) advice, please checkout the excellent
whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
It gives extensive details on how to diagnose and cope with fragmentation,
including working out which indexes to focus on and even whether you need to
bother, based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
else, it keeps a log file of all the actions a user has taken during his use
of the app.
This log file is stored in a database table that has a primary key of type
"bigint" that auto increments (1, 1).
If ~100 to ~500 actions (insertions, deletions) are made to this table per
day, how long before I need to re-index the table? Do I need to re-index it
at all?
Thanks in advance,
Peter
pnp,
When are your maintenance windows? Do you have ANY maintenance windows? If you get a chance it would be good to recreate your indexes using the CREATE INDEX statement and the DROP_EXISTING clause - however test this for performance against DBCC DBREINDEX.
Remember that these are OFFLINE operations and will lock tables.
If you don't have a maintenance window, then measure your defragmentation using DBCC SHOWCONTIG. Based on a value acceptable to you, you can rebuild your index with DBCC INDEXDEFRAG - this is an ONLINE operation and will not lock tables, however it is not
as thorough as the other methods.
My advice would be to run DBCC SHOWCONTIG first before doing a rebuild, and then decide when to do it based on your maintenance windows. From the activity you describe it sounds like you may need to monitor it daily with DBCC SHOWCONTIG.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Hi,
Execute the below command with in the database to identify the
fragmentation,
DBCC SHOWCONTIG ('table_name') WITH FAST
DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
fragmentation occurs through the process of data modifications (INSERT,
UPDATE, and DELETE statements) made against the table. This
will cause additional page reads results in slow performance.
How to over come the Fragmentation:
1. Drop and re-create a clustered index.
2. DBCC INDEXDEFRAG (Refer books online)
Have a look into DBCC SHOWCONTIG in books online for more information.
Thanks
Hari
MCDBA
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
|||On a slighly different thread.
I'd be curious to know how SQL Server indexes deal with incremental keys.
Other RDBMS implemented hash indexes as btrees can become lopsided with
these keys.
Paul Cahill
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eoOuuoJGEHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Execute the below command with in the database to identify the
> fragmentation,
> DBCC SHOWCONTIG ('table_name') WITH FAST
> DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
> fragmentation occurs through the process of data modifications (INSERT,
> UPDATE, and DELETE statements) made against the table. This
> will cause additional page reads results in slow performance.
> How to over come the Fragmentation:
> 1. Drop and re-create a clustered index.
> 2. DBCC INDEXDEFRAG (Refer books online)
> Have a look into DBCC SHOWCONTIG in books online for more information.
> Thanks
> Hari
> MCDBA
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
everything
> use
type
per
> it
>
|||To add to all the other (sound) advice, please checkout the excellent
whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
It gives extensive details on how to diagnose and cope with fragmentation,
including working out which indexes to focus on and even whether you need to
bother, based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
Re-indexing required?
Hi, I'm developing a database driven application that, besides everything
else, it keeps a log file of all the actions a user has taken during his use
of the app.
This log file is stored in a database table that has a primary key of type
"bigint" that auto increments (1, 1).
If ~100 to ~500 actions (insertions, deletions) are made to this table per
day, how long before I need to re-index the table? Do I need to re-index it
at all?
Thanks in advance,
Peterpnp,
When are your maintenance windows? Do you have ANY maintenance windows? If y
ou get a chance it would be good to recreate your indexes using the CREATE I
NDEX statement and the DROP_EXISTING clause - however test this for performa
nce against DBCC DBREINDEX.
Remember that these are OFFLINE operations and will lock tables.
If you don't have a maintenance window, then measure your defragmentation us
ing DBCC SHOWCONTIG. Based on a value acceptable to you, you can rebuild you
r index with DBCC INDEXDEFRAG - this is an ONLINE operation and will not loc
k tables, however it is not
as thorough as the other methods.
My advice would be to run DBCC SHOWCONTIG first before doing a rebuild, and
then decide when to do it based on your maintenance windows. From the activi
ty you describe it sounds like you may need to monitor it daily with DBCC SH
OWCONTIG.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Hi,
Execute the below command with in the database to identify the
fragmentation,
DBCC SHOWCONTIG ('table_name') WITH FAST
DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
fragmentation occurs through the process of data modifications (INSERT,
UPDATE, and DELETE statements) made against the table. This
will cause additional page reads results in slow performance.
How to over come the Fragmentation:
1. Drop and re-create a clustered index.
2. DBCC INDEXDEFRAG (Refer books online)
Have a look into DBCC SHOWCONTIG in books online for more information.
Thanks
Hari
MCDBA
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>|||On a slighly different thread.
I'd be curious to know how SQL Server indexes deal with incremental keys.
Other RDBMS implemented hash indexes as btrees can become lopsided with
these keys.
Paul Cahill
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eoOuuoJGEHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Execute the below command with in the database to identify the
> fragmentation,
> DBCC SHOWCONTIG ('table_name') WITH FAST
> DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
> fragmentation occurs through the process of data modifications (INSERT,
> UPDATE, and DELETE statements) made against the table. This
> will cause additional page reads results in slow performance.
> How to over come the Fragmentation:
> 1. Drop and re-create a clustered index.
> 2. DBCC INDEXDEFRAG (Refer books online)
> Have a look into DBCC SHOWCONTIG in books online for more information.
> Thanks
> Hari
> MCDBA
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
everything
> use
type
per
> it
>|||To add to all the other (sound) advice, please checkout the excellent
whitepaper at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
It gives extensive details on how to diagnose and cope with fragmentation,
including working out which indexes to focus on and even whether you need to
bother, based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
else, it keeps a log file of all the actions a user has taken during his use
of the app.
This log file is stored in a database table that has a primary key of type
"bigint" that auto increments (1, 1).
If ~100 to ~500 actions (insertions, deletions) are made to this table per
day, how long before I need to re-index the table? Do I need to re-index it
at all?
Thanks in advance,
Peterpnp,
When are your maintenance windows? Do you have ANY maintenance windows? If y
ou get a chance it would be good to recreate your indexes using the CREATE I
NDEX statement and the DROP_EXISTING clause - however test this for performa
nce against DBCC DBREINDEX.
Remember that these are OFFLINE operations and will lock tables.
If you don't have a maintenance window, then measure your defragmentation us
ing DBCC SHOWCONTIG. Based on a value acceptable to you, you can rebuild you
r index with DBCC INDEXDEFRAG - this is an ONLINE operation and will not loc
k tables, however it is not
as thorough as the other methods.
My advice would be to run DBCC SHOWCONTIG first before doing a rebuild, and
then decide when to do it based on your maintenance windows. From the activi
ty you describe it sounds like you may need to monitor it daily with DBCC SH
OWCONTIG.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Hi,
Execute the below command with in the database to identify the
fragmentation,
DBCC SHOWCONTIG ('table_name') WITH FAST
DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
fragmentation occurs through the process of data modifications (INSERT,
UPDATE, and DELETE statements) made against the table. This
will cause additional page reads results in slow performance.
How to over come the Fragmentation:
1. Drop and re-create a clustered index.
2. DBCC INDEXDEFRAG (Refer books online)
Have a look into DBCC SHOWCONTIG in books online for more information.
Thanks
Hari
MCDBA
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>|||On a slighly different thread.
I'd be curious to know how SQL Server indexes deal with incremental keys.
Other RDBMS implemented hash indexes as btrees can become lopsided with
these keys.
Paul Cahill
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eoOuuoJGEHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Execute the below command with in the database to identify the
> fragmentation,
> DBCC SHOWCONTIG ('table_name') WITH FAST
> DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
> fragmentation occurs through the process of data modifications (INSERT,
> UPDATE, and DELETE statements) made against the table. This
> will cause additional page reads results in slow performance.
> How to over come the Fragmentation:
> 1. Drop and re-create a clustered index.
> 2. DBCC INDEXDEFRAG (Refer books online)
> Have a look into DBCC SHOWCONTIG in books online for more information.
> Thanks
> Hari
> MCDBA
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
everything
> use
type
per
> it
>|||To add to all the other (sound) advice, please checkout the excellent
whitepaper at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
It gives extensive details on how to diagnose and cope with fragmentation,
including working out which indexes to focus on and even whether you need to
bother, based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
Reindexing log shipped database
Hallo
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tomyou want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tomyou want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>
Reindexing log shipped database
Hallo
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tom
you want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon
|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>
|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ
|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tom
you want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon
|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>
|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ
|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>
Reindexing log shipped database
Hallo
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tomyou want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tomyou want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>
Friday, March 9, 2012
Regular intermittent Kerberos failures
Hi guys,
This is a last desperate call for help. About once a week, for between
2 and 10 minutes, users are unable to log in to our main web
application (ASP based). They get the following message:
'Failed to generate SSPI context'
Looking at the System Log on the web server displays the following
messages for the web site and SQL SPNs:
'The Security System detected an authentication error for the server
HTTP/<website name>. The failure code from authentication protocol
Kerberos was "The time at the Primary Domain Controller is different
than the time at the Backup Domain Controller or member server by too
large an amount.
(0xc0000133)".'
' The Security System detected an authentication error for the server
MSSQLSvc/S05010010.corp.dnsdom.net:1433. The failure code from
authentication protocol Kerberos was "The time at the Primary Domain
Controller is different than the time at the Backup Domain Controller
or member server by too large an amount.
(0xc0000133)".'
I have used net time to check the times on the Domain Controller, web
server and db server. Can't see any problems. Our system guys have
been through the 'Failed to generate SSPI context' knowledge base
articles.
I haven't seen anything referring to this as a regularly repeating
intermittent problem. We are getting worried cos there is always the
chance it won't come back up!
Any help very gratefully received.
Cheers,
JamesHi James
At a guess this could be a network failure, although if there is a pattern
to the times this occur it would point to something which is scheduled such
as AV or IDS software.
To eliminate the time difference being an issue you may want to try
syncronising both servers with an external time source and not rely on the AD.
John
"JimLad" wrote:
> Hi guys,
> This is a last desperate call for help. About once a week, for between
> 2 and 10 minutes, users are unable to log in to our main web
> application (ASP based). They get the following message:
> 'Failed to generate SSPI context'
> Looking at the System Log on the web server displays the following
> messages for the web site and SQL SPNs:
> 'The Security System detected an authentication error for the server
> HTTP/<website name>. The failure code from authentication protocol
> Kerberos was "The time at the Primary Domain Controller is different
> than the time at the Backup Domain Controller or member server by too
> large an amount.
> (0xc0000133)".'
> ' The Security System detected an authentication error for the server
> MSSQLSvc/S05010010.corp.dnsdom.net:1433. The failure code from
> authentication protocol Kerberos was "The time at the Primary Domain
> Controller is different than the time at the Backup Domain Controller
> or member server by too large an amount.
> (0xc0000133)".'
> I have used net time to check the times on the Domain Controller, web
> server and db server. Can't see any problems. Our system guys have
> been through the 'Failed to generate SSPI context' knowledge base
> articles.
> I haven't seen anything referring to this as a regularly repeating
> intermittent problem. We are getting worried cos there is always the
> chance it won't come back up!
> Any help very gratefully received.
> Cheers,
> James
>|||The messages you posted indicate an Active Directory configuration
problem rather than a SQL Server problem.
>From the information you've provided, its impossible to diagnose what
the problem is without knowing the architecture of your active
directory forest -- whether the HTTP server that logs into your SQL
Server is a member of the domain (which it sounds like it is), and
whether it goes thorough a firewall or any proxy servers that maybe
caching old records.
While Active Directory identifies clients connecting to servers,
Kerberos (which is a layer that runs ontop of active directory for
Microsoft platforms) also authenticates a server to the client. If
the servers are farmed, or there are many secondary domain
controllers, kerberos will check that they are all true mirrors of
each other to prevent somebody from setting up an unauthorized
secondary domain controller to spoof your forest (and thereby allow
unauthorized access via bogus active directory account entries on the
spoofed controller).|||On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
> The messages you posted indicate an Active Directory configuration
> problem rather than a SQL Server problem.
> >From the information you've provided, its impossible to diagnose what
> the problem is without knowing the architecture of your active
> directory forest -- whether the HTTP server that logs into your SQL
> Server is a member of the domain (which it sounds like it is), and
> whether it goes thorough a firewall or any proxy servers that maybe
> caching old records.
> While Active Directory identifies clients connecting to servers,
> Kerberos (which is a layer that runs ontop of active directory for
> Microsoft platforms) also authenticates a server to the client. If
> the servers are farmed, or there are many secondary domain
> controllers, kerberos will check that they are all true mirrors of
> each other to prevent somebody from setting up an unauthorized
> secondary domain controller to spoof your forest (and thereby allow
> unauthorized access via bogus active directory account entries on the
> spoofed controller).
Hi Andy,
Thanks for that. The messages indicate a timing problem: given that
Kerberos only requires servers to be within 5 minutes is this a case
of a misleading error message or is it that I am not using net time on
enough domain controllers? I also notice that the Kerberos group
policy "Maximum Tolerance for
Computer Clock Synchronization" is 'Not Defined'. Does this need to
be
defined or will it automatically use the default of 5 minutes?
Would turning on Kerberos event logging help to diagnose this? Would
turning it on on the web server be sufficient or would it need to be
enabled on dcs and db server as well? And would turning it on be a bad
idea on a production system?
More info: there is a single web server and a single db server, based
in London, no proxy or firewall between them. There are 4 domain
controllers in London. All of these are in the same domain. There are
other domain controllers at other locations in the same domain.
Any ideas on how to diagnose the problem would be extremely welcome.
Many thanks.
Cheers,
James|||On Aug 28, 10:01 am, JimLad <jamesdbi...@.yahoo.co.uk> wrote:
> On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
>
>
> > The messages you posted indicate an Active Directory configuration
> > problem rather than a SQL Server problem.
> > >From the information you've provided, its impossible to diagnose what
> > the problem is without knowing the architecture of your active
> > directory forest -- whether the HTTP server that logs into your SQL
> > Server is a member of the domain (which it sounds like it is), and
> > whether it goes thorough a firewall or any proxy servers that maybe
> > caching old records.
> > While Active Directory identifies clients connecting to servers,
> > Kerberos (which is a layer that runs ontop of active directory for
> > Microsoft platforms) also authenticates a server to the client. If
> > the servers are farmed, or there are many secondary domain
> > controllers, kerberos will check that they are all true mirrors of
> > each other to prevent somebody from setting up an unauthorized
> > secondary domain controller to spoof your forest (and thereby allow
> > unauthorized access via bogus active directory account entries on the
> > spoofed controller).
> Hi Andy,
> Thanks for that. The messages indicate a timing problem: given that
> Kerberos only requires servers to be within 5 minutes is this a case
> of a misleading error message or is it that I am not using net time on
> enough domain controllers? I also notice that the Kerberos group
> policy "Maximum Tolerance for
> Computer Clock Synchronization" is 'Not Defined'. Does this need to
> be
> defined or will it automatically use the default of 5 minutes?
> Would turning on Kerberos event logging help to diagnose this? Would
> turning it on on the web server be sufficient or would it need to be
> enabled on dcs and db server as well? And would turning it on be a bad
> idea on a production system?
> More info: there is a single web server and a single db server, based
> in London, no proxy or firewall between them. There are 4 domain
> controllers in London. All of these are in the same domain. There are
> other domain controllers at other locations in the same domain.
> Any ideas on how to diagnose the problem would be extremely welcome.
> Many thanks.
> Cheers,
> James- Hide quoted text -
> - Show quoted text -
Hi,
We turned on Kerberos tracing and in the 16 seconds that it didn't
work this week we got the following messages on the web server:
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:38
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:39.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433
Target Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:47
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:49.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: HTTP/<websitehostheader>
Target Name: HTTP/<websitehostheader>@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
0xB - KDC_ERR_NEVER_VALID: Requested start time is later than end time
Associated internal Windows error codes
=B7None
Corresponding debug output messages
=B7DebugLog("Client asked for endtime before starttime\n")
Possible Cause and Resolution
=B7There is a time difference between the KDC and the client.
Resolution
For Kerberos authentication to work, you must synchronize clocks on
the client and on the server. For more information about this error
and how to resolve it, see Time Synchronization (Clock Skew) earlier
in this white paper.
Any ideas why we would get this error message once a week for a window
of between a few seconds and 10 minutes?
Is there any way of knowing where the KDC is? I assume it's one of the
domain controllers, but as we have several is there a way of knowing
which is being used?
We have also been getting non-fatal Kerberos messages (0x25
KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
and isn't involved in the authentication so I'm not sure why we are
getting this message, even though that server is indeed 6 minutes
fast.
Outside this time window we get lots of the following messages:
0x34 KRB_ERR_RESPONSE_TOO_BIG
0xd KDC_ERR_BADOPTION
0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
0x25 KRB_AP_ERR_SKEW
Cheers,
James|||Hi,
To answer some questions:
KDC runs on all Domain Controllers by default. You need to use a tool like
KerbTray or KList to see where the Kerberos tickets in question are coming
from
> We have also been getting non-fatal Kerberos messages (0x25
> KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
> and isn't involved in the authentication so I'm not sure why we are
> getting this message, even though that server is indeed 6 minutes
> fast.
Well, machines also authenticate to each other.
> 0x34 KRB_ERR_RESPONSE_TOO_BIG
Generally means that the packet was too big to be transmitted and was
fragmented. Should generally be OK, because Kerberos can be sent over TCP
rather than just UDP.
> 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
An SPN is missing from within your Active Directory
> 0x25 KRB_AP_ERR_SKEW
Time is out by more than the permitted deviation.
It looks like you have some time sync issues in your organisation. Are you
using the default Windows time sync heirachy (by default all DCs sync time
with the PDCe FSMO role holder, and all clients sync with their
authenticating DCs), or have you overriden this in some way?
Cheers
Ken
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1188491704.797545.33500@.50g2000hsm.googlegroups.com...
On Aug 28, 10:01 am, JimLad <jamesdbi...@.yahoo.co.uk> wrote:
> On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
>
>
> > The messages you posted indicate an Active Directory configuration
> > problem rather than a SQL Server problem.
> > >From the information you've provided, its impossible to diagnose what
> > the problem is without knowing the architecture of your active
> > directory forest -- whether the HTTP server that logs into your SQL
> > Server is a member of the domain (which it sounds like it is), and
> > whether it goes thorough a firewall or any proxy servers that maybe
> > caching old records.
> > While Active Directory identifies clients connecting to servers,
> > Kerberos (which is a layer that runs ontop of active directory for
> > Microsoft platforms) also authenticates a server to the client. If
> > the servers are farmed, or there are many secondary domain
> > controllers, kerberos will check that they are all true mirrors of
> > each other to prevent somebody from setting up an unauthorized
> > secondary domain controller to spoof your forest (and thereby allow
> > unauthorized access via bogus active directory account entries on the
> > spoofed controller).
> Hi Andy,
> Thanks for that. The messages indicate a timing problem: given that
> Kerberos only requires servers to be within 5 minutes is this a case
> of a misleading error message or is it that I am not using net time on
> enough domain controllers? I also notice that the Kerberos group
> policy "Maximum Tolerance for
> Computer Clock Synchronization" is 'Not Defined'. Does this need to
> be
> defined or will it automatically use the default of 5 minutes?
> Would turning on Kerberos event logging help to diagnose this? Would
> turning it on on the web server be sufficient or would it need to be
> enabled on dcs and db server as well? And would turning it on be a bad
> idea on a production system?
> More info: there is a single web server and a single db server, based
> in London, no proxy or firewall between them. There are 4 domain
> controllers in London. All of these are in the same domain. There are
> other domain controllers at other locations in the same domain.
> Any ideas on how to diagnose the problem would be extremely welcome.
> Many thanks.
> Cheers,
> James- Hide quoted text -
> - Show quoted text -
Hi,
We turned on Kerberos tracing and in the 16 seconds that it didn't
work this week we got the following messages on the web server:
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:38
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:39.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433
Target Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:47
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:49.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: HTTP/<websitehostheader>
Target Name: HTTP/<websitehostheader>@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
0xB - KDC_ERR_NEVER_VALID: Requested start time is later than end time
Associated internal Windows error codes
·None
Corresponding debug output messages
·DebugLog("Client asked for endtime before starttime\n")
Possible Cause and Resolution
·There is a time difference between the KDC and the client.
Resolution
For Kerberos authentication to work, you must synchronize clocks on
the client and on the server. For more information about this error
and how to resolve it, see Time Synchronization (Clock Skew) earlier
in this white paper.
Any ideas why we would get this error message once a week for a window
of between a few seconds and 10 minutes?
Is there any way of knowing where the KDC is? I assume it's one of the
domain controllers, but as we have several is there a way of knowing
which is being used?
We have also been getting non-fatal Kerberos messages (0x25
KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
and isn't involved in the authentication so I'm not sure why we are
getting this message, even though that server is indeed 6 minutes
fast.
Outside this time window we get lots of the following messages:
0x34 KRB_ERR_RESPONSE_TOO_BIG
0xd KDC_ERR_BADOPTION
0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
0x25 KRB_AP_ERR_SKEW
Cheers,
James|||On Sep 4, 6:50 am, "Ken Schaefer" <kenREM...@.THISadOpenStatic.com>
wrote:
> Hi,
> To answer some questions:
> KDC runs on all Domain Controllers by default. You need to use a tool like
> KerbTray or KList to see where the Kerberos tickets in question are coming
> from
> > We have also been getting non-fatal Kerberos messages (0x25
> > KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
> > and isn't involved in the authentication so I'm not sure why we are
> > getting this message, even though that server is indeed 6 minutes
> > fast.
> Well, machines also authenticate to each other.
> > 0x34 KRB_ERR_RESPONSE_TOO_BIG
> Generally means that the packet was too big to be transmitted and was
> fragmented. Should generally be OK, because Kerberos can be sent over TCP
> rather than just UDP.
> > 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
> An SPN is missing from within your Active Directory
> > 0x25 KRB_AP_ERR_SKEW
> Time is out by more than the permitted deviation.
> It looks like you have some time sync issues in your organisation. Are you
> using the default Windows time sync heirachy (by default all DCs sync time
> with the PDCe FSMO role holder, and all clients sync with their
> authenticating DCs), or have you overriden this in some way?
> Cheers
> Ken
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:1188491704.797545.33500@.50g2000hsm.googlegroups.com...
> On Aug 28, 10:01 am, JimLad <jamesdbi...@.yahoo.co.uk> wrote:
>
>
> > On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
> > > The messages you posted indicate an Active Directory configuration
> > > problem rather than a SQL Server problem.
> > > >From the information you've provided, its impossible to diagnose what
> > > the problem is without knowing the architecture of your active
> > > directory forest -- whether the HTTP server that logs into your SQL
> > > Server is a member of the domain (which it sounds like it is), and
> > > whether it goes thorough a firewall or any proxy servers that maybe
> > > caching old records.
> > > While Active Directory identifies clients connecting to servers,
> > > Kerberos (which is a layer that runs ontop of active directory for
> > > Microsoft platforms) also authenticates a server to the client. If
> > > the servers are farmed, or there are many secondary domain
> > > controllers, kerberos will check that they are all true mirrors of
> > > each other to prevent somebody from setting up an unauthorized
> > > secondary domain controller to spoof your forest (and thereby allow
> > > unauthorized access via bogus active directory account entries on the
> > > spoofed controller).
> > Hi Andy,
> > Thanks for that. The messages indicate a timing problem: given that
> > Kerberos only requires servers to be within 5 minutes is this a case
> > of a misleading error message or is it that I am not using net time on
> > enough domain controllers? I also notice that the Kerberos group
> > policy "Maximum Tolerance for
> > Computer Clock Synchronization" is 'Not Defined'. Does this need to
> > be
> > defined or will it automatically use the default of 5 minutes?
> > Would turning on Kerberos event logging help to diagnose this? Would
> > turning it on on the web server be sufficient or would it need to be
> > enabled on dcs and db server as well? And would turning it on be a bad
> > idea on a production system?
> > More info: there is a single web server and a single db server, based
> > in London, no proxy or firewall between them. There are 4 domain
> > controllers in London. All of these are in the same domain. There are
> > other domain controllers at other locations in the same domain.
> > Any ideas on how to diagnose the problem would be extremely welcome.
> > Many thanks.
> > Cheers,
> > James- Hide quoted text -
> > - Show quoted text -
> Hi,
> We turned on Kerberos tracing and in the 16 seconds that it didn't
> work this week we got the following messages on the web server:
> Event Type: Error
> Event Source: Kerberos
> Event Category: None
> Event ID: 3
> Date: 30/08/2007
> Time: 17:01:38
> User: N/A
> Computer: S05010072
> Description:
> A Kerberos Error Message was received:
> on logon session
> Client Time:
> Server Time: 16:1:39.0000 8/30/2007 Z
> Error Code: 0xb KDC_ERR_NEVER_VALID
> Extended Error: 0xc0000133 KLIN(0)
> Client Realm:
> Client Name:
> Server Realm: CORP.DNSDOM.NET
> Server Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433
> Target Name: MSSQLSvc/S05010010.corp.dnsdom.net:1...@.CORP.DNSDOM.NET
> Error Text:
> File: 9
> Line: ae0
> Error Data is in record data.
> Event Type: Error
> Event Source: Kerberos
> Event Category: None
> Event ID: 3
> Date: 30/08/2007
> Time: 17:01:47
> User: N/A
> Computer: S05010072
> Description:
> A Kerberos Error Message was received:
> on logon session
> Client Time:
> Server Time: 16:1:49.0000 8/30/2007 Z
> Error Code: 0xb KDC_ERR_NEVER_VALID
> Extended Error: 0xc0000133 KLIN(0)
> Client Realm:
> Client Name:
> Server Realm: CORP.DNSDOM.NET
> Server Name: HTTP/<websitehostheader>
> Target Name: HTTP/<websitehostheader>@.CORP.DNSDOM.NET
> Error Text:
> File: 9
> Line: ae0
> Error Data is in record data.
> 0xB - KDC_ERR_NEVER_VALID: Requested start time is later than end time
> Associated internal Windows error codes
> =B7None
> Corresponding debug output messages
> =B7DebugLog("Client asked for endtime before starttime\n")
> Possible Cause and Resolution
> =B7There is a time difference between the KDC and the client.
> Resolution
> For Kerberos authentication to work, you must synchronize clocks on
> the client and on the server. For more information about this error
> and how to resolve it, see Time Synchronization (Clock Skew) earlier
> in this white paper.
> Any ideas why we would get this error message once a week for a window
> of between a few seconds and 10 minutes?
> Is there any way of knowing where the KDC is? I assume it's one of the
> domain controllers, but as we have several is there a way of knowing
> which is being used?
> We have also been getting non-fatal Kerberos messages (0x25
> KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
> and isn't involved in the authentication so I'm not sure why we are
> getting this message, even though that server is indeed 6 minutes
> fast.
> Outside this time window we get lots of the following messages:
> 0x34 KRB_ERR_RESPONSE_TOO_BIG
> 0xd KDC_ERR_BADOPTION
> 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
> 0x25 KRB_AP_ERR_SKEW
> Cheers,
> James- Hide quoted text -
> - Show quoted text -
Thanks Ken. Useful stuff.
I've posted a new subject based on a message I found in the security
log on the DC.
http://groups.google.com/group/comp.protocols.kerberos/browse_thread/thread=
/ce62e8b04e3cddad/5af3d0b03cee0927#5af3d0b03cee0927
Cheers,
James
This is a last desperate call for help. About once a week, for between
2 and 10 minutes, users are unable to log in to our main web
application (ASP based). They get the following message:
'Failed to generate SSPI context'
Looking at the System Log on the web server displays the following
messages for the web site and SQL SPNs:
'The Security System detected an authentication error for the server
HTTP/<website name>. The failure code from authentication protocol
Kerberos was "The time at the Primary Domain Controller is different
than the time at the Backup Domain Controller or member server by too
large an amount.
(0xc0000133)".'
' The Security System detected an authentication error for the server
MSSQLSvc/S05010010.corp.dnsdom.net:1433. The failure code from
authentication protocol Kerberos was "The time at the Primary Domain
Controller is different than the time at the Backup Domain Controller
or member server by too large an amount.
(0xc0000133)".'
I have used net time to check the times on the Domain Controller, web
server and db server. Can't see any problems. Our system guys have
been through the 'Failed to generate SSPI context' knowledge base
articles.
I haven't seen anything referring to this as a regularly repeating
intermittent problem. We are getting worried cos there is always the
chance it won't come back up!
Any help very gratefully received.
Cheers,
JamesHi James
At a guess this could be a network failure, although if there is a pattern
to the times this occur it would point to something which is scheduled such
as AV or IDS software.
To eliminate the time difference being an issue you may want to try
syncronising both servers with an external time source and not rely on the AD.
John
"JimLad" wrote:
> Hi guys,
> This is a last desperate call for help. About once a week, for between
> 2 and 10 minutes, users are unable to log in to our main web
> application (ASP based). They get the following message:
> 'Failed to generate SSPI context'
> Looking at the System Log on the web server displays the following
> messages for the web site and SQL SPNs:
> 'The Security System detected an authentication error for the server
> HTTP/<website name>. The failure code from authentication protocol
> Kerberos was "The time at the Primary Domain Controller is different
> than the time at the Backup Domain Controller or member server by too
> large an amount.
> (0xc0000133)".'
> ' The Security System detected an authentication error for the server
> MSSQLSvc/S05010010.corp.dnsdom.net:1433. The failure code from
> authentication protocol Kerberos was "The time at the Primary Domain
> Controller is different than the time at the Backup Domain Controller
> or member server by too large an amount.
> (0xc0000133)".'
> I have used net time to check the times on the Domain Controller, web
> server and db server. Can't see any problems. Our system guys have
> been through the 'Failed to generate SSPI context' knowledge base
> articles.
> I haven't seen anything referring to this as a regularly repeating
> intermittent problem. We are getting worried cos there is always the
> chance it won't come back up!
> Any help very gratefully received.
> Cheers,
> James
>|||The messages you posted indicate an Active Directory configuration
problem rather than a SQL Server problem.
>From the information you've provided, its impossible to diagnose what
the problem is without knowing the architecture of your active
directory forest -- whether the HTTP server that logs into your SQL
Server is a member of the domain (which it sounds like it is), and
whether it goes thorough a firewall or any proxy servers that maybe
caching old records.
While Active Directory identifies clients connecting to servers,
Kerberos (which is a layer that runs ontop of active directory for
Microsoft platforms) also authenticates a server to the client. If
the servers are farmed, or there are many secondary domain
controllers, kerberos will check that they are all true mirrors of
each other to prevent somebody from setting up an unauthorized
secondary domain controller to spoof your forest (and thereby allow
unauthorized access via bogus active directory account entries on the
spoofed controller).|||On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
> The messages you posted indicate an Active Directory configuration
> problem rather than a SQL Server problem.
> >From the information you've provided, its impossible to diagnose what
> the problem is without knowing the architecture of your active
> directory forest -- whether the HTTP server that logs into your SQL
> Server is a member of the domain (which it sounds like it is), and
> whether it goes thorough a firewall or any proxy servers that maybe
> caching old records.
> While Active Directory identifies clients connecting to servers,
> Kerberos (which is a layer that runs ontop of active directory for
> Microsoft platforms) also authenticates a server to the client. If
> the servers are farmed, or there are many secondary domain
> controllers, kerberos will check that they are all true mirrors of
> each other to prevent somebody from setting up an unauthorized
> secondary domain controller to spoof your forest (and thereby allow
> unauthorized access via bogus active directory account entries on the
> spoofed controller).
Hi Andy,
Thanks for that. The messages indicate a timing problem: given that
Kerberos only requires servers to be within 5 minutes is this a case
of a misleading error message or is it that I am not using net time on
enough domain controllers? I also notice that the Kerberos group
policy "Maximum Tolerance for
Computer Clock Synchronization" is 'Not Defined'. Does this need to
be
defined or will it automatically use the default of 5 minutes?
Would turning on Kerberos event logging help to diagnose this? Would
turning it on on the web server be sufficient or would it need to be
enabled on dcs and db server as well? And would turning it on be a bad
idea on a production system?
More info: there is a single web server and a single db server, based
in London, no proxy or firewall between them. There are 4 domain
controllers in London. All of these are in the same domain. There are
other domain controllers at other locations in the same domain.
Any ideas on how to diagnose the problem would be extremely welcome.
Many thanks.
Cheers,
James|||On Aug 28, 10:01 am, JimLad <jamesdbi...@.yahoo.co.uk> wrote:
> On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
>
>
> > The messages you posted indicate an Active Directory configuration
> > problem rather than a SQL Server problem.
> > >From the information you've provided, its impossible to diagnose what
> > the problem is without knowing the architecture of your active
> > directory forest -- whether the HTTP server that logs into your SQL
> > Server is a member of the domain (which it sounds like it is), and
> > whether it goes thorough a firewall or any proxy servers that maybe
> > caching old records.
> > While Active Directory identifies clients connecting to servers,
> > Kerberos (which is a layer that runs ontop of active directory for
> > Microsoft platforms) also authenticates a server to the client. If
> > the servers are farmed, or there are many secondary domain
> > controllers, kerberos will check that they are all true mirrors of
> > each other to prevent somebody from setting up an unauthorized
> > secondary domain controller to spoof your forest (and thereby allow
> > unauthorized access via bogus active directory account entries on the
> > spoofed controller).
> Hi Andy,
> Thanks for that. The messages indicate a timing problem: given that
> Kerberos only requires servers to be within 5 minutes is this a case
> of a misleading error message or is it that I am not using net time on
> enough domain controllers? I also notice that the Kerberos group
> policy "Maximum Tolerance for
> Computer Clock Synchronization" is 'Not Defined'. Does this need to
> be
> defined or will it automatically use the default of 5 minutes?
> Would turning on Kerberos event logging help to diagnose this? Would
> turning it on on the web server be sufficient or would it need to be
> enabled on dcs and db server as well? And would turning it on be a bad
> idea on a production system?
> More info: there is a single web server and a single db server, based
> in London, no proxy or firewall between them. There are 4 domain
> controllers in London. All of these are in the same domain. There are
> other domain controllers at other locations in the same domain.
> Any ideas on how to diagnose the problem would be extremely welcome.
> Many thanks.
> Cheers,
> James- Hide quoted text -
> - Show quoted text -
Hi,
We turned on Kerberos tracing and in the 16 seconds that it didn't
work this week we got the following messages on the web server:
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:38
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:39.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433
Target Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:47
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:49.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: HTTP/<websitehostheader>
Target Name: HTTP/<websitehostheader>@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
0xB - KDC_ERR_NEVER_VALID: Requested start time is later than end time
Associated internal Windows error codes
=B7None
Corresponding debug output messages
=B7DebugLog("Client asked for endtime before starttime\n")
Possible Cause and Resolution
=B7There is a time difference between the KDC and the client.
Resolution
For Kerberos authentication to work, you must synchronize clocks on
the client and on the server. For more information about this error
and how to resolve it, see Time Synchronization (Clock Skew) earlier
in this white paper.
Any ideas why we would get this error message once a week for a window
of between a few seconds and 10 minutes?
Is there any way of knowing where the KDC is? I assume it's one of the
domain controllers, but as we have several is there a way of knowing
which is being used?
We have also been getting non-fatal Kerberos messages (0x25
KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
and isn't involved in the authentication so I'm not sure why we are
getting this message, even though that server is indeed 6 minutes
fast.
Outside this time window we get lots of the following messages:
0x34 KRB_ERR_RESPONSE_TOO_BIG
0xd KDC_ERR_BADOPTION
0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
0x25 KRB_AP_ERR_SKEW
Cheers,
James|||Hi,
To answer some questions:
KDC runs on all Domain Controllers by default. You need to use a tool like
KerbTray or KList to see where the Kerberos tickets in question are coming
from
> We have also been getting non-fatal Kerberos messages (0x25
> KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
> and isn't involved in the authentication so I'm not sure why we are
> getting this message, even though that server is indeed 6 minutes
> fast.
Well, machines also authenticate to each other.
> 0x34 KRB_ERR_RESPONSE_TOO_BIG
Generally means that the packet was too big to be transmitted and was
fragmented. Should generally be OK, because Kerberos can be sent over TCP
rather than just UDP.
> 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
An SPN is missing from within your Active Directory
> 0x25 KRB_AP_ERR_SKEW
Time is out by more than the permitted deviation.
It looks like you have some time sync issues in your organisation. Are you
using the default Windows time sync heirachy (by default all DCs sync time
with the PDCe FSMO role holder, and all clients sync with their
authenticating DCs), or have you overriden this in some way?
Cheers
Ken
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1188491704.797545.33500@.50g2000hsm.googlegroups.com...
On Aug 28, 10:01 am, JimLad <jamesdbi...@.yahoo.co.uk> wrote:
> On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
>
>
> > The messages you posted indicate an Active Directory configuration
> > problem rather than a SQL Server problem.
> > >From the information you've provided, its impossible to diagnose what
> > the problem is without knowing the architecture of your active
> > directory forest -- whether the HTTP server that logs into your SQL
> > Server is a member of the domain (which it sounds like it is), and
> > whether it goes thorough a firewall or any proxy servers that maybe
> > caching old records.
> > While Active Directory identifies clients connecting to servers,
> > Kerberos (which is a layer that runs ontop of active directory for
> > Microsoft platforms) also authenticates a server to the client. If
> > the servers are farmed, or there are many secondary domain
> > controllers, kerberos will check that they are all true mirrors of
> > each other to prevent somebody from setting up an unauthorized
> > secondary domain controller to spoof your forest (and thereby allow
> > unauthorized access via bogus active directory account entries on the
> > spoofed controller).
> Hi Andy,
> Thanks for that. The messages indicate a timing problem: given that
> Kerberos only requires servers to be within 5 minutes is this a case
> of a misleading error message or is it that I am not using net time on
> enough domain controllers? I also notice that the Kerberos group
> policy "Maximum Tolerance for
> Computer Clock Synchronization" is 'Not Defined'. Does this need to
> be
> defined or will it automatically use the default of 5 minutes?
> Would turning on Kerberos event logging help to diagnose this? Would
> turning it on on the web server be sufficient or would it need to be
> enabled on dcs and db server as well? And would turning it on be a bad
> idea on a production system?
> More info: there is a single web server and a single db server, based
> in London, no proxy or firewall between them. There are 4 domain
> controllers in London. All of these are in the same domain. There are
> other domain controllers at other locations in the same domain.
> Any ideas on how to diagnose the problem would be extremely welcome.
> Many thanks.
> Cheers,
> James- Hide quoted text -
> - Show quoted text -
Hi,
We turned on Kerberos tracing and in the 16 seconds that it didn't
work this week we got the following messages on the web server:
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:38
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:39.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433
Target Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:47
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:49.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: HTTP/<websitehostheader>
Target Name: HTTP/<websitehostheader>@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
0xB - KDC_ERR_NEVER_VALID: Requested start time is later than end time
Associated internal Windows error codes
·None
Corresponding debug output messages
·DebugLog("Client asked for endtime before starttime\n")
Possible Cause and Resolution
·There is a time difference between the KDC and the client.
Resolution
For Kerberos authentication to work, you must synchronize clocks on
the client and on the server. For more information about this error
and how to resolve it, see Time Synchronization (Clock Skew) earlier
in this white paper.
Any ideas why we would get this error message once a week for a window
of between a few seconds and 10 minutes?
Is there any way of knowing where the KDC is? I assume it's one of the
domain controllers, but as we have several is there a way of knowing
which is being used?
We have also been getting non-fatal Kerberos messages (0x25
KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
and isn't involved in the authentication so I'm not sure why we are
getting this message, even though that server is indeed 6 minutes
fast.
Outside this time window we get lots of the following messages:
0x34 KRB_ERR_RESPONSE_TOO_BIG
0xd KDC_ERR_BADOPTION
0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
0x25 KRB_AP_ERR_SKEW
Cheers,
James|||On Sep 4, 6:50 am, "Ken Schaefer" <kenREM...@.THISadOpenStatic.com>
wrote:
> Hi,
> To answer some questions:
> KDC runs on all Domain Controllers by default. You need to use a tool like
> KerbTray or KList to see where the Kerberos tickets in question are coming
> from
> > We have also been getting non-fatal Kerberos messages (0x25
> > KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
> > and isn't involved in the authentication so I'm not sure why we are
> > getting this message, even though that server is indeed 6 minutes
> > fast.
> Well, machines also authenticate to each other.
> > 0x34 KRB_ERR_RESPONSE_TOO_BIG
> Generally means that the packet was too big to be transmitted and was
> fragmented. Should generally be OK, because Kerberos can be sent over TCP
> rather than just UDP.
> > 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
> An SPN is missing from within your Active Directory
> > 0x25 KRB_AP_ERR_SKEW
> Time is out by more than the permitted deviation.
> It looks like you have some time sync issues in your organisation. Are you
> using the default Windows time sync heirachy (by default all DCs sync time
> with the PDCe FSMO role holder, and all clients sync with their
> authenticating DCs), or have you overriden this in some way?
> Cheers
> Ken
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:1188491704.797545.33500@.50g2000hsm.googlegroups.com...
> On Aug 28, 10:01 am, JimLad <jamesdbi...@.yahoo.co.uk> wrote:
>
>
> > On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
> > > The messages you posted indicate an Active Directory configuration
> > > problem rather than a SQL Server problem.
> > > >From the information you've provided, its impossible to diagnose what
> > > the problem is without knowing the architecture of your active
> > > directory forest -- whether the HTTP server that logs into your SQL
> > > Server is a member of the domain (which it sounds like it is), and
> > > whether it goes thorough a firewall or any proxy servers that maybe
> > > caching old records.
> > > While Active Directory identifies clients connecting to servers,
> > > Kerberos (which is a layer that runs ontop of active directory for
> > > Microsoft platforms) also authenticates a server to the client. If
> > > the servers are farmed, or there are many secondary domain
> > > controllers, kerberos will check that they are all true mirrors of
> > > each other to prevent somebody from setting up an unauthorized
> > > secondary domain controller to spoof your forest (and thereby allow
> > > unauthorized access via bogus active directory account entries on the
> > > spoofed controller).
> > Hi Andy,
> > Thanks for that. The messages indicate a timing problem: given that
> > Kerberos only requires servers to be within 5 minutes is this a case
> > of a misleading error message or is it that I am not using net time on
> > enough domain controllers? I also notice that the Kerberos group
> > policy "Maximum Tolerance for
> > Computer Clock Synchronization" is 'Not Defined'. Does this need to
> > be
> > defined or will it automatically use the default of 5 minutes?
> > Would turning on Kerberos event logging help to diagnose this? Would
> > turning it on on the web server be sufficient or would it need to be
> > enabled on dcs and db server as well? And would turning it on be a bad
> > idea on a production system?
> > More info: there is a single web server and a single db server, based
> > in London, no proxy or firewall between them. There are 4 domain
> > controllers in London. All of these are in the same domain. There are
> > other domain controllers at other locations in the same domain.
> > Any ideas on how to diagnose the problem would be extremely welcome.
> > Many thanks.
> > Cheers,
> > James- Hide quoted text -
> > - Show quoted text -
> Hi,
> We turned on Kerberos tracing and in the 16 seconds that it didn't
> work this week we got the following messages on the web server:
> Event Type: Error
> Event Source: Kerberos
> Event Category: None
> Event ID: 3
> Date: 30/08/2007
> Time: 17:01:38
> User: N/A
> Computer: S05010072
> Description:
> A Kerberos Error Message was received:
> on logon session
> Client Time:
> Server Time: 16:1:39.0000 8/30/2007 Z
> Error Code: 0xb KDC_ERR_NEVER_VALID
> Extended Error: 0xc0000133 KLIN(0)
> Client Realm:
> Client Name:
> Server Realm: CORP.DNSDOM.NET
> Server Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433
> Target Name: MSSQLSvc/S05010010.corp.dnsdom.net:1...@.CORP.DNSDOM.NET
> Error Text:
> File: 9
> Line: ae0
> Error Data is in record data.
> Event Type: Error
> Event Source: Kerberos
> Event Category: None
> Event ID: 3
> Date: 30/08/2007
> Time: 17:01:47
> User: N/A
> Computer: S05010072
> Description:
> A Kerberos Error Message was received:
> on logon session
> Client Time:
> Server Time: 16:1:49.0000 8/30/2007 Z
> Error Code: 0xb KDC_ERR_NEVER_VALID
> Extended Error: 0xc0000133 KLIN(0)
> Client Realm:
> Client Name:
> Server Realm: CORP.DNSDOM.NET
> Server Name: HTTP/<websitehostheader>
> Target Name: HTTP/<websitehostheader>@.CORP.DNSDOM.NET
> Error Text:
> File: 9
> Line: ae0
> Error Data is in record data.
> 0xB - KDC_ERR_NEVER_VALID: Requested start time is later than end time
> Associated internal Windows error codes
> =B7None
> Corresponding debug output messages
> =B7DebugLog("Client asked for endtime before starttime\n")
> Possible Cause and Resolution
> =B7There is a time difference between the KDC and the client.
> Resolution
> For Kerberos authentication to work, you must synchronize clocks on
> the client and on the server. For more information about this error
> and how to resolve it, see Time Synchronization (Clock Skew) earlier
> in this white paper.
> Any ideas why we would get this error message once a week for a window
> of between a few seconds and 10 minutes?
> Is there any way of knowing where the KDC is? I assume it's one of the
> domain controllers, but as we have several is there a way of knowing
> which is being used?
> We have also been getting non-fatal Kerberos messages (0x25
> KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
> and isn't involved in the authentication so I'm not sure why we are
> getting this message, even though that server is indeed 6 minutes
> fast.
> Outside this time window we get lots of the following messages:
> 0x34 KRB_ERR_RESPONSE_TOO_BIG
> 0xd KDC_ERR_BADOPTION
> 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
> 0x25 KRB_AP_ERR_SKEW
> Cheers,
> James- Hide quoted text -
> - Show quoted text -
Thanks Ken. Useful stuff.
I've posted a new subject based on a message I found in the security
log on the DC.
http://groups.google.com/group/comp.protocols.kerberos/browse_thread/thread=
/ce62e8b04e3cddad/5af3d0b03cee0927#5af3d0b03cee0927
Cheers,
James
Wednesday, March 7, 2012
Regular Backup co-exist with log-shipping (or SQL mirroring)
Suppose I have a 3rd party SQL backup software to backup my databases, can I also setup a stand-by server for log shipping?
I'm not sure if this is possible? If not, is SQL mirroring a choice instead of log shipping?
Thanks a lot!
The only restriction is that only one thread of log backups can exist.
So, if your 3rd-party backup software is also doing log backups, log shipping will not work.
Alternatively, you can do full backups with your 3rd-party software and log shipping will work just fine.
Mirroring has no such restrictions.
Regular Backup co-exist with log-shipping (or SQL mirroring)
Suppose I have a 3rd party SQL backup software to backup my databases, can I also setup a stand-by server for log shipping?
I'm not sure if this is possible? If not, is SQL mirroring a choice instead of log shipping?
Thanks a lot!
The only restriction is that only one thread of log backups can exist.
So, if your 3rd-party backup software is also doing log backups, log shipping will not work.
Alternatively, you can do full backups with your 3rd-party software and log shipping will work just fine.
Mirroring has no such restrictions.
Subscribe to:
Posts (Atom)