Deleting Older Files

I have a backup job that has failed.

The database size is 20.6 GB and the Transaction logs are 135MB

The amount of disk space I have left is 3.65MB. Which I know is not going to work.

However on the maintenance plan it is suppose to remove files older than 1 day.

I am wondering if a job works like this:

Step 1 create backup file
Step 2 Create Transaction Log Back up
Step 3 Delete old backup file
Step 4 Delete old Transaction log back up

Which tell me I would need to have double amount of disk space to accommodate 2 20 GB backup file and 2 135MB Transaction log file.

Is this correct??

Also is there a way that I can have step 3,4 done first.

LystraAs far as the full backup, you can overwrite the old backup file every time you take a full backup. This way you don't need to reserve the space for two full backup file. In the case of log backup, if you don't need to or don't want to backup the log file, just use simple recovery mode. It looks like you are deleting them any way.|||That's the problem it is not deleting the older files, even though I have it check to delete the files.

The database recovery mode is set to full.

Lystra|||Drives are cheap....

And what happens when you need to migrate data around?

Do you have a disaster box?

How odten do you dump the transaction log?

How many dumps do you have now?

How big is the hard drive?|||I think I have solve the problem. The actual mdf is 47.2GB big and there is not enough disk space to accommodate this backup.

Thanks

Lystra|||Not to sound like a broken record.

I think I have solve the problem. The actual mdf is 47.2GB big and there is not enough disk space to accommodate this backup.

Thanks

Lystra

Deleting log file in sql 2000 database

Hi ,
I have a huge sql server 2000 database. Everyday I am importing some
records into it. The .mdf file size is around 20 GB. Surprisingly the
.ldf file size is increasing rapidly and it has reached 50 GB.
I want to import lot more records and there is a possibility that I
may run out of disk space.
Please let me know if I can delete this log file. I can always detach
the database -delete the log file and reattach the database again. But
the question is- Is it safe to do ? Also what is the possibility of
database not getting attached properly and data loss ? any guesses.
Thanks in advance,
Vardhan.Vardahan
Perfom
BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
news:868ed17f.0401210146.7771e293@.posting.google.com...
> Hi ,
> I have a huge sql server 2000 database. Everyday I am importing some
> records into it. The .mdf file size is around 20 GB. Surprisingly the
> .ldf file size is increasing rapidly and it has reached 50 GB.
> I want to import lot more records and there is a possibility that I
> may run out of disk space.
> Please let me know if I can delete this log file. I can always detach
> the database -delete the log file and reattach the database again. But
> the question is- Is it safe to do ? Also what is the possibility of
> database not getting attached properly and data loss ? any guesses.
> Thanks in advance,
> Vardhan.|||Deleting your log file will kill your database, so its
probably not a good thing to do ;)
Instead either shrink it, or change the SQL so it
immeditatly commits to disk,
J
>--Original Message--
>Hi ,
>I have a huge sql server 2000 database. Everyday I am
importing some
>records into it. The .mdf file size is around 20 GB.
Surprisingly the
>..ldf file size is increasing rapidly and it has reached
50 GB.
>I want to import lot more records and there is a
possibility that I
>may run out of disk space.
>Please let me know if I can delete this log file. I can
always detach
>the database -delete the log file and reattach the
database again. But
>the question is- Is it safe to do ? Also what is the
possibility of
>database not getting attached properly and data loss ?
any guesses.
>Thanks in advance,
>Vardhan.
>.
>|||Does backing up the DB, & then shrinking it not reduce the size?
If it doesnt, this implies you have long-running transactions which might be
stopping the log from being truncated.
Also, have you considered switching to Bulk-Logged recovery model? If most
of this inflation occurs because of the records you are importing, this
might help reduce the log size...
This link might help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||doesn't work, I also tried backup log files then dbcc shrinkfile (logfile)
this also didn't work. manually deleting the file will be mow much risky ?
thanks in advance
Vardhan.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<e2OvHaA4DHA.3416@.tk2msftngp13.phx.gbl>...
> Vardahan
> Perfom
> BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it and
> truncates the log. This option frees space. Specifying a backup device is
> unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
> are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes, immediately
> execute BACKUP DATABASE.
> "Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
> news:868ed17f.0401210146.7771e293@.posting.google.com...
> > Hi ,
> > I have a huge sql server 2000 database. Everyday I am importing some
> > records into it. The .mdf file size is around 20 GB. Surprisingly the
> > .ldf file size is increasing rapidly and it has reached 50 GB.
> >
> > I want to import lot more records and there is a possibility that I
> > may run out of disk space.
> >
> > Please let me know if I can delete this log file. I can always detach
> > the database -delete the log file and reattach the database again. But
> > the question is- Is it safe to do ? Also what is the possibility of
> > database not getting attached properly and data loss ? any guesses.
> >
> > Thanks in advance,
> > Vardhan.

Deleting log file in sql 2000 database

Hi ,
I have a huge sql server 2000 database. Everyday I am importing some
records into it. The .mdf file size is around 20 GB. Surprisingly the
.ldf file size is increasing rapidly and it has reached 50 GB.
I want to import lot more records and there is a possibility that I
may run out of disk space.
Please let me know if I can delete this log file. I can always detach
the database -delete the log file and reattach the database again. But
the question is- Is it safe to do ? Also what is the possibility of
database not getting attached properly and data loss ? any guesses.
Thanks in advance,
Vardhan.Vardahan
Perfom
BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
news:868ed17f.0401210146.7771e293@.posting.google.com...

quote:

---

> Hi ,
> I have a huge sql server 2000 database. Everyday I am importing some
> records into it. The .mdf file size is around 20 GB. Surprisingly the
> .ldf file size is increasing rapidly and it has reached 50 GB.
> I want to import lot more records and there is a possibility that I
> may run out of disk space.
> Please let me know if I can delete this log file. I can always detach
> the database -delete the log file and reattach the database again. But
> the question is- Is it safe to do ? Also what is the possibility of
> database not getting attached properly and data loss ? any guesses.
> Thanks in advance,
> Vardhan.

---

|||Does backing up the DB, & then shrinking it not reduce the size?
If it doesnt, this implies you have long-running transactions which might be
stopping the log from being truncated.
Also, have you considered switching to Bulk-Logged recovery model? If most
of this inflation occurs because of the records you are importing, this
might help reduce the log size...
This link might help:
http://msdn.microsoft.com/library/d... />
t_4l83.asp
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||doesn't work, I also tried backup log files then dbcc shrinkfile (logfile)
this also didn't work. manually deleting the file will be mow much risky ?
thanks in advance
Vardhan.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<e2OvHaA4DHA.3416@.tk2msftngp13.phx.gbl>...[Q
UOTE]
> Vardahan
> Perfom
> BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it an
d
> truncates the log. This option frees space. Specifying a backup device is
> unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
> are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes, immediatel
y
> execute BACKUP DATABASE.
> "Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
> news:868ed17f.0401210146.7771e293@.posting.google.com...

Deleting data bloats log file

When I delete substantial amounts of data using the SQL DELETE command, the database size apparently remains the same (702 mb) and the log file goes from about 17 mb to over 1 gig. I was expecting for the overall size to decrease drastically, but got just the opposite.

Is this typical? Can I do something to slim it down? As I am just trying to decrease the overall size to make it easier to work with when creating my application in VB, I am not worried about restoring the db (I have secure copies).

The deletes are recorded in the Transaction Log, so that in case of needing to rebuild a 'crashed' database, you have a complete record or every action.

However, if you are just trying to clean out tables, you 'might' be able to use the TRUNCATE command.

TRUNCATE TABLE MyTable

If you are just removing part of the data, then you could change the database recovery type to 'Simple', and the log will be truncated as soon as the deletes are complete.

|||Hi Arnie,

I just want to create a lightweight version of the DB to make the VB application load and run faster during the early stages of development (and take up less drive space). I want the structure to remain the same so that when the application code is in good shape I will use the full data version. So I want to remove most of the data and keep the file size (especially log) as low as possible.

It sounds like I need to change the recovery type to 'Simple' but don't know how. Have checked out the menus and search through Books Online without luck.
|||

One consideration many of us make is to create a script of the database, perhaps including scripted data inserts for Lookup tables, etc.

Then when there is a schema change, you add the schema change to the script, and then you can regularly drop the development database and run the script to re-create a 'clean' copy. You can script out the objects by right-clicking on the database, select [Tasks...], and then [Generate Scripts...]. Follow the prompts. There are also third party tools for this purpose. Below are some resources that will help with scripting out data.

To change the Recovery Model, using Object Explorer, right-click on the database, select [Properties], then [Options]. You can change the Recovery model on that screen. Be sure to make a note to return the Recovery Model to [FULL] when you move the database to production. You may need to 'shrink' the log file since it has grown so large.

DDL -Script Database to File
http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx
http://www.sqlteam.com/publish/scriptio
http://www.aspfaq.com/etiquette.asp?id=5006
http://www.codeproject.com/dotnet/ScriptDatabase.asp
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
http://rac4sql.net/qalite_main.asp

DDL –Script Database to File using SMO (VB.NET)
http://msdn2.microsoft.com/en-us/library/ms162138.aspx

DDL –Script Data to file (Database Publishing Wizard)
http://www.microsoft.com/downloads/details.aspx?FamilyID=56e5b1c5-bf17-42e0-a410-371a838e570a&DisplayLang=en

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

|||Thanks for the info.

I set the Recovery Model to simple.

I deleted about 95% of the data.

I ran DBCC SHRINKFILE and shrunk the log file to 2 mb (FANTASTIC! - NO PROBLEMS)

But the file size for the DB still show 702 mb in Windows Explorer. So I looked at the Disk Usage where it shows 702 mb for SPACE RESERVED but only 4.3 mb for SPACE USED.

I wonder if this is because the field with the most data is ntext datatype (which I believe reserves a certain amount of disk space regardless of what is used. The db was created several years ago in Access and migrated to SQL Server 2000. I have considered changing the datatype to nvarchar (MAX) which I believe only reserves what is actually needed. Also, when I create a full-text index, I get an alert that the ntext datatype may prevent some features of WITH CHANGE_TRACKING AUTO;

Do you think the ntext datatype is keeping the file size so large? If so, is it simply a matter of changing datatype on that column? Or could there be other factors keeping the db file size so large?|||

First up the warning about ntext and fulltext search. Full Text change tracking does not support updates made via writetext or updatetext which is probably why you are getting that error. As you are using SQL2005 (?) i'd suggest changing the column type to nvarchar(max). Text, Ntext and image have been superceded but are supported for backward compatability.

As for the size of the database. When you delete data, the size of the datafile will not shrink but the pages within that will be marked as free. Generally speaking its a good idea to have your datafile at a size bigger than the current database size as this allows for growth of data without the (expensive) operation of growing the datafile.

In saying this, you are able to shrink the data file in a similar way to the transaction log.

HTH!

|||Thanks Rich,

I shrunk the db as well and it really does make a big difference while I developing in VB. I'm learning LINQ so there is alot of trial and error, so this really saves me some time.

Now to the question of what to do with the database when I am ready for full size (when I get my VB code worked out) since there is clearly some redundant data that will need to be deleted. What do you mean by "its a good idea to have your datafile at a size bigger than the current databse size as this allows for growth of data without the expensive operation of growing the datafile"?

I didn't realize that you could directly determine the size of the data other than choosing column datatypes that limit or extend size (e.g. nvarchar(10)). This was one of the concerns about the ntext datatype (besides being outdated). I had read that it used up a certain amount of space regardless of the data whereas nvarchar(max) is flexible in allocating space depending upon the amount of data, hence a smaller file size customized to the requirements of the data.

For this particular database, the size should not increase significantly once it is finished since users will not be able to add or delete data. From time to time corrections (typos) may need to be made but should not add substantiallly to the size.

|||

Sorry if my wording was a bit confusing.

When you create a database you can specify the size of the data and log files which essentially allocates space for your data to be stored.

If you set your files too small and you have a lot of database write activity, the files will physically need to grow to store the data. The physical growth of a file is expensive in terms of resources and you want to minimise this, so set the files at a size that reflects how big you predict your data to grow.

The size of the your columns etc is how you control how much data is stored within the datafile and this is more likely to be a factor in performance rather than the physical file size.

HTH!

|||Thanks for the helpful explanation. I will keep that in mind as I alter this db and create new ones.

As a followup to the datatype question, I have been trying to change the datatype ntext on the SectionText column to nvarchar(max), but am having a problem. My sql is:

ALTER TABLE FullDocuments MODIFY SectionText nvarchar(Max) not null;

I am getting this error message:

Incorrect syntax near 'MODIFY'.

Any thought on what I am doing wrong and how to fix it?|||

Thats not the correct syntax for that command:

Try:

Code Snippet

ALTER TABLE FullDocuments
ALTER COLUMN SectionText NVARCHAR(Max) NOT NULL;

Good luck!|||

Let me expand upon Rich's explanation.

When a database runs out of space, and if 'AutoGrow' is set to TRUE, it will automatically acquire additional disk space from the OS. Consider the analogy of a notebook. When SQL Server acquires disk space from the OS, it is like the notebook gets paper, and then that paper has to be divided into pages, lines drawn on the pages, and they have to be numbered before they are ready to use. Once done, it is quick and efficient for SQL Server to use the pages. But when the pages fill up, and SQL Server has to acquire additional space from the OS, it is a 'costly' operation to have to wait until all of the new pages are ready to use before the operation that needed to store data can continue. So it is best to 'size' the database large enough to contain the expected data growth over a good period of time. Then it is a 'Best Practice' to have a scheduled task that will periodically assess the need for additional space, and acquire that space during 'non-peak' times. (AutoGrow could fire off at the worst moment for server load.)

Perhaps these resources will help:

FileSize -Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512

FileSize -DB Shrink Issues
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

|||Thanks again.

deleted mdf file want to restore from logfile?

I have deleted my mdf file by accident en need to restore the db from a logfile
the size of the logfile is 54gb.
I am totally desparate this was critical data, I tried to get the data from
the subscription server but this file is totally messed up , so I bought a
app to recover the data but it only managed bits and pieces.
My only hope now is to restore the data from the logfile but I have no idea
how or where to start. Would appriciate any helpDo you have full backup and transaction files up to the time that the
mdf was deleted or just the ldf? If not, I'm afraid you're going to
be out of luck.
On Sep 25, 9:00 am, jules <ju...@.discussions.microsoft.com> wrote:
> I have deleted my mdf file by accident en need to restore the db from a logfile
> the size of the logfile is 54gb.
> I am totally desparate this was critical data, I tried to get the data from
> the subscription server but this file is totally messed up , so I bought a
> app to recover the data but it only managed bits and pieces.
> My only hope now is to restore the data from the logfile but I have no idea
> how or where to start. Would appriciate any help|||In article <0A70FE38-9F46-4EBC-BFE3-9ABA3D9D1985@.microsoft.com>,
jules@.discussions.microsoft.com says...
> I have deleted my mdf file by accident en need to restore the db from a logfile
> the size of the logfile is 54gb.
> I am totally desparate this was critical data, I tried to get the data from
> the subscription server but this file is totally messed up , so I bought a
> app to recover the data but it only managed bits and pieces.
> My only hope now is to restore the data from the logfile but I have no idea
> how or where to start. Would appriciate any help
>
Might investigate Lumigent Logreader -- can restore data from a log
file. There may be other products as well
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||Get in touch with Brian Lockwood at ApexSQL. They have done this type of
disaster recovery before, although I am not sure recovery is possible
without the database file as well as the log file. apexsql.com
"jules" <jules@.discussions.microsoft.com> wrote in message
news:0A70FE38-9F46-4EBC-BFE3-9ABA3D9D1985@.microsoft.com...
>I have deleted my mdf file by accident en need to restore the db from a
>logfile
> the size of the logfile is 54gb.
> I am totally desparate this was critical data, I tried to get the data
> from
> the subscription server but this file is totally messed up , so I bought a
> app to recover the data but it only managed bits and pieces.
> My only hope now is to restore the data from the logfile but I have no
> idea
> how or where to start. Would appriciate any help

Deleted half the rows from a table but it's still the same size

We have recently started archiving data in our database. With some of
the tables that I've archived I see a reduction in the physical size of
the table when I delete rows. In other words if I delete 50% of the
rows the amount of disk space the table uses also drop by close to 50%
But there are a few particularly lage table that don't decrease in
size. For example one of our largest tables had 115 million rows
consuming about 35 GB of disk space. After archiving some of the data
we're down to 70 million rows (about a 40% reduction if my poor math
skill are correct) but the table still takes up 35 GB.
I was thinking that this could be due to fragmentation. The table in
question has high extent fragmentation (80-90%) for all the indexes.
Would defragmenting help in this case?
ThanksFirst you need to determine whather the interesting part is the reserved ext
ends (which can have
unused pages on them) or if it is pages with little used space. SHOWCONTIG w
ill tell you. In any
case, yes, rebuilding will probably fix, and possibly also reorganizing (dep
ending on what type of
fragmentation you have).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1147372556.194478.32110@.j73g2000cwa.googlegroups.
com...
> We have recently started archiving data in our database. With some of
> the tables that I've archived I see a reduction in the physical size of
> the table when I delete rows. In other words if I delete 50% of the
> rows the amount of disk space the table uses also drop by close to 50%
> But there are a few particularly lage table that don't decrease in
> size. For example one of our largest tables had 115 million rows
> consuming about 35 GB of disk space. After archiving some of the data
> we're down to 70 million rows (about a 40% reduction if my poor math
> skill are correct) but the table still takes up 35 GB.
> I was thinking that this could be due to fragmentation. The table in
> question has high extent fragmentation (80-90%) for all the indexes.
> Would defragmenting help in this case?
> Thanks
>

Deleted half the rows from a table but it's still the same size

We have recently started archiving data in our database. With some of
the tables that I've archived I see a reduction in the physical size of
the table when I delete rows. In other words if I delete 50% of the
rows the amount of disk space the table uses also drop by close to 50%
But there are a few particularly lage table that don't decrease in
size. For example one of our largest tables had 115 million rows
consuming about 35 GB of disk space. After archiving some of the data
we're down to 70 million rows (about a 40% reduction if my poor math
skill are correct) but the table still takes up 35 GB.
I was thinking that this could be due to fragmentation. The table in
question has high extent fragmentation (80-90%) for all the indexes.
Would defragmenting help in this case?
ThanksFirst you need to determine whather the interesting part is the reserved extends (which can have
unused pages on them) or if it is pages with little used space. SHOWCONTIG will tell you. In any
case, yes, rebuilding will probably fix, and possibly also reorganizing (depending on what type of
fragmentation you have).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1147372556.194478.32110@.j73g2000cwa.googlegroups.com...
> We have recently started archiving data in our database. With some of
> the tables that I've archived I see a reduction in the physical size of
> the table when I delete rows. In other words if I delete 50% of the
> rows the amount of disk space the table uses also drop by close to 50%
> But there are a few particularly lage table that don't decrease in
> size. For example one of our largest tables had 115 million rows
> consuming about 35 GB of disk space. After archiving some of the data
> we're down to 70 million rows (about a 40% reduction if my poor math
> skill are correct) but the table still takes up 35 GB.
> I was thinking that this could be due to fragmentation. The table in
> question has high extent fragmentation (80-90%) for all the indexes.
> Would defragmenting help in this case?
> Thanks
>

Deleted 99 of 154 columns, yet size of table is still the same?

I had a table with 154 columns. I dropped 99 of them. Each had a lot of
data in them.
But afterwards, when I sp_spaceused TABLENAME, the results in terms of
size were still the same.
Is there something I'm missing?
[ Sugapablo ]
[ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--music ]
[ http://www.2ra.org <--political | http://www.subuse.net <--discuss ]
See DBCC CLEANTABLE in the BOL
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.24.10.51.15.294163@.REMOVEsugapabl o.com...
> I had a table with 154 columns. I dropped 99 of them. Each had a lot of
> data in them.
> But afterwards, when I sp_spaceused TABLENAME, the results in terms of
> size were still the same.
> Is there something I'm missing?
> --
> [
]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
<--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
<--discuss ]
>
|||Try running DBCC UPDATEUSAGE and see if that changes anything.
Andrew J. Kelly SQL MVP
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.24.10.51.15.294163@.REMOVEsugapabl o.com...
>I had a table with 154 columns. I dropped 99 of them. Each had a lot of
> data in them.
> But afterwards, when I sp_spaceused TABLENAME, the results in terms of
> size were still the same.
> Is there something I'm missing?
> --
> [
> ]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
> <--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
> <--discuss ]
>
|||Hi
Just check for DBCC CLEANTABLE in BOL
http://msdn.microsoft.com/library/en...asp?frame=true
this might help you
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Sugapablo" wrote:

> I had a table with 154 columns. I dropped 99 of them. Each had a lot of
> data in them.
> But afterwards, when I sp_spaceused TABLENAME, the results in terms of
> size were still the same.
> Is there something I'm missing?
> --
> [ Sugapablo ]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net <--discuss ]
>

Deleted 99 of 154 columns, yet size of table is still the same?

I had a table with 154 columns. I dropped 99 of them. Each had a lot of
data in them.
But afterwards, when I sp_spaceused TABLENAME, the results in terms of
size were still the same.
Is there something I'm missing?
[ Sugapablo
]
[ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--mu
sic ]
[ http://www.2ra.org <--political | http://www.subuse.net <--di
scuss ]See DBCC CLEANTABLE in the BOL
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.24.10.51.15.294163@.REMOVEsugapablo.com...
> I had a table with 154 columns. I dropped 99 of them. Each had a lot of
> data in them.
> But afterwards, when I sp_spaceused TABLENAME, the results in terms of
> size were still the same.
> Is there something I'm missing?
> --
> [
]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
<--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
<--discuss ]
>|||Try running DBCC UPDATEUSAGE and see if that changes anything.
Andrew J. Kelly SQL MVP
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.24.10.51.15.294163@.REMOVEsugapablo.com...
>I had a table with 154 columns. I dropped 99 of them. Each had a lot of
> data in them.
> But afterwards, when I sp_spaceused TABLENAME, the results in terms of
> size were still the same.
> Is there something I'm missing?
> --
> [
> ]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
> <--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
> <--discuss ]
>|||Hi
Just check for DBCC CLEANTABLE in BOL
http://msdn.microsoft.com/library/e...asp?frame=true
this might help you
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Sugapablo" wrote:

> I had a table with 154 columns. I dropped 99 of them. Each had a lot of
> data in them.
> But afterwards, when I sp_spaceused TABLENAME, the results in terms of
> size were still the same.
> Is there something I'm missing?
> --
> [ Sugapablo
]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--
music ]
> [ http://www.2ra.org <--political | http://www.subuse.net <--
discuss ]
>

delete one secondary data file and increase size of primary data f

SQL server 2000 sp3
Hi
I have a sql server 2000 database. In one application initial database size
was 13 gb. We created primary data file for required size. Application later
required 430 gb more space. We added another datafile (secondary) with 430
gb. The problem is the application does not allow us to specify where to put
the file. Looks like it just load the data in the primary datafile and then
does not try to load files in secondary datafile and secondary datafile is
not being used.
Application developer wants me to just have one datafile (Primary data file
with 450 gb). What is the best method to delete the secondary datafile and
add that much space to primary datafile.
ontario, canada
I'm just curious as to what happens when you empty the database files
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:F2A7EDC3-DBD2-46BB-854E-B1E27BAB2651@.microsoft.com...
>
> 1. Make sure the primary file can accomodate the data. Expand if
> neccesary. Something like
> ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
> 2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
> 3. Remove that now empty file from the database:
> ALTER DATABASE dbname REMOVE FILE logical_file_name
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
>
|||Thanks Tibor.
Bass: In my case pages were not moved as the secondary file was empty, and
file was deleted.
ontario, canada
"Tibor Karaszi" wrote:

> DBCC SHRINKFILE using the EMPTYFILE option will "push" pages over to other file, causing them to
> autogrow if they can't accommodate this. These operation is logged so one need to be very cautious
> with the transaction log if lots of pages will be "moved" to other files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bass_player" <bass_player@.mvps.org> wrote in message
> news:%235OUzhZiIHA.5820@.TK2MSFTNGP04.phx.gbl...
>
>

delete one secondary data file and increase size of primary data f

SQL server 2000 sp3
Hi
I have a sql server 2000 database. In one application initial database size
was 13 gb. We created primary data file for required size. Application later
required 430 gb more space. We added another datafile (secondary) with 430
gb. The problem is the application does not allow us to specify where to put
the file. Looks like it just load the data in the primary datafile and then
does not try to load files in secondary datafile and secondary datafile is
not being used.
Application developer wants me to just have one datafile (Primary data file
with 450 gb). What is the best method to delete the secondary datafile and
add that much space to primary datafile.
--
ontario, canada> What is the best method to delete the secondary datafile and
> add that much space to primary datafile.
1. Make sure the primary file can accomodate the data. Expand if neccesary. Something like
ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
3. Remove that now empty file from the database:
ALTER DATABASE dbname REMOVE FILE logical_file_name
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"db" <db@.discussions.microsoft.com> wrote in message
news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
> SQL server 2000 sp3
> Hi
> I have a sql server 2000 database. In one application initial database size
> was 13 gb. We created primary data file for required size. Application later
> required 430 gb more space. We added another datafile (secondary) with 430
> gb. The problem is the application does not allow us to specify where to put
> the file. Looks like it just load the data in the primary datafile and then
> does not try to load files in secondary datafile and secondary datafile is
> not being used.
> Application developer wants me to just have one datafile (Primary data file
> with 450 gb). What is the best method to delete the secondary datafile and
> add that much space to primary datafile.
>
> --
> ontario, canada|||I'm just curious as to what happens when you empty the database files
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:F2A7EDC3-DBD2-46BB-854E-B1E27BAB2651@.microsoft.com...
>> What is the best method to delete the secondary datafile and
>> add that much space to primary datafile.
>
> 1. Make sure the primary file can accomodate the data. Expand if
> neccesary. Something like
> ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
> 2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
> 3. Remove that now empty file from the database:
> ALTER DATABASE dbname REMOVE FILE logical_file_name
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
>> SQL server 2000 sp3
>> Hi
>> I have a sql server 2000 database. In one application initial database
>> size
>> was 13 gb. We created primary data file for required size. Application
>> later
>> required 430 gb more space. We added another datafile (secondary) with
>> 430
>> gb. The problem is the application does not allow us to specify where to
>> put
>> the file. Looks like it just load the data in the primary datafile and
>> then
>> does not try to load files in secondary datafile and secondary datafile
>> is
>> not being used.
>> Application developer wants me to just have one datafile (Primary data
>> file
>> with 450 gb). What is the best method to delete the secondary datafile
>> and
>> add that much space to primary datafile.
>>
>> --
>> ontario, canada
>|||DBCC SHRINKFILE using the EMPTYFILE option will "push" pages over to other file, causing them to
autogrow if they can't accommodate this. These operation is logged so one need to be very cautious
with the transaction log if lots of pages will be "moved" to other files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player" <bass_player@.mvps.org> wrote in message
news:%235OUzhZiIHA.5820@.TK2MSFTNGP04.phx.gbl...
> I'm just curious as to what happens when you empty the database files
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:F2A7EDC3-DBD2-46BB-854E-B1E27BAB2651@.microsoft.com...
>> What is the best method to delete the secondary datafile and
>> add that much space to primary datafile.
>>
>> 1. Make sure the primary file can accomodate the data. Expand if neccesary. Something like
>> ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
>> 2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
>> 3. Remove that now empty file from the database:
>> ALTER DATABASE dbname REMOVE FILE logical_file_name
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
>> SQL server 2000 sp3
>> Hi
>> I have a sql server 2000 database. In one application initial database size
>> was 13 gb. We created primary data file for required size. Application later
>> required 430 gb more space. We added another datafile (secondary) with 430
>> gb. The problem is the application does not allow us to specify where to put
>> the file. Looks like it just load the data in the primary datafile and then
>> does not try to load files in secondary datafile and secondary datafile is
>> not being used.
>> Application developer wants me to just have one datafile (Primary data file
>> with 450 gb). What is the best method to delete the secondary datafile and
>> add that much space to primary datafile.
>>
>> --
>> ontario, canada|||Thanks Tibor.
Bass: In my case pages were not moved as the secondary file was empty, and
file was deleted.
--
ontario, canada
"Tibor Karaszi" wrote:
> DBCC SHRINKFILE using the EMPTYFILE option will "push" pages over to other file, causing them to
> autogrow if they can't accommodate this. These operation is logged so one need to be very cautious
> with the transaction log if lots of pages will be "moved" to other files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bass_player" <bass_player@.mvps.org> wrote in message
> news:%235OUzhZiIHA.5820@.TK2MSFTNGP04.phx.gbl...
> > I'm just curious as to what happens when you empty the database files
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> > news:F2A7EDC3-DBD2-46BB-854E-B1E27BAB2651@.microsoft.com...
> >> What is the best method to delete the secondary datafile and
> >> add that much space to primary datafile.
> >>
> >>
> >> 1. Make sure the primary file can accomodate the data. Expand if neccesary. Something like
> >> ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
> >>
> >> 2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
> >>
> >> 3. Remove that now empty file from the database:
> >> ALTER DATABASE dbname REMOVE FILE logical_file_name
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "db" <db@.discussions.microsoft.com> wrote in message
> >> news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
> >> SQL server 2000 sp3
> >> Hi
> >> I have a sql server 2000 database. In one application initial database size
> >> was 13 gb. We created primary data file for required size. Application later
> >> required 430 gb more space. We added another datafile (secondary) with 430
> >> gb. The problem is the application does not allow us to specify where to put
> >> the file. Looks like it just load the data in the primary datafile and then
> >> does not try to load files in secondary datafile and secondary datafile is
> >> not being used.
> >>
> >> Application developer wants me to just have one datafile (Primary data file
> >> with 450 gb). What is the best method to delete the secondary datafile and
> >> add that much space to primary datafile.
> >>
> >>
> >>
> >> --
> >> ontario, canada
> >>
>
>