How To Accelerate The Insert Rate And Reduce Transaction Logs

Jan 10, 2007

SQL Server 2005
XEON CPU 3.0G
MEMORY 2.0G
RAID

Tow tables:
HIS_HTTP_ONLINE_LOG(PARTITION)       FOR HISTORY DATA
REL_HTTP_ONLINE_LOG(NOT PARTITIONED) FOR EVERYDAY DATA,
AND THEY HAVE THE SAME STRUCTURE

CREATE TABLE HIS_HTTP_ONLINE_LOG(
ID numeric(20,0)  NOT NULL,
USERID varchar(32) NOT NULL,
USERIP varchar(16) NOT NULL,
USERPORT numeric(10, 0) NULL,
OBJECTIP varchar(16) NULL,
OBJECTPORT numeric(10, 0) NULL,
HTTPURL varchar(256) NULL,
HTTPHOST varchar(128) NULL,
HTTPDNS varchar(128) NULL,
VISITIME numeric(10, 0) NULL,
STARTIME datetime NOT NULL,
ENDTIME datetime NOT NULL
).......

SELECT * INTO REL_HTTP_ONLINE_LOG
SELECT *
FROM HIS_HTTP_ONLINE_LOGWHERE 1=2

There are 5 indexes in HIS_HTTP_ONLINE_LOG ,
There is not one index in REL_HTTP_ONLINE_LOG

There are about 5000,000 records in REL_HTTP_ONLINE_LOG everyday,
at night it will move into HIS_HTTP_ONLINE_LOG automatically,
The data of everyday in REL_HTTP_ONLINE_LOG will be last 90 days.

My operations:
1: ALTER DATABASE DB SET RECOVERY SIMPLE
2: EXEC SP_DBOPTION DB, 'select into/bulkcopy', 'TRUE'
3:INSERT INTO REL_HTTP_ONLINE_LOG
   SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=2
4: TRUNCATE TABLE REL_HTTP_ONLINE_LOG

ASK:
why the step 3 cost so much time ? (about 1 hour)
 and how can I reduce the transaction logs in this period ? 
 
Could you give me some suggestions ?
Thanks!

View 1 Replies


ADVERTISEMENT

How To Accelerate The Insert Rate And Reduce Transaction Logs

Jan 10, 2007

SQL Server 2005XEON CPU 3.0GMEMORY 2.0GRAID
Tow tables:HIS_HTTP_ONLINE_LOG(PARTITION)       FOR HISTORY DATAREL_HTTP_ONLINE_LOG(NOT PARTITIONED) FOR EVERYDAY DATA,AND THEY HAVE THE SAME STRUCTURE
CREATE TABLE HIS_HTTP_ONLINE_LOG(ID numeric(20,0)  NOT NULL,USERID varchar(32) NOT NULL,USERIP varchar(16) NOT NULL,USERPORT numeric(10, 0) NULL,OBJECTIP varchar(16) NULL,OBJECTPORT numeric(10, 0) NULL,HTTPURL varchar(256) NULL,HTTPHOST varchar(128) NULL,HTTPDNS varchar(128) NULL,VISITIME numeric(10, 0) NULL,STARTIME datetime NOT NULL,ENDTIME datetime NOT NULL).......
SELECT * INTO REL_HTTP_ONLINE_LOG SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=2
There are 5 indexes in HIS_HTTP_ONLINE_LOG ,There is not one index in REL_HTTP_ONLINE_LOG
There are about 5000,000 records in REL_HTTP_ONLINE_LOG everyday,at night it will move into HIS_HTTP_ONLINE_LOG automatically,The data of everyday in REL_HTTP_ONLINE_LOG will be last 90 days.
My operations:1: ALTER DATABASE DB SET RECOVERY SIMPLE2: EXEC SP_DBOPTION DB, 'select into/bulkcopy', 'TRUE'3:INSERT INTO REL_HTTP_ONLINE_LOG    SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=24: TRUNCATE TABLE REL_HTTP_ONLINE_LOG
ASK:why the step 3 cost so much time ? (about 1 hour) and how can I reduce the transaction logs in this period ?   Could you give me some suggestions ?Thanks!

View 5 Replies View Related

How To Reduce Transaction Log Size?

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

How To Reduce The Transaction Log File

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

Reduce The Transaction Log File Size

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

How To Reduce Size Of Transaction Log File (*.ldf)

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

MS SQL 2000- Reduce Transaction Log Size

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

Unable To Reduce Transaction Log File Size

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

T-SQL (SS2K8) :: Reduce Locking For Insert Statement?

Sep 10, 2014

There are 2 tables which need to have data inserted into them for auditing purposes. The number of inserts per minute seems be at least 50-100. How to reduce locks during inserts.

There are 2 tables

Table1
ID - Surrogate Key/identity Column
SomeColumn1
SomeColumn2
SomeColumn3
SomeColumn4_timestamp

clustered index on ID column

Table2
ID Column ..... there's a call to get id from SCOPE_IDENTITY()
SomeColumn1
SomeColumn2
clustered index on ID column
NC idx on SomeColum1
NC idx on SomeColum2

A Sproc has the following code:

I changed the names to protect the innocent

CREATE PROCEDURE [dbo].[logging_sp]
@Audit BIGINT,
@varT1_1 NVARCHAR (50),
@varT1_2 NVARCHAR (64),
@varT1_3 INT,
@VarTime DATETIME,
@varT2_1 NVARCHAR (50),
@varT2_2 NVARCHAR (1024),

[code]....

View 5 Replies View Related

Transaction Logs

Apr 24, 2002

What is the best way to clear the transaction logs. My backup job each night is ending because it says it is running out of disk space and I need to clear up the transaction logs. Any help is appreciated. I see many different options (trucate option, auto shrink, etc.), just need some assistance tosome more specific best approaches. Thanks

View 3 Replies View Related

Transaction Logs

Aug 13, 2001

Im having issues truncating my transaction logs. I have logs in excess of 40 gigs. All the info in the BOL is very vague. Any assistance would be apreciated.

Regards

SeTi Ni

View 5 Replies View Related

Transaction Logs

Jun 22, 2000

When we backup the transaction log,will it truncate the log or it will grow from there?Truncate log on check point is desabled.
Thanks!

View 2 Replies View Related

Transaction Logs

Aug 30, 2000

to take the transaction log back up regularly I should have truncate log on check point false. If I do so then how will I truncate the log.
regards,
Renu

View 3 Replies View Related

Transaction Logs

Aug 20, 1998

I am trying to import records via bcp (about 1,500,000 records) and I keep running out of disk space. Is there any way to limit or do away with the transaction log (and still be able to import)?

View 2 Replies View Related

Transaction Logs Help!!!!!!!!!

Dec 14, 1999

how do i decrease the transaction log example 500mb to 100mb, can you help me with the syntax pls!!

View 1 Replies View Related

Transaction Logs

Sep 2, 2004

I need to move transaction logs to a different drive and am looking for a way to do it without detaching the db.

Can I create a second transaction log file for the DB and then delete the original log file?

What is the best way to do this for db's that are being replicated?

Thanks,
Ken Nicholson

View 3 Replies View Related

Transaction Logs

Jun 13, 2002

I need to seperate my SMS data files and transaction logs onto two seperate drives. Can anyone assist me with this?

thanks...

View 1 Replies View Related

Transaction Logs

Aug 2, 2004

HELP!!!!

When I look at the Database maintience plan history entry for backup I have a message that reads: "Backup can not be performed on this database. This sub task is ignored".

Have anyone come across this error before?? As part of the Maintenance plan some transaction log are being backup and some aren't instead they receive the message above.

Thanks

Lystra

View 2 Replies View Related

Transaction Logs

Nov 19, 2004

Has anyone ever had the problem of the IT department not backing up thier transaction logs simply because they didnt find it necessary??

View 3 Replies View Related

Transaction Logs

Jan 8, 2004

i have several sql servers doing maintenance plans and backing up the transaction logs to tape. unfortunately it seems that the server keeps adding the transaction logs to the same tape, without overwriting them. Nowadays a transaction log backup to disk takes 2 minutes but when done to tape it is taking up 1hr54minutes. What can i do so that the tape is automatically initialised without having to do it manually... Thanks

View 1 Replies View Related

Transaction Logs

Apr 9, 2008

i was wondering if smeone could give me a breif over view how far back do these go

View 12 Replies View Related

Transaction Logs And LDF

Jun 11, 2008

HI All,

Is this is a good idea or not?

If i attached my SQL Server to SAN only for DATA and LOG (Ldf) drives
through the FC. then we put *.bak and *.trn on to local disk RAID 1 drive?

Any performance isues?

View 3 Replies View Related

Transaction Logs

Dec 7, 2007

Is there a way to view the transactions from a .TRN transaction log file? If so can I overwrite some of the transactions on the file and then restore from it? I am just curious.

View 2 Replies View Related

Transaction Logs

Mar 11, 2007

Hi, I know just about nothing about SQL Server. I am getting this error:The log file for database 'my_database' is full. Back up the transaction logfor the database to free up some log space.I can't access the transaction logs to back them up. I am told that my ISP'stech support should have it set up to shrink those logs automatically everyso often. Is that true?Why are they needed? Up till Monday, the logs are just of our getting SQLServer set up, so couldn't the logs just be deleted? Most of my site can'trun with this problem.I'd appreciate any enlightenment anyone can give me!Thanks, Jill

View 1 Replies View Related

Transaction Logs

Jul 20, 2005

This seems like it'd be a really stupid question, but for the life of me Ihaven't been able to find an answer that works....I have a database that's approximately 400MB when shrunk... and thetransaction logs are at about 4.8GB when shrunk... I can't seem to get thetransaction log any smaller, no what I try. How can I get the log downbelow 1GB and keep it there? I've only got an 18GB hard drive and I need torun 2 copies of this database.-steve

View 4 Replies View Related

Reading Transaction Logs?

Jan 8, 2008

Hello,
I am trying to figure out the time a certain store procedure  was executed. I know the SP's name and approximetly the time it was executed. Is this possible to do?
Honestly, i am not sure if SQL server 2005 is smart enough to keep track of commited transaction on the server.
 
Any suggestins?
Thanks

View 2 Replies View Related

SQL 7.0 Transaction Logs Won't Shrink!!!

Jun 5, 2002

I have seen quite a few people post this type of problem, but I am finding few solutions. Your advice and/or experiences are greatly appreciated.

Here's my scenario:

Environment: Windows NT 4.0 SP 6, SQL 7.0 (set up for Transactional Replication)

Problem: We have several remote dB machines configured for full recovery. On these machines are several dB's that are capturing aprox. 280 data points per second per unit. (Each dB represents one unit, and we have 21 units) No problem here. The problem is the transaction logs, that obviously grow profusely, will not SHRINK after, backups, dbcc commands and TSQL has been issued in failed attempts to shrink the logs. (in other words we've tried everything)

My questions are:

1. Because we are replicating, is it absolutely necessary to configure dB's for FULL recovery? How do I check in 7.0 if the dB is certainly in FULL vs. SIMPLE recovery mode?

2. I work with SQL 2000 and shrinking files is no problem. How can we shrink these log files in SQL 7.0?

It is rather urgent I find a solution as we are running out of hard drive space on our remote machines. Please help :-(

Christine

View 1 Replies View Related

Truncating Transaction Logs.

Jun 7, 2001

I am having trouble Truncating a Transaction Log. I`ve tried everything in Book Online.
I`ve backed up the database, I`ve tried DBCC SHRINKFILE, DBCC SHRINKDATABASE, BACKUP LOG TRUNCATE_ONLY ...etc, but it will not shrink. Any suggestions ? Thanks.

View 1 Replies View Related

How Can I Move Transaction Logs?

May 4, 2000

Currently, my databases and their corresponding transaction logs are all on the same disk array. I finally was able to acquire a seperate disk, specifically to seperate the logs, in case of failure, etc.
Now, I need to figure out how to go about moving the tansaction logs off the current disk array and on to the new disk.
In Enterprise Manager, I brought up "Properties" for the first database, and went to the "Transaction Log" tab. Clicked on the "browse" or "..." button in the location field and got the following message:
"A transaction log file's physical name cannot be changed once the transaction log file has been created". :(

I have been looking through Books On Line, but have been unable to find anything helpful yet.

Can anyone help me figure out how to go about moving a transaction log's location? There has to be some way.
Even if it involves shutting down the server, altering system tables, etc. I need to get these moved.

Your help is appreciated. Thanks.

View 2 Replies View Related

Help !!! None Of My Transaction Logs Are Getting Backed Up

Jan 19, 2001

Hi all,

I have set up a maintenance plan to backup my databases but when I view the maintenance plan history the transaction log backup steps have a success tick but a message saying :

"Backup can not be performed on this database. This sub task is ignored"

I have looked in my backup directory and only see *.BAK files and no *.TRN.
The transaction logs are supposed to back up at 1AM and the databases at 2AM

All my databases being backed up have the truncate log on checkpoint option set. Is this best practise according to my backup schedule ?

Help !!!!

Regards,

Tim

View 4 Replies View Related

Moving Transaction Logs

Jun 15, 2000

Is it possible to move .ldf's to another drive. For example I would like to move a database transaction log file from C:mssql7data to D:mssql7logs.

How can this be done?

TIA!
JJ

View 1 Replies View Related

Restoring From Transaction Logs.

Aug 28, 2000

Hi all,

I am restoring database from Transaction Logs. I followed all the steps
mentioned in the book. Just before the last step there are three options
1. Leave Database Operational. No Additional Logs can be Restored.
2. Leave Database Nonoperational But Able to Restore Additional
Transaction Logs.
3. Leave Database Read_Only And Able To Restore Additional Transaction Logs.

Option 2 and 3 aresupposed to set on the NORECOVERY flag.

I tried both options 2, and 3 , But still got messages that I did not
specify WITH NORECOVERY or WITH STANDBY.

Can anyone please tell me what I am doing wrong?

Thanks in Advance.

Attaullah

View 2 Replies View Related

Emptying Transaction Logs - SQL 6.5

Jul 11, 2000

I am currently having a problem with a transaction log not emptying even when backed up and truncated. I have done a full database backup and then a transaction log backup expecting this to flush the log however the log is not emptied and it is growing larger and larger. Even when I truncate the log it still doesn't free any space up. Can anybody out there spot the fundemental error in my working??? most grateful. Andy (SQL allegedly)

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved