File size is growing fast

Hi,
My SQL database is about 90MB and start growing very fast to 1000MB over a
w time. I try to shrink from the EM with no success. I notice that only
the size of the log file is reduced. Is there a way to do shrink the
database file through Query Analyzer.
I also want to run the shrink statement at the time of starting the service.
I know that there is a SP in the Master DB that do this.
Any advice on the above topic would be very grateful
SFDBCC SHRINKDATABASE
( database_name [ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
You need to run the DBCC SHRIKNDATABASE first and then execute the DBCC
SHRINKFILE for both files, data and log.
Another note, usually there is a reason for the files to grow to the size
they are before you shrink them. You may want to identify and monitor it. Yo
u
don't want to shrink a file which will be expanded subsequently due to the
normal processing. Expanding a file could be affect performance, specially i
t
it happens during peak hours.
"SF" wrote:

> Hi,
> My SQL database is about 90MB and start growing very fast to 1000MB over a
> w time. I try to shrink from the EM with no success. I notice that only
> the size of the log file is reduced. Is there a way to do shrink the
> database file through Query Analyzer.
> I also want to run the shrink statement at the time of starting the servic
e.
> I know that there is a SP in the Master DB that do this.
> Any advice on the above topic would be very grateful
> SF
>
>|||> I also want to run the shrink statement at the time of starting the servic
e.
I suggest you read this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SF" <socfund@.online.com.kh> wrote in message news:ungJVRrWGHA.1192@.TK2MSFTNGP03.phx.gbl...

> Hi,
> My SQL database is about 90MB and start growing very fast to 1000MB over a
> w time. I try to shrink from the EM with no success. I notice that only
> the size of the log file is reduced. Is there a way to do shrink the
> database file through Query Analyzer.
> I also want to run the shrink statement at the time of starting the servic
e.
> I know that there is a SP in the Master DB that do this.
> Any advice on the above topic would be very grateful
> SF
>|||Is the size of the data file (mdf) growing quickly? or that of the log file
(ldf)?
You can check the size of the database files, and if it is the log file that
grows quickly, you are suggested to implement appropriate backup strategy
accordingly.
Martin C K Poon
Senior Analyst Programmer
====================================
"SF" <socfund@.online.com.kh> bl
news:ungJVRrWGHA.1192@.TK2MSFTNGP03.phx.gbl g...
> Hi,
> My SQL database is about 90MB and start growing very fast to 1000MB over a
> w time. I try to shrink from the EM with no success. I notice that only
> the size of the log file is reduced. Is there a way to do shrink the
> database file through Query Analyzer.
> I also want to run the shrink statement at the time of starting the
service.
> I know that there is a SP in the Master DB that do this.
> Any advice on the above topic would be very grateful
> SF
>|||It is the size of the data file (mdf) that grows quickly.
SF
"Martin C K Poon" < martin__dot__poon__at__multiable__dot__c
om> wrote in
message news:eyqL#OwWGHA.4972@.TK2MSFTNGP02.phx.gbl...
> Is the size of the data file (mdf) growing quickly? or that of the log
file
> (ldf)?
> You can check the size of the database files, and if it is the log file
that
> grows quickly, you are suggested to implement appropriate backup strategy
> accordingly.
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "SF" <socfund@.online.com.kh> bl
> news:ungJVRrWGHA.1192@.TK2MSFTNGP03.phx.gbl g...
a
only
> service.
>|||SF (socfund@.online.com.kh) writes:
> It is the size of the data file (mdf) that grows quickly.
Most likely then it grows because you are adding a lot of data to it.
If you don't expect it to grow that fast, you should examine what is
going on.
First run this:
exec sp_spaceused null, true
to get accurate information on space within the file.
Then run
SELECT object_name(id), reserved, used
FROM sysindexes
WHERE indid IN (0, 1)
ORDER BY reserved DESC
to see which objects that are taking space in your database.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You might want to check the default fillfactor (server level) (Database
Settings Tab or run sp_configure)...I have seen this happen when somone
changed this setting without knowing what they were doing.