database backup not clearing the transaction log...

Hi
I have a 150Gb 'time series' type database running in SQL 2000. every sunday
@. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
creates a big fat transaction log.
At 0030 Monday morning the database is backed up using
BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NOUNLOAD ,
NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
NOFORMAT
which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is only
cleared at 0800 Monday morning when the trans log is backed up (and is
continually backed up every 10 minutes throughout the working day all week
until Saturday) using the following syntax:
surely the database backup at 0030 should clear down the size of the trans
log?

> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
Sure , it does not. You need to BACKUP LOG file in order to truncate
inactive portions in the LOG
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:11993BB5-D220-4AFF-99D1-F0C7FAE045F6@.microsoft.com...
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every
> sunday
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NOUNLOAD
> ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>
|||Methodology wrote:
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every sunday
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NOUNLOAD ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>
A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
transaction log, only a log backup will do that (assuming Full or
Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
of the log file will not change when truncating. Shrinking is a
separate process, and there are many reasons why you don't want to do it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Here is an idea. I did this once at one job I had. I had a similarlarly
large sized databases whose transaction logs "blew out" on a reindex
(weekly). I had a lot of ugly clustered indexes that had to be rebuilt
and took a lot of space. SInce the database was not heavily used during
that period, I did an alter database and put the db on simple mode.
Then it won't blow out the log. I did the re-index on all necessary
tables and then did a full backup. Note, this is dangerous as you break
the transaction log sequence at that time, so you must do an immediate
full backup. In my case, this was ok as it was a mainly Mon-Sat db.
KR
Tracy McKibben wrote:
> Methodology wrote:
> A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
> transaction log, only a log backup will do that (assuming Full or
> Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
> of the log file will not change when truncating. Shrinking is a
> separate process, and there are many reasons why you don't want to do it.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
sql

database backup not clearing the transaction log...

Hi
I have a 150Gb 'time series' type database running in SQL 2000. every sunday
@. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
creates a big fat transaction log.
At 0030 Monday morning the database is backed up using
BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NO
UNLOAD ,
NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
NOFORMAT
which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is only
cleared at 0800 Monday morning when the trans log is backed up (and is
continually backed up every 10 minutes throughout the working day all week
until Saturday) using the following syntax:
surely the database backup at 0030 should clear down the size of the trans
log?
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
Sure , it does not. You need to BACKUP LOG file in order to truncate
inactive portions in the LOG
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:11993BB5-D220-4AFF-99D1-F0C7FAE045F6@.microsoft.com...
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every
> sunday
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT ,
NOUNLOAD
> ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>|||Methodology wrote:
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every sund
ay
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT ,
NOUNLOAD ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is onl
y
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>
A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
transaction log, only a log backup will do that (assuming Full or
Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
of the log file will not change when truncating. Shrinking is a
separate process, and there are many reasons why you don't want to do it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Here is an idea. I did this once at one job I had. I had a similarlarly
large sized databases whose transaction logs "blew out" on a reindex
(weekly). I had a lot of ugly clustered indexes that had to be rebuilt
and took a lot of space. SInce the database was not heavily used during
that period, I did an alter database and put the db on simple mode.
Then it won't blow out the log. I did the re-index on all necessary
tables and then did a full backup. Note, this is dangerous as you break
the transaction log sequence at that time, so you must do an immediate
full backup. In my case, this was ok as it was a mainly Mon-Sat db.
KR
Tracy McKibben wrote:
> Methodology wrote:
> A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
> transaction log, only a log backup will do that (assuming Full or
> Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
> of the log file will not change when truncating. Shrinking is a
> separate process, and there are many reasons why you don't want to do it.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Database Anomaly

Hi

I have a table call Employee, and got a field name call Employee Name

Employee Name

--, --

%%%,

(Gpsi) Dr Ac, Baruah

(Gpsi) Dr Ap, Dhital

0, 0

1 To 1, Centre

ABAYOMI, EMJ

4, US

5F4MBL01, 5F4MBL01

5Jxths01, 5Jxths01

A&E Dept(Rochdale, Zts)

NULL

ABAYASIRIWARDA, JM

Shaw, A

NULL

Zzzzzzzzzzzzzz, Aaaaaaaaaaaa

From this example data, I need to get only bolded records only, rest of the records are garbage records.

Eg: garbage Records like: “A&E Dept(Rochdale, Zts) “ has got a ‘&’ sign is not a valid employee name

“1 To 1, Centre” has got a ‘1’ is not a valid employee name

“%%%,” has got a ‘%’ sign is not a valid employee name

what is the best way to identify only valid emplyee names ?

Please help me to solve this problem

Sujithf

Sujithf:

Here is my first pass at this; I have quite a few questions:

-- --
-- Issues:
-- (1) What non alphabetic characters besides comma are allowed?
-- (2) I have assumed that a name must be formatted "lastName, firstName"
-- to be legal. Therefore, the name "Madonna" is filtered out.
-- (3) I am allowing the single quote, hyphen, period and comma characters
-- and all alphabetics. How does this list need to change?
-- (4) I set the "repeating letter" threshold at 3 repeated letters being
-- invalid.
-- (5) I put in a special condition for the 3 repeated letters condition
-- to allow "Fred Flintstone III" to pass. If this is not valid
-- this special condition test can be eliminated.
-- (6) If Roman numerals as part of the name aren't valid a test for this
-- will need to be put together.
-- (7) I have assumed that the name "Katie O'Neal" is valid.
-- (8) I have assumed that the name "Mike De La Hoz" is valid.
-- (9) I have assumed that the name [Jimmy "The Z" De Zego] is not valid.
-- --
set nocount on

declare @.names table
( employeeName varchar (30)
)
insert into @.names values ('- -, - -')
insert into @.names values ('%%%')
insert into @.names values ('(Gpsi) Dr Ac, Baruah')
insert into @.names values ('(Gpsi) Dr Ap, Dhital')
insert into @.names values ('0, 0')
insert into @.names values ('1 to 1, Centre')
insert into @.names values ('4, US')
insert into @.names values ('Abayomi, EMJ')
insert into @.names values ('5F4MBL01, 5F4MBL01')
insert into @.names values ('5Jxths01, 5Jxths01')
insert into @.names values (null)
insert into @.names values ('Abayasiriwarda, JM')
insert into @.names values (null)
insert into @.names values ('Shaw, A')
insert into @.names values ('Zzzzzzzzzzzzz, Aaaaaaaaaaaaa')
insert into @.names values ('A&E Dept(Rochdale, Zts)')

insert into @.names values ('Madonna')
insert into @.names values ('Thant, U')
insert into @.names values ('U, Betcha')
insert into @.names values ('U, U')
insert into @.names values ('Flintstone, Fred Jr')
insert into @.names values ('Flintstone, Fred III')
insert into @.names values ('Flintstone, Fred VVV')
insert into @.names values ('O''Neal, Katie')
insert into @.names values ('De La Hoz, Mike')
insert into @.names values ('Aardvark, Jimmy')
insert into @.names values ('Taylor-Curry, Ann')
insert into @.names values ('De Zego, Jimmy "The Z"')
insert into @.names values ('Suds, Dog &')
insert into @.names values ('Auto, Big 4')
insert into @.names values ('This, i''s, a-bad, name.')
--select * from @.names

select employeeName
from @.names
where employeeName like '[abcdefghijklmnopqrstuvwxyz]%, [abcdefghijklmnopqrstuvwxyz]%'
and employeeName not like '%[^abcdefghijklmnopqrstuvwxyz, ''-.]%'
and len (employeeName) = len (replace (employeeName, ',', '')) + 1
and employeeName not like '%aaa%'
and employeeName not like '%bbb%'
and employeeName not like '%ccc%'
and employeeName not like '%ddd%'
and employeeName not like '%eee%'
and employeeName not like '%fff%'
and employeeName not like '%ggg%'
and employeeName not like '%hhh%'
and employeeName not like '%iiii%'
and employeeName not like '%jjj%'
and employeeName not like '%kkk%'
and employeeName not like '%lll%'
and employeeName not like '%mmm%'
and employeeName not like '%nnn%'
and employeeName not like '%ooo%'
and employeeName not like '%ppp%'
and employeeName not like '%qqq%'
and employeeName not like '%rrr%'
and employeeName not like '%sss%'
and employeeName not like '%ttt%'
and employeeName not like '%uuu%'
and employeeName not like '%vvv%'
and employeeName not like '%www%'
and employeeName not like '%xxx%'
and employeeName not like '%yyy%'
and employeeName not like '%zzz%'

-- Sample Output:

-- employeeName
--
-- Abayomi, EMJ
-- Abayasiriwarda, JM
-- Shaw, A
-- Thant, U
-- U, Betcha
-- U, U
-- Flintstone, Fred Jr
-- Flintstone, Fred III
-- O'Neal, Katie
-- De La Hoz, Mike
-- Aardvark, Jimmy
-- Taylor-Curry, Ann

Data Type question

Hi
I often have the dilemma of whether to use Text type or Varchar type.
Normally the situation is that the user will input 50 characters however in
some cases he will input 2000 characters should I make the field Text or
Varchar(2000)
Technically the question is whether a varchar field is assigned the space
automatically or only on request also how significant is the overhead of
using the Text type
Thank you in advance,
Shmuel Shulman
SBS Technologies LTD
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi
> I often have the dilemma of whether to use Text type or Varchar type.
> Normally the situation is that the user will input 50 characters however
> in some cases he will input 2000 characters should I make the field Text
> or Varchar(2000)
> Technically the question is whether a varchar field is assigned the space
> automatically or only on request also how significant is the overhead of
> using the Text type
>
The "var" in varchar is because the storage is variable. It only takes up
as much space as you use (plus a small fixed overhead).
Using the text type causes a 16-byte locator to be stored in the row instead
of the actual value. The actual value is stored on another page. So the
overhead of using Text is mainly the extra read required to get to the
actual value. For 50-2000 characters, use Varchar.
David
|||Thanks you for your response,
Shmuel
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e7rPgN0nFHA.3540@.TK2MSFTNGP10.phx.gbl...
> "S Shulman" <smshulman@.hotmail.com> wrote in message
> news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> The "var" in varchar is because the storage is variable. It only takes up
> as much space as you use (plus a small fixed overhead).
> Using the text type causes a 16-byte locator to be stored in the row
> instead of the actual value. The actual value is stored on another page.
> So the overhead of using Text is mainly the extra read required to get to
> the actual value. For 50-2000 characters, use Varchar.
> David
>

Data Type question

Hi
I often have the dilemma of whether to use Text type or Varchar type.
Normally the situation is that the user will input 50 characters however in
some cases he will input 2000 characters should I make the field Text or
Varchar(2000)
Technically the question is whether a varchar field is assigned the space
automatically or only on request also how significant is the overhead of
using the Text type
Thank you in advance,
Shmuel Shulman
SBS Technologies LTD"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi
> I often have the dilemma of whether to use Text type or Varchar type.
> Normally the situation is that the user will input 50 characters however
> in some cases he will input 2000 characters should I make the field Text
> or Varchar(2000)
> Technically the question is whether a varchar field is assigned the space
> automatically or only on request also how significant is the overhead of
> using the Text type
>
The "var" in varchar is because the storage is variable. It only takes up
as much space as you use (plus a small fixed overhead).
Using the text type causes a 16-byte locator to be stored in the row instead
of the actual value. The actual value is stored on another page. So the
overhead of using Text is mainly the extra read required to get to the
actual value. For 50-2000 characters, use Varchar.
David|||Thanks you for your response,
Shmuel
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e7rPgN0nFHA.3540@.TK2MSFTNGP10.phx.gbl...
> "S Shulman" <smshulman@.hotmail.com> wrote in message
> news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> The "var" in varchar is because the storage is variable. It only takes up
> as much space as you use (plus a small fixed overhead).
> Using the text type causes a 16-byte locator to be stored in the row
> instead of the actual value. The actual value is stored on another page.
> So the overhead of using Text is mainly the extra read required to get to
> the actual value. For 50-2000 characters, use Varchar.
> David
>

Data transformation services

Hi

I was told that using DTS will allow me to schedule stored procedures to keep an sql database up to date. For example if a user registers but does not activate the registration, his details will be removed by a stored procedure which is scheduled to run every 24 hours. I use to use the global.asax file to fire a update by using a file containing a the date of the last update and then by adding 24 hours to it, it would execute a SP to delete unwanted data.

I have tried to install DTS with no success. I am running the following

Visual web studio express

SQL 2005 Express. (From SQLExpr_exe) and I have told it to install all the extra components

Installed SQLEXPR_Toolkit.exe with all its options

Installed SQLServer2005_DTS.MSI

When I go into the sql server using MS SQL Server Management Studio Express. I cannot see the Data transformation services node. I have also just installed server reports which I had no problems installing.

Can somebody please help me.

DTS is a SQL2000 component; SQL2005 hasa totally rewritten equivalent called SSIS. An SSIS (SQL Server Integration Sercvices) job amongst other things will run stored procedures for you. However it is SQL Agent that provides the scheduling capability.

|||

Hi

Thanks for the reply. I need to know where to download the ssis installation application. The other thing is my service provider that I use uses SQL2000. Im developing in SQL Express 2005. How will I deploy the scheduled jobs to there server if im using the newer version.

Regards