does importing a trace file via FN_TRACE_GETTABLE use the tempDB?

i need to look at some large trace files (3 - 8 Gig). The drive that
has the tempDB on is is small..currently has < 2 G of free space on
it.
does the FN_TRACE_GETTABLE() function route the import through the
tempDB?
if so is there a way to avoid this?
I don't want to choke the root drive of the server importing this...I have not seen it being spooled on a simple select into from a trace file
but you might just want to keep an eye on it.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<kent@.kaeservices.com> wrote in message
news:29307ecd-75d3-42e1-ba4a-06d67f0c5c14@.d4g2000prg.googlegroups.com...
>i need to look at some large trace files (3 - 8 Gig). The drive that
> has the tempDB on is is small..currently has < 2 G of free space on
> it.
> does the FN_TRACE_GETTABLE() function route the import through the
> tempDB?
> if so is there a way to avoid this?
> I don't want to choke the root drive of the server importing this...

does importing a trace file via FN_TRACE_GETTABLE use the tempDB?

i need to look at some large trace files (3 - 8 Gig). The drive that
has the tempDB on is is small..currently has < 2 G of free space on
it.
does the FN_TRACE_GETTABLE() function route the import through the
tempDB?
if so is there a way to avoid this?
I don't want to choke the root drive of the server importing this...
I have not seen it being spooled on a simple select into from a trace file
but you might just want to keep an eye on it.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<kent@.kaeservices.com> wrote in message
news:29307ecd-75d3-42e1-ba4a-06d67f0c5c14@.d4g2000prg.googlegroups.com...
>i need to look at some large trace files (3 - 8 Gig). The drive that
> has the tempDB on is is small..currently has < 2 G of free space on
> it.
> does the FN_TRACE_GETTABLE() function route the import through the
> tempDB?
> if so is there a way to avoid this?
> I don't want to choke the root drive of the server importing this...

Does Disk space affect Database performance

We have aroud 12 databases running on a live production server which is
segmented
into 3 physical drives.
The E drive is where data and logs are stored.
We have implemented a back up strategy over the past week. As a result , the
E
drive has shrunk in size.
Its a total of 135 GB but has 125 MB free.
Consequently we have noticed that various VB6 and MS Access applications are
suffering from slow performance.
But..the apps are merely querying data and bringing it back to the client.
My question is the lack of disk space affecting performance?
The lack of disk space will cause performance problems, iirc the MS
recommendation is to not exceed about 80% capacity on a disk/disk set.
Not sure what you mean by backing up causing the disk to shrink in
size? Also not a good idea to have log and data files on the same
physical drive.
ALI

Does Disk space affect Database performance

We have aroud 12 databases running on a live production server which is
segmented
into 3 physical drives.
The E drive is where data and logs are stored.
We have implemented a back up strategy over the past week. As a result , the
E
drive has shrunk in size.
Its a total of 135 GB but has 125 MB free.
Consequently we have noticed that various VB6 and MS Access applications are
suffering from slow performance.
But..the apps are merely querying data and bringing it back to the client.
My question is the lack of disk space affecting performance?The lack of disk space will cause performance problems, iirc the MS
recommendation is to not exceed about 80% capacity on a disk/disk set.
Not sure what you mean by backing up causing the disk to shrink in
size? Also not a good idea to have log and data files on the same
physical drive.
ALI

Does Disk space affect Database performance

We have aroud 12 databases running on a live production server which is
segmented
into 3 physical drives.
The E drive is where data and logs are stored.
We have implemented a back up strategy over the past week. As a result , the
E
drive has shrunk in size.
Its a total of 135 GB but has 125 MB free.
Consequently we have noticed that various VB6 and MS Access applications are
suffering from slow performance.
But..the apps are merely querying data and bringing it back to the client.
My question is the lack of disk space affecting performance?The lack of disk space will cause performance problems, iirc the MS
recommendation is to not exceed about 80% capacity on a disk/disk set.
Not sure what you mean by backing up causing the disk to shrink in
size? Also not a good idea to have log and data files on the same
physical drive.
ALI

Does DBCC SHRINKFILE on a data file cause the log to grow?

We have 60GB free of a 140GB data file in one of our databases in SQL
Server 2000 SP2. I've truncated the free space at the end, but it won't
shrink any further now. I tried running DBCC SHRINKFILE, but had to
cancel it and run a log backup when the transaction log grew to 40GB and
started threatening the free disk space. Is this expected behaviour.
If it is, can anybody recommend a way to shrink the data file without
growing the log - e.g. do I have to temporarily change the database
model to simple, or something like that?
Cheers,
MalcMalcolm
Make a long story shortly
1)Perfom BACKUP LOG file ( it removes all inactive transaction )
2)Pefrom DBCC SHRINKFILE to reduce physical size of the log file.
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Sometimes, if this fails, you have to put some transactions into the
database to roll the virtual log to the beginning of the physical log. I've
had success with:
1) Backup the log
2) Run DBCC SHRINKFILE
3) If step 2 does not work, create a temp table in the database and add 1000
rows.
4) Delete the rows and the temp table. This will create t-log entries that
will force the virtual log to roll to the frony of the physical file (See
BOL for details on t-log architecture).
5) Backup the log.
6) Run DBCC SHRINKFILE again. It should work this time. If not, repeat
from 3.
The process is documented in a Q article somewhere for SQL 7. You are not
supposed to need it for SQL 2000 but I havbe found it comes in handy.
Christian Smith
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Yes this is expected behavior. When you shrink a data file it has to
physically move all data at the end of the file towards the beginning and
each move is fully logged. I would shrink in smaller increments and backup
the log after each one to keep it in check.
Andrew J. Kelly
SQL Server MVP
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Guys,
Thanks for the advice, but I'm not trying to shrink the log file. I'm
trying to shrink the *DATA* file. However, whenever I try this, the log
file starts growing like crazy.
Please advice further,
Malc
Christian Smith wrote:

>Sometimes, if this fails, you have to put some transactions into the
>database to roll the virtual log to the beginning of the physical log. I'v
e
>had success with:
>1) Backup the log
>2) Run DBCC SHRINKFILE
>3) If step 2 does not work, create a temp table in the database and add 100
0
>rows.
>4) Delete the rows and the temp table. This will create t-log entries tha
t
>will force the virtual log to roll to the frony of the physical file (See
>BOL for details on t-log architecture).
>5) Backup the log.
>6) Run DBCC SHRINKFILE again. It should work this time. If not, repeat
>from 3.
>The process is documented in a Q article somewhere for SQL 7. You are not
>supposed to need it for SQL 2000 but I havbe found it comes in handy.
>Christian Smith
>"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
>message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
>
>
>|||Andrew J. Kelly wrote:

>Yes this is expected behavior. When you shrink a data file it has to
>physically move all data at the end of the file towards the beginning and
>each move is fully logged. I would shrink in smaller increments and backup
>the log after each one to keep it in check.
>
Andrew,
That's interesting. If I understand you correctly, when trying to
recover some of this 61,000MB of slack space, I should do the following:
DBCC SHRINKFILE (MyDb_Data, 140000)
-> Backup log
DBCC SHRINKFILE (MyDb_Data, 135000)
-> Backup log
DBCC ... etc.
rather than:
DBCC SHRINKFILE (MyDb_Data)
or
DBCC SHRINKFILE (MyDb_Data, 85000)
If I do this, you're saying it will consume less log space?
Cheers,
Malc|||yes. shrinking the data file(s) will fill up your log file. you could try
setting your recovery mode to simple before doing the shrinkfile. you'll st
ill
need some available space in your log file even with simple recovery.
Malcolm Ferguson wrote:

> Thanks for the advice, but I'm not trying to shrink the log file. I'm
> trying to shrink the *DATA* file. However, whenever I try this, the log
> file starts growing like crazy.|||What I am saying is it will allow you to control your log file by giving you
time to issue log backups in between the shrinks. That way your log file
won't grow on you. It also allows you to manage the process a bit better.
By the way you don't want to remove any where near all of your free space.
The database needs lots of free space to operate properly. The less free
space you have the more you risk fragmentating your tables when you reindex.
When you reindex a table you should ensure you have 1.2 to 1.5 times the
size of the table and indexes free and hopefully contiguous.
Andrew J. Kelly
SQL Server MVP
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:%23nlIL7j9DHA.3880@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
>
backup
> Andrew,
> That's interesting. If I understand you correctly, when trying to
> recover some of this 61,000MB of slack space, I should do the following:
> DBCC SHRINKFILE (MyDb_Data, 140000)
> -> Backup log
> DBCC SHRINKFILE (MyDb_Data, 135000)
> -> Backup log
> DBCC ... etc.
> rather than:
> DBCC SHRINKFILE (MyDb_Data)
> or
> DBCC SHRINKFILE (MyDb_Data, 85000)
> If I do this, you're saying it will consume less log space?
> Cheers,
> Malc
>|||Andrew J. Kelly wrote:

>What I am saying is it will allow you to control your log file by giving yo
u
>time to issue log backups in between the shrinks. That way your log file
>won't grow on you. It also allows you to manage the process a bit better.
>By the way you don't want to remove any where near all of your free space.
>The database needs lots of free space to operate properly. The less free
>space you have the more you risk fragmentating your tables when you reindex
.
>When you reindex a table you should ensure you have 1.2 to 1.5 times the
>size of the table and indexes free and hopefully contiguous.
>
Thanks all for you help and advice. I was able to remove 20GB using the
suggestions, which leaves plenty of slack space in the datafile, and
leaves the free disk space at a sane level.
Cheers,
Malc

Does DBCC SHRINKFILE on a data file cause the log to grow?

We have 60GB free of a 140GB data file in one of our databases in SQL
Server 2000 SP2. I've truncated the free space at the end, but it won't
shrink any further now. I tried running DBCC SHRINKFILE, but had to
cancel it and run a log backup when the transaction log grew to 40GB and
started threatening the free disk space. Is this expected behaviour.
If it is, can anybody recommend a way to shrink the data file without
growing the log - e.g. do I have to temporarily change the database
model to simple, or something like that?
Cheers,
MalcMalcolm
Make a long story shortly
1)Perfom BACKUP LOG file ( it removes all inactive transaction )
2)Pefrom DBCC SHRINKFILE to reduce physical size of the log file.
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Sometimes, if this fails, you have to put some transactions into the
database to roll the virtual log to the beginning of the physical log. I've
had success with:
1) Backup the log
2) Run DBCC SHRINKFILE
3) If step 2 does not work, create a temp table in the database and add 1000
rows.
4) Delete the rows and the temp table. This will create t-log entries that
will force the virtual log to roll to the frony of the physical file (See
BOL for details on t-log architecture).
5) Backup the log.
6) Run DBCC SHRINKFILE again. It should work this time. If not, repeat
from 3.
The process is documented in a Q article somewhere for SQL 7. You are not
supposed to need it for SQL 2000 but I havbe found it comes in handy.
Christian Smith
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Yes this is expected behavior. When you shrink a data file it has to
physically move all data at the end of the file towards the beginning and
each move is fully logged. I would shrink in smaller increments and backup
the log after each one to keep it in check.
--
Andrew J. Kelly
SQL Server MVP
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Guys,
Thanks for the advice, but I'm not trying to shrink the log file. I'm
trying to shrink the *DATA* file. However, whenever I try this, the log
file starts growing like crazy.
Please advice further,
Malc
Christian Smith wrote:
>Sometimes, if this fails, you have to put some transactions into the
>database to roll the virtual log to the beginning of the physical log. I've
>had success with:
>1) Backup the log
>2) Run DBCC SHRINKFILE
>3) If step 2 does not work, create a temp table in the database and add 1000
>rows.
>4) Delete the rows and the temp table. This will create t-log entries that
>will force the virtual log to roll to the frony of the physical file (See
>BOL for details on t-log architecture).
>5) Backup the log.
>6) Run DBCC SHRINKFILE again. It should work this time. If not, repeat
>from 3.
>The process is documented in a Q article somewhere for SQL 7. You are not
>supposed to need it for SQL 2000 but I havbe found it comes in handy.
>Christian Smith
>"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
>message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
>
>>We have 60GB free of a 140GB data file in one of our databases in SQL
>>Server 2000 SP2. I've truncated the free space at the end, but it won't
>>shrink any further now. I tried running DBCC SHRINKFILE, but had to
>>cancel it and run a log backup when the transaction log grew to 40GB and
>>started threatening the free disk space. Is this expected behaviour.
>>If it is, can anybody recommend a way to shrink the data file without
>>growing the log - e.g. do I have to temporarily change the database
>>model to simple, or something like that?
>>Cheers,
>>Malc
>>
>
>|||Andrew J. Kelly wrote:
>Yes this is expected behavior. When you shrink a data file it has to
>physically move all data at the end of the file towards the beginning and
>each move is fully logged. I would shrink in smaller increments and backup
>the log after each one to keep it in check.
>
Andrew,
That's interesting. If I understand you correctly, when trying to
recover some of this 61,000MB of slack space, I should do the following:
DBCC SHRINKFILE (MyDb_Data, 140000)
-> Backup log
DBCC SHRINKFILE (MyDb_Data, 135000)
-> Backup log
DBCC ... etc.
rather than:
DBCC SHRINKFILE (MyDb_Data)
or
DBCC SHRINKFILE (MyDb_Data, 85000)
If I do this, you're saying it will consume less log space?
Cheers,
Malc|||yes. shrinking the data file(s) will fill up your log file. you could try
setting your recovery mode to simple before doing the shrinkfile. you'll still
need some available space in your log file even with simple recovery.
Malcolm Ferguson wrote:
> Thanks for the advice, but I'm not trying to shrink the log file. I'm
> trying to shrink the *DATA* file. However, whenever I try this, the log
> file starts growing like crazy.|||What I am saying is it will allow you to control your log file by giving you
time to issue log backups in between the shrinks. That way your log file
won't grow on you. It also allows you to manage the process a bit better.
By the way you don't want to remove any where near all of your free space.
The database needs lots of free space to operate properly. The less free
space you have the more you risk fragmentating your tables when you reindex.
When you reindex a table you should ensure you have 1.2 to 1.5 times the
size of the table and indexes free and hopefully contiguous.
--
Andrew J. Kelly
SQL Server MVP
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:%23nlIL7j9DHA.3880@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> >Yes this is expected behavior. When you shrink a data file it has to
> >physically move all data at the end of the file towards the beginning and
> >each move is fully logged. I would shrink in smaller increments and
backup
> >the log after each one to keep it in check.
> >
> >
> Andrew,
> That's interesting. If I understand you correctly, when trying to
> recover some of this 61,000MB of slack space, I should do the following:
> DBCC SHRINKFILE (MyDb_Data, 140000)
> -> Backup log
> DBCC SHRINKFILE (MyDb_Data, 135000)
> -> Backup log
> DBCC ... etc.
> rather than:
> DBCC SHRINKFILE (MyDb_Data)
> or
> DBCC SHRINKFILE (MyDb_Data, 85000)
> If I do this, you're saying it will consume less log space?
> Cheers,
> Malc
>|||Andrew J. Kelly wrote:
>What I am saying is it will allow you to control your log file by giving you
>time to issue log backups in between the shrinks. That way your log file
>won't grow on you. It also allows you to manage the process a bit better.
>By the way you don't want to remove any where near all of your free space.
>The database needs lots of free space to operate properly. The less free
>space you have the more you risk fragmentating your tables when you reindex.
>When you reindex a table you should ensure you have 1.2 to 1.5 times the
>size of the table and indexes free and hopefully contiguous.
>
Thanks all for you help and advice. I was able to remove 20GB using the
suggestions, which leaves plenty of slack space in the datafile, and
leaves the free disk space at a sane level.
Cheers,
Malc

Does DB Size decrease when I delete a huge table ?

Hi,
My DB size (Right click on DB Name, Data Files tab, Space Allocated field) was 10914 MB.
I delete a huge table (1.2 million records * 15 columns).
I checked the db size again. It didnt change.
Shouldn't it decrease because I delete a huge table ??The size will only decrease if you use the shrinkdb command.|||Originally posted by rokslide
The size will only decrease if you use the shrinkdb command.

When and why should we use Shrink DB ??|||Originally posted by rokslide
The size will only decrease if you use the shrinkdb command.

Would the deleted data be still on the database physical file ?|||The use of shrinkdb depends on who you talk to,... I really don't have a theory about it except to say that if the database is taking up too much space, shrink it...

As for the data still being in the database file... I would guess (and it's just a guess) that it would still be there but it would be unaccessible via the database. I think the delete would work much like deleting a file off your hard drive, all that is really deleted is the file header information, the data is still there, but you can't access it unless you use a tool to specificly locate the data.

Does that make sense? Can anyone else offer more advise??|||Originally posted by rokslide
The use of shrinkdb depends on who you talk to,... I really don't have a theory about it except to say that if the database is taking up too much space, shrink it...

As for the data still being in the database file... I would guess (and it's just a guess) that it would still be there but it would be unaccessible via the database. I think the delete would work much like deleting a file off your hard drive, all that is really deleted is the file header information, the data is still there, but you can't access it unless you use a tool to specificly locate the data.

Does that make sense? Can anyone else offer more advise??

Can u give a quick example of how I use ShrinkDB ?|||Originally posted by forXLDB
Can u give a quick example of how I use ShrinkDB ?
How do I know what size to reduce to ?
(If the size doesnt decrease even after deleting tables, how do i know what must be the exact size occupied by all the tables)...
I would have deleted and created lots of huge files involving around a million records atleast 10 times..

and above all, does the size effect the sql server performance in any way ?|||straight from the books online

This example decreases the size of the files in the UserDB user database to allow 10 percent free space in the files of UserDB.

DBCC SHRINKDATABASE (UserDB, 10)
GO

or you can do it through the EM|||Originally posted by rokslide
straight from the books online

or you can do it through the EM

i just did use for another test database, it shrunk more than half the size...

thx anyway !!1

Still looking for the answer on any performance degradation if the db file size is more ?|||Guessing once again I would say that it shouldn't affect the speed to any great extent,.. fragmentation of the database file would... there are some commands/functions that you can use to find out this kinda stuff but I have never used them so I can't really help alot I'm sorry.|||Originally posted by rokslide
Guessing once again I would say that it shouldn't affect the speed to any great extent,.. fragmentation of the database file would... there are some commands/functions that you can use to find out this kinda stuff but I have never used them so I can't really help alot I'm sorry.
thx again...

lemme know any functions !!!|||Since the log file takes the big size of the database, you can use the following script to reduce the file size:

USE DatabaseName
GO
DBCC SHRINKFILE ('DatabaseName_Log', Size)
GO

Here "Size" is the number of MB, i.e., if you put 100, that means the file will be shrinked to 100 MB.|||Since you are already working in em - when you right click on the database go to all task ->shrink database ->files... Under database file, look at the data file and log file. What is the current size/space used ? Are you backing up the database/transaction log ? What is the recovery model ?

What is the performance issue ?|||You can use "dbcc showcontig" to display fragmentation info. Yes performance will be affected - you need to either drop/recreate the indexes, use dbcc dbreindex or dbcc indexdefrag. Since you deleted so many records (more than half), fragmentation is a good start for performance issues.|||Do you have access to Books Online (BOL) ?|||An addition to rnealejr's post on dbcc indexdefrag and recreating indexes. If you have tables with a large amount of data input or modification take a good look at the indexes. If you use a non-seed value for your index then increasing the fillfactor number can help to reduce fragmentation in the future. I.E if your customer table uses first four characters of the name plus the zipcode for a customerID then you would be inserting new records into the middle of the table frequently. Once the fillfactor is used up then the next record inserted will create a new page and now your data is getting fragmented which does impact performance as mentioned be other posters.

Now that you've opened this pandoras box, you could spend a week in BOL learning how to tweak each tables index based on usage, calculating how many records fit on a page, use of varchar over char, benifits/penalties of seed indexes...............|||Originally posted by rnealejr
Do you have access to Books Online (BOL) ?
Yes. I can read BOL

Does a database need contiguous space?

Hello,
We had a problem that we're trying to figure out. We have a database that's
about 3.5GB in size on a partition with about 10GB of free space. The
database appeared normal, however when the client tried to write data to it
we'd receive a write error message.
We recreated the database on a different partition with 40GB free and it
works fine now. However we're still trying to figure out why the original
database failed and there comes the question-does SQL need contiguous free
space to work? The drive may be fairly fragmented and while there is ~10GB
free there may not be much contiguous space.
Everything else with SQL appears normal.
Thanks!Although a DB doesn't "need" contiguous space, it generally performs better
if it does have it.
What specifically is the error you get? Does the SQL Server service account
have write permission on that partition?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike Chiodo" <MikeChiodo@.discussions.microsoft.com> wrote in message
news:B860E70E-2CC7-4644-A624-C21EB0373BCF@.microsoft.com...
Hello,
We had a problem that we're trying to figure out. We have a database that's
about 3.5GB in size on a partition with about 10GB of free space. The
database appeared normal, however when the client tried to write data to it
we'd receive a write error message.
We recreated the database on a different partition with 40GB free and it
works fine now. However we're still trying to figure out why the original
database failed and there comes the question-does SQL need contiguous free
space to work? The drive may be fairly fragmented and while there is ~10GB
free there may not be much contiguous space.
Everything else with SQL appears normal.
Thanks!|||Yes, the SQL server account has permission to write to that partition. As
far as the error message-it was an error generated by the program not a SQL
error.
I suspect the database was simply corrupt but don't know for certain.
Thanks for your answer-it helped clear up a piece of the puzzle.
Mike
"Tom Moreau" wrote:
> Although a DB doesn't "need" contiguous space, it generally performs better
> if it does have it.
> What specifically is the error you get? Does the SQL Server service account
> have write permission on that partition?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Mike Chiodo" <MikeChiodo@.discussions.microsoft.com> wrote in message
> news:B860E70E-2CC7-4644-A624-C21EB0373BCF@.microsoft.com...
> Hello,
> We had a problem that we're trying to figure out. We have a database that's
> about 3.5GB in size on a partition with about 10GB of free space. The
> database appeared normal, however when the client tried to write data to it
> we'd receive a write error message.
> We recreated the database on a different partition with 40GB free and it
> works fine now. However we're still trying to figure out why the original
> database failed and there comes the question-does SQL need contiguous free
> space to work? The drive may be fairly fragmented and while there is ~10GB
> free there may not be much contiguous space.
> Everything else with SQL appears normal.
> Thanks!
>

Does a database need contiguous space?

Although a DB doesn't "need" contiguous space, it generally performs better
if it does have it.
What specifically is the error you get? Does the SQL Server service account
have write permission on that partition?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike Chiodo" <MikeChiodo@.discussions.microsoft.com> wrote in message
news:B860E70E-2CC7-4644-A624-C21EB0373BCF@.microsoft.com...
Hello,
We had a problem that we're trying to figure out. We have a database that's
about 3.5GB in size on a partition with about 10GB of free space. The
database appeared normal, however when the client tried to write data to it
we'd receive a write error message.
We recreated the database on a different partition with 40GB free and it
works fine now. However we're still trying to figure out why the original
database failed and there comes the question-does SQL need contiguous free
space to work? The drive may be fairly fragmented and while there is ~10GB
free there may not be much contiguous space.
Everything else with SQL appears normal.
Thanks!Hello,
We had a problem that we're trying to figure out. We have a database that's
about 3.5GB in size on a partition with about 10GB of free space. The
database appeared normal, however when the client tried to write data to it
we'd receive a write error message.
We recreated the database on a different partition with 40GB free and it
works fine now. However we're still trying to figure out why the original
database failed and there comes the question-does SQL need contiguous free
space to work? The drive may be fairly fragmented and while there is ~10GB
free there may not be much contiguous space.
Everything else with SQL appears normal.
Thanks!|||Although a DB doesn't "need" contiguous space, it generally performs better
if it does have it.
What specifically is the error you get? Does the SQL Server service account
have write permission on that partition?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike Chiodo" <MikeChiodo@.discussions.microsoft.com> wrote in message
news:B860E70E-2CC7-4644-A624-C21EB0373BCF@.microsoft.com...
Hello,
We had a problem that we're trying to figure out. We have a database that's
about 3.5GB in size on a partition with about 10GB of free space. The
database appeared normal, however when the client tried to write data to it
we'd receive a write error message.
We recreated the database on a different partition with 40GB free and it
works fine now. However we're still trying to figure out why the original
database failed and there comes the question-does SQL need contiguous free
space to work? The drive may be fairly fragmented and while there is ~10GB
free there may not be much contiguous space.
Everything else with SQL appears normal.
Thanks!|||Yes, the SQL server account has permission to write to that partition. As
far as the error message-it was an error generated by the program not a SQL
error.
I suspect the database was simply corrupt but don't know for certain.
Thanks for your answer-it helped clear up a piece of the puzzle.
Mike
"Tom Moreau" wrote:

> Although a DB doesn't "need" contiguous space, it generally performs bette
r
> if it does have it.
> What specifically is the error you get? Does the SQL Server service accou
nt
> have write permission on that partition?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Mike Chiodo" <MikeChiodo@.discussions.microsoft.com> wrote in message
> news:B860E70E-2CC7-4644-A624-C21EB0373BCF@.microsoft.com...
> Hello,
> We had a problem that we're trying to figure out. We have a database that
's
> about 3.5GB in size on a partition with about 10GB of free space. The
> database appeared normal, however when the client tried to write data to i
t
> we'd receive a write error message.
> We recreated the database on a different partition with 40GB free and it
> works fine now. However we're still trying to figure out why the original
> database failed and there comes the question-does SQL need contiguous free
> space to work? The drive may be fairly fragmented and while there is ~10G
B
> free there may not be much contiguous space.
> Everything else with SQL appears normal.
> Thanks!
>|||Yes, the SQL server account has permission to write to that partition. As
far as the error message-it was an error generated by the program not a SQL
error.
I suspect the database was simply corrupt but don't know for certain.
Thanks for your answer-it helped clear up a piece of the puzzle.
Mike
"Tom Moreau" wrote:

> Although a DB doesn't "need" contiguous space, it generally performs bette
r
> if it does have it.
> What specifically is the error you get? Does the SQL Server service accou
nt
> have write permission on that partition?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Mike Chiodo" <MikeChiodo@.discussions.microsoft.com> wrote in message
> news:B860E70E-2CC7-4644-A624-C21EB0373BCF@.microsoft.com...
> Hello,
> We had a problem that we're trying to figure out. We have a database that
's
> about 3.5GB in size on a partition with about 10GB of free space. The
> database appeared normal, however when the client tried to write data to i
t
> we'd receive a write error message.
> We recreated the database on a different partition with 40GB free and it
> works fine now. However we're still trying to figure out why the original
> database failed and there comes the question-does SQL need contiguous free
> space to work? The drive may be fairly fragmented and while there is ~10G
B
> free there may not be much contiguous space.
> Everything else with SQL appears normal.
> Thanks!
>

Documented t-sql for file space ?

Hi There

I would like to write sql to check that space used by any given data file in SS2000.

sp_spaceused only returns the total space used by the DB or object in the DB, not for a specific data file.

DBCC SHOWFILESTATS does give me this information , but it is not documented in BOL, i would prefer not to use an undocumented command to ensure future use in later versions. Is DBCC SHOWFILESTATS an undocumented T-SQL command ? If so what documented t-sql command will provide me with this information ?

I could query system table's and work this out , but as mentioned this may not not work in future versions of SS.

Thanx

Dont worry, seems FILEPROPERTY function will do this for me , thanx