Shrink DB in SQL 2005

Question

SQL 2005 DB restored in express edition.

MDF file size is 85 MB and the LDF file size is 3 GB

Through Microsoft SQL Server Management Studio Express, I shrink the database.

But there is a small variation in the size. How can I shrink the database effectively ?

Answer 1

SQL 2005 DB restored in express edition.

MDF file size is 85 MB and the LDF file size is 3 GB

Through Microsoft SQL Server Management Studio Express, I shrink the database.

But there is a small variation in the size. How can I shrink the database effectively ?

shrinking the database effects only the MDF file which is already only 85MB and probably won't shrink much.

you've to shrink the transaction log file by using Task -> Shrink database files -> select log file.

eventually you need to take transaction log backup first.

instead of SSMS you can use T-SQL statement DBCC SHRINKFILE instead:

http://msdn.microsoft.com/de-de/library/ms189493.aspx

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Hi,

Shrinking a file can cause big fragmentation which hurts performance. As you data file is of 85 MB, I think you should skip doing anything on it.

For Log file, if you are not taking log backups, then you can switch recovery mode of database to SIMPLE. Now you can use the

DBCC SHRINKFILE(LogcalLogFilename, TRUNCATEONLY) 

To know logical file names, you can run sp_helpfile against database.

---

- Chintak (My Blog)

Answer 3

>> you've to shrink the transaction log file by using Task -> Shrink database files -> select log file.

I already done this step, there is no change

Answer 4

Hi,

Can you please let us know the output of below query for your database

select log_reuse_wait_desc from sys.databases where name ='YourDBName'

---

- Chintak (My Blog)

Answer 5

Hi,

Can you please let us know the output of below query for your database

select log_reuse_wait_desc from sys.databases where name ='YourDBName'

---

- Chintak (My Blog)

Run your script and the output is

 LOG_BACKUP

Answer 6

If you don't want to take log backup then you can change the recovery model to simple and then you shoudl be able to shrink.

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Answer 7

Hi   Balmukund

>> If you don't want to take log backup then you can change the recovery model to simple and then you shoudl be able to shrink.

How can I change the recovery model to simple ?

Answer 8

Hi   Balmukund

>> If you don't want to take log backup then you can change the recovery model to simple and then you shoudl be able to shrink.

How can I change the recovery model to simple ?

ALTER DATABASE [Database Name] SET RECOVERY SIMPLE WITH NO_WAIT;

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Answer 9

If you want help, you can also ping me at blakhani (at) koolmail (dot) in on MSN messenger.

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Answer 10

check my blog for best practice of shrinking a log file

http://sqlservr.blog.com/2012/06/26/best-practice-to-shrink-in-logldf-file-in-sql-server/

if you are using it in test environment then

set the database in simple recovery mode using

ALTER DATABASE [dbname] SET RECOVERY SIMPLE WITH NO_WAIT

to shrink the log file size

USE [attach]
GO
DBCC SHRINKFILE (N'dbname_log' , 0)
GO

replace dbname_log file name with the name of your log file,

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 11

>> If you don't want to take log backup then you can change the recovery model to simple and then you shoudl be able to shrink.

How can I change the recovery model to simple ?

Hi   Balmukund

>> ALTER DATABASE [Database Name] SET RECOVERY SIMPLE WITH NO_WAIT;

Error when running this script

Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database DBName or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Answer 12

hi ssa2010

you need to replace [Database Name] with the name of your database.

or

check that you've the permission to change the Recovery Model of the database.

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 13

Hi Daniel_Steiner

>> check that you've the permission to change the Recovery Model of the database.

I logged in as 'sa'