Help For Shrinking Database

Jun 20, 2006

Hi guys.

I have a huge database file. When I run sp_spaceused this are the
results:

db_size 1337,31 Mb
unallocated 14,62 Mb
reserved 1088456 Kb
data 258992 Kb
index_size 6224 Kb
unused 823240 Kb

Now, when I run dbcc shrinkfile(database_name, 50) or whatever value of
percent, it says there is not enough free space in db.

Please advice how to shrink the size of that file and why is it so big?

Thanks in advance

Zvonko

View 3 Replies


ADVERTISEMENT

Shrinking Database

Apr 13, 2008

Hi all,I've deleted a lot of albums but the size of personal.mdf isn't shrinking - how do i go about acheiving this? I've tried to shrink it in sql management express but the file is read only..
please help!
thanks

View 5 Replies View Related

Shrinking Database

Jan 8, 2001

I have a 1.2 gig db which has truncate checkpoint set and over 850 meg of free
space. WHen I shrink it through either DBCC or EM I see no change. ANy
ideas?
Thanks

View 2 Replies View Related

Shrinking Of Database

Dec 30, 1999

i havae a database size of 1000mb with used as 507.50 and unused as 492.50. i tried with commands like dbcc shrink file and dbcc shrink database but i could not get the result as what i need. I need to shrink in such a way that it should have 25% of the used space as free space. could any one help

View 4 Replies View Related

Notification After Shrinking Database

Mar 26, 2001

Hi All,

I am working on a client product that shrinks the database in MS SqlServer. After shrinking the database files, it does not send any email notification to the users.

My question is: Is there any way in SqlServer to sense that the database has been shrunk and send a notification to the users (may be through an Alert or a Job or something). Please let me know the steps to do that.

Thanks
Sri

View 1 Replies View Related

Shrinking Database Problem

Jun 4, 2003

I have a SQL 2000 database that I would like to shrink.

Recovery Model = Simple
Data file space allocated = 78 Gig
Space free = 41 Gig

Similar problems with Trans logs sometimes

As I understand it, I can't shrink the database below the allocated size. How do I get that space back ?

Any extra insights and explanations about this topic in general would be appreciated .... Thanks in advance.

View 3 Replies View Related

Error While Shrinking Database

Oct 13, 2005

Hi,

When I am trying to shrink a database, I got the following error.

Executed as user: NT AUTHORITYSYSTEM. ...he PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491894). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491893). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491892). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491891). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491890). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491889). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:490615). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8...

Please help me to resolve this issue

Thanks & Regards,
Eldho Abraham

View 7 Replies View Related

Shrinking Database Files

Jul 23, 2005

I have a database file LEAR_Index(yes, it hold index data) from a havehave recently removed a bunch of data.It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink thefile to lean 30-40Gb.I´ve been trying this:dbcc shrinkfile('LEAR_Index',40000)But to no apparent avail; the file did not shrink.I´ve tried using enterprise manager for this but it consistenlycrashes when performing this operation.#Any thoughs, idear as to what i might be doing wrong?

View 6 Replies View Related

Shrinking Database Log Used For Mirroring

Nov 22, 2006

Can anyone offer some advice on how to reduce the size of a databaselog. The database is mirrored on another server using the logs. Thanks

View 5 Replies View Related

Problem Shrinking A Database

Jul 20, 2005

Hi all.I'm having trouble shrinking a database. In short, using the SQL QueryAnalyzer,I enter the following commands:DBCC SHRINKDATABASE (database, 80)GOThe command returns the expected 'DBCC execution completed.' response, butthe databaseremains the same size when I run 'sp_helpdb database'. Am I missingsomething?Thanks in advance,Jaeger

View 2 Replies View Related

Shrinking The Master Database Logfile

Feb 9, 2001

Hello

Can anyone help, I have a master database on my server which has an extremely large log file size. I need to shrink this down and so I have tried using dbcc shrinkfile (master, 50, truncateonly) and dbcc shrinkdatabase although it does not seem to reduce in size

Please can anyone help

View 1 Replies View Related

Resizing/ Shrinking Database Devices

May 3, 1999

Is there a way to shrink database devices in SQL Server 6.5 Other than dropping and restoring?

View 1 Replies View Related

Will Database Shrinking Affect The Data?

Aug 6, 2004

Hi all,

does the shrinking of the database affect the data in the database, such as will it change the data, or deleting the data away?

Thanx

View 1 Replies View Related

SQL 2012 :: Shrinking Database For Dev Environment?

Nov 26, 2014

I've read lots about why you shouldn't normally shrink databases in posts such as this:

[URL]

But we have a situation where we are required to copy the live db to various non production environments for testing. Part of this process involves truncating a number of tables with masses of blob data. So we're freeing up quite a lot of space. The question is how to reclaim this? The database is peculiar in that it's got no clustered indexes so I can't rebuild indexes on another filegroup with drop to move and rebuild.

I've tried dbcc shrinkfile specifying a size. I've tried to shrink the file in increments. The problem is I'm just not getting much space released. I get maybe 2-3%. I suspect this is because we're dealing with heaps with some tables that have sparsely populated blob / image data.

Is there an alternative to shrinking? Should I recreate all the db objects in a new database? It doesn't matter if the process takes a while or if it has to be done manually.

View 7 Replies View Related

Shrinking Database Files In Production

May 15, 2008

Hi,

We have a database in production which has free space about 200 GB in Data files and Index files, I want to shrink Data files and Index files.If I do incremental shrink in daytime does it hurt the performance of the database or please advise what is the best practice.

thanks

View 5 Replies View Related

Shrinking SQL2000 Database W 20 Datafiles.

Jul 23, 2005

Hello,I am trying to clean up a database I inherited.I have an 80GB SQL 2000 database with 20 datafiles each 4096 in size. Ihave been able to remove unneeded data and am now trying to clean up.If I do a Shrink on each datafile would able to recover on average 2gbout of 4g, however I would prefer to have 10 full datafiles and 10empty. (or better yet 5 full 8GB datafiles and 15 empty)Can someone point me in the right direction on how to move the dataaround so that dont have 20 partially filled datafiles?I have noticed that I can shrink a single file and use the "empty thefile option (and move data to other files in the group)." option. Ihave already done this to the last 2 datafiles as a test but not surehow to do this on a large scale. I have also set the 1st 10 datafilesto be able to grow to 8 GB.For lack of a better way to say this, Is there a way to defrag orreorganize the data ables so everything "moves to the front".BTW, I have already run a maintenance plan to reorganize the data andindex pages.

View 2 Replies View Related

DB Engine :: Database Files Shrinking

Jul 6, 2015

I have dataware house database and it's size is 2 TB with simple recovery model.I want to reduce it's size because everyday before loading table gets truncate.Is it best practice to shrink the datafiles?database having 5 data files and one log file.what is the best way to reduce?

View 7 Replies View Related

Shrinking Database File After Removing Data

Apr 20, 2007

Hi there,

I have a large (420GB) database that has never had data archived off before. I taken a backup to a test server and run a script supplied by the product vendor which has removed a large ammount of old data no longer required.

I have checked within enterprise manager that this data has now gone, however the actual file itself has not shrunk in size. Is there a further step I need to take to get back the space.

Kind Regards

View 2 Replies View Related

Shrinking

Feb 20, 2001

1) Does shrinking a db have any side affects ? Or this is pretty much a normal
operation ?
2) Also in db options, is it recommended to have auto shrink checked ?

Thanks for your help.

View 1 Replies View Related

DB Shrinking

Dec 14, 2007

I am new to SQL server.

We have many databases for which log files have grown much bigger. The one I need to Shrink immediately has 16 MB .mdf file and 12 GB of .ldf file.

I will very much appreciate, if somebody can help me with step by step process to shrink the database/log file (some way). We are in a crunch situation

Thanks

View 3 Replies View Related

Shrinking The Transaction Log

Feb 1, 2005

I have a webhost where it seems my control of my database is fairly restricted. I cannot backup the database because I don't have the necessary permissions. I cannot perform a DBCC SHRINKFILE (permissions) - and many other DBCC commands. I ran into a problem where my log file filled up during the middle of the day and impacted my operations - data was lost.

I found some TSQL for shrinking the log file, but the statement:
SET @TruncLog = 'BACKUP LOG [' + db_name() + '] WITH TRUNCATE_ONLY'; EXEC (@TruncLog)

will not execute because I cannot execute the BACKUP LOG command (permissions)...

Is there anything else I can do, or am I to far up the creek (w/o my paddle)??

Thanx
Jerry

View 6 Replies View Related

Help! Log Is Auto Shrinking Itself

Mar 4, 2002

HiRunning SQL 7 sp3 on NT 4

I have a database that has the auto shrink option turned OFF. However, the log file seems to auto shrink after the user
runs bulk insert.

The log file is not setup to auto grow either.Any ideas.

Thanks,
Tariq

View 2 Replies View Related

Shrinking The Log File

Apr 25, 2002

I have a 13 Gig Log File with only 121 Mgs of space used. I have run the DBCCSHRINKFILE command and it has shrunk it by about 100 Mgs. Why can't I get it to shrink to a reasonable size.

View 2 Replies View Related

Shrinking Transaction Log

May 3, 2001

I've been trying to shrink my SQL 7 Transaction log after it had grown to 30+GB's. After running the command dbcc shrinkfile 'filename' and the new size, I'm getting a result set of:

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
6 2 640 128 640 128

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

However my transaction log remains the same size. Any advise would be appreciated!

View 2 Replies View Related

Shrinking Transaction Log...

Aug 28, 2001

I'm having a problem shrinking my transaction log. I have a 1GB database with a 500MB transaction log. The transaction logs are backed up every 10 minutes but it has grown to 2.2GB. I've tried backing the transaction log with TRUNCATE_ONLY and then tried doing a DBCC SHRINKFILE but it doesn't seem to work. I've checked if there were any old, long running queries but there is none. What else can I do to reduce the transaction log size?

I've had a thought to create a secondary log file and then delete the primary but that isn't allowed. Is there anyway I can make the secondary log file the primary and vice-versa? This way I should be able to delete the secondary log file to reclaim space.

Any assistance is appreciated...

Many thanks,
Michelle

View 8 Replies View Related

Shrinking Db File

Sep 18, 2001

Have a 6G data file in a single filegroup.

Using dbcc shrinkfile to shrink.

Does not have any effect.

Any ideas?

View 1 Replies View Related

Transaction Log Keeps Shrinking

Oct 18, 2001

We have a database that was created with a 50 MB transaction log, that is set to autogrow. Due to activity the log periodically grows to over 200 MB. So we expanded it to 200 MB. But everytime the log is backed up, it shrinks to it's original size of 50 MB. The autoshrink option is not set, and no one is manually shrinking the log.
Any idea what could be causing this?
We are using SQL 7, SP 3.

Thanks!

View 2 Replies View Related

Transaction Log Not Shrinking

Jul 27, 2000

Hello all,

I am having a problem truncating a transaction log. The truncate and database shrink commands execute successfully, but do not reduce the size of the transaction log.

Thanks in advance for any help on this issue

-Matt

View 5 Replies View Related

Shrinking The Log File

Sep 15, 2000

Hi, is there any other way to shrink the log file other than truncating every time it gets bigger... I have a 800MGs database and the log is getting as big as 11Gigs... any suggestions other than truncating it? Is there a way to set it off?

View 1 Replies View Related

Log Shrinking When DB Not Set To AutoShrink

Jan 11, 2001

Hello,

Ran the Andrew Zanevsky script to shrink the Tran log, this works great, but say the log gets shrinked to 30MB, I then expand it to 40MB, then sometime within a 24 hour period it magically gets reset to 30MB. The database is a standalone DB, and is on SQL7 SP1 the script I ran can be found @ http://www.pinnaclepublishing.com/sq/SQMag.nsf/WebIndexByIssue/F36C151BD80FAD8F852568D0007799BE?open.
Can anyone please give some insight into this or maybe a flag that got reset somewhere?
Thanks in advance
Pat

View 4 Replies View Related

Shrinking Log Files

Feb 1, 2000

Hi again

Damn, people, how to shrink log file. I've tried several times with
DBCC SHRINKDATABASE ( database_name , TRUNCATEONLY)
and
DBCC SHRINKFILE (log_name, final_MB, TRUNCATEONLY)

and the log still is _untouched_

I have got big table, about 190 mln records (15 GB) and after simple update the log is very big but unnessesery.

Michal Pajkowski

View 3 Replies View Related

Shrinking The Tempdb

Oct 9, 2000

HI-

I've got a development server where I periodically have to allow the tempdb to grow to over 2GB. However, I'm at a loss on how to shrink the tempdb back down to a manageable size. I need the drive space on the server for other tasks. This seems like such a simple thing but I can't find information anywhere on it.

-Ryan

View 2 Replies View Related

Shrinking Databases

Dec 4, 2000

I have a database that is 9 Gig in size and I am no longer needing the information that was stored in this database.

So I deleted the User tables that I created and deleted the User stored procedures.

I performed the Shrinking database feature and it only shrunk my database to 2 Gig.

How come it did not shrink the database to at least 10 MB since I no longer have data stored in the database?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved