DBCC Shrinkfile

What happens underlying the DBCC Shrinkfile statement ? What moves around
,etc.. ? Trying to figure out why it takes so long for this command to run
,, what does it do ? ThanksHassan, you really should pick up a copy of Kalen Delaney's Inside SQL
Server 2000. It answers all of the questions you've had recently, and then
some. It really is the best money you'll spend if you want to learn the
guts of SQL Server...
On 3/19/05 1:14 AM, in article OllldrELFHA.3988@.tk2msftngp13.phx.gbl,
"Hassan" <fatima_ja@.hotmail.com> wrote:
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||It moves data pages from the end of the file towards then beginning of the file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||I forgot to mention, that all of this data movement and space
allocation/deallocation is logged, so your transaction log can grow very
large as a result as well.
--
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
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||Kalen also has a nice article in this month's SQL mag... But in addition to
Itzik's comment it is really a 2 part operation, first it move either rows,
or whole pages from the end of the file to the front of the file, then it
( by default) releases the space from the end of the file to the OS... One
warning however, is that this can cause fragmentation of your data, which
may ( or may not ) affect your performance.
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
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>

DBCC Shrinkfile

What happens underlying the DBCC Shrinkfile statement ? What moves around
,etc.. ? Trying to figure out why it takes so long for this command to run
,, what does it do ? ThanksHassan, you really should pick up a copy of Kalen Delaney's Inside SQL
Server 2000. It answers all of the questions you've had recently, and then
some. It really is the best money you'll spend if you want to learn the
guts of SQL Server...
On 3/19/05 1:14 AM, in article OllldrELFHA.3988@.tk2msftngp13.phx.gbl,
"Hassan" <fatima_ja@.hotmail.com> wrote:

> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||It moves data pages from the end of the file towards then beginning of the f
ile.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl
..
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||I forgot to mention, that all of this data movement and space
allocation/deallocation is logged, so your transaction log can grow very
large as a result as well.
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
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||Kalen also has a nice article in this month's SQL mag... But in addition to
Itzik's comment it is really a 2 part operation, first it move either rows,
or whole pages from the end of the file to the front of the file, then it
( by default) releases the space from the end of the file to the OS... One
warning however, is that this can cause fragmentation of your data, which
may ( or may not ) affect your performance.
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
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>

DBCC Shrinkfile

What happens underlying the DBCC Shrinkfile statement ? What moves around
,etc.. ? Trying to figure out why it takes so long for this command to run
,, what does it do ? Thanks
Hassan, you really should pick up a copy of Kalen Delaney's Inside SQL
Server 2000. It answers all of the questions you've had recently, and then
some. It really is the best money you'll spend if you want to learn the
guts of SQL Server...
On 3/19/05 1:14 AM, in article OllldrELFHA.3988@.tk2msftngp13.phx.gbl,
"Hassan" <fatima_ja@.hotmail.com> wrote:

> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
|||It moves data pages from the end of the file towards then beginning of the file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
|||I forgot to mention, that all of this data movement and space
allocation/deallocation is logged, so your transaction log can grow very
large as a result as well.
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
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
|||Kalen also has a nice article in this month's SQL mag... But in addition to
Itzik's comment it is really a 2 part operation, first it move either rows,
or whole pages from the end of the file to the front of the file, then it
( by default) releases the space from the end of the file to the OS... One
warning however, is that this can cause fragmentation of your data, which
may ( or may not ) affect your performance.
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
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>

DBCC CheckDB timeout

Hi I'm trying to figure out a way of getting the DBCC CheckDB command to timeout in the event a table in my database is locked.

Lets say I run this stored procedure:

Begin tran
Select * from NumbersTable with(tablockx)

Of course, the NumbersTable in my database is locked.
Now I run DBCC CheckDB('database'), which will hang because the stored procedure above caused a block/lock on the table.

My question is, given this situation is there anyway to set some sort of timeout for DBCC CheckDB so it doesn't run forever?

And yeap, I know I can commit the transaction or kill the lock so DBCC CheckDB can resume. But I'm looking for a way of getting it to timeout.

I've tried the command SET LOCK_TIMEOUT 10000 but that doesn't seem to do anything.

Thank you all.

I don't quite understand why you'd want to do this, but...

one way that might work - you could call the DBCC command by invoking OSQL with the -t <timeout> switch.
(haven't tried it, but it seems to do the trick)

/Kenneth

|||Hi thank you for the reply.
The reason I want to do this is because I've written a C++ utility that goes through all databases in SQL Server and runs a maintanence script on the databases.

Here is the script:

USE master
DBCC CHECKDB (‘<database name>’)
DBCC SHRINKDATABASE (‘<database name>’)
USE <database name>
EXEC sp_MsForEachTable 'DBCC DBREINDEX (''?'', '''', 0)'
EXEC sp_createstats
EXEC sp_updatestats
USE master
DBCC UPDATEUSAGE ('<database name>')

Then when a corruption is found, I run CheckDB in repair mode:
EXEC sp_dboption '<database name>',single,true
DBCC CHECKDB (‘<database name>’, <advised repair command>)
EXEC sp_dboption '<database name>',single,false

When a table in a database is locked, then the DBCC CheckDB part will hang. Sure the chances of it actually happening are small (only been manually locking it), but if possible I'd like to know a way of setting a timeout.
I've tried SET LOCK_TIMEOUT 10000 before CheckDB but it doesn't work.
Is there anyway to include your OSQL timeout into the script above?

Any help is appreciated.|||

As I understand it (haven't actually used this parameter), the timeout switch in OSQL would terminate the command that OSQL is running when the value is exceeded.

The script, being a 'normal' sql script, can be called from OSQL by the /i <myscriptfile.sql> switch.

Though, I'm having doubts about running DBCC in repairmode in such an automated way.
You are fully aware of the possibilites that you may experience dataloss with this option?
Is this really the intention? Normally, each case of suspected corruption or DBCC error needs some 'human evaluation' in order to decide what action to perform next.

/Kenneth

DBCC CheckDB timeout

Hi I'm trying to figure out a way of getting the DBCC CheckDB command to timeout in the event a table in my database is locked.

Lets say I run this stored procedure:

Begin tran
Select * from NumbersTable with(tablockx)

Of course, the NumbersTable in my database is locked.
Now I run DBCC CheckDB('database'), which will hang because the stored procedure above caused a block/lock on the table.

My question is, given this situation is there anyway to set some sort of timeout for DBCC CheckDB so it doesn't run forever?

And yeap, I know I can commit the transaction or kill the lock so DBCC CheckDB can resume. But I'm looking for a way of getting it to timeout.

I've tried the command SET LOCK_TIMEOUT 10000 but that doesn't seem to do anything.

Thank you all.

I don't quite understand why you'd want to do this, but...

one way that might work - you could call the DBCC command by invoking OSQL with the -t <timeout> switch.
(haven't tried it, but it seems to do the trick)

/Kenneth

|||Hi thank you for the reply.
The reason I want to do this is because I've written a C++ utility that goes through all databases in SQL Server and runs a maintanence script on the databases.

Here is the script:

USE master
DBCC CHECKDB (‘<database name>’)
DBCC SHRINKDATABASE (‘<database name>’)
USE <database name>
EXEC sp_MsForEachTable 'DBCC DBREINDEX (''?'', '''', 0)'
EXEC sp_createstats
EXEC sp_updatestats
USE master
DBCC UPDATEUSAGE ('<database name>')

Then when a corruption is found, I run CheckDB in repair mode:
EXEC sp_dboption '<database name>',single,true
DBCC CHECKDB (‘<database name>’, <advised repair command>)
EXEC sp_dboption '<database name>',single,false

When a table in a database is locked, then the DBCC CheckDB part will hang. Sure the chances of it actually happening are small (only been manually locking it), but if possible I'd like to know a way of setting a timeout.
I've tried SET LOCK_TIMEOUT 10000 before CheckDB but it doesn't work.
Is there anyway to include your OSQL timeout into the script above?

Any help is appreciated.
|||

As I understand it (haven't actually used this parameter), the timeout switch in OSQL would terminate the command that OSQL is running when the value is exceeded.

The script, being a 'normal' sql script, can be called from OSQL by the /i <myscriptfile.sql> switch.

Though, I'm having doubts about running DBCC in repairmode in such an automated way.
You are fully aware of the possibilites that you may experience dataloss with this option?
Is this really the intention? Normally, each case of suspected corruption or DBCC error needs some 'human evaluation' in order to decide what action to perform next.

/Kenneth