HOW TO REDUCE THE SIZE OF SQL SERVER DATABASE.
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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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 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 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 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
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
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
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
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
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
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
Jan 13, 2000
I'm worhing with SQL Server7.0 and I have created a database.
I have objets and data in my database
My database have 300Mb and I only need about 50.
How can I reduce to 50 the dimension of my database.
Is it posible??
Any advice will be greatly apreciatted.
Nauj
View 2 Replies
View Related
Oct 29, 2015
I looked at the number of VLFs in ony databases and figured out it was 300. I looked at the log file and its 40GB with 99% free space. I reduced the log file to 1GB. Now I looked at the VLFs and the count is 250. so 250 vlfs in a 1GB of log file. Is there a way to get the VLFs to a decent number?
Also my transaction log backups run once every 5 mins and auto growth on data and log files is 10MB.
View 18 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
Mar 29, 2001
Hi,
1. sysfiles stores file size for specific database
2. sysdatabases stores file name
From what table can get size of all database files in server ?
Thanks
View 5 Replies
View Related
Mar 29, 2006
Hi! Everybody there!!
I am faced some problem for SQL SERVER!
Database size info:
>> Current Size: 4.1GB
>> Space Used: 3.7GB
Yesterday, I had shrink the database from 4.1GB to 3.7GB.
>> Current Size: 3.7GB
>> Space Used: 3.7GB
Today, i checked it again, found out that the database was increase to 4.5GB.
>> Current Size: 4.5GB
>> Space Used: 3.7GB
Is that SQL SERVER is running in this way? it is behaviour action? But my other database did not have this problem happen
Please help!!!
Thank you
Melvin
View 3 Replies
View Related
Mar 15, 2007
Good evening,
I have a SQL Server 2005 database installed, serving as back-end DB, for an ASP.Net 2 application. Currently the database size is nearly 800Mb with recovery mode set to Simple. Every single day a maintenance job runs shrinking the db size, reorganizing indexes and statistics, etc.
Since I'm not an expert in SQL Server Administration I'm trying to figure out if this space is really necessary. The previous version of this application was running under PHP5 and MySql 4. After a year, with pretty much the same load as the current version, the database size was arround 370Mb. With the SQL Server it's already -800Mb after 3 months.
Pretty much my guess is that I'm doing something wrong. I just don't have any idea of what might be going on. If one of you guys could throw in some ideas on how to check if this size was really necessary and, if not, how to reduce it, I'd be very thankful.
Best regards,
ahkaru
View 6 Replies
View Related
Oct 29, 2004
Hi,
Can anybody suggest any stored procedure name or C# code to find the Sql database size (database size as well as transaction log)? I want to migrate some data from my local database to the web server. Before migration, i want to know the database size on the web.
Thanks and regards,
mrc
View 2 Replies
View Related
