I 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!!
hi
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
If 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 Relatedi 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 RelatedI 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!
I 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,
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
Can 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
Hello. 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 RelatedAt 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
Hello,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 RelatedIn 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 Related
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?
Hi All,
I'm building an archiving applicaition for my small organization, but I have many file types such as videos, photos, texts, pdfs.... and some huge file sizes of about 500 MB.
My questions are:
1. Somefriend told me that there is a hardware compression device which can compress allready compressed videos and JPEGs. Is it correct? If so, where can I surf to find such devices?
2. Which datatype have I to use with SQL Server 2000 to handle all the different file types and sizes?
Thanks very much
Haytham
Hi,
We have a table of size greater than 200GB, so all the SQL is very slow.
Is there any way of doing table partition, If so How?
Is there any better way of archiving a portion of table used currently which can be restored later when is required, If so How ?
Thanks
John Jayaseelan
Hi everyone!
My problem is, that i don#t know how i can archive the data. That means to documentate when, who, etc. changed the data (in a seperate table).
I tried to solve it with different triggers.
Thanks in advance,
Maria.
Hi ,
I need to archive my production database to a new Server.....
Is it possible to move data using INSERT INTO ServerName.DBName.dbo.TableName from the current Database Server!!!
Do I need to create a linked server to do this....or shoud I go for DTS..
Thanks
Cheriyan.
What's the best archiving procedures? :)
SlayerS_`BoxeR` + [ReD]NaDa
Hi,
Actually i have done a program (vb.net) to archive the database.
Due to some policy issues, i not allow putting the EXE file into the database server.
So, i only can using the SQL Agent.
My question is:
(1) Since i already prepare set of program (vb.net), how can I put into the sql agent?
(2) I found some articles, SSIS can be done. How to do it?
How you all have a clear guide for me.
Thanks anyway.
I was wondering how i could use dts to import accessfiles and then archivethem to another folder. I've read some examples on sqldts.com but i stillcan't figure it out.Basically this is what i want:- import an accessfile which has a name like this <companyname>_<today'sdate>.mdb. The importfolder is called in my case d:import- for updating some fields, the dts should use the company's name from theaccessfile and check that with a lookuptable to translate the name into aint value.- after some processing, the dts should place the file to another folder. Inmy case it is d:archiveIf someone can help me with this, i would be very greatful.------------------------------------------------------This mailbox protected from junk email by MailFrontier Desktopfrom MailFrontier, Inc. http://info.mailfrontier.com
View 2 Replies View RelatedHello,
I have current events going to a log, and I'm implementing partitioning it into weeks using the following function...
CREATE PARTITION FUNCTION [trackPointLogWeekPF](int)
AS RANGE LEFT FOR VALUES (7, 14, 21, 28)
and in the table create I add an extra field of day number to pass to the function...
[intPartitionDayNum] AS (datepart(day,[dtTrackPointTime]))
So if that's all for the current month, is it possible to have monthly partitions for the older data so that I could drop off a month from a year ago for example or would I need to keep it weekly?
Thanks for any help.
Hi,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 RelatedI've got two tables, one is an archive of the second (tables are identical). I'd like to migrate records from one to the other (as in, move, insert into one while deleting from the other).
I know I can start a transaction, do an INSERT INTO...SELECT, followed by a DELETE, check rows affected, then closing with a commit transaction (or rollback if the counts don't match), but it seems as though I might be over thinking it. Is this considered the optimal approach?
The Database will hold 2 tables. One of those includes dates. They are joint by a constraint using an ID. What i got to do is, store the tables in a different schema named after the month the data was created. I will have to keep the original tables aktive because this should work while accessing the tables but can flush the data to keep the database small. So i would end up with 2 tables in 1 schema for every month and the productive that keeps track of the current bookings.
I would have access to the enterprise edition if that changes anything at all.
Hello,Does anyone know of a way to schedule the archiving of analysisdatabases? Seems pretty lame if you can't... The only answer I've gottenis "maybe in Yukon"....Thanks.*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Relatedi 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 RelatedHi 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..
Hi 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
Hi,
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
I 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?
Hi all,
Would anyone be able to point me in the right direction for creating a procedure to archive rows using a DATE field value as the determinant for selecting the rows to be moved to a backup table?
I'd really appreciate any assistance that anyone can provide.
Thanks,
Tom C.
How do I put data into a text or excel file before I attempt a deleteion from a large table. I know how to select the necessary data, but i'm not sure about the t-sql required to put it into a file?
are there any better methods of archiving?
thanks
Hi all,
i am a student doing my final year project and i have the duty to develop a automated part-archiving for our MsSql Server 2000 Database, but its not so simple :eek: .
the environment:
- MsSql Server 2000
- Suns App-Server Glasfish
- Java (J2EE)
- Hibernate
the requirements:
1.)
Some tables should be synchronized and redundant (the same data in both databases)
2.)
If the maindatabase is changing the schema the archivedatabse must do the same changes.
3.)
The datas in some tables (the ones that aren't synchronized) which are older then 2 Years should be moving (copy, paste) to the archivedatabase every 24 hours.
4.)
If there are is a access for a data which are not in the maindatabase, it is necassary to get it from the archivedatabase.
I don't find a way to realise this ...
can some one give me a hint?
... Sorry for my bad english, i hope i explained my problem good enough.
Thanks for ur answers