how to truncate log

Hi there,
I use SQL 2000 and the log of a database is very big(I have full recovery).
I tried backing it up with Veritas BackupExec in order to truncate it, did
not work.
What should I do?
Thanks a lot, CalinRun DBCC OPENTRAN ('yourDB') to see whether you have an
active transaction that prevents the log from being
truncated.
Linchi
>--Original Message--
>Hi there,
>I use SQL 2000 and the log of a database is very big(I
have full recovery).
>I tried backing it up with Veritas BackupExec in order to
truncate it, did
>not work.
>What should I do?
>Thanks a lot, Calin
>.
>|||There is a difference between truncating the log and shrinking the file
size. A normal log backup will truncate the log, freeing internal space,
but the file size will remain the same. You'll need to use DBCC SHRINKFILE
to reduce the transaction log file size.
Jim
"Tester" <CalinGuga@.netscape.net> wrote in message
news:%23E8xJUkzDHA.2412@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> I use SQL 2000 and the log of a database is very big(I have full
recovery).
> I tried backing it up with Veritas BackupExec in order to truncate it, did
> not work.
> What should I do?
> Thanks a lot, Calin
>|||I don't know about Veritas BackupExec but I do have some
knowledge of SQLServer backup. When you do a full DB
backup using SQLServer backup, the transactions which are
in the log file and which have been marked as commited
are deleted from the log file. Then you can run DBCC
SHRINKFILE and it will work.
If you have Auto Shrink checked on the database
properties, then the SQLServer will shrink the size of
the log file over time after a full DB backup. This is
the advantage of using a maintenance plan backup in
SQLServer.
If you are in a super hurry to shrink the log file, do
the following:
1. Make sure you are not publishing using Transactional
replication.
2. Make sure there are no open transactions by running0
DBCC OPENTRAN.
3. Make sure there are no remote server links to this
database.
4. Make sure you know what maintenance plans are using
this database because you will probably have to rebuild
them.
5. Detach the database.
6. Manually delete the log file in Windows Explorer.
7. Attach the database which will build a new, 504 KB,
log file.
8. Rebuild your mainenance plans.
>--Original Message--
>Hi there,
>I use SQL 2000 and the log of a database is very big(I
have full recovery).
>I tried backing it up with Veritas BackupExec in order
to truncate it, did
>not work.
>What should I do?
>Thanks a lot, Calin
>.
>

How to track movement between departments (calculate retention)

Hello,

In order to do a retention calculation I need to track the employees from month to month.

So let's say I want to find the retention for a department. I need to know who started in that department (at the beginning of the year) and if they are still in that department (at the time of the query).

So if there were 100 employees and now there are 90, obviously the headcount is down by 10 people. However, it could have been down by 20 people but 10 more were hired back in. So 20 people left that department.

If this calculation was just a headcount one, it would be easy to calculate. But I need to track each person.

Is there a methodology that anyone is aware of that would allow me to compare the beginning group to the current group and count who has left, by employee?

This can get much worse because my leadership asks for retention by department or manager or geographic area.

Thank you for the help.

-Gumbatman

If you are tracking just counts, no measures are associated, then one of the options:

Create snapshot for initial load of employees. This could be done just once, or yearly or in some cases even monthly.

For each change create record with -1 for exit and +1 for entry. This should be done in SQL server, so your loads into SSAS are simple.

That is if employee changed department, there is record for same day key for old department with -1 as measure and then +1 for new department.

Same rule if employee changes more than 1 attribute at the same time (department + manager) : record for exit with old attributes and record for entry with new attributes.

Sum of records from start snapshot to any day will give you who is in department that day.

This should be enough to track each employee.

Lets say you have 100 employees in HR. You create 20 exits, 10 entries. At the end you have sum of 90, all accounted. Plus, you can filter by any other attribute and have correct results.

Vidas Matelis

|||

Vidas,

That is a pretty cool method, I need to work through it a little more the get my head around it.

Thank you for the help!

-Gumbatman