asked on

Checking space

Two weeks ago i ran this on all our databases
exec sp_helpDB 'database'

I wrote down the size of the DATA and the LOG into an excel spreadsheet i get around to automation of this in to a table one day.

Then after two weeks i did it again and the sizes remained the same.............i was hoping it go up.........or no one doing anything but i find that hard to do as its our ERP database.

Am i running the best check

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TRACEYMARY

ASKER

is the best command i should run.

When set up database i do not put any restrictions ...put automatically grow..........does sql automatically allocate free space.

Anthony Perkins

>>does sql automatically allocate free space.<<
Only you know the answer to that question. It depends how you defined the database. By default the database increases in size in 10% increments, but this can be changed.

iloya

Use this query against Master Database. It will help you to get the actual sizes of Databases and Log files along with growth factor. The sizes are in KB.

Select Name, FileName, Growth, Size from master..sysaltfiles

imran_fast

<<Then after two weeks i did it again and the sizes remained the same>>
The size of the files will only increase when it needs them by default data and log file will increase by 10% so i think in your case there is still available free space in the data and log file and it will increase by 10% when it ran out of it.

you can check the available free space in your file by simply

in enterprise manager Right Click Database ----> All Tasks --> Shirnk Database --> space Free

TRACEYMARY

ASKER

I see the 10 % .............i must have missed that always looked at whether it was ticked to unrestricted file growth .....for the main erp databases...
Got it the size of file will have 10% used space....................when that 10% space has been used up it will then grew the database size to the size of the database + 10%.

So if i have 27 gig i work out 10% of 27 gigs ...........

If i check space free................and measure that each week............................when it is nearly used i can determine that the new file size will become 27 gigs * 10%

Then i can ensure the file server has enough space to accomodate it.

Does that sound right.........

TRACEYMARY

ASKER

i ran the command
Select Name, FileName, Growth, Size from master..sysaltfiles
Is there a way for me to see Growth , free , used

SOLUTION

iloya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

imran_fast

<<file size will become 27 gigs * 10% >>
27 + 27 * 0.1 = 29.7 gb

why wont you increase you database be 512 mb instead of 10% that would be good.

/*run this on your current database and you will see the existing used space of the file*/
select * from dbo.sysfiles

the size column is calculated as (sized currently used by database in KB)/ 8

iloya

The above sp needs some more modifications though but I guess you can use the idea and get whatever you want if you just go thru the code.

TRACEYMARY

ASKER

I not sure on how much space is being used weekly........if i can work that out then i could set it to 512mb instead of 10% but i do not want to give 512 and it be greater than that.

ASKER CERTIFIED SOLUTION

imran_fast

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TRACEYMARY

ASKER

Let me run some commands over here..........and see

Thanks i appreciate your help

imran_fast

check this command as well
exec sp_spaceused