Database memory usage issue 1

[wp1]

I have a sql server 2008 database called Mish. It has crashed twice in the last 2 months and recovered both times fine. It said as of yesterday when looked at size in db properties, that it was 6.3GB in size and only had 10MB worth of storage left. First question is how is the size selected? Where is this configurable parameter set?

No big deal i thought, i'll just clear up some records to open up room. Wrote a delete query after storing a table as a CSV file. This cleared up 40MB. I went through and wrote down all tables with there index and data sizes and found it to be ~125MB of space that could be cleared.. There is still 6 G worth of memory used according to the properties. There are views but they show 0 memory use.

Any thoughts on what's going on? If the 6G of memory is not data being stored, then what is it? Is there a way to clean this up?

 

[wp1]

PS I have over 50 GB of memory to on the virtual server. Its not a drive capacity issue.

 

[gmmastros]

It's hard to say what your actual problem is. Please run the query below and post the results here:

select * From [sys].[database_files]

I am most interested in knowing: size, max_size, growth, and is_percent_growth.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wp1]

i just found that it was the log file. How do i safely purge this thing

 

[wp1]

also, not sure why it would crash over a big log file.

over all size is 6.3gb
data size is under 200mb. Its a transactional database used mainly for static referencing. There is some logging, but most of that is handled on another DB

don't know how to tell growth rate. Can say its taken almost 3 years to get to its present size.

 

[gmmastros]

Before I answer that, can you run this and post the results?

Select DatabasePropertyEx(db_name(), 'Recovery')

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wp1]

Msg 195, Level 15, State 10, Line 2
'Mishawaka' is not a recognized built-in function name.

 

[gmmastros]

The log file is probably not what you think it is.

SQL Server log files exist to prevent corruption in your data. Whenever you insert, update, or delete data, the log file is used.

Generally, the process is this... You issue a SQL command to change data. SQL Server (behind the scenes) will write to the log file that it is about to make changes. It effectively copies the original data to the log file. It then updates the actual data, and then finally marks (in the log file) that the update is complete. If the server loses power during this process, SQL server will detect it when it is restarted. It would notice an incomplete transaction and effectively do a rollback where it copies the original data from the transaction log back in to the original data file so that your data is exactly as it was prior to losing power.

There are 3 recovery models you can use with SQL server. For small, read-only, non-mission critical databases, the recovery model is usually set to SIMPLE. With a simple recovery model, when a transaction completes, the space in the log file that was used for the transaction is re-used for the next transaction. If you ever need to recover the data, your only option is to restore the last full backup.

With a FULL recovery model, the transaction data is kept in the log file until you back up the log file. In this scenario, most people will take a full backup daily, and then transaction log backups with a shorter interval (ex: every 5 minutes). Since it's only 5 minutes, the transaction log backups will be very small. However, with the transaction log backups you can effectively restore to any increment of 5 minutes.

If your database is in full recovery mode and you've never taken a log backup, this could explain why you have 6,000 MB of log file with only 200 MB of real data.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

try this:

Select recovery_model_desc from sys.databases Where name = db_name()

When you run this in SQL server management studio, please make sure that you run it in the correct database?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wp1]

its Full. So a backup will reset the log file?

 

[wp1]

i take that back, when i went to database properties, it said FULL in the drop down list, BUT running your query returned SIMPLE

 

[gmmastros]

Since your transaction log is so large, you clearly haven't been backing it up regularly. You should really ask yourself... do you want FULL recovery or SIMPLE recovery. My guess is that SIMPLE recovery is what you really want.

If so, change it to simple and back up the database. This should reduce the size of the log file. If it doesn't, then right click on the database, click Tasks -> click Shrink -> Database. Since your database is small, you should also select "Reorganize files before releasing space".

If you want full recovery, then you should do a backup of the data file, and then do a backup of the log file.

In SQL Server Management studio, right click on the database, click tasks -> backup. For "BackupType", select full, and backup the DB. Then go back in and change "Backup Type" to Transaction Log, then click on "Options" (top left corner). Make sure it says "Truncate the transaction log", then click OK.

Since the transaction log file is 6GB, it may take a while to complete. When it is done. your transaction log file should be MUCH smaller.

Having your database in full recovery mode means that you should schedule transaction log backups to occur on a regular basis. This will keep your transaction log at a manageable size.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom