Tuesday, March 27, 2012

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
>

No comments:

Post a Comment