DBCC Shrinkfile

Hi,
I am plng to shrink a production database logfile from
14 GB to 3 GB. Does this slow down the application or does
it block users from accessing the database while i am
shrinking it ? It is an OLTP system. Also any idea how
much time shrinking a 14GB file would take ? Just
ballpark ..
TIA
MOHi MO,
Its best to update your stats on the DB and run your DBCCs first then do a
backup. The shrik should not take that long I would expect 15 min at the
outside. However, it will slow the system down and the more load from users
that is on the box the longer it will take. I would recommend shceduling
this for a slow time of the day I usually do this after 3am.
Hope that helps
John ...
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
> Hi,
> I am plng to shrink a production database logfile from
> 14 GB to 3 GB. Does this slow down the application or does
> it block users from accessing the database while i am
> shrinking it ? It is an OLTP system. Also any idea how
> much time shrinking a 14GB file would take ? Just
> ballpark ..
> TIA
> MO|||Mo - there's no way to predict how long a shrink will take on a live system
as it could block waiting for a lock or spend time searching for free space
if the space usage within the database is fragmented. It also depends on how
much heap and text usage you have, the speed of your IO subsystem, the
concurrent workload (as John says below) and so on. I'd be very surprised to
see it take only 15 minutes. The best you can do is time it on a completely
quiescent system and then extrapolate.
Shrink is setup to be the deadlock victim in all cases so should not cause
deadlocks. It does not hold long-term locks so should not block although
because it does hold page locks while moving pages, and does a bunch of IO
you should expect some slowdown. On a test system setup with a TPCC
benchmark workload running at close to 100% cpu, I've seen a roughly 20%
drop in transaction throughput while shrink is running - YMMV.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John VanderVliet" <john.vandervliet@.sjrb.ca.NOSPAM> wrote in message
news:e6MPetW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi MO,
> Its best to update your stats on the DB and run your DBCCs first then do a
> backup. The shrik should not take that long I would expect 15 min at the
> outside. However, it will slow the system down and the more load from
users
> that is on the box the longer it will take. I would recommend shceduling
> this for a slow time of the day I usually do this after 3am.
> Hope that helps
> John ...
> "Mo" <anonymous@.discussions.microsoft.com> wrote in message
> news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
> > Hi,
> > I am plng to shrink a production database logfile from
> > 14 GB to 3 GB. Does this slow down the application or does
> > it block users from accessing the database while i am
> > shrinking it ? It is an OLTP system. Also any idea how
> > much time shrinking a 14GB file would take ? Just
> > ballpark ..
> > TIA
> > MO
>

DBCC Shrinkfile

Hi,
I am plng to shrink a production database logfile from
14 GB to 3 GB. Does this slow down the application or does
it block users from accessing the database while i am
shrinking it ? It is an OLTP system. Also any idea how
much time shrinking a 14GB file would take ? Just
ballpark ..
TIA
MOHi MO,
Its best to update your stats on the DB and run your DBCCs first then do a
backup. The shrik should not take that long I would expect 15 min at the
outside. However, it will slow the system down and the more load from users
that is on the box the longer it will take. I would recommend shceduling
this for a slow time of the day I usually do this after 3am.
Hope that helps
John ...
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
> Hi,
> I am plng to shrink a production database logfile from
> 14 GB to 3 GB. Does this slow down the application or does
> it block users from accessing the database while i am
> shrinking it ? It is an OLTP system. Also any idea how
> much time shrinking a 14GB file would take ? Just
> ballpark ..
> TIA
> MO|||Mo - there's no way to predict how long a shrink will take on a live system
as it could block waiting for a lock or spend time searching for free space
if the space usage within the database is fragmented. It also depends on how
much heap and text usage you have, the speed of your IO subsystem, the
concurrent workload (as John says below) and so on. I'd be very surprised to
see it take only 15 minutes. The best you can do is time it on a completely
quiescent system and then extrapolate.
Shrink is setup to be the deadlock victim in all cases so should not cause
deadlocks. It does not hold long-term locks so should not block although
because it does hold page locks while moving pages, and does a bunch of IO
you should expect some slowdown. On a test system setup with a TPCC
benchmark workload running at close to 100% cpu, I've seen a roughly 20%
drop in transaction throughput while shrink is running - YMMV.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John VanderVliet" <john.vandervliet@.sjrb.ca.NOSPAM> wrote in message
news:e6MPetW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi MO,
> Its best to update your stats on the DB and run your DBCCs first then do a
> backup. The shrik should not take that long I would expect 15 min at the
> outside. However, it will slow the system down and the more load from
users
> that is on the box the longer it will take. I would recommend shceduling
> this for a slow time of the day I usually do this after 3am.
> Hope that helps
> John ...
> "Mo" <anonymous@.discussions.microsoft.com> wrote in message
> news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
>sql

dbcc dbreindex on all tables/indexes and slow

This is what i ran and it was slow
declare reindex_cursor insensitive cursor for
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
and table_type = 'Base table'
declare @.table_name sysname
open reindex_cursor
fetch next from reindex_cursor into @.table_name
while (@.@.fetch_status = 0)
begin
print ('dbcc dbreindex(' + @.table_name + ')' )
exec ('dbcc dbreindex(' + @.table_name + ')' )
fetch next from reindex_cursor into @.table_name
end
close reindex_cursor
deallocate reindex_cursor
Could the problem be because of the insensitive that i mentioned in the
cursor declaration ? I can see that the DBCC is still running and not being
blocked but its way too slow. If I do cancel in between, will it rollback
everything ?For something like that I always use a STATIC cursor. You also don't need
to use dynamic sql as DBCC DBREINDEX will take a variable for the table
name. My guess why the operation is so slow is that your log and db files
probably don't have enough room in them for this and are constantly growing.
If your in full recovery mode you might want to backup your logs in between
so try something like this:
SET NOCOUNT ON
DECLARE @.TableName VARCHAR(100), @.Counter INT
SET @.Counter = 1
DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN curTables
FETCH NEXT FROM curTables INTO @.TableName
SET @.TableName = RTRIM(@.TableName)
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT 'Reindexing ' + @.TableName
DBCC DBREINDEX (@.TableName)
SET @.Counter = @.Counter + 1
-- Backup the Log every so often so as not to fill the log
IF @.Counter % 10 = 0
BEGIN
BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT , NOUNLOAD
,
NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
NOFORMAT
END
FETCH NEXT FROM curTables INTO @.TableName
END
CLOSE curTables
DEALLOCATE curTables
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> This is what i ran and it was slow
> declare reindex_cursor insensitive cursor for
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> and table_type = 'Base table'
> declare @.table_name sysname
> open reindex_cursor
> fetch next from reindex_cursor into @.table_name
> while (@.@.fetch_status = 0)
> begin
> print ('dbcc dbreindex(' + @.table_name + ')' )
> exec ('dbcc dbreindex(' + @.table_name + ')' )
> fetch next from reindex_cursor into @.table_name
> end
> close reindex_cursor
> deallocate reindex_cursor
> Could the problem be because of the insensitive that i mentioned in the
> cursor declaration ? I can see that the DBCC is still running and not
being
> blocked but its way too slow. If I do cancel in between, will it rollback
> everything ?
>|||Why you don't use an database maintance plan?
Ronald
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> This is what i ran and it was slow
> declare reindex_cursor insensitive cursor for
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> and table_type = 'Base table'
> declare @.table_name sysname
> open reindex_cursor
> fetch next from reindex_cursor into @.table_name
> while (@.@.fetch_status = 0)
> begin
> print ('dbcc dbreindex(' + @.table_name + ')' )
> exec ('dbcc dbreindex(' + @.table_name + ')' )
> fetch next from reindex_cursor into @.table_name
> end
> close reindex_cursor
> deallocate reindex_cursor
> Could the problem be because of the insensitive that i mentioned in the
> cursor declaration ? I can see that the DBCC is still running and not
being
> blocked but its way too slow. If I do cancel in between, will it rollback
> everything ?
>|||Now changing gears to the use of dynamic sql. How can i tell what needs a
dynamic sql and what can accept variables. Can you provide some examples and
how can i figure it out ? Also the definition of insensitive and static
seemed to be the same from BOL. Btw, i got the initial script from google
too.
Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uXF66WQsDHA.4088@.TK2MSFTNGP11.phx.gbl...
> For something like that I always use a STATIC cursor. You also don't need
> to use dynamic sql as DBCC DBREINDEX will take a variable for the table
> name. My guess why the operation is so slow is that your log and db files
> probably don't have enough room in them for this and are constantly
growing.
> If your in full recovery mode you might want to backup your logs in
between
> so try something like this:
>
> SET NOCOUNT ON
> DECLARE @.TableName VARCHAR(100), @.Counter INT
> SET @.Counter = 1
> DECLARE curTables CURSOR STATIC LOCAL
> FOR
> SELECT Table_Name
> FROM Information_Schema.Tables
> WHERE Table_Type = 'BASE TABLE'
> OPEN curTables
> FETCH NEXT FROM curTables INTO @.TableName
> SET @.TableName = RTRIM(@.TableName)
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT 'Reindexing ' + @.TableName
> DBCC DBREINDEX (@.TableName)
> SET @.Counter = @.Counter + 1
> -- Backup the Log every so often so as not to fill the log
> IF @.Counter % 10 = 0
> BEGIN
> BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT ,
NOUNLOAD
> ,
> NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
> NOFORMAT
> END
> FETCH NEXT FROM curTables INTO @.TableName
> END
> CLOSE curTables
> DEALLOCATE curTables
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> > This is what i ran and it was slow
> >
> > declare reindex_cursor insensitive cursor for
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> > and table_type = 'Base table'
> >
> > declare @.table_name sysname
> >
> > open reindex_cursor
> > fetch next from reindex_cursor into @.table_name
> >
> > while (@.@.fetch_status = 0)
> > begin
> > print ('dbcc dbreindex(' + @.table_name + ')' )
> > exec ('dbcc dbreindex(' + @.table_name + ')' )
> > fetch next from reindex_cursor into @.table_name
> > end
> >
> > close reindex_cursor
> > deallocate reindex_cursor
> >
> > Could the problem be because of the insensitive that i mentioned in the
> > cursor declaration ? I can see that the DBCC is still running and not
> being
> > blocked but its way too slow. If I do cancel in between, will it
rollback
> > everything ?
> >
> >
>|||Sorry, I spaced on the Insensitive. As for when you can and when you can't
it isn't always spelled out but if you look at the entry in BOL it usually
gives a hint. If there are quotes around the option you can usually use a
variable.
DBCC DBREINDEX
( [ 'database.owner.table_name'
[ , index_name
[ , fillfactor ]
]
]
) [ WITH NO_INFOMSGS ]
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23CQ4liTsDHA.3416@.tk2msftngp13.phx.gbl...
> Now changing gears to the use of dynamic sql. How can i tell what needs a
> dynamic sql and what can accept variables. Can you provide some examples
and
> how can i figure it out ? Also the definition of insensitive and static
> seemed to be the same from BOL. Btw, i got the initial script from google
> too.
> Thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uXF66WQsDHA.4088@.TK2MSFTNGP11.phx.gbl...
> > For something like that I always use a STATIC cursor. You also don't
need
> > to use dynamic sql as DBCC DBREINDEX will take a variable for the table
> > name. My guess why the operation is so slow is that your log and db
files
> > probably don't have enough room in them for this and are constantly
> growing.
> > If your in full recovery mode you might want to backup your logs in
> between
> > so try something like this:
> >
> >
> > SET NOCOUNT ON
> >
> > DECLARE @.TableName VARCHAR(100), @.Counter INT
> >
> > SET @.Counter = 1
> >
> > DECLARE curTables CURSOR STATIC LOCAL
> > FOR
> > SELECT Table_Name
> > FROM Information_Schema.Tables
> > WHERE Table_Type = 'BASE TABLE'
> >
> > OPEN curTables
> > FETCH NEXT FROM curTables INTO @.TableName
> > SET @.TableName = RTRIM(@.TableName)
> >
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > SELECT 'Reindexing ' + @.TableName
> >
> > DBCC DBREINDEX (@.TableName)
> >
> > SET @.Counter = @.Counter + 1
> > -- Backup the Log every so often so as not to fill the log
> > IF @.Counter % 10 = 0
> > BEGIN
> > BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT ,
> NOUNLOAD
> > ,
> > NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
> > NOFORMAT
> > END
> >
> > FETCH NEXT FROM curTables INTO @.TableName
> > END
> >
> > CLOSE curTables
> > DEALLOCATE curTables
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> > > This is what i ran and it was slow
> > >
> > > declare reindex_cursor insensitive cursor for
> > > select TABLE_NAME
> > > from INFORMATION_SCHEMA.TABLES
> > > where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> > > and table_type = 'Base table'
> > >
> > > declare @.table_name sysname
> > >
> > > open reindex_cursor
> > > fetch next from reindex_cursor into @.table_name
> > >
> > > while (@.@.fetch_status = 0)
> > > begin
> > > print ('dbcc dbreindex(' + @.table_name + ')' )
> > > exec ('dbcc dbreindex(' + @.table_name + ')' )
> > > fetch next from reindex_cursor into @.table_name
> > > end
> > >
> > > close reindex_cursor
> > > deallocate reindex_cursor
> > >
> > > Could the problem be because of the insensitive that i mentioned in
the
> > > cursor declaration ? I can see that the DBCC is still running and not
> > being
> > > blocked but its way too slow. If I do cancel in between, will it
> rollback
> > > everything ?
> > >
> > >
> >
> >
>|||On a seperate note, I'd like to encourage you to read the excellent
whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
It will help you determine the best way to deal with fragmentation and
whetehr you need to do anything about it at all, rather than just rebuilding
every table in the database.
Regards,
Paul.
--
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:#iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> This is what i ran and it was slow
> declare reindex_cursor insensitive cursor for
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> and table_type = 'Base table'
> declare @.table_name sysname
> open reindex_cursor
> fetch next from reindex_cursor into @.table_name
> while (@.@.fetch_status = 0)
> begin
> print ('dbcc dbreindex(' + @.table_name + ')' )
> exec ('dbcc dbreindex(' + @.table_name + ')' )
> fetch next from reindex_cursor into @.table_name
> end
> close reindex_cursor
> deallocate reindex_cursor
> Could the problem be because of the insensitive that i mentioned in the
> cursor declaration ? I can see that the DBCC is still running and not
being
> blocked but its way too slow. If I do cancel in between, will it rollback
> everything ?
>|||Thanks Paul, I should have included that again for completeness but I know
that link has been referred to him several times already<g>.
--
Andrew J. Kelly
SQL Server MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uwswsAisDHA.2244@.TK2MSFTNGP09.phx.gbl...
> On a seperate note, I'd like to encourage you to read the excellent
> whitepaper at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> It will help you determine the best way to deal with fragmentation and
> whetehr you need to do anything about it at all, rather than just
rebuilding
> every table in the database.
> Regards,
> Paul.
> --
> 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:#iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> > This is what i ran and it was slow
> >
> > declare reindex_cursor insensitive cursor for
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> > and table_type = 'Base table'
> >
> > declare @.table_name sysname
> >
> > open reindex_cursor
> > fetch next from reindex_cursor into @.table_name
> >
> > while (@.@.fetch_status = 0)
> > begin
> > print ('dbcc dbreindex(' + @.table_name + ')' )
> > exec ('dbcc dbreindex(' + @.table_name + ')' )
> > fetch next from reindex_cursor into @.table_name
> > end
> >
> > close reindex_cursor
> > deallocate reindex_cursor
> >
> > Could the problem be because of the insensitive that i mentioned in the
> > cursor declaration ? I can see that the DBCC is still running and not
> being
> > blocked but its way too slow. If I do cancel in between, will it
rollback
> > everything ?
> >
> >
>

dbcc checkdatabase

I got a call from my manager because users were complaining about the server
being slow, and he said he found a lots of locks on the server.
I logged in and I see there is a process running DBCC CHECKDATABASE on
(DYNAMICS) the main Great Plains database.
I'm the DBA, there are many users that know the SA password, I don't think
any of them executed the command.
My question is: Anybody in this forum knows of a program that would execute
this command by itself?
Is there a reason for a program or the server to to do this by itself?
I have read the documentation and they advise to use it when the load is
light because it could impact disk I/O.
Thanks
Tim Bales
timbales@.bellsouth.netHave you checked to see if there is a scheduled job that might have run
this?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tim Bales" <timbales@.bellsouth.net> wrote in message
news:Oielg63YIHA.5612@.TK2MSFTNGP06.phx.gbl...
>I got a call from my manager because users were complaining about the
>server being slow, and he said he found a lots of locks on the server.
> I logged in and I see there is a process running DBCC CHECKDATABASE on
> (DYNAMICS) the main Great Plains database.
> I'm the DBA, there are many users that know the SA password, I don't think
> any of them executed the command.
> My question is: Anybody in this forum knows of a program that would
> execute this command by itself?
> Is there a reason for a program or the server to to do this by itself?
> I have read the documentation and they advise to use it when the load is
> light because it could impact disk I/O.
> Thanks
> Tim Bales
> timbales@.bellsouth.net
>|||Andrew is right. Great Plains creates a number of jobs for varying
purposes.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:epV4%23K4YIHA.4828@.TK2MSFTNGP05.phx.gbl...
> Have you checked to see if there is a scheduled job that might have run
> this?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Tim Bales" <timbales@.bellsouth.net> wrote in message
> news:Oielg63YIHA.5612@.TK2MSFTNGP06.phx.gbl...
>>I got a call from my manager because users were complaining about the
>>server being slow, and he said he found a lots of locks on the server.
>> I logged in and I see there is a process running DBCC CHECKDATABASE on
>> (DYNAMICS) the main Great Plains database.
>> I'm the DBA, there are many users that know the SA password, I don't
>> think any of them executed the command.
>> My question is: Anybody in this forum knows of a program that would
>> execute this command by itself?
>> Is there a reason for a program or the server to to do this by itself?
>> I have read the documentation and they advise to use it when the load is
>> light because it could impact disk I/O.
>> Thanks
>> Tim Bales
>> timbales@.bellsouth.net
>|||I've just found GP to be a pain, in general. It opens a ton of
connections...it's slow, in general. Anyone else seeing this behavior?
Thanks.
Chris
"Tim Bales" wrote:
> I got a call from my manager because users were complaining about the server
> being slow, and he said he found a lots of locks on the server.
> I logged in and I see there is a process running DBCC CHECKDATABASE on
> (DYNAMICS) the main Great Plains database.
> I'm the DBA, there are many users that know the SA password, I don't think
> any of them executed the command.
> My question is: Anybody in this forum knows of a program that would execute
> this command by itself?
> Is there a reason for a program or the server to to do this by itself?
> I have read the documentation and they advise to use it when the load is
> light because it could impact disk I/O.
> Thanks
> Tim Bales
> timbales@.bellsouth.net
>
>