Hi all;
i m using SQL 2000, i have a database with 86G mdf and 56G ldf size. i shrink the ldf and it reduced to 32M, however, i did not do anything on my mdf file, but the size of mdf has been reduced to 28G. just would like to check, is this correct?why is mdf size reduced when i only shrink my ldf? hope can help. thanks
I have one db test with one .mdf and .ldf file...mdf file size is 100mb and for some reson i removed all the tablesfrom that .mdf file and transfer it into new secondary file so all thetables moved into secondary file now i want to reduce the first .mdffile from 100 mb to 50mb is that possible,it's showing 90mb is free.Please reply
View 1 Replies View RelatedI need to reduce the size of a db from it's original allocated size of 2.0 gb to 1.0 gb.so that I can allocate more to another db.How can I do?Thanks,
Ravi.
I'm new to SQL Server Maintainance. I need some disk space now. In my database, I have some table that has 7 year old data and I can get rid of that without any conequences. Could you please tell me the best course of action ?? I was thinking about:
1. Dropping those tables.
2. Recreating them again
3. Running ShrinkDB on entire DB.
However, this solution does not sound right or elegant to me. Could you please help me with that ? Thanks
Hi my database .ldf (log file) size is very big. How can I decrease the size of it?
Thanks.
My database's transaction log has become 1.7 GB. Can I reduce it's size? I have tried to shrink database and also set truncate on checkpoint option and also taken the backup after that. but nothing helps. Please advice.
View 1 Replies View RelatedI am using SQL 7, SP1 / NT 4. The .LDF file has grown to 1.1GIG; I ran a DBCC SQLPerf(LogSpace), the used portion of the log is 2%. When I run a DBCC Shrinkdatabase and DBCC Shrinkfile, the log file does not reduce in size. How do I get the virtual log files that are not active released back to the system? Is there a way to tell if all the virtual log files are active, therefore, not reducing the size of the file? Any help is greatly appreciated.......
View 3 Replies View RelatedCan anyone suggest a method to reduce the size of one our log devices. The DB was set up
initially at 500Mb with a log size of 1 Gb (typo by the client). We would like to reduce it to 100Mb.
if possible. Our environment is SQL Server 6.5 with service pack 3.
thanks,,,brad
Dear All,
I am running Slq2000 (EV) on NT4.0. I have problem is that a size of Transactional Log(*.ldf) file is 3GB. I want to reduced the size to 2GB.Can anyone help me answer this question.
Note: I hv already took the backup of Transactional file by choosing Trunct file after backup but size is not got reduced.
Hi All,
On my server C drive is of 34GB. Right now tempdb size is 22GB which is causing C drive to be full. How I can I reduce it? I dont want to move tempdb to any other drive, and I am only looking a way to reduce its size.
Please help quickly....
Thanks,
Zee
Database log of my DB is around 2GB.The database is using FULL recovery option.I want to reduce the file size of the log cause it takes up a lot ofspace.I'd do a full database backup, then backup the transaction log as well.... both backup performed with a check on the option "clear inactiveentries from transaction log".But after I backup, the database log is still 2GB.What should I do to reduce the database log file size?Should I use?:==============================Dump Tran databaseName with no_logDBCC shrinkdatabase(databaseName, 30)==============================Is that safe to be used in production server?Peter CCH
View 14 Replies View RelatedHi, my database size has grown out of control and I need help with thefollowing issues. (I am very new to databases)I am storing financial tick data in one of the tables and after two monthsthe database has grown to 30GB. I do not need a permanent record of thistick data after it has been processed and tried to remove all rows from thistable (delete from Tickdata), however sql does not take kindly to removingmillions of rows and the operation seams to time out. The only solution Icould come up with was to delete the table.Secondly, after managing to clear out these tables I have noticed that thedatabase size is still 30GB, despite 29GB being available. Is there any wayto reduce the size of the database from 30GB. I tried the shrink databaseoption but it does not do anything. Any ideas?Thanks.
View 5 Replies View Relatedhi, i'm a newbie in SQL Server and have recently setup a test SQL 7 server. I used all the defaults at the beginning, and now the MDB file is about 500MB and the LDF file is of similar size.
i'm still trying to figure out how to reduce the size of the transaction log file. Currently I only have full backup of the database once a week, and there is no backup for transaction log.
as of this moment, the transaction log is of not much use to me, but I really want to get it reduced as i'm running out of disk space.
and i'd also greatly appreciate if someone could suggest a good DBA practice on the proper setup/handling of transaction logs (how to balance the disk-space usage AND be able to use the transacton logs for proper roll-over during a recovery process).
i'd soon be setting up a SQL 7 server where about 10 active users are expected at any one time. I've read that the transaction log file should be about 40% to 50% of the estimated size of the database file, and should be allowed "auto-grow". So what happens if the more space is required by the transaction logs? Does a full-backup purge the transaction logs (like the way they do in Exchange Server)?
Thanks!
Hi,
My transaction log file's physical size has exceeded 2GB. How can I reduce that?
Is there any way by which this file size can be controlled?
We have a database which was created with an initial file size of 10 gig. Currently it is only using 2 gigs.
We have developers that want to have a copy of the database on their desktops, but do not have 10 gigs free space.
What is the best way to get them a copy of the database while reducing the footprint?
The size of my transaction log file is out of control. I've backed up the database and the transaction log went from 120 GB to 120 MB. Now, I can't reduce the size of the transaction log file. It's still at 120 GB (w/ almost all of that being held as Free Space). I get errors when I try to manually reduce the file size. Any tips?
View 9 Replies View RelatedHello,I am hoping you can help me with the following problem; I need to processthe following steps every couple of hours in order to keep our Sql 2000database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql serverdatabase and while I have found multiple articles online about the topics Ineed to accomplish, I cannot find any actual examples that explain where Iinput the coded used to accomplish the above-mentioned steps. I dounderstand the theory behind the steps I just do not know how to accomplishthem!If you know of a well-documented tutorial (Aside from Books Online (F1)),please point me in the right direction.Regards.
View 1 Replies View RelatedI have patch server on which the database is SQL server 2000.
The Database size is 27GB and I want to reduce the size of DB by deleting all the records and keeping only year 2007 records.
Please advise me how to reduce the size of the DB.
If I need to delete any record, please let me know how to delete it.
If their is any Query for it, please send the Query.
The DB contains total of 11 tables.
Thanks to All,
Mohammed Muzaffar.
HELP !!! I have a 1.2GB transaction log that I cannot reduce. I have tried SHRINKFILE, SHRINKDATABASE, restoring the file into a new data base, etc. The log file is only using 15MB and will not release the remainder to the OS
Any and all suggestions would be helpful
Thanks, Chad
I have issue with my DATA file ( MDF) .. The usage is 99.87% for database DB1 . File size is 3 GB . How do I reduce it ?
I have tried to shrink it by changing the recovery from FULL to SIMPLE and set it back to FULL .
I notice the index Defragmentation is high ..
Can I change the Initial Size into 1 GB for example ?
We are using SQL Server 2000 Standard ed, sp4 on Server 2000 Advanced.We have one table that is three times as large as the rest of the database.Most of the data is static after approximately 3-6 months, but we arerequired to keep it for 8 years. I would like to archive this table (A), butthere are complications.1. the only way to access the data is through the application (they areimages produced by the application-built on Power-Builder)2. there are multiple tables refrencing this table and vise-versa3. we restore the entire db to two other servers for testing and trainingregularly4. there might be more complications that have not been thought ofCurrently, our only plan is to setup a seperate server with a copy of this dbon it and the application. Leave only the tables necessary to access the data,and if this 'archive' works, remove from production the data from the table Aand all references to the table A from rows on the other tables.I mentioned #3 because someone mentioned a third party tool that may be ableto pull the data from the table, archive it elsewhere, and at the same time,place a 'pointer' in the table to the new storage location. The tool theymentioned only works on Oracle and we have not explored beyond that yet.I am ready to explore ideas and suggestions; I am still new to the DBA world,I am out of ideas.Thank you!--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200607/1
View 1 Replies View RelatedI am working on SQL Server 2008 and there is a problem in my SQL Server log file. I am seeing this from last 8 days, my log file size increasing continuously while i have set it on Simple Recovery model. Is there any method from which I can reduce the size of my log file and also I want to know, how the Recovery models affect to the size of a log file?
View 4 Replies View RelatedI have a table with 99.9% of unused size. How do I reduce the size?I have tried those commands below but they do not work.
ALTER INDEX [VBDATA~0] ON qa2.VBDATA REBUILD
DBCC CLEANTABLE (QA2,"qa2.VBDATA", 0)
WITH NO_INFOMSGS;
GO
ALTER INDEX ALL ON qa2.VBDATA REBUILD
Hi,
Is it possible to reduce the file size of an image when the user uploads it into a sql server image field.
i.e Crush it from 500K to less.
Regards,
RG
I have a Reporting Services report available in a Windows Sharepoint Services webpart. The report is too large to display in the webpart window requiring the user to use the scroll bars. Is there any way to reduce the size of the report so that fits completely within the webpart without using scroll bars?
Thank you.
David
I need some advice for an issue with a production transaction log min size having been set to 50 GB by some rouge developer while I was on vacation. Now, as the only resident DBA, I'm responsible for going in and reducing it down so we can free up limited disk space. It’s in SQL 2000 and participates in replication.
Because the log file cannot be set below its current minimum size, I’m a little stumped. Can I somehow force the minimum size by updating the table database.dbo.sysfiles?
Otherwise, what do you-all think of this plan?
1.)Back up the database.
2.)Verify no open transactions with DBCC opentran(‘database’) and proceed accordingly.
3.)Change it to single user mode.
4.)Set it to Simply Recovery mode.
5.)Issue a checkpoint to clear the log.
6.)Script out push replication.
7.)Delete replication.
8.)Detach it.
9.)Delete the Transaction Log.
10.) Attach it and let SQL Server recreate a transaction log to the SLQ Server default size.
11.) Run the Replication Script.
12.) Start the Snapshot Agent.
13.) Restart the Transaction Log Reader Agent.
14.) Celebrate a Victory!
Thanks in advance for your help!
Jeffrey.Hoover@gmail.com
Minneapolis, MN
I want to shrink database:
1. backup log ABC with truncate_only
2. dbcc shrinkdatabase(ABC, 10)
Cannot shrink log file 2 (ABC_Log) because requested size (113792KB) is larger than the start of the last logical log file.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
7 2 14432 12800 14432 12800
there is no transaction in this ABC database. Is there way to shrink it?
Not quite sure about "because requested size (113792KB) is larger than the start of the last logical log file.". The current log size is 113 mb.
thanks
-D
I have a tempdb that was created at 1Gig. I don't know why but I want to shrink it below the original creation size. Is there a way to shrink this file or create a new file and delete the old?
I have tried shinkfile and shrink database with no luck.
Thanks Jeff
I have a sharepoint content database in sql 2008 R2 (WSS_Content) that is at 230Gb size, but has 40% of it is empty space. Â This is because we have removed a large amount of old content from sharepoint. Â The log file is fine. Â I have 60GB left in my drive that host the database files. Â I would like to shrink the datafile to get disk space back. Â I found that under the files property, the WSS_Content data file's initial size is 228702 MB (220 Gb or so). Â
When i try to do a shrink file (data file) from management studio, i see the 60 GB of drive space keep dropping. Â So i have to kill the process. what i should do to reduce this data file.
why it keep using up all the free space in the drive when i try to shrink the data file?
I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.
View 4 Replies View RelatedGreetings:
I have a database that is 1.7terabyte in size with 136gb free and throws a "transport level error" telling me to discard the results when I run dbccshrinkfile ('DBNAME', size). I have tried various increments of size, from truncateonly to 1MB below its current value, and nothing works. I have tried to detach and reattach the db, restart the service, restart the server, and none have provided a solution. Any ideas?
Regards,
Derek
I am working on complex invoice document that handles various types of invoicing, credit, and progress bill customer facing documents. Â I am faced with same problem I see many SSRS devs posting on. Â And that is extra blank pages, being generated because the report width exceeds the width of the page.
My problem is I cannot shrink the horizontal width of the tablix and therefore the report width either...
While I would have done things differently if developing from scratch, the tablix in question has only one column and objects contained in rectangles are placed in the row/column cell.....The link to the image below shows the row I am currently working on. Â Essentially all the client wants a columnar report with column lines, closed footers, etc... Â so rather redoing the whole tablix, which I still might do, I am using rectangles with the column then lining them up. Â Anyway, that is a whole 'nuther post. Â My specific questions are: Â
Invoice in Report Builder
1. In the image I have selected the parent rectangle. Â The children are other rectangles and the data is with grandchildren textboxes that exist within those rectangles. Â I clearly have a bit of right side white space in the main parent rectangle, but there is no sizing tool on a hover... just move tool. Â And if I change the rectangle size in the prop form it just snaps back to the cell width. Â How do I size the parent rectangle and keep if from expanding.
Here I am guessing that to actually reduce the horizontal width of tablix and then the report, I have to first decrease the parent width of the rectangles that occur in each row?
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?