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

General SQL Server Forums

New to SQL Server Administration

shrink database

Author

Topic

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2010-06-10 : 10:56:54

I am unable to shrink data and log files in dev/test server.

I am using below query
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

MyDB
data file 763 MB By 1 MB, unrestricted growth
Log file 5 MB By 10 percent, restricted growth to 2097152 MB

D:Mydb.mdf 781312KB
Mydb.ldf 5000 KB

Kristen
Test

22859 Posts

Posted - 2010-06-10 : 11:24:26

data file 763 MB
Log file 5 MB

or

D:Mydb.mdf 781312MB
Mydb.ldf 5000 MB

which is correct?

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2010-06-10 : 11:34:10

MyDB
data file 763 MB By 1 MB, unrestricted growth
Log file 5 MB By 10 percent, restricted growth to 2097152 MB

D:Mydb.mdf 781312KB
Mydb.ldf 5000 KB

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-10 : 11:46:28

Do you get an error or message when you try to shrink the files?

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2010-06-10 : 11:51:07

no error, but the mdf and ldf size remains same in drive.

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2010-06-10 : 11:56:36

sp_spaceused details

database_name database_size unallocated space
mydb 767.88 MB 0.08 MB

reserved data index_size unused
781232 KB 486760 KB 286096 KB 8376 KB

Kristen
Test

22859 Posts

Posted - 2010-06-10 : 11:57:16

5MB is pretty small for LDF file - maybe there are only 4 VLFs and thus it can't shrink further?

What does

DBCC LOGINFO()

give?

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2010-06-10 : 12:10:50

FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
2 2555904 8192 652 0 128 0
2 2555904 2564096 653 2 128 0

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2010-06-10 : 12:20:08

any body guide me on how to shrink mdf files. We have around 20 databases in test server, mdf files are huge and occupied space on drive. So need to release space.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-10 : 13:14:07

You have to specify the data file name or ID in order to shrink it. Your posted code only shrinks the log file.

Kristen
Test

22859 Posts

Posted - 2010-06-10 : 13:25:53

mdf files are huge "

I'm not seeing that, or am I missing something?

770MB MDF file with 8MB free and 5MB LDF file. You probably could squeeze those a bit, but I very much doubt that you don't have a transaction that will need that much LDF space, and any Index Rebuild will need 8MB, probably more, on the MDF.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources