SQL 2008 database growing to fast after SQL 2005 migration

I migrated from SQL 2005 to SQL 2008 a couple of weeks ago and my database has gone from 78gigs to 270gigs. Please help!!
Who is Participating?
Daniel_PLConnect With a Mentor DB Expert/ArchitectCommented:
Why do you mean by fixing backups?
You can backup to the same file using WITH INIT clause, e.g.:
BACKUP DATABASE some_db_name_here TO DISK='D:\BACKUP\some_db_name_here.bak'

You can backup to new file each time by adding date stamp at the and of file, next deleting old backup file:

Finally you can use already created scripts for backup, and even more - by Ola Hallegren:
Daniel_PLDB Expert/ArchitectCommented:
Can you run this query:

USE <your db name>
SELECT	mf.name,mf.file_id AS fileid,FILEPROPERTY(mf.name,'Spaceused')*8/1024 AS size_MB,
        mf.size * 8 AS [Initial SIZE IN KB],CASE mf.max_size WHEN -1 THEN 'Unlimited' END AS maxsize, (mf.growth *8) AS growth_KB,
        ((mf.size * 8)-(FILEPROPERTY(mf.name,'Spaceused')*8))/1024 AS Free_Space_MB
        FROM	sys.master_files AS mf
JOIN  sys.database_files AS df ON mf.name=df.name
WHERE	mf.database_id = DB_ID()

Open in new window

boxer327Author Commented:
The database is currently in production. I ran the query and got the following message back:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.

 I'm a SQL beginner. Please provide more details. Thanks
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Daniel_PLDB Expert/ArchitectCommented:
Please don't use < > brackets, just use your database name instead.
please verify what is the t-log file sizes

use the below code

dbcc sqlperf(logspace)

this query gives the t-log file size if it is grown you need to shrink the t-log file

dbcc shrinkfile(2,1)

please let me know if you need any more clarifications

boxer327Author Commented:
The database is actually only 41gigs, but the full backup for the Database is now over 310gigs. How do i fix this?
Daniel_PLDB Expert/ArchitectCommented:
You are probably backing up your database with no init clause - it means each new backup is appended to your backup file. Change it to WITH INIT and every new backup will overwrite existing.
Daniel_PLDB Expert/ArchitectCommented:
Run following query to view contents of your backup file:
RESTORE HEADERONLY FROM DISK='path to your backup file'  
boxer327Author Commented:
Your correct how do I fix the backups?
boxer327Author Commented:
You've corrected my backup problem. Thank you
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.

All Courses

From novice to tech pro — start learning today.