Understanding data size vs log size in SQL Database

My client has a SQL Server 2000 which we are trying to upgrade. In the meantime, the Server is running out of space and I am trying to understand the size of the database.

When I check the size
select name ,      (size*8)/(1024.) AS SizeInMBs
from master.dbo.sysaltfiles

The data is 900 MB and the Log is 110 GB. I have these questions:

So what is the size of that database? Should I consider the Log size?
Can the log file be truncated without risk of data loss?
Will deleting old records from the Database help reduce the log size?
The recovery model is FULL. Will we lose any data if I change it to SIMPLE? If not, once I change it to simple, will it reduce the current log size or just avoid it from growing much going further?
Angel02Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
The data file is a more true representation of the size of the database.  The log file just tells you how active the database is depending on how often it is backed up.  It looks to me that the database is in FULL RECOVERY MODEL but it has never been backed up.  A 110GB log file for a 900MB data file is pretty ridiculous unless you have an EXTREMELY busy database.

What is the recovery model of your database?

I would recommend that you run the following command to evaluate how much of your log files are actually being utilized.  Did it just grow to a ridiculous size at one point due to a bulk insert/delete and was never corrected?

DBCC SQLPERF(LOGSPACE)
0
Brian CroweDatabase AdministratorCommented:
Sorry, apparently i missed the last couple of lines of your question.

Changing to simple will not harm your data or reduce the size of the log file.  You would need to evaluate which model is correct for your situation depending on its usage and criticality.

You can run...

SHRINKFILE(<filename>, <size in mb>)

to reduce the size of the file down to what it is currently using.
0
Brian CroweDatabase AdministratorCommented:
If you change the recovery model to SIMPLE your log file shouldn't grow very large at all assuming you don't have a bunch of longstanding transactions.  If you don't need the ability to restore your database within small tolerances like 15 or 30 minutes then you might be fine with SIMPLE recovery model and scheduling a weekly full backup and daily differential backups.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Angel02Author Commented:
Thank you for the reply.
I ran DBCC SQLPERF(LOGSPACE) and found this

Database Name      Log Size (MB)       Log Space Used(%)
<dbname>                 110625.39                99.999969      

So it means almost all the log space is being used? Will the ShrinkFile make a difference now?
From what you said, I think SIMPLE recovery model will be good.

So the space if all being used up by the Log file. The data isn't really that much. So deleting any data will not make much difference in space, correct? Can you please confirm?
0
Brian CroweDatabase AdministratorCommented:
SHRINKFILE will do nothing in your case because it only frees up unused space.  When is the last time a transaction log backup or a full backup were performed on the database?  My guess is that it has been a while.  If you switch to SIMPLE recovery model and rerun the SQLPERF query you should see a drastic reduction in % used and SHRINKFILE would then help.

There is typically no need to delete data especially when you're only dealing with 110MB.  It sounds like someone set up a database in FULL RECOVERY a while back and didn't maintain it.  If that is the case then changing to SIMPLE sounds appropriate.

USE <your db name here>;
GO
ALTER DATABASE SET RECOVERY SIMPLE;
GO

disclaimer: I haven't worked with SQL 2000 in years so if you have any issues with the queries i put up let me know and we can find the equivalent for that version.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian CroweDatabase AdministratorCommented:
You can run this against your database to see backup history

SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO
0
Angel02Author Commented:
I am having trouble running the query to find the back up history, I believe due to SQL 2000. i am trying to find out from our IT person about how the back ups are performed on this Database. Can you please help me understand the relation between transaction log and back ups?

You said "It sounds like someone set up a database in FULL RECOVERY a while back and didn't maintain it. "
So how should a Database be maintained if it is in Full Recovery?

Thank you!
0
Angel02Author Commented:
Should I back up the transaction log and will it automatically truncate it?
0
Brian CroweDatabase AdministratorCommented:
The transaction log is just that it contains a list of database transactions that occur, inserts, updates, deletes, etc.  If you're in SIMPLE recovery then as soon as it is done with a transaction then it just "forgets" about it and writes over it with the next one so the file never gets very big.  If you are in FULL recovery then each transaction is recorded and saved.  When the file fills up then it allocates more space indefinitely.  To prevent the transaction log from growing out of control you have to perform either a full backup of the database or a transaction log.  When that happens it will essentially flag all of those transactions in the log as safe to delete and release the space inside the file for the next set of transactions following the backup.  Notice that I said it releases the space, it does not return it to the operating system so the file doesn't get any smaller it just uses less of the file internally.
0
Angel02Author Commented:
Thank you for such detailed explanation.
So I think I am going to change the recovery model to Simple, then ShrinkFile, and then change it back to FULL. Is that OK?

I wanted to back up the Log file before Shrink but I don't have 110 GB of space available for a back up. I can try backing up on an external drive but may be I skip the back up and continue with
- Change to Simple
- ShrinkFile
- Change back to FULL
- Then set up a transaction log back up

Does that sound right?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
No, don't change to Simple. You should perform a transaction log backup and then check the data and log files again to see the difference.
After that it's better you perform a FULL backup and only then you should shrink the transaction log file to recover disk space.
0
Scott PletcherSenior DBACommented:
No, do not perform a log backup.  It's pointless, and it will take a very, very long time.  You'd never want to apply (restore) a 110GB log anyway, even assuming you could somehow find the exact full database backup to restore first to be able use a log backup at all.

Put the db in SIMPLE mode.
CHECKPOINT the db, just in case.
Shrink the log file as low as it will go, then regrow it in one (or two) allocation(s).
If you want, you can put the db back into FULL mode.  If you do, start taking log backups from then on.
Take a full backup of the db.
0
Angel02Author Commented:
I ran the query Brian Crowe gave me to see the back up history and got the following results

<dbname>      <path>      872 MB      376 Seconds      6/8/15 6:00 PM      30895000000015900000.00      30895000000016600000.00      Full      <Servername>

<dbname>      {7767490C-E3FD-4CA5-B8F3-58CDE1C30CAE}6      0 MB      11 Seconds      6/7/15 11:07 PM      30883000000105500000.00      30883000000106300000.00      Full      <Servername>

Type is D in the back ups. Does it mean a Full back up including the transaction log or just the database back up?
0
Angel02Author Commented:
When I check the database maintenance plan, I see a "Complete back up" tab which runs at 6 pm every day. Then I see Transaction log tab which is disabled.
When I check the complete back up file dbname.bak, it's size is 800 MB. Does that mean complete back does not include transaction log?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Does that mean complete back does not include transaction log?
That's why I recommended you to perform a transaction log backup. I know 110GB is a lot but if you want to restore to a point in time you'll need it.
It's up to you to decide if you can lose that information or not.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.