Orion SQL db size is very high but tables very small

Hi guys,

I have a strange situation here.

My Database total space usage is: 150.5Gbytes

Data space usage is: 1.5Gbytes

When I check the tables, the biggest one is 1.5Gbytes and all less than that. The combined size is nowhere near the 150.

Can anyone tell me what is the problem?

Thanks,

Delgee

database problem.jpg

Find more posts tagged with

shrinkfile

transaction_logs

simple_recovery

log_file_size

full_recovery

Accepted answers

mezdem

ok can you tell us are you looking after the db or is the dba team?

If you are in full recovery they should have trn backups in place, if this is the case then by all means run the shrinkfile... this will then let you remove any unused space in the log file. If you have not done a backup of transactions you will need to do one first, then run shrinkfile.

If you have no space to backup either then you can alter to if you alter to 'simple' recovery, then perform a shrinkfile.

I have simple on for NPM then the log file automatically shrinks itself - depending on tranaction lengths it should never grow to big..except during large upgrades!.

**Choose your recovery model carefully, if you dont need point in time recovery dont use full..use batch or simple!

All comments

jeff.stewart

I would check the log files to see how large they are and maybe run some DB maintenance to shrink/compact the DB.

mezdem

Yes your screen shot does not show the bottom of the details page, what does the value in 'Transaction Log Size' state?

njoylif

I had this problem a while back. if you have the space on that drive (assuming both on same drive, probably need around 300GB), go into SQL and select the correct DB. Try doing a full backup. I believe that usually truncates the log file.

If that doesn't work, run the query:
DBCC SHRINKFILE (NetPerfMon_log, TRUNCATEONLY)

These won't work if there is not enough space.

if you don't have that much space and have another drive with 300GB, move the transaction log to the other drive.

Odds are that is your issue like the post from "mezdem" stated.

Also, this will help too:
Read up on the effects, but go into DB properties -> options and set the "Recovery Model" to simple.

mezdem

ok can you tell us are you looking after the db or is the dba team?

If you have no space to backup either then you can alter to if you alter to 'simple' recovery, then perform a shrinkfile.

I have simple on for NPM then the log file automatically shrinks itself - depending on tranaction lengths it should never grow to big..except during large upgrades!.

**Choose your recovery model carefully, if you dont need point in time recovery dont use full..use batch or simple!

Delgee

Thanks guys.

I changed the recovery model to simple and shrinked the file. We have plenty of storage but I thought the actual db is very small and how can the log file be that big.

Delgee