Need work around to lower initial size and shrink database

Question

Post reply

Need work around to lower initial size and shrink database

Viewing 15 posts - 16 through 30 (of 39 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

OK, all of the people saying that they are in this situation, please confirm that you have done all of the following:

1) checked that your are on SQL 2005

2) checked that the DB's compatibility level is 90

3) Done a full backup first

4) Then done a SHRINKFILE on the mdf

-- With REORG

5) Then done a SHRINKDATABASE

???

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

kenken SSC Veteran Points: 204 More actions · Answer 2

Yes ...

Below is the result os sp_spaceused

Database_name Database Size Unallocated space

XXXXX_Test 166720.63 MB 147340.23 MB

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Well, I would take it to Microsoft then.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

cbondeson SSC Journeyman Points: 81 More actions · Answer 4

After much work this fixed the issue

use tempdb

dbcc shrinkfile ('tempdev',7)

dbcc shrinkfile ('templog',7)

USE MASTER

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev,

SIZE=2000 MB,

FILENAME = 'D:\MSSQL.1\MSSQL\Data\tempdb.mdf')

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog,

SIZE = 1000 MB,

FILENAME = 'D:\MSSQL.1\MSSQL\Data\templog.ldf')

GO

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Yeah, my recollection is that occasionally, I have had to go through this cycle a couple of times over 24 or 48 hours before it worked. Not sure why, but I had suspected that it was running in the background but was stalled because it was being blocked by locks held on resources from current/on-going activity. Just my guess though.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

kenken SSC Veteran Points: 204 More actions · Answer 6

thanks for the solution ...

why this doesn't take effect still in my case?

EA RTW SSC Rookie Points: 45 More actions · Answer 7

Just run dbcc shrinkfile(file_id, size) on the database you want to amend.

Darren Myher Old Hand Points: 397 More actions · Answer 8

Eben (4/29/2009)
Just run dbcc shrinkfile(file_id, size) on the database you want to amend.

To everyone reading this thread: the point is that the command above and all those like it do not work.

I think the reason is that the databases upon which we've been trying to run this command are SQL 2000 databases that were mounted on SQL2005 servers and are still in mode 80 compatibiliy.

My understanding from reading the thread is that if the database is changed to mode 90 compatibility that commands like the one suggested above should start to work. But I have yet to have an opportunity to revisit this to try it out.

EA RTW SSC Rookie Points: 45 More actions · Answer 9

Point taken Darren as I agree there is no need to add to confusion.

However I have restored some databases from SQL 7 and 2000 into 2005 and run the dbcc shrinkfile command and this has worked for compatibility modes 70, 80 and 90.

jiri.holubik SSC Rookie Points: 39 More actions · Answer 10

I had the same issue with 380GB MDF file, which was empty. I tried to run DBCC SHRINKFILE with all possible options, but nothing worked. Then I used following:

1. run ALTER DATABASE XXX MODIFY FILE (Name = N'your MDF file', SIZE=bigger then actual size)

2. run DBCC SHRINKFILE('your MDF file')

... and it worked for me, good luck 🙂

Jiri

Sanjay Rohra Ten Centuries Points: 1158 More actions · Answer 11

I had a similar requirement. was running out of disk space, hence had to compress the log files. After taking the backup of the log files and truncating them, I couldn't shrink the log files to size (100 MB)smaller than their initial size (in GB's). Again I took the backup of the log files and then tried to Shrink the log files. And to my surprise, this time I was successfull with the SHRINK operation.

MSSQL dude Valued Member Points: 68 More actions · Answer 12

Had an identical problem with my SQL2000 database - not able to SHRINK the transaction log file that grew to 10GB. Had to switch the database to simple recovery first. That did the trick.

ALTER DATABASE [BIGDB] SET RECOVERY SIMPLE

USE [BIGDB] DBCC SHRINKFILE (N'BIGDB_Log', 512)

ALTER DATABASE [BIGDB] SET RECOVERY FULL

kenjievan Grasshopper Points: 21 More actions · Answer 13

HI all, if you already purge the db, and cannot shrink because of db file initial size, is a bug, so you must restart the sql service, and will be able to shrink the file

SQLRocker SSCrazy Points: 2217 More actions · Answer 14

Shrinkfile (can use GUI - release unused space) - t-log backup - Shrinkfile (can use GUI - release unused space)

Deeps001 SSC Enthusiast Points: 101 More actions · Answer 15

I have tried shrking the database MDf file using all the possible methods mentioned in this thread. I was finally happy to see that the initial Size reduced and space released to OS. After the Shrink, I decided to rebuild the indexes. After the indexes are rebuilt , I am baffled to see that the Initial size has come back to what it initially used to be (before the shrink) Does rebuilding the indexes change the Inital DB size for the mdf file ?