Log File Size Not Reduce
Jul 23, 2005
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 of
space.
I'd do a full database backup, then backup the transaction log as well
.... both backup performed with a check on the option "clear inactive
entries 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_log
DBCC shrinkdatabase(databaseName, 30)
==============================
Is that safe to be used in production server?
Peter CCH
View 14 Replies
ADVERTISEMENT
Jul 25, 2007
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 Related
Jun 7, 2008
Hi my database .ldf (log file) size is very big. How can I decrease the size of it?
Thanks.
View 2 Replies
View Related
Dec 9, 1999
I 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 Related
Dec 10, 2001
hi, 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!
View 2 Replies
View Related
Jul 19, 2002
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?
View 1 Replies
View Related
Mar 9, 2005
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?
View 6 Replies
View Related
Mar 8, 2000
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
View 1 Replies
View Related
Apr 30, 2015
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 ?
View 9 Replies
View Related
Oct 5, 2015
I 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 Related
Nov 2, 2004
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
View 5 Replies
View Related
Aug 17, 2001
I 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.
View 2 Replies
View Related
May 16, 2007
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
View 12 Replies
View Related
Mar 19, 2000
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 Related
Feb 5, 1999
Can 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
View 1 Replies
View Related
May 25, 2002
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.
View 4 Replies
View Related
Apr 29, 2008
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
View 8 Replies
View Related
Jun 17, 2008
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
View 2 Replies
View Related
Jul 20, 2005
Hi, 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 Related
May 5, 2004
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 Related
Jul 20, 2005
Hello,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 Related
Nov 25, 2007
I 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.
View 5 Replies
View Related
Jul 20, 2006
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 Related
Jan 20, 2012
I 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
View 10 Replies
View Related
Mar 14, 2007
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
View 1 Replies
View Related
Sep 4, 2007
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?
View 1 Replies
View Related
Jun 14, 2015
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 Related
Aug 17, 2000
I had a database that’s comprised of different file groups and log files spread out among different hard drives. I have recently upgraded the database to SQL 7.0 on a RAID 10 volume. I would like to consolidate all the file groups and files as well as various log files into one primary datafile and logfile. How do I do that? Thanks in advance.
View 2 Replies
View Related
Feb 11, 2004
Hi all,
I know this topic has been discussed in the past, but I still don't quite get it. So please be patient with me
database size created with automatically growth of 10% with unrestricted file growth.
database size = 5 Gb used 4.5 GB (taskpad)
transaction log=8GB used 54MB (taskpad) 7.5 GB free
database run in FULL mode
full backup nightly, transaction log backup every 30 min
What should I do to free up the space that are not used in the transaction log.
Thanks for your help.
View 7 Replies
View Related
Sep 26, 2007
My Database plan is as follows:
Full Database backup every night, Full recovery mode.
T-Log backups , every 4 hours.
both are appended to media,
there are no active transactions,
but Log file will not shrink or reduce in size.
what would you suggest.
Thanks,
View 3 Replies
View Related
Aug 30, 2006
Hi.. We have a MSSQL application and the DB file (not the log file) seems getting bigger over this few year and right now you are running almost out of space. May I know how does the other company deal with this kind of situation?
i am sure other company data is getting bigger as well and it has been longer time than ours. How to deal with it ?
View 3 Replies
View Related
Apr 15, 2008
I have a log file that is approximately 50 GIG. I backed up just the log and the file size of the .bak is 192 GIG . Why is this? Shouldn't it be closer to the 50 GIG.
Normally I wouldn't let log grow this much. But we are in process of getting new server up and running and don't have backups going yet. They are working on getting that up and running this week.
So I did a log backup to give me back some log space for now but was concerned when I saw the size of the .bak file.
When I view media contents of the backup device it shows one tranaction log back up and size of 192 GIG.
What is up with this. I know in SQL 2000 the log backup files where never this big. they were about the size of the log itself.
Any ideas?
Stacy
View 8 Replies
View Related
Jun 15, 2006
I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message
native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.
I'll try to post the full log in a new post.
View 11 Replies
View Related
