dbcc shrinkdb

hi,
I need to shring a SQL 2000 db by 50GB. Is it better to start sql in single
user mode when doing such action?I believe you will find that the database will be put into single user mode
for the life of your shrink session automatically. But it might simplify
matters to do it manually.
Joseph R.P. Maloney, CSP,CCP,CDP
"stoney" wrote:
> hi,
> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in single
> user mode when doing such action?|||Thanks very much for your prompt response. Does the system have a better
performance if I shrink the database more often? Say if I shrink the
database every 3 months.
Thanks.
"jrpm" <jrpm@.discussions.microsoft.com> wrote in message
news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>I believe you will find that the database will be put into single user mode
> for the life of your shrink session automatically. But it might simplify
> matters to do it manually.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "stoney" wrote:
>> hi,
>> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> single
>> user mode when doing such action?|||In my humble opinion database server perform better with their logs shrunk
and stored in medias.
The data are more important than the logs.
and logs are overhead to database services
thats why a sound backup and restore strategy must implemented
--
thanks,
--
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Diane Walker" wrote:
> Thanks very much for your prompt response. Does the system have a better
> performance if I shrink the database more often? Say if I shrink the
> database every 3 months.
> Thanks.
> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
> >I believe you will find that the database will be put into single user mode
> > for the life of your shrink session automatically. But it might simplify
> > matters to do it manually.
> >
> >
> > --
> > Joseph R.P. Maloney, CSP,CCP,CDP
> >
> >
> > "stoney" wrote:
> >
> >> hi,
> >>
> >> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
> >> single
> >> user mode when doing such action?
>
>|||Shrinking does not require the db to be in single user mode and there is
nothing that does that automatically.
--
Andrew J. Kelly SQL MVP
"jrpm" <jrpm@.discussions.microsoft.com> wrote in message
news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>I believe you will find that the database will be put into single user mode
> for the life of your shrink session automatically. But it might simplify
> matters to do it manually.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "stoney" wrote:
>> hi,
>> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> single
>> user mode when doing such action?|||Actually you get better performance if you don't shrink it at all in most
cases. If you need to keep shrinking it that is a pretty good indication it
wants to be larger than you think it should be. So leave it alone. See
this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Andrew J. Kelly SQL MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:OI6jb4EwFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Thanks very much for your prompt response. Does the system have a better
> performance if I shrink the database more often? Say if I shrink the
> database every 3 months.
> Thanks.
> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>>I believe you will find that the database will be put into single user
>>mode
>> for the life of your shrink session automatically. But it might simplify
>> matters to do it manually.
>>
>> --
>> Joseph R.P. Maloney, CSP,CCP,CDP
>>
>> "stoney" wrote:
>> hi,
>> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> single
>> user mode when doing such action?
>|||Just the opposite. Transaction logs are crucial to performance on SQL
Server. Since all data manipulation is sent to the transaction log first it
will slow everything else down if it can not write fast enough. By
shrinking the log files too much you will force them to grow just when it
needs to write entries to the log file. They then must wait on that
operation to complete before they can continue.
--
Andrew J. Kelly SQL MVP
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:986EDB0C-E8C8-47F5-8646-175E0CE40A22@.microsoft.com...
> In my humble opinion database server perform better with their logs shrunk
> and stored in medias.
> The data are more important than the logs.
> and logs are overhead to database services
> thats why a sound backup and restore strategy must implemented
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Diane Walker" wrote:
>> Thanks very much for your prompt response. Does the system have a better
>> performance if I shrink the database more often? Say if I shrink the
>> database every 3 months.
>> Thanks.
>> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
>> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>> >I believe you will find that the database will be put into single user
>> >mode
>> > for the life of your shrink session automatically. But it might
>> > simplify
>> > matters to do it manually.
>> >
>> >
>> > --
>> > Joseph R.P. Maloney, CSP,CCP,CDP
>> >
>> >
>> > "stoney" wrote:
>> >
>> >> hi,
>> >>
>> >> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> >> single
>> >> user mode when doing such action?
>>|||Thanks very much for the information.
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:986EDB0C-E8C8-47F5-8646-175E0CE40A22@.microsoft.com...
> In my humble opinion database server perform better with their logs shrunk
> and stored in medias.
> The data are more important than the logs.
> and logs are overhead to database services
> thats why a sound backup and restore strategy must implemented
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Diane Walker" wrote:
>> Thanks very much for your prompt response. Does the system have a better
>> performance if I shrink the database more often? Say if I shrink the
>> database every 3 months.
>> Thanks.
>> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
>> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>> >I believe you will find that the database will be put into single user
>> >mode
>> > for the life of your shrink session automatically. But it might
>> > simplify
>> > matters to do it manually.
>> >
>> >
>> > --
>> > Joseph R.P. Maloney, CSP,CCP,CDP
>> >
>> >
>> > "stoney" wrote:
>> >
>> >> hi,
>> >>
>> >> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> >> single
>> >> user mode when doing such action?
>>|||My database file is 55 MB. The transaction log is 14 GB. Do you recommend
that I leave the transaction log alone? Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uKa26MFwFHA.2516@.TK2MSFTNGP12.phx.gbl...
> Actually you get better performance if you don't shrink it at all in most
> cases. If you need to keep shrinking it that is a pretty good indication
> it wants to be larger than you think it should be. So leave it alone.
> See this:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:OI6jb4EwFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> Thanks very much for your prompt response. Does the system have a better
>> performance if I shrink the database more often? Say if I shrink the
>> database every 3 months.
>> Thanks.
>> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
>> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>>I believe you will find that the database will be put into single user
>>mode
>> for the life of your shrink session automatically. But it might
>> simplify
>> matters to do it manually.
>>
>> --
>> Joseph R.P. Maloney, CSP,CCP,CDP
>>
>> "stoney" wrote:
>> hi,
>> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> single
>> user mode when doing such action?
>>
>|||No that is excessive and warrants a shrinking<g>. But why did it get there
in the first place? Chances are you are in FULL recovery mode and only issue
Full backups. You need to issue regular (at least several times a day) LOG
backups in order for the log to truncate the committed transactions and
reuse the space. If you don't want to do Log backups then change the
recovery mode to SIMPLE and it will keep it in check for you.
--
Andrew J. Kelly SQL MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:u3917jGwFHA.3720@.TK2MSFTNGP14.phx.gbl...
> My database file is 55 MB. The transaction log is 14 GB. Do you
> recommend that I leave the transaction log alone? Thanks.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uKa26MFwFHA.2516@.TK2MSFTNGP12.phx.gbl...
>> Actually you get better performance if you don't shrink it at all in most
>> cases. If you need to keep shrinking it that is a pretty good indication
>> it wants to be larger than you think it should be. So leave it alone.
>> See this:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:OI6jb4EwFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> Thanks very much for your prompt response. Does the system have a
>> better performance if I shrink the database more often? Say if I shrink
>> the database every 3 months.
>> Thanks.
>> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
>> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>>I believe you will find that the database will be put into single user
>>mode
>> for the life of your shrink session automatically. But it might
>> simplify
>> matters to do it manually.
>>
>> --
>> Joseph R.P. Maloney, CSP,CCP,CDP
>>
>> "stoney" wrote:
>> hi,
>> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> single
>> user mode when doing such action?
>>
>>
>|||Thanks very much for your prompt response. I have no idea why the log file
gets that big. What should I be doing so that the log file would not get
this big? We have had this database for 1 year and 3 months. I am doing
the full backup everyday, running Backexec from Veritas.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OltiB%23HwFHA.3688@.tk2msftngp13.phx.gbl...
> No that is excessive and warrants a shrinking<g>. But why did it get
> there in the first place? Chances are you are in FULL recovery mode and
> only issue Full backups. You need to issue regular (at least several
> times a day) LOG backups in order for the log to truncate the committed
> transactions and reuse the space. If you don't want to do Log backups
> then change the recovery mode to SIMPLE and it will keep it in check for
> you.
> --
> Andrew J. Kelly SQL MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:u3917jGwFHA.3720@.TK2MSFTNGP14.phx.gbl...
>> My database file is 55 MB. The transaction log is 14 GB. Do you
>> recommend that I leave the transaction log alone? Thanks.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uKa26MFwFHA.2516@.TK2MSFTNGP12.phx.gbl...
>> Actually you get better performance if you don't shrink it at all in
>> most cases. If you need to keep shrinking it that is a pretty good
>> indication it wants to be larger than you think it should be. So leave
>> it alone. See this:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:OI6jb4EwFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> Thanks very much for your prompt response. Does the system have a
>> better performance if I shrink the database more often? Say if I
>> shrink the database every 3 months.
>> Thanks.
>> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
>> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>>I believe you will find that the database will be put into single user
>>mode
>> for the life of your shrink session automatically. But it might
>> simplify
>> matters to do it manually.
>>
>> --
>> Joseph R.P. Maloney, CSP,CCP,CDP
>>
>> "stoney" wrote:
>> hi,
>> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> single
>> user mode when doing such action?
>>
>>
>>
>|||I listed what I feel the issue was in the last post<g>. Change your
recovery mode to Simple if you don't do log backups.
--
Andrew J. Kelly SQL MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:u65jHzSwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Thanks very much for your prompt response. I have no idea why the log
> file gets that big. What should I be doing so that the log file would not
> get this big? We have had this database for 1 year and 3 months. I am
> doing the full backup everyday, running Backexec from Veritas.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltiB%23HwFHA.3688@.tk2msftngp13.phx.gbl...
>> No that is excessive and warrants a shrinking<g>. But why did it get
>> there in the first place? Chances are you are in FULL recovery mode and
>> only issue Full backups. You need to issue regular (at least several
>> times a day) LOG backups in order for the log to truncate the committed
>> transactions and reuse the space. If you don't want to do Log backups
>> then change the recovery mode to SIMPLE and it will keep it in check for
>> you.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:u3917jGwFHA.3720@.TK2MSFTNGP14.phx.gbl...
>> My database file is 55 MB. The transaction log is 14 GB. Do you
>> recommend that I leave the transaction log alone? Thanks.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uKa26MFwFHA.2516@.TK2MSFTNGP12.phx.gbl...
>> Actually you get better performance if you don't shrink it at all in
>> most cases. If you need to keep shrinking it that is a pretty good
>> indication it wants to be larger than you think it should be. So leave
>> it alone. See this:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:OI6jb4EwFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> Thanks very much for your prompt response. Does the system have a
>> better performance if I shrink the database more often? Say if I
>> shrink the database every 3 months.
>> Thanks.
>> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
>> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>>I believe you will find that the database will be put into single user
>>mode
>> for the life of your shrink session automatically. But it might
>> simplify
>> matters to do it manually.
>>
>> --
>> Joseph R.P. Maloney, CSP,CCP,CDP
>>
>> "stoney" wrote:
>>> hi,
>>>
>>> I need to shring a SQL 2000 db by 50GB. Is it better to start sql
>>> in single
>>> user mode when doing such action?
>>
>>
>>
>>
>