would DBCC shrinkfile cause any blocking or any hit to an OLTP environment
while its running. Ive got a lot of extra space on some data files that I
want to shrink and was wondering if its safe to do it during our peak
hours... What does it do internally ? Any locking ,etc..Using SQL 2000Yes. It issues a lot of IO and takes short term X page locks. In internal
tests we've seen up to 20% drop in transaction throughput, depending on the
exact workload and hardware configuration. This is unavoidable due to the
operations shrink has to perform.
What proportion of the database size is free-space? Consider not doing the
shrink unless you're really desperate for the disk space or you *know* the
database size won't grow again. If you shrink, the odds are that the
database will have to grow again anyway. As always, depends on your exact
workload etc etc
It is always 'safe' to do a shrink.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uBcDfwnFEHA.2876@.TK2MSFTNGP09.phx.gbl...
> would DBCC shrinkfile cause any blocking or any hit to an OLTP environment
> while its running. Ive got a lot of extra space on some data files that I
> want to shrink and was wondering if its safe to do it during our peak
> hours... What does it do internally ? Any locking ,etc..Using SQL 2000
>
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
DBCC SHOWFILESTATS
Hello,
I′m testing the execution of my database backups with omniback. These
backups fails so i've made a trace and i get the following error: "Error:
7983, Severity: 14, State: 14"
"dbcc showfilestats( 1 )"
The user used to access the databases is a domain admin but in sql server i
only give him the backup operator database role.
If this user were sysadmin everything goes fine but i want to limit his
access.
Do you have any idea about this?
What does the documentation for the backup vendor say? Does it say that the login need to be
sysadmin. If not, you have a bug in their program. If it does, well...
You cannot grant permissions on this. Also, this is an undocumented command, so the backup vendor is
using it at their own risk.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
> Hello,
> Im testing the execution of my database backups with omniback. These
> backups fails so i've made a trace and i get the following error: "Error:
> 7983, Severity: 14, State: 14"
> "dbcc showfilestats( 1 )"
> The user used to access the databases is a domain admin but in sql server i
> only give him the backup operator database role.
> If this user were sysadmin everything goes fine but i want to limit his
> access.
> Do you have any idea about this?
>
|||Moreover, the SQL Backup alternatives typically want to use the Virtual
Backup Device Interface API. At this time, only system admins can make calls
with this interface. Backup Operators do not.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> What does the documentation for the backup vendor say? Does it say that the login need to be
> sysadmin. If not, you have a bug in their program. If it does, well...
> You cannot grant permissions on this. Also, this is an undocumented command, so the backup vendor is
> using it at their own risk.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
> news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
>
>
|||That undocumented DBCC command is SA-only - there's no way around this.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:354E62F2-2E58-4D0A-BE1F-E1AA3D8262BE@.microsoft.com...
> Moreover, the SQL Backup alternatives typically want to use the Virtual
> Backup Device Interface API. At this time, only system admins can make
calls[vbcol=seagreen]
> with this interface. Backup Operators do not.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
the login need to be[vbcol=seagreen]
command, so the backup vendor is[vbcol=seagreen]
"Error:[vbcol=seagreen]
server i[vbcol=seagreen]
his[vbcol=seagreen]
I′m testing the execution of my database backups with omniback. These
backups fails so i've made a trace and i get the following error: "Error:
7983, Severity: 14, State: 14"
"dbcc showfilestats( 1 )"
The user used to access the databases is a domain admin but in sql server i
only give him the backup operator database role.
If this user were sysadmin everything goes fine but i want to limit his
access.
Do you have any idea about this?
What does the documentation for the backup vendor say? Does it say that the login need to be
sysadmin. If not, you have a bug in their program. If it does, well...
You cannot grant permissions on this. Also, this is an undocumented command, so the backup vendor is
using it at their own risk.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
> Hello,
> Im testing the execution of my database backups with omniback. These
> backups fails so i've made a trace and i get the following error: "Error:
> 7983, Severity: 14, State: 14"
> "dbcc showfilestats( 1 )"
> The user used to access the databases is a domain admin but in sql server i
> only give him the backup operator database role.
> If this user were sysadmin everything goes fine but i want to limit his
> access.
> Do you have any idea about this?
>
|||Moreover, the SQL Backup alternatives typically want to use the Virtual
Backup Device Interface API. At this time, only system admins can make calls
with this interface. Backup Operators do not.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> What does the documentation for the backup vendor say? Does it say that the login need to be
> sysadmin. If not, you have a bug in their program. If it does, well...
> You cannot grant permissions on this. Also, this is an undocumented command, so the backup vendor is
> using it at their own risk.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
> news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
>
>
|||That undocumented DBCC command is SA-only - there's no way around this.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:354E62F2-2E58-4D0A-BE1F-E1AA3D8262BE@.microsoft.com...
> Moreover, the SQL Backup alternatives typically want to use the Virtual
> Backup Device Interface API. At this time, only system admins can make
calls[vbcol=seagreen]
> with this interface. Backup Operators do not.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
the login need to be[vbcol=seagreen]
command, so the backup vendor is[vbcol=seagreen]
"Error:[vbcol=seagreen]
server i[vbcol=seagreen]
his[vbcol=seagreen]
Thursday, March 22, 2012
DBCC SHOWCONTIG
I've been running above procedure using the "with all_indexes, fast;" option
on a table in my database. This table has 18 indexes. The results in this
procedure refer to index 1, 2, ..., 18. I don't relate to ID number as my
indexes all have english names (e.g. Account, LastName, etc.). I'm not sure
which index ID is referring to what named index. Are these ID numbers simply
the order in which they appear when I see the list in Enterprise Manager,
Manage Indexes option? Is there a way to have the DBCC SHOWCONTIG procedure
reflect the actual index names I had assigned?
Thanks, Jim
Hi Jim
What version are you using? In SQL 2005 there is a replacement for DBCC
SHOWCONTIG that allows you to filter the results and add more information to
the output. The output is much more readable, too. DBCC is not a procedure,
so there is very little you can do to modify how it works.
You cannot assume the index numbers are the same as the order the indexes
come back in the EM list. You can run this query to see what number goes
with what index: (substitute your own table name, of course)
SELECT indid, name
FROM sysindexes
WHERE id = object_id('your-table-name')
AND indexproperty(id,name, 'isStatistics') = 0
AND indexproperty(id,name, 'isHypothetical') = 0
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim B" <JB@.lightning.com> wrote in message
news:1BE95CD5-1977-47C9-8778-57DBA4A9F183@.microsoft.com...
> I've been running above procedure using the "with all_indexes, fast;"
> option
> on a table in my database. This table has 18 indexes. The results in
> this
> procedure refer to index 1, 2, ..., 18. I don't relate to ID number as my
> indexes all have english names (e.g. Account, LastName, etc.). I'm not
> sure
> which index ID is referring to what named index. Are these ID numbers
> simply
> the order in which they appear when I see the list in Enterprise Manager,
> Manage Indexes option? Is there a way to have the DBCC SHOWCONTIG
> procedure
> reflect the actual index names I had assigned?
> --
> Thanks, Jim
|||Kalen. Thanks for the quick reply. I have to to support sites that use
either SQL2000 or SQL2005. What would be the better option to DBCC
SHOWCONTIG that you mentioned for SQL2005. The query you gave me worked
great in both 2000 and 2005. Thanks again for help.
Thanks, Jim
"Kalen Delaney" wrote:
> Hi Jim
> What version are you using? In SQL 2005 there is a replacement for DBCC
> SHOWCONTIG that allows you to filter the results and add more information to
> the output. The output is much more readable, too. DBCC is not a procedure,
> so there is very little you can do to modify how it works.
> You cannot assume the index numbers are the same as the order the indexes
> come back in the EM list. You can run this query to see what number goes
> with what index: (substitute your own table name, of course)
> SELECT indid, name
> FROM sysindexes
> WHERE id = object_id('your-table-name')
> AND indexproperty(id,name, 'isStatistics') = 0
> AND indexproperty(id,name, 'isHypothetical') = 0
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Jim B" <JB@.lightning.com> wrote in message
> news:1BE95CD5-1977-47C9-8778-57DBA4A9F183@.microsoft.com...
>
>
|||Jim
In SQL 2005 there is a table valued function called
sys.dm_db_index_physical_stats. It returns a LOT of information, but you can
filter it in any way you like, and since it returns table results, you can
join it to the sys.indexes to get the index name, and you can restrict the
columns coming back to just what you need.
You should read about it in BOL first, and if you have a SQL Server Magazine
subscription, you can find a couple of articles I wrote about it on their
site.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim B" <JB@.lightning.com> wrote in message
news:1E20DDE1-6435-4ED1-A4F4-E7402C3B05B2@.microsoft.com...[vbcol=seagreen]
> Kalen. Thanks for the quick reply. I have to to support sites that use
> either SQL2000 or SQL2005. What would be the better option to DBCC
> SHOWCONTIG that you mentioned for SQL2005. The query you gave me worked
> great in both 2000 and 2005. Thanks again for help.
> --
> Thanks, Jim
>
> "Kalen Delaney" wrote:
on a table in my database. This table has 18 indexes. The results in this
procedure refer to index 1, 2, ..., 18. I don't relate to ID number as my
indexes all have english names (e.g. Account, LastName, etc.). I'm not sure
which index ID is referring to what named index. Are these ID numbers simply
the order in which they appear when I see the list in Enterprise Manager,
Manage Indexes option? Is there a way to have the DBCC SHOWCONTIG procedure
reflect the actual index names I had assigned?
Thanks, Jim
Hi Jim
What version are you using? In SQL 2005 there is a replacement for DBCC
SHOWCONTIG that allows you to filter the results and add more information to
the output. The output is much more readable, too. DBCC is not a procedure,
so there is very little you can do to modify how it works.
You cannot assume the index numbers are the same as the order the indexes
come back in the EM list. You can run this query to see what number goes
with what index: (substitute your own table name, of course)
SELECT indid, name
FROM sysindexes
WHERE id = object_id('your-table-name')
AND indexproperty(id,name, 'isStatistics') = 0
AND indexproperty(id,name, 'isHypothetical') = 0
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim B" <JB@.lightning.com> wrote in message
news:1BE95CD5-1977-47C9-8778-57DBA4A9F183@.microsoft.com...
> I've been running above procedure using the "with all_indexes, fast;"
> option
> on a table in my database. This table has 18 indexes. The results in
> this
> procedure refer to index 1, 2, ..., 18. I don't relate to ID number as my
> indexes all have english names (e.g. Account, LastName, etc.). I'm not
> sure
> which index ID is referring to what named index. Are these ID numbers
> simply
> the order in which they appear when I see the list in Enterprise Manager,
> Manage Indexes option? Is there a way to have the DBCC SHOWCONTIG
> procedure
> reflect the actual index names I had assigned?
> --
> Thanks, Jim
|||Kalen. Thanks for the quick reply. I have to to support sites that use
either SQL2000 or SQL2005. What would be the better option to DBCC
SHOWCONTIG that you mentioned for SQL2005. The query you gave me worked
great in both 2000 and 2005. Thanks again for help.
Thanks, Jim
"Kalen Delaney" wrote:
> Hi Jim
> What version are you using? In SQL 2005 there is a replacement for DBCC
> SHOWCONTIG that allows you to filter the results and add more information to
> the output. The output is much more readable, too. DBCC is not a procedure,
> so there is very little you can do to modify how it works.
> You cannot assume the index numbers are the same as the order the indexes
> come back in the EM list. You can run this query to see what number goes
> with what index: (substitute your own table name, of course)
> SELECT indid, name
> FROM sysindexes
> WHERE id = object_id('your-table-name')
> AND indexproperty(id,name, 'isStatistics') = 0
> AND indexproperty(id,name, 'isHypothetical') = 0
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Jim B" <JB@.lightning.com> wrote in message
> news:1BE95CD5-1977-47C9-8778-57DBA4A9F183@.microsoft.com...
>
>
|||Jim
In SQL 2005 there is a table valued function called
sys.dm_db_index_physical_stats. It returns a LOT of information, but you can
filter it in any way you like, and since it returns table results, you can
join it to the sys.indexes to get the index name, and you can restrict the
columns coming back to just what you need.
You should read about it in BOL first, and if you have a SQL Server Magazine
subscription, you can find a couple of articles I wrote about it on their
site.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim B" <JB@.lightning.com> wrote in message
news:1E20DDE1-6435-4ED1-A4F4-E7402C3B05B2@.microsoft.com...[vbcol=seagreen]
> Kalen. Thanks for the quick reply. I have to to support sites that use
> either SQL2000 or SQL2005. What would be the better option to DBCC
> SHOWCONTIG that you mentioned for SQL2005. The query you gave me worked
> great in both 2000 and 2005. Thanks again for help.
> --
> Thanks, Jim
>
> "Kalen Delaney" wrote:
dbcc reports different size than shown in database properties
In sql 2k, I've been looking at size and autogrowth, and some issues have
come up.
In my database properties, general tab, I see size 9118mb, space avail
1372mb. However, when I run
dbcc showfilestats
I get an allocated size of 7808mb (TotalExtents*64/1024.00), and free space
of 189mb ((TotalExtents-UsedExtents)*24/1024). Why this huge discrepancy?
Perhaps the log is counted in the size, but surely that should not affect
space available.
Jeremy
Try running DBCC UPDATEUSAGE and see if that helps.
Andrew J. Kelly SQL MVP
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:eUDnqux7GHA.1560@.TK2MSFTNGP04.phx.gbl...
> In sql 2k, I've been looking at size and autogrowth, and some issues have
> come up.
> In my database properties, general tab, I see size 9118mb, space avail
> 1372mb. However, when I run
> dbcc showfilestats
> I get an allocated size of 7808mb (TotalExtents*64/1024.00), and free
> space of 189mb ((TotalExtents-UsedExtents)*24/1024). Why this huge
> discrepancy? Perhaps the log is counted in the size, but surely that
> should not affect space available.
> Jeremy
>
come up.
In my database properties, general tab, I see size 9118mb, space avail
1372mb. However, when I run
dbcc showfilestats
I get an allocated size of 7808mb (TotalExtents*64/1024.00), and free space
of 189mb ((TotalExtents-UsedExtents)*24/1024). Why this huge discrepancy?
Perhaps the log is counted in the size, but surely that should not affect
space available.
Jeremy
Try running DBCC UPDATEUSAGE and see if that helps.
Andrew J. Kelly SQL MVP
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:eUDnqux7GHA.1560@.TK2MSFTNGP04.phx.gbl...
> In sql 2k, I've been looking at size and autogrowth, and some issues have
> come up.
> In my database properties, general tab, I see size 9118mb, space avail
> 1372mb. However, when I run
> dbcc showfilestats
> I get an allocated size of 7808mb (TotalExtents*64/1024.00), and free
> space of 189mb ((TotalExtents-UsedExtents)*24/1024). Why this huge
> discrepancy? Perhaps the log is counted in the size, but surely that
> should not affect space available.
> Jeremy
>
Monday, March 19, 2012
DBCC loginfo
I've been using this useful undocumented DBCC command
recently but the only information I can find on the Status
column it returns, is that above 0 means that the virtual
log is in use.
Now all the VLF's I examined had a status of 2 and these
we're not uncommitted or undistrubted, just not backed
up. These returned to a status of 0 when backed up.
So, does anyone know what the values in the Status field
mean? How is say 1 different to 2? What other values can
it take?
Graham Davies
DBA
I think a value of 1 is the VL that SQL is currently writing to, 0 is the
inactive part of the log and 2 is the active part of the log... Only the
inactive part of the log can be shrunk away...
BTW, this is undocumented and these are just my observations ( which may be
incorrect.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Graham Davies" <anonymous@.discussions.microsoft.com> wrote in message
news:186401c43f48$a96c9fc0$3a01280a@.phx.gbl...
> I've been using this useful undocumented DBCC command
> recently but the only information I can find on the Status
> column it returns, is that above 0 means that the virtual
> log is in use.
> Now all the VLF's I examined had a status of 2 and these
> we're not uncommitted or undistrubted, just not backed
> up. These returned to a status of 0 when backed up.
> So, does anyone know what the values in the Status field
> mean? How is say 1 different to 2? What other values can
> it take?
> Graham Davies
> DBA
>
recently but the only information I can find on the Status
column it returns, is that above 0 means that the virtual
log is in use.
Now all the VLF's I examined had a status of 2 and these
we're not uncommitted or undistrubted, just not backed
up. These returned to a status of 0 when backed up.
So, does anyone know what the values in the Status field
mean? How is say 1 different to 2? What other values can
it take?
Graham Davies
DBA
I think a value of 1 is the VL that SQL is currently writing to, 0 is the
inactive part of the log and 2 is the active part of the log... Only the
inactive part of the log can be shrunk away...
BTW, this is undocumented and these are just my observations ( which may be
incorrect.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Graham Davies" <anonymous@.discussions.microsoft.com> wrote in message
news:186401c43f48$a96c9fc0$3a01280a@.phx.gbl...
> I've been using this useful undocumented DBCC command
> recently but the only information I can find on the Status
> column it returns, is that above 0 means that the virtual
> log is in use.
> Now all the VLF's I examined had a status of 2 and these
> we're not uncommitted or undistrubted, just not backed
> up. These returned to a status of 0 when backed up.
> So, does anyone know what the values in the Status field
> mean? How is say 1 different to 2? What other values can
> it take?
> Graham Davies
> DBA
>
Sunday, March 11, 2012
dbcc inputbuffer and long commands
We have some commands here that go over 2k characters, and I've seen
some elsewhere with text values that went over 64k, but dbcc
inputbuffer only seems to return 256 characters. Is there any way to
fetch longer commands?
Thanks.
Josh
On Tue, 07 Dec 2004 10:28:45 -0800, jxstern <jxstern@.nowhere.com>
wrote:
>We have some commands here that go over 2k characters, and I've seen
>some elsewhere with text values that went over 64k, but dbcc
>inputbuffer only seems to return 256 characters. Is there any way to
>fetch longer commands?
Aha, found:
http://support.microsoft.com/default...b;en-us;325607
FIX: The fn_get_sql Function Returns SQL Text for Handle in the
Sysprocesses System Table
But it doesn't seem to be very robust - seems not to find the command
in cache all the time.
At least the fn/hotfix is present on a SQL2K sp3a system, fwiw.
J.
|||jxstern wrote:
> We have some commands here that go over 2k characters, and I've seen
> some elsewhere with text values that went over 64k, but dbcc
> inputbuffer only seems to return 256 characters. Is there any way to
> fetch longer commands?
> Thanks.
> Josh
DBCC INPUTBUFFER returns an nvarchar(255)
You might be able to use fn_get_sql. See BOL for more info.
David Gugick
Imceda Software
www.imceda.com
some elsewhere with text values that went over 64k, but dbcc
inputbuffer only seems to return 256 characters. Is there any way to
fetch longer commands?
Thanks.
Josh
On Tue, 07 Dec 2004 10:28:45 -0800, jxstern <jxstern@.nowhere.com>
wrote:
>We have some commands here that go over 2k characters, and I've seen
>some elsewhere with text values that went over 64k, but dbcc
>inputbuffer only seems to return 256 characters. Is there any way to
>fetch longer commands?
Aha, found:
http://support.microsoft.com/default...b;en-us;325607
FIX: The fn_get_sql Function Returns SQL Text for Handle in the
Sysprocesses System Table
But it doesn't seem to be very robust - seems not to find the command
in cache all the time.
At least the fn/hotfix is present on a SQL2K sp3a system, fwiw.
J.
|||jxstern wrote:
> We have some commands here that go over 2k characters, and I've seen
> some elsewhere with text values that went over 64k, but dbcc
> inputbuffer only seems to return 256 characters. Is there any way to
> fetch longer commands?
> Thanks.
> Josh
DBCC INPUTBUFFER returns an nvarchar(255)
You might be able to use fn_get_sql. See BOL for more info.
David Gugick
Imceda Software
www.imceda.com
dbcc indexdefrag
Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment?
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.
DBCC INDEXDEFRAG logs everything, since the defrag occurs as a bunch of
mini-transactions. Your log usage will increase significantly, so be sure
to backup the log frequently (even 1-min intervals, if necessary) to manage
this growth. I've done this on DB's up to 200+GB. We had a 36GB log and
seemed to manage with 15-min backups.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
Does dbcc indexdefrag cause transaction log growth? I've got a bit
database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment?
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.
|||DBCC INDEXDEFRAG is fully logged and will cause significant log growth. You
can use/adapt the script found in BOL under DBCC SHOWCONTIG to limit the
particular tables you defrag according to fragementation percentage. You
can also add a section to determine log space consumed so you can pause the
defrag job between tables and fire off a log backup job.
Generally, without these limits, your defrag job will consume about as much
log space as the entire data partition of your database. And yes, there are
some improvements available in SQL 2005.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit
> database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables
> I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
|||From BOL
A very fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
http://sqlservercode.blogspot.com/
"fnguy" wrote:
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
|||In addition to the other posts:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment?
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.
DBCC INDEXDEFRAG logs everything, since the defrag occurs as a bunch of
mini-transactions. Your log usage will increase significantly, so be sure
to backup the log frequently (even 1-min intervals, if necessary) to manage
this growth. I've done this on DB's up to 200+GB. We had a 36GB log and
seemed to manage with 15-min backups.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
Does dbcc indexdefrag cause transaction log growth? I've got a bit
database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment?
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.
|||DBCC INDEXDEFRAG is fully logged and will cause significant log growth. You
can use/adapt the script found in BOL under DBCC SHOWCONTIG to limit the
particular tables you defrag according to fragementation percentage. You
can also add a section to determine log space consumed so you can pause the
defrag job between tables and fire off a log backup job.
Generally, without these limits, your defrag job will consume about as much
log space as the entire data partition of your database. And yes, there are
some improvements available in SQL 2005.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit
> database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables
> I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
|||From BOL
A very fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
http://sqlservercode.blogspot.com/
"fnguy" wrote:
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
|||In addition to the other posts:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
Tuesday, February 14, 2012
DBCC CheckDB and CheckAlloc
I've recently joined an organization which has no practice of regularly
running DBCC's with the explanation provided that since SQL Server is now
file based and many improvements have been made to the file system, that file
corruption/database corruption including allocation issues are no longer a
threat. That DBCC's are a holdover from Sybases use of raw partitions for
storing it's databases.
Anyone care to comment on that?
I can think of hardware issues that might cause problems that this would
catch.
Any recommendations or whitepapers or links supporting use of DBCC's would
be appreciated.
Running sql 2000 Enterprise on Win Srvr 2003 Enterprise.
thanks
I recall recommendations from MS and others when the new architecture (7.0) was released with such
statements. They are absolutely right that improvements were huge.
But as you know, other things can also happen, so we definitely want to run DBCC CHECKDB and
CHECKCATALOG (CHECKCATALOG is included in CHECKDB in 2005). No need for CHECKALLOG if you run
CHECKDB, though. CHECKALLOG is included in CHECKDB as of 7.0.
I'm sure there are good articles. I'd search Google and KB. I've might even listed some here:
http://www.karaszi.com/SQLServer/msarticles.asp. Don't forget Books Online (make sure you have most
recent version). Perhaps other will jump in with specific links.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Frost" <TomFrost@.discussions.microsoft.com> wrote in message
news:AF1B2648-0D01-44EE-B761-E024A21F443D@.microsoft.com...
> I've recently joined an organization which has no practice of regularly
> running DBCC's with the explanation provided that since SQL Server is now
> file based and many improvements have been made to the file system, that file
> corruption/database corruption including allocation issues are no longer a
> threat. That DBCC's are a holdover from Sybases use of raw partitions for
> storing it's databases.
> Anyone care to comment on that?
> I can think of hardware issues that might cause problems that this would
> catch.
> Any recommendations or whitepapers or links supporting use of DBCC's would
> be appreciated.
> Running sql 2000 Enterprise on Win Srvr 2003 Enterprise.
> thanks
running DBCC's with the explanation provided that since SQL Server is now
file based and many improvements have been made to the file system, that file
corruption/database corruption including allocation issues are no longer a
threat. That DBCC's are a holdover from Sybases use of raw partitions for
storing it's databases.
Anyone care to comment on that?
I can think of hardware issues that might cause problems that this would
catch.
Any recommendations or whitepapers or links supporting use of DBCC's would
be appreciated.
Running sql 2000 Enterprise on Win Srvr 2003 Enterprise.
thanks
I recall recommendations from MS and others when the new architecture (7.0) was released with such
statements. They are absolutely right that improvements were huge.
But as you know, other things can also happen, so we definitely want to run DBCC CHECKDB and
CHECKCATALOG (CHECKCATALOG is included in CHECKDB in 2005). No need for CHECKALLOG if you run
CHECKDB, though. CHECKALLOG is included in CHECKDB as of 7.0.
I'm sure there are good articles. I'd search Google and KB. I've might even listed some here:
http://www.karaszi.com/SQLServer/msarticles.asp. Don't forget Books Online (make sure you have most
recent version). Perhaps other will jump in with specific links.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Frost" <TomFrost@.discussions.microsoft.com> wrote in message
news:AF1B2648-0D01-44EE-B761-E024A21F443D@.microsoft.com...
> I've recently joined an organization which has no practice of regularly
> running DBCC's with the explanation provided that since SQL Server is now
> file based and many improvements have been made to the file system, that file
> corruption/database corruption including allocation issues are no longer a
> threat. That DBCC's are a holdover from Sybases use of raw partitions for
> storing it's databases.
> Anyone care to comment on that?
> I can think of hardware issues that might cause problems that this would
> catch.
> Any recommendations or whitepapers or links supporting use of DBCC's would
> be appreciated.
> Running sql 2000 Enterprise on Win Srvr 2003 Enterprise.
> thanks
Subscribe to:
Posts (Atom)