shrink database

Question

Post reply

shrink database

abr_mah

SSC Enthusiast

Points: 191
More actions
July 29, 2009 at 3:05 am

#137033

dear all,
i have grate problem and i need your help.
- i have database its size became 28 Gigabyte the size if DB.mdf is 1.5 Gigabyte , and the size of DB_log.ldf is 26.5 Gigabyte ,
after i shrink it it became 26 Gigabyte .
the size of log file is very big and i want to shrink it .
please any help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chand00 SSCertifiable Points: 6466 More actions · Answer 1

Hi,

What is the recovery model of your database. Are you taking log backups regularly if it is in full recovery model

[font="Verdana"]Thanks
Chandra Mohan[/font]

abr_mah SSC Enthusiast Points: 191 More actions · Answer 2

hi thanks for your reply ,

but i can't understand what do you mean, i'm not DBA ,

i'm adeveloper and want to copy DB from server to attach it on another server ,so i found its size very big and i want to shrink it ,

informs you that i use the code

---

dbcc checktable (syslogs)

dump tran with no_log

dbcc checktable (syslogs)

dbcc perflog

--

and

DUMP TRANSACTION [database_name] WITH NO_LOG

--------------

and the size of DB became small 1.5 Gigabyte

next i try to do this again but the database not attahced to new server and get error

this what i do

vinod.km SSC Eights! Points: 971 More actions · Answer 3

---Below command will solve ur problem

BACKUP LOG WITH TRUNCATE_ONLY

GO

DBCC SHRINKFILE ()

ps. One Orange Chip Points: 29422 More actions · Answer 4

vinod.km (7/29/2009)
---Below command will solve ur problem
BACKUP LOG WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE ()

Remember that truncating a log will break the log chain.

Also shrinkfile will require the name of ldf file.

dbcc shrinkfile(myLogFile, 10)

Here myLogFile is the logical name of the log file and after running this, the log will be reduced to 10 MB in size.

Take a full backup after this to start a new chain.

Pradeep Singh

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

Since you don't need the database in full recovery model, I would change the recovery model to simple, then shrink the log file. If you don't change the recovery model - you are going to find the transaction log will grow again, unless you implement regular transaction log backups.

Review the article I link to in my signature about managing transaction logs.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs