Diskspace problem running tuning advisor after server profiler?!

Hi,
I am trying to run a session in Database tuning advisor on a ~8G database
and the workload is ~350M. During the session, diskspace on drive C is
running low, it's using more than 3G after a while.
Which database/location is the tuning advisor using for temporary files?
Drive C is too small, and I want to change the setting to use drive E for
example. Anyone knows how to do this?
Thanks!
ValentijnHi
If the DTA is using tempdb space you would be able to see this, by looking
at the file sizes; but if you suddenly regain this space after the program is
stopped then it is more likely to be temporary files in the temp directory.
In general it is best practice if you move the temp directories away from the
system disc onto a drive that is less critical if it fills up.
John
"Valentijn" wrote:
> Hi,
> I am trying to run a session in Database tuning advisor on a ~8G database
> and the workload is ~350M. During the session, diskspace on drive C is
> running low, it's using more than 3G after a while.
> Which database/location is the tuning advisor using for temporary files?
> Drive C is too small, and I want to change the setting to use drive E for
> example. Anyone knows how to do this?
> Thanks!
> Valentijn|||Microsoft recommends using of these tools on another machine if possible,
not on the production one.
--
Ekrem Ã?nsoy
"Valentijn" <Valentijn@.discussions.microsoft.com> wrote in message
news:B3C3E818-D440-4A0E-8D5C-5489D45C62F8@.microsoft.com...
> Hi,
> I am trying to run a session in Database tuning advisor on a ~8G database
> and the workload is ~350M. During the session, diskspace on drive C is
> running low, it's using more than 3G after a while.
> Which database/location is the tuning advisor using for temporary files?
> Drive C is too small, and I want to change the setting to use drive E for
> example. Anyone knows how to do this?
> Thanks!
> Valentijn|||Hi
If you are only talking about SQL Profiler and not DTA then it certainly
does use space in the temp folder.
John
"John Bell" wrote:
> Hi
> If the DTA is using tempdb space you would be able to see this, by looking
> at the file sizes; but if you suddenly regain this space after the program is
> stopped then it is more likely to be temporary files in the temp directory.
> In general it is best practice if you move the temp directories away from the
> system disc onto a drive that is less critical if it fills up.
> John
> "Valentijn" wrote:
> > Hi,
> >
> > I am trying to run a session in Database tuning advisor on a ~8G database
> > and the workload is ~350M. During the session, diskspace on drive C is
> > running low, it's using more than 3G after a while.
> >
> > Which database/location is the tuning advisor using for temporary files?
> > Drive C is too small, and I want to change the setting to use drive E for
> > example. Anyone knows how to do this?
> >
> > Thanks!
> >
> > Valentijn|||Hi John,
Thanks for the answer. I'm using the Tuning Advisor from the GUI (Management
console), so I'm not using the commandline tool DTA directly.
The SQL Profiler runs fine, but the diskspace problem occurs when running
the tuning advisor. And yes, the diskspace suddenly comes back after stopping
the tuning advisor.
One of the things I will try monday at work is changing the temp folder.
Would I do that by just changeing the environment variable TEMP?
Furthermore, how can I see DTA (or the graphical version) does use the
tempdb? I looked at the filesize op tempmdb.mdf (and the other system
databases, but they were not growing during the tuning session)..
Thanks again,
Valentijn
"John Bell" wrote:
> Hi
> If you are only talking about SQL Profiler and not DTA then it certainly
> does use space in the temp folder.
> John
> "John Bell" wrote:
> > Hi
> >
> > If the DTA is using tempdb space you would be able to see this, by looking
> > at the file sizes; but if you suddenly regain this space after the program is
> > stopped then it is more likely to be temporary files in the temp directory.
> > In general it is best practice if you move the temp directories away from the
> > system disc onto a drive that is less critical if it fills up.
> >
> > John
> >
> > "Valentijn" wrote:
> >
> > > Hi,
> > >
> > > I am trying to run a session in Database tuning advisor on a ~8G database
> > > and the workload is ~350M. During the session, diskspace on drive C is
> > > running low, it's using more than 3G after a while.
> > >
> > > Which database/location is the tuning advisor using for temporary files?
> > > Drive C is too small, and I want to change the setting to use drive E for
> > > example. Anyone knows how to do this?
> > >
> > > Thanks!
> > >
> > > Valentijn|||Hi
If the space is reclaimed once you have stopped dta then it is not tempdb
that is being used as the tempdb file would remain enlarged. sp_helpfile or
sp_helpdb from a query window would tell you the size of tempdb files
before, during and again after you ran the wizard if you ran it several
times. You could also use the perfmon counters to monitor database file sizes.
From BOL: Database Engine Tuning Advisor stores tuning session data and
other information in the msdb database. Implement an appropriate backup
strategy for the msdb database to avoid the risk of losing tuning session
data.
Again you can use the above methods to monitor the size of msdb but you say
these have not changed which again points to files being created in the temp
folder.
You need to set the temp environment variable before starting the database
tuning advisor.
John
"Valentijn" wrote:
> Hi John,
> Thanks for the answer. I'm using the Tuning Advisor from the GUI (Management
> console), so I'm not using the commandline tool DTA directly.
> The SQL Profiler runs fine, but the diskspace problem occurs when running
> the tuning advisor. And yes, the diskspace suddenly comes back after stopping
> the tuning advisor.
> One of the things I will try monday at work is changing the temp folder.
> Would I do that by just changeing the environment variable TEMP?
> Furthermore, how can I see DTA (or the graphical version) does use the
> tempdb? I looked at the filesize op tempmdb.mdf (and the other system
> databases, but they were not growing during the tuning session)..
> Thanks again,
> Valentijn
> "John Bell" wrote:
> > Hi
> >
> > If you are only talking about SQL Profiler and not DTA then it certainly
> > does use space in the temp folder.
> >
> > John
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > If the DTA is using tempdb space you would be able to see this, by looking
> > > at the file sizes; but if you suddenly regain this space after the program is
> > > stopped then it is more likely to be temporary files in the temp directory.
> > > In general it is best practice if you move the temp directories away from the
> > > system disc onto a drive that is less critical if it fills up.
> > >
> > > John
> > >
> > > "Valentijn" wrote:
> > >
> > > > Hi,
> > > >
> > > > I am trying to run a session in Database tuning advisor on a ~8G database
> > > > and the workload is ~350M. During the session, diskspace on drive C is
> > > > running low, it's using more than 3G after a while.
> > > >
> > > > Which database/location is the tuning advisor using for temporary files?
> > > > Drive C is too small, and I want to change the setting to use drive E for
> > > > example. Anyone knows how to do this?
> > > >
> > > > Thanks!
> > > >
> > > > Valentijn

Disk space getting low

I have 3 disk volumes on my server. C, D, E. C=System, D=Logs, E= Data
How do I get the 2 databases to use drive D in addition to drive E for data?
I have 50 gig volume for E and I have about 9 gig free as of now. D has
only consumed 1 gig and free is 34 gig.
TIA
__StephenPre-allocate storage for the database files as desired. If you need to shrink one of the file (so
that data will be pushed to the other file), you can do it using DBCC SHRINKFILE. It will take a
while for a lot of data, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:ugcOCCvsFHA.3040@.TK2MSFTNGP14.phx.gbl...
>I have 3 disk volumes on my server. C, D, E. C=System, D=Logs, E= Data
> How do I get the 2 databases to use drive D in addition to drive E for data? I have 50 gig volume
> for E and I have about 9 gig free as of now. D has only consumed 1 gig and free is 34 gig.
> TIA
> __Stephen
>
>

Disk space getting low

I have 3 disk volumes on my server. C, D, E. C=System, D=Logs, E= Data
How do I get the 2 databases to use drive D in addition to drive E for data?
I have 50 gig volume for E and I have about 9 gig free as of now. D has
only consumed 1 gig and free is 34 gig.
TIA
__Stephen
Pre-allocate storage for the database files as desired. If you need to shrink one of the file (so
that data will be pushed to the other file), you can do it using DBCC SHRINKFILE. It will take a
while for a lot of data, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:ugcOCCvsFHA.3040@.TK2MSFTNGP14.phx.gbl...
>I have 3 disk volumes on my server. C, D, E. C=System, D=Logs, E= Data
> How do I get the 2 databases to use drive D in addition to drive E for data? I have 50 gig volume
> for E and I have about 9 gig free as of now. D has only consumed 1 gig and free is 34 gig.
> TIA
> __Stephen
>
>

Disk space getting low

I have 3 disk volumes on my server. C, D, E. C=System, D=Logs, E= Data
How do I get the 2 databases to use drive D in addition to drive E for data?
I have 50 gig volume for E and I have about 9 gig free as of now. D has
only consumed 1 gig and free is 34 gig.
TIA
__StephenPre-allocate storage for the database files as desired. If you need to shrin
k one of the file (so
that data will be pushed to the other file), you can do it using DBCC SHRINK
FILE. It will take a
while for a lot of data, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:ugcOCCvsFHA.3040@.TK2MSFTNGP14.phx.gbl...
>I have 3 disk volumes on my server. C, D, E. C=System, D=Logs, E= Data
> How do I get the 2 databases to use drive D in addition to drive E for dat
a? I have 50 gig volume
> for E and I have about 9 gig free as of now. D has only consumed 1 gig a
nd free is 34 gig.
> TIA
> __Stephen
>
>sql

Disk Full of transactions log's

Hi,
One of my app servers has filled the c:\ drive with transactions log's.
Someone moved the log's to another disk, SQL now reports the database as
being suspect. We moved the log's back, it still reports the db as suspect?
Any help would be greatly appreciated
Rich
Hi,
take a look here:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||reset should resolve the problem (order in sequences, try the next step
if the first one doesn't work).
1) restart the MSSQL services (starts the auto recovery)
2) detach and re-attach the database
3) restore the latest database backup
Mel

Disk Full of transactions log's

Hi,
One of my app servers has filled the c:\ drive with transactions log's.
Someone moved the log's to another disk, SQL now reports the database as
being suspect. We moved the log's back, it still reports the db as suspect?
Any help would be greatly appreciated
RichHi,
take a look here:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||reset should resolve the problem (order in sequences, try the next step
if the first one doesn't work).
1) restart the MSSQL services (starts the auto recovery)
2) detach and re-attach the database
3) restore the latest database backup
Mel

Disk Full of transactions log's

Hi,
One of my app servers has filled the c:\ drive with transactions log's.
Someone moved the log's to another disk, SQL now reports the database as
being suspect. We moved the log's back, it still reports the db as suspect?
Any help would be greatly appreciated
RichHi,
take a look here:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||reset should resolve the problem (order in sequences, try the next step
if the first one doesn't work).
1) restart the MSSQL services (starts the auto recovery)
2) detach and re-attach the database
3) restore the latest database backup
Mel

disk fragmentation

I have a Dell PowerEdge 2850 with Windows 2003 server. 2 disk drives with RAID. My databases are fragmented on the drive and I need to defrag the drive at least once a week. My database files are fragmented. Defragger cleans up the files but as soon as I start running processes again, the files fragment and system response time suffers.

What can I do to keep the files from fragmenting?

calculate the database growth

and set the file increment in the db option

bigger than that. i meann just a little bigger

|||

Question : does file fragmentation really impact the performance of a DB ?

I mean : by construction, the data in files are fragmented, and access to data in DB always require a lot of disk seek. Sequential access through files should be marginal. So, fragmentation of the whole file should not imply a big performance impact.

Am I correct ?

|||

How are you measuring the fragmentation?

There are two types of fragmentation here:

1) The normal filesystem fragmentation of the database files. This should have minimal to nil impact unless you do a lot of table-scans. On the other hand, you shouldn't be getting this sort of fragmentation unless you are constantly growing your database. Far better to size it for growth initially than to allow it to grow incrementally.

2) Internal fragmentation of the information within the database files (as measured by commands like DBCC SHOWCONTIG). THis has nothing to do with the physical files being fragmented on disk, and is resolved by using SQL techniques such as DBCC INDEXDEFRAG or ALTER INDEX REBUILD.

disk drive layout for best database performance

In creating a database which option will provide the best performance in a
OLTP environment. Our server has 2 fiber channel HBAs that are load balance
d
attached to an EMC SAN. Should the database be created in a single file on
one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
spread accross 6 disk drives each?
--
DanneHi,
You can get more out of a 4 files, but you'll have to do some tables
movement between files so you'll find the optimal places for tables so work
can be equally distributed through all 4 files.
Danijel Novak
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> In creating a database which option will provide the best performance in a
> OLTP environment. Our server has 2 fiber channel HBAs that are load
> balanced
> attached to an EMC SAN. Should the database be created in a single file
> on
> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> spread accross 6 disk drives each?
> --
> Danne|||Not sure on your setup, but with our servers running of SAN it is hard to
tweak performance by spreading accross disk as the Shark has a crap load of
cache which makes the point mute, the only thing to check it to see if other
aps will contend for same disks on your storage. If disks a dedicated to
that server then i would bet that cache would even out almost any setup you
make. but again i dont know your setup
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You can get more out of a 4 files, but you'll have to do some tables
> movement between files so you'll find the optimal places for tables so
> work can be equally distributed through all 4 files.
> --
> Danijel Novak
>
> "Danne" <Danne@.discussions.microsoft.com> wrote in message
> news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>|||We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9 G
B
logical slices. There are other applciations using the same physical drives
.
We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
for system databases. We are allocating 4 9 GB devices for tempdb per a
recomendation from the software vendor. the 200 GB drive or 4 50 GB drives
are for the data file(s). I am not sure if we will see any difference
between the two choices, but I wanted to ask to see what best practices are.
Thanks for your comments.
--
Danne
"David J. Cartwright" wrote:

> Not sure on your setup, but with our servers running of SAN it is hard to
> tweak performance by spreading accross disk as the Shark has a crap load o
f
> cache which makes the point mute, the only thing to check it to see if oth
er
> aps will contend for same disks on your storage. If disks a dedicated to
> that server then i would bet that cache would even out almost any setup yo
u
> make. but again i dont know your setup
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
>
>|||Danne,
The general rule of thumb for RAID is the more head/disks you have, the
better your performance. Of course it also has to do with the total
cache available on the SAN controllers. This is from the hardware/RAID
level. All the RAID drives created for SQL Server, or any database
should be RAID 0+1 or 10. DO NOT USE RAID 5!
Shahryar
Danne wrote:

>In creating a database which option will provide the best performance in a
>OLTP environment. Our server has 2 fiber channel HBAs that are load balanc
ed
>attached to an EMC SAN. Should the database be created in a single file on
>one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>spread accross 6 disk drives each?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||*best practice would accomplish as many as possible of the following
avoid having to read and write to the same disk at same time
avoid contention with other applications
log and data files on separate drives
have tempdb on seperate than from log and data files
with that said, having 4 50GB drives for data would give you some flexiblity
with file groups and putting certain tables on certain groups (ie drives),
but a SAN blured the strategy as even though the drives may be seperated
from each other, there may be other app needing to use that same drive when
you need it. or the *different drives could actualy be the same drives
anyway. If you can get drives allocated from different physical drives on
the SAN, and have these drives exclusivly, that would be the best choice.
If you can do this, then having 4 50 GB drives spread accross the storage
unit, would allow you to put tables that are joined etc...on different
drives, then when a query is run that joines or updates or whatever would
read read/write from different location where the data would come from on
drive, tempdb proccessing would accour on another and transaction would be
logged to yet another. But i would guess that unless the storage unit is
heavily used that this would all happen in cache on the storage unit and
would appear to be light speed to your server...your server would continue
on its way and the actualy data would be read or written from cache at the
units leasure.
locating the data, log and tempdb on seperate drives gives you flexibility
in the future also.
but.....on one of our systems we found the best performance when all where
on the same drive.
location on the storage solution, cache, and contention with other apps is
what you need to tweak.
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:8A8E8377-198A-439A-9D4B-F32D74EC455C@.microsoft.com...[vbcol=seagreen]
> We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9
> GB
> logical slices. There are other applciations using the same physical
> drives.
> We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
> for system databases. We are allocating 4 9 GB devices for tempdb per a
> recomendation from the software vendor. the 200 GB drive or 4 50 GB
> drives
> are for the data file(s). I am not sure if we will see any difference
> between the two choices, but I wanted to ask to see what best practices
> are.
> Thanks for your comments.
> --
> Danne
>
> "David J. Cartwright" wrote:
>|||What's wrong with RAID 5' We have been using it for a long time. It
has been an industry standard for while, right?
Shahryar G. Hashemi wrote:

> Danne,
> The general rule of thumb for RAID is the more head/disks you have, the
> better your performance. Of course it also has to do with the total
> cache available on the SAN controllers. This is from the hardware/RAID
> level. All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
> Shahryar
> Danne wrote:
>
>|||In article <e2GoRhnAGHA.3804@.TK2MSFTNGP14.phx.gbl>, kkogan@.haiint.com
says...
> What's wrong with RAID 5' We have been using it for a long time. It
> has been an industry standard for while, right?
The information he lists (in another post) for reasons to NOT use R5
have not been experienced on any of the hundreds of servers we maintain.
Not once, never, nada.
We do Mirrors for Log files and R5 (normally 5xdrives + hot spare) for
the database data files.
All RAID Arrays on their own controller, on a single channel, with RAM
on the card (and battery).
spam999free@.rrohio.com
remove 999 in order to email me|||"Shahryar G. Hashemi" wrote:
> All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
wow, all of my production servers run raid 5.
i'll go immediately change them to raid 10.sql

disk drive layout for best database performance

In creating a database which option will provide the best performance in a
OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
attached to an EMC SAN. Should the database be created in a single file on
one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
spread accross 6 disk drives each?
Danne
Hi,
You can get more out of a 4 files, but you'll have to do some tables
movement between files so you'll find the optimal places for tables so work
can be equally distributed through all 4 files.
Danijel Novak
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> In creating a database which option will provide the best performance in a
> OLTP environment. Our server has 2 fiber channel HBAs that are load
> balanced
> attached to an EMC SAN. Should the database be created in a single file
> on
> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> spread accross 6 disk drives each?
> --
> Danne
|||Not sure on your setup, but with our servers running of SAN it is hard to
tweak performance by spreading accross disk as the Shark has a crap load of
cache which makes the point mute, the only thing to check it to see if other
aps will contend for same disks on your storage. If disks a dedicated to
that server then i would bet that cache would even out almost any setup you
make. but again i dont know your setup
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You can get more out of a 4 files, but you'll have to do some tables
> movement between files so you'll find the optimal places for tables so
> work can be equally distributed through all 4 files.
> --
> Danijel Novak
>
> "Danne" <Danne@.discussions.microsoft.com> wrote in message
> news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>
|||We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9 GB
logical slices. There are other applciations using the same physical drives.
We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
for system databases. We are allocating 4 9 GB devices for tempdb per a
recomendation from the software vendor. the 200 GB drive or 4 50 GB drives
are for the data file(s). I am not sure if we will see any difference
between the two choices, but I wanted to ask to see what best practices are.
Thanks for your comments.
Danne
"David J. Cartwright" wrote:

> Not sure on your setup, but with our servers running of SAN it is hard to
> tweak performance by spreading accross disk as the Shark has a crap load of
> cache which makes the point mute, the only thing to check it to see if other
> aps will contend for same disks on your storage. If disks a dedicated to
> that server then i would bet that cache would even out almost any setup you
> make. but again i dont know your setup
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
>
>
|||Danne,
The general rule of thumb for RAID is the more head/disks you have, the
better your performance. Of course it also has to do with the total
cache available on the SAN controllers. This is from the hardware/RAID
level. All the RAID drives created for SQL Server, or any database
should be RAID 0+1 or 10. DO NOT USE RAID 5!
Shahryar
Danne wrote:

>In creating a database which option will provide the best performance in a
>OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
>attached to an EMC SAN. Should the database be created in a single file on
>one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>spread accross 6 disk drives each?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||*best practice would accomplish as many as possible of the following
avoid having to read and write to the same disk at same time
avoid contention with other applications
log and data files on separate drives
have tempdb on seperate than from log and data files
with that said, having 4 50GB drives for data would give you some flexiblity
with file groups and putting certain tables on certain groups (ie drives),
but a SAN blured the strategy as even though the drives may be seperated
from each other, there may be other app needing to use that same drive when
you need it. or the *different drives could actualy be the same drives
anyway. If you can get drives allocated from different physical drives on
the SAN, and have these drives exclusivly, that would be the best choice.
If you can do this, then having 4 50 GB drives spread accross the storage
unit, would allow you to put tables that are joined etc...on different
drives, then when a query is run that joines or updates or whatever would
read read/write from different location where the data would come from on
drive, tempdb proccessing would accour on another and transaction would be
logged to yet another. But i would guess that unless the storage unit is
heavily used that this would all happen in cache on the storage unit and
would appear to be light speed to your server...your server would continue
on its way and the actualy data would be read or written from cache at the
units leasure.
locating the data, log and tempdb on seperate drives gives you flexibility
in the future also.
but.....on one of our systems we found the best performance when all where
on the same drive.
location on the storage solution, cache, and contention with other apps is
what you need to tweak.
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:8A8E8377-198A-439A-9D4B-F32D74EC455C@.microsoft.com...[vbcol=seagreen]
> We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9
> GB
> logical slices. There are other applciations using the same physical
> drives.
> We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
> for system databases. We are allocating 4 9 GB devices for tempdb per a
> recomendation from the software vendor. the 200 GB drive or 4 50 GB
> drives
> are for the data file(s). I am not sure if we will see any difference
> between the two choices, but I wanted to ask to see what best practices
> are.
> Thanks for your comments.
> --
> Danne
>
> "David J. Cartwright" wrote:
|||What's wrong with RAID 5? We have been using it for a long time. It
has been an industry standard for while, right?
Shahryar G. Hashemi wrote:

> Danne,
> The general rule of thumb for RAID is the more head/disks you have, the
> better your performance. Of course it also has to do with the total
> cache available on the SAN controllers. This is from the hardware/RAID
> level. All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
> Shahryar
> Danne wrote:
>
>
|||In article <e2GoRhnAGHA.3804@.TK2MSFTNGP14.phx.gbl>, kkogan@.haiint.com
says...
> What's wrong with RAID 5? We have been using it for a long time. It
> has been an industry standard for while, right?
The information he lists (in another post) for reasons to NOT use R5
have not been experienced on any of the hundreds of servers we maintain.
Not once, never, nada.
We do Mirrors for Log files and R5 (normally 5xdrives + hot spare) for
the database data files.
All RAID Arrays on their own controller, on a single channel, with RAM
on the card (and battery).

spam999free@.rrohio.com
remove 999 in order to email me
|||"Shahryar G. Hashemi" wrote:
> All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
wow, all of my production servers run raid 5.
i'll go immediately change them to raid 10.

disk drive layout for best database performance

In creating a database which option will provide the best performance in a
OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
attached to an EMC SAN. Should the database be created in a single file on
one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
spread accross 6 disk drives each?
--
DanneHi,
You can get more out of a 4 files, but you'll have to do some tables
movement between files so you'll find the optimal places for tables so work
can be equally distributed through all 4 files.
--
Danijel Novak
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> In creating a database which option will provide the best performance in a
> OLTP environment. Our server has 2 fiber channel HBAs that are load
> balanced
> attached to an EMC SAN. Should the database be created in a single file
> on
> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> spread accross 6 disk drives each?
> --
> Danne|||Not sure on your setup, but with our servers running of SAN it is hard to
tweak performance by spreading accross disk as the Shark has a crap load of
cache which makes the point mute, the only thing to check it to see if other
aps will contend for same disks on your storage. If disks a dedicated to
that server then i would bet that cache would even out almost any setup you
make. but again i dont know your setup
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You can get more out of a 4 files, but you'll have to do some tables
> movement between files so you'll find the optimal places for tables so
> work can be equally distributed through all 4 files.
> --
> Danijel Novak
>
> "Danne" <Danne@.discussions.microsoft.com> wrote in message
> news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>> In creating a database which option will provide the best performance in
>> a
>> OLTP environment. Our server has 2 fiber channel HBAs that are load
>> balanced
>> attached to an EMC SAN. Should the database be created in a single file
>> on
>> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>> spread accross 6 disk drives each?
>> --
>> Danne
>|||We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9 GB
logical slices. There are other applciations using the same physical drives.
We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
for system databases. We are allocating 4 9 GB devices for tempdb per a
recomendation from the software vendor. the 200 GB drive or 4 50 GB drives
are for the data file(s). I am not sure if we will see any difference
between the two choices, but I wanted to ask to see what best practices are.
Thanks for your comments.
--
Danne
"David J. Cartwright" wrote:
> Not sure on your setup, but with our servers running of SAN it is hard to
> tweak performance by spreading accross disk as the Shark has a crap load of
> cache which makes the point mute, the only thing to check it to see if other
> aps will contend for same disks on your storage. If disks a dedicated to
> that server then i would bet that cache would even out almost any setup you
> make. but again i dont know your setup
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > You can get more out of a 4 files, but you'll have to do some tables
> > movement between files so you'll find the optimal places for tables so
> > work can be equally distributed through all 4 files.
> >
> > --
> > Danijel Novak
> >
> >
> >
> > "Danne" <Danne@.discussions.microsoft.com> wrote in message
> > news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> >> In creating a database which option will provide the best performance in
> >> a
> >> OLTP environment. Our server has 2 fiber channel HBAs that are load
> >> balanced
> >> attached to an EMC SAN. Should the database be created in a single file
> >> on
> >> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> >> spread accross 6 disk drives each?
> >> --
> >> Danne
> >
> >
>
>|||Danne,
The general rule of thumb for RAID is the more head/disks you have, the
better your performance. Of course it also has to do with the total
cache available on the SAN controllers. This is from the hardware/RAID
level. All the RAID drives created for SQL Server, or any database
should be RAID 0+1 or 10. DO NOT USE RAID 5!
Shahryar
Danne wrote:
>In creating a database which option will provide the best performance in a
>OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
>attached to an EMC SAN. Should the database be created in a single file on
>one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>spread accross 6 disk drives each?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||*best practice would accomplish as many as possible of the following
avoid having to read and write to the same disk at same time
avoid contention with other applications
log and data files on separate drives
have tempdb on seperate than from log and data files
with that said, having 4 50GB drives for data would give you some flexiblity
with file groups and putting certain tables on certain groups (ie drives),
but a SAN blured the strategy as even though the drives may be seperated
from each other, there may be other app needing to use that same drive when
you need it. or the *different drives could actualy be the same drives
anyway. If you can get drives allocated from different physical drives on
the SAN, and have these drives exclusivly, that would be the best choice.
If you can do this, then having 4 50 GB drives spread accross the storage
unit, would allow you to put tables that are joined etc...on different
drives, then when a query is run that joines or updates or whatever would
read read/write from different location where the data would come from on
drive, tempdb proccessing would accour on another and transaction would be
logged to yet another. But i would guess that unless the storage unit is
heavily used that this would all happen in cache on the storage unit and
would appear to be light speed to your server...your server would continue
on its way and the actualy data would be read or written from cache at the
units leasure.
locating the data, log and tempdb on seperate drives gives you flexibility
in the future also.
but.....on one of our systems we found the best performance when all where
on the same drive.
location on the storage solution, cache, and contention with other apps is
what you need to tweak.
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:8A8E8377-198A-439A-9D4B-F32D74EC455C@.microsoft.com...
> We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9
> GB
> logical slices. There are other applciations using the same physical
> drives.
> We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
> for system databases. We are allocating 4 9 GB devices for tempdb per a
> recomendation from the software vendor. the 200 GB drive or 4 50 GB
> drives
> are for the data file(s). I am not sure if we will see any difference
> between the two choices, but I wanted to ask to see what best practices
> are.
> Thanks for your comments.
> --
> Danne
>
> "David J. Cartwright" wrote:
>> Not sure on your setup, but with our servers running of SAN it is hard to
>> tweak performance by spreading accross disk as the Shark has a crap load
>> of
>> cache which makes the point mute, the only thing to check it to see if
>> other
>> aps will contend for same disks on your storage. If disks a dedicated to
>> that server then i would bet that cache would even out almost any setup
>> you
>> make. but again i dont know your setup
>> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
>> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
>> > Hi,
>> >
>> > You can get more out of a 4 files, but you'll have to do some tables
>> > movement between files so you'll find the optimal places for tables so
>> > work can be equally distributed through all 4 files.
>> >
>> > --
>> > Danijel Novak
>> >
>> >
>> >
>> > "Danne" <Danne@.discussions.microsoft.com> wrote in message
>> > news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>> >> In creating a database which option will provide the best performance
>> >> in
>> >> a
>> >> OLTP environment. Our server has 2 fiber channel HBAs that are load
>> >> balanced
>> >> attached to an EMC SAN. Should the database be created in a single
>> >> file
>> >> on
>> >> one 200 GB drive spread across 24 disk drives or four 50 GB disk
>> >> drives
>> >> spread accross 6 disk drives each?
>> >> --
>> >> Danne
>> >
>> >
>>|||What's wrong with RAID 5' We have been using it for a long time. It
has been an industry standard for while, right?
Shahryar G. Hashemi wrote:
> Danne,
> The general rule of thumb for RAID is the more head/disks you have, the
> better your performance. Of course it also has to do with the total
> cache available on the SAN controllers. This is from the hardware/RAID
> level. All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
> Shahryar
> Danne wrote:
>> In creating a database which option will provide the best performance
>> in a OLTP environment. Our server has 2 fiber channel HBAs that are
>> load balanced attached to an EMC SAN. Should the database be created
>> in a single file on one 200 GB drive spread across 24 disk drives or
>> four 50 GB disk drives spread accross 6 disk drives each?
>>
>|||"Shahryar G. Hashemi" wrote:
> All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
wow, all of my production servers run raid 5.
i'll go immediately change them to raid 10.

disaster recovery

my server has 3 partitions (c: , d: ,e:). my sql 2000 is
in d: and my win 2K is in c:. my c drive crashed and i
would like to recover my sql database in d: after i
reinstall my win 2K in c:. is that possible?Hi Bobby.
If SQL Server managed to shot down successfully, it would have checkpointed
the databases & you might be able to re-attach the .mdb files after
re-installing Windows / SQL Server. To attach a db, you can either use the
sp_attach_db stored proc or the SQL Enterprise Manager has a menu option to
do it - right click on the "databases" node, select "All Tasks" then "Attach
Database" & follow the prompts..
Do you have a backup of the database? If so, this is likely the better
option if the system crashed before you lost it.
Regards,
Greg Linwood
SQL Server MVP
"bobby" <bllua@.hotmail.com> wrote in message
news:09e601c37f3f$3ea1bc80$a401280a@.phx.gbl...
> my server has 3 partitions (c: , d: ,e:). my sql 2000 is
> in d: and my win 2K is in c:. my c drive crashed and i
> would like to recover my sql database in d: after i
> reinstall my win 2K in c:. is that possible?|||"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:eQepu%230fDHA.944@.TK2MSFTNGP11.phx.gbl...
> Hi Bobby.
> If SQL Server managed to shot down successfully, it would have
checkpointed
> the databases & you might be able to re-attach the .mdb files after
> re-installing Windows / SQL Server. To attach a db, you can either use the
> sp_attach_db stored proc or the SQL Enterprise Manager has a menu option
to
> do it - right click on the "databases" node, select "All Tasks" then
"Attach
> Database" & follow the prompts..
> Do you have a backup of the database? If so, this is likely the better
> option if the system crashed before you lost it.
> Regards,
> Greg Linwood
> SQL Server MVP
Also, if you have a backup of your Master database you would not have to
re-attach the db files
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/2003

Disaster freeing space in 200GB database

Hi,
I have been developing a database which a couple of days ago was about
180GB, which almost filled up the 200GB hard drive.
In order to free space I used DBCC ShrinkDatabase(logfilename,1) on the log
file which was 20GB but this then ADDED 20GB to the database (I understand
now that you shouldn't try to shrink the log this much). Using shrink
database on the data file didn't seem to work - kept running and running with
the system idle so I eventually stopped it. I then attached a 80GB hardrive
and added a new datafile to allow the database more temporary room.
Next I tried the DELETE command on a number of tables no longer required to
have data in them (I need the tables to still exist but be empty for now so I
didn't use DROP) but this then added to the database once again and filled
the entire new 80GB drive.
I now have 280GB full and no idea how to reduce it in size. I have read
everything in Books Online and read many posts and articles on the net
without really having an idea of what to try next. Unfortunately I don't
have any room at all elsewhere to shift parts of the database around.
The transaction log is only 1.7GB and was before the DELETEs as well. Given
that DELETEs should swell the log what has happened here? - log stayed the
same size, data file increased by the amount of data deleted (80GB). What is
filling up the data file? If it is any help, I have specific tables which
are now empty of records which are where all the free space should be. There
might be some way to shift these to a small database (since empty tables
shouldn't consume much memory) and then free up the space? Not sure if this
would transfer the huge files across or if it would help anyway with
shrinking the database...
Appreciate any help at all!
TIA,
RobRobert Chapman wrote:
> Hi,
> I have been developing a database which a couple of days ago was about
> 180GB, which almost filled up the 200GB hard drive.
> In order to free space I used DBCC ShrinkDatabase(logfilename,1) on
> the log file which was 20GB but this then ADDED 20GB to the database
> (I understand now that you shouldn't try to shrink the log this
> much). Using shrink database on the data file didn't seem to work -
> kept running and running with the system idle so I eventually stopped
> it. I then attached a 80GB hardrive and added a new datafile to
> allow the database more temporary room.
> Next I tried the DELETE command on a number of tables no longer
> required to have data in them (I need the tables to still exist but
> be empty for now so I didn't use DROP) but this then added to the
> database once again and filled the entire new 80GB drive.
> I now have 280GB full and no idea how to reduce it in size. I have
> read everything in Books Online and read many posts and articles on
> the net without really having an idea of what to try next.
> Unfortunately I don't have any room at all elsewhere to shift parts
> of the database around.
> The transaction log is only 1.7GB and was before the DELETEs as well.
> Given that DELETEs should swell the log what has happened here? - log
> stayed the same size, data file increased by the amount of data
> deleted (80GB). What is filling up the data file? If it is any
> help, I have specific tables which are now empty of records which are
> where all the free space should be. There might be some way to shift
> these to a small database (since empty tables shouldn't consume much
> memory) and then free up the space? Not sure if this would transfer
> the huge files across or if it would help anyway with shrinking the
> database...
> Appreciate any help at all!
> TIA,
> Rob
You could have empty space in the files. SQL Server does not attempt to
release unused space in data or log files. Run sp_spaceused on the
database and see what space is unused. You may have more space than you
think. Then I would set an upper limit for the data file on the 200GB
drive so it does not attempt to grow beyond the drive limits.
--
David Gugick
Imceda Software
www.imceda.com|||Ouch. Lets see exactly what went wrong and how to deal with this.
Run DBCC UPDATEUSAGE to make sure you are seeing accurate information.
You may be confused about the Enterprise Manager display. Use the TASKPAD
view to get a good look at the overall space and the space used. If you do
have a lot of free data and log space, then you can proceed to recapture
that empty space.
DBCC SHRINKDATABASE is probably not the correct command. I strongly prefer
DBCC SHRINKFILE since it gives me a finer degree of control. It sounds like
your log file may not be able to accommodate what you are trying to do so
lets do it in smaller increments. Run DBCC SHRINKFILE
(MyBigHonkingDataFileName, xxxxx) where xxxx is maybe 1 or 2 GB smaller than
your existing file size. You will have to run it several times, but
eventually you will get more space.
If you want to empty a large table, try the TRUNCATE TABLE command. It
doesn't take up much log space at all. Delete does take up log space.
These suggestions should get you started. Feel free to ask back for more
help if you run into more problems.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Chapman" <RobertChapman@.discussions.microsoft.com> wrote in message
news:5440B4DA-8FF1-4DF7-88B2-B60C6B174668@.microsoft.com...
> Hi,
> I have been developing a database which a couple of days ago was about
> 180GB, which almost filled up the 200GB hard drive.
> In order to free space I used DBCC ShrinkDatabase(logfilename,1) on the
log
> file which was 20GB but this then ADDED 20GB to the database (I understand
> now that you shouldn't try to shrink the log this much). Using shrink
> database on the data file didn't seem to work - kept running and running
with
> the system idle so I eventually stopped it. I then attached a 80GB
hardrive
> and added a new datafile to allow the database more temporary room.
> Next I tried the DELETE command on a number of tables no longer required
to
> have data in them (I need the tables to still exist but be empty for now
so I
> didn't use DROP) but this then added to the database once again and filled
> the entire new 80GB drive.
> I now have 280GB full and no idea how to reduce it in size. I have read
> everything in Books Online and read many posts and articles on the net
> without really having an idea of what to try next. Unfortunately I don't
> have any room at all elsewhere to shift parts of the database around.
> The transaction log is only 1.7GB and was before the DELETEs as well.
Given
> that DELETEs should swell the log what has happened here? - log stayed the
> same size, data file increased by the amount of data deleted (80GB). What
is
> filling up the data file? If it is any help, I have specific tables which
> are now empty of records which are where all the free space should be.
There
> might be some way to shift these to a small database (since empty tables
> shouldn't consume much memory) and then free up the space? Not sure if
this
> would transfer the huge files across or if it would help anyway with
> shrinking the database...
> Appreciate any help at all!
> TIA,
> Rob|||Sounds like you have a bunch of empty text pages. Did your tables have
text/image columns in them? There's a bug in one version of shrink (fixed in
SP3) that can cause a shrink to allocate more text space - this would
account for the db growth you saw when you ran shrink on the database.
Deletes can't take more space in the data file. I suggest you contact PSS to
help you remedy this as, from what you've described, there's something
strange going on.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OTULvWa$EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Robert Chapman wrote:
> > Hi,
> >
> > I have been developing a database which a couple of days ago was about
> > 180GB, which almost filled up the 200GB hard drive.
> >
> > In order to free space I used DBCC ShrinkDatabase(logfilename,1) on
> > the log file which was 20GB but this then ADDED 20GB to the database
> > (I understand now that you shouldn't try to shrink the log this
> > much). Using shrink database on the data file didn't seem to work -
> > kept running and running with the system idle so I eventually stopped
> > it. I then attached a 80GB hardrive and added a new datafile to
> > allow the database more temporary room.
> >
> > Next I tried the DELETE command on a number of tables no longer
> > required to have data in them (I need the tables to still exist but
> > be empty for now so I didn't use DROP) but this then added to the
> > database once again and filled the entire new 80GB drive.
> >
> > I now have 280GB full and no idea how to reduce it in size. I have
> > read everything in Books Online and read many posts and articles on
> > the net without really having an idea of what to try next.
> > Unfortunately I don't have any room at all elsewhere to shift parts
> > of the database around.
> >
> > The transaction log is only 1.7GB and was before the DELETEs as well.
> > Given that DELETEs should swell the log what has happened here? - log
> > stayed the same size, data file increased by the amount of data
> > deleted (80GB). What is filling up the data file? If it is any
> > help, I have specific tables which are now empty of records which are
> > where all the free space should be. There might be some way to shift
> > these to a small database (since empty tables shouldn't consume much
> > memory) and then free up the space? Not sure if this would transfer
> > the huge files across or if it would help anyway with shrinking the
> > database...
> >
> > Appreciate any help at all!
> >
> > TIA,
> >
> > Rob
> You could have empty space in the files. SQL Server does not attempt to
> release unused space in data or log files. Run sp_spaceused on the
> database and see what space is unused. You may have more space than you
> think. Then I would set an upper limit for the data file on the 200GB
> drive so it does not attempt to grow beyond the drive limits.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||And if you ever need to recapture so much space again in the future, you
might want to try, during off-hours of course, temporarily setting the
RECOVERY MODE to SIMPLE, do your work, then set it back to FULL RECOVERY,
and make an immediate FULL DATABASE backup.
Sincerely,
Anthony Thomas
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:u5Y5jba$EHA.2540@.TK2MSFTNGP09.phx.gbl...
Ouch. Lets see exactly what went wrong and how to deal with this.
Run DBCC UPDATEUSAGE to make sure you are seeing accurate information.
You may be confused about the Enterprise Manager display. Use the TASKPAD
view to get a good look at the overall space and the space used. If you do
have a lot of free data and log space, then you can proceed to recapture
that empty space.
DBCC SHRINKDATABASE is probably not the correct command. I strongly prefer
DBCC SHRINKFILE since it gives me a finer degree of control. It sounds like
your log file may not be able to accommodate what you are trying to do so
lets do it in smaller increments. Run DBCC SHRINKFILE
(MyBigHonkingDataFileName, xxxxx) where xxxx is maybe 1 or 2 GB smaller than
your existing file size. You will have to run it several times, but
eventually you will get more space.
If you want to empty a large table, try the TRUNCATE TABLE command. It
doesn't take up much log space at all. Delete does take up log space.
These suggestions should get you started. Feel free to ask back for more
help if you run into more problems.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Chapman" <RobertChapman@.discussions.microsoft.com> wrote in message
news:5440B4DA-8FF1-4DF7-88B2-B60C6B174668@.microsoft.com...
> Hi,
> I have been developing a database which a couple of days ago was about
> 180GB, which almost filled up the 200GB hard drive.
> In order to free space I used DBCC ShrinkDatabase(logfilename,1) on the
log
> file which was 20GB but this then ADDED 20GB to the database (I understand
> now that you shouldn't try to shrink the log this much). Using shrink
> database on the data file didn't seem to work - kept running and running
with
> the system idle so I eventually stopped it. I then attached a 80GB
hardrive
> and added a new datafile to allow the database more temporary room.
> Next I tried the DELETE command on a number of tables no longer required
to
> have data in them (I need the tables to still exist but be empty for now
so I
> didn't use DROP) but this then added to the database once again and filled
> the entire new 80GB drive.
> I now have 280GB full and no idea how to reduce it in size. I have read
> everything in Books Online and read many posts and articles on the net
> without really having an idea of what to try next. Unfortunately I don't
> have any room at all elsewhere to shift parts of the database around.
> The transaction log is only 1.7GB and was before the DELETEs as well.
Given
> that DELETEs should swell the log what has happened here? - log stayed the
> same size, data file increased by the amount of data deleted (80GB). What
is
> filling up the data file? If it is any help, I have specific tables which
> are now empty of records which are where all the free space should be.
There
> might be some way to shift these to a small database (since empty tables
> shouldn't consume much memory) and then free up the space? Not sure if
this
> would transfer the huge files across or if it would help anyway with
> shrinking the database...
> Appreciate any help at all!
> TIA,
> Rob|||Hi Geoff,
Thanks for your suggestions (and everyone else's too).
I have run EXEC sp_spaceused @.updateusage = 'TRUE' with these results:
Database size Unallocated space
257672.06 MB 224.60 MB
Reserved Data Space Indexes
Unused
261840344 KB 240624184 KB 20345472 KB 870688 KB
Current Size Used pages
Estimated pages
8 3 9729984 128 9729824 9729824
So hasn't exposed any free space however about half of my tables (all the
unindexed ones) are empty so there should be plenty of unused space.
I have tried to run shrinkfile on the data file on the 2nd (80GB) hard drive
which seemed to complete saying 1 row affected and didn't reduce the size of
the file. I am going to try it again with a different value.
I have downloaded Service Pack 3 and updated but this hasn't helped. Paul,
who is PSS?
Hmm, getting a bit desperate here as very limited time to sort this out.
Need to get some idea if I'll have to bail out of this by creating a new
database elsewhere and copying tables over bit by bit (not much storage space
available) and whetehr that has a chance of sorting the problem. Another
possibility is to somehow detach the second data file that sits on the new
80GB drive as I didn't add any fresh data to that, it has simply been filled
up somehow with the results/logs (though doesn't appear as a log of course)
of my table DELETEs, is there any chance of that working?
Appreciate any more ideas if there are any out there?
Thanks,
Rob
"Geoff N. Hiten" wrote:
> Ouch. Lets see exactly what went wrong and how to deal with this.
> Run DBCC UPDATEUSAGE to make sure you are seeing accurate information.
> You may be confused about the Enterprise Manager display. Use the TASKPAD
> view to get a good look at the overall space and the space used. If you do
> have a lot of free data and log space, then you can proceed to recapture
> that empty space.
> DBCC SHRINKDATABASE is probably not the correct command. I strongly prefer
> DBCC SHRINKFILE since it gives me a finer degree of control. It sounds like
> your log file may not be able to accommodate what you are trying to do so
> lets do it in smaller increments. Run DBCC SHRINKFILE
> (MyBigHonkingDataFileName, xxxxx) where xxxx is maybe 1 or 2 GB smaller than
> your existing file size. You will have to run it several times, but
> eventually you will get more space.
> If you want to empty a large table, try the TRUNCATE TABLE command. It
> doesn't take up much log space at all. Delete does take up log space.
> These suggestions should get you started. Feel free to ask back for more
> help if you run into more problems.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>|||PSS is Microsoft Product Support Services. They offer fee-based and
subscription based support. I believe the current per-incident support
charge is $250. They take credit cards.
http://support.microsoft.com
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Chapman" <RobertChapman@.discussions.microsoft.com> wrote in message
news:49AB9EEE-C51A-4BED-84BA-8364DBBE538F@.microsoft.com...
> Hi Geoff,
> Thanks for your suggestions (and everyone else's too).
> I have run EXEC sp_spaceused @.updateusage = 'TRUE' with these results:
> Database size Unallocated space
> 257672.06 MB 224.60 MB
> Reserved Data Space Indexes
> Unused
> 261840344 KB 240624184 KB 20345472 KB 870688 KB
> Current Size Used pages
> Estimated pages
> 8 3 9729984 128 9729824 9729824
> So hasn't exposed any free space however about half of my tables (all the
> unindexed ones) are empty so there should be plenty of unused space.
> I have tried to run shrinkfile on the data file on the 2nd (80GB) hard
drive
> which seemed to complete saying 1 row affected and didn't reduce the size
of
> the file. I am going to try it again with a different value.
> I have downloaded Service Pack 3 and updated but this hasn't helped.
Paul,
> who is PSS?
> Hmm, getting a bit desperate here as very limited time to sort this out.
> Need to get some idea if I'll have to bail out of this by creating a new
> database elsewhere and copying tables over bit by bit (not much storage
space
> available) and whetehr that has a chance of sorting the problem. Another
> possibility is to somehow detach the second data file that sits on the new
> 80GB drive as I didn't add any fresh data to that, it has simply been
filled
> up somehow with the results/logs (though doesn't appear as a log of
course)
> of my table DELETEs, is there any chance of that working?
> Appreciate any more ideas if there are any out there?
> Thanks,
> Rob
> "Geoff N. Hiten" wrote:
> > Ouch. Lets see exactly what went wrong and how to deal with this.
> >
> > Run DBCC UPDATEUSAGE to make sure you are seeing accurate information.
> > You may be confused about the Enterprise Manager display. Use the
TASKPAD
> > view to get a good look at the overall space and the space used. If you
do
> > have a lot of free data and log space, then you can proceed to recapture
> > that empty space.
> >
> > DBCC SHRINKDATABASE is probably not the correct command. I strongly
prefer
> > DBCC SHRINKFILE since it gives me a finer degree of control. It sounds
like
> > your log file may not be able to accommodate what you are trying to do
so
> > lets do it in smaller increments. Run DBCC SHRINKFILE
> > (MyBigHonkingDataFileName, xxxxx) where xxxx is maybe 1 or 2 GB smaller
than
> > your existing file size. You will have to run it several times, but
> > eventually you will get more space.
> >
> > If you want to empty a large table, try the TRUNCATE TABLE command. It
> > doesn't take up much log space at all. Delete does take up log space.
> >
> > These suggestions should get you started. Feel free to ask back for
more
> > help if you run into more problems.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
>|||I don't understand this line:
"So hasn't exposed any free space however about half of my tables (all the
unindexed ones) are empty so there should be plenty of unused space."
Who cares about the empty ones? It's the full ones that you need to be
worried about.
Could you post the results of the following query? You can mask the names,
but put some other indicated in there so we have a reference that you can
translate on your side.
Here's the query:
SELECT objname = OBJECT_NAME(id), indname = [name], indid, groupid, keycnt,
maxirow
,minlen, xmaxlen, dpages, reserved, used, rowcnt, rowmodctr, pgmodctr
FROM dbo.sysindexes
WHERE indid IN(0, 1, 255)
AND id > 1000
ORDER BY reserved DESC
This will tell you which indexes have been reserved the most database pages.
INDID 0 are the data pages that use heap indexes--tables without clustered
indexes defined. INDID 1 are the datapages that use clustered indexes.
INDID 255 are the data pages allocated for BLOB/CLOB data.
I've ordered this by reserved. This will show you which tables have the
bulk of the database usage. However, take a look at the used attribute.
This will show you how many data and index pages have been allocated to this
table, sans BLOB/CLOB data. If reserved is high but used is log, you have
space that you can reclaim. However, given the output of the sp_spaceused
you provided earlier, not only do you have reserved space, 250 GB, you have
high used space, 230 GB.
Now, if you have the defaults for AUTOGROW set to 10% increments, or
increased it to 20%, and, if you were almost out of space when you added the
secondary data file, the first AUTOGROW allocation could have added anywhere
from 25 to 50 GB of new unused data and index pages. Given you have about
20 GB of unused space, I suspect this is what happened.
When was the last time you did a database reorg in with the maintenance plan
wizard or buy rebuilding all of the clustered indexes? If you use the
wizard, you can also have it move all the pages to the beginning of the
file. This would let you shrink the file more easily, which is what I
suspect was happening when you attempted the shrink operation before. It
has to move pages before it can shrink and 250 GB of data just doesn't move
that quickly.
Sincerely,
Anthony Thomas
"Robert Chapman" <RobertChapman@.discussions.microsoft.com> wrote in message
news:49AB9EEE-C51A-4BED-84BA-8364DBBE538F@.microsoft.com...
Hi Geoff,
Thanks for your suggestions (and everyone else's too).
I have run EXEC sp_spaceused @.updateusage = 'TRUE' with these results:
Database size Unallocated space
257672.06 MB 224.60 MB
Reserved Data Space Indexes
Unused
261840344 KB 240624184 KB 20345472 KB 870688 KB
Current Size Used pages
Estimated pages
8 3 9729984 128 9729824 9729824
So hasn't exposed any free space however about half of my tables (all the
unindexed ones) are empty so there should be plenty of unused space.
I have tried to run shrinkfile on the data file on the 2nd (80GB) hard drive
which seemed to complete saying 1 row affected and didn't reduce the size of
the file. I am going to try it again with a different value.
I have downloaded Service Pack 3 and updated but this hasn't helped. Paul,
who is PSS?
Hmm, getting a bit desperate here as very limited time to sort this out.
Need to get some idea if I'll have to bail out of this by creating a new
database elsewhere and copying tables over bit by bit (not much storage
space
available) and whetehr that has a chance of sorting the problem. Another
possibility is to somehow detach the second data file that sits on the new
80GB drive as I didn't add any fresh data to that, it has simply been filled
up somehow with the results/logs (though doesn't appear as a log of course)
of my table DELETEs, is there any chance of that working?
Appreciate any more ideas if there are any out there?
Thanks,
Rob
"Geoff N. Hiten" wrote:
> Ouch. Lets see exactly what went wrong and how to deal with this.
> Run DBCC UPDATEUSAGE to make sure you are seeing accurate information.
> You may be confused about the Enterprise Manager display. Use the TASKPAD
> view to get a good look at the overall space and the space used. If you
do
> have a lot of free data and log space, then you can proceed to recapture
> that empty space.
> DBCC SHRINKDATABASE is probably not the correct command. I strongly
prefer
> DBCC SHRINKFILE since it gives me a finer degree of control. It sounds
like
> your log file may not be able to accommodate what you are trying to do so
> lets do it in smaller increments. Run DBCC SHRINKFILE
> (MyBigHonkingDataFileName, xxxxx) where xxxx is maybe 1 or 2 GB smaller
than
> your existing file size. You will have to run it several times, but
> eventually you will get more space.
> If you want to empty a large table, try the TRUNCATE TABLE command. It
> doesn't take up much log space at all. Delete does take up log space.
> These suggestions should get you started. Feel free to ask back for more
> help if you run into more problems.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>|||Hi Anthony,
Thanks a whole lot for your post, it led to a solution. What I found is
that the problem wasn't the DELETEs run but rather a table that had swelled
to almost 100GB! Not quite sure how it occured but there it was sitting at
the top of the list in the query you gave me with rather an unrealistic
number of rows. A quick TRUNCATE TABLE solved the problem and I have used
DBCC SHRINKFILE to return things to order.
Many thanks for your help!
Rob
"Anthony Thomas" wrote:
> I don't understand this line:
> "So hasn't exposed any free space however about half of my tables (all the
> unindexed ones) are empty so there should be plenty of unused space."
> Who cares about the empty ones? It's the full ones that you need to be
> worried about.
> Could you post the results of the following query? You can mask the names,
> but put some other indicated in there so we have a reference that you can
> translate on your side.
> Here's the query:
> SELECT objname = OBJECT_NAME(id), indname = [name], indid, groupid, keycnt,
> maxirow
> ,minlen, xmaxlen, dpages, reserved, used, rowcnt, rowmodctr, pgmodctr
> FROM dbo.sysindexes
> WHERE indid IN(0, 1, 255)
> AND id > 1000
> ORDER BY reserved DESC
> This will tell you which indexes have been reserved the most database pages.
> INDID 0 are the data pages that use heap indexes--tables without clustered
> indexes defined. INDID 1 are the datapages that use clustered indexes.
> INDID 255 are the data pages allocated for BLOB/CLOB data.
> I've ordered this by reserved. This will show you which tables have the
> bulk of the database usage. However, take a look at the used attribute.
> This will show you how many data and index pages have been allocated to this
> table, sans BLOB/CLOB data. If reserved is high but used is log, you have
> space that you can reclaim. However, given the output of the sp_spaceused
> you provided earlier, not only do you have reserved space, 250 GB, you have
> high used space, 230 GB.
> Now, if you have the defaults for AUTOGROW set to 10% increments, or
> increased it to 20%, and, if you were almost out of space when you added the
> secondary data file, the first AUTOGROW allocation could have added anywhere
> from 25 to 50 GB of new unused data and index pages. Given you have about
> 20 GB of unused space, I suspect this is what happened.
> When was the last time you did a database reorg in with the maintenance plan
> wizard or buy rebuilding all of the clustered indexes? If you use the
> wizard, you can also have it move all the pages to the beginning of the
> file. This would let you shrink the file more easily, which is what I
> suspect was happening when you attempted the shrink operation before. It
> has to move pages before it can shrink and 250 GB of data just doesn't move
> that quickly.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Robert Chapman" <RobertChapman@.discussions.microsoft.com> wrote in message
> news:49AB9EEE-C51A-4BED-84BA-8364DBBE538F@.microsoft.com...
> Hi Geoff,
> Thanks for your suggestions (and everyone else's too).
> I have run EXEC sp_spaceused @.updateusage = 'TRUE' with these results:
> Database size Unallocated space
> 257672.06 MB 224.60 MB
> Reserved Data Space Indexes
> Unused
> 261840344 KB 240624184 KB 20345472 KB 870688 KB
> Current Size Used pages
> Estimated pages
> 8 3 9729984 128 9729824 9729824
> So hasn't exposed any free space however about half of my tables (all the
> unindexed ones) are empty so there should be plenty of unused space.
> I have tried to run shrinkfile on the data file on the 2nd (80GB) hard drive
> which seemed to complete saying 1 row affected and didn't reduce the size of
> the file. I am going to try it again with a different value.
> I have downloaded Service Pack 3 and updated but this hasn't helped. Paul,
> who is PSS?
> Hmm, getting a bit desperate here as very limited time to sort this out.
> Need to get some idea if I'll have to bail out of this by creating a new
> database elsewhere and copying tables over bit by bit (not much storage
> space
> available) and whetehr that has a chance of sorting the problem. Another
> possibility is to somehow detach the second data file that sits on the new
> 80GB drive as I didn't add any fresh data to that, it has simply been filled
> up somehow with the results/logs (though doesn't appear as a log of course)
> of my table DELETEs, is there any chance of that working?
> Appreciate any more ideas if there are any out there?
> Thanks,
> Rob
> "Geoff N. Hiten" wrote:
> > Ouch. Lets see exactly what went wrong and how to deal with this.
> >
> > Run DBCC UPDATEUSAGE to make sure you are seeing accurate information.
> > You may be confused about the Enterprise Manager display. Use the TASKPAD
> > view to get a good look at the overall space and the space used. If you
> do
> > have a lot of free data and log space, then you can proceed to recapture
> > that empty space.
> >
> > DBCC SHRINKDATABASE is probably not the correct command. I strongly
> prefer
> > DBCC SHRINKFILE since it gives me a finer degree of control. It sounds
> like
> > your log file may not be able to accommodate what you are trying to do so
> > lets do it in smaller increments. Run DBCC SHRINKFILE
> > (MyBigHonkingDataFileName, xxxxx) where xxxx is maybe 1 or 2 GB smaller
> than
> > your existing file size. You will have to run it several times, but
> > eventually you will get more space.
> >
> > If you want to empty a large table, try the TRUNCATE TABLE command. It
> > doesn't take up much log space at all. Delete does take up log space.
> >
> > These suggestions should get you started. Feel free to ask back for more
> > help if you run into more problems.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
>
>

Disaster freeing space in 200GB database

Hi,
I have been developing a database which a couple of days ago was about
180GB, which almost filled up the 200GB hard drive.
In order to free space I used DBCC ShrinkDatabase(logfilename,1) on the log
file which was 20GB but this then ADDED 20GB to the database (I understand
now that you shouldn't try to shrink the log this much). Using shrink
database on the data file didn't seem to work - kept running and running with
the system idle so I eventually stopped it. I then attached a 80GB hardrive
and added a new datafile to allow the database more temporary room.
Next I tried the DELETE command on a number of tables no longer required to
have data in them (I need the tables to still exist but be empty for now so I
didn't use DROP) but this then added to the database once again and filled
the entire new 80GB drive.
I now have 280GB full and no idea how to reduce it in size. I have read
everything in Books Online and read many posts and articles on the net
without really having an idea of what to try next. Unfortunately I don't
have any room at all elsewhere to shift parts of the database around.
The transaction log is only 1.7GB and was before the DELETEs as well. Given
that DELETEs should swell the log what has happened here? - log stayed the
same size, data file increased by the amount of data deleted (80GB). What is
filling up the data file? If it is any help, I have specific tables which
are now empty of records which are where all the free space should be. There
might be some way to shift these to a small database (since empty tables
shouldn't consume much memory) and then free up the space? Not sure if this
would transfer the huge files across or if it would help anyway with
shrinking the database...
Appreciate any help at all!
TIA,
Rob
Robert Chapman wrote:
> Hi,
> I have been developing a database which a couple of days ago was about
> 180GB, which almost filled up the 200GB hard drive.
> In order to free space I used DBCC ShrinkDatabase(logfilename,1) on
> the log file which was 20GB but this then ADDED 20GB to the database
> (I understand now that you shouldn't try to shrink the log this
> much). Using shrink database on the data file didn't seem to work -
> kept running and running with the system idle so I eventually stopped
> it. I then attached a 80GB hardrive and added a new datafile to
> allow the database more temporary room.
> Next I tried the DELETE command on a number of tables no longer
> required to have data in them (I need the tables to still exist but
> be empty for now so I didn't use DROP) but this then added to the
> database once again and filled the entire new 80GB drive.
> I now have 280GB full and no idea how to reduce it in size. I have
> read everything in Books Online and read many posts and articles on
> the net without really having an idea of what to try next.
> Unfortunately I don't have any room at all elsewhere to shift parts
> of the database around.
> The transaction log is only 1.7GB and was before the DELETEs as well.
> Given that DELETEs should swell the log what has happened here? - log
> stayed the same size, data file increased by the amount of data
> deleted (80GB). What is filling up the data file? If it is any
> help, I have specific tables which are now empty of records which are
> where all the free space should be. There might be some way to shift
> these to a small database (since empty tables shouldn't consume much
> memory) and then free up the space? Not sure if this would transfer
> the huge files across or if it would help anyway with shrinking the
> database...
> Appreciate any help at all!
> TIA,
> Rob
You could have empty space in the files. SQL Server does not attempt to
release unused space in data or log files. Run sp_spaceused on the
database and see what space is unused. You may have more space than you
think. Then I would set an upper limit for the data file on the 200GB
drive so it does not attempt to grow beyond the drive limits.
David Gugick
Imceda Software
www.imceda.com
|||Ouch. Lets see exactly what went wrong and how to deal with this.
Run DBCC UPDATEUSAGE to make sure you are seeing accurate information.
You may be confused about the Enterprise Manager display. Use the TASKPAD
view to get a good look at the overall space and the space used. If you do
have a lot of free data and log space, then you can proceed to recapture
that empty space.
DBCC SHRINKDATABASE is probably not the correct command. I strongly prefer
DBCC SHRINKFILE since it gives me a finer degree of control. It sounds like
your log file may not be able to accommodate what you are trying to do so
lets do it in smaller increments. Run DBCC SHRINKFILE
(MyBigHonkingDataFileName, xxxxx) where xxxx is maybe 1 or 2 GB smaller than
your existing file size. You will have to run it several times, but
eventually you will get more space.
If you want to empty a large table, try the TRUNCATE TABLE command. It
doesn't take up much log space at all. Delete does take up log space.
These suggestions should get you started. Feel free to ask back for more
help if you run into more problems.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Chapman" <RobertChapman@.discussions.microsoft.com> wrote in message
news:5440B4DA-8FF1-4DF7-88B2-B60C6B174668@.microsoft.com...
> Hi,
> I have been developing a database which a couple of days ago was about
> 180GB, which almost filled up the 200GB hard drive.
> In order to free space I used DBCC ShrinkDatabase(logfilename,1) on the
log
> file which was 20GB but this then ADDED 20GB to the database (I understand
> now that you shouldn't try to shrink the log this much). Using shrink
> database on the data file didn't seem to work - kept running and running
with
> the system idle so I eventually stopped it. I then attached a 80GB
hardrive
> and added a new datafile to allow the database more temporary room.
> Next I tried the DELETE command on a number of tables no longer required
to
> have data in them (I need the tables to still exist but be empty for now
so I
> didn't use DROP) but this then added to the database once again and filled
> the entire new 80GB drive.
> I now have 280GB full and no idea how to reduce it in size. I have read
> everything in Books Online and read many posts and articles on the net
> without really having an idea of what to try next. Unfortunately I don't
> have any room at all elsewhere to shift parts of the database around.
> The transaction log is only 1.7GB and was before the DELETEs as well.
Given
> that DELETEs should swell the log what has happened here? - log stayed the
> same size, data file increased by the amount of data deleted (80GB). What
is
> filling up the data file? If it is any help, I have specific tables which
> are now empty of records which are where all the free space should be.
There
> might be some way to shift these to a small database (since empty tables
> shouldn't consume much memory) and then free up the space? Not sure if
this
> would transfer the huge files across or if it would help anyway with
> shrinking the database...
> Appreciate any help at all!
> TIA,
> Rob
|||Sounds like you have a bunch of empty text pages. Did your tables have
text/image columns in them? There's a bug in one version of shrink (fixed in
SP3) that can cause a shrink to allocate more text space - this would
account for the db growth you saw when you ran shrink on the database.
Deletes can't take more space in the data file. I suggest you contact PSS to
help you remedy this as, from what you've described, there's something
strange going on.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OTULvWa$EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Robert Chapman wrote:
> You could have empty space in the files. SQL Server does not attempt to
> release unused space in data or log files. Run sp_spaceused on the
> database and see what space is unused. You may have more space than you
> think. Then I would set an upper limit for the data file on the 200GB
> drive so it does not attempt to grow beyond the drive limits.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||And if you ever need to recapture so much space again in the future, you
might want to try, during off-hours of course, temporarily setting the
RECOVERY MODE to SIMPLE, do your work, then set it back to FULL RECOVERY,
and make an immediate FULL DATABASE backup.
Sincerely,
Anthony Thomas

"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:u5Y5jba$EHA.2540@.TK2MSFTNGP09.phx.gbl...
Ouch. Lets see exactly what went wrong and how to deal with this.
Run DBCC UPDATEUSAGE to make sure you are seeing accurate information.
You may be confused about the Enterprise Manager display. Use the TASKPAD
view to get a good look at the overall space and the space used. If you do
have a lot of free data and log space, then you can proceed to recapture
that empty space.
DBCC SHRINKDATABASE is probably not the correct command. I strongly prefer
DBCC SHRINKFILE since it gives me a finer degree of control. It sounds like
your log file may not be able to accommodate what you are trying to do so
lets do it in smaller increments. Run DBCC SHRINKFILE
(MyBigHonkingDataFileName, xxxxx) where xxxx is maybe 1 or 2 GB smaller than
your existing file size. You will have to run it several times, but
eventually you will get more space.
If you want to empty a large table, try the TRUNCATE TABLE command. It
doesn't take up much log space at all. Delete does take up log space.
These suggestions should get you started. Feel free to ask back for more
help if you run into more problems.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Chapman" <RobertChapman@.discussions.microsoft.com> wrote in message
news:5440B4DA-8FF1-4DF7-88B2-B60C6B174668@.microsoft.com...
> Hi,
> I have been developing a database which a couple of days ago was about
> 180GB, which almost filled up the 200GB hard drive.
> In order to free space I used DBCC ShrinkDatabase(logfilename,1) on the
log
> file which was 20GB but this then ADDED 20GB to the database (I understand
> now that you shouldn't try to shrink the log this much). Using shrink
> database on the data file didn't seem to work - kept running and running
with
> the system idle so I eventually stopped it. I then attached a 80GB
hardrive
> and added a new datafile to allow the database more temporary room.
> Next I tried the DELETE command on a number of tables no longer required
to
> have data in them (I need the tables to still exist but be empty for now
so I
> didn't use DROP) but this then added to the database once again and filled
> the entire new 80GB drive.
> I now have 280GB full and no idea how to reduce it in size. I have read
> everything in Books Online and read many posts and articles on the net
> without really having an idea of what to try next. Unfortunately I don't
> have any room at all elsewhere to shift parts of the database around.
> The transaction log is only 1.7GB and was before the DELETEs as well.
Given
> that DELETEs should swell the log what has happened here? - log stayed the
> same size, data file increased by the amount of data deleted (80GB). What
is
> filling up the data file? If it is any help, I have specific tables which
> are now empty of records which are where all the free space should be.
There
> might be some way to shift these to a small database (since empty tables
> shouldn't consume much memory) and then free up the space? Not sure if
this
> would transfer the huge files across or if it would help anyway with
> shrinking the database...
> Appreciate any help at all!
> TIA,
> Rob
|||Hi Geoff,
Thanks for your suggestions (and everyone else's too).
I have run EXEC sp_spaceused @.updateusage = 'TRUE' with these results:
Database size Unallocated space
257672.06 MB224.60 MB
Reserved Data Space Indexes
Unused
261840344 KB240624184 KB20345472 KB 870688 KB
Current Size Used pages
Estimated pages
839729984128 9729824 9729824
So hasn't exposed any free space however about half of my tables (all the
unindexed ones) are empty so there should be plenty of unused space.
I have tried to run shrinkfile on the data file on the 2nd (80GB) hard drive
which seemed to complete saying 1 row affected and didn't reduce the size of
the file. I am going to try it again with a different value.
I have downloaded Service Pack 3 and updated but this hasn't helped. Paul,
who is PSS?
Hmm, getting a bit desperate here as very limited time to sort this out.
Need to get some idea if I'll have to bail out of this by creating a new
database elsewhere and copying tables over bit by bit (not much storage space
available) and whetehr that has a chance of sorting the problem. Another
possibility is to somehow detach the second data file that sits on the new
80GB drive as I didn't add any fresh data to that, it has simply been filled
up somehow with the results/logs (though doesn't appear as a log of course)
of my table DELETEs, is there any chance of that working?
Appreciate any more ideas if there are any out there?
Thanks,
Rob
"Geoff N. Hiten" wrote:

> Ouch. Lets see exactly what went wrong and how to deal with this.
> Run DBCC UPDATEUSAGE to make sure you are seeing accurate information.
> You may be confused about the Enterprise Manager display. Use the TASKPAD
> view to get a good look at the overall space and the space used. If you do
> have a lot of free data and log space, then you can proceed to recapture
> that empty space.
> DBCC SHRINKDATABASE is probably not the correct command. I strongly prefer
> DBCC SHRINKFILE since it gives me a finer degree of control. It sounds like
> your log file may not be able to accommodate what you are trying to do so
> lets do it in smaller increments. Run DBCC SHRINKFILE
> (MyBigHonkingDataFileName, xxxxx) where xxxx is maybe 1 or 2 GB smaller than
> your existing file size. You will have to run it several times, but
> eventually you will get more space.
> If you want to empty a large table, try the TRUNCATE TABLE command. It
> doesn't take up much log space at all. Delete does take up log space.
> These suggestions should get you started. Feel free to ask back for more
> help if you run into more problems.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
|||PSS is Microsoft Product Support Services. They offer fee-based and
subscription based support. I believe the current per-incident support
charge is $250. They take credit cards.
http://support.microsoft.com
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Chapman" <RobertChapman@.discussions.microsoft.com> wrote in message
news:49AB9EEE-C51A-4BED-84BA-8364DBBE538F@.microsoft.com...
> Hi Geoff,
> Thanks for your suggestions (and everyone else's too).
> I have run EXEC sp_spaceused @.updateusage = 'TRUE' with these results:
> Database size Unallocated space
> 257672.06 MB 224.60 MB
> Reserved Data Space Indexes
> Unused
> 261840344 KB 240624184 KB 20345472 KB 870688 KB
> Current Size Used pages
> Estimated pages
> 8 3 9729984 128 9729824 9729824
> So hasn't exposed any free space however about half of my tables (all the
> unindexed ones) are empty so there should be plenty of unused space.
> I have tried to run shrinkfile on the data file on the 2nd (80GB) hard
drive
> which seemed to complete saying 1 row affected and didn't reduce the size
of
> the file. I am going to try it again with a different value.
> I have downloaded Service Pack 3 and updated but this hasn't helped.
Paul,
> who is PSS?
> Hmm, getting a bit desperate here as very limited time to sort this out.
> Need to get some idea if I'll have to bail out of this by creating a new
> database elsewhere and copying tables over bit by bit (not much storage
space
> available) and whetehr that has a chance of sorting the problem. Another
> possibility is to somehow detach the second data file that sits on the new
> 80GB drive as I didn't add any fresh data to that, it has simply been
filled
> up somehow with the results/logs (though doesn't appear as a log of
course)[vbcol=seagreen]
> of my table DELETEs, is there any chance of that working?
> Appreciate any more ideas if there are any out there?
> Thanks,
> Rob
> "Geoff N. Hiten" wrote:
TASKPAD[vbcol=seagreen]
do[vbcol=seagreen]
prefer[vbcol=seagreen]
like[vbcol=seagreen]
so[vbcol=seagreen]
than[vbcol=seagreen]
more
>
|||I don't understand this line:
"So hasn't exposed any free space however about half of my tables (all the
unindexed ones) are empty so there should be plenty of unused space."
Who cares about the empty ones? It's the full ones that you need to be
worried about.
Could you post the results of the following query? You can mask the names,
but put some other indicated in there so we have a reference that you can
translate on your side.
Here's the query:
SELECT objname = OBJECT_NAME(id), indname = [name], indid, groupid, keycnt,
maxirow
,minlen, xmaxlen, dpages, reserved, used, rowcnt, rowmodctr, pgmodctr
FROM dbo.sysindexes
WHERE indid IN(0, 1, 255)
AND id > 1000
ORDER BY reserved DESC
This will tell you which indexes have been reserved the most database pages.
INDID 0 are the data pages that use heap indexes--tables without clustered
indexes defined. INDID 1 are the datapages that use clustered indexes.
INDID 255 are the data pages allocated for BLOB/CLOB data.
I've ordered this by reserved. This will show you which tables have the
bulk of the database usage. However, take a look at the used attribute.
This will show you how many data and index pages have been allocated to this
table, sans BLOB/CLOB data. If reserved is high but used is log, you have
space that you can reclaim. However, given the output of the sp_spaceused
you provided earlier, not only do you have reserved space, 250 GB, you have
high used space, 230 GB.
Now, if you have the defaults for AUTOGROW set to 10% increments, or
increased it to 20%, and, if you were almost out of space when you added the
secondary data file, the first AUTOGROW allocation could have added anywhere
from 25 to 50 GB of new unused data and index pages. Given you have about
20 GB of unused space, I suspect this is what happened.
When was the last time you did a database reorg in with the maintenance plan
wizard or buy rebuilding all of the clustered indexes? If you use the
wizard, you can also have it move all the pages to the beginning of the
file. This would let you shrink the file more easily, which is what I
suspect was happening when you attempted the shrink operation before. It
has to move pages before it can shrink and 250 GB of data just doesn't move
that quickly.
Sincerely,
Anthony Thomas

"Robert Chapman" <RobertChapman@.discussions.microsoft.com> wrote in message
news:49AB9EEE-C51A-4BED-84BA-8364DBBE538F@.microsoft.com...
Hi Geoff,
Thanks for your suggestions (and everyone else's too).
I have run EXEC sp_spaceused @.updateusage = 'TRUE' with these results:
Database size Unallocated space
257672.06 MB 224.60 MB
Reserved Data Space Indexes
Unused
261840344 KB 240624184 KB 20345472 KB 870688 KB
Current Size Used pages
Estimated pages
8 3 9729984 128 9729824 9729824
So hasn't exposed any free space however about half of my tables (all the
unindexed ones) are empty so there should be plenty of unused space.
I have tried to run shrinkfile on the data file on the 2nd (80GB) hard drive
which seemed to complete saying 1 row affected and didn't reduce the size of
the file. I am going to try it again with a different value.
I have downloaded Service Pack 3 and updated but this hasn't helped. Paul,
who is PSS?
Hmm, getting a bit desperate here as very limited time to sort this out.
Need to get some idea if I'll have to bail out of this by creating a new
database elsewhere and copying tables over bit by bit (not much storage
space
available) and whetehr that has a chance of sorting the problem. Another
possibility is to somehow detach the second data file that sits on the new
80GB drive as I didn't add any fresh data to that, it has simply been filled
up somehow with the results/logs (though doesn't appear as a log of course)
of my table DELETEs, is there any chance of that working?
Appreciate any more ideas if there are any out there?
Thanks,
Rob
"Geoff N. Hiten" wrote:

> Ouch. Lets see exactly what went wrong and how to deal with this.
> Run DBCC UPDATEUSAGE to make sure you are seeing accurate information.
> You may be confused about the Enterprise Manager display. Use the TASKPAD
> view to get a good look at the overall space and the space used. If you
do
> have a lot of free data and log space, then you can proceed to recapture
> that empty space.
> DBCC SHRINKDATABASE is probably not the correct command. I strongly
prefer
> DBCC SHRINKFILE since it gives me a finer degree of control. It sounds
like
> your log file may not be able to accommodate what you are trying to do so
> lets do it in smaller increments. Run DBCC SHRINKFILE
> (MyBigHonkingDataFileName, xxxxx) where xxxx is maybe 1 or 2 GB smaller
than
> your existing file size. You will have to run it several times, but
> eventually you will get more space.
> If you want to empty a large table, try the TRUNCATE TABLE command. It
> doesn't take up much log space at all. Delete does take up log space.
> These suggestions should get you started. Feel free to ask back for more
> help if you run into more problems.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
|||Hi Anthony,
Thanks a whole lot for your post, it led to a solution. What I found is
that the problem wasn't the DELETEs run but rather a table that had swelled
to almost 100GB! Not quite sure how it occured but there it was sitting at
the top of the list in the query you gave me with rather an unrealistic
number of rows. A quick TRUNCATE TABLE solved the problem and I have used
DBCC SHRINKFILE to return things to order.
Many thanks for your help!
Rob
"Anthony Thomas" wrote:

> I don't understand this line:
> "So hasn't exposed any free space however about half of my tables (all the
> unindexed ones) are empty so there should be plenty of unused space."
> Who cares about the empty ones? It's the full ones that you need to be
> worried about.
> Could you post the results of the following query? You can mask the names,
> but put some other indicated in there so we have a reference that you can
> translate on your side.
> Here's the query:
> SELECT objname = OBJECT_NAME(id), indname = [name], indid, groupid, keycnt,
> maxirow
> ,minlen, xmaxlen, dpages, reserved, used, rowcnt, rowmodctr, pgmodctr
> FROM dbo.sysindexes
> WHERE indid IN(0, 1, 255)
> AND id > 1000
> ORDER BY reserved DESC
> This will tell you which indexes have been reserved the most database pages.
> INDID 0 are the data pages that use heap indexes--tables without clustered
> indexes defined. INDID 1 are the datapages that use clustered indexes.
> INDID 255 are the data pages allocated for BLOB/CLOB data.
> I've ordered this by reserved. This will show you which tables have the
> bulk of the database usage. However, take a look at the used attribute.
> This will show you how many data and index pages have been allocated to this
> table, sans BLOB/CLOB data. If reserved is high but used is log, you have
> space that you can reclaim. However, given the output of the sp_spaceused
> you provided earlier, not only do you have reserved space, 250 GB, you have
> high used space, 230 GB.
> Now, if you have the defaults for AUTOGROW set to 10% increments, or
> increased it to 20%, and, if you were almost out of space when you added the
> secondary data file, the first AUTOGROW allocation could have added anywhere
> from 25 to 50 GB of new unused data and index pages. Given you have about
> 20 GB of unused space, I suspect this is what happened.
> When was the last time you did a database reorg in with the maintenance plan
> wizard or buy rebuilding all of the clustered indexes? If you use the
> wizard, you can also have it move all the pages to the beginning of the
> file. This would let you shrink the file more easily, which is what I
> suspect was happening when you attempted the shrink operation before. It
> has to move pages before it can shrink and 250 GB of data just doesn't move
> that quickly.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Robert Chapman" <RobertChapman@.discussions.microsoft.com> wrote in message
> news:49AB9EEE-C51A-4BED-84BA-8364DBBE538F@.microsoft.com...
> Hi Geoff,
> Thanks for your suggestions (and everyone else's too).
> I have run EXEC sp_spaceused @.updateusage = 'TRUE' with these results:
> Database size Unallocated space
> 257672.06 MB 224.60 MB
> Reserved Data Space Indexes
> Unused
> 261840344 KB 240624184 KB 20345472 KB 870688 KB
> Current Size Used pages
> Estimated pages
> 8 3 9729984 128 9729824 9729824
> So hasn't exposed any free space however about half of my tables (all the
> unindexed ones) are empty so there should be plenty of unused space.
> I have tried to run shrinkfile on the data file on the 2nd (80GB) hard drive
> which seemed to complete saying 1 row affected and didn't reduce the size of
> the file. I am going to try it again with a different value.
> I have downloaded Service Pack 3 and updated but this hasn't helped. Paul,
> who is PSS?
> Hmm, getting a bit desperate here as very limited time to sort this out.
> Need to get some idea if I'll have to bail out of this by creating a new
> database elsewhere and copying tables over bit by bit (not much storage
> space
> available) and whetehr that has a chance of sorting the problem. Another
> possibility is to somehow detach the second data file that sits on the new
> 80GB drive as I didn't add any fresh data to that, it has simply been filled
> up somehow with the results/logs (though doesn't appear as a log of course)
> of my table DELETEs, is there any chance of that working?
> Appreciate any more ideas if there are any out there?
> Thanks,
> Rob
> "Geoff N. Hiten" wrote:
> do
> prefer
> like
> than
>
>