DBCC Shirnkfile command

Question

DBCC Shirnkfile command

SQL Galaxy

One Orange Chip

Points: 29472
More actions
December 24, 2010 at 10:10 am

#104020

I want shirnk this file and reduce 150GB, 120GB due to disk space issues and backup is going to fail. I don't have any option or 3rd party tools for how to reduce the file size & compress backup?
Datafile1 - 189670400 (189GB)
Datafile2 - 149670822 (149GB)
I ran this DBCC SHIRNKFILE (DATAFILE1, 150225) this command but tooks lot of time to finished. please tell me it there alternative solution for shirnk data file and quickly.
Thanks for your help.
ananda

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

How big is the database in total (file size) and how big is the data (sp_spaceused)

Shrink is not a solution to low disk space, shrink doesn't compress data and it won't change backup size. If your database is growing, you're going to eventually need more drive space.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Thanks GilaMonster for reply

sp_spaceused, its outcomes below

Database name -BRIBS

Database_size - 330072.75 MB

Unallocated Sapce - 22.71 MB

Reserved - 337968424 KB

data - 337237296 KB

Index_size 314768 KB

unused 416360 KB

here attached xls sheet for each table size, rows, index size .etc.

Mainly this database storing Image data and details, I asking to vendor upgrade SQL 2008 (IN place), but I am thinking upgrade process will going to failed due to image strong that table.

SQL 2008 introduce Filestream object, so all image can store externaly not in database.

Please tell me what action should be follow for reduce database size?

Thanks

ananda

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

There's only 22MB of unused space in that database. Shrink is going to do nothing, as I said it's not compression, it's just releasing unused space to the OS.

To reduce the size of that database you're going to delete some data. If you can't do that, you'll need to get more drive space.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

ananda.murugesan (12/24/2010)
Mainly this database storing Image data and details, I asking to vendor upgrade SQL 2008 (IN place), but I am thinking upgrade process will going to failed due to image strong that table.

Why would the upgrade fail? The image data type is deprecated in SQL 2008, not removed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 5

Thanks Gail for reply..

There some possibilites do that

1. Add more space in server - asking serveradmin team for increasing more free space.

2. Management not accept do delete the old data for reduce size.

3. To upgrade to SQL server 2008 ( one of the feature backup compression)

finally Let me talk to vendor, upgrade the SQL server 2008 with SP2. after tested with upgrade advisor tool and application.

Could you suggestion me, Is it possible taking backup to another remote server throu SQL 2000 maintenance plan? I tried but MAP drive is not coming to option of backup location in maintenance plan.

Remote server backup as below error

BackupDiskFile::CreateMedia: Backup device '\\Servername\d$\BACKUP\DB5.bak' failed to create. Operating system error = 5(Access is denied.).

Resolved those error.

SQL services should be running on domain account user. then only allows the remote network to storing backup file.

In this case whenever changed domain account password, at same time need to reset sql services also, please clarify.

Thanks

ananda

Meet George Jetson SSChampion Points: 11638 More actions · Answer 6

You said management would not accept removing older data. Is this "older" data used regularly? Or, can you remove it to a new database called "DRIBS_Archive" and then take a copy of that database to CD/DVD?

Chris Powell

George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.

vinodsy123 SSC Rookie Points: 32 More actions · Answer 7

I think if u can detach the database then copy the .mdf & .ldf file in a disk if it won't work then use xcopy through command prompt.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 8

Hi,

How can detach and xcopy .mdf and Ldf file as you said, this server is production and live server. there is no downtime

If copy those files minimum 8 hrs for taking these file (340 GB).

So I comes one sloution, for taking Differential backup daily at every 2 hrs. weekly full backup.

For restore first full backup with no recovery then apply all differential backup day wise.

Please give me differntial backup script ( for user database)

Thanks for your help

ananda

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

ananda.murugesan (12/28/2010)
For restore first full backup with no recovery then apply all differential backup day wise.

Apply last differential, not all of them.

Please give me differntial backup script ( for user database)

Look up BACKUP DATABASE in SQL's books online. The syntax is described there and there should be an example too.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass