Can t-log size handle large number of deletions?

Hi All,
Im just wondering if there is a way i could find out if the size of my
transaction log will be able to record the number of deletions i will be
performing? Basically i have to delete 325 million rows from a table and my
t-log is, say 20GB. is there a way i can calculate if the t-log is big
enough or will it need to expand?
thx in advance.Well I certainly would not advocate deleting them all in one batch. If you
delete them in smaller batches (say 10K or 100K at a time) it will not only
be faster but you would have the option to backup or truncate the log as you
go along. How many rows in the table do you want to keep. It may be
easier to BCP out the ones to keep, truncate the table and bcp them back in.
--
Andrew J. Kelly
SQL Server MVP
"M Sandico" <msandico@.muchomail.com> wrote in message
news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Im just wondering if there is a way i could find out if the size of my
> transaction log will be able to record the number of deletions i will be
> performing? Basically i have to delete 325 million rows from a table and
my
> t-log is, say 20GB. is there a way i can calculate if the t-log is big
> enough or will it need to expand?
> thx in advance.
>|||You are correct in that I dont delete them all in one batch. I delete in
batches of 1000 with a BEGIN TRAN and COMMIT TRAN as well. What I am
planning to do is just set the model to SIMPLE during the deletion and have
the COMMIT TRAN force the log to be flushed during the automatic checkpoint
(when log is 70% full).
Just thought there would be a way to guesstimate how big a t-log youd need
for certain operations..
thx.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e9rVaHJpDHA.360@.TK2MSFTNGP12.phx.gbl...
> Well I certainly would not advocate deleting them all in one batch. If
you
> delete them in smaller batches (say 10K or 100K at a time) it will not
only
> be faster but you would have the option to backup or truncate the log as
you
> go along. How many rows in the table do you want to keep. It may be
> easier to BCP out the ones to keep, truncate the table and bcp them back
in.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "M Sandico" <msandico@.muchomail.com> wrote in message
> news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > Im just wondering if there is a way i could find out if the size of my
> > transaction log will be able to record the number of deletions i will be
> > performing? Basically i have to delete 325 million rows from a table and
> my
> > t-log is, say 20GB. is there a way i can calculate if the t-log is big
> > enough or will it need to expand?
> >
> > thx in advance.
> >
> >
>|||I don't know of a formula off hand. You would have to account for at least
the amount of data that you are deleting and then some percentage for
overhead etc. What that percentage is I don't really know.
--
Andrew J. Kelly
SQL Server MVP
"M Sandico" <msandico@.muchomail.com> wrote in message
news:ugg192JpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> You are correct in that I dont delete them all in one batch. I delete in
> batches of 1000 with a BEGIN TRAN and COMMIT TRAN as well. What I am
> planning to do is just set the model to SIMPLE during the deletion and
have
> the COMMIT TRAN force the log to be flushed during the automatic
checkpoint
> (when log is 70% full).
> Just thought there would be a way to guesstimate how big a t-log youd need
> for certain operations..
> thx.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e9rVaHJpDHA.360@.TK2MSFTNGP12.phx.gbl...
> > Well I certainly would not advocate deleting them all in one batch. If
> you
> > delete them in smaller batches (say 10K or 100K at a time) it will not
> only
> > be faster but you would have the option to backup or truncate the log as
> you
> > go along. How many rows in the table do you want to keep. It may be
> > easier to BCP out the ones to keep, truncate the table and bcp them back
> in.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "M Sandico" <msandico@.muchomail.com> wrote in message
> > news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > > Hi All,
> > >
> > > Im just wondering if there is a way i could find out if the size of my
> > > transaction log will be able to record the number of deletions i will
be
> > > performing? Basically i have to delete 325 million rows from a table
and
> > my
> > > t-log is, say 20GB. is there a way i can calculate if the t-log is big
> > > enough or will it need to expand?
> > >
> > > thx in advance.
> > >
> > >
> >
> >
>

can this be done?

Table1
ID|CATID|NAME
1 3 A
2 3 B
3 3 C
4 4 D
5 4 E
I want to write a query that pull all the record from the same CATID but
with only ID given.
If ID = 4 it would return ID 4 and 5 because they are in the same category
If ID = 2 it would return ID 1,2,3
Thanks,
HowardHoward wrote:
> Table1
> ID|CATID|NAME
> 1 3 A
> 2 3 B
> 3 3 C
> 4 4 D
> 5 4 E
> I want to write a query that pull all the record from the same CATID but
> with only ID given.
> If ID = 4 it would return ID 4 and 5 because they are in the same category
> If ID = 2 it would return ID 1,2,3
> Thanks,
> Howard
DECLARE @.id INT;
SET @.id = 4;
SELECT id
FROM table1
WHERE catid =
(SELECT catid
FROM table1
WHERE id = @.id);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> I want to write a query that pull all the record from the same CATID
> but with only ID given.
> If ID = 4 it would return ID 4 and 5 because they are in the same
> category If ID = 2 it would return ID 1,2,3
A relatively easy way to do that would be using in sub-query:
create table dbo.table1(id tinyint,catid tinyint,name char(1))
go
insert into dbo.table1(id,catid,name)
select 1,3,'A' union all
select 2,3,'B' union all
select 3,3,'C' union all
select 4,4,'D' union all
select 5,4,'E'
go
declare @.id tinyint
set @.id = 1
select * from dbo.table1
where catid in
(select catid from dbo.table1 where id=@.id)
go
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hello Kent,
declare @.id tinyint
set @.id = 4
select t2.* from dbo.table1 t1
left join dbo.table1 t2
on t1.catid = t2.catid
where t1.id = @.id
is an option too.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/sql

can sql server allow to read record ramdomly?

anyone have the solution or work around?

my system need to select x% ramdom record from a batch of data from sql server for validation. is this posible?

any sql statement allow to select the record ramdomly? let say total record is around 1000, example record that i need to select is:-

record no 5,48,49,50,147,148,256,257,258,411,412,413,414,415,..... so and so

can i use cursor to move around the record to read it?

if we view on performance of the system. how can i make this at max speed? is that clone this table into local access database will make this more faster?

regards

terence chua

How do you intend to use this data ? If you won't display it in tabular form, you could read the entire DataTable, then use random number generation to access different rows in random order ?

|||

actually this is a system to receive customer feedback.

and now i want to basic on the total transaction to take out the % of transaction to collect customer contact information. then i will create a new contact list to allow the system to send out sms to customer to say thanks and request them to reply. just to make sure the customer did feedback to us but not our own staff key in the feedback.

this sms list will also be a report to show to top management. regarding how many sms we send out everyday.

regard

terence chua

|||

You can use TABLESAMPLE (nnn PERCENT) or TABLESAMPLE (nnn ROWS) in your SELECT statement. But be carefully - TABLESAMPLE extracts not exactly nnn percent or nnn rows, i.e. two executions of, for example, 'select * from Person.Contact tablesample (10 percent)' will have thow different rowcounts. See BOL for details.

WBR, Evergray -- Words mean nothing...|||

YOu could use something like this:

SELECT TOP 10 *

FROM

SomeTable

Order by NEWID()

HTH, jens Suessmeyer.

|||

Yes, this kind of query will return exactly specified number of records, but by cost of performance (in common case). Query with 'tablesample' will read random number of table pages (and, yeah, may even return 0 records or double expected count) while query with 'order by newid()' should scan entire table or index and compute newid() for each record to properly sort them and select top(n). The second type of queries may be better (in performance) only with highly selective (or covered) queries with appropriate indexes for support them (and, again, always more accurate :-)). Or, if table has an 'uniqueidentifier' or 'rowguid' column (indexed) - this is may be case, too.

If the main goal is performance of such a query (as I've understand from the first post), and the query is not very selective and not covered by any index, the choice is tablesampling. Inaccuracy of this clause may be eliminated by doing tablesampling twice (or more), for example:

select top 10 percent * from
(select * from sometable
tablesample (10 percent)
where x=y
union
select * from sometable
tablesample (10 percent)
where x=y
) a

will be more accurate with percentage than one sample. Anyway, you should compare performance of queries of both types to select the better one for your needs.

This data may be used to fill out temporary table (directly or by using table variable - the last will be better choice) to process it on server side (sms via Database Mail?). But, if you doing processing in an external application, sure you can (maybe even should) extract data from server in any store which is local for that application so server will be free from take a care of it.

Good luck!

WBR, Evergray
--
Words mean nothing...|||Hi Everygray,
what your mean is using more then 1 time tablesampling to make sure the record more accurate?
to make sure what i understand is correct in ur sample, "a" is temporary table? is the "tablesample(10 percent)" is the syntax for the query?
below is the code for single tablesampling?
--
select * from sometable
tablesample (10 percent)
where x=y
--
so let say my table name call "Answers" then my query should be like this?
select top 30 percent * from
(select * from Answers
tablesample (10 percent)
where date >= yesterday
union
select * from Answers
tablesample (10 percent)
where date >= yesterday
) a
now my system should able to take out the contact information and pass to external system to process and send out the sms. so the output should be in either .txt or .dat file or something else like .xml.
btw are you having any better idea or better way to done the job with sms the data directly out to my customer phone without export the data out? because now my company planing to corporate with a communication provided company to send out sms. so them may request the customer list in a txt, dat or xml file. tat's y i looking for the solution now before my management want it to be implement.
regards
terence chua
|||

Terence Chua wrote:

Hi Everygray,

what your mean is using more then 1 time tablesampling to make sure the record more accurate?

see below...

Terence Chua wrote:

to make sure what i understand is correct in ur sample, "a" is temporary table?

No, it's called 'derived table' (named subquery)

Terence Chua wrote:

is the "tablesample(10 percent)" is the syntax for the query?

below is the code for single tablesampling?
--
select * from sometable
tablesample (10 percent)
where x=y
--

Yes

Terence Chua wrote:

so let say my table name call "Answers" then my query should be like this?

select top 30 percent * from
(select * from Answers
tablesample (10 percent)
where date >= yesterday
union
select * from Answers
tablesample (10 percent)
where date >= yesterday
) a

Ok, maybe I've been not clear enough about sampling. Tablesample reads random number of PAGES, not ROWS. If you specify "tablesample (10 percent)" in your query, server will randomly select approximately 10 percent of pages on which table data is stored (let's say, 10 of 100 total) and return ALL rows from each selected page. If your table is big enough and rows are evenly distributed on data pages, resulting count of rows will be close to 10 percent. But, if, for example, your table is quite small (for example, 5 pages), 10% of total number of pages is zero, so query will result no rows at all. Or, if some of data pages in your table has small count of rows (e.g. 2-3), and some - big (e.g. 20-30), the resulting count will depend on which pages were selected (e.g. your query may return 3 rows or 300 on the same data). Or, if you specify "tablesample (10 rows)" for table which contains 300 rows at all, which are stored on 15 pages, the result again be 0 rows (10 rows from 300 are 3%, and 3% from 15 pages are 0 pages). So, if the same query executed several times against the same data returns very different results (e.g. 0, 30, 300, 100....) we may think about 'equalizing' these results. Your query above should return number of records closer to 6% of total rows than simple 'select * .. tablesample 6%'.

Terence Chua wrote:

now my system should able to take out the contact information and pass to external system to process and send out the sms. so the output should be in either .txt or .dat file or something else like .xml.

btw are you having any better idea or better way to done the job with sms the data directly out to my customer phone without export the data out? because now my company planing to corporate with a communication provided company to send out sms. so them may request the customer list in a txt, dat or xml file. tat's y i looking for the solution now before my management want it to be implement.

regards
terence chua

If number of messages to send is low, you may use sp_send_dbmail stored proc to send them through your smtp server (little emails to xxxxxxx@.mobile.operator.com or so). But this is not likely your case, because it is resource consuming task (all messages are stored in a queue on the server, mailing program executes here too), at least, you should't use the same SQL Server instance for it.

As for export - there are MANY different ways to export data in any format - using linked servers (in any external database or plaintext file), simply executing query with sqlcmd and storing results (maybe using FOR XML) in plaintext file, create webservice which will return such data in desired format to your partner and so on... Again, what the goal is? Which restrictions are? ;-)

WBR, Evergray
--
Words mean nothing...|||

Hi Evergray,

Thanks for your help, but when i try the sql statement in sql server Enterprise Manager, at the view. i get this error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'PERCENT'.

below is the code i test on hard code the date at 26th Jan 2006 and get the answer between a range.

SELECT TOP 30 PERCENT *
FROM (SELECT *
FROM Answers tablesample(10 PERCENT)
WHERE (iMinutes BETWEEN 570 AND 1020) AND (MONTH(dtAnswer) = 1) AND (YEAR(dtAnswer) = 2006) AND (iTemplateId = 1) AND (fDiscard = 0)
AND (fIncomplete = 0) AND (DAY(dtAnswer) = 26)
UNION
SELECT *
FROM Answers tablesample(10 PERCENT)
WHERE (iMinutes BETWEEN 570 AND 1020) AND (MONTH(dtAnswer) = 1) AND (YEAR(dtAnswer) = 2006) AND (iTemplateId = 1) AND (fDiscard = 0) AND
(fIncomplete = 0) AND (DAY(dtAnswer) = 26))

|||

The first, you should add an alias for your inner query, such as

select top 30 percent *
from (...) somealias

The second... the error message you refer to... Looks like you are using SQL Server 2000 or your database compatibility level is not 9.0, as required for tablesample to work... If this is the case, you should use Jens's suggestion because tablesampling is unavailable for you.

The third. If your Answers table has an index on dtAnswer column, you'd better use two DateTime constants and BETWEEN clause in you query rather than using these Day(), Year() etc., because your query doesn't benefit from such index, always doing fullscan of table.

WBR, Evergray
--
Words mean nothing....

Can SQL Lose Records?

We are running SQL 7 with a front end that links to the tables through ODBC.
In our main table, the user has no way to delete a record through the
interface, though it is possible to delete it by opening the ODBC link.
Users would have no reason to delete a record, but one of our records turned
up missing.

Now, it's possible that a user may have accidentally deleted the record.
But, since users don't have any reason to delete records, and since they
don't access the ODBC links, it seems unlikely (though possible).

I was wondering if anyone had every heard of SQL Server ever "losing" a
record that had previously been saved. I checked the nightly backup from the
night after it was added, and the record was there. So either a user deleted
it, or somehow it got lost in SQL Server. I have no code that deletes
records in this table in any way, shape or form, so it couldn't have been
malfunctioning code.

So, while I have a hard time believing that SQL Server would just "lose" a
record, I also know that anything's possible, so I thought I'd ask if anyone
had ever heard of such a thing.

Thanks!

NeilOn Jun 20, 2:53 pm, "Neil" <nos...@.nospam.netwrote:

Quote:

Originally Posted by

We are running SQL 7 with a front end that links to the tables through ODBC.
In our main table, the user has no way to delete a record through the
interface, though it is possible to delete it by opening the ODBC link.
Users would have no reason to delete a record, but one of our records turned
up missing.
>
Now, it's possible that a user may have accidentally deleted the record.
But, since users don't have any reason to delete records, and since they
don't access the ODBC links, it seems unlikely (though possible).
>
I was wondering if anyone had every heard of SQL Server ever "losing" a
record that had previously been saved. I checked the nightly backup from the
night after it was added, and the record was there. So either a user deleted
it, or somehow it got lost in SQL Server. I have no code that deletes
records in this table in any way, shape or form, so it couldn't have been
malfunctioning code.
>
So, while I have a hard time believing that SQL Server would just "lose" a
record, I also know that anything's possible, so I thought I'd ask if anyone
had ever heard of such a thing.
>
Thanks!
>
Neil

Never heard of it. Revoke delete permissions from all your users. Add
a trigger prohibiting any deletes.|||I've never seen or heard of a row going missing either, and I spent
plenty of time using 7.0.

Along with what Alex suggested I would suggest doing a complete set of
DBCC integrity checks on the database.

Roy Harvey
Beacon Falls, CT

On Wed, 20 Jun 2007 19:53:32 GMT, "Neil" <nospam@.nospam.netwrote:

Quote:

Originally Posted by

>We are running SQL 7 with a front end that links to the tables through ODBC.
>In our main table, the user has no way to delete a record through the
>interface, though it is possible to delete it by opening the ODBC link.
>Users would have no reason to delete a record, but one of our records turned
>up missing.
>
>Now, it's possible that a user may have accidentally deleted the record.
>But, since users don't have any reason to delete records, and since they
>don't access the ODBC links, it seems unlikely (though possible).
>
>I was wondering if anyone had every heard of SQL Server ever "losing" a
>record that had previously been saved. I checked the nightly backup from the
>night after it was added, and the record was there. So either a user deleted
>it, or somehow it got lost in SQL Server. I have no code that deletes
>records in this table in any way, shape or form, so it couldn't have been
>malfunctioning code.
>
>So, while I have a hard time believing that SQL Server would just "lose" a
>record, I also know that anything's possible, so I thought I'd ask if anyone
>had ever heard of such a thing.
>
>Thanks!
>
>Neil

|||Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

So, while I have a hard time believing that SQL Server would just "lose"
a record, I also know that anything's possible, so I thought I'd ask if
anyone had ever heard of such a thing.

Well, I have lost rows, but that was a on a system where no one was looking
at the event log or the DBCC logs, and finally the database broke down,
with several levels of corruption.

As Roy said, run DBCC. If it comes up with corruption, then that may be
the answer.

But I'm prepared to place my bets that there was a human involved.

--
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|||

Quote:

Originally Posted by

Never heard of it. Revoke delete permissions from all your users. Add
a trigger prohibiting any deletes.
>

If I add a trigger prohibiting any deletes, then it wouldn't be possible for
me to go in and delete a record if I ever needed to, right? Or is there a
way to set up a trigger so that it can allow the delete in some cases?

Thanks.|||I'm not familiar with DBCC. Can you point me in the right direction?

Thanks.

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9955ED0DF77BAYazorman@.127.0.0.1...

Quote:

Originally Posted by

Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

>So, while I have a hard time believing that SQL Server would just "lose"
>a record, I also know that anything's possible, so I thought I'd ask if
>anyone had ever heard of such a thing.

>
Well, I have lost rows, but that was a on a system where no one was
looking
at the event log or the DBCC logs, and finally the database broke down,
with several levels of corruption.
>
As Roy said, run DBCC. If it comes up with corruption, then that may be
the answer.
>
But I'm prepared to place my bets that there was a human involved.
>
>
--
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

|||On Jun 20, 5:01 pm, "Neil" <nos...@.nospam.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Never heard of it. Revoke delete permissions from all your users. Add
a trigger prohibiting any deletes.

>
If I add a trigger prohibiting any deletes, then it wouldn't be possible for
me to go in and delete a record if I ever needed to, right? Or is there a
way to set up a trigger so that it can allow the delete in some cases?
>
Thanks.

You can disable the trigger for the duration of your delete.
Alternatively you can have you trigger allow you to do whatever you
want, based on user_id() or suser_id(). Trigger can be bypassed using
nested triggers and or recursive trigger setting. There are other ways
best described in T-SQL Programming by Itzik Ben-Gan.

http://sqlserver-tips.blogspot.com/|||Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

I'm not familiar with DBCC. Can you point me in the right direction?

There are several DBCC commands, but the one of interest here is DBCC
CHECKDB which checks the database for consistency errors. If the database is
of any size, run it off-hours.

You should regularly run DBCC on your database, for instance as part of a
maintenance job, and make sure that you get alerted if it finds any errors.

If memory serves, you just say "DBCC CHECKDB" in the database you want to
examine. But check Books Online for details.

--
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|||Hello,

You can think of SQL Profiler as well if still you doubt on the SQL
Activities for some particular time.

Thanks
Ajay

Can report services use Request.QueryString("varName") for record selection?

I know the parameters are available, but let's say if I've got a variable in a request.QueryString() function I want to use from a page which links to the report, is it possible for the record selection to be filtered by the Request.QueryString() variable?You need to pass the page parameters into as report parameter name / value pairs. We do not provide you the raw page querystring in the report (as the report execution isn't even guaranteed to come from a web page).

Can notification services record bounced emails automatically?

Hi,

I've not used SSNS yet but am considering it for an up coming project.

One of the criteria is that "SMTP delivery failures are to be recorded against the recipients".

Is this something that happens "out of the box" with SSNS? If not would it be difficult to write something to do it?

Cheers for any advice.

Martin

Try this posting....

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/d86305394d873284/cecc579ef585b9da?lnk=gst&q=kate+bounce+email&rnum=1#cecc579ef585b9da

HTH...

Joe