Reducing Size Of The Database
Apr 29, 2002hi
i have a database that becomes to big after a few days. is there a chance to say if the table reaches a number of lines or a special memorysize, delete (or better archive) the oldest entries?
thanks
ADVERTISEMENT
Reducing Database Size
Dec 14, 2001I have a production database of a size of 70 GB. Half of the data was archived and deleted from the current database. What is the best way to
reduce the size of the database, as we cannot shrink an entire database to be smaller than its original size? Thanks a lot!
Reducing Database Size (contains Images)
Jun 8, 2004Hello. I am wondering how to effectively reduce the size of my database. After viewing the individual table sizes, I have come to realize that nearly 99% of the database's size is due to images. I am told that too much binary data is not good. How can I go about reducing the size of my database (possibly the images themselves)? I'd appreciate any help.
View 11 Replies View RelatedReducing Database Size After Dropping Text Column
Jul 20, 2005Hello,A while back I dropped a text column from a SQL Server 7 databaseroughly 3GB in size. I expected the size of the database to decreaseby around 1GB, but no change occurred. After searching usenet, Idiscovered that SQL Server 7 has no way of reclaiming that space, butthat there is some command that can be run in SQL Server 2000 thatwill reclaim it.I have since migrated this database to SQL Server 2000, and am nowtrying to figure out what that command is, but cannot locate anyusenet posts about it... also tried searching books online, but can'tfind anything that way either.Does anyone know what I should run?Thanks,Tom
View 6 Replies View RelatedReducing Size Of Transaction Log
May 25, 2001If I have a transaction log in a database of size 1GB ( space allocated is during creation of database) currently only 300 mb of its space is used i.e. nearly 700 mb is free. If I want to reduce physical file size of transaction log by 200 mb and release it for operating system then How can I do it???
View 4 Replies View RelatedReducing Transaction Log Size
Jul 17, 2003i am new to sql server. i recently found the transaction log size of my database has reached 109 MB. how can i reduce it. a transaction log backup was sceduled daily at 12.00 noon nad full backup monthly.
View 3 Replies View RelatedArchiving Is Not Reducing DB Size
Jan 10, 2005I created few jobs that would archive the production DB and delete the archived data...
but it looks like the DB size is not reducing!!! Some times it looks like the size has increased!!
I think this is because of the log file size has increaded by the DELETE operations....But what can I do for this???
Please Help!!
Reducing Log File Size....!
Dec 7, 2007I have a Log file grown to 28 G.
TASK: I want to claim the hard disk space.
I want to use
--backup Log DB-NAME with truncate_only
--dbcc shrinkfile(DB-NAME_Log,1)
Is there any risk involved in above steps, and OR would any experienced Folk like to share his or her idea to the Task as above.
Many Thanks,
Reducing Log File Size
Feb 12, 2008
I have a database who is in full recovery mode. I have four maintenence plans setup: database backup, log backup, optimization and integrity checkup. The last two plans run weekly and the first two run daily. I found that the log size often increase to a dramatically size in a very short period, almost same size as the database file (4G). Further I found that the size seems increase a lot after the last two plan runs.
My question is that the optimization operation(reconstruct index page) will write any reocord to log file? Is this possible a reason?
Now the log file occupy too much disk space (90% of space can be free). What I should do? Shrink database weekly?
Thanks
Reducing Transaction Log Size - SQL2000
Jan 3, 2001Can anyone help me reduce a transaction log. It is currently at 2.5GB because it was set to autogrow with no backup !?
I need to drastically reduce it and have backed it up and tried dbcc shrinkfile...but...it now says space used is 120MB but current size is still 2.5GB.
How can i reduce this down please ??
thanks
Reducing The Size Of The Transaction Log File
Oct 3, 2006At this time I am only playing with the applications that generate the data and send it to the database. Without doing too much, and with deleting most data tables that were created, my transaction log file has grown over a gigabyte. I tried using the SQL server management studio (express) to shrink the database (tried shrinking files, too) but that did not make the file smaller. Right now there is hardly any data in the database (6 tables, a dozen columns and rows each) so it must be old transactions that are kept in the log. How do I get rid of the old data and make the file size smaller? Thanks.
Kamen
Reporting Services :: SSRS Report Body Size Is Not Reducing
Nov 10, 2011In one of my SSRS report under body properties default size (width = 34.54712cm , height = 20.92853cm ). I've tried to reduce the size to width = 26cm ,height = 18 . once i changed. the width its going to the same orginal size again.
View 2 Replies View RelatedReducing Database And Log Sizes
Dec 16, 1999I have inherited a number of databases which were substantially over sized when they were set up. I'd like to reduce both the log and database files to be smaller than their original sizes, what's the easiest way to do this? If anyone has any experience of doing this please reply.
View 1 Replies View RelatedNeed Help Reducing Database Backup Times
Feb 5, 2007Hi all,
Currently we take full database backups nightly for our SQL Server 2000 data warehouse systems. The backups take a very long time over 20 hours and we would like to find a good way to reduce these backup times. How can I change our backup plan to reduce the long backup run times. Data size is 1 TB for our data warehouse database server.
Thanks
Table Size And Database Size
Mar 2, 2008Hi,
i use this script that show me the size of each table and do the sum of all the table size.
SELECT
X.[name],
REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
(SELECT
CAST(object_name(id) AS varchar(50)) AS [name],
SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
SUM(CONVERT(bigint, dpages)) * 8 AS data,
SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY sysindexes.id WITH ROLLUP) AS X
ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup.
example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when
i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
THX
SQL 2005 Resize Initial Log Size: MODIFY FILE Failed. Specified Size Is Less Than Current Size.
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?
Getting Database Size And Log Size
Aug 11, 2000Hi,
I am using
exec sp_helpdb
go
dbcc sqlperf(logspace) for
getting database size and log size. Is this gives the correct
database size and log size or Is there any other way to get the logsize and database size by means of query analyzer.
Thanks in Advance.
Seenu. S
The Database File Is Larger Than The Configured Maximum Database Size.
Mar 20, 2007I'm getting this error while trying to insert records into a SQL Server Compact Edition database. I have pasted my connection string that was used when creating the database as well as for accessing that same database from my Windows application.
Thanks for any help any of you can give!
Data Source=OnTheGo.sdf;Encrypt Database=True;Password=<password>;Max Database Size=4091
Reducing Many-to-many To One-to-one
Sep 14, 2007Hi,I have the following three tables below containing Resources,Categories and a link table so each Resource can belong to one or moreCategories. I would like to create a view (ResourceID, ResourceName,CategoryID, CategoryName) that includes one row for each Resource withjust one of the Categories that it belongs to.Resource table- ResourceID- ResourceName- etc..Category table- CategoryID- CategoryName- etc..ResourceCategory table- ResourceID- CategoryIDCan anyone help? Thanks.
View 2 Replies View RelatedDatabase File Is Larger Than The Configured Max Database Size.
Feb 18, 2008Hello,
I am developing a smart device application with Visual Studio .Net 2005 and SQL Server Compact Edition database. And also using merge replication to synchronize the data from the mobile device to the SQL Server.
My database size is around 350MB. So when I am trying to synchronize this is the error message that I get.
" The database file is larger than the configured maximum database size. The setting takes effect on the first concurrent database connection only.[Required Max Database size ( in MB; 0 if unknown)=129].
I tried changing the Max database size in the connection string and my connection string looks as follows and still did not have any luck.
connstr= "Data Source=Storage CardItems.sdf;Max Database Size=500;"
Any help regarding this would be appreciated.
Thank you
.
Log File Not Reducing
Jan 2, 2001i used "dbcc shrink file" to reduce the log file of a database.the query analyzer says "successfully executed" but the log file doesn't seem to reduce..am i missing something?
View 2 Replies View RelatedReducing Log Space
Dec 4, 2000Hi all!
The transaction log of the databse i am using has grown up to 7GB...(previously the setting was unrestricted file growth...now changed to restrict file growth to 7 GB approx.)
now this 7GB space is not needed....i would like to reduce the size to around 2 GB...how can i achieve this?i observer that on the properties i can only increase the size and not decrease it...also i am using transactional replication..this server is the publisher to four subscribers..
Reducing 3 SQL Transactions To Just 1
Jan 9, 2008Hi guys, how are you?
Ive got a little question, i hope you can help me up
in sql server i have 3 tables
Code:
user
id_user - autoincremental int, primary key
username - varchar 30
car
id_car - autoincremental int, primary key
carName - varchar 30
user_car
id_user - int, foreign key
id_car - int, foreign key
what i want to do, is to
1.- insert a row in User,
2.- insert a row in Car
3.- using SCOPE_IDENTITY, to insert a new row in user_car that relates the ids of the last added car with the last added user.
But i need that to be done in one single transaction.
Once that is done, i have another question
how can i do the same but where i can add a variable number of different cars? in other words, i add 1 user and then add 5 cars, then create 5 rows in the user_car where i have the id of the last added user with the incremental id of the last 5 added cars
how can this be done in one single transaction, i cant make it in 3 transactions cuz it would cause me a lot of trouble. Any help?
my transaction should be something like this
Code:
insert into user(username) values('user1');
insert into car(carName) values('car1');
insert into user_car(???,???)
but im not sure how to do this in one single transaction
Reducing LDF Files
Apr 18, 2007Hi,
If I understand it correctly, you only need an LDF file to restore to a point in time after the last full backup? If this is so, then Could the LDF file not be reduced in size on perfoming a full backup?
Most of the time it's not an issue as there is enough space on the HDD, but is it possible to reduce the ldf file size periodically (manually would be fine). Is changing the recovery mode from FULL to SIMPLE and then back to FULL an option?
If so, is anyone able to tell me how, exactly, I can do this? ... I've sifted through the documentaion to no avail ... :eek:
Many thanks
Rob
Reducing Tempdb
May 8, 2007I am trying to reduce the size of my tempdb using the DBCC SHRINKFILE command and get the following error.
DBCC SHRINKFILE: Page 1:1164376 could not be moved because it is a work table page.
How can I get around this so I can shrink the db?
Reducing Number Of Extents
Jul 29, 2002Hi all,
I have started to look at the way our production DB has been defined and set up, with the view to improving performance.
The DB is now 11gb, and the original size was set up as 3000mb, the rest has been take in 10% additional extents.
Now, back in my DB2 DBA days, this was a bad thing to have any data spread across extents as they may not be contiguous. I am assuming that is the same with SQL Server. Can someone confirm/deny this?
If this is the case, how can I get the DB back into one primary partition?
Thanks in advance.
Mike
Reducing Redundancies In XML Output
Feb 9, 2012Having difficulty achieving an end-result in transforming the results of a rowset query into XML.
Here is simplified test code that displays my problem:
declare @TimesheetHdrs table (EmpID int,EntryYear smallint,EntryPeriod tinyint,AdminNotes varchar(max),UserNotes varchar(max))
declare @TimesheetDtls table (EmpID int,EntryYear smallint,EntryPeriod tinyint,ProjCode varchar(25),ActCode varchar(25),ExpendCode varchar(10),EntryDate date,EntryQty decimal(7,2))
declare @Projects table (ProjCode varchar(25),ProjName varchar(200))
[Code] ....
The result of the above code is the following:
Code:
<root>
<timesheet empid="1" entryyear="2012" entryPeriod="1" adminnotes="These are the admin notes" empnotes="These are the user notes">
<project projnum="TestProject" projname="The really big project for our best customer">
<activity actcode="000103020200302302322" actname="Demolish the 55th story of the main tower">
[Code] ....
Notice how there is a tremendous amount of redundancy in the XML. I was hoping to come up with an XML result of the following, which transmits the same data, without the redundancies.
Code:
<root>
<timesheet empid="1" entryyear="2012" entryPeriod="1" adminnotes="These are the admin notes" empnotes="These are the user notes">
<project projnum="TestProject" projname="The really big project for our best customer">
<activity actcode="000103020200302302322" actname="Demolish the 55th story of the main tower">
<expenditure expcode="1" expname="Regular Hours">
[Code] ....
Column Length Reducing
Feb 23, 2004The length of a column is 20(varchar),
When i m trying to execute select column name it gives all 20 characters.
My requirement is - is there any option by which i will be able to see only 10 characters ?
Reducing Reads Question
Aug 24, 2007I'm trying to insert all the rows from a table to a new table.
(insert A select * from AA)
The reads on Profiler shows ar really high value (10253548).
First I created a unique clustered index and the reads shows (3258445), then I created a non clustered index expecting to have lower reads. Instead the reads shows (10253548).
I read creating indexes helps reduce reads. But it's not happening.
Any ideas what is going on?
=============================
http://www.sqlserverstudy.com
Reducing Like Returned Data
Jul 20, 2005I am working on a personal project and am drawing a complete blank(too much celebrating last night?) on the SQL term that is used toeliminate multiples of like data when it is returned from thedatabase.ie, instead of ....redblueredgreenit would return ...redbluegreenSorry for the trouble and thanks.
View 2 Replies View RelatedHelp Reducing DB Performance Issues
May 19, 2008Hi all,
We currently have an e-commerce app written in .NET with SQL Server backend and built-in CMS that works just fine. We are now implementing a service to remove the need for the CMS by automating the synchronisation of the e-commerce database with a back-office database (non SQL Server). The problem we have run into is that during some of the larger updates to the website (i.e. new product information), the e-commerce system is experiencing timeouts. The synchronisation service uses transactions while performing updates and so I am assuming that the timeouts are being caused by the transactions locking tables and data.
What steps can I take to try to reduce these locks? The transactions are as short as possible so I do not think we can reduce the amount of processing each transaction deals with. I was looking at different isolation modes, Snapshot in particular, to reduce the locks, but would like some advice from someone who may have dealt with this type of situation before I start messing around here. (The synchronisation service uses the default ReadCommitted level, BTW)
Any advice you have to offer will be much appreciated.
Regards,
Stephen.
Reducing Failover Time
Jul 12, 2006Hi All,
Is it possible to reduce the automatic failover time on MS SQL 2005? It seems to take around a minute on my servers.
Thanks for your help.
Regards,
Automatically Reducing Precision On Numerics
Mar 26, 2008Hi all,
I'm running a transformation script that's taking decimal(18,10) data and trying to shoehorn it into a numeric(9,6). generally this works, as most of the data in the original table is not using anywhere near the precision it's capable of, but once in a while I run into one that does use it.
Is there any way to automagically reduce the precision so that i can cram the data into the destination table?
___________________________
Geek At Large