PranasLu's Blog

2010-09-09

MSSQL DB shrink files

Filed under: Programming — Tags: , — pranaslu @ 17:23

By default Recovery model is set to Full.

image

Check the database properties in enterprise manager.
On the options tab, look at the "recovery" option.
If you have it set to "full," then the log will never shrink by itself, as you have implicity said somthing like "I want to use the log to resore a database."
If you have it set to "Bulk-logged" then you are imlying "Don’t shrink the log untill I’ve backed it up."
If you have it set to "Simple" then the log will auto shrink as each transaction completes. Note: when using the simple recorory method, only an MDF backup can recorve a database, and once transactions complete, you got no chance of rolling them back.

If Recovery mode is set to Full – Dbcc Shrink… doesn’t work. You probably need to backup database.

Set Recovery model to Simple and then run these command:

DECLARE @dbName varchar(50)
DECLARE @dbNameLOG varchar(50)
set @dbName = DB_NAME()
set @dbNameLOG = DB_NAME() + ‘_LOG’
DBCC ShrinkDatabase(dbName)
DBCC ShrinkFile(dbName)
DBCC ShrinkFile(dbNameLOG)

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: