Re: 1105 - log file getting full - SQL Server 6.5
From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 06/18/04
- Next message: Umut Nazlica: "RE: How do people use SQL in Win authenticated mode only?"
- Previous message: Wayne Snyder: "Re: Sql Server:communication link failure"
- In reply to: Mike NG: "1105 - log file getting full - SQL Server 6.5"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 18 Jun 2004 08:15:28 -0400
Follow Hari's instructions, backup the transaction log on a regular basis,
especially when doing large deletes..
Additionally, In direct answer to your question, Backup/restore will do
nothing to help you in this..Don't waste your time...
-- Wayne Snyder, MCDBA, SQL Server MVP Mariner, Charlotte, NC www.mariner-usa.com (Please respond only to the newsgroups.) I support the Professional Association of SQL Server (PASS) and it's community of SQL Server professionals. www.sqlpass.org "Mike NG" <sweepdog@deadspam.com> wrote in message news:Y+4LLABXXi0AFwb4@ntlworld.com... > I am getting an error along the lines of > > Can't allocate space for object 'Syslogs' in database ''corp'' because > the > 'logsegment' segment is full. If you ran out of space in Syslogs, dump > the > transaction log > > when trying to delete data from 'corp' (no it's not the tempdb problem!) > > I' increased the size of the logsegment and tried doing something along > the lines of > > SET ROWCOUNT 10 > go > while (select (count(*) from table) > 0 > begin > delete from table > end > > to break the task down which did work. However about a couple of weeks > on, I need to do some more deletions on the table, and the log is full > again - even doing the broken down version above. I could increase the > log file again a bit, but a) I need to stop this happening, and b) there > isn't much disk space left. I have no control over the disk purchases > and I think pretty much all the data is needed > > I can't do truncate because replication is turned on and it'd be a right > pain disabaling all the tables I need to do and then turning them back > on again > > I've done DUMP transaction corp with no_log but this either fails or > doesn't recover any space. DBCC Checktables didn't do a right lot > either (excuse me for my vagueness, but my notes are at work and I need > a solution for Saturday morning) > > I have tried putting the database into single user modem but this has > always failed since I haven't been the exclusive user of it. I doubt > very much that it will work if I tried it at the weekend > > > So someone has suggested doing a backup of corp and restoring it again. > Is this going to work. The database has separate data and log devices > (rather than shared). Any thoughts on whether this is going to work > would be appreciated. Did I remember seeing an option somewhere that > would select whether the log file would be copied or not, or was I > dreaming. > > Any help greatly appreciated > > Many thanks > -- > Mike > Please post replies to newsgroup to benefit others > Replace dead spam with ntl world to reply by email
- Next message: Umut Nazlica: "RE: How do people use SQL in Win authenticated mode only?"
- Previous message: Wayne Snyder: "Re: Sql Server:communication link failure"
- In reply to: Mike NG: "1105 - log file getting full - SQL Server 6.5"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
