MDF file too big 45 GB: how to split?

[chaoma]

Hello,

I have a single mdf/ldf file for my database. Now it grows to 45 GB. About once a week, I got log file is full message. I tried to shrink database, but it takes too long. Is there anyway to split the file into muliple files? I don't need to backup my tables because the data get refresh every week.

Thank you for any assistance.

 

[SQLDenis]

well a backup will shrink your log


run a truncate log Logname with truncate_only then shrink the DB

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQL Server Programming Hacks

 

[SQLDenis]

of course truncate log Logname with truncate_only then

needs to be

backup log DBNAME with truncate_only

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQL Server Programming Hacks

 

[chaoma]

I'll try:

BACKUP databasename
WITH TRUNCATE_ONLY

How do I split this file into smaller files (instead of 45GB)?

Thanks.

 

[SQLSister]

Look at the relative sizes of your log files and your database files. Is the log significantly larger that the database? Logs grow until they take up the whole hard drive unless you back them up or truncate them. Since you say you don't need to be able to backup your tables or restore from the transaction logs, you certainly don't need to keep the log large. Denis told you what to do. Once you have done this once and shrunk the database once, then you should set this database to simple recovery mode. Monitor the log growth after you do this and if need be, schedule a job to periodically truncate it.

It is unlikely that you need to partition your database file. Once you have control over the log, likely your space problems will disappear.

Note to others reading this, this advice is only for people with a database that they are doing a full refresh of and not for those who have database that are transaction oriented.

"NOTHING is more important in a database than integrity." ESquared

 

[chaoma]

My log file is much smaller than MDF file. It has always been set to "simple". I always schedule to have it shrink every week. But I'll try to truncate the log file to see how it will go. I should know by next week.

Thank you for your assistance.