MS SQL 2K problem *urgent*

Beëlzebooze

Hi,

I have recently taken over the admin of a small company, we have an SQL db running our cust queries.

Now the database has been set up with full transaction logging, and as you can imagine the log fills up pretty quick, It transpires that the database has not been backed up in a while, so the transaction log has not been truncated, it has now grown to ober 6GB in size.

I have 10MB left on that particular disk!!!!!!!

I believe that I can only free up space after a backup has been processed, but I have no way of making a backup, I have no tape drive or enough free space to make a backup to disk.

Is there any way to get this transaction log size down before the database drive fills up and dies on me?

shurl

Sorry Beëlzebooze,
I'm not to sure without actually sitting down and havin a look.

but did ye try doing a search on MS Technet?
http://www.microsoft.com/technet

Its usually pretty good. Got me out of a few jams in the past.


Sorry not much help

Simon

shurl

Actually, re-reading your post.

Any chance you can stick another drive into the server and transfer the logs to it?

Simon

Beëlzebooze

/me checks breast pocket

no, no spare lvd drives handy

The only options I have are to either BACKUP or DUMP the transaction log, both require space......

still scouring the net for an answer.

thanks anyway, and if you come up with something else, please post!

davej

How about this:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&safe=off&threadm=9va90n%24hp%241%40newsg3.svr.pol.co.uk&rnum=2&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26safe%3Doff%26q%3Dtransaction%2Blogging%2Bspace%2Bsql

davej

Beëlzebooze

yeah,

I tried the dummy table trick, but no joy.

I didn't know about the dbcc loginfo command though, and it shows alot of uncommitted transactions in the log, so that is why I cannot truncate.

I am also quite hesitant to do anything radical, as this is the production database, and we have no backup *sigh*

b.t.w. the office manager suggested that I just go into explorer and delete the log file :rolleyes:

I'll create a dummy db and see what can be done,

thanks so far with this guys,
and as I said all other suggestions are more than welcome........
(this might become a dealclincher at review time, and I'll buy yis all a pint!)

ButcherOfNog

Before you try anything, u need to backup the DB

Take a HD from somewhere, another office pc, and stick it in the server. Or purchase a tape backup unit and backup the DB onto that. Anything like, just back it up first

Beëlzebooze

I have a backup of the datafile, but the transaction log is too big to go anywhere, I have brought that database offline, and have tried to copy the transaction log to another machine, 6.5GB of data ffs... it goes all the way to 10sec remaining, then jumps to 321000 minutes remaining.


I have moved the datafile to a differenmt location and re-attached the database, after the usual compliants about not finding the log, it has created a new one, we are now in the process of testing it.

if the database throws a knickerfit, the powers that be might be considered it a valid reason to buy a backup device!

Thanks for the suggestions lads,
I owe yis all a pint!

shurl

Cool,

Have to agree on the Backup device though.
Prob one of THE most important devices for IT.

Rebuilding a SQL database file from pieces is NOT for the faint hearted although not as bad as an Exchange server database.
Thats a whole different nightmare altogether.

//Simon remembers back to spending 25 hours straight fixing exchange just because of ONE dodgy email attachment.//


Simon