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!!
boxer327Asked:
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:
http://ola.hallengren.com/
0
 
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

0
 
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
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Daniel_PLDB Expert/ArchitectCommented:
Please don't use < > brackets, just use your database name instead.
0
 
Umesh_MadapCommented:
Hi
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

0
 
boxer327Author Commented:
The database is actually only 41gigs, but the full backup for the Database is now over 310gigs. How do i fix this?
0
 
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.
0
 
Daniel_PLDB Expert/ArchitectCommented:
Run following query to view contents of your backup file:
RESTORE HEADERONLY FROM DISK='path to your backup file'  
0
 
boxer327Author Commented:
Your correct how do I fix the backups?
0
 
boxer327Author Commented:
You've corrected my backup problem. Thank you
0
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.