Database has grown too big (5 GB)

Hello
My database should have a normal size on about 500-1000 mb, but it now has
the size of 5 gb. I am talking about the MDF file, not the log file.
I think it is because of a wrong setup of the maintenance plan. Now I have
altered the optimization and checked "remove unused space from database
files", so it is set to shrink database when it grows beyond 50 Mb.
So do you think that next time the maintenance plan runs the Optimization,
the database size will be set to normal ?
I use MSSQL 2000, service pack 3, MDAC 2.8
\AndersIt depends on how much space is being used. Run a sp_spaceused inside the
database name. The reserved number (which equals data + log+ unused) is
roughly how much space to expect from shrinking, which can also be
accomplished by running dbcc shrinkfile (<data name>, 0) -- see SQL Server
Books online for details (which can be downloaded for free from Microsoft's
SQL site -- http://www.microsoft.com/sql)
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>|||If after shrinking the DB, you see the same problem, then try the following:
Do you have clustered index on your tables? If not, add and see if you see
the change.
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>

Database has grown too big (5 GB)

Hello
My database should have a normal size on about 500-1000 mb, but it now has
the size of 5 gb. I am talking about the MDF file, not the log file.
I think it is because of a wrong setup of the maintenance plan. Now I have
altered the optimization and checked "remove unused space from database
files", so it is set to shrink database when it grows beyond 50 Mb.
So do you think that next time the maintenance plan runs the Optimization,
the database size will be set to normal ?
I use MSSQL 2000, service pack 3, MDAC 2.8
\AndersIt depends on how much space is being used. Run a sp_spaceused inside the
database name. The reserved number (which equals data + log+ unused) is
roughly how much space to expect from shrinking, which can also be
accomplished by running dbcc shrinkfile (<data name>, 0) -- see SQL Server
Books online for details (which can be downloaded for free from Microsoft's
SQL site -- http://www.microsoft.com/sql)
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>|||If after shrinking the DB, you see the same problem, then try the following:
Do you have clustered index on your tables? If not, add and see if you see
the change.
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>

Database has gotten relatively large

I upgraded a database from SQL2000 to SQL2005 and it went pretty smooth. After the transition was made, I backed up the DB. The size of the database was as expected, about 5 GB. About 5 hours later, a maintenance plan executed a few optimization jobs in the following order: Reorganize Index, Rebuild Index, Shrink Database, Update Statistics. Soon after that, another job backed up the database and it was then 32 GB. During the time after the first backup, no one was really using the database.

I've been trying to track this down for several days. Does anyone have any ideas for me?

Thanks,
Bobby Crawford
SQL Workbench has some nifty reports you can run, one of which is Disk Usage, it details how much space each table is using. THis should help you track it down.

Database Growth Options

Please, is there an advantage for using a static megabit number over using a
percentage and what will be a recommeded growth size for a 3gb database.
Thanks in advance for your assistance.
KenO
In a production environment, relying upon the Autogrow features is outside
of 'Best Practices'. Not bad to have as an 'emergency fallback' option, but
the database filegrowth 'should' be actively managed and extended during
'slack' operation times.
If you 'MUST' reply upon the Autogrow features, then the use of the
database, the new/changed data flow, and the number of users are important
considerations. A database used only for reporting may not need any
Autogrowth, whereas a very active order entry database with hundreds of
users may need to 'grow' in large chunks.
The primary thing to remember is that Autogrow has a performance impact, and
may result in fragmented datafiles, so you don't want it happening often.
Grow by large enough chunks to handle a substantial amount of need.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO
|||KenO wrote:
> Please, is there an advantage for using a static megabit number over using a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO
Imagine your database is 214GB, and you've set it to auto-grow at a 10%
interval. The next time it grows, you're going to have to wait for it
to write 21.4GB of empty space. Might take a while.
Ideally, your database should never grow automatically. You, as the
DBA, should plan accordingly, and adjust the database size as needed
during planned outages or quiet periods when the overhead of growing the
database won't affect performance.
How much to grow by? Again, YOU need to determine that. If you add
600MB of data each month, and you have a monthly outage window, then
you'll want to add AT LEAST 600MB to the database during that outage.
You also need to consider disk fragmentation. The more often you add
space to the database, the more "pieces" the database will be broken in
to. You can reach a point where the database is so badly fragmented
that performance begins to suffer, due to the need to hunt around on the
disk for all of the fragments.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Ken
http://www.sql-server-performance.com/database_settings.asp
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO

Database Growth Options

Please, is there an advantage for using a static megabit number over using a
percentage and what will be a recommeded growth size for a 3gb database.
Thanks in advance for your assistance.
KenOIn a production environment, relying upon the Autogrow features is outside
of 'Best Practices'. Not bad to have as an 'emergency fallback' option, but
the database filegrowth 'should' be actively managed and extended during
'slack' operation times.
If you 'MUST' reply upon the Autogrow features, then the use of the
database, the new/changed data flow, and the number of users are important
considerations. A database used only for reporting may not need any
Autogrowth, whereas a very active order entry database with hundreds of
users may need to 'grow' in large chunks.
The primary thing to remember is that Autogrow has a performance impact, and
may result in fragmented datafiles, so you don't want it happening often.
Grow by large enough chunks to handle a substantial amount of need.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO|||KenO wrote:
> Please, is there an advantage for using a static megabit number over using
a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO
Imagine your database is 214GB, and you've set it to auto-grow at a 10%
interval. The next time it grows, you're going to have to wait for it
to write 21.4GB of empty space. Might take a while.
Ideally, your database should never grow automatically. You, as the
DBA, should plan accordingly, and adjust the database size as needed
during planned outages or quiet periods when the overhead of growing the
database won't affect performance.
How much to grow by? Again, YOU need to determine that. If you add
600MB of data each month, and you have a monthly outage window, then
you'll want to add AT LEAST 600MB to the database during that outage.
You also need to consider disk fragmentation. The more often you add
space to the database, the more "pieces" the database will be broken in
to. You can reach a point where the database is so badly fragmented
that performance begins to suffer, due to the need to hunt around on the
disk for all of the fragments.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Ken
http://www.sql-server-performance.c...se_settings.asp
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO

database grows abnormally

there are 350 tables in database.in each hour 60 records appended to each
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
Message posted via http://www.sqlmonster.com
sepideh iranpour via SQLMonster.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
David Gugick
Imceda Software
www.imceda.com
sql

database grows abnormally

there are 350 tables in database.in each hour 60 records appended to each
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
--
Message posted via http://www.sqlmonster.comsepideh iranpour via SQLMonster.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
--
David Gugick
Imceda Software
www.imceda.com

database grows abnormally

there are 350 tables in database.in each hour 60 records appended to each
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
Message posted via http://www.droptable.comsepideh iranpour via droptable.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
David Gugick
Imceda Software
www.imceda.com

Database grew 50% in a week ??

What happened ?
normal volume of transaction.. db recovery is set to simple
Any Idea
Is there any way I can know the size of each table ?
Thanks
Sam
Probably did a reindex. That requires lots of free space other wise it will
grow the db.
Andrew J. Kelly SQL MVP
"Sam" <samnadeau@.sympatico.ca> wrote in message
news:elb7gTX3EHA.936@.TK2MSFTNGP12.phx.gbl...
> What happened ?
> normal volume of transaction.. db recovery is set to simple
> Any Idea
> Is there any way I can know the size of each table ?
> Thanks
> Sam
>
|||Can the system do this by itself ? Cause I know for sure I didn't do it.
What else ?
How can we get more info the the space usage of each tables ?
Thanks
Sam
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uS9Pmaa3EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Probably did a reindex. That requires lots of free space other wise it
> will grow the db.
> --
> Andrew J. Kelly SQL MVP
>
> "Sam" <samnadeau@.sympatico.ca> wrote in message
> news:elb7gTX3EHA.936@.TK2MSFTNGP12.phx.gbl...
>
|||It's also possible that that you had many inserts on a table that was
clustered in such a way that the inserts occurred throughout the table and
caused a lot of page splits. Run DBCC SHOWCONTIG to see the extent of
fragmentation. Pay close attention to page density.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Sam" <samnadeau@.sympatico.ca> wrote in message
news:ePgqTXf3EHA.3416@.TK2MSFTNGP09.phx.gbl...
Can the system do this by itself ? Cause I know for sure I didn't do it.
What else ?
How can we get more info the the space usage of each tables ?
Thanks
Sam
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uS9Pmaa3EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Probably did a reindex. That requires lots of free space other wise it
> will grow the db.
> --
> Andrew J. Kelly SQL MVP
>
> "Sam" <samnadeau@.sympatico.ca> wrote in message
> news:elb7gTX3EHA.936@.TK2MSFTNGP12.phx.gbl...
>
|||SQL Server doesn't do this by itself, but perhaps you have a join, possibly created by Maintenance
Plan Wizard, that does this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <samnadeau@.sympatico.ca> wrote in message news:ePgqTXf3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Can the system do this by itself ? Cause I know for sure I didn't do it.
> What else ?
> How can we get more info the the space usage of each tables ?
> Thanks
> Sam
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uS9Pmaa3EHA.1564@.TK2MSFTNGP09.phx.gbl...
>
|||> SQL Server doesn't do this by itself, but perhaps you have a join,
> possibly created by Maintenance
I think Tibor really means "Job" not "Join"<g>.
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uRtaG1f3EHA.2156@.TK2MSFTNGP10.phx.gbl...
> SQL Server doesn't do this by itself, but perhaps you have a join,
> possibly created by Maintenance
> Plan Wizard, that does this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sam" <samnadeau@.sympatico.ca> wrote in message
> news:ePgqTXf3EHA.3416@.TK2MSFTNGP09.phx.gbl...
>
|||True. Thanks Andrew.
I've just talked about joins in class, and probably typed a bit too fast... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O7e919f3EHA.1152@.TK2MSFTNGP14.phx.gbl...
> I think Tibor really means "Job" not "Join"<g>.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uRtaG1f3EHA.2156@.TK2MSFTNGP10.phx.gbl...
>

database free spaces

I have a database total size 2710 MB. It is 2699 MB used and 11 MB free.
This is what I see it in the view taskpad of the database. I did setup the
database for unrestrict growth. But the free spaces so small, do I need to
adjust it? If I do, I need it done in a hurry. Can someone provide
sugestions? Thanks.
--
GigiFist, the report might be based on old information. Read up on DBCC UPDATEUS
AGE. And to increase the
file size, you use ALTER DATABASE ... MODIFY FILE... Or, use EM to specify
a size larger than the
current size.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gigi" <gigi@.discussions.mcirosoft.com> wrote in message
news:45B66CA4-FE21-4019-88EF-C9E3507C47EB@.microsoft.com...
>I have a database total size 2710 MB. It is 2699 MB used and 11 MB free.
> This is what I see it in the view taskpad of the database. I did setup th
e
> database for unrestrict growth. But the free spaces so small, do I need t
o
> adjust it? If I do, I need it done in a hurry. Can someone provide
> sugestions? Thanks.
> --
> Gigi

database free spaces

I have a database total size 2710 MB. It is 2699 MB used and 11 MB free.
This is what I see it in the view taskpad of the database. I did setup the
database for unrestrict growth. But the free spaces so small, do I need to
adjust it? If I do, I need it done in a hurry. Can someone provide
sugestions? Thanks.
Gigi
Fist, the report might be based on old information. Read up on DBCC UPDATEUSAGE. And to increase the
file size, you use ALTER DATABASE ... MODIFY FILE... Or, use EM to specify a size larger than the
current size.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gigi" <gigi@.discussions.mcirosoft.com> wrote in message
news:45B66CA4-FE21-4019-88EF-C9E3507C47EB@.microsoft.com...
>I have a database total size 2710 MB. It is 2699 MB used and 11 MB free.
> This is what I see it in the view taskpad of the database. I did setup the
> database for unrestrict growth. But the free spaces so small, do I need to
> adjust it? If I do, I need it done in a hurry. Can someone provide
> sugestions? Thanks.
> --
> Gigi
sql

database free spaces

I have a database total size 2710 MB. It is 2699 MB used and 11 MB free.
This is what I see it in the view taskpad of the database. I did setup the
database for unrestrict growth. But the free spaces so small, do I need to
adjust it? If I do, I need it done in a hurry. Can someone provide
sugestions? Thanks.
--
GigiFist, the report might be based on old information. Read up on DBCC UPDATEUSAGE. And to increase the
file size, you use ALTER DATABASE ... MODIFY FILE... Or, use EM to specify a size larger than the
current size.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gigi" <gigi@.discussions.mcirosoft.com> wrote in message
news:45B66CA4-FE21-4019-88EF-C9E3507C47EB@.microsoft.com...
>I have a database total size 2710 MB. It is 2699 MB used and 11 MB free.
> This is what I see it in the view taskpad of the database. I did setup the
> database for unrestrict growth. But the free spaces so small, do I need to
> adjust it? If I do, I need it done in a hurry. Can someone provide
> sugestions? Thanks.
> --
> Gigi

Database file size question please

Hi,

I have set the DB to auto grow by 30 %. As well I have set it to
unrestricted size... However , I see the available size continually being
reduced to now less then .54 MB... Why is there not enough available ?

Gtime_to_go (camper_66@.hotmail.com) writes:
> I have set the DB to auto grow by 30 %. As well I have set it to
> unrestricted size... However , I see the available size continually being
> reduced to now less then .54 MB... Why is there not enough available ?

Where do you see thie available size?

Auto-grow does not set in, until there are no free extents at all, and a
new extent is needed.

Note that if there is no free disk space for these 30%, auto-grow will
fail.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,

I see the available space in the general tab > space allocation. it shows
used as 35.5 MB and availble .53. I have checked disk space available and
it is sufficient.

r,
g
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96C0C9EE71401Yazorman@.127.0.0.1...
> time_to_go (camper_66@.hotmail.com) writes:
> > I have set the DB to auto grow by 30 %. As well I have set it to
> > unrestricted size... However , I see the available size continually
being
> > reduced to now less then .54 MB... Why is there not enough available
?
> Where do you see thie available size?
> Auto-grow does not set in, until there are no free extents at all, and a
> new extent is needed.
> Note that if there is no free disk space for these 30%, auto-grow will
> fail.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||time_to_go (camper_66@.hotmail.com) writes:
> I see the available space in the general tab > space allocation. it shows
> used as 35.5 MB and availble .53. I have checked disk space available and
> it is sufficient.

Have you experienced any errors which claims that you are out of space?

Also, try running DBCC UPDATEUSAGE and then sp_spaceused in Query Analyzer.
Enterprise Manager is not always trustable for size information.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

database File size problem

Hi,
I have a database which is 10 GB big but on disk it shows 20GB. I did
backup and shrinkdb but it doesn't shrink. I could shrink log but not
the actual db. Any hints why I cannot do this? Also weird thing is that
when I run DBCC shrinkdb I don't get any error message.
Here is the exact command I run:
DBCC SHRINKDATABASE (MYDB, 5, TRUNCATEONLY)
GO
Tnank you,
hjHi
DBCC SHRINKDATABASE will not shrink a file smaller than it's initial size,
which may be the issue in your case. Use DBCC SHRINKFILE to shrink the
individual file, but if you are will be expanding to 20GB at some time, then
you may not want to shrink it at all. Your log file should be a reasonably
constant size, if you backup the log regularly (in FULL recovery mode).
John
"Hitesh" wrote:

> Hi,
> I have a database which is 10 GB big but on disk it shows 20GB. I did
> backup and shrinkdb but it doesn't shrink. I could shrink log but not
> the actual db. Any hints why I cannot do this? Also weird thing is that
> when I run DBCC shrinkdb I don't get any error message.
> Here is the exact command I run:
> DBCC SHRINKDATABASE (MYDB, 5, TRUNCATEONLY)
> GO
> Tnank you,
> hj
>|||DBCC SHRINKDATABASE will not shrink a file smaller than its shrinkpoint.
The shrinkpoint starts out at the initial size of the file, but once you use
DBCC SHRINKFILE, that can set a new shrinkpoint, and subsequent DBCC
SHRINKDATABASE operations can shrink to that new smaller size.
Also, Hitesh, make sure you're aware what the parameters to DBCC
SHRINKDATABASE mean. The 5 parameter means to shrink so that there is 5%
free space in the file. If there is already more than 5% free space, no
shrinking will take place.
When you run DBCC SHRINKFILE, then the number is the size in MB to which you
want to shrink the file.
Why do you think you might get an error message?
--
HTH
Kalen Delaney, SQL Server MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:79C1DC8D-4BB0-4D11-BBED-A98A9BA35A2C@.microsoft.com...[vbcol=seagreen]
> Hi
> DBCC SHRINKDATABASE will not shrink a file smaller than it's initial size,
> which may be the issue in your case. Use DBCC SHRINKFILE to shrink the
> individual file, but if you are will be expanding to 20GB at some time,
> then
> you may not want to shrink it at all. Your log file should be a reasonably
> constant size, if you backup the log regularly (in FULL recovery mode).
> John
> "Hitesh" wrote:
>|||Thank you John & Kalean.
When I used DBCC SHRINKFILE on indivisual database files, it worked.
Kalean is right I might be using wrong free space percentage.
Thank you both for your help.
hj
Kalen Delaney wrote:[vbcol=seagreen]
> DBCC SHRINKDATABASE will not shrink a file smaller than its shrinkpoint.
> The shrinkpoint starts out at the initial size of the file, but once you u
se
> DBCC SHRINKFILE, that can set a new shrinkpoint, and subsequent DBCC
> SHRINKDATABASE operations can shrink to that new smaller size.
> Also, Hitesh, make sure you're aware what the parameters to DBCC
> SHRINKDATABASE mean. The 5 parameter means to shrink so that there is 5%
> free space in the file. If there is already more than 5% free space, no
> shrinking will take place.
> When you run DBCC SHRINKFILE, then the number is the size in MB to which y
ou
> want to shrink the file.
> Why do you think you might get an error message?
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:79C1DC8D-4BB0-4D11-BBED-A98A9BA35A2C@.microsoft.com...

database File size problem

Hi,
I have a database which is 10 GB big but on disk it shows 20GB. I did
backup and shrinkdb but it doesn't shrink. I could shrink log but not
the actual db. Any hints why I cannot do this? Also weird thing is that
when I run DBCC shrinkdb I don't get any error message.
Here is the exact command I run:
DBCC SHRINKDATABASE (MYDB, 5, TRUNCATEONLY)
GO
Tnank you,
hjHi
DBCC SHRINKDATABASE will not shrink a file smaller than it's initial size,
which may be the issue in your case. Use DBCC SHRINKFILE to shrink the
individual file, but if you are will be expanding to 20GB at some time, then
you may not want to shrink it at all. Your log file should be a reasonably
constant size, if you backup the log regularly (in FULL recovery mode).
John
"Hitesh" wrote:
> Hi,
> I have a database which is 10 GB big but on disk it shows 20GB. I did
> backup and shrinkdb but it doesn't shrink. I could shrink log but not
> the actual db. Any hints why I cannot do this? Also weird thing is that
> when I run DBCC shrinkdb I don't get any error message.
> Here is the exact command I run:
> DBCC SHRINKDATABASE (MYDB, 5, TRUNCATEONLY)
> GO
> Tnank you,
> hj
>|||DBCC SHRINKDATABASE will not shrink a file smaller than its shrinkpoint.
The shrinkpoint starts out at the initial size of the file, but once you use
DBCC SHRINKFILE, that can set a new shrinkpoint, and subsequent DBCC
SHRINKDATABASE operations can shrink to that new smaller size.
Also, Hitesh, make sure you're aware what the parameters to DBCC
SHRINKDATABASE mean. The 5 parameter means to shrink so that there is 5%
free space in the file. If there is already more than 5% free space, no
shrinking will take place.
When you run DBCC SHRINKFILE, then the number is the size in MB to which you
want to shrink the file.
Why do you think you might get an error message?
--
HTH
Kalen Delaney, SQL Server MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:79C1DC8D-4BB0-4D11-BBED-A98A9BA35A2C@.microsoft.com...
> Hi
> DBCC SHRINKDATABASE will not shrink a file smaller than it's initial size,
> which may be the issue in your case. Use DBCC SHRINKFILE to shrink the
> individual file, but if you are will be expanding to 20GB at some time,
> then
> you may not want to shrink it at all. Your log file should be a reasonably
> constant size, if you backup the log regularly (in FULL recovery mode).
> John
> "Hitesh" wrote:
>> Hi,
>> I have a database which is 10 GB big but on disk it shows 20GB. I did
>> backup and shrinkdb but it doesn't shrink. I could shrink log but not
>> the actual db. Any hints why I cannot do this? Also weird thing is that
>> when I run DBCC shrinkdb I don't get any error message.
>> Here is the exact command I run:
>> DBCC SHRINKDATABASE (MYDB, 5, TRUNCATEONLY)
>> GO
>> Tnank you,
>> hj
>>|||Thank you John & Kalean.
When I used DBCC SHRINKFILE on indivisual database files, it worked.
Kalean is right I might be using wrong free space percentage.
Thank you both for your help.
hj
Kalen Delaney wrote:
> DBCC SHRINKDATABASE will not shrink a file smaller than its shrinkpoint.
> The shrinkpoint starts out at the initial size of the file, but once you use
> DBCC SHRINKFILE, that can set a new shrinkpoint, and subsequent DBCC
> SHRINKDATABASE operations can shrink to that new smaller size.
> Also, Hitesh, make sure you're aware what the parameters to DBCC
> SHRINKDATABASE mean. The 5 parameter means to shrink so that there is 5%
> free space in the file. If there is already more than 5% free space, no
> shrinking will take place.
> When you run DBCC SHRINKFILE, then the number is the size in MB to which you
> want to shrink the file.
> Why do you think you might get an error message?
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:79C1DC8D-4BB0-4D11-BBED-A98A9BA35A2C@.microsoft.com...
> > Hi
> >
> > DBCC SHRINKDATABASE will not shrink a file smaller than it's initial size,
> > which may be the issue in your case. Use DBCC SHRINKFILE to shrink the
> > individual file, but if you are will be expanding to 20GB at some time,
> > then
> > you may not want to shrink it at all. Your log file should be a reasonably
> > constant size, if you backup the log regularly (in FULL recovery mode).
> >
> > John
> >
> > "Hitesh" wrote:
> >
> >> Hi,
> >>
> >> I have a database which is 10 GB big but on disk it shows 20GB. I did
> >> backup and shrinkdb but it doesn't shrink. I could shrink log but not
> >> the actual db. Any hints why I cannot do this? Also weird thing is that
> >> when I run DBCC shrinkdb I don't get any error message.
> >> Here is the exact command I run:
> >>
> >> DBCC SHRINKDATABASE (MYDB, 5, TRUNCATEONLY)
> >>
> >> GO
> >>
> >> Tnank you,
> >> hj
> >>
> >>

Database File size monitor

What are folks using to monitor database file sizes? I have been tasked
with writing a script to monitor our db's (Yes I know there are growth
controls, etc...). I was hoping there maybe a way to keep track of this via
some widget dashboard, etc... I have Solarwinds and will look at importing
SAN mibs, but still would like to hear thoughts from others.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.Take a look at sp_helpdb. It'll show you how the db sizes are calculated.
Instead of just displaying the values, store then in a table and then you'll
be able to see how fast you db is growing each day, month, minute, hour...or
whatever.
--
MG
"Paul Bergson [MVP-DS]" wrote:
> What are folks using to monitor database file sizes? I have been tasked
> with writing a script to monitor our db's (Yes I know there are growth
> controls, etc...). I was hoping there maybe a way to keep track of this via
> some widget dashboard, etc... I have Solarwinds and will look at importing
> SAN mibs, but still would like to hear thoughts from others.
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||DECLARE @.DB sysname
DECLARE @.SQL nvarchar(255)
if exists ( select * from tempdb..sysobjects where name LIKE
'#FileStats__%' ) drop table #FileStats
CREATE TABLE #FileStats(
[FileId] INT,
[FileGroup] INT,
[TotalExtents] INT,
[UsedExtents] INT,
[Name] sysname,
[Filename] varchar(255)
)
DECLARE @.FileStats TABLE (
[FileId] INT,
[FileGroup] INT,
[TotalExtents] INT,
[UsedExtents] INT,
[Name] sysname,
[Filename] varchar(255)
)
DECLARE cDatabases CURSOR FOR
SELECT QUOTENAME(sdb.name)
FROM master.dbo.sysdatabases sdb
WHERE status & 32 != 32
AND status & 64 != 64
AND status & 128 != 128
AND status & 256 != 256
AND status & 512 != 512
AND status & 1024 != 1024
AND status & 4096 != 4096
AND status & 32768 !=32768
OPEN cDatabases
FETCH FROM cDatabases INTO @.DB
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
DELETE FROM #FileStats
SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
SHOWFILESTATS'')'
EXEC (@.SQL)
UPDATE #FileStats SET name = @.DB
INSERT INTO @.FileStats SELECT * FROM #FileStats
FETCH FROM cDatabases INTO @.DB
END
CLOSE cDatabases
DEALLOCATE cDatabases
SELECT
[Name]
,[TotalExtents]*64/1024. AS TotalExtInMB
,[UsedExtents]*64/1024. AS UsedExtInMB
,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocExtInMB --* 64 /
1024. AS UnAllocExtInMB
,CAST(FLOOR(ROUND([UsedExtents] * 100. / [TotalExtents], 0)) AS
VARCHAR(3)) + '%' AS Pct_Full
FROM @.FileStats
ORDER BY TotalExtInMB DESC
--exec sp_spaceused
DBCC sqlperf(logspace)
"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
> What are folks using to monitor database file sizes? I have been tasked
> with writing a script to monitor our db's (Yes I know there are growth
> controls, etc...). I was hoping there maybe a way to keep track of this
> via some widget dashboard, etc... I have Solarwinds and will look at
> importing SAN mibs, but still would like to hear thoughts from others.
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||I use a custom script based around aggregating data from DBCC SHOWFILESTATS.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
> What are folks using to monitor database file sizes? I have been tasked
> with writing a script to monitor our db's (Yes I know there are growth
> controls, etc...). I was hoping there maybe a way to keep track of this
> via some widget dashboard, etc... I have Solarwinds and will look at
> importing SAN mibs, but still would like to hear thoughts from others.
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Thanks for your feedback, it is appreciated.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hurme" <michael.geles@.thomson.com> wrote in message
news:F8CA1619-B919-45B9-9C76-75DF14160A66@.microsoft.com...
> Take a look at sp_helpdb. It'll show you how the db sizes are calculated.
> Instead of just displaying the values, store then in a table and then
> you'll
> be able to see how fast you db is growing each day, month, minute,
> hour...or
> whatever.
> --
> MG
>
> "Paul Bergson [MVP-DS]" wrote:
>> What are folks using to monitor database file sizes? I have been tasked
>> with writing a script to monitor our db's (Yes I know there are growth
>> controls, etc...). I was hoping there maybe a way to keep track of this
>> via
>> some widget dashboard, etc... I have Solarwinds and will look at
>> importing
>> SAN mibs, but still would like to hear thoughts from others.
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Thanks for your feedback, it is appreciated.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jay" <nospam@.nospam.org> wrote in message
news:uUlhoBZ8HHA.5012@.TK2MSFTNGP02.phx.gbl...
> DECLARE @.DB sysname
> DECLARE @.SQL nvarchar(255)
> if exists ( select * from tempdb..sysobjects where name LIKE
> '#FileStats__%' ) drop table #FileStats
> CREATE TABLE #FileStats(
> [FileId] INT,
> [FileGroup] INT,
> [TotalExtents] INT,
> [UsedExtents] INT,
> [Name] sysname,
> [Filename] varchar(255)
> )
> DECLARE @.FileStats TABLE (
> [FileId] INT,
> [FileGroup] INT,
> [TotalExtents] INT,
> [UsedExtents] INT,
> [Name] sysname,
> [Filename] varchar(255)
> )
> DECLARE cDatabases CURSOR FOR
> SELECT QUOTENAME(sdb.name)
> FROM master.dbo.sysdatabases sdb
> WHERE status & 32 != 32
> AND status & 64 != 64
> AND status & 128 != 128
> AND status & 256 != 256
> AND status & 512 != 512
> AND status & 1024 != 1024
> AND status & 4096 != 4096
> AND status & 32768 !=32768
> OPEN cDatabases
> FETCH FROM cDatabases INTO @.DB
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> DELETE FROM #FileStats
> SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
> SHOWFILESTATS'')'
> EXEC (@.SQL)
> UPDATE #FileStats SET name = @.DB
> INSERT INTO @.FileStats SELECT * FROM #FileStats
> FETCH FROM cDatabases INTO @.DB
> END
> CLOSE cDatabases
> DEALLOCATE cDatabases
> SELECT
> [Name]
> ,[TotalExtents]*64/1024. AS TotalExtInMB
> ,[UsedExtents]*64/1024. AS UsedExtInMB
> ,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocExtInMB --* 64 /
> 1024. AS UnAllocExtInMB
> ,CAST(FLOOR(ROUND([UsedExtents] * 100. / [TotalExtents], 0)) AS
> VARCHAR(3)) + '%' AS Pct_Full
> FROM @.FileStats
> ORDER BY TotalExtInMB DESC
> --exec sp_spaceused
> DBCC sqlperf(logspace)
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
>> What are folks using to monitor database file sizes? I have been tasked
>> with writing a script to monitor our db's (Yes I know there are growth
>> controls, etc...). I was hoping there maybe a way to keep track of this
>> via some widget dashboard, etc... I have Solarwinds and will look at
>> importing SAN mibs, but still would like to hear thoughts from others.
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>|||Thanks for your feedback, it is appreciated.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:ON2b3EZ8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>I use a custom script based around aggregating data from DBCC
>SHOWFILESTATS.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
>> What are folks using to monitor database file sizes? I have been tasked
>> with writing a script to monitor our db's (Yes I know there are growth
>> controls, etc...). I was hoping there maybe a way to keep track of this
>> via some widget dashboard, etc... I have Solarwinds and will look at
>> importing SAN mibs, but still would like to hear thoughts from others.
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>

Database File Size

I have a Database which when I Right Click and go to Properties size is 52 GB

But the Size of MDF + NDF Files is 25 + 7 = 32 GB. Log file Size is 20 GB. So I am thinking -- Properties Size of DB includes size of Log Files too -- is that correct?

But when I do a Full Backup the .bak File Size is 26 GB -- does the Full Backup Shrink a DB ?

I thot Full Backup only Shrink the Log Files and could not find anywhere in BOL where it says BACKUP shrinks the empty space in Database -- can somebody confirm this?

Hi JaguarRDA,

Its because backup is always lower than original size of database, because backup copy only used pages.

|||

Yeah -- also Backups dont touch the Log Files -- that was a wrong statement on my behalf that they shrunk the log files

|||

JaguarRDA283361 wrote:

Yeah -- also Backups dont touch the Log Files --

Depends on the type of "recovery model", see

sql

Database Expansion Problem

Hello:
I have an SQL2K db that is 7.5GB in size. The data files
have 0% free space. When I attempt to expand the db to
add more space, the size of the data file increases by
whatever increment I have just put in.
When I check the properties of the database after
expanding it, it still shows 0% free space. If I check
the actual size of the .mdb file, it has also increased.
But if I shrink the database, it shrinks to the original
7.5GB I've done this with another database and the
increase in the amount of what I allocate for the
database shows as free space when I check the db
properties.
Thanks for any help
BrennanTry running
USE YourDatabaseName
sp_spaceused @.updateusage='TRUE' . This will update the usage information
for the database.
Jim
"Brennan" <anonymous@.discussions.microsoft.com> wrote in message
news:027e01c3ce56$89a001e0$a601280a@.phx.gbl...
> Hello:
> I have an SQL2K db that is 7.5GB in size. The data files
> have 0% free space. When I attempt to expand the db to
> add more space, the size of the data file increases by
> whatever increment I have just put in.
> When I check the properties of the database after
> expanding it, it still shows 0% free space. If I check
> the actual size of the .mdb file, it has also increased.
> But if I shrink the database, it shrinks to the original
> 7.5GB I've done this with another database and the
> increase in the amount of what I allocate for the
> database shows as free space when I check the db
> properties.
> Thanks for any help
> Brennan

Database dump size

Hi there. Running a Sql 2000 installation with latest service pack on
Windows 2000 server. My database dump to disk backup keeps increasing
by about 1 meg per day. The database itself is increasing by a small
percentage of that. In fact the dump is now about the same size as the
database. What's going on? Thanks.
What kind of backup are you doing? If you don't use the INIT option the
current backup will append to the existing ones in the dump device.
Andrew J. Kelly
SQL Server MVP
"cscott" <christopher@.uncommonlaw.com> wrote in message
news:a94f8da8.0406070533.6a59d6fb@.posting.google.c om...
> Hi there. Running a Sql 2000 installation with latest service pack on
> Windows 2000 server. My database dump to disk backup keeps increasing
> by about 1 meg per day. The database itself is increasing by a small
> percentage of that. In fact the dump is now about the same size as the
> database. What's going on? Thanks.
|||Hi ,
To add on to Andrew post, Execute the below comand from query analyzer to
check if BACKUP is executed in append mode.
restore headeronly from disk='Directory\dbname.BAK'
If this list more than 1 entry (Check the postion column in output), means
you have taken the backup in Append mode. To remove he append mode add an
OPTION
INIT along with backup database command
BACKUP DATABASE dbname to disk='c:\dbname.bak' with INIT
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uYtrikJTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> What kind of backup are you doing? If you don't use the INIT option the
> current backup will append to the existing ones in the dump device.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "cscott" <christopher@.uncommonlaw.com> wrote in message
> news:a94f8da8.0406070533.6a59d6fb@.posting.google.c om...
>
|||Used the maintenance plan wizard to set it up. It's a dump to disk.
There aren't many options to select really. It does seem like it's
appending the dump. Is there an option in Enterprise manager to disable
append or will I have to do it the hard way? Thanks.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Maint plan doesn't append. It creates a new backup device each time it performs a backup. Verify using RESTORE
HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Christopher Scott" <christopher@.uncommonlaw.com> wrote in message
news:%23cTXggVTEHA.2416@.TK2MSFTNGP12.phx.gbl...
>
> Used the maintenance plan wizard to set it up. It's a dump to disk.
> There aren't many options to select really. It does seem like it's
> appending the dump. Is there an option in Enterprise manager to disable
> append or will I have to do it the hard way? Thanks.
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Database dump size

Hi there. Running a Sql 2000 installation with latest service pack on
Windows 2000 server. My database dump to disk backup keeps increasing
by about 1 meg per day. The database itself is increasing by a small
percentage of that. In fact the dump is now about the same size as the
database. What's going on? Thanks.What kind of backup are you doing? If you don't use the INIT option the
current backup will append to the existing ones in the dump device.
Andrew J. Kelly
SQL Server MVP
"cscott" <christopher@.uncommonlaw.com> wrote in message
news:a94f8da8.0406070533.6a59d6fb@.posting.google.com...
> Hi there. Running a Sql 2000 installation with latest service pack on
> Windows 2000 server. My database dump to disk backup keeps increasing
> by about 1 meg per day. The database itself is increasing by a small
> percentage of that. In fact the dump is now about the same size as the
> database. What's going on? Thanks.|||Hi ,
To add on to Andrew post, Execute the below comand from query analyzer to
check if BACKUP is executed in append mode.
restore headeronly from disk='Directory\dbname.BAK'
If this list more than 1 entry (Check the postion column in output), means
you have taken the backup in Append mode. To remove he append mode add an
OPTION
INIT along with backup database command
BACKUP DATABASE dbname to disk='c:\dbname.bak' with INIT
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uYtrikJTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> What kind of backup are you doing? If you don't use the INIT option the
> current backup will append to the existing ones in the dump device.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "cscott" <christopher@.uncommonlaw.com> wrote in message
> news:a94f8da8.0406070533.6a59d6fb@.posting.google.com...
>|||Used the maintenance plan wizard to set it up. It's a dump to disk.
There aren't many options to select really. It does seem like it's
appending the dump. Is there an option in Enterprise manager to disable
append or will I have to do it the hard way? Thanks.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Maint plan doesn't append. It creates a new backup device each time it perfo
rms a backup. Verify using RESTORE
HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Christopher Scott" <christopher@.uncommonlaw.com> wrote in message
news:%23cTXggVTEHA.2416@.TK2MSFTNGP12.phx.gbl...
>
> Used the maintenance plan wizard to set it up. It's a dump to disk.
> There aren't many options to select really. It does seem like it's
> appending the dump. Is there an option in Enterprise manager to disable
> append or will I have to do it the hard way? Thanks.
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!