I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to the
correct filegroup (it defaulted to the primary).
I am trying to use DBCC shrinkfile with the emptyfile option so that I can
then go and do a 'alter database remove file'. I start the process and with
sp_who2 active it shows the command DbccFilesCompact, but it just sits there
and spins forever.
I see cpu and diskio cycles escalating but nothing ever happens.
is there a step before this that i am forgetting?
setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
model.
gracias,
Robert
i should state a few more facts.
It is a data file (not log), and the whole DB 1.8 TB. The file is only 100GB.
I have tried to backup just that filegroup (only 1 file in it) and do a
emptyfile right afterwards but no luck...
"sql411@.nospam.com" wrote:
> I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to the
> correct filegroup (it defaulted to the primary).
> I am trying to use DBCC shrinkfile with the emptyfile option so that I can
> then go and do a 'alter database remove file'. I start the process and with
> sp_who2 active it shows the command DbccFilesCompact, but it just sits there
> and spins forever.
> I see cpu and diskio cycles escalating but nothing ever happens.
> is there a step before this that i am forgetting?
> setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
> model.
> gracias,
> Robert
sql
Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts
Thursday, March 29, 2012
DBCC SHRINKFILE ('filename' , EMPTYFILE) not working
I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to th
e
correct filegroup (it defaulted to the primary).
I am trying to use DBCC shrinkfile with the emptyfile option so that I can
then go and do a 'alter database remove file'. I start the process and with
sp_who2 active it shows the command DbccFilesCompact, but it just sits there
and spins forever.
I see cpu and diskio cycles escalating but nothing ever happens.
is there a step before this that i am forgetting?
setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
model.
gracias,
Roberti should state a few more facts.
It is a data file (not log), and the whole DB 1.8 TB. The file is only 100G
B.
I have tried to backup just that filegroup (only 1 file in it) and do a
emptyfile right afterwards but no luck...
"sql411@.nospam.com" wrote:
> I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to
the
> correct filegroup (it defaulted to the primary).
> I am trying to use DBCC shrinkfile with the emptyfile option so that I can
> then go and do a 'alter database remove file'. I start the process and wi
th
> sp_who2 active it shows the command DbccFilesCompact, but it just sits the
re
> and spins forever.
> I see cpu and diskio cycles escalating but nothing ever happens.
> is there a step before this that i am forgetting?
> setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
> model.
> gracias,
> Robert
e
correct filegroup (it defaulted to the primary).
I am trying to use DBCC shrinkfile with the emptyfile option so that I can
then go and do a 'alter database remove file'. I start the process and with
sp_who2 active it shows the command DbccFilesCompact, but it just sits there
and spins forever.
I see cpu and diskio cycles escalating but nothing ever happens.
is there a step before this that i am forgetting?
setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
model.
gracias,
Roberti should state a few more facts.
It is a data file (not log), and the whole DB 1.8 TB. The file is only 100G
B.
I have tried to backup just that filegroup (only 1 file in it) and do a
emptyfile right afterwards but no luck...
"sql411@.nospam.com" wrote:
> I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to
the
> correct filegroup (it defaulted to the primary).
> I am trying to use DBCC shrinkfile with the emptyfile option so that I can
> then go and do a 'alter database remove file'. I start the process and wi
th
> sp_who2 active it shows the command DbccFilesCompact, but it just sits the
re
> and spins forever.
> I see cpu and diskio cycles escalating but nothing ever happens.
> is there a step before this that i am forgetting?
> setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
> model.
> gracias,
> Robert
dbcc shrinkfile & recovery mode
Occasionally, we have gotten messages that the logfile is too big on one
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BC
AutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BC
AutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
dbcc shrinkfile & recovery mode
Occasionally, we have gotten messages that the logfile is too big on one
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BCAutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BCAutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
dbcc shrinkfile & recovery mode
Occasionally, we have gotten messages that the logfile is too big on one
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BCAutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BCAutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
dbcc shrinkfile - SQL Server 2000 - invalidate log?
Hi,
I need to shrink a database file and was wondering whether it is
required to run a full backup after the shrink operation.
In SQL Server 7.0 shrinkfile was a non-logged operation so would
invalidate your transaction logs. Is the same true for 2000?
Obviously as a matter of course I would backup before and after the
operation but going forward I may want to implement this on a regular
basis.
Cheers
Deedeebeeay@.gmail.com wrote:
> Hi,
> I need to shrink a database file and was wondering whether it is
> required to run a full backup after the shrink operation.
> In SQL Server 7.0 shrinkfile was a non-logged operation so would
> invalidate your transaction logs. Is the same true for 2000?
> Obviously as a matter of course I would backup before and after the
> operation but going forward I may want to implement this on a regular
> basis.
> Cheers
> Dee
Shrinking log files does not invalidate the log. It is however a very
bad idea to shrink on a regular basis. If you are running full recovery
and doing regular transaction log backups then why would you want to
keep shrinking the log?
For more information see:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Hi David,
Its not the log I want to shrink but the database.|||deebeeay@.gmail.com wrote:
> Hi David,
> Its not the log I want to shrink but the database.
Skrinking the database regularly is also unlikely to be a good idea in
most environments. Do you enable autogrow? Auto-growing a database is
potentially a very expensive operation. Much better to preset the
database size, turn auto-grow OFF and then don't shrink it at all.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Yes I agree, auto-growth isn't good and by that same token a regular
shrink is probably not a good idea.
However I'd still like to know if a dbcc shrinkfile would then require
a post full database backup to ensure recoverability.|||deebeeay@.gmail.com wrote:
> Yes I agree, auto-growth isn't good and by that same token a regular
> shrink is probably not a good idea.
> However I'd still like to know if a dbcc shrinkfile would then require
> a post full database backup to ensure recoverability.
Shrinking a data file does not invalidate the log. However, it will
cause an exceptional amount of logging. Shrinking may require at least
as much log as you have data in the file(s) being shrunk. So your next
log backup could be vastly inflated. Assuming your database is offline
you may want to backup the log with the TRUNCATE ONLY option
immediately after shrinking and then perform a database backup. That's
not essential but it does mean you can return the log to its more
typical size. On the other hand if you think you need to do this
regularly then you'll have to ensure sufficient log space to support it
- so shrinking the data file is usually a false economy because it
moves data around without reducing the storage requirement.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Many thanks for the reply David.
If I do a dbcc shrinkfile using the TRUNCATEONLY option - would this
reduce the amount of logging required?|||deebeeay@.gmail.com (deebeeay@.gmail.com) writes:
> Many thanks for the reply David.
> If I do a dbcc shrinkfile using the TRUNCATEONLY option - would this
> reduce the amount of logging required?
As I understand Books Online, this should not be an expensive operation
with regards to the tranaction log. On the other hand, it may not have
much effect, since it removes only extents at the end of the data file.
If you mistakenly created a file 10 times too large, then I would
expect TRUNCATEONLY to be useful. But if you want to shrink the database,
because you just deleted 5 years worth of data, TRUNCATEONLY is not likely
to have any effect at all.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
dbcc shrinkfile - how long will it take
Hi,
We are shrinking a large databse using dbcc shrinkfile and it has been
running for hours. Is there any way of determining how long the job still has
left to run (the equivalent of the Oracle dynamic view v$session_longops)?.
Thanks,
Andy
Not that I am aware of. I avoid this by shrinking in increments of 100MB.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy
|||Hi,
In SQL Server we can not exactly say how the process is going to run. But as
Kevin mentioned you could try
shrinking the files by providing a lower value (500 MB or soo...). Ensure
that you do a Backup LOG command
before shrinking the tranasction log.
Thanks
Hari
Sql server MVP
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy
|||Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
but even that is just a percentage complete with an elapsed-time based
extrapolation of the completion time. Basically, there are far too many
variables to consider to have a hope of being able to predict the run-time -
the worst ones being blocking, the starting state of the file/database, and
how much work shrink needs to do. For instance, if another process takes a
lock that shrink needs, shrink will wait forever for that lock.
Have you checked to make sure it is actually progressing and isn't blocked?
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
has
> left to run (the equivalent of the Oracle dynamic view
v$session_longops)?.
> Thanks,
> Andy
|||Not sure if this is related to the original post but I have SQL2000 SP4
install and have tried running DBCC SHRINKDATABASE (MyDB). This results is a
lock that is not released. Ent. Mgr shows "spid 58 (blocked by 58)" the Wait
Type is "PAGEIOLATCH_SH".
I think this maybe a bug in the SP4? I haven't had this problem with a DB
shrink before.
Regards,
John
"Paul S Randal [MS]" wrote:
> Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
> but even that is just a percentage complete with an elapsed-time based
> extrapolation of the completion time. Basically, there are far too many
> variables to consider to have a hope of being able to predict the run-time -
> the worst ones being blocking, the starting state of the file/database, and
> how much work shrink needs to do. For instance, if another process takes a
> lock that shrink needs, shrink will wait forever for that lock.
> Have you checked to make sure it is actually progressing and isn't blocked?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
> news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> has
> v$session_longops)?.
>
>
We are shrinking a large databse using dbcc shrinkfile and it has been
running for hours. Is there any way of determining how long the job still has
left to run (the equivalent of the Oracle dynamic view v$session_longops)?.
Thanks,
Andy
Not that I am aware of. I avoid this by shrinking in increments of 100MB.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy
|||Hi,
In SQL Server we can not exactly say how the process is going to run. But as
Kevin mentioned you could try
shrinking the files by providing a lower value (500 MB or soo...). Ensure
that you do a Backup LOG command
before shrinking the tranasction log.
Thanks
Hari
Sql server MVP
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy
|||Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
but even that is just a percentage complete with an elapsed-time based
extrapolation of the completion time. Basically, there are far too many
variables to consider to have a hope of being able to predict the run-time -
the worst ones being blocking, the starting state of the file/database, and
how much work shrink needs to do. For instance, if another process takes a
lock that shrink needs, shrink will wait forever for that lock.
Have you checked to make sure it is actually progressing and isn't blocked?
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
has
> left to run (the equivalent of the Oracle dynamic view
v$session_longops)?.
> Thanks,
> Andy
|||Not sure if this is related to the original post but I have SQL2000 SP4
install and have tried running DBCC SHRINKDATABASE (MyDB). This results is a
lock that is not released. Ent. Mgr shows "spid 58 (blocked by 58)" the Wait
Type is "PAGEIOLATCH_SH".
I think this maybe a bug in the SP4? I haven't had this problem with a DB
shrink before.
Regards,
John
"Paul S Randal [MS]" wrote:
> Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
> but even that is just a percentage complete with an elapsed-time based
> extrapolation of the completion time. Basically, there are far too many
> variables to consider to have a hope of being able to predict the run-time -
> the worst ones being blocking, the starting state of the file/database, and
> how much work shrink needs to do. For instance, if another process takes a
> lock that shrink needs, shrink will wait forever for that lock.
> Have you checked to make sure it is actually progressing and isn't blocked?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
> news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> has
> v$session_longops)?.
>
>
dbcc shrinkfile - how long will it take
Hi,
We are shrinking a large databse using dbcc shrinkfile and it has been
running for hours. Is there any way of determining how long the job still ha
s
left to run (the equivalent of the Oracle dynamic view v$session_longops)?.
Thanks,
AndyNot that I am aware of. I avoid this by shrinking in increments of 100MB.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Hi,
In SQL Server we can not exactly say how the process is going to run. But as
Kevin mentioned you could try
shrinking the files by providing a lower value (500 MB or soo...). Ensure
that you do a Backup LOG command
before shrinking the tranasction log.
Thanks
Hari
Sql server MVP
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
but even that is just a percentage complete with an elapsed-time based
extrapolation of the completion time. Basically, there are far too many
variables to consider to have a hope of being able to predict the run-time -
the worst ones being blocking, the starting state of the file/database, and
how much work shrink needs to do. For instance, if another process takes a
lock that shrink needs, shrink will wait forever for that lock.
Have you checked to make sure it is actually progressing and isn't blocked?
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
has
> left to run (the equivalent of the Oracle dynamic view
v$session_longops)?.
> Thanks,
> Andy|||Not sure if this is related to the original post but I have SQL2000 SP4
install and have tried running DBCC SHRINKDATABASE (MyDB). This results is a
lock that is not released. Ent. Mgr shows "spid 58 (blocked by 58)" the Wait
Type is "PAGEIOLATCH_SH".
I think this maybe a bug in the SP4? I haven't had this problem with a DB
shrink before.
Regards,
John
"Paul S Randal [MS]" wrote:
> Not in SQL Server 2000. We've put progress reporting in for SQL Server 200
5,
> but even that is just a percentage complete with an elapsed-time based
> extrapolation of the completion time. Basically, there are far too many
> variables to consider to have a hope of being able to predict the run-time
-
> the worst ones being blocking, the starting state of the file/database, an
d
> how much work shrink needs to do. For instance, if another process takes a
> lock that shrink needs, shrink will wait forever for that lock.
> Have you checked to make sure it is actually progressing and isn't blocked
?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
> news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> has
> v$session_longops)?.
>
>
We are shrinking a large databse using dbcc shrinkfile and it has been
running for hours. Is there any way of determining how long the job still ha
s
left to run (the equivalent of the Oracle dynamic view v$session_longops)?.
Thanks,
AndyNot that I am aware of. I avoid this by shrinking in increments of 100MB.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Hi,
In SQL Server we can not exactly say how the process is going to run. But as
Kevin mentioned you could try
shrinking the files by providing a lower value (500 MB or soo...). Ensure
that you do a Backup LOG command
before shrinking the tranasction log.
Thanks
Hari
Sql server MVP
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
but even that is just a percentage complete with an elapsed-time based
extrapolation of the completion time. Basically, there are far too many
variables to consider to have a hope of being able to predict the run-time -
the worst ones being blocking, the starting state of the file/database, and
how much work shrink needs to do. For instance, if another process takes a
lock that shrink needs, shrink will wait forever for that lock.
Have you checked to make sure it is actually progressing and isn't blocked?
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
has
> left to run (the equivalent of the Oracle dynamic view
v$session_longops)?.
> Thanks,
> Andy|||Not sure if this is related to the original post but I have SQL2000 SP4
install and have tried running DBCC SHRINKDATABASE (MyDB). This results is a
lock that is not released. Ent. Mgr shows "spid 58 (blocked by 58)" the Wait
Type is "PAGEIOLATCH_SH".
I think this maybe a bug in the SP4? I haven't had this problem with a DB
shrink before.
Regards,
John
"Paul S Randal [MS]" wrote:
> Not in SQL Server 2000. We've put progress reporting in for SQL Server 200
5,
> but even that is just a percentage complete with an elapsed-time based
> extrapolation of the completion time. Basically, there are far too many
> variables to consider to have a hope of being able to predict the run-time
-
> the worst ones being blocking, the starting state of the file/database, an
d
> how much work shrink needs to do. For instance, if another process takes a
> lock that shrink needs, shrink will wait forever for that lock.
> Have you checked to make sure it is actually progressing and isn't blocked
?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
> news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> has
> v$session_longops)?.
>
>
dbcc shrinkfile - how long will it take
Hi,
We are shrinking a large databse using dbcc shrinkfile and it has been
running for hours. Is there any way of determining how long the job still has
left to run (the equivalent of the Oracle dynamic view v$session_longops)?.
Thanks,
AndyNot that I am aware of. I avoid this by shrinking in increments of 100MB.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Hi,
In SQL Server we can not exactly say how the process is going to run. But as
Kevin mentioned you could try
shrinking the files by providing a lower value (500 MB or soo...). Ensure
that you do a Backup LOG command
before shrinking the tranasction log.
Thanks
Hari
Sql server MVP
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
but even that is just a percentage complete with an elapsed-time based
extrapolation of the completion time. Basically, there are far too many
variables to consider to have a hope of being able to predict the run-time -
the worst ones being blocking, the starting state of the file/database, and
how much work shrink needs to do. For instance, if another process takes a
lock that shrink needs, shrink will wait forever for that lock.
Have you checked to make sure it is actually progressing and isn't blocked?
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
has
> left to run (the equivalent of the Oracle dynamic view
v$session_longops)?.
> Thanks,
> Andy|||Not sure if this is related to the original post but I have SQL2000 SP4
install and have tried running DBCC SHRINKDATABASE (MyDB). This results is a
lock that is not released. Ent. Mgr shows "spid 58 (blocked by 58)" the Wait
Type is "PAGEIOLATCH_SH".
I think this maybe a bug in the SP4? I haven't had this problem with a DB
shrink before.
Regards,
John
"Paul S Randal [MS]" wrote:
> Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
> but even that is just a percentage complete with an elapsed-time based
> extrapolation of the completion time. Basically, there are far too many
> variables to consider to have a hope of being able to predict the run-time -
> the worst ones being blocking, the starting state of the file/database, and
> how much work shrink needs to do. For instance, if another process takes a
> lock that shrink needs, shrink will wait forever for that lock.
> Have you checked to make sure it is actually progressing and isn't blocked?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
> news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> > Hi,
> > We are shrinking a large databse using dbcc shrinkfile and it has been
> > running for hours. Is there any way of determining how long the job still
> has
> > left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> >
> > Thanks,
> > Andy
>
>
We are shrinking a large databse using dbcc shrinkfile and it has been
running for hours. Is there any way of determining how long the job still has
left to run (the equivalent of the Oracle dynamic view v$session_longops)?.
Thanks,
AndyNot that I am aware of. I avoid this by shrinking in increments of 100MB.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Hi,
In SQL Server we can not exactly say how the process is going to run. But as
Kevin mentioned you could try
shrinking the files by providing a lower value (500 MB or soo...). Ensure
that you do a Backup LOG command
before shrinking the tranasction log.
Thanks
Hari
Sql server MVP
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
but even that is just a percentage complete with an elapsed-time based
extrapolation of the completion time. Basically, there are far too many
variables to consider to have a hope of being able to predict the run-time -
the worst ones being blocking, the starting state of the file/database, and
how much work shrink needs to do. For instance, if another process takes a
lock that shrink needs, shrink will wait forever for that lock.
Have you checked to make sure it is actually progressing and isn't blocked?
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
has
> left to run (the equivalent of the Oracle dynamic view
v$session_longops)?.
> Thanks,
> Andy|||Not sure if this is related to the original post but I have SQL2000 SP4
install and have tried running DBCC SHRINKDATABASE (MyDB). This results is a
lock that is not released. Ent. Mgr shows "spid 58 (blocked by 58)" the Wait
Type is "PAGEIOLATCH_SH".
I think this maybe a bug in the SP4? I haven't had this problem with a DB
shrink before.
Regards,
John
"Paul S Randal [MS]" wrote:
> Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
> but even that is just a percentage complete with an elapsed-time based
> extrapolation of the completion time. Basically, there are far too many
> variables to consider to have a hope of being able to predict the run-time -
> the worst ones being blocking, the starting state of the file/database, and
> how much work shrink needs to do. For instance, if another process takes a
> lock that shrink needs, shrink will wait forever for that lock.
> Have you checked to make sure it is actually progressing and isn't blocked?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
> news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> > Hi,
> > We are shrinking a large databse using dbcc shrinkfile and it has been
> > running for hours. Is there any way of determining how long the job still
> has
> > left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> >
> > Thanks,
> > Andy
>
>
DBCC ShrinkFile
How long does it take to execute DBCC Shrinkfile(DB_FILE, emptyfile) on a 10GB datafiles? If you put your datafiles together with the tempdb datafiles on the same logical drive do we have a performance issue?
Thanks
The time taken for this activity is depends.
THe other part of ur question, Temp db should always be in seperate physical drive if possible for better performance. Datafile is Random read/write but temp db will have moreor less sequential read./write. So if u keep both in same drive it will have performance hit
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/tempdb_capacity_planning_index.mspx
http://msdn2.microsoft.com/en-us/library/ms345368.aspx
Madhu
dbcc shrinkfile
hi, can I shrink the database file, not only the trans log file?You can shrink individual files using DBCC SHRINKFILE. See the Books Online
for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"js" <js@.someone.com> wrote in message
news:udr2WIcWFHA.1044@.TK2MSFTNGP10.phx.gbl...
> hi, can I shrink the database file, not only the trans log file?
>|||Hi JS
Check for DBCC SHRINKDATABASE if you want to shrink the size of the data
files in the specified database
http://msdn.microsoft.com/library/d...
pd1.asp
If you are trying to reduce the size of a single file, then u need to check
DBCC SHRINKFILE
http://msdn.microsoft.com/library/e...asp?frame=true
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"js" wrote:
> hi, can I shrink the database file, not only the trans log file?
>
>|||Thanks,
When to shrink database and when to shrink file usually?
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
> Hi JS
> Check for DBCC SHRINKDATABASE if you want to shrink the size of the data
> files in the specified database
> http://msdn.microsoft.com/library/d...r />
_3pd1.asp
> If you are trying to reduce the size of a single file, then u need to
> check
> DBCC SHRINKFILE
> http://msdn.microsoft.com/library/e...s.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "js" wrote:
>|||Another question:
how to show the database files in size use query? Thanks.
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
> Hi JS
> Check for DBCC SHRINKDATABASE if you want to shrink the size of the data
> files in the specified database
> http://msdn.microsoft.com/library/d...r />
_3pd1.asp
> If you are trying to reduce the size of a single file, then u need to
> check
> DBCC SHRINKFILE
> http://msdn.microsoft.com/library/e...s.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "js" wrote:
>|||js
exec sp_helpdb 'databasename'
"js" <js@.someone@.hotmail.com> wrote in message
news:ednshxhWFHA.228@.TK2MSFTNGP12.phx.gbl...
> Another question:
> how to show the database files in size use query? Thanks.
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
http://msdn.microsoft.com/library/d...s_dbcc_3pd1.asp[c
olor=darkred]
http://msdn.microsoft.com/library/e...asp?frame=true
>|||Usually never. You really only want to shrink the files when they have
grown due to a one time data load or similar type operation. Since
operations such as reindexing require lots of free space int he files you
should always have plenty of free space available.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"js" <js@.someone@.hotmail.com> wrote in message
news:Oizi9mhWFHA.2448@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> When to shrink database and when to shrink file usually?
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
>|||In addition to sbcc shrinkfile, you can use dbcc showcontig to determine the
level of data and index fragmentation. Much space within a table can be
wasted if you perform frequent updates and deletes on a table with a
clustered index or insert rows in an order other than the clustered order.
Also, you can use dbcc indexdefrag to defragment indexes prior to shrinking
the file or database.
"js" <js@.someone.com> wrote in message
news:udr2WIcWFHA.1044@.TK2MSFTNGP10.phx.gbl...
> hi, can I shrink the database file, not only the trans log file?
>|||BTW - Do you know that you're a month ahead of the rest of the world?
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Tue, 14 Jun 2005 22:50:50 -0400, "js" <js@.someone.com> wrote:
>hi, can I shrink the database file, not only the trans log file?
>sql
for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"js" <js@.someone.com> wrote in message
news:udr2WIcWFHA.1044@.TK2MSFTNGP10.phx.gbl...
> hi, can I shrink the database file, not only the trans log file?
>|||Hi JS
Check for DBCC SHRINKDATABASE if you want to shrink the size of the data
files in the specified database
http://msdn.microsoft.com/library/d...
pd1.asp
If you are trying to reduce the size of a single file, then u need to check
DBCC SHRINKFILE
http://msdn.microsoft.com/library/e...asp?frame=true
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"js" wrote:
> hi, can I shrink the database file, not only the trans log file?
>
>|||Thanks,
When to shrink database and when to shrink file usually?
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
> Hi JS
> Check for DBCC SHRINKDATABASE if you want to shrink the size of the data
> files in the specified database
> http://msdn.microsoft.com/library/d...r />
_3pd1.asp
> If you are trying to reduce the size of a single file, then u need to
> check
> DBCC SHRINKFILE
> http://msdn.microsoft.com/library/e...s.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "js" wrote:
>|||Another question:
how to show the database files in size use query? Thanks.
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
> Hi JS
> Check for DBCC SHRINKDATABASE if you want to shrink the size of the data
> files in the specified database
> http://msdn.microsoft.com/library/d...r />
_3pd1.asp
> If you are trying to reduce the size of a single file, then u need to
> check
> DBCC SHRINKFILE
> http://msdn.microsoft.com/library/e...s.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "js" wrote:
>|||js
exec sp_helpdb 'databasename'
"js" <js@.someone@.hotmail.com> wrote in message
news:ednshxhWFHA.228@.TK2MSFTNGP12.phx.gbl...
> Another question:
> how to show the database files in size use query? Thanks.
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
http://msdn.microsoft.com/library/d...s_dbcc_3pd1.asp[c
olor=darkred]
http://msdn.microsoft.com/library/e...asp?frame=true
>|||Usually never. You really only want to shrink the files when they have
grown due to a one time data load or similar type operation. Since
operations such as reindexing require lots of free space int he files you
should always have plenty of free space available.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"js" <js@.someone@.hotmail.com> wrote in message
news:Oizi9mhWFHA.2448@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> When to shrink database and when to shrink file usually?
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
>|||In addition to sbcc shrinkfile, you can use dbcc showcontig to determine the
level of data and index fragmentation. Much space within a table can be
wasted if you perform frequent updates and deletes on a table with a
clustered index or insert rows in an order other than the clustered order.
Also, you can use dbcc indexdefrag to defragment indexes prior to shrinking
the file or database.
"js" <js@.someone.com> wrote in message
news:udr2WIcWFHA.1044@.TK2MSFTNGP10.phx.gbl...
> hi, can I shrink the database file, not only the trans log file?
>|||BTW - Do you know that you're a month ahead of the rest of the world?
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Tue, 14 Jun 2005 22:50:50 -0400, "js" <js@.someone.com> wrote:
>hi, can I shrink the database file, not only the trans log file?
>sql
DBCC Shrinkfile
I ran a dbcc shrinkfile after deleting data in order to take thte db down in size and it has been running for 2 days. Can I canel the command? Will the file be partially shrunk? Last year when we did this I ran a re-index command. Will I have to run a bakup or truncate log command? I have been trying to get the client to upgrade from SQL 7 but they just have not done it yet.follow up question. If I cancel how long will it take to finish the cancel operation?
DBCC Shrinkfile
Hi,
Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
the size of the log file in my SQL Server 2000 server? After backing up the
log file, I tried to shrink the physical size of the log file to 2000 MB but
it gave me an error (which I can't remember) even though the actual used size
(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
and this time it worked.
TIA.When you have problems shrinking, you should check for open
transactions using dbcc opentran
You also need to keep in mind that an active portion of the
log can't be impacted. So if you have an active portion of
the log at the end, you won't be able to shrink. You can
check this using dbcc loginfo(YourDatabase). A status of 2
indicates it's active.
Make sure you aren't needlessly shrinking. It should be more
of an ad hoc process - after some out of control process
blows out the size of the log type of thing.
You can find more information in the following article and a
more thorough explanation of what you see with dbcc loginfo:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
-Sue
On Wed, 11 Oct 2006 14:19:02 -0700, Rob
<Rob@.discussions.microsoft.com> wrote:
>Hi,
>Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
>the size of the log file in my SQL Server 2000 server? After backing up the
>log file, I tried to shrink the physical size of the log file to 2000 MB but
>it gave me an error (which I can't remember) even though the actual used size
>(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
>and this time it worked.
>TIA.
Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
the size of the log file in my SQL Server 2000 server? After backing up the
log file, I tried to shrink the physical size of the log file to 2000 MB but
it gave me an error (which I can't remember) even though the actual used size
(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
and this time it worked.
TIA.When you have problems shrinking, you should check for open
transactions using dbcc opentran
You also need to keep in mind that an active portion of the
log can't be impacted. So if you have an active portion of
the log at the end, you won't be able to shrink. You can
check this using dbcc loginfo(YourDatabase). A status of 2
indicates it's active.
Make sure you aren't needlessly shrinking. It should be more
of an ad hoc process - after some out of control process
blows out the size of the log type of thing.
You can find more information in the following article and a
more thorough explanation of what you see with dbcc loginfo:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
-Sue
On Wed, 11 Oct 2006 14:19:02 -0700, Rob
<Rob@.discussions.microsoft.com> wrote:
>Hi,
>Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
>the size of the log file in my SQL Server 2000 server? After backing up the
>log file, I tried to shrink the physical size of the log file to 2000 MB but
>it gave me an error (which I can't remember) even though the actual used size
>(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
>and this time it worked.
>TIA.
DBCC SHRINKFILE
I have recently changed the indexes ina large table in our database. I have
a lot of excess unused space ,approx 64GB, in the .MDF and want to give it
back to Windows 200 Server.
Our .MDF is not in the SQL DATA directory it is on another volume.
Is there a way to specify a path to another volume when using DBCC SHRINKFILE?
Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long time.
TIA,
-BenDBCC SHRINKFILE doesn't care where the file is as it uses the logical
filename. There is no reason you need to use SHRINKDATABASE.
--
Andrew J. Kelly SQL MVP
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:33AB1ADE-1D27-45F0-A054-B6F4B5F88727@.microsoft.com...
>I have recently changed the indexes ina large table in our database. I
>have
> a lot of excess unused space ,approx 64GB, in the .MDF and want to give
> it
> back to Windows 200 Server.
> Our .MDF is not in the SQL DATA directory it is on another volume.
> Is there a way to specify a path to another volume when using DBCC
> SHRINKFILE?
> Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long
> time.
> TIA,
> -Ben
a lot of excess unused space ,approx 64GB, in the .MDF and want to give it
back to Windows 200 Server.
Our .MDF is not in the SQL DATA directory it is on another volume.
Is there a way to specify a path to another volume when using DBCC SHRINKFILE?
Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long time.
TIA,
-BenDBCC SHRINKFILE doesn't care where the file is as it uses the logical
filename. There is no reason you need to use SHRINKDATABASE.
--
Andrew J. Kelly SQL MVP
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:33AB1ADE-1D27-45F0-A054-B6F4B5F88727@.microsoft.com...
>I have recently changed the indexes ina large table in our database. I
>have
> a lot of excess unused space ,approx 64GB, in the .MDF and want to give
> it
> back to Windows 200 Server.
> Our .MDF is not in the SQL DATA directory it is on another volume.
> Is there a way to specify a path to another volume when using DBCC
> SHRINKFILE?
> Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long
> time.
> TIA,
> -Ben
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
>
,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
I am looking to shrink a database file using DBCC Shrinkfile to try to reclaim some disk space. For some unexplained reason I have some unsettled feelings. I need to confirm:
1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
2. If I do not have a clue as to what target size I might go for, what is safe?
3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
--
Message posted via http://www.sqlmonster.com"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.com...
> I am looking to shrink a database file using DBCC Shrinkfile to try to
reclaim some disk space. For some unexplained reason I have some unsettled
feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
Shrinkfile?
>
1. That's the option you should use -- you might have to defragment some
indexes first, though. If you have heavy fragmentation you probably won't
reclaim much space (if any is available).
2. I usually use 1 if I want to reclaim ALL possible space. What that
option is actually doing is specifying a new minimum size for the database
(overriding whatever was set when it was created). So there's no real
danger in using whatever value you want...
3. None that I'm aware of -- probably a small performance hit if you run it
on a very active system, though...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||What version of sql are you using?
"Robert Richards via SQLMonster.com" wrote:
> I am looking to shrink a database file using DBCC Shrinkfile to try to reclaim some disk space. For some unexplained reason I have some unsettled feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is safe?
> 3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
> --
> Message posted via http://www.sqlmonster.com
>|||I am running SQL 2K.
--
Message posted via http://www.sqlmonster.com|||I am running SQL 2K.
--
Message posted via http://www.sqlmonster.com|||1. Sure it may free up some space but what happens when you need more room
in the db? You also need plenty of free space to do things like creating
and reindexing.
2. Reindexing an index will require at least 1.2 times the size of the
index or in the case of a clustered index the table itself.
3. Shrinking a file simply to gain more disk space is a bad idea in most
cases. There was some reason why the file got that large in the first place
and there is a good chance it will need that much space again. If you
shrink the file and use that space for something else what is going to
happen when the DB grows again? Chances are you will stop any modifications
on your sql server for that db. If you are that low on disk space I suggest
you get another disk before you really run into trouble.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.com...
>I am looking to shrink a database file using DBCC Shrinkfile to try to
>reclaim some disk space. For some unexplained reason I have some unsettled
>feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
> argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
> safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
> Shrinkfile?
> --
> Message posted via http://www.sqlmonster.comsql
1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
2. If I do not have a clue as to what target size I might go for, what is safe?
3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
--
Message posted via http://www.sqlmonster.com"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.com...
> I am looking to shrink a database file using DBCC Shrinkfile to try to
reclaim some disk space. For some unexplained reason I have some unsettled
feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
Shrinkfile?
>
1. That's the option you should use -- you might have to defragment some
indexes first, though. If you have heavy fragmentation you probably won't
reclaim much space (if any is available).
2. I usually use 1 if I want to reclaim ALL possible space. What that
option is actually doing is specifying a new minimum size for the database
(overriding whatever was set when it was created). So there's no real
danger in using whatever value you want...
3. None that I'm aware of -- probably a small performance hit if you run it
on a very active system, though...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||What version of sql are you using?
"Robert Richards via SQLMonster.com" wrote:
> I am looking to shrink a database file using DBCC Shrinkfile to try to reclaim some disk space. For some unexplained reason I have some unsettled feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is safe?
> 3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
> --
> Message posted via http://www.sqlmonster.com
>|||I am running SQL 2K.
--
Message posted via http://www.sqlmonster.com|||I am running SQL 2K.
--
Message posted via http://www.sqlmonster.com|||1. Sure it may free up some space but what happens when you need more room
in the db? You also need plenty of free space to do things like creating
and reindexing.
2. Reindexing an index will require at least 1.2 times the size of the
index or in the case of a clustered index the table itself.
3. Shrinking a file simply to gain more disk space is a bad idea in most
cases. There was some reason why the file got that large in the first place
and there is a good chance it will need that much space again. If you
shrink the file and use that space for something else what is going to
happen when the DB grows again? Chances are you will stop any modifications
on your sql server for that db. If you are that low on disk space I suggest
you get another disk before you really run into trouble.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.com...
>I am looking to shrink a database file using DBCC Shrinkfile to try to
>reclaim some disk space. For some unexplained reason I have some unsettled
>feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
> argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
> safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
> Shrinkfile?
> --
> Message posted via http://www.sqlmonster.comsql
DBCC SHRINKFILE
When I attempt to launch DBCC SHRINKFILE for any filename that contains a
period before the extension , (example : northwnd.ldf), I receive the error
message:
"Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.
What am I doing wrong? Thanks to everyone for being there to help.You need to use the ligical file name and not the physical.
Example:
use northwind
go
exec sp_helpdb northwind
go
dbcc shrinkfile (Northwind_log, 5)
go
AMB
"coenzyme" wrote:
> When I attempt to launch DBCC SHRINKFILE for any filename that contains a
> period before the extension , (example : northwnd.ldf), I receive the error
> message:
> "Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '.'.
> What am I doing wrong? Thanks to everyone for being there to help.
period before the extension , (example : northwnd.ldf), I receive the error
message:
"Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.
What am I doing wrong? Thanks to everyone for being there to help.You need to use the ligical file name and not the physical.
Example:
use northwind
go
exec sp_helpdb northwind
go
dbcc shrinkfile (Northwind_log, 5)
go
AMB
"coenzyme" wrote:
> When I attempt to launch DBCC SHRINKFILE for any filename that contains a
> period before the extension , (example : northwnd.ldf), I receive the error
> message:
> "Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '.'.
> What am I doing wrong? Thanks to everyone for being there to help.
DBCC shrinkfile
As I have discovered over several communications with you
(have been very helpful), its seems best that I have our
production database in a "Full" model recovery, with the
database file and transaction file in autogrowth mode.
Because my transaction file was extremely large I also
found out that I needed to do a "backup log" command in
order to shrink the transaction file. Another question:
Our database after a backup is 735mg and the database file
before the backup shows a size of 7Gigs. Is it necessary
to also shrink the database file. Does this mean that
there is a lot of space within the database that needs to
be cleaned out?
Thank you again,
RandyRand,
If your in Full recovery mode you need to issue a log backup on a regular
basis, not just to shrink it. And by the way backing it up does not shrink
it, that is a result of running DBCC SHRINKFILE. The backup log just
truncates the committed trans so it can be shrunk. When you say your
database is 735mg after a backup and 7Gigs before, where are you getting
these numbers?
--
Andrew J. Kelly
SQL Server MVP
"Rand Monroe" <randmo@.ix.netcom.com> wrote in message
news:042501c357c1$daf6ae10$a601280a@.phx.gbl...
> As I have discovered over several communications with you
> (have been very helpful), its seems best that I have our
> production database in a "Full" model recovery, with the
> database file and transaction file in autogrowth mode.
> Because my transaction file was extremely large I also
> found out that I needed to do a "backup log" command in
> order to shrink the transaction file. Another question:
> Our database after a backup is 735mg and the database file
> before the backup shows a size of 7Gigs. Is it necessary
> to also shrink the database file. Does this mean that
> there is a lot of space within the database that needs to
> be cleaned out?
> Thank you again,
> Randy
(have been very helpful), its seems best that I have our
production database in a "Full" model recovery, with the
database file and transaction file in autogrowth mode.
Because my transaction file was extremely large I also
found out that I needed to do a "backup log" command in
order to shrink the transaction file. Another question:
Our database after a backup is 735mg and the database file
before the backup shows a size of 7Gigs. Is it necessary
to also shrink the database file. Does this mean that
there is a lot of space within the database that needs to
be cleaned out?
Thank you again,
RandyRand,
If your in Full recovery mode you need to issue a log backup on a regular
basis, not just to shrink it. And by the way backing it up does not shrink
it, that is a result of running DBCC SHRINKFILE. The backup log just
truncates the committed trans so it can be shrunk. When you say your
database is 735mg after a backup and 7Gigs before, where are you getting
these numbers?
--
Andrew J. Kelly
SQL Server MVP
"Rand Monroe" <randmo@.ix.netcom.com> wrote in message
news:042501c357c1$daf6ae10$a601280a@.phx.gbl...
> As I have discovered over several communications with you
> (have been very helpful), its seems best that I have our
> production database in a "Full" model recovery, with the
> database file and transaction file in autogrowth mode.
> Because my transaction file was extremely large I also
> found out that I needed to do a "backup log" command in
> order to shrink the transaction file. Another question:
> Our database after a backup is 735mg and the database file
> before the backup shows a size of 7Gigs. Is it necessary
> to also shrink the database file. Does this mean that
> there is a lot of space within the database that needs to
> be cleaned out?
> Thank you again,
> Randy
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
>
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
A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
goHi
Execute DBCC SHRINKFILE when there is no active transacton in progress for that database. Check this by executing
DBCC OPENTRAN(DBNAME). It can be a replication process or it can can be a normal user connecting to the server and using some DML.
So execute the SHRINKFILE command when there is no activity in the database. Preferably do not schedule this as a job, rather do it manually if it is a production serve
Thank
Har
MCDB
-- KC wrote: --
A few days ago, I made some structure changes to 5 tables and adde
some indexes to them
This caused the mdf file size to increase from 82GB to 109GB. Becaus
our development and training boxes do not have enough disk space,
tried to shrink the file so that the database can fit into th
development box when I do a restore
I created a job to run the following at 3AM
DBCC SHRINKFILE (N'Nu_Data', 85000
At 6AM, the job crashed with the following error
Transaction (Process ID 66) was deadlocked on lock resources wit
another process and has been chosen as the deadlock victim. Rerun th
transaction
Our database is running transactional replication. Do I need to paus
the replication and set the database in single user mode befor
executing the DBCC SHRINKFILE (N'N_Data', 85000)
alter database N
set single_user with ROLLBACK IMMEDIATE
g
DBCC SHRINKFILE (N'Nu_Data', 85000
g
alter database N
set multi_use
g
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
goHi
Execute DBCC SHRINKFILE when there is no active transacton in progress for that database. Check this by executing
DBCC OPENTRAN(DBNAME). It can be a replication process or it can can be a normal user connecting to the server and using some DML.
So execute the SHRINKFILE command when there is no activity in the database. Preferably do not schedule this as a job, rather do it manually if it is a production serve
Thank
Har
MCDB
-- KC wrote: --
A few days ago, I made some structure changes to 5 tables and adde
some indexes to them
This caused the mdf file size to increase from 82GB to 109GB. Becaus
our development and training boxes do not have enough disk space,
tried to shrink the file so that the database can fit into th
development box when I do a restore
I created a job to run the following at 3AM
DBCC SHRINKFILE (N'Nu_Data', 85000
At 6AM, the job crashed with the following error
Transaction (Process ID 66) was deadlocked on lock resources wit
another process and has been chosen as the deadlock victim. Rerun th
transaction
Our database is running transactional replication. Do I need to paus
the replication and set the database in single user mode befor
executing the DBCC SHRINKFILE (N'N_Data', 85000)
alter database N
set single_user with ROLLBACK IMMEDIATE
g
DBCC SHRINKFILE (N'Nu_Data', 85000
g
alter database N
set multi_use
g
Subscribe to:
Posts (Atom)