space problem

[Former Member]

I have problem

I am using mssql2005/win2003 ecc6. the problem is a space problem.

Namely someone (probably network administrator) switched off backup of transaction log.

I have only 500 Mb free(all is on drive c: as we are speaking aout a small system).

35G of transaction log shold be cut off.

1.

I have heard it is possible to detach database , delete transaction log and attach database.

If so that is no problem(how can I see the database is up and running- assuming this is prerequisite)

2.

Is it possible to backup transaction log on an mapped network disk?

3. do yo have an other idea?

[Former Member · Accepted solution]

Try changing DB to simple mode (but this will break your tlog target DB, which means you have to re-establish your log shipping target DB all over again). Once DB is in simple mode, cycle the SQL engine, then perform the truncate mentioned above. This should bring your LOG DB down in size.

Once LOGDB is small again, change DB to full mode again and configure tlog shipping.

[former_member193399]

try this..

step1: use

sp_helpdb

to get the file no of the transaction log.

step 2: truncate the unused log space:

use <dbname>
DBCC SHRINKFILE (<file no>, 2024, TRUNCATEONLY); --truncate to 2gb

step 3: check log size:

sp_helpdb <dbname>

if it not reduced to desired space then repeat step1, step2 & step3

Make sure you backup the database as soon as you recover the database size and perform periodical log backup using SQL server maintenance plan.

-RT