Shrinking Database Files
Jul 23, 2005
I have a database file LEAR_Index(yes, it hold index data) from a have
have recently removed a bunch of data.
It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink the
file 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 consistenly
crashes when performing this operation.#
Any thoughs, idear as to what i might be doing wrong?
View 6 Replies
ADVERTISEMENT
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
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
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
Nov 3, 2006
What is the best way to Shrink a Data file and Log Files.
View 1 Replies
View Related
Apr 11, 2008
Hello,
we are running on sql server 2005 and in some of our production databases the log files are twice as bigger than the data files. we are planning to reduce the log file size. I saw the option on the management studio to shrink the log file. I just have some questions on this.
as long as we are doing on the production server can we do that while the database is online or do we need to take the database offline? any help would be appreciated. Thank you!
.
View 9 Replies
View Related
Jul 22, 2003
Hello everybody.
I have SQL2000 sp3 standard
with 50 db's
All db set for full recovery
and autoshrink
Backup done with Tivoli
full backup once a week
log backup done every 12 hrs
Problem .. shrinking logs
every 20 min I run job
DBCC SHRINKFILE (My_db_logFile) for every db
70% of the time I am getting
message similar to
-------------
Cannot shrink log file 2 (Wholesale_Log) because all logical log files are in use.
--------------
1. I checked with sp_who2
The is no activity on db 'Wholesale_Log' or any other db returning "Cannot shrink..."
Why i getting "Cannot shrink ..." ?
even if job runs right after backup of the log files ,I still have messages.
Thank you
Alex
View 6 Replies
View Related
Jun 26, 2006
Hi,
I have some doubts about Shrinking databases or files.
while shrinking a file, I learned that we are alllowed to shrink more than the minimum size of the file, does not it bring damage to the data in that file ??
View 13 Replies
View Related
Sep 8, 2006
I've been tasked with taking over the support for a client's SQL Serverdatabase. I'm not a DBA by profession, I'm a software developer whouses SQL Server as a database designer.The clients have reported that the server is running out of disk spaceand examination shows that the log files for several of the databasesare at 5Gb or more.After reading around the subject I suggested the following sequence ofoperations:-- Select the name of the database you want to shrinkUSE MyDB-- Dump unwanted transactionsdump tran MyDB with truncate_only-- Get the name of the logfileSELECT * from sysfiles-- Having examined the rows returned by this use the log file....-- Shrink the file to required size (in MB)DBCC SHRINKFILE('MyDB_log', 10)Is this a reasonable approach? Please bear in mind that I'm pretty newto this, and I have many other tasks to do besides manage the server.A previous DBA has set up good maintenance plans etc. so everything isbeing properly backed up (well, I think it is)If this IS a good approach, would it be reasonable to do this on, say,a monthly basis as a scheduled job? Obviously the stepSELECT * from sysfileswhich gives us the physical name of the log file would be removed andthe job would operate explicitly on each log file for each database inturn.Many thanks for reading.William Balmer.
View 2 Replies
View Related
Aug 6, 2015
I have a no of databases in full recovery model whose files are many times their datafiles. It is because these databases were copied from the development servers and in the development servers they were not taking the transactional log backups although once in the production server it is ensured that a transactional log is taken once in a day atleast. I plan to shrink the logfiles using the dbcc commands. However I am afraid that it may lead to severe defragmentation and performance hits.
We are using Sql Server 2008R2 enterprise edition which is clustered.
In this context my questions are:-
1)What is the best course to do the shrinking of log with out defragmentation?
2)Can I do the shrinking when the database is in use or is online in production?
3)Will the shrinking of the logfile improve the performance in any manner like that of the i/o operations or paging?
4)Can I do the shrinking of the log files alone without the shrinking of the corresponding data files?
View 10 Replies
View Related
Jun 21, 2015
I am using Sql Server 2012 in always on configuration with multi subnet failover clustering. Size of data file has suddenly increased, i dropped all the unnecessary table from database three days back. day before yesterday i tried shrinking data file using DBCCÂ command but it is taking too much time. is there any other option for deallocating the space.
View 7 Replies
View Related
May 26, 2015
I have a database which has log file size 300 GB. As the drive is filling up i need to clear the space on the drive, for that i have to shrink the log file.Â
Unfortunately i dont have option to take backup of the database.And i am not able to shrink the file now. Is there any way to shrink the log file with out taking backup of it ?
View 5 Replies
View Related
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
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
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
Jun 20, 2006
Hi guys.I have a huge database file. When I run sp_spaceused this are theresults:db_size 1337,31 Mbunallocated 14,62 Mbreserved 1088456 Kbdata 258992 Kbindex_size 6224 Kbunused 823240 KbNow, when I run dbcc shrinkfile(database_name, 50) or whatever value ofpercent, 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 advanceZvonko
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
Nov 5, 2007
Hi all,
I downloaded and ran AdventureWorks.msi into my SQL Server Management Studio Express (SSMSE) one year ago.But I did not know how to attach it to my SSMSE then. Last week, I deleted it from the "Add or Remove" of Control Panel and I downloaded the new AdventureWork.msi and installed it my SSMSE. Today, I tried to use the Database Explorer of VB 2005 Express for the first Stored Procedure programming. I clicked on AdventureWorks.mdf and I got the following error: One or more files do not match the primary of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupt and should be restored from a backup. Cannot open user default database. Login failed. Login failed for user 'CENADe1enxshc'. Log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data_log.ldf' does not match the primary file. It may be from a different database of the log may have been rebuilt previously. Please help and advise me how to correct this problem.
Thanks,
Scott Chang
View 9 Replies
View Related
Apr 8, 2007
Hi all (newbie @ asp.net)(oldie @ ASP 3)What is the purpose of using an attached MDF database files in the App_Data folder on a web site as to importing it into the SQL server directly or creating it on the SQL server. Does a mdf database attached file purely use the SQL server as a connection interface.Is it something similiar to DSN(ODBC) Connections for ms access databases.
View 2 Replies
View Related
May 14, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I am trying to modify the files path (primary file, log file) of databases, but it looks like I am not able to mofidy their files path directly from the database property dialogue? Would please any experts here give me some ideas on what else can I try to figure it out? Thanks a lot in advance and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 5 Replies
View Related
Aug 19, 2015
I have a client that has POS software called Restaurant Pro Express (RPE) from [URL] ...
Their old POS computer had a hardware failure, but I was able to attach the hard-drive to another computer and recover the data. RPE uses a MSSQL database system. However, my client doesn't seem to make backups very often
- the last one is dated January 5, 2015. I was able to copy the C:Program FilesMicrosoft SQL Server folder over which contained the instance as well as all the data files - and has up-to-date information. The instance in the recovered Microsoft SQL Server folder was called MSSQL.1. I installed the RPE software on their new computer, and it too now has an instance called MSSQL10_50.PCAMERICA. The new computer is using MSSQL 2008 R2, while I believe the old computer would have been using MSSQL 2005.
I am no DBA expert, especially when it comes to MSSQL. Is is possible to 'restore' the database from the 'raw' .mdf and .ldf files of the old computer to the new computer / database instance? If so, how should I proceed?
View 3 Replies
View Related
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
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
