Query about keeping down the size of transaction Logs

Hi I have been struggerling with a problem with the transaction log for a while now and find I'm just getting myself in more of a mess.

My main aim is to get around the problem that the transaction logs just keep getting bigger and bigger.

Now I have look at a few ways around it firstly I tried to use the commands:

BACKUP LOG db_name WITH TRUNCATE_ONLY
DBCC SHRINKFILE (log_name,2)

and this worked really well to reduce the size to a manageable level, however I only just found out the real impact that it has on my recovery ability.

The other solution I have looked at was to backup the database and then to use this command to backup the Transaction Logs and clear them down.

BACKUP LOG db_name
TO Disk = 'C:/example.TRN'
WITH INIT;

However after testing this it doesn't seem to reduce the size of the transaction Logs at all.

It may be a problem with my understand of the way the transaction log works, I persume that when you have a good backup then to get a point in time recovery all you should need is the details of transactions that have happened past the point of the backup and therefore be able to somehow perge the log of all other data. And that is what I persummed the last command I tried would do. However the .LDF file remains the same size.

Am i doing something wrong am I missing something out?

Thanks for any help! EdHowdy

You are on the right track, here are some ideas :

When you take a full backup, it resets all the counters for backups and any tran log backups before the last full backup cant be used after it. OK, so if you want to do a point in time recovery, do a full backup then do tran log backups after that.

What you can do ( during after hours as then you have no activity in the database to cause problems) is this :

(1) start up Query Analyser and connect to the database
(2) execute the command CHECKPOINT
- this will write all inactive entries in the tran log to the database and give you a know starting point for size reduction.

(3) run the command :
DBCC SHRINKFILE('log_file_logical_name',10)

This will attempt to shrink the tran log to 10 MB. This *should* remove all the empty space from the tran log.

****Alternatively ( and this is a big-ish step so make sure no-one is on the database when its happening as while this is going on, you WILL NOT have database recovery capability and will have to go back to your last full backup if problems occur ) :

(1) Take full backup of the database & keep safely to one side ( just in case ) and label it as "BEFORE_<DB_NAME>_SHRINK.BAK" or similar.

(2) Change database into SIMPLE recovery mode. This will allow maximum shrinkage of the tran log. This is the old "truncate log on checkpoint" setting from SQL 6.5/7 .

(3) Open Query Analyser on the database & execute CHECKPOINT .
Then execute :
DBCC SHRINKFILE('<db_data_file_logical_name>',10)
DBCC SHRINKFILE('<db_log_file_logical_name>',10)

(4) Change the database back to FULL recovery mode.

(5) Take a full backup - VERY IMPORTANT. Note that this is the reference point that tran log backups will use if you need to recover the database.

This will be the case untill you do your next normal scheduled full backup, at which point that full backup will be the starting point for any tran logs that follow it, etc.

Post back if anything not clear.

Cheers

SG.|||Thanks SQLguy I think I finally understand now. It all seems to be working okay, that a relieve! Cheers Nixies

Queries and wildcards

I am trying to write a query with a wildcard as part of it. the table that I
am looking at is mostly text (they are smdr logs from my phone system) I
currently use access to write the queries connected to SQL server. in the
example below I need to put 1150 and 1152 for each of the t10xx numbers. I
would like to use a wildcard for the called_party but it does not work I have
tried *,#, and a few others but no success. any ideas would be helpful.
SELECT date, start_time, calling_party, called_party, rx_calls
FROM dbo.[All Phone Logs]
WHERE (calling_party = N't1050') AND (called_party = N'1150') OR
(calling_party = N't1051') AND (called_party = N'1150') OR
(calling_party = N't1052') AND (called_party = N'1150') OR
(calling_party = N't1053') AND (called_party = N'1150') OR
(calling_party = N't1054') AND (called_party = N'1150') OR
(calling_party = N't1055') AND (called_party = N'1150') OR
(calling_party = N't1056') AND (called_party = N'1150') OR
(calling_party = N't1057') AND (called_party = N'1150') OR
(calling_party = N't1058') AND (called_party = N'1150') OR
(calling_party = N't1059') AND (called_party = N'1150') OR
(calling_party = N't1060') AND (called_party = N'1150') OR
(calling_party = N't1051') AND (called_party = N'1152') OR
(calling_party = N't1052') AND (called_party = N'1152') OR
(calling_party = N't1053') AND (called_party = N'1152') OR
(calling_party = N't1054') AND (called_party = N'1152') OR
(calling_party = N't1055') AND (called_party = N'1152') OR
(calling_party = N't1056') AND (called_party = N'1152') OR
(calling_party = N't1057') AND (called_party = N'1152') OR
(calling_party = N't1058') AND (called_party = N'1152') OR
(calling_party = N't1059') AND (called_party = N'1152') OR
(calling_party = N't1060') AND (called_party = N'1152')The wild card characters supported in T-SQL are '%' and _ (underscore)
--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Bill Farinella" <BillFarinella@.discussions.microsoft.com> wrote in message
news:A375B699-7ECF-4F4E-8DD2-F395D8B7C897@.microsoft.com...
>I am trying to write a query with a wildcard as part of it. the table that
>I
> am looking at is mostly text (they are smdr logs from my phone system) I
> currently use access to write the queries connected to SQL server. in the
> example below I need to put 1150 and 1152 for each of the t10xx numbers. I
> would like to use a wildcard for the called_party but it does not work I
> have
> tried *,#, and a few others but no success. any ideas would be helpful.
>
> SELECT date, start_time, calling_party, called_party, rx_calls
> FROM dbo.[All Phone Logs]
> WHERE (calling_party = N't1050') AND (called_party = N'1150') OR
> (calling_party = N't1051') AND (called_party => N'1150') OR
> (calling_party = N't1052') AND (called_party => N'1150') OR
> (calling_party = N't1053') AND (called_party => N'1150') OR
> (calling_party = N't1054') AND (called_party => N'1150') OR
> (calling_party = N't1055') AND (called_party => N'1150') OR
> (calling_party = N't1056') AND (called_party => N'1150') OR
> (calling_party = N't1057') AND (called_party => N'1150') OR
> (calling_party = N't1058') AND (called_party => N'1150') OR
> (calling_party = N't1059') AND (called_party => N'1150') OR
> (calling_party = N't1060') AND (called_party => N'1150') OR
> (calling_party = N't1051') AND (called_party => N'1152') OR
> (calling_party = N't1052') AND (called_party => N'1152') OR
> (calling_party = N't1053') AND (called_party => N'1152') OR
> (calling_party = N't1054') AND (called_party => N'1152') OR
> (calling_party = N't1055') AND (called_party => N'1152') OR
> (calling_party = N't1056') AND (called_party => N'1152') OR
> (calling_party = N't1057') AND (called_party => N'1152') OR
> (calling_party = N't1058') AND (called_party => N'1152') OR
> (calling_party = N't1059') AND (called_party => N'1152') OR
> (calling_party = N't1060') AND (called_party => N'1152')
>|||On Wed, 23 Nov 2005 07:30:11 -0800, Bill Farinella wrote:
>I am trying to write a query with a wildcard as part of it. the table that I
>am looking at is mostly text (they are smdr logs from my phone system) I
>currently use access to write the queries connected to SQL server. in the
>example below I need to put 1150 and 1152 for each of the t10xx numbers. I
>would like to use a wildcard for the called_party but it does not work I have
>tried *,#, and a few others but no success. any ideas would be helpful.
(snip)
Hi Bill,
In this case, you might want to use IN instead of wildcards:
SELECT date, start_time, calling_party, called_party, rx_calls
FROM dbo.[All Phone Logs]
WHERE calling_party IN (N't1050', N't1051', ..., N't1060')
AND called_part IN (N'1150', N'1152')
Note: the ellipsis in the query above should be substituted by the full
list of valued you want to search for in calling_party.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Wow, Thanks That worked great!! :) I wish I posted this sooner now. It could
have saved me days worth of work.
Thanks again,
Bill
"Hugo Kornelis" wrote:
> On Wed, 23 Nov 2005 07:30:11 -0800, Bill Farinella wrote:
> >I am trying to write a query with a wildcard as part of it. the table that I
> >am looking at is mostly text (they are smdr logs from my phone system) I
> >currently use access to write the queries connected to SQL server. in the
> >example below I need to put 1150 and 1152 for each of the t10xx numbers. I
> >would like to use a wildcard for the called_party but it does not work I have
> >tried *,#, and a few others but no success. any ideas would be helpful.
> (snip)
> Hi Bill,
> In this case, you might want to use IN instead of wildcards:
> SELECT date, start_time, calling_party, called_party, rx_calls
> FROM dbo.[All Phone Logs]
> WHERE calling_party IN (N't1050', N't1051', ..., N't1060')
> AND called_part IN (N'1150', N'1152')
> Note: the ellipsis in the query above should be substituted by the full
> list of valued you want to search for in calling_party.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Queries and wildcards

I am trying to write a query with a wildcard as part of it. the table that I
am looking at is mostly text (they are smdr logs from my phone system) I
currently use access to write the queries connected to SQL server. in the
example below I need to put 1150 and 1152 for each of the t10xx numbers. I
would like to use a wildcard for the called_party but it does not work I hav
e
tried *,#, and a few others but no success. any ideas would be helpful.
SELECT date, start_time, calling_party, called_party, rx_calls
FROM dbo.[All Phone Logs]
WHERE (calling_party = N't1050') AND (called_party = N'1150') OR
(calling_party = N't1051') AND (called_party =
N'1150') OR
(calling_party = N't1052') AND (called_party =
N'1150') OR
(calling_party = N't1053') AND (called_party =
N'1150') OR
(calling_party = N't1054') AND (called_party =
N'1150') OR
(calling_party = N't1055') AND (called_party =
N'1150') OR
(calling_party = N't1056') AND (called_party =
N'1150') OR
(calling_party = N't1057') AND (called_party =
N'1150') OR
(calling_party = N't1058') AND (called_party =
N'1150') OR
(calling_party = N't1059') AND (called_party =
N'1150') OR
(calling_party = N't1060') AND (called_party =
N'1150') OR
(calling_party = N't1051') AND (called_party =
N'1152') OR
(calling_party = N't1052') AND (called_party =
N'1152') OR
(calling_party = N't1053') AND (called_party =
N'1152') OR
(calling_party = N't1054') AND (called_party =
N'1152') OR
(calling_party = N't1055') AND (called_party =
N'1152') OR
(calling_party = N't1056') AND (called_party =
N'1152') OR
(calling_party = N't1057') AND (called_party =
N'1152') OR
(calling_party = N't1058') AND (called_party =
N'1152') OR
(calling_party = N't1059') AND (called_party =
N'1152') OR
(calling_party = N't1060') AND (called_party = N'1152')The wild card characters supported in T-SQL are '%' and _ (underscore)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Bill Farinella" <BillFarinella@.discussions.microsoft.com> wrote in message
news:A375B699-7ECF-4F4E-8DD2-F395D8B7C897@.microsoft.com...
>I am trying to write a query with a wildcard as part of it. the table that
>I
> am looking at is mostly text (they are smdr logs from my phone system) I
> currently use access to write the queries connected to SQL server. in the
> example below I need to put 1150 and 1152 for each of the t10xx numbers. I
> would like to use a wildcard for the called_party but it does not work I
> have
> tried *,#, and a few others but no success. any ideas would be helpful.
>
> SELECT date, start_time, calling_party, called_party, rx_calls
> FROM dbo.[All Phone Logs]
> WHERE (calling_party = N't1050') AND (called_party = N'1150') OR
> (calling_party = N't1051') AND (called_party =
> N'1150') OR
> (calling_party = N't1052') AND (called_party =
> N'1150') OR
> (calling_party = N't1053') AND (called_party =
> N'1150') OR
> (calling_party = N't1054') AND (called_party =
> N'1150') OR
> (calling_party = N't1055') AND (called_party =
> N'1150') OR
> (calling_party = N't1056') AND (called_party =
> N'1150') OR
> (calling_party = N't1057') AND (called_party =
> N'1150') OR
> (calling_party = N't1058') AND (called_party =
> N'1150') OR
> (calling_party = N't1059') AND (called_party =
> N'1150') OR
> (calling_party = N't1060') AND (called_party =
> N'1150') OR
> (calling_party = N't1051') AND (called_party =
> N'1152') OR
> (calling_party = N't1052') AND (called_party =
> N'1152') OR
> (calling_party = N't1053') AND (called_party =
> N'1152') OR
> (calling_party = N't1054') AND (called_party =
> N'1152') OR
> (calling_party = N't1055') AND (called_party =
> N'1152') OR
> (calling_party = N't1056') AND (called_party =
> N'1152') OR
> (calling_party = N't1057') AND (called_party =
> N'1152') OR
> (calling_party = N't1058') AND (called_party =
> N'1152') OR
> (calling_party = N't1059') AND (called_party =
> N'1152') OR
> (calling_party = N't1060') AND (called_party =
> N'1152')
>|||On Wed, 23 Nov 2005 07:30:11 -0800, Bill Farinella wrote:

>I am trying to write a query with a wildcard as part of it. the table that
I
>am looking at is mostly text (they are smdr logs from my phone system) I
>currently use access to write the queries connected to SQL server. in the
>example below I need to put 1150 and 1152 for each of the t10xx numbers. I
>would like to use a wildcard for the called_party but it does not work I ha
ve
>tried *,#, and a few others but no success. any ideas would be helpful.
(snip)
Hi Bill,
In this case, you might want to use IN instead of wildcards:
SELECT date, start_time, calling_party, called_party, rx_calls
FROM dbo.[All Phone Logs]
WHERE calling_party IN (N't1050', N't1051', ..., N't1060')
AND called_part IN (N'1150', N'1152')
Note: the ellipsis in the query above should be substituted by the full
list of valued you want to search for in calling_party.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Wow, Thanks That worked great!! I wish I posted this sooner now. It could
have saved me days worth of work.
Thanks again,
Bill
"Hugo Kornelis" wrote:

> On Wed, 23 Nov 2005 07:30:11 -0800, Bill Farinella wrote:
>
> (snip)
> Hi Bill,
> In this case, you might want to use IN instead of wildcards:
> SELECT date, start_time, calling_party, called_party, rx_calls
> FROM dbo.[All Phone Logs]
> WHERE calling_party IN (N't1050', N't1051', ..., N't1060')
> AND called_part IN (N'1150', N'1152')
> Note: the ellipsis in the query above should be substituted by the full
> list of valued you want to search for in calling_party.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Queries and wildcards

I am trying to write a query with a wildcard as part of it. the table that I
am looking at is mostly text (they are smdr logs from my phone system) I
currently use access to write the queries connected to SQL server. in the
example below I need to put 1150 and 1152 for each of the t10xx numbers. I
would like to use a wildcard for the called_party but it does not work I have
tried *,#, and a few others but no success. any ideas would be helpful.
SELECT date, start_time, calling_party, called_party, rx_calls
FROM dbo.[All Phone Logs]
WHERE (calling_party = N't1050') AND (called_party = N'1150') OR
(calling_party = N't1051') AND (called_party =
N'1150') OR
(calling_party = N't1052') AND (called_party =
N'1150') OR
(calling_party = N't1053') AND (called_party =
N'1150') OR
(calling_party = N't1054') AND (called_party =
N'1150') OR
(calling_party = N't1055') AND (called_party =
N'1150') OR
(calling_party = N't1056') AND (called_party =
N'1150') OR
(calling_party = N't1057') AND (called_party =
N'1150') OR
(calling_party = N't1058') AND (called_party =
N'1150') OR
(calling_party = N't1059') AND (called_party =
N'1150') OR
(calling_party = N't1060') AND (called_party =
N'1150') OR
(calling_party = N't1051') AND (called_party =
N'1152') OR
(calling_party = N't1052') AND (called_party =
N'1152') OR
(calling_party = N't1053') AND (called_party =
N'1152') OR
(calling_party = N't1054') AND (called_party =
N'1152') OR
(calling_party = N't1055') AND (called_party =
N'1152') OR
(calling_party = N't1056') AND (called_party =
N'1152') OR
(calling_party = N't1057') AND (called_party =
N'1152') OR
(calling_party = N't1058') AND (called_party =
N'1152') OR
(calling_party = N't1059') AND (called_party =
N'1152') OR
(calling_party = N't1060') AND (called_party = N'1152')
The wild card characters supported in T-SQL are '%' and _ (underscore)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Bill Farinella" <BillFarinella@.discussions.microsoft.com> wrote in message
news:A375B699-7ECF-4F4E-8DD2-F395D8B7C897@.microsoft.com...
>I am trying to write a query with a wildcard as part of it. the table that
>I
> am looking at is mostly text (they are smdr logs from my phone system) I
> currently use access to write the queries connected to SQL server. in the
> example below I need to put 1150 and 1152 for each of the t10xx numbers. I
> would like to use a wildcard for the called_party but it does not work I
> have
> tried *,#, and a few others but no success. any ideas would be helpful.
>
> SELECT date, start_time, calling_party, called_party, rx_calls
> FROM dbo.[All Phone Logs]
> WHERE (calling_party = N't1050') AND (called_party = N'1150') OR
> (calling_party = N't1051') AND (called_party =
> N'1150') OR
> (calling_party = N't1052') AND (called_party =
> N'1150') OR
> (calling_party = N't1053') AND (called_party =
> N'1150') OR
> (calling_party = N't1054') AND (called_party =
> N'1150') OR
> (calling_party = N't1055') AND (called_party =
> N'1150') OR
> (calling_party = N't1056') AND (called_party =
> N'1150') OR
> (calling_party = N't1057') AND (called_party =
> N'1150') OR
> (calling_party = N't1058') AND (called_party =
> N'1150') OR
> (calling_party = N't1059') AND (called_party =
> N'1150') OR
> (calling_party = N't1060') AND (called_party =
> N'1150') OR
> (calling_party = N't1051') AND (called_party =
> N'1152') OR
> (calling_party = N't1052') AND (called_party =
> N'1152') OR
> (calling_party = N't1053') AND (called_party =
> N'1152') OR
> (calling_party = N't1054') AND (called_party =
> N'1152') OR
> (calling_party = N't1055') AND (called_party =
> N'1152') OR
> (calling_party = N't1056') AND (called_party =
> N'1152') OR
> (calling_party = N't1057') AND (called_party =
> N'1152') OR
> (calling_party = N't1058') AND (called_party =
> N'1152') OR
> (calling_party = N't1059') AND (called_party =
> N'1152') OR
> (calling_party = N't1060') AND (called_party =
> N'1152')
>
|||On Wed, 23 Nov 2005 07:30:11 -0800, Bill Farinella wrote:

>I am trying to write a query with a wildcard as part of it. the table that I
>am looking at is mostly text (they are smdr logs from my phone system) I
>currently use access to write the queries connected to SQL server. in the
>example below I need to put 1150 and 1152 for each of the t10xx numbers. I
>would like to use a wildcard for the called_party but it does not work I have
>tried *,#, and a few others but no success. any ideas would be helpful.
(snip)
Hi Bill,
In this case, you might want to use IN instead of wildcards:
SELECT date, start_time, calling_party, called_party, rx_calls
FROM dbo.[All Phone Logs]
WHERE calling_party IN (N't1050', N't1051', ..., N't1060')
AND called_part IN (N'1150', N'1152')
Note: the ellipsis in the query above should be substituted by the full
list of valued you want to search for in calling_party.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Wow, Thanks That worked great!! I wish I posted this sooner now. It could
have saved me days worth of work.
Thanks again,
Bill
"Hugo Kornelis" wrote:

> On Wed, 23 Nov 2005 07:30:11 -0800, Bill Farinella wrote:
> (snip)
> Hi Bill,
> In this case, you might want to use IN instead of wildcards:
> SELECT date, start_time, calling_party, called_party, rx_calls
> FROM dbo.[All Phone Logs]
> WHERE calling_party IN (N't1050', N't1051', ..., N't1060')
> AND called_part IN (N'1150', N'1152')
> Note: the ellipsis in the query above should be substituted by the full
> list of valued you want to search for in calling_party.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>