I am looking to shrink a database file using DBCC Shrinkfile to try to reclaim some disk space. For some unexplained reason I have some unsettled feelings. I need to confirm:
1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
2. If I do not have a clue as to what target size I might go for, what is safe?
3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
--
Message posted via http://www.sqlmonster.com"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.com...
> I am looking to shrink a database file using DBCC Shrinkfile to try to
reclaim some disk space. For some unexplained reason I have some unsettled
feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
Shrinkfile?
>
1. That's the option you should use -- you might have to defragment some
indexes first, though. If you have heavy fragmentation you probably won't
reclaim much space (if any is available).
2. I usually use 1 if I want to reclaim ALL possible space. What that
option is actually doing is specifying a new minimum size for the database
(overriding whatever was set when it was created). So there's no real
danger in using whatever value you want...
3. None that I'm aware of -- probably a small performance hit if you run it
on a very active system, though...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||What version of sql are you using?
"Robert Richards via SQLMonster.com" wrote:
> I am looking to shrink a database file using DBCC Shrinkfile to try to reclaim some disk space. For some unexplained reason I have some unsettled feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is safe?
> 3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
> --
> Message posted via http://www.sqlmonster.com
>|||I am running SQL 2K.
--
Message posted via http://www.sqlmonster.com|||I am running SQL 2K.
--
Message posted via http://www.sqlmonster.com|||1. Sure it may free up some space but what happens when you need more room
in the db? You also need plenty of free space to do things like creating
and reindexing.
2. Reindexing an index will require at least 1.2 times the size of the
index or in the case of a clustered index the table itself.
3. Shrinking a file simply to gain more disk space is a bad idea in most
cases. There was some reason why the file got that large in the first place
and there is a good chance it will need that much space again. If you
shrink the file and use that space for something else what is going to
happen when the DB grows again? Chances are you will stop any modifications
on your sql server for that db. If you are that low on disk space I suggest
you get another disk before you really run into trouble.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.com...
>I am looking to shrink a database file using DBCC Shrinkfile to try to
>reclaim some disk space. For some unexplained reason I have some unsettled
>feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
> argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
> safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
> Shrinkfile?
> --
> Message posted via http://www.sqlmonster.comsql
Showing posts with label disk. Show all posts
Showing posts with label disk. Show all posts
Thursday, March 29, 2012
DBCC Shrinkfile
I am looking to shrink a database file using DBCC Shrinkfile to try to reclaim some disk space. For some unexplained reason I have some unsettled feelings. I need to confirm:
1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
2. If I do not have a clue as to what target size I might go for, what is safe?
3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
Message posted via http://www.sqlmonster.com
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.c om...
> I am looking to shrink a database file using DBCC Shrinkfile to try to
reclaim some disk space. For some unexplained reason I have some unsettled
feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
Shrinkfile?
>
1. That's the option you should use -- you might have to defragment some
indexes first, though. If you have heavy fragmentation you probably won't
reclaim much space (if any is available).
2. I usually use 1 if I want to reclaim ALL possible space. What that
option is actually doing is specifying a new minimum size for the database
(overriding whatever was set when it was created). So there's no real
danger in using whatever value you want...
3. None that I'm aware of -- probably a small performance hit if you run it
on a very active system, though...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||What version of sql are you using?
"Robert Richards via SQLMonster.com" wrote:
> I am looking to shrink a database file using DBCC Shrinkfile to try to reclaim some disk space. For some unexplained reason I have some unsettled feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is safe?
> 3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
> --
> Message posted via http://www.sqlmonster.com
>
|||I am running SQL 2K.
Message posted via http://www.sqlmonster.com
|||I am running SQL 2K.
Message posted via http://www.sqlmonster.com
|||1. Sure it may free up some space but what happens when you need more room
in the db? You also need plenty of free space to do things like creating
and reindexing.
2. Reindexing an index will require at least 1.2 times the size of the
index or in the case of a clustered index the table itself.
3. Shrinking a file simply to gain more disk space is a bad idea in most
cases. There was some reason why the file got that large in the first place
and there is a good chance it will need that much space again. If you
shrink the file and use that space for something else what is going to
happen when the DB grows again? Chances are you will stop any modifications
on your sql server for that db. If you are that low on disk space I suggest
you get another disk before you really run into trouble.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.c om...
>I am looking to shrink a database file using DBCC Shrinkfile to try to
>reclaim some disk space. For some unexplained reason I have some unsettled
>feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
> argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
> safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
> Shrinkfile?
> --
> Message posted via http://www.sqlmonster.com
1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
2. If I do not have a clue as to what target size I might go for, what is safe?
3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
Message posted via http://www.sqlmonster.com
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.c om...
> I am looking to shrink a database file using DBCC Shrinkfile to try to
reclaim some disk space. For some unexplained reason I have some unsettled
feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
Shrinkfile?
>
1. That's the option you should use -- you might have to defragment some
indexes first, though. If you have heavy fragmentation you probably won't
reclaim much space (if any is available).
2. I usually use 1 if I want to reclaim ALL possible space. What that
option is actually doing is specifying a new minimum size for the database
(overriding whatever was set when it was created). So there's no real
danger in using whatever value you want...
3. None that I'm aware of -- probably a small performance hit if you run it
on a very active system, though...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||What version of sql are you using?
"Robert Richards via SQLMonster.com" wrote:
> I am looking to shrink a database file using DBCC Shrinkfile to try to reclaim some disk space. For some unexplained reason I have some unsettled feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is safe?
> 3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
> --
> Message posted via http://www.sqlmonster.com
>
|||I am running SQL 2K.
Message posted via http://www.sqlmonster.com
|||I am running SQL 2K.
Message posted via http://www.sqlmonster.com
|||1. Sure it may free up some space but what happens when you need more room
in the db? You also need plenty of free space to do things like creating
and reindexing.
2. Reindexing an index will require at least 1.2 times the size of the
index or in the case of a clustered index the table itself.
3. Shrinking a file simply to gain more disk space is a bad idea in most
cases. There was some reason why the file got that large in the first place
and there is a good chance it will need that much space again. If you
shrink the file and use that space for something else what is going to
happen when the DB grows again? Chances are you will stop any modifications
on your sql server for that db. If you are that low on disk space I suggest
you get another disk before you really run into trouble.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.c om...
>I am looking to shrink a database file using DBCC Shrinkfile to try to
>reclaim some disk space. For some unexplained reason I have some unsettled
>feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
> argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
> safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
> Shrinkfile?
> --
> Message posted via http://www.sqlmonster.com
DBCC Shrinkfile
I am looking to shrink a database file using DBCC Shrinkfile to try to recla
im some disk space. For some unexplained reason I have some unsettled feelin
gs. I need to confirm:
1. I was considering running DBCC Shrinkfile with the TruncateOnly argument,
believing this is how I might free up some disk space?
2. If I do not have a clue as to what target size I might go for, what is sa
fe?
3. Are there any unknown dangers lurking out their when running DBCC Shrinkf
ile?
Message posted via http://www.droptable.com"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQ
droptable.com...
> I am looking to shrink a database file using DBCC Shrinkfile to try to
reclaim some disk space. For some unexplained reason I have some unsettled
feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
Shrinkfile?
>
1. That's the option you should use -- you might have to defragment some
indexes first, though. If you have heavy fragmentation you probably won't
reclaim much space (if any is available).
2. I usually use 1 if I want to reclaim ALL possible space. What that
option is actually doing is specifying a new minimum size for the database
(overriding whatever was set when it was created). So there's no real
danger in using whatever value you want...
3. None that I'm aware of -- probably a small performance hit if you run it
on a very active system, though...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||What version of sql are you using?
"Robert Richards via droptable.com" wrote:
> I am looking to shrink a database file using DBCC Shrinkfile to try to rec
laim some disk space. For some unexplained reason I have some unsettled feel
ings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly argumen
t, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
safe?
> 3. Are there any unknown dangers lurking out their when running DBCC Shrin
kfile?
> --
> Message posted via http://www.droptable.com
>|||I am running SQL 2K.
Message posted via http://www.droptable.com|||I am running SQL 2K.
Message posted via http://www.droptable.com|||1. Sure it may free up some space but what happens when you need more room
in the db? You also need plenty of free space to do things like creating
and reindexing.
2. Reindexing an index will require at least 1.2 times the size of the
index or in the case of a clustered index the table itself.
3. Shrinking a file simply to gain more disk space is a bad idea in most
cases. There was some reason why the file got that large in the first place
and there is a good chance it will need that much space again. If you
shrink the file and use that space for something else what is going to
happen when the DB grows again? Chances are you will stop any modifications
on your sql server for that db. If you are that low on disk space I suggest
you get another disk before you really run into trouble.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQ
droptable.com...
>I am looking to shrink a database file using DBCC Shrinkfile to try to
>reclaim some disk space. For some unexplained reason I have some unsettled
>feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
> argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
> safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
> Shrinkfile?
> --
> Message posted via http://www.droptable.com
im some disk space. For some unexplained reason I have some unsettled feelin
gs. I need to confirm:
1. I was considering running DBCC Shrinkfile with the TruncateOnly argument,
believing this is how I might free up some disk space?
2. If I do not have a clue as to what target size I might go for, what is sa
fe?
3. Are there any unknown dangers lurking out their when running DBCC Shrinkf
ile?
Message posted via http://www.droptable.com"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQ
droptable.com...
> I am looking to shrink a database file using DBCC Shrinkfile to try to
reclaim some disk space. For some unexplained reason I have some unsettled
feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
Shrinkfile?
>
1. That's the option you should use -- you might have to defragment some
indexes first, though. If you have heavy fragmentation you probably won't
reclaim much space (if any is available).
2. I usually use 1 if I want to reclaim ALL possible space. What that
option is actually doing is specifying a new minimum size for the database
(overriding whatever was set when it was created). So there's no real
danger in using whatever value you want...
3. None that I'm aware of -- probably a small performance hit if you run it
on a very active system, though...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||What version of sql are you using?
"Robert Richards via droptable.com" wrote:
> I am looking to shrink a database file using DBCC Shrinkfile to try to rec
laim some disk space. For some unexplained reason I have some unsettled feel
ings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly argumen
t, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
safe?
> 3. Are there any unknown dangers lurking out their when running DBCC Shrin
kfile?
> --
> Message posted via http://www.droptable.com
>|||I am running SQL 2K.
Message posted via http://www.droptable.com|||I am running SQL 2K.
Message posted via http://www.droptable.com|||1. Sure it may free up some space but what happens when you need more room
in the db? You also need plenty of free space to do things like creating
and reindexing.
2. Reindexing an index will require at least 1.2 times the size of the
index or in the case of a clustered index the table itself.
3. Shrinking a file simply to gain more disk space is a bad idea in most
cases. There was some reason why the file got that large in the first place
and there is a good chance it will need that much space again. If you
shrink the file and use that space for something else what is going to
happen when the DB grows again? Chances are you will stop any modifications
on your sql server for that db. If you are that low on disk space I suggest
you get another disk before you really run into trouble.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQ
droptable.com...
>I am looking to shrink a database file using DBCC Shrinkfile to try to
>reclaim some disk space. For some unexplained reason I have some unsettled
>feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
> argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
> safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
> Shrinkfile?
> --
> Message posted via http://www.droptable.com
Tuesday, March 27, 2012
DBCC ShrinkDatabase on large DB
Hi,
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian KingHave you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateonly')
go
dbcc shrinkfile('logical_ldf_name','truncateonly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian KingHave you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateonly')
go
dbcc shrinkfile('logical_ldf_name','truncateonly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
DBCC ShrinkDatabase on large DB
Hi,
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian KingHave you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateo
nly')
go
dbcc shrinkfile('logical_ldf_name','truncateo
nly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian KingHave you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateo
nly')
go
dbcc shrinkfile('logical_ldf_name','truncateo
nly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
DBCC ShrinkDatabase on large DB
Hi,
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian King
Have you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateonly')
go
dbcc shrinkfile('logical_ldf_name','truncateonly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
sql
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian King
Have you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateonly')
go
dbcc shrinkfile('logical_ldf_name','truncateonly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
sql
Sunday, March 25, 2012
DBCC SHOWCONTIG results: Good/bad?
My company is running Microsoft Navision Axapta as our ERP solution.
We've had some performance problems (high disk loads). After reading
"Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
...I decided to run DBCC SHOWCONTIG on the Axapta database.
The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
(500 kb text)
I'm new to administering SQL servers with large datbases and heavy
load. I need som expert opinions! Is this database heavily fragmented
or not? Should I schedule a daily index defragmentation job?
Any feedback appreciated!
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> My company is running Microsoft Navision Axapta as our ERP solution.
> We've had some performance problems (high disk loads). After reading
> "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> (500 kb text)
> I'm new to administering SQL servers with large datbases and heavy
> load. I need som expert opinions! Is this database heavily fragmented
> or not? Should I schedule a daily index defragmentation job?
> Any feedback appreciated!
>
Look to the best:actual counts. If there was no fragmentation they would be
close to the same.
However, fragmentation is not necessarily a bad thing. In an OLTP database
(loads of updates, few reports) it is good. In an OLAP database (few
updates, bags of reports) it is bad.
You really need to know why your database is running slow by analysing
output from Profiler and System Manager. There's roughly a gazillion things
that can affect performance, fragmentation being only one of them.
Does it affect just SELECT statements? INSERT/DELETE/UPDATE statements?
Both?
If your Indexes are fragmented, try rebuilding the indexes with a lower
FILL_FACTOR setting. That will cause fewer page splits, though SELECT
statements may theoretically increase as SQL will have to read more pages
per index search.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003|||You really don't expect us to go through all that, do you? ;-)
I suggest you run SHOWCONTIG this way:
DBCC SHOWCONTIG WITH TABLERESULTS
That gives you a resultset. I even use below method (from VB code):
INSERT INTO #tbl (...)
DBCC SHOWCONTIG WITH TABLERESULTS
You have to create the temp tables first, with proper columns, but that gives you the ability to do
SELECT with WHERE, ORDER BY etc. Easy to do an ORDER BY LogicalFragmentation DESC, for example.
General tips:
If you don't have > 500 to 1000 pages, don't worry about fragmentation.
If you have > 1 database files, use Logial Fragmentation, scan density will not report correct.
Seems you have some tables without clustered index... Any particular reason?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> My company is running Microsoft Navision Axapta as our ERP solution.
> We've had some performance problems (high disk loads). After reading
> "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> (500 kb text)
> I'm new to administering SQL servers with large datbases and heavy
> load. I need som expert opinions! Is this database heavily fragmented
> or not? Should I schedule a daily index defragmentation job?
> Any feedback appreciated!
>
> H.M.Haga
> '98 Subaru Impreza GT
> '91 Suzuki Bandit 400
> http://www.imprezadriver.com/phpBB2
> http://home.c2i.net/hmhaga|||> Look to the best:actual counts. If there was no fragmentation they would be
> close to the same.
Just watch out if > 1 database file for the filegroup. Jumps between files will count as
fragmentation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver|||You should read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
which explains everything you need to know about managing fragmentation.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:#0LxzdInDHA.2416@.TK2MSFTNGP10.phx.gbl...
> You really don't expect us to go through all that, do you? ;-)
> I suggest you run SHOWCONTIG this way:
> DBCC SHOWCONTIG WITH TABLERESULTS
> That gives you a resultset. I even use below method (from VB code):
> INSERT INTO #tbl (...)
> DBCC SHOWCONTIG WITH TABLERESULTS
> You have to create the temp tables first, with proper columns, but that
gives you the ability to do
> SELECT with WHERE, ORDER BY etc. Easy to do an ORDER BY
LogicalFragmentation DESC, for example.
> General tips:
> If you don't have > 500 to 1000 pages, don't worry about fragmentation.
> If you have > 1 database files, use Logial Fragmentation, scan density
will not report correct.
> Seems you have some tables without clustered index... Any particular
reason?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
> news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> > My company is running Microsoft Navision Axapta as our ERP solution.
> > We've had some performance problems (high disk loads). After reading
> > "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
> >
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> >
> > ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> > The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> > (500 kb text)
> >
> > I'm new to administering SQL servers with large datbases and heavy
> > load. I need som expert opinions! Is this database heavily fragmented
> > or not? Should I schedule a daily index defragmentation job?
> >
> > Any feedback appreciated!
> >
> >
> > H.M.Haga
> > '98 Subaru Impreza GT
> > '91 Suzuki Bandit 400
> > http://www.imprezadriver.com/phpBB2
> > http://home.c2i.net/hmhaga
>|||On Mon, 27 Oct 2003 13:39:52 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
>You really don't expect us to go through all that, do you? ;-)
No... ;-)
After I read your answers, I signed up for "2072 Administering a MS
SQL Server 2000 Database" :-)
Our server has been running for years AS IS, but after we decided to
put our ERP solution on it - it suddenly requires a lot more attention
- and knowledge!
I've just started to analyze the database, currently it is in the same
state as it was when our vendor installed the solution (Axapta).
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga|||> After I read your answers, I signed up for "2072 Administering a MS
> SQL Server 2000 Database" :-)
Teaching those courses, I just want to say that the Admin course do not deal with indexes,
fragmentation and such. The programming course does (2073). Just a heads up. :-)
Again, check out the tables with some 500 pages or more, and concentrate on those. Also, consider
why some tables doesn't have clustered indexes. That would be a good start. And read the paper that
Paul referred to. My tips...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:dlfspv8vi4esvgf2cv2ktgv3ot1ejp9pht@.4ax.com...
> On Mon, 27 Oct 2003 13:39:52 +0100, "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
> >You really don't expect us to go through all that, do you? ;-)
> No... ;-)
> After I read your answers, I signed up for "2072 Administering a MS
> SQL Server 2000 Database" :-)
> Our server has been running for years AS IS, but after we decided to
> put our ERP solution on it - it suddenly requires a lot more attention
> - and knowledge!
> I've just started to analyze the database, currently it is in the same
> state as it was when our vendor installed the solution (Axapta).
>
> H.M.Haga
> '98 Subaru Impreza GT
> '91 Suzuki Bandit 400
> http://www.imprezadriver.com/phpBB2
> http://home.c2i.net/hmhaga|||On Tue, 28 Oct 2003 12:08:46 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
>Teaching those courses, I just want to say that the Admin course do not deal with indexes,
>fragmentation and such. The programming course does (2073). Just a heads up. :-)
Thanks! I've just signed 2073 as well, 2 weeks of SQL courses then :)
As for clustered index, neither the database nor the application has
been tuned or optimized in any way after the implementation - so I
guess that is up to me. Our dealer has very limited knowledge of SQL
(it's like a black box), they've put a layer of Axapta application
logic between themself and SQL! ;-)
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga
We've had some performance problems (high disk loads). After reading
"Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
...I decided to run DBCC SHOWCONTIG on the Axapta database.
The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
(500 kb text)
I'm new to administering SQL servers with large datbases and heavy
load. I need som expert opinions! Is this database heavily fragmented
or not? Should I schedule a daily index defragmentation job?
Any feedback appreciated!
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> My company is running Microsoft Navision Axapta as our ERP solution.
> We've had some performance problems (high disk loads). After reading
> "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> (500 kb text)
> I'm new to administering SQL servers with large datbases and heavy
> load. I need som expert opinions! Is this database heavily fragmented
> or not? Should I schedule a daily index defragmentation job?
> Any feedback appreciated!
>
Look to the best:actual counts. If there was no fragmentation they would be
close to the same.
However, fragmentation is not necessarily a bad thing. In an OLTP database
(loads of updates, few reports) it is good. In an OLAP database (few
updates, bags of reports) it is bad.
You really need to know why your database is running slow by analysing
output from Profiler and System Manager. There's roughly a gazillion things
that can affect performance, fragmentation being only one of them.
Does it affect just SELECT statements? INSERT/DELETE/UPDATE statements?
Both?
If your Indexes are fragmented, try rebuilding the indexes with a lower
FILL_FACTOR setting. That will cause fewer page splits, though SELECT
statements may theoretically increase as SQL will have to read more pages
per index search.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003|||You really don't expect us to go through all that, do you? ;-)
I suggest you run SHOWCONTIG this way:
DBCC SHOWCONTIG WITH TABLERESULTS
That gives you a resultset. I even use below method (from VB code):
INSERT INTO #tbl (...)
DBCC SHOWCONTIG WITH TABLERESULTS
You have to create the temp tables first, with proper columns, but that gives you the ability to do
SELECT with WHERE, ORDER BY etc. Easy to do an ORDER BY LogicalFragmentation DESC, for example.
General tips:
If you don't have > 500 to 1000 pages, don't worry about fragmentation.
If you have > 1 database files, use Logial Fragmentation, scan density will not report correct.
Seems you have some tables without clustered index... Any particular reason?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> My company is running Microsoft Navision Axapta as our ERP solution.
> We've had some performance problems (high disk loads). After reading
> "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> (500 kb text)
> I'm new to administering SQL servers with large datbases and heavy
> load. I need som expert opinions! Is this database heavily fragmented
> or not? Should I schedule a daily index defragmentation job?
> Any feedback appreciated!
>
> H.M.Haga
> '98 Subaru Impreza GT
> '91 Suzuki Bandit 400
> http://www.imprezadriver.com/phpBB2
> http://home.c2i.net/hmhaga|||> Look to the best:actual counts. If there was no fragmentation they would be
> close to the same.
Just watch out if > 1 database file for the filegroup. Jumps between files will count as
fragmentation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver|||You should read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
which explains everything you need to know about managing fragmentation.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:#0LxzdInDHA.2416@.TK2MSFTNGP10.phx.gbl...
> You really don't expect us to go through all that, do you? ;-)
> I suggest you run SHOWCONTIG this way:
> DBCC SHOWCONTIG WITH TABLERESULTS
> That gives you a resultset. I even use below method (from VB code):
> INSERT INTO #tbl (...)
> DBCC SHOWCONTIG WITH TABLERESULTS
> You have to create the temp tables first, with proper columns, but that
gives you the ability to do
> SELECT with WHERE, ORDER BY etc. Easy to do an ORDER BY
LogicalFragmentation DESC, for example.
> General tips:
> If you don't have > 500 to 1000 pages, don't worry about fragmentation.
> If you have > 1 database files, use Logial Fragmentation, scan density
will not report correct.
> Seems you have some tables without clustered index... Any particular
reason?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
> news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> > My company is running Microsoft Navision Axapta as our ERP solution.
> > We've had some performance problems (high disk loads). After reading
> > "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
> >
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> >
> > ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> > The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> > (500 kb text)
> >
> > I'm new to administering SQL servers with large datbases and heavy
> > load. I need som expert opinions! Is this database heavily fragmented
> > or not? Should I schedule a daily index defragmentation job?
> >
> > Any feedback appreciated!
> >
> >
> > H.M.Haga
> > '98 Subaru Impreza GT
> > '91 Suzuki Bandit 400
> > http://www.imprezadriver.com/phpBB2
> > http://home.c2i.net/hmhaga
>|||On Mon, 27 Oct 2003 13:39:52 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
>You really don't expect us to go through all that, do you? ;-)
No... ;-)
After I read your answers, I signed up for "2072 Administering a MS
SQL Server 2000 Database" :-)
Our server has been running for years AS IS, but after we decided to
put our ERP solution on it - it suddenly requires a lot more attention
- and knowledge!
I've just started to analyze the database, currently it is in the same
state as it was when our vendor installed the solution (Axapta).
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga|||> After I read your answers, I signed up for "2072 Administering a MS
> SQL Server 2000 Database" :-)
Teaching those courses, I just want to say that the Admin course do not deal with indexes,
fragmentation and such. The programming course does (2073). Just a heads up. :-)
Again, check out the tables with some 500 pages or more, and concentrate on those. Also, consider
why some tables doesn't have clustered indexes. That would be a good start. And read the paper that
Paul referred to. My tips...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:dlfspv8vi4esvgf2cv2ktgv3ot1ejp9pht@.4ax.com...
> On Mon, 27 Oct 2003 13:39:52 +0100, "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
> >You really don't expect us to go through all that, do you? ;-)
> No... ;-)
> After I read your answers, I signed up for "2072 Administering a MS
> SQL Server 2000 Database" :-)
> Our server has been running for years AS IS, but after we decided to
> put our ERP solution on it - it suddenly requires a lot more attention
> - and knowledge!
> I've just started to analyze the database, currently it is in the same
> state as it was when our vendor installed the solution (Axapta).
>
> H.M.Haga
> '98 Subaru Impreza GT
> '91 Suzuki Bandit 400
> http://www.imprezadriver.com/phpBB2
> http://home.c2i.net/hmhaga|||On Tue, 28 Oct 2003 12:08:46 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
>Teaching those courses, I just want to say that the Admin course do not deal with indexes,
>fragmentation and such. The programming course does (2073). Just a heads up. :-)
Thanks! I've just signed 2073 as well, 2 weeks of SQL courses then :)
As for clustered index, neither the database nor the application has
been tuned or optimized in any way after the implementation - so I
guess that is up to me. Our dealer has very limited knowledge of SQL
(it's like a black box), they've put a layer of Axapta application
logic between themself and SQL! ;-)
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga
Sunday, March 11, 2012
DBCC INDEXDEFRAG - Interleaved on the disks
DBCC INDEXDEFRAG will not help if two indexes are
interleaved on the disk.
I see this everyone, but what does "interleaved on the
disk" mean? Is that a composite index?
Please send replies to: mikecathey@.hotmail.comThis means that the index pages are not contiguous on the disk. The defrag
operation sorts the contents of the index pages (logical order) without
rearranging the physical order of the pages on the disk.
You have to rebuild the indexes to rearrange the physical layout of the
pages on disk.
"Mike Cathey" <mikecathey@.hotmail.com> wrote in message
news:c95f01c38219$55c20dc0$a601280a@.phx.gbl...
> DBCC INDEXDEFRAG will not help if two indexes are
> interleaved on the disk.
> I see this everyone, but what does "interleaved on the
> disk" mean? Is that a composite index?
> Please send replies to: mikecathey@.hotmail.com
>|||DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical
order of the pages matches the left-to-right logical order of the leaf
nodes. However, the key here is that, it does this "in-place". So, if you
have two indexes that are interleaved on the disk, and let us say that all
the physical order of the pages match left-to-right logical order, then
indexdefrag will not be able to improve performance.
If you are planning to improve performance and your problem is
interleaved indexes, you want to improve the clustering of the pages - for
which indexdefrag is not the right tool - rebuilding the indexes is the what
you should do. If fragmentation is your problem, then you should use
indexdefrag.
HTH,
Shriram
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"Don Peterson" <no1@.nunya.com> wrote in message
news:eESYGKigDHA.3128@.tk2msftngp13.phx.gbl...
> This means that the index pages are not contiguous on the disk. The
defrag
> operation sorts the contents of the index pages (logical order) without
> rearranging the physical order of the pages on the disk.
> You have to rebuild the indexes to rearrange the physical layout of the
> pages on disk.
> "Mike Cathey" <mikecathey@.hotmail.com> wrote in message
> news:c95f01c38219$55c20dc0$a601280a@.phx.gbl...
> > DBCC INDEXDEFRAG will not help if two indexes are
> > interleaved on the disk.
> >
> > I see this everyone, but what does "interleaved on the
> > disk" mean? Is that a composite index?
> >
> > Please send replies to: mikecathey@.hotmail.com
> >
> >
>
interleaved on the disk.
I see this everyone, but what does "interleaved on the
disk" mean? Is that a composite index?
Please send replies to: mikecathey@.hotmail.comThis means that the index pages are not contiguous on the disk. The defrag
operation sorts the contents of the index pages (logical order) without
rearranging the physical order of the pages on the disk.
You have to rebuild the indexes to rearrange the physical layout of the
pages on disk.
"Mike Cathey" <mikecathey@.hotmail.com> wrote in message
news:c95f01c38219$55c20dc0$a601280a@.phx.gbl...
> DBCC INDEXDEFRAG will not help if two indexes are
> interleaved on the disk.
> I see this everyone, but what does "interleaved on the
> disk" mean? Is that a composite index?
> Please send replies to: mikecathey@.hotmail.com
>|||DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical
order of the pages matches the left-to-right logical order of the leaf
nodes. However, the key here is that, it does this "in-place". So, if you
have two indexes that are interleaved on the disk, and let us say that all
the physical order of the pages match left-to-right logical order, then
indexdefrag will not be able to improve performance.
If you are planning to improve performance and your problem is
interleaved indexes, you want to improve the clustering of the pages - for
which indexdefrag is not the right tool - rebuilding the indexes is the what
you should do. If fragmentation is your problem, then you should use
indexdefrag.
HTH,
Shriram
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"Don Peterson" <no1@.nunya.com> wrote in message
news:eESYGKigDHA.3128@.tk2msftngp13.phx.gbl...
> This means that the index pages are not contiguous on the disk. The
defrag
> operation sorts the contents of the index pages (logical order) without
> rearranging the physical order of the pages on the disk.
> You have to rebuild the indexes to rearrange the physical layout of the
> pages on disk.
> "Mike Cathey" <mikecathey@.hotmail.com> wrote in message
> news:c95f01c38219$55c20dc0$a601280a@.phx.gbl...
> > DBCC INDEXDEFRAG will not help if two indexes are
> > interleaved on the disk.
> >
> > I see this everyone, but what does "interleaved on the
> > disk" mean? Is that a composite index?
> >
> > Please send replies to: mikecathey@.hotmail.com
> >
> >
>
Subscribe to:
Posts (Atom)