Understanding data size vs log size in SQL Database

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)

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.

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.

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?

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

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!

Should I back up the transaction log and will it automatically truncate it?

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.

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?

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.

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?

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?