SQL Server Database Size Reduction Query

Question

I have a SQL Server 2008 Express Database which is 7.8 GB in size

DataFile 1.2 GB
LogFile 6.6 GB

Recovery Model = Full
Auto Shrink = False

On a Live database, what is the best way to reduce the size of this database?

Read up on Recovery Model Overview and that should help you understand why your database has grown so large. Unless you're just low on disk space, I would handle the log appropriately (regular tlog backups or change recovery model) but not shrink it unless it meets some criteria — billinkc
– billinkc, Commented Sep 7, 2011 at 2:16
On a Live database, can I change the recovery model from FULL to Simple. I can set a Backup job that takes backup of the database everyday? What will happen to the log file when I switch from FULL to Simple? — Refti
– Refti, Commented Sep 7, 2011 at 2:23
Yes you can. The syntax would be ALTER DATABASE [MY_DATABASE] SET RECOVERY SIMPLE WITH NO_WAIT and you can use windows task scheduler to create a full backup on whatever schedule makes sense for your application. Database backup code BACKUP DATABASE [MY_DATABASE] TO DISK = N'C:\mydatabase.bak' WITH NOFORMAT, NOINIT, NAME = N'My Database backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 But, don't try any of this in production, yet. The backup is fine, it won't hurt you. Before change anything production related, try them in a dev/test environment first and evaluate the results. — billinkc
– billinkc, Commented Sep 7, 2011 at 12:18

Steven Berkovitz · Accepted Answer · 2011-09-07 02:03:51Z

1

Before you can shrink a database running in full recovery model, you must backup the transaction log. So the procedure is to run a transaction log backup, and then shrink the log file.

If you have never performed a transaction log backup then you will continue to suffer from run-away log files and shrinking it will only be a band-aid solution.

answered Sep 7, 2011 at 2:03

Steven Berkovitz

7814 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Refti Over a year ago

Steven, how can I do a transaction log back up? Also I have a limit of 10 GB which I will running out on very soon. So I probably won't need that transaction log which is 7.8 GB. Btw, is it normal to have log files that big. Does it effect performance?

Steven Berkovitz Over a year ago

From the Sql Management Studio UI, Right click your DB, choose Tasks -> Backup. Set Backup Type to "Transaction Log". Regarding if its normal to have a log that big, that ultimately depends on the DB, but in your case its surely due to a runaway transaction log, so no its not normal

Amit Gajjar · Accepted Answer · 2012-12-13 12:10:58Z

1

You can also identify unused tables and remove those tables(if there is)
you can create Archive database that will stored some old unused data
you can normalize your database more to reduce table size.

hope this information helps you.

answered Dec 13, 2012 at 12:10

Amit Gajjar

515 bronze badges

Collectives™ on Stack Overflow

SQL Server Database Size Reduction Query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related