Big Log file that won't shrink....

I have a fairly simple DB, One Log file, one data file, its got half a dozen tables in it that are used for a web based app that allows users to look each other up (think online telephone directory with a few extra bits and you are there), there's a data feed that adds new lines each day as users data changes or new people get added. But this is a couple of hundred or so lines a day, the tables hold about 40,000 data lines as we never delete data just retire it.

The problem is the main data file is about 200Mb, the Log file however is 3.8Gb. Now we did import and re-import various bits initially and clear down tables and such. But I don't understand why i can't shrink the log file. I've tried everything I can think of. I've even detached and re-attached the DB. Still no joy.

The file size/usage is

data file 200Mb size in use 117Mb

log file 3749.99Mb size in use 3719.89Mb

Anyone have any suggestions for things to look at?

many thanks

Steve

Hi,

look here:

http://www.aspfaq.com/show.asp?id=2471

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

bidirectional snapshot replication?

I currently have a list of users in a SQL 2005 database. Users are
authenticated based on their email address, and each user has a number
of areas they do and don't have access to. A website is used to add
users to the database, or to add area permissions for an exisiting
user.
I would like to set up a number of replicated servers around the world.
At least one would have very high latency and very low bandwidth to it.
All Sites are connected by VPN.
My question is: Is there a way to do bidirectional replication between
all of the servers? All servers would ideally be both publishers and
subscribers.. If so, which type of replication would you use? I was
thinking snapshot replication twice a day, but that may not make
sense..
Thanks in advance..
Have a look at peer-to-peer transactional replication - this sounds like
what you require.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

BETWEEN clause & <= operators

Hi ,
In a stored procedure when retrieving records based on a DATETIME values in WHERE clause - can we use BETWEEN clause or Col <= AND Col >= ?
Please suggest which is the optimised way.
Thanks in Advance,
Hari Haran ArulmozhiThey are both the same. The optimser comverts BETWEEN to >= and <= anyway. Depends on what you prefer to type\ read. I like BETWEEN as I don't have to check if there is a >= to correspond with any <= I find.

HTH|||optimized approach for datetime ranges involving two dates is actually to use something like this --

where datetimecol >= '2006-08-09'
and datetimecol < '2006-08-11'this returns all datetimes for the 9th and the 10th

using BETWEEN you have two choices -- code the upper value as '2006-08-10 23:59:59.999' (clumsy) or code the upper end as '2006-08-11' (and risk getting a row from the 11th at midnight)

Better way to do a large delete?

I have to do a delete from a table with about 25 million rows. I need to
delete about 4 million rows, based on the date in another table
Table Session
SessionID SessionDate
1 1/25/2004
2 7/25/2001
I have created a view (SessionView) containing all sessionid's where the
sessiondate less than 3 years old.
(Create view SessionView As Select SessionID From Session Where
Session.sessiondate >= getdate() - 1095)
Table Claims
ClaimID SessionID etc etc
etc
24578 1 15
HealthPractice 5554441234
6548975 2 52 ChirocPract
5551234567
What I need is to delete all rows from Table Claims that have sessionids in
SessionView.
Delete From Claims Where Claims.sessionID In (Select SessionID from
SessionView)
-In this case ClaimID 6548975 would be deleted but ClaimID 24578 would not.
But I must be doing something wrong (I am very new at SQL Server, and have
never done a large scale delete that wasn't just straight forward), because
the delete seems to take forever. Last time I had to stop it at just over 5
hours because the network admin needed to do some work on the server and
reboot it.
I have set the recovery to simple, and the server has no other active
databases or other applications running on it.
Any assistance appreciated.
Nancy Lytle
N_Lytle@.terplaum.umd.eduyou could also copy the rows you want to keep to another table, drop
the orginal one, and rename the copy|||nancy,
try deleting in smaller chunks, say 1000 rows each. this should help.
dean
"Nancy Lytle" <word_diva@.hotmail.com> wrote in message
news:er8ntEsIGHA.2668@.tk2msftngp13.phx.gbl...
> I have to do a delete from a table with about 25 million rows. I need to
> delete about 4 million rows, based on the date in another table
> Table Session
> SessionID SessionDate
> 1 1/25/2004
> 2 7/25/2001
> I have created a view (SessionView) containing all sessionid's where the
> sessiondate less than 3 years old.
> (Create view SessionView As Select SessionID From Session Where
> Session.sessiondate >= getdate() - 1095)
> Table Claims
> ClaimID SessionID etc etc etc
> 24578 1 15 HealthPractice
> 5554441234
> 6548975 2 52
> ChirocPract 5551234567
> What I need is to delete all rows from Table Claims that have sessionids
> in SessionView.
> Delete From Claims Where Claims.sessionID In (Select SessionID from
> SessionView)
> -In this case ClaimID 6548975 would be deleted but ClaimID 24578 would
> not.
> But I must be doing something wrong (I am very new at SQL Server, and have
> never done a large scale delete that wasn't just straight forward),
> because the delete seems to take forever. Last time I had to stop it at
> just over 5 hours because the network admin needed to do some work on the
> server and reboot it.
> I have set the recovery to simple, and the server has no other active
> databases or other applications running on it.
> Any assistance appreciated.
> Nancy Lytle
> N_Lytle@.terplaum.umd.edu
>|||Another thing could be to drop all indexes, delete your rows, recreate
the indexes.
Its all about how much you can do with the table. Do you have a
maintenance window where no one are using the database or is it always
online?|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:580414
Nancy Lytle (word_diva@.hotmail.com) writes:
> I have created a view (SessionView) containing all sessionid's where the
> sessiondate less than 3 years old.
> (Create view SessionView As Select SessionID From Session Where
> Session.sessiondate >= getdate() - 1095)
>...
> What I need is to delete all rows from Table Claims that have sessionids
> in SessionView.
> Delete From Claims Where Claims.sessionID In (Select SessionID from
> SessionView)
> -In this case ClaimID 6548975 would be deleted but ClaimID 24578 would
> not.
> But I must be doing something wrong (I am very new at SQL Server, and
> have never done a large scale delete that wasn't just straight forward),
> because the delete seems to take forever. Last time I had to stop it at
> just over 5 hours because the network admin needed to do some work on
> the server and reboot it.
Rather than creating a view, insert all the sessionids to delete into
a temp table.
You could also try this syntax:
DELETE Claims
FROM Claims c
JOIN #temp t ON c.session_id = t.session_id
If Claims does not have its clustered index on SessionId, it could be
worth to do
CREATE CLUSTERED INDEX session_id_ix ON Claims(session_id)
WITH DROP_EXISTING
for this task, and then restore the original index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks, I was trying to avoid that because I actually have about 10 tables
that I have to handle in a similiar fashion, and copying was also going
slowly.
I think I will end up using Dean's suggesting and chunking it out.
Thanks,
Nancy
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1138312336.363053.109410@.o13g2000cwo.googlegroups.com...
> you could also copy the rows you want to keep to another table, drop
> the orginal one, and rename the copy
>|||I think this is the way I will have to go, so I will just use smaller date
intervals, until I get everything deleted.
Thanks,
Nancy
"Dean" <dvitner@.nospam.gmail.com> wrote in message
news:OvslVLsIGHA.2828@.TK2MSFTNGP12.phx.gbl...
> nancy,
> try deleting in smaller chunks, say 1000 rows each. this should help.
> dean
> "Nancy Lytle" <word_diva@.hotmail.com> wrote in message
> news:er8ntEsIGHA.2668@.tk2msftngp13.phx.gbl...
>|||When bulk deleting 4 million rows, the slowness you are experiencing
probably has more to do with transaction logging than anything else. I
assume this must a one time purge, but if it is a recurring process, then
you will probably want to reconsider your data model and workflow.
Start by dropping any indexes from the table that would not assist in
qualifying the rows needed for deletion. For example, if you are deleting
based on transaction date, then you would not need to retain an index on
LastName or AccountType. This will reduce the amount of transaction logging
during the process, and besides, all the indexes will need to be
defragmented after the process has completed anyway.
You can try creating a loop and delete the rows based on a date column,
however, this usally results in batches that vary greatly in size. For
example, your company have a lot more transactions in 2005 than in 2004.
Using the technique below, you can delete in batches of exactly 1000 rows.
set rowcount 1000
while
delete from mytable where . . .
if @.@.rowcount = 0 break
checkpoint
end
"Nancy Lytle" <word_diva@.hotmail.com> wrote in message
news:er8ntEsIGHA.2668@.tk2msftngp13.phx.gbl...
> I have to do a delete from a table with about 25 million rows. I need to
> delete about 4 million rows, based on the date in another table
> Table Session
> SessionID SessionDate
> 1 1/25/2004
> 2 7/25/2001
> I have created a view (SessionView) containing all sessionid's where the
> sessiondate less than 3 years old.
> (Create view SessionView As Select SessionID From Session Where
> Session.sessiondate >= getdate() - 1095)
> Table Claims
> ClaimID SessionID etc etc etc
> 24578 1 15 HealthPractice
> 5554441234
> 6548975 2 52
> ChirocPract 5551234567
> What I need is to delete all rows from Table Claims that have sessionids
> in SessionView.
> Delete From Claims Where Claims.sessionID In (Select SessionID from
> SessionView)
> -In this case ClaimID 6548975 would be deleted but ClaimID 24578 would
> not.
> But I must be doing something wrong (I am very new at SQL Server, and have
> never done a large scale delete that wasn't just straight forward),
> because the delete seems to take forever. Last time I had to stop it at
> just over 5 hours because the network admin needed to do some work on the
> server and reboot it.
> I have set the recovery to simple, and the server has no other active
> databases or other applications running on it.
> Any assistance appreciated.
> Nancy Lytle
> N_Lytle@.terplaum.umd.edu
>

Best way to search a table based on another table's data?

I have a table of keywords (hundreds/thousands of records):

KeywordID KeywordName
--
1 Apple
2 Orange
3 Pear

I then have a table of products (also hundreds/thousands of records):

ProductID ProductName ProductDescription
--
123 Apple Tree Better than an orange tree, this...
124 Great Scent This great scent smells like orange...

What's the most efficent way to search the entire product table and return all records that have any of the keywords from the keyword table (in eiter productname or peoductdescription)?

Thanks,
Ron

Here is one way... I used a join with a like. This will give you one row per matching keyword:

Code Snippet

create table keyword
(
KeywordId int primary key,
KeywordName varchar(10)
)
insert into keyword
select 1,'Apple'
union all
select 2,'Orange'
union all
select 3,'Pear'
go

create table product
(
ProductId int primary key,
ProductName varchar(20),
ProductDescription varchar(40)
)
insert into product
select 123,'Apple Tree','Better than an orange tree, this...'
union all
select 124,'Great Scent','This great scent smells like orange...'
go

select Keyword.KeywordName, Product.ProductName, Product.ProductDescription

from Product
join Keyword
on Product.ProductName like '%' + Keyword.KeywordName + '%'
or Product.ProductDescription like '%' + Keyword.KeywordName + '%'

Which returns:

Code Snippet

KeywordName ProductName ProductDescription

-- -- -

Apple Apple Tree Better than an orange tree, this...

Orange Apple Tree Better than an orange tree, this...

Orange Great Scent This great scent smells like orange...

Or if you don't want duplicates, you could use:

Code Snippet

select Product.ProductName, Product.ProductDescription
from Product
where exists (select *
from keyword
where Product.ProductName like '%' + Keyword.KeywordName + '%'
or Product.ProductDescription like '%' + Keyword.KeywordName + '%')

Another possibility is to use full text search...|||select p.*
from keywords k inner join products p
on p.ProductName like '%' + k.KeywordName + '%'
or p.ProductDescription like '%' + k.KeywordName + '%'

best way to order results sequentially starting from somewhere in the middle

I'm working with SQL Server 2005, and I'm trying to sort the results based on a user selected letter. Say the user selects 'D' to filter his results. I'd like to return the results starting from D followed by E, F, G...Z, A, B, C. What I'm getting is the results for the D entries at the top of the result set, followed by A, B, C, E...Z.

A solution comes to mind that would be very long and db intensive, by querying on 'like 'D', followed by like 'E', followed by like 'F', etc, but I'm sure that there is a much more efficient way to do this. Below is the code that I'm using now.

' where @.SortString = 'd' and @.Test is a temp Table

BEGIN

Insert into @.Test

Select CompanyName,ContactId, CompanyId

from vContacts where CompanyName like @.SortString +'%'

Order by CompanyName

Insert into @.Test

Select CompanyName,ContactId, CompanyId

from vContacts where CompanyName not like @.SortString +'%'

Order by CompanyName

END

Thanks in advance for your help

Code Snippet

SELECT
OrderSet = 1,
CompanyName,
ContactId,
CompanyId
FROM vContacts
WHERE CompanyName >= @.SortString

UNION

Select
2
CompanyName,
ContactId,
CompanyId
FROM vContacts
WHERE CompanyName < @.SortString

ORDER BY
OrderSet,

CompanyName

|||Thank you Arnie. Right on with the answer

Best way to do a free text search

HiI have a .net 2 website that works from a sql 2000 db. I am building a form that will allow the user to type in any search criteria and based on their input I need to produce some results (that's the simplest way to put it)I will need to search various parts of my db to find similar sounding information, I was just wondering what is the best way to do this. I had the following thoughts1) Search columns using Soundex tsql function (but not sure how good this is?)2) Remove all noise words from user input (eg, and, or, the etc...) and then use a regular expression to search the fields in the dbAlternatively are their some third party components to do can do this for me Many thanks in advance

You can full text search enable the database, and create a full text index on the column in the table you want to search. Then you will be able to search any word that appears inside that column. Here are a couple of links that might be helpful to you.

http://www.databasejournal.com/features/mssql/article.php/3441981

http://www.wherecanibuyit.co.uk/ASP/full-text-search.html

http://msdn2.microsoft.com/en-us/library/ms177652.aspx

|||

Hi

Thanks for that, it was a great help

I have noticed that their is a RowNumber method in SQL 2005, is their any way to do this functionality in SQL 2000

One method I thought of was to put the data in temp table and loop through each record and give it a number, and then pull out 10 records at a time as the user pages through.

Are their any other ways I could do this.

Many thanks in advance

|||

The short answer is yes. You may want to search for articles on Custom Paging with SQL Server 2000. There are ways to do it, but they are not as simple or clean as the ROW_NUMBER() function. Here is an article to get you started.

http://www.4guysfromrolla.com/webtech/042606-1.shtml