Data File Size Increase

Hi,
Currently we are having 20 databases and mergereplicating the databases
also.
But only for 2 databases the DATAFile size got increased.
Please give what may be the reason for that.
Please give me the Solution as early as Possible
Thanks
SouraHi
Do you have autogrow feature enabled?
Look at ALTER DATABASE command to increase a size if the file
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> Hi,
> Currently we are having 20 databases and mergereplicating the
databases
> also.
> But only for 2 databases the DATAFile size got increased.
> Please give what may be the reason for that.
> Please give me the Solution as early as Possible
> Thanks
> Soura
>|||Perhaps there was free space for the other database's database data files to accommodate the
modifications?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> Hi,
> Currently we are having 20 databases and mergereplicating the databases
> also.
> But only for 2 databases the DATAFile size got increased.
> Please give what may be the reason for that.
> Please give me the Solution as early as Possible
> Thanks
> Soura
>|||Hi,
Also check if you have restricted file growth to the other databases in the
databases option tab.
CU
Andreas
"Uri Dimant" wrote:
> Hi
> Do you have autogrow feature enabled?
> Look at ALTER DATABASE command to increase a size if the file
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> > Hi,
> > Currently we are having 20 databases and mergereplicating the
> databases
> > also.
> >
> > But only for 2 databases the DATAFile size got increased.
> >
> > Please give what may be the reason for that.
> >
> > Please give me the Solution as early as Possible
> >
> > Thanks
> > Soura
> >
> >
>
>

Data File Size Increase

Hi,
Currently we are having 20 databases and mergereplicating the databases
also.
But only for 2 databases the DATAFile size got increased.
Please give what may be the reason for that.
Please give me the Solution as early as Possible
Thanks
Soura
Hi
Do you have autogrow feature enabled?
Look at ALTER DATABASE command to increase a size if the file
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> Hi,
> Currently we are having 20 databases and mergereplicating the
databases
> also.
> But only for 2 databases the DATAFile size got increased.
> Please give what may be the reason for that.
> Please give me the Solution as early as Possible
> Thanks
> Soura
>
|||Perhaps there was free space for the other database's database data files to accommodate the
modifications?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> Hi,
> Currently we are having 20 databases and mergereplicating the databases
> also.
> But only for 2 databases the DATAFile size got increased.
> Please give what may be the reason for that.
> Please give me the Solution as early as Possible
> Thanks
> Soura
>
|||Hi,
Also check if you have restricted file growth to the other databases in the
databases option tab.
CU
Andreas
"Uri Dimant" wrote:

> Hi
> Do you have autogrow feature enabled?
> Look at ALTER DATABASE command to increase a size if the file
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> databases
>
>

Data File Size Increase

Hi,
Currently we are having 20 databases and mergereplicating the databases
also.
But only for 2 databases the DATAFile size got increased.
Please give what may be the reason for that.
Please give me the Solution as early as Possible
Thanks
SouraHi
Do you have autogrow feature enabled?
Look at ALTER DATABASE command to increase a size if the file
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> Hi,
> Currently we are having 20 databases and mergereplicating the
databases
> also.
> But only for 2 databases the DATAFile size got increased.
> Please give what may be the reason for that.
> Please give me the Solution as early as Possible
> Thanks
> Soura
>|||Perhaps there was free space for the other database's database data files to
accommodate the
modifications?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> Hi,
> Currently we are having 20 databases and mergereplicating the database
s
> also.
> But only for 2 databases the DATAFile size got increased.
> Please give what may be the reason for that.
> Please give me the Solution as early as Possible
> Thanks
> Soura
>|||Hi,
Also check if you have restricted file growth to the other databases in the
databases option tab.
CU
Andreas
"Uri Dimant" wrote:

> Hi
> Do you have autogrow feature enabled?
> Look at ALTER DATABASE command to increase a size if the file
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> databases
>
>sql

Data File Size

SELECT size_in_mb,used_size_in_mb,size_in_mb-used_size_in_mb as free_in_mb FROM (
SELECT cntr_value/1024 size_in_mb ,
(SELECT cntr_value/1024 FROM master..sysperfinfo WHERE counter_name='Log File(s) Used Size (KB)' AND instance_name='mydb') used_size_in_mb
FROM master..sysperfinfO WHERE counter_name='Log File(s) Size (KB)' AND INSTANCE_NAME='mydb'
) a

I need to store totalsize,usedsize,freesize of the datafiles in a table to get an average of how much my datafile has increased over a week.
The above query i am using is for logfile size. Can any one help me with datafile size plz.
I've checked sp_helpfile, sysfiles but couldn't find what i am lookin for(used and free space). EM in taskpad view for a database shows the statistics for the datafile. I've tried a trace to find out a stored procedure but couldn't!!!
May be i am unaware of a simple stored-procedure that can do this for me.

Howdy!use master
go
sp_helptext sp_spaceused
go

Maybe you will get some ideas from here ... am a little busy ... so just help yourself ...|||You can use code from sp_spaceused to make your own logic ...|||use master
go
sp_helptext sp_spaceused
go

Maybe you will get some ideas from here ... am a little busy ... so just help yourself ...

Thanx for guiding; i would try.

Howdy!

Data File size

My datafile is at 80GB. The data used in the datafile is about 70GB and
growing, with approximately 4,000,000 records in a table that contains a
Text field. I have a weekly purge that deletes about 500,000 records. (Disk
space is at a premium.) When I delete the 500,000 records in the table, the
data used in the data file does not reduce, but remains the same and keeps
on growing. I need to keep the datafile comfortably below 80GB.
My understanding is that shrinking the data file is not the best thing to
do, but if I have to, then I will. So essentially, to keep the data file
comfortably below 80GB I would:
1. Perform the weekly purge (deleting approximately 500,000 records).
2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
Would the above 2 steps be the means of keeping my datafile comfortably
below 80GB?
Message posted via http://www.droptable.com
You can turn off auto-grow option and lock your
datafile size on 80Gb. Make sure you have enough space
for your transaction log though in order to be able to
delete records weekly.
Shrink is really not good operation especially on large DBs.
Regards.
"Robert Richards via droptable.com" wrote:

> My datafile is at 80GB. The data used in the datafile is about 70GB and
> growing, with approximately 4,000,000 records in a table that contains a
> Text field. I have a weekly purge that deletes about 500,000 records. (Disk
> space is at a premium.) When I delete the 500,000 records in the table, the
> data used in the data file does not reduce, but remains the same and keeps
> on growing. I need to keep the datafile comfortably below 80GB.
> My understanding is that shrinking the data file is not the best thing to
> do, but if I have to, then I will. So essentially, to keep the data file
> comfortably below 80GB I would:
> 1. Perform the weekly purge (deleting approximately 500,000 records).
> 2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
> Would the above 2 steps be the means of keeping my datafile comfortably
> below 80GB?
> --
> Message posted via http://www.droptable.com
>
|||That does not sound like a solution, turning off auto grow. The reason why
is that at the current rate, the data file is growing and will eventually
fill up the 80GB. The purge is not reducing space in the data file.
Probably due to a high water mark. Therefore, even though I am keeping the
number of records at 4,000,000 or below, the unused space never gets
recovered, thus the datafile keeps growing even though the number of
records remains approximately the same.
I understand that ShrinkFile is not the best option, but what other option
is there?
Message posted via http://www.droptable.com
|||Hi,
What you are performing is absolutely perfect. Do a weekly purge or move the
old data into a history database.
Since you are going to store the data back into the same file you may not
need to shrink the file because the space you
purged will be utilized for the new data which is coming in.
What is the main reason you need to keep 80GB as maximum for your database?
There are many databases in the globe with more than Terabytes
and running smoothly.
Thanks
Hari
Sql Server MVP
"Robert Richards via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:0be9557414954ceca658ef750e5bb45e@.droptable.co m...
> My datafile is at 80GB. The data used in the datafile is about 70GB and
> growing, with approximately 4,000,000 records in a table that contains a
> Text field. I have a weekly purge that deletes about 500,000 records.
> (Disk
> space is at a premium.) When I delete the 500,000 records in the table,
> the
> data used in the data file does not reduce, but remains the same and keeps
> on growing. I need to keep the datafile comfortably below 80GB.
> My understanding is that shrinking the data file is not the best thing to
> do, but if I have to, then I will. So essentially, to keep the data file
> comfortably below 80GB I would:
> 1. Perform the weekly purge (deleting approximately 500,000 records).
> 2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
> Would the above 2 steps be the means of keeping my datafile comfortably
> below 80GB?
> --
> Message posted via http://www.droptable.com
|||I do not believe I am being understood. When I delete the 500,000, it is
not to an archive table. These 500,000 records are gone.
The 80GB max is due to available disk space.
When I delete records, I need to reclaim the unused space. Are the two
steps below a way of doing that?
1. Perform the weekly purge (deleting approximately 500,000 records).
2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
Is there a better way to reclaim unused space?
Message posted via http://www.droptable.com
|||What you are seeing is mainly due to the fact you have text or image
columns. Even though you delete rows it may not always free up all the
space it previously used for the text columns. If you have a clustered
index on the table you can reindex the table and possibly free up some space
that may have been due to fragmentation of the non-text columns. But in
2000 there is nothing you can do to clean up text space usage short of
exporting all the data, truncating the table and importing it back in. SQL
2005 will allow you to reorganize blobs. Your real solution is to get more
disk space so you can deal with it better.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:0be9557414954ceca658ef750e5bb45e@.droptable.co m...
> My datafile is at 80GB. The data used in the datafile is about 70GB and
> growing, with approximately 4,000,000 records in a table that contains a
> Text field. I have a weekly purge that deletes about 500,000 records.
> (Disk
> space is at a premium.) When I delete the 500,000 records in the table,
> the
> data used in the data file does not reduce, but remains the same and keeps
> on growing. I need to keep the datafile comfortably below 80GB.
> My understanding is that shrinking the data file is not the best thing to
> do, but if I have to, then I will. So essentially, to keep the data file
> comfortably below 80GB I would:
> 1. Perform the weekly purge (deleting approximately 500,000 records).
> 2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
> Would the above 2 steps be the means of keeping my datafile comfortably
> below 80GB?
> --
> Message posted via http://www.droptable.com
|||Why then does the "data used" decrease on the data file when I delete
records from the same table in my Test environment, but when I delete
records from the table in production the "data used" does not decrease?
Message posted via http://www.droptable.com
|||How are you determining the "data used"? Are you using sp_spaceused? Have
you tried running DBCC UPDATE USAGE or specifying the UpdateUasge option?
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:ce78b9bd5c5447658b8a0e2badaf82cb@.droptable.co m...
> Why then does the "data used" decrease on the data file when I delete
> records from the same table in my Test environment, but when I delete
> records from the table in production the "data used" does not decrease?
> --
> Message posted via http://www.droptable.com
|||Also, the TRUCATE ONLY option will not Free Up the space becaue truncate
will only remove from the end of the file. You need to MOVE all the data to
the head of the file before shrinking. You can only do that if you use the
DBCC SHRINKFILE(MyDB_Data, TargetSize).
Reindexing the clustered index will most likely cause the file to grow even
more because the entire table is basically copied to a new when as it
reindexes. However, if you reindex, that will defragment by the index
order. Then if you shrink the file, it will move the data pages in
defragmented order, but could take quite some time, especially on a 80 GB
database.
You should consider creating 1 to 1 relationships for the LOB data and their
associated base tables. Then you could create VIEWs to replace the original
table definitions to minimize code impact. Then put all of the LOB data in
a seperate table(s). Those tables could then be created in seperate files
on seperate FileGroups. The shrinking and reordering just those files
should minimize the durations of those operations.
If you want to garauntee what you are looking at, run sp_spaceused
@.UPDATEUSAGE = 'true'.
Sincerely,
Anthony Thomas

"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ecOFGg9ZFHA.3168@.TK2MSFTNGP10.phx.gbl...
How are you determining the "data used"? Are you using sp_spaceused? Have
you tried running DBCC UPDATE USAGE or specifying the UpdateUasge option?
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:ce78b9bd5c5447658b8a0e2badaf82cb@.droptable.co m...
> Why then does the "data used" decrease on the data file when I delete
> records from the same table in my Test environment, but when I delete
> records from the table in production the "data used" does not decrease?
> --
> Message posted via http://www.droptable.com
|||I am still not clear how or why my data continues to increase. What you say
I cannot quite comprehend given my current data set. For instance:
5/21/2005
Records in table: 4,091,571
Data used within data file: 64,546.3 MB
6/5/2005
Records in table: 3,679,559
Data used within data file: 71,828.5 MB
So even though I have 412,012 less records in the table (the only other
tables are small, static lookup tables) the data within the data file has
grown 7,282.2 MB.
I guess I just do not understand the text column well enough, to understand
why the substantial growth, despite the significantly less records in the
table.
Please help, as I am going to have to explain this to my supervisor in such
a way to either justify more disk space, or resolve the growth issue
despite less records. Thanks for your patience.
Message posted via http://www.droptable.com

Data File Size

Thanks Guys,
I understand that if the database is at a point that it's forced to autogrow
that will degrade performance while the database is growing. I want to know
what the impact of a say 90-95% full database is. This is with out an
autogrow situation. There will be updates, inserts, and reads but we never
reach full capicity. Does a database say that is 60-75% full perfom better
than a database that is 90-95% full or is there no known impact.
"TheSQLGuru" wrote:

> Best practice is to size your database proactively and only let autogrowth
> act in 'emergency' cases. Size the database to allow for 1-2 years of
> expected growth, and revisit at least every 6 months. This will allow for
> maintenance operations like index rebuilds/reorgs to be able to lay data
> down sequentially on disk for optimal performance and also avoid autogrowth
> slowdowns during peak periods.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "JDS" <JDS@.discussions.microsoft.com> wrote in message
> news:6D4CD763-FC56-4660-9097-8AC4E188D9AF@.microsoft.com...
>
>
Just to add more to what Kevin was getting at. Chances are (not 100%
guaranteed) that the closer you get to a full database the more the data
will be non-contiguous in the data files. When you reindex an index with
DBCC DBREINDEX or ALTER INDEX REBUILD the engine will create an entirely new
copy of the index in the file and then drop the old one when done. So first
off you need about 1.2 times the size of the index in free space just to
rebuild it. But if you only have that exact amount or anything close you
will most likely get the new index built in small fragments or pockets
within the data file(s) where ever there happened to be room. Where as with
plenty of free space the chances are much greater that you will have the
indexes built in a contiguous fashion. And if you do any range or table
scans, read - ahead's etc. this can make a big difference in performance.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"JDS" <JDS@.discussions.microsoft.com> wrote in message
news:F5291FF9-8516-437D-9053-3814FB5835B1@.microsoft.com...[vbcol=seagreen]
> Thanks Guys,
> I understand that if the database is at a point that it's forced to
> autogrow
> that will degrade performance while the database is growing. I want to
> know
> what the impact of a say 90-95% full database is. This is with out an
> autogrow situation. There will be updates, inserts, and reads but we
> never
> reach full capicity. Does a database say that is 60-75% full perfom
> better
> than a database that is 90-95% full or is there no known impact.
> "TheSQLGuru" wrote:

Data file size

Hi,
I brought this question up before, but I still need some help with it.
About a week or two ago, the data file for my SQL 2000 database was about
3.5 GB, with a 1 GB Transaction log. I back up the transaction log every 3
hours during the day and backup the Data file nightly.
I recently imported around 500,000 additional records into the database, and
the data file grew to about 5.5 GB, with a 8 GB Transaction log. I ran a
DBCC shrink and it reduced the data file to about 4 GB and the Tranaction Log
to about 3 GB.
I've done this numerous times already, and in another week, the data and
transaction log files will probably be right back up to 5.5 GB and 8 GB, and
probably even larger. Is this supposed to happen, or am I forgetting to do
something?
Any help would be terrific.
Thanks
-
Stu
Are you performing log backups? If not, make sure you have selected the
Simple recovery model.
David Portas
SQL Server MVP
|||Yes - The logs are backed up at 11:00, 2:00, and 5:00 every day.
"David Portas" wrote:

> Are you performing log backups? If not, make sure you have selected the
> Simple recovery model.
> --
> David Portas
> SQL Server MVP
> --
>
|||You need space in both the data and log files for the new data you are
inserting. If the files are not large enough they will grow. Depending on
the size and the growth percentage they may grow quite large. If you will
just repeat this process each week it makes no sense to shrink the files.
That does more harm than good and they will simply grow back again. Having
too much free space is not a problem but too little is.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:6BF3273A-E9DA-4434-B60A-0B8281854592@.microsoft.com...
> Hi,
> I brought this question up before, but I still need some help with it.
> About a week or two ago, the data file for my SQL 2000 database was about
> 3.5 GB, with a 1 GB Transaction log. I back up the transaction log every
> 3
> hours during the day and backup the Data file nightly.
> I recently imported around 500,000 additional records into the database,
> and
> the data file grew to about 5.5 GB, with a 8 GB Transaction log. I ran a
> DBCC shrink and it reduced the data file to about 4 GB and the Tranaction
> Log
> to about 3 GB.
> I've done this numerous times already, and in another week, the data and
> transaction log files will probably be right back up to 5.5 GB and 8 GB,
> and
> probably even larger. Is this supposed to happen, or am I forgetting to
> do
> something?
> Any help would be terrific.
> Thanks
> -
> Stu