SQL Server, Log issues

microsoft.public.sqlserver.server - Microsoft SQL Server forum discussing administration and other SQL Server related topics. MS SQL Server is used for high performance and enterprise level applications.


SQL Server >> Log issues

by JOE » Tue, 14 Sep 2004 20:45:47 GMT

Hi All,
I have 2 issues with my log files.
1) I have a database where I combine 20 tables from 20
DBs into one very large table. Each table in the 20 DBs
are the same. My procedure uses 20 insert into
statements. For some reason my log file fills and the job
fails. Does anyone know how I can stop the file from
growing like this?

2) I created my Dbs with 400MB trans file. I only use
about 30MB. Is there a way to drop the size down to 50MB
and let it grow from there?

TIA
Joe

Top

SQL Server >> RE: Log issues

by JOE » Tue, 14 Sep 2004 21:09:05 GMT


Thanks for the info.
I will try the simple.
I do not delete but I do Truncate my table before I stert
the inserts. I Truncate because I know it does not hit
the Trans Log.

Joe


Top
Similar Threads

1. SSAS 2000 64 bit Query Log Issue

2. performance monitor counter log issue 

i've created a log w/ several sql server counters, e.g.,
sqlserver:buffer manager\target pages. they show up fine in the system
monitor, but when i run the log they don't show up. there are non sql
server counters that do appear.

i've tried unloddctr.exe mssqlserver/lodctr sqlctr.ini/reboot - but
that didn't fix anything.

any other ideas? thanks!

arthur

3. Transaction Log Issues - SQL Server

4. Restore log issue 

I ran the restore log statement and it does not return the status.  I used
sp_who2 to check the status and it said "sleeping".  I have to kill that
connection and continue on to the next log backup. If I rerun the restore
log for the same log backup file, it indicated the LSN already passed.  If I
run the restore for the next log, it is OK.

Is it a bug or am I do anything wrong with the restore statement?  Here is
what I did.

restore database Payroll from disk = 'D:\Backup\Payroll.bak' with
    move 'Payroll_Data' to 'F:\MSSQL\Data\Payroll_Data.mdf',
    move 'Payroll_Log'  to 'G:\MSSQL\Data\Payroll_Log.ldf',
    NORECOVERY
go
restore log Payroll from disk = 'D:\Backup\Payroll_TLog1.bak' with
NORECOVERY
go
restore log Payroll from disk = 'D:\Backup\Payroll_TLog2.bak' with
NORECOVERY
go
restore log Payroll from disk = 'D:\Backup\Payroll_TLog3.bak' with
NORECOVERY
go
restore log Payroll from disk = 'D:\Backup\Payroll_TLog4.bak' with
NORECOVERY
go
restore log Payroll from disk = 'D:\Backup\Payroll_TLog5.bak' with  RECOVERY
go

I got issues with log 2 & 5. The log 2 & 5 are small (20MB).  I did this
restore before with the same backup files on another server and it worked
perfectly.  When I did this on another SQL Server 2000 (Std Ed), I have
problem with getting the status back.

Please help.  Thanks!

5. SQL server SNMP/Event log issues - SQL Server

6. Transaction Log Issues

7. registration page logging issues with RS - SQL Server

8. unable to get into databases (trans. log issue??)... 

Hi, 


I think I know the reason why my updates were not 
working, but the previous process seems to have locked me 
out of being able to get into any of the databases.  My 
transaction log is huge, so that could be the problem.  I 
tried shrinking the database (which, as i understood 
it... was going to shrink the log), but that locked it up 
as well.  I tried detaching the database, but it says 
it's in use.

I did something along the lines of:

use master
sp_detach_db 'name'





I'm not sure how to "close" or kill processes, but even 
after I try to kill them... I still can't in.  I've had 
to delete and create a couple different databases in 
order to get back in.

So, the bottom line is the following:

1)  If a process is not going through and the transaction 
log has grown to immense size, and the memory is hogging 
thing up... how can I kill the processes in order to get 
back into the databases?

2)  I've changed a datatype from float to int on the 
fields that I was joining on (I didn't set them as 
float... they just came in that way).  I realize that 
float is not a good datatype to join on, but would 
it "stop" a process of updating a join of a couple 
millions records?  I'm thinking that the composite key in 
the other might be part of the problem, so I'm going to 
get rid of the composite key, and just index the one 
field that is being joined for the update.


I also recently uninstalled Oracle on my server... 
thinking it was the problem, but now I'm not sure.  But I 
sure didn't have any problems before installing it.

Any thoughts you may have would be appreciated!!

Rick