How to shrink database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to shrink database

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-04-28 : 11:10:49

HI, i am using sqlserver 2005 server. I have one database which having .mdf size is 23 GB and .ldf file size is 21 GB. I want to shrink this database and remove all log data. i tried option by right clicking database --> tasks --> Shrink -->Database /Files. I tried these options. But the size is not reducing..How to reduce the log file size ?

G. Satish

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-28 : 11:30:03

What recovery model are you using? Your ldf files will grow very large with a full recovery model.

Have you taken a full backup yet? The log files will get truncated only after a full backup shrinking them won't do anything before.

NB: I'm 100% wrong about this - as Gail says it's the log backups that will truncate your ldf files in full recovery

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-04-28 : 14:18:06

If you're in full recovery, you need to be running log backups as full backups do not truncate the transaction log.
Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]

--
Gail Shaw
SQL Server MVP

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-04-29 : 02:08:11

Hi, i dont want log files data. but i want to minimize this log files and also in future i dont want these files to increase this size? what to do?

quote:
Originally posted by Transact Charlie

What recovery model are you using? Your ldf files will grow very large with a full recovery model.

Have you taken a full backup yet? The log files will get truncated only after a full backup shrinking them won't do anything before.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

G. Satish

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-04-29 : 02:35:10

Did you read the article I linked?

--
Gail Shaw
SQL Server MVP

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-04-29 : 09:24:26

thank you. I have set recovery model from full to simple. Now the log file size is in MB.