After completing some intensive DB maintenance I would like to shrink a 400G
B
database with 200GB free space. I am looking for a ballpark figure for how
long something like that would take on a standard 2CPU 8MB RAM server. If
anyone else has done something similar how long has it taken? I understand
that shrinkfile depends on a lot of factors - I am mainly looking for an ide
a
- will it take a day? a weekend? a week?
ThanksBased on my experience it will take couple of tens minutes up to couple of
hours,
depends on hardware, current utilization, etc.
Regards.
"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 40
0GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an i
dea
> - will it take a day? a weekend? a week?
> Thanks|||Hi,
My 8 CPU , 8GB RAM machine took me 15 minutes to shrink from 600 GB 290 GB.
But I recommend you to shrink in
pieces of 5000 MB each.
Thanks
Hari
SQLQ Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||many people would not recommend shrinking at all unless you have a very good
reason to do so.
if you're running out of diskspace, consider buying additional disks if you
can as opposed to shrinking the DB.
Greg Jackson
PDX, Oregon|||You said yourself it depends on a lot of factors so it is very hard to give
you an accurate answer, even a ballpark. You stated that you just did some
extensive maintenance so I assume this was mainly reindexing. Reindexing
will defragment your indexes but shrinking the file will just fragment them
again. So you will undo most of what you spent all that time working on.
The DB needs plenty of free space to operate normally. While 200GB of free
space on a 200GB db may be more than you actually need it doesn't hurt to
have extra free space. Have a look at this:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 40
0GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken?
I'll chime in by saying that it's really variable. For example, my 4CPU 4GB
RAM server recently took a day to shrink an 80GB file down to 50GB.
One gotcha: it is possible for a SHRINKFILE to deadlock on a SELECT. In
that case,
the SHRINKFILE will be terminated in favor of the SELECT, and you'll have to
start
the SHRINKFILE again. So I'd recommend shrinking when the database is
relatively unused.
-- J
Showing posts with label space. Show all posts
Showing posts with label space. Show all posts
Tuesday, March 20, 2012
ballpark time for shrinkfile
After completing some intensive DB maintenance I would like to shrink a 400GB
database with 200GB free space. I am looking for a ballpark figure for how
long something like that would take on a standard 2CPU 8MB RAM server. If
anyone else has done something similar how long has it taken? I understand
that shrinkfile depends on a lot of factors - I am mainly looking for an idea
- will it take a day? a weekend? a week?
ThanksBased on my experience it will take couple of tens minutes up to couple of
hours,
depends on hardware, current utilization, etc.
Regards.
"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an idea
> - will it take a day? a weekend? a week?
> Thanks|||Hi,
My 8 CPU , 8GB RAM machine took me 15 minutes to shrink from 600 GB 290 GB.
But I recommend you to shrink in
pieces of 5000 MB each.
Thanks
Hari
SQLQ Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||many people would not recommend shrinking at all unless you have a very good
reason to do so.
if you're running out of diskspace, consider buying additional disks if you
can as opposed to shrinking the DB.
Greg Jackson
PDX, Oregon|||You said yourself it depends on a lot of factors so it is very hard to give
you an accurate answer, even a ballpark. You stated that you just did some
extensive maintenance so I assume this was mainly reindexing. Reindexing
will defragment your indexes but shrinking the file will just fragment them
again. So you will undo most of what you spent all that time working on.
The DB needs plenty of free space to operate normally. While 200GB of free
space on a 200GB db may be more than you actually need it doesn't hurt to
have extra free space. Have a look at this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken?
I'll chime in by saying that it's really variable. For example, my 4CPU 4GB
RAM server recently took a day to shrink an 80GB file down to 50GB.
One gotcha: it is possible for a SHRINKFILE to deadlock on a SELECT. In
that case,
the SHRINKFILE will be terminated in favor of the SELECT, and you'll have to
start
the SHRINKFILE again. So I'd recommend shrinking when the database is
relatively unused.
-- J
database with 200GB free space. I am looking for a ballpark figure for how
long something like that would take on a standard 2CPU 8MB RAM server. If
anyone else has done something similar how long has it taken? I understand
that shrinkfile depends on a lot of factors - I am mainly looking for an idea
- will it take a day? a weekend? a week?
ThanksBased on my experience it will take couple of tens minutes up to couple of
hours,
depends on hardware, current utilization, etc.
Regards.
"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an idea
> - will it take a day? a weekend? a week?
> Thanks|||Hi,
My 8 CPU , 8GB RAM machine took me 15 minutes to shrink from 600 GB 290 GB.
But I recommend you to shrink in
pieces of 5000 MB each.
Thanks
Hari
SQLQ Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||many people would not recommend shrinking at all unless you have a very good
reason to do so.
if you're running out of diskspace, consider buying additional disks if you
can as opposed to shrinking the DB.
Greg Jackson
PDX, Oregon|||You said yourself it depends on a lot of factors so it is very hard to give
you an accurate answer, even a ballpark. You stated that you just did some
extensive maintenance so I assume this was mainly reindexing. Reindexing
will defragment your indexes but shrinking the file will just fragment them
again. So you will undo most of what you spent all that time working on.
The DB needs plenty of free space to operate normally. While 200GB of free
space on a 200GB db may be more than you actually need it doesn't hurt to
have extra free space. Have a look at this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken?
I'll chime in by saying that it's really variable. For example, my 4CPU 4GB
RAM server recently took a day to shrink an 80GB file down to 50GB.
One gotcha: it is possible for a SHRINKFILE to deadlock on a SELECT. In
that case,
the SHRINKFILE will be terminated in favor of the SELECT, and you'll have to
start
the SHRINKFILE again. So I'd recommend shrinking when the database is
relatively unused.
-- J
ballpark time for shrinkfile
After completing some intensive DB maintenance I would like to shrink a 400GB
database with 200GB free space. I am looking for a ballpark figure for how
long something like that would take on a standard 2CPU 8MB RAM server. If
anyone else has done something similar how long has it taken? I understand
that shrinkfile depends on a lot of factors - I am mainly looking for an idea
- will it take a day? a weekend? a week?
Thanks
Based on my experience it will take couple of tens minutes up to couple of
hours,
depends on hardware, current utilization, etc.
Regards.
"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an idea
> - will it take a day? a weekend? a week?
> Thanks
|||Hi,
My 8 CPU , 8GB RAM machine took me 15 minutes to shrink from 600 GB 290 GB.
But I recommend you to shrink in
pieces of 5000 MB each.
Thanks
Hari
SQLQ Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks
|||many people would not recommend shrinking at all unless you have a very good
reason to do so.
if you're running out of diskspace, consider buying additional disks if you
can as opposed to shrinking the DB.
Greg Jackson
PDX, Oregon
|||You said yourself it depends on a lot of factors so it is very hard to give
you an accurate answer, even a ballpark. You stated that you just did some
extensive maintenance so I assume this was mainly reindexing. Reindexing
will defragment your indexes but shrinking the file will just fragment them
again. So you will undo most of what you spent all that time working on.
The DB needs plenty of free space to operate normally. While 200GB of free
space on a 200GB db may be more than you actually need it doesn't hurt to
have extra free space. Have a look at this:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks
|||"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken?
I'll chime in by saying that it's really variable. For example, my 4CPU 4GB
RAM server recently took a day to shrink an 80GB file down to 50GB.
One gotcha: it is possible for a SHRINKFILE to deadlock on a SELECT. In
that case,
the SHRINKFILE will be terminated in favor of the SELECT, and you'll have to
start
the SHRINKFILE again. So I'd recommend shrinking when the database is
relatively unused.
-- J
database with 200GB free space. I am looking for a ballpark figure for how
long something like that would take on a standard 2CPU 8MB RAM server. If
anyone else has done something similar how long has it taken? I understand
that shrinkfile depends on a lot of factors - I am mainly looking for an idea
- will it take a day? a weekend? a week?
Thanks
Based on my experience it will take couple of tens minutes up to couple of
hours,
depends on hardware, current utilization, etc.
Regards.
"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an idea
> - will it take a day? a weekend? a week?
> Thanks
|||Hi,
My 8 CPU , 8GB RAM machine took me 15 minutes to shrink from 600 GB 290 GB.
But I recommend you to shrink in
pieces of 5000 MB each.
Thanks
Hari
SQLQ Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks
|||many people would not recommend shrinking at all unless you have a very good
reason to do so.
if you're running out of diskspace, consider buying additional disks if you
can as opposed to shrinking the DB.
Greg Jackson
PDX, Oregon
|||You said yourself it depends on a lot of factors so it is very hard to give
you an accurate answer, even a ballpark. You stated that you just did some
extensive maintenance so I assume this was mainly reindexing. Reindexing
will defragment your indexes but shrinking the file will just fragment them
again. So you will undo most of what you spent all that time working on.
The DB needs plenty of free space to operate normally. While 200GB of free
space on a 200GB db may be more than you actually need it doesn't hurt to
have extra free space. Have a look at this:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks
|||"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken?
I'll chime in by saying that it's really variable. For example, my 4CPU 4GB
RAM server recently took a day to shrink an 80GB file down to 50GB.
One gotcha: it is possible for a SHRINKFILE to deadlock on a SELECT. In
that case,
the SHRINKFILE will be terminated in favor of the SELECT, and you'll have to
start
the SHRINKFILE again. So I'd recommend shrinking when the database is
relatively unused.
-- J
balloning t-log
Every Satuday evening my t-log balloons and consume my available drive
space. What can I do to monitor the og so i can try to see what is
causing this to keep happening.
I have interrogated the application support ema to see if they do any
large amount of work that would cause the problem but only "not me"
answers
any help/suggestions are greatly appreciated.Can you set up a trace to see who is doing what? My guess is something like
index rebuilds.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
> Every Satuday evening my t-log balloons and consume my available drive
> space. What can I do to monitor the og so i can try to see what is
> causing this to keep happening.
> I have interrogated the application support ema to see if they do any
> large amount of work that would cause the problem but only "not me"
> answers
> any help/suggestions are greatly appreciated.|||On Jan 20, 7:41=A0pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Can you set up a trace to see who is doing what? My guess is something lik=e
> index rebuilds.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelan=
ey.com
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
>
> > Every Satuday evening my t-log balloons and consume my available drive
> > space. What can I do to monitor the og so i can try to see what is
> > causing this to keep happening.
> > I have interrogated the application support ema to see if they do any
> > large amount of work =A0that would cause the problem but only "not me"
> > answers
> > any help/suggestions are greatly appreciated.- Hide quoted text -
> - Show quoted text -
I do perform maintenance at 1am on Sunday mornings which does an index
reorg but not a rebuild. would performing maintenance more often help
or will that balloon the logs suring the production week which I don't
want to happen.
Any suggestions are welcomed.|||Yes, re-organising OR rebuilding databases does cause massive transaction
logging.
Instead of actually rebuilding or re-organising your indexes, you might be
better off just rebuilding index statistics (which doesn't generate such
massive log entries). You should also look into tuing the queries which are
scanning your tables - these queries are the ones benefiting from the
re-orgs / rebuilds but they'd probably run much better if they just had a
good index.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:f69cf48c-bdeb-4026-9bb0-bd01ae9627b5@.c23g2000hsa.googlegroups.com...
On Jan 20, 7:41 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Can you set up a trace to see who is doing what? My guess is something
> like
> index rebuilds.
> --
> HTH
> Kalen Delaney, SQL Server
> MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
>
> > Every Satuday evening my t-log balloons and consume my available drive
> > space. What can I do to monitor the og so i can try to see what is
> > causing this to keep happening.
> > I have interrogated the application support ema to see if they do any
> > large amount of work that would cause the problem but only "not me"
> > answers
> > any help/suggestions are greatly appreciated.- Hide quoted text -
> - Show quoted text -
I do perform maintenance at 1am on Sunday mornings which does an index
reorg but not a rebuild. would performing maintenance more often help
or will that balloon the logs suring the production week which I don't
want to happen.
Any suggestions are welcomed.|||Also use the script from BOL that checks fragmentation first and only redoes
stuff that needs it. Also, if you avoid clustered indexes (unless severely
fragmented) that could keep logging down too.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OBPTWM9WIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Yes, re-organising OR rebuilding databases does cause massive transaction
> logging.
> Instead of actually rebuilding or re-organising your indexes, you might be
> better off just rebuilding index statistics (which doesn't generate such
> massive log entries). You should also look into tuing the queries which
> are scanning your tables - these queries are the ones benefiting from the
> re-orgs / rebuilds but they'd probably run much better if they just had a
> good index.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "NC3" <ncoleman3@.yahoo.com> wrote in message
> news:f69cf48c-bdeb-4026-9bb0-bd01ae9627b5@.c23g2000hsa.googlegroups.com...
> On Jan 20, 7:41 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
>> Can you set up a trace to see who is doing what? My guess is something
>> like
>> index rebuilds.
>> --
>> HTH
>> Kalen Delaney, SQL Server
>> MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
>> "NC3" <ncolem...@.yahoo.com> wrote in message
>> news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
>>
>> > Every Satuday evening my t-log balloons and consume my available drive
>> > space. What can I do to monitor the og so i can try to see what is
>> > causing this to keep happening.
>> > I have interrogated the application support ema to see if they do any
>> > large amount of work that would cause the problem but only "not me"
>> > answers
>> > any help/suggestions are greatly appreciated.- Hide quoted text -
>> - Show quoted text -
> I do perform maintenance at 1am on Sunday mornings which does an index
> reorg but not a rebuild. would performing maintenance more often help
> or will that balloon the logs suring the production week which I don't
> want to happen.
> Any suggestions are welcomed.
>|||On Jan 20, 9:14=A0pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> Yes, re-organising OR rebuilding databases does cause massive transaction
> logging.
> Instead of actually rebuilding or re-organising your indexes, you might be=
> better off just rebuilding index statistics (which doesn't generate such
> massive log entries). You should also look into tuing the queries which ar=e
> scanning your tables - these queries are the ones benefiting from the
> re-orgs / rebuilds but they'd probably run much better if they just had a
> good index.
> Regards,
> Greg Linwood
> SQL Server MVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performancehttp://www.SQLBenchmarkPro.com
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:f69cf48c-bdeb-4026-9bb0-bd01ae9627b5@.c23g2000hsa.googlegroups.com...
> On Jan 20, 7:41 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
>
>
> > Can you set up a trace to see who is doing what? My guess is something
> > like
> > index rebuilds.
> > --
> > HTH
> > Kalen Delaney, SQL Server
> > MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
> > "NC3" <ncolem...@.yahoo.com> wrote in message
> >news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...=
> > > Every Satuday evening my t-log balloons and consume my available drive=
> > > space. What can I do to monitor the og so i can try to see what is
> > > causing this to keep happening.
> > > I have interrogated the application support ema to see if they do any
> > > large amount of work that would cause the problem but only "not me"
> > > answers
> > > any help/suggestions are greatly appreciated.- Hide quoted text -
> > - Show quoted text -
> I do perform maintenance at 1am on Sunday mornings which does an index
> reorg but not a rebuild. would performing maintenance more often help
> or will that balloon the logs suring the production week which I don't
> want to happen.
> Any suggestions are welcomed.- Hide quoted text -
> - Show quoted text -
I appreciate your comments and will look into the suggestions that I
received. Tuning the queries are a problem since they belong to a
third party application. However I will learn how to use the SQL
traces suggested by Kalen Delaney, and reveiw BOL for fragmentation
queries as well as the rebuild stats.
New things to learn are always exciting (and frustrating)!
Thanks again.sql
space. What can I do to monitor the og so i can try to see what is
causing this to keep happening.
I have interrogated the application support ema to see if they do any
large amount of work that would cause the problem but only "not me"
answers
any help/suggestions are greatly appreciated.Can you set up a trace to see who is doing what? My guess is something like
index rebuilds.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
> Every Satuday evening my t-log balloons and consume my available drive
> space. What can I do to monitor the og so i can try to see what is
> causing this to keep happening.
> I have interrogated the application support ema to see if they do any
> large amount of work that would cause the problem but only "not me"
> answers
> any help/suggestions are greatly appreciated.|||On Jan 20, 7:41=A0pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Can you set up a trace to see who is doing what? My guess is something lik=e
> index rebuilds.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelan=
ey.com
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
>
> > Every Satuday evening my t-log balloons and consume my available drive
> > space. What can I do to monitor the og so i can try to see what is
> > causing this to keep happening.
> > I have interrogated the application support ema to see if they do any
> > large amount of work =A0that would cause the problem but only "not me"
> > answers
> > any help/suggestions are greatly appreciated.- Hide quoted text -
> - Show quoted text -
I do perform maintenance at 1am on Sunday mornings which does an index
reorg but not a rebuild. would performing maintenance more often help
or will that balloon the logs suring the production week which I don't
want to happen.
Any suggestions are welcomed.|||Yes, re-organising OR rebuilding databases does cause massive transaction
logging.
Instead of actually rebuilding or re-organising your indexes, you might be
better off just rebuilding index statistics (which doesn't generate such
massive log entries). You should also look into tuing the queries which are
scanning your tables - these queries are the ones benefiting from the
re-orgs / rebuilds but they'd probably run much better if they just had a
good index.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:f69cf48c-bdeb-4026-9bb0-bd01ae9627b5@.c23g2000hsa.googlegroups.com...
On Jan 20, 7:41 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Can you set up a trace to see who is doing what? My guess is something
> like
> index rebuilds.
> --
> HTH
> Kalen Delaney, SQL Server
> MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
>
> > Every Satuday evening my t-log balloons and consume my available drive
> > space. What can I do to monitor the og so i can try to see what is
> > causing this to keep happening.
> > I have interrogated the application support ema to see if they do any
> > large amount of work that would cause the problem but only "not me"
> > answers
> > any help/suggestions are greatly appreciated.- Hide quoted text -
> - Show quoted text -
I do perform maintenance at 1am on Sunday mornings which does an index
reorg but not a rebuild. would performing maintenance more often help
or will that balloon the logs suring the production week which I don't
want to happen.
Any suggestions are welcomed.|||Also use the script from BOL that checks fragmentation first and only redoes
stuff that needs it. Also, if you avoid clustered indexes (unless severely
fragmented) that could keep logging down too.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OBPTWM9WIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Yes, re-organising OR rebuilding databases does cause massive transaction
> logging.
> Instead of actually rebuilding or re-organising your indexes, you might be
> better off just rebuilding index statistics (which doesn't generate such
> massive log entries). You should also look into tuing the queries which
> are scanning your tables - these queries are the ones benefiting from the
> re-orgs / rebuilds but they'd probably run much better if they just had a
> good index.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "NC3" <ncoleman3@.yahoo.com> wrote in message
> news:f69cf48c-bdeb-4026-9bb0-bd01ae9627b5@.c23g2000hsa.googlegroups.com...
> On Jan 20, 7:41 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
>> Can you set up a trace to see who is doing what? My guess is something
>> like
>> index rebuilds.
>> --
>> HTH
>> Kalen Delaney, SQL Server
>> MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
>> "NC3" <ncolem...@.yahoo.com> wrote in message
>> news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
>>
>> > Every Satuday evening my t-log balloons and consume my available drive
>> > space. What can I do to monitor the og so i can try to see what is
>> > causing this to keep happening.
>> > I have interrogated the application support ema to see if they do any
>> > large amount of work that would cause the problem but only "not me"
>> > answers
>> > any help/suggestions are greatly appreciated.- Hide quoted text -
>> - Show quoted text -
> I do perform maintenance at 1am on Sunday mornings which does an index
> reorg but not a rebuild. would performing maintenance more often help
> or will that balloon the logs suring the production week which I don't
> want to happen.
> Any suggestions are welcomed.
>|||On Jan 20, 9:14=A0pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> Yes, re-organising OR rebuilding databases does cause massive transaction
> logging.
> Instead of actually rebuilding or re-organising your indexes, you might be=
> better off just rebuilding index statistics (which doesn't generate such
> massive log entries). You should also look into tuing the queries which ar=e
> scanning your tables - these queries are the ones benefiting from the
> re-orgs / rebuilds but they'd probably run much better if they just had a
> good index.
> Regards,
> Greg Linwood
> SQL Server MVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performancehttp://www.SQLBenchmarkPro.com
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:f69cf48c-bdeb-4026-9bb0-bd01ae9627b5@.c23g2000hsa.googlegroups.com...
> On Jan 20, 7:41 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
>
>
> > Can you set up a trace to see who is doing what? My guess is something
> > like
> > index rebuilds.
> > --
> > HTH
> > Kalen Delaney, SQL Server
> > MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
> > "NC3" <ncolem...@.yahoo.com> wrote in message
> >news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...=
> > > Every Satuday evening my t-log balloons and consume my available drive=
> > > space. What can I do to monitor the og so i can try to see what is
> > > causing this to keep happening.
> > > I have interrogated the application support ema to see if they do any
> > > large amount of work that would cause the problem but only "not me"
> > > answers
> > > any help/suggestions are greatly appreciated.- Hide quoted text -
> > - Show quoted text -
> I do perform maintenance at 1am on Sunday mornings which does an index
> reorg but not a rebuild. would performing maintenance more often help
> or will that balloon the logs suring the production week which I don't
> want to happen.
> Any suggestions are welcomed.- Hide quoted text -
> - Show quoted text -
I appreciate your comments and will look into the suggestions that I
received. Tuning the queries are a problem since they belong to a
third party application. However I will learn how to use the SQL
traces suggested by Kalen Delaney, and reveiw BOL for fragmentation
queries as well as the rebuild stats.
New things to learn are always exciting (and frustrating)!
Thanks again.sql
Sunday, February 19, 2012
Backup/restore rids free space?
Can someone explain this? I'm trying to do a backup and
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0
Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0
Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
Backup/restore rids free space?
Can someone explain this? I'm trying to do a backup and
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
Backup/restore rids free space?
Can someone explain this? I'm trying to do a backup and
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
Thursday, February 16, 2012
Backup/copy of sql server 2000 DB
Hi people!!
I was wondering how u can copy a sql server database (to make a backup) on to either a CD or floppy(dunno if theres enough disk space for that)?
ThanksHi,
you can make a backup of the database to disk and copy that to a cd
Command : backup database name of database to disk='c:\backup.bak'
or you can copy the fysical database file's to cd
then you have to copy the MDF and LDF files to cd
Lambik
I was wondering how u can copy a sql server database (to make a backup) on to either a CD or floppy(dunno if theres enough disk space for that)?
ThanksHi,
you can make a backup of the database to disk and copy that to a cd
Command : backup database name of database to disk='c:\backup.bak'
or you can copy the fysical database file's to cd
then you have to copy the MDF and LDF files to cd
Lambik
Monday, February 13, 2012
Backup using GZIP
I'm sure I'm not the only one in this situation: out of disk space on
the backup drive, no other drives available, no money for new drives,
no money for software.
I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
with the fiscal year ending and our fuel costs consuming all revenue.
I've seen some hints of using GZIP to compress the backups using named
pipes. I can get the backup to work manually by startiing the restore
going to a command prompt and issuing the gzip command based on this
link:
http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_b
logpart_blogpart=blogview&_c=blogpart#permalink
I can't get the restore to work properly, although I can manually
uncompress the file and the restore works fine (the gunzip statement
can't locate the pipe).
Is anyone using something like this that is an automated job?
Anyone see any problems/issues with using gzip to compress the backup
files?
Thanks,
BillSQL Backup from RedGate is only like $300 per server.
<Disclosure>No connection with them (they did once give me a free pass to VS
Live, but that was after I was a satisfied customer).</Disclosure>
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148653651.258474.50350@.38g2000cwa.googlegroups.com...
> I'm sure I'm not the only one in this situation: out of disk space on
> the backup drive, no other drives available, no money for new drives,
> no money for software.
> I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
> with the fiscal year ending and our fuel costs consuming all revenue.
> I've seen some hints of using GZIP to compress the backups using named
> pipes. I can get the backup to work manually by startiing the restore
> going to a command prompt and issuing the gzip command based on this
> link:
> http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11
_blogpart_blogpart=blogview&_c=blogpart#permalink
> I can't get the restore to work properly, although I can manually
> uncompress the file and the restore works fine (the gunzip statement
> can't locate the pipe).
> Is anyone using something like this that is an automated job?
> Anyone see any problems/issues with using gzip to compress the backup
> files?
> Thanks,
> Bill
>|||You've never worked for the government. When I say there's no money -
there's really no money. I can barely get them to buy paper for the
printer.|||Hmmmm...that is a bummer. Are there any old backup files on the HD that you
can delete? Can you truncate any old static reporting tables or any load
tables that are no longer being used within the database? Any large files
or applications on the server that are no longer needed that can be removed?
Perhaps backup locally then automatically copy the backup files to a
different system with more space? Or backup to a different system
altogether using UNC?
Just a couple of ideas.
HTH
Jerry
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148655353.974446.11690@.i39g2000cwa.googlegroups.com...
> You've never worked for the government. When I say there's no money -
> there's really no money. I can barely get them to buy paper for the
> printer.
>|||No space anywhere - this is just a DB server so there aren't any other
applications on it. The backups have their own drive, it's just not
big enough. We're barely storing a days worth of data. I'm fighting
for space on a SAN, but not making much headway because of the location
of the server. We have some servers opening up in two or three months,
but I need to do something within the next couple of days.
the backup drive, no other drives available, no money for new drives,
no money for software.
I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
with the fiscal year ending and our fuel costs consuming all revenue.
I've seen some hints of using GZIP to compress the backups using named
pipes. I can get the backup to work manually by startiing the restore
going to a command prompt and issuing the gzip command based on this
link:
http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_b
logpart_blogpart=blogview&_c=blogpart#permalink
I can't get the restore to work properly, although I can manually
uncompress the file and the restore works fine (the gunzip statement
can't locate the pipe).
Is anyone using something like this that is an automated job?
Anyone see any problems/issues with using gzip to compress the backup
files?
Thanks,
BillSQL Backup from RedGate is only like $300 per server.
<Disclosure>No connection with them (they did once give me a free pass to VS
Live, but that was after I was a satisfied customer).</Disclosure>
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148653651.258474.50350@.38g2000cwa.googlegroups.com...
> I'm sure I'm not the only one in this situation: out of disk space on
> the backup drive, no other drives available, no money for new drives,
> no money for software.
> I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
> with the fiscal year ending and our fuel costs consuming all revenue.
> I've seen some hints of using GZIP to compress the backups using named
> pipes. I can get the backup to work manually by startiing the restore
> going to a command prompt and issuing the gzip command based on this
> link:
> http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11
_blogpart_blogpart=blogview&_c=blogpart#permalink
> I can't get the restore to work properly, although I can manually
> uncompress the file and the restore works fine (the gunzip statement
> can't locate the pipe).
> Is anyone using something like this that is an automated job?
> Anyone see any problems/issues with using gzip to compress the backup
> files?
> Thanks,
> Bill
>|||You've never worked for the government. When I say there's no money -
there's really no money. I can barely get them to buy paper for the
printer.|||Hmmmm...that is a bummer. Are there any old backup files on the HD that you
can delete? Can you truncate any old static reporting tables or any load
tables that are no longer being used within the database? Any large files
or applications on the server that are no longer needed that can be removed?
Perhaps backup locally then automatically copy the backup files to a
different system with more space? Or backup to a different system
altogether using UNC?
Just a couple of ideas.
HTH
Jerry
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148655353.974446.11690@.i39g2000cwa.googlegroups.com...
> You've never worked for the government. When I say there's no money -
> there's really no money. I can barely get them to buy paper for the
> printer.
>|||No space anywhere - this is just a DB server so there aren't any other
applications on it. The backups have their own drive, it's just not
big enough. We're barely storing a days worth of data. I'm fighting
for space on a SAN, but not making much headway because of the location
of the server. We have some servers opening up in two or three months,
but I need to do something within the next couple of days.
Backup using GZIP
I'm sure I'm not the only one in this situation: out of disk space on
the backup drive, no other drives available, no money for new drives,
no money for software.
I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
with the fiscal year ending and our fuel costs consuming all revenue.
I've seen some hints of using GZIP to compress the backups using named
pipes. I can get the backup to work manually by startiing the restore
going to a command prompt and issuing the gzip command based on this
link:
http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_blogpart_blogpart=blogview&_c=blogpart#permalink
I can't get the restore to work properly, although I can manually
uncompress the file and the restore works fine (the gunzip statement
can't locate the pipe).
Is anyone using something like this that is an automated job?
Anyone see any problems/issues with using gzip to compress the backup
files?
Thanks,
BillSQL Backup from RedGate is only like $300 per server.
<Disclosure>No connection with them (they did once give me a free pass to VS
Live, but that was after I was a satisfied customer).</Disclosure>
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148653651.258474.50350@.38g2000cwa.googlegroups.com...
> I'm sure I'm not the only one in this situation: out of disk space on
> the backup drive, no other drives available, no money for new drives,
> no money for software.
> I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
> with the fiscal year ending and our fuel costs consuming all revenue.
> I've seen some hints of using GZIP to compress the backups using named
> pipes. I can get the backup to work manually by startiing the restore
> going to a command prompt and issuing the gzip command based on this
> link:
> http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_blogpart_blogpart=blogview&_c=blogpart#permalink
> I can't get the restore to work properly, although I can manually
> uncompress the file and the restore works fine (the gunzip statement
> can't locate the pipe).
> Is anyone using something like this that is an automated job?
> Anyone see any problems/issues with using gzip to compress the backup
> files?
> Thanks,
> Bill
>|||You've never worked for the government. When I say there's no money -
there's really no money. I can barely get them to buy paper for the
printer.|||Hmmmm...that is a bummer. Are there any old backup files on the HD that you
can delete? Can you truncate any old static reporting tables or any load
tables that are no longer being used within the database? Any large files
or applications on the server that are no longer needed that can be removed?
Perhaps backup locally then automatically copy the backup files to a
different system with more space? Or backup to a different system
altogether using UNC?
Just a couple of ideas.
HTH
Jerry
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148655353.974446.11690@.i39g2000cwa.googlegroups.com...
> You've never worked for the government. When I say there's no money -
> there's really no money. I can barely get them to buy paper for the
> printer.
>|||No space anywhere - this is just a DB server so there aren't any other
applications on it. The backups have their own drive, it's just not
big enough. We're barely storing a days worth of data. I'm fighting
for space on a SAN, but not making much headway because of the location
of the server. We have some servers opening up in two or three months,
but I need to do something within the next couple of days.
the backup drive, no other drives available, no money for new drives,
no money for software.
I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
with the fiscal year ending and our fuel costs consuming all revenue.
I've seen some hints of using GZIP to compress the backups using named
pipes. I can get the backup to work manually by startiing the restore
going to a command prompt and issuing the gzip command based on this
link:
http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_blogpart_blogpart=blogview&_c=blogpart#permalink
I can't get the restore to work properly, although I can manually
uncompress the file and the restore works fine (the gunzip statement
can't locate the pipe).
Is anyone using something like this that is an automated job?
Anyone see any problems/issues with using gzip to compress the backup
files?
Thanks,
BillSQL Backup from RedGate is only like $300 per server.
<Disclosure>No connection with them (they did once give me a free pass to VS
Live, but that was after I was a satisfied customer).</Disclosure>
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148653651.258474.50350@.38g2000cwa.googlegroups.com...
> I'm sure I'm not the only one in this situation: out of disk space on
> the backup drive, no other drives available, no money for new drives,
> no money for software.
> I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
> with the fiscal year ending and our fuel costs consuming all revenue.
> I've seen some hints of using GZIP to compress the backups using named
> pipes. I can get the backup to work manually by startiing the restore
> going to a command prompt and issuing the gzip command based on this
> link:
> http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_blogpart_blogpart=blogview&_c=blogpart#permalink
> I can't get the restore to work properly, although I can manually
> uncompress the file and the restore works fine (the gunzip statement
> can't locate the pipe).
> Is anyone using something like this that is an automated job?
> Anyone see any problems/issues with using gzip to compress the backup
> files?
> Thanks,
> Bill
>|||You've never worked for the government. When I say there's no money -
there's really no money. I can barely get them to buy paper for the
printer.|||Hmmmm...that is a bummer. Are there any old backup files on the HD that you
can delete? Can you truncate any old static reporting tables or any load
tables that are no longer being used within the database? Any large files
or applications on the server that are no longer needed that can be removed?
Perhaps backup locally then automatically copy the backup files to a
different system with more space? Or backup to a different system
altogether using UNC?
Just a couple of ideas.
HTH
Jerry
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148655353.974446.11690@.i39g2000cwa.googlegroups.com...
> You've never worked for the government. When I say there's no money -
> there's really no money. I can barely get them to buy paper for the
> printer.
>|||No space anywhere - this is just a DB server so there aren't any other
applications on it. The backups have their own drive, it's just not
big enough. We're barely storing a days worth of data. I'm fighting
for space on a SAN, but not making much headway because of the location
of the server. We have some servers opening up in two or three months,
but I need to do something within the next couple of days.
Sunday, February 12, 2012
Backup Transaction Log
What is the proper way to backup a transaction log for a database whose name contains a space. (I.E. DBname = Database Name). I am trying to execute a command, but it does not like the space in the database name. I have also tried wrapping the name in single quotes and parethesis to no avail.What is the proper way to backup a transaction log for a database whose name contains a space. (I.E. DBname = Database Name). I am trying to execute a command, but it does not like the space in the database name. I have also tried wrapping the name in single quotes and parethesis to no avail.
Parenthesis ()? Or square brackets []? Try the square brackets.
Also (not trying to be flippant), try to avoid spaces in db names.
Regards,
hmscott|||Thanks for the fast reply. The square brackets did the trick.
Parenthesis ()? Or square brackets []? Try the square brackets.
Also (not trying to be flippant), try to avoid spaces in db names.
Regards,
hmscott|||Thanks for the fast reply. The square brackets did the trick.
Friday, February 10, 2012
Backup to remote pc
Hi there,
I use shared space MSSQL server in my hosting server.
And I can't backup my DB to my remote server.
Please help how can I do it.
Thank you<anuke@.bk.ru> wrote in message
news:e23cb98a.0410080308.1596832a@.posting.google.c om...
> Hi there,
> I use shared space MSSQL server in my hosting server.
> And I can't backup my DB to my remote server.
> Please help how can I do it.
> Thank you
I use shared space MSSQL server in my hosting server.
And I can't backup my DB to my remote server.
Please help how can I do it.
Thank you<anuke@.bk.ru> wrote in message
news:e23cb98a.0410080308.1596832a@.posting.google.c om...
> Hi there,
> I use shared space MSSQL server in my hosting server.
> And I can't backup my DB to my remote server.
> Please help how can I do it.
> Thank you
You can ask your hosting company to make a backup and put in on an FTP
server, or somewhere else for you to download it. If that's not possible,
and assuming you have access to your database with Enterprise Manager and
Query Analyzer, you can generate the scripts for all the objects in the
database, and run the scripts on your own server. That will recreate all the
objects, and you can use bcp.exe or DTS to pull the data from the hosted
server to your own server.
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<41668095
> You can ask your hosting company to make a backup and put in on an FTP
> server, or somewhere else for you to download it. If that's not possible,
> and assuming you have access to your database with Enterprise Manager and
> Query Analyzer, you can generate the scripts for all the objects in the
> database, and run the scripts on your own server. That will recreate all the
> objects, and you can use bcp.exe or DTS to pull the data from the hosted
> server to your own server.
> Simon
Thank you.
But i need to backup my database twice in day, in later maybe more.
MSSQL Server is in local area with my server. Can i share folder or
disk to copy backup by script to? Or MS SQL Server can't make backup
files on share-dick?
Thank you very much.|||<anuke@.bk.ru> wrote in message
news:e23cb98a.0410090412.2473a5ec@.posting.google.c om...
> "Simon Hayes" <sql@.hayes.ch> wrote in message news:<41668095
>> You can ask your hosting company to make a backup and put in on an FTP
>> server, or somewhere else for you to download it. If that's not possible,
>> and assuming you have access to your database with Enterprise Manager and
>> Query Analyzer, you can generate the scripts for all the objects in the
>> database, and run the scripts on your own server. That will recreate all
>> the
>> objects, and you can use bcp.exe or DTS to pull the data from the hosted
>> server to your own server.
>>
>> Simon
> Thank you.
> But i need to backup my database twice in day, in later maybe more.
> MSSQL Server is in local area with my server. Can i share folder or
> disk to copy backup by script to? Or MS SQL Server can't make backup
> files on share-dick?
> Thank you very much.
I think I didn't understand your question - if both servers are on the same
LAN and in the same domain, then there should be no problem. MSSQL can back
up to a UNC path, like \\server\sharename, but to do this the MSSQL service
account needs to have access to the target UNC path. So it must be running
under a domain account, not the LocalSystem account. Then you can do
something like this:
backup database MyDB to disk = '\\myserver\backups\mssql\mydb.bak'
See "Setting up Windows Services Accounts" and "BACKUP" in Books Online.
Simon
Subscribe to:
Posts (Atom)