SQL tool for MSDE: log

Showing posts with label log. Show all posts

Tuesday, March 27, 2012

Aliases not found on log in

When loggining into SSMS there does not appear to be a way to connect to the
ALIAS remote servers names like you could with SQL Server 2000. Does any one
know how to use the Alias names at the SQL Server 2005 connection dialog?D. Haber (DHaber@.discussions.microsoft.com) writes:
> When loggining into SSMS there does not appear to be a way to connect to
> the ALIAS remote servers names like you could with SQL Server 2000. Does
> any one know how to use the Alias names at the SQL Server 2005
> connection dialog?
What aliases? Those you defined in the Client Network Utility? I guess you
need to use the SQL Configuration Manager to set up aliaes for the SQL 2005
tools. It's under SQL Native Client configuration, which is a little
misleading, but I did a very quick test, and it seemed to work.
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

Sunday, March 25, 2012

Alerts and Jobs for transaction log processing

Scenario: I have an alert defined which monitors the transaction log. It should start a job which should backup the transaction log once the log is 75 percent full.

So far so good.

The challenge is to give the database name to the command for backing up (in our case it is a external tool) the appropriate transaction log

I know, that you can use tokens (A-DBN) to do this.

I know, that there is a bug in this area, so tokens are not set correctly in SP1

Question:

Is there any trick/alternative to create a workaround?

Regards

Norbert

If you created a WMI Event alert you may be able to read the Database name as part of the event $(WMI(DatabaseName))

Thursday, March 22, 2012

Alert not triggering

I've configured an alert for the TLog that when the server generates error
9002 (out of log space) to kick off a job that will backup the TLog to clear
it. I am testing the alert and I receive the error message, but the it will
not activate the alert. I've ensured that it is enabled and that the job
works, but for some reason the alert is not working. Can anyone help?
AndrewDoes the error show up in the `Windows event log?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Big Ern" <BigErn@.discussions.microsoft.com> wrote in message
news:0BD5E05C-F708-43D7-ADC1-6CCC75F61D26@.microsoft.com...
> I've configured an alert for the TLog that when the server generates error
> 9002 (out of log space) to kick off a job that will backup the TLog to clear
> it. I am testing the alert and I receive the error message, but the it will
> not activate the alert. I've ensured that it is enabled and that the job
> works, but for some reason the alert is not working. Can anyone help?
> Andrew|||Yes the error appears in both the SQL and Windows event logs.
"Tibor Karaszi" wrote:
> Does the error show up in the `Windows event log?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Big Ern" <BigErn@.discussions.microsoft.com> wrote in message
> news:0BD5E05C-F708-43D7-ADC1-6CCC75F61D26@.microsoft.com...
> > I've configured an alert for the TLog that when the server generates error
> > 9002 (out of log space) to kick off a job that will backup the TLog to clear
> > it. I am testing the alert and I receive the error message, but the it will
> > not activate the alert. I've ensured that it is enabled and that the job
> > works, but for some reason the alert is not working. Can anyone help?
> >
> > Andrew
>|||The last think I can think of is that agent isn't started. If that isn't the case and you can't find
anything obvious in Eventlog, I'm out of ideas, I'm afraid...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Big Ern" <BigErn@.discussions.microsoft.com> wrote in message
news:7AA07BA5-5744-4DC1-BF8F-310C1C700A49@.microsoft.com...
> Yes the error appears in both the SQL and Windows event logs.
> "Tibor Karaszi" wrote:
>> Does the error show up in the `Windows event log?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Big Ern" <BigErn@.discussions.microsoft.com> wrote in message
>> news:0BD5E05C-F708-43D7-ADC1-6CCC75F61D26@.microsoft.com...
>> > I've configured an alert for the TLog that when the server generates error
>> > 9002 (out of log space) to kick off a job that will backup the TLog to clear
>> > it. I am testing the alert and I receive the error message, but the it will
>> > not activate the alert. I've ensured that it is enabled and that the job
>> > works, but for some reason the alert is not working. Can anyone help?
>> >
>> > Andrew
>>

Alert not triggering

Tuesday, March 20, 2012

Alert Demo: Sev. 25 Errors - Fatal Error !

Hi, All
I am using SQLserver 2000.
The SQL Server Agent -> Alert says the follower msg:
Demo, Full msbd log
Demo, Full tempdb
Demo, Sev 19Erros (019-Fatal Error in Resource)
Demo, Sev. 20 Errors (020-Fatal Error In Current Process)
....
Demo, Sev. 25 Errors (025 - Fatal Error)
What should I do with these?
Thanks a lot!!!
Michaelproperly ur disk is full, try realse some space to store tempdb's data filesql

Monday, March 19, 2012

Aggregation Queries with > 80.000.000 rows

Hi,
We have a logging database with a table "Logs" that hold 80.000.000 log rows
(about 1 year of application logs). We need to analyze this data in for
variable time periods, so we cannot simply split the table. The table
contains a column "LogID" (Identity INT and the only member of the clustered
PK) and a column "Time" (datetime there exists an index for this column).
The query
SELECT *
FROM Logs WITH (nolock)
WHERE LogID > 110385284
takes 2 seconds and returns about 3000 rows.
The query
SELECT MAX(Time), MIN(Time)
FROM Logs WITH (nolock)
WHERE LogID > 110385284
takes more than a minute (I've cancelled the query after a minute).
How can this be? The query analyzer shows me a really simple query plan for
the first query using the PK. For the second query, the query plan shows me
no usage of the PK, but usage of the index for the column "Time" (two time,
one for min and one for max).
Even more strange is this query:
SELECT MAX(Logs1.Time), MIN(Logs1.Time)
FROM Logs Logs1 WITH (nolock)
INNER JOIN Logs Logs2 WITH (nolock) ON Logs1.LogID = Logs2.LogID
WHERE Logs1.LogID > 110385284
the query finishes in less than a second, and is using the PK.
Also specifying the PK as an index hint solves the problem, but I don't like
to specify query hints, because this may prevent an updated query processor
to use a better query plan.
Another problem is that we also have less expirienced programmers that need
to deal with such tables, so at the moment I need to review each and every
"slow" query and insert query hints. Will there be a fix that "optimizes" the
query engine of sql server?
CU,
Sven
Matzen
Yes, SQL Server needs to do some extra work when you specify an aggregate
functions.
Look, you may want to use INDEX hint to dictate the query optimizer to use
PK,but generally speaking it's not a good idea because the Optimizer is
smart enough to create much more efficient execution plan.
Also,you may create a clustered index on Time column and see how it does the
work.
"Matzen" <Matzen@.discussions.microsoft.com> wrote in message
news:ADAE2C5C-C301-4F72-966C-C6AB4EAB9FF7@.microsoft.com...
> Hi,
> We have a logging database with a table "Logs" that hold 80.000.000 log
rows
> (about 1 year of application logs). We need to analyze this data in for
> variable time periods, so we cannot simply split the table. The table
> contains a column "LogID" (Identity INT and the only member of the
clustered
> PK) and a column "Time" (datetime there exists an index for this column).
> The query
> SELECT *
> FROM Logs WITH (nolock)
> WHERE LogID > 110385284
> takes 2 seconds and returns about 3000 rows.
> The query
> SELECT MAX(Time), MIN(Time)
> FROM Logs WITH (nolock)
> WHERE LogID > 110385284
> takes more than a minute (I've cancelled the query after a minute).
> How can this be? The query analyzer shows me a really simple query plan
for
> the first query using the PK. For the second query, the query plan shows
me
> no usage of the PK, but usage of the index for the column "Time" (two
time,
> one for min and one for max).
> Even more strange is this query:
> SELECT MAX(Logs1.Time), MIN(Logs1.Time)
> FROM Logs Logs1 WITH (nolock)
> INNER JOIN Logs Logs2 WITH (nolock) ON Logs1.LogID = Logs2.LogID
> WHERE Logs1.LogID > 110385284
> the query finishes in less than a second, and is using the PK.
> Also specifying the PK as an index hint solves the problem, but I don't
like
> to specify query hints, because this may prevent an updated query
processor
> to use a better query plan.
> Another problem is that we also have less expirienced programmers that
need
> to deal with such tables, so at the moment I need to review each and every
> "slow" query and insert query hints. Will there be a fix that "optimizes"
the
> query engine of sql server?
> CU,
> Sven
|||Hi Uri,
As you can see in my post, the query including the hint performs at least 60
times better than without it (without > 1 minute, with hint less than a
second).
With the clustered index ... well 80 million rows are currently clustered by
ID, resorting them to sort by another column may take a while ... may be next
year.
My "problem" is that I cannot belive that a query optimizer like the one of
sql server does not recognize that the "where" statement reduces the amount
of data to be processed from 80 million to 3000 (table statistics are up to
date), because this analysis is not really compex:
1) the "where" does contain an identity field, that is equal to the
clustered PK
2) the condition in the where stament eleminates > 90% of the data to be
processed
This must be a bug, so I assume this to be removed in the next service pack.
CU,
Sven
"Uri Dimant" wrote:

> Matzen
> Yes, SQL Server needs to do some extra work when you specify an aggregate
> functions.
> Look, you may want to use INDEX hint to dictate the query optimizer to use
> PK,but generally speaking it's not a good idea because the Optimizer is
> smart enough to create much more efficient execution plan.
> Also,you may create a clustered index on Time column and see how it does the
> work.
>
>
>
>
> "Matzen" <Matzen@.discussions.microsoft.com> wrote in message
> news:ADAE2C5C-C301-4F72-966C-C6AB4EAB9FF7@.microsoft.com...
> rows
> clustered
> for
> me
> time,
> like
> processor
> need
> the
>
>
|||Matzen wrote:
> Hi Uri,
> As you can see in my post, the query including the hint performs at
> least 60 times better than without it (without > 1 minute, with hint
> less than a second).
> With the clustered index ... well 80 million rows are currently
> clustered by ID, resorting them to sort by another column may take a
> while ... may be next year.
> My "problem" is that I cannot belive that a query optimizer like the
> one of sql server does not recognize that the "where" statement
> reduces the amount of data to be processed from 80 million to 3000
> (table statistics are up to date), because this analysis is not
> really compex: 1) the "where" does contain an identity field, that is
> equal to the clustered PK
> 2) the condition in the where stament eleminates > 90% of the data to
> be processed
> This must be a bug, so I assume this to be removed in the next
> service pack.
> CU,
> Sven
>
The problem you are seeing is not really a bug, but a known issue with
the query optimizer. Many times SQL Server decides that based on the
number of rows likely to be returned from a query that using an index
(index seek + bookmark lookup for a non-clustered index or just a
clustered index seek for a clustered index) is actually more work than
scanning the table.
It's my understanding that SQL Server fails over to a table scan /
clustered index scan operation too soon in some cases. Your case just
may be one of those. I would keep the index hint and just keep an eye on
the query.
David Gugick
Imceda Software
www.imceda.com

Aggregation Queries with > 80.000.000 rows

Hi,
We have a logging database with a table "Logs" that hold 80.000.000 log rows
(about 1 year of application logs). We need to analyze this data in for
variable time periods, so we cannot simply split the table. The table
contains a column "LogID" (Identity INT and the only member of the clustered
PK) and a column "Time" (datetime there exists an index for this column).
The query
SELECT *
FROM Logs WITH (nolock)
WHERE LogID > 110385284
takes 2 seconds and returns about 3000 rows.
The query
SELECT MAX(Time), MIN(Time)
FROM Logs WITH (nolock)
WHERE LogID > 110385284
takes more than a minute (I've cancelled the query after a minute).
How can this be? The query analyzer shows me a really simple query plan for
the first query using the PK. For the second query, the query plan shows me
no usage of the PK, but usage of the index for the column "Time" (two time,
one for min and one for max).
Even more strange is this query:
SELECT MAX(Logs1.Time), MIN(Logs1.Time)
FROM Logs Logs1 WITH (nolock)
INNER JOIN Logs Logs2 WITH (nolock) ON Logs1.LogID = Logs2.LogID
WHERE Logs1.LogID > 110385284
the query finishes in less than a second, and is using the PK.
Also specifying the PK as an index hint solves the problem, but I don't like
to specify query hints, because this may prevent an updated query processor
to use a better query plan.
Another problem is that we also have less expirienced programmers that need
to deal with such tables, so at the moment I need to review each and every
"slow" query and insert query hints. Will there be a fix that "optimizes" the
query engine of sql server?
CU,
SvenMatzen
Yes, SQL Server needs to do some extra work when you specify an aggregate
functions.
Look, you may want to use INDEX hint to dictate the query optimizer to use
PK,but generally speaking it's not a good idea because the Optimizer is
smart enough to create much more efficient execution plan.
Also,you may create a clustered index on Time column and see how it does the
work.
"Matzen" <Matzen@.discussions.microsoft.com> wrote in message
news:ADAE2C5C-C301-4F72-966C-C6AB4EAB9FF7@.microsoft.com...
> Hi,
> We have a logging database with a table "Logs" that hold 80.000.000 log
rows
> (about 1 year of application logs). We need to analyze this data in for
> variable time periods, so we cannot simply split the table. The table
> contains a column "LogID" (Identity INT and the only member of the
clustered
> PK) and a column "Time" (datetime there exists an index for this column).
> The query
> SELECT *
> FROM Logs WITH (nolock)
> WHERE LogID > 110385284
> takes 2 seconds and returns about 3000 rows.
> The query
> SELECT MAX(Time), MIN(Time)
> FROM Logs WITH (nolock)
> WHERE LogID > 110385284
> takes more than a minute (I've cancelled the query after a minute).
> How can this be? The query analyzer shows me a really simple query plan
for
> the first query using the PK. For the second query, the query plan shows
me
> no usage of the PK, but usage of the index for the column "Time" (two
time,
> one for min and one for max).
> Even more strange is this query:
> SELECT MAX(Logs1.Time), MIN(Logs1.Time)
> FROM Logs Logs1 WITH (nolock)
> INNER JOIN Logs Logs2 WITH (nolock) ON Logs1.LogID = Logs2.LogID
> WHERE Logs1.LogID > 110385284
> the query finishes in less than a second, and is using the PK.
> Also specifying the PK as an index hint solves the problem, but I don't
like
> to specify query hints, because this may prevent an updated query
processor
> to use a better query plan.
> Another problem is that we also have less expirienced programmers that
need
> to deal with such tables, so at the moment I need to review each and every
> "slow" query and insert query hints. Will there be a fix that "optimizes"
the
> query engine of sql server?
> CU,
> Sven|||Hi Uri,
As you can see in my post, the query including the hint performs at least 60
times better than without it (without > 1 minute, with hint less than a
second).
With the clustered index ... well 80 million rows are currently clustered by
ID, resorting them to sort by another column may take a while ... may be next
year.
My "problem" is that I cannot belive that a query optimizer like the one of
sql server does not recognize that the "where" statement reduces the amount
of data to be processed from 80 million to 3000 (table statistics are up to
date), because this analysis is not really compex:
1) the "where" does contain an identity field, that is equal to the
clustered PK
2) the condition in the where stament eleminates > 90% of the data to be
processed
This must be a bug, so I assume this to be removed in the next service pack.
CU,
Sven
"Uri Dimant" wrote:
> Matzen
> Yes, SQL Server needs to do some extra work when you specify an aggregate
> functions.
> Look, you may want to use INDEX hint to dictate the query optimizer to use
> PK,but generally speaking it's not a good idea because the Optimizer is
> smart enough to create much more efficient execution plan.
> Also,you may create a clustered index on Time column and see how it does the
> work.
>
>
>
>
> "Matzen" <Matzen@.discussions.microsoft.com> wrote in message
> news:ADAE2C5C-C301-4F72-966C-C6AB4EAB9FF7@.microsoft.com...
> > Hi,
> >
> > We have a logging database with a table "Logs" that hold 80.000.000 log
> rows
> > (about 1 year of application logs). We need to analyze this data in for
> > variable time periods, so we cannot simply split the table. The table
> > contains a column "LogID" (Identity INT and the only member of the
> clustered
> > PK) and a column "Time" (datetime there exists an index for this column).
> >
> > The query
> > SELECT *
> > FROM Logs WITH (nolock)
> > WHERE LogID > 110385284
> > takes 2 seconds and returns about 3000 rows.
> >
> > The query
> > SELECT MAX(Time), MIN(Time)
> > FROM Logs WITH (nolock)
> > WHERE LogID > 110385284
> > takes more than a minute (I've cancelled the query after a minute).
> >
> > How can this be? The query analyzer shows me a really simple query plan
> for
> > the first query using the PK. For the second query, the query plan shows
> me
> > no usage of the PK, but usage of the index for the column "Time" (two
> time,
> > one for min and one for max).
> > Even more strange is this query:
> > SELECT MAX(Logs1.Time), MIN(Logs1.Time)
> > FROM Logs Logs1 WITH (nolock)
> > INNER JOIN Logs Logs2 WITH (nolock) ON Logs1.LogID = Logs2.LogID
> > WHERE Logs1.LogID > 110385284
> > the query finishes in less than a second, and is using the PK.
> >
> > Also specifying the PK as an index hint solves the problem, but I don't
> like
> > to specify query hints, because this may prevent an updated query
> processor
> > to use a better query plan.
> >
> > Another problem is that we also have less expirienced programmers that
> need
> > to deal with such tables, so at the moment I need to review each and every
> > "slow" query and insert query hints. Will there be a fix that "optimizes"
> the
> > query engine of sql server?
> >
> > CU,
> > Sven
>
>|||Matzen wrote:
> Hi Uri,
> As you can see in my post, the query including the hint performs at
> least 60 times better than without it (without > 1 minute, with hint
> less than a second).
> With the clustered index ... well 80 million rows are currently
> clustered by ID, resorting them to sort by another column may take a
> while ... may be next year.
> My "problem" is that I cannot belive that a query optimizer like the
> one of sql server does not recognize that the "where" statement
> reduces the amount of data to be processed from 80 million to 3000
> (table statistics are up to date), because this analysis is not
> really compex: 1) the "where" does contain an identity field, that is
> equal to the clustered PK
> 2) the condition in the where stament eleminates > 90% of the data to
> be processed
> This must be a bug, so I assume this to be removed in the next
> service pack.
> CU,
> Sven
>
The problem you are seeing is not really a bug, but a known issue with
the query optimizer. Many times SQL Server decides that based on the
number of rows likely to be returned from a query that using an index
(index seek + bookmark lookup for a non-clustered index or just a
clustered index seek for a clustered index) is actually more work than
scanning the table.
It's my understanding that SQL Server fails over to a table scan /
clustered index scan operation too soon in some cases. Your case just
may be one of those. I would keep the index hint and just keep an eye on
the query.
David Gugick
Imceda Software
www.imceda.com

Aggregation Queries with > 80.000.000 rows

Hi,
We have a logging database with a table "Logs" that hold 80.000.000 log rows
(about 1 year of application logs). We need to analyze this data in for
variable time periods, so we cannot simply split the table. The table
contains a column "LogID" (Identity INT and the only member of the clustered
PK) and a column "Time" (datetime there exists an index for this column).
The query
SELECT *
FROM Logs WITH (nolock)
WHERE LogID > 110385284
takes 2 seconds and returns about 3000 rows.
The query
SELECT MAX(Time), MIN(Time)
FROM Logs WITH (nolock)
WHERE LogID > 110385284
takes more than a minute (I've cancelled the query after a minute).
How can this be? The query analyzer shows me a really simple query plan for
the first query using the PK. For the second query, the query plan shows me
no usage of the PK, but usage of the index for the column "Time" (two time,
one for min and one for max).
Even more strange is this query:
SELECT MAX(Logs1.Time), MIN(Logs1.Time)
FROM Logs Logs1 WITH (nolock)
INNER JOIN Logs Logs2 WITH (nolock) ON Logs1.LogID = Logs2.LogID
WHERE Logs1.LogID > 110385284
the query finishes in less than a second, and is using the PK.
Also specifying the PK as an index hint solves the problem, but I don't like
to specify query hints, because this may prevent an updated query processor
to use a better query plan.
Another problem is that we also have less expirienced programmers that need
to deal with such tables, so at the moment I need to review each and every
"slow" query and insert query hints. Will there be a fix that "optimizes" th
e
query engine of sql server?
CU,
SvenMatzen
Yes, SQL Server needs to do some extra work when you specify an aggregate
functions.
Look, you may want to use INDEX hint to dictate the query optimizer to use
PK,but generally speaking it's not a good idea because the Optimizer is
smart enough to create much more efficient execution plan.
Also,you may create a clustered index on Time column and see how it does the
work.
"Matzen" <Matzen@.discussions.microsoft.com> wrote in message
news:ADAE2C5C-C301-4F72-966C-C6AB4EAB9FF7@.microsoft.com...
> Hi,
> We have a logging database with a table "Logs" that hold 80.000.000 log
rows
> (about 1 year of application logs). We need to analyze this data in for
> variable time periods, so we cannot simply split the table. The table
> contains a column "LogID" (Identity INT and the only member of the
clustered
> PK) and a column "Time" (datetime there exists an index for this column).
> The query
> SELECT *
> FROM Logs WITH (nolock)
> WHERE LogID > 110385284
> takes 2 seconds and returns about 3000 rows.
> The query
> SELECT MAX(Time), MIN(Time)
> FROM Logs WITH (nolock)
> WHERE LogID > 110385284
> takes more than a minute (I've cancelled the query after a minute).
> How can this be? The query analyzer shows me a really simple query plan
for
> the first query using the PK. For the second query, the query plan shows
me
> no usage of the PK, but usage of the index for the column "Time" (two
time,
> one for min and one for max).
> Even more strange is this query:
> SELECT MAX(Logs1.Time), MIN(Logs1.Time)
> FROM Logs Logs1 WITH (nolock)
> INNER JOIN Logs Logs2 WITH (nolock) ON Logs1.LogID = Logs2.LogID
> WHERE Logs1.LogID > 110385284
> the query finishes in less than a second, and is using the PK.
> Also specifying the PK as an index hint solves the problem, but I don't
like
> to specify query hints, because this may prevent an updated query
processor
> to use a better query plan.
> Another problem is that we also have less expirienced programmers that
need
> to deal with such tables, so at the moment I need to review each and every
> "slow" query and insert query hints. Will there be a fix that "optimizes"
the
> query engine of sql server?
> CU,
> Sven|||Hi Uri,
As you can see in my post, the query including the hint performs at least 60
times better than without it (without > 1 minute, with hint less than a
second).
With the clustered index ... well 80 million rows are currently clustered by
ID, resorting them to sort by another column may take a while ... may be nex
t
year.
My "problem" is that I cannot belive that a query optimizer like the one of
sql server does not recognize that the "where" statement reduces the amount
of data to be processed from 80 million to 3000 (table statistics are up to
date), because this analysis is not really compex:
1) the "where" does contain an identity field, that is equal to the
clustered PK
2) the condition in the where stament eleminates > 90% of the data to be
processed
This must be a bug, so I assume this to be removed in the next service pack.
CU,
Sven
"Uri Dimant" wrote:

> Matzen
> Yes, SQL Server needs to do some extra work when you specify an aggregate
> functions.
> Look, you may want to use INDEX hint to dictate the query optimizer to use
> PK,but generally speaking it's not a good idea because the Optimizer is
> smart enough to create much more efficient execution plan.
> Also,you may create a clustered index on Time column and see how it does t
he
> work.
>
>
>
>
> "Matzen" <Matzen@.discussions.microsoft.com> wrote in message
> news:ADAE2C5C-C301-4F72-966C-C6AB4EAB9FF7@.microsoft.com...
> rows
> clustered
> for
> me
> time,
> like
> processor
> need
> the
>
>|||Matzen wrote:
> Hi Uri,
> As you can see in my post, the query including the hint performs at
> least 60 times better than without it (without > 1 minute, with hint
> less than a second).
> With the clustered index ... well 80 million rows are currently
> clustered by ID, resorting them to sort by another column may take a
> while ... may be next year.
> My "problem" is that I cannot belive that a query optimizer like the
> one of sql server does not recognize that the "where" statement
> reduces the amount of data to be processed from 80 million to 3000
> (table statistics are up to date), because this analysis is not
> really compex: 1) the "where" does contain an identity field, that is
> equal to the clustered PK
> 2) the condition in the where stament eleminates > 90% of the data to
> be processed
> This must be a bug, so I assume this to be removed in the next
> service pack.
> CU,
> Sven
>
The problem you are seeing is not really a bug, but a known issue with
the query optimizer. Many times SQL Server decides that based on the
number of rows likely to be returned from a query that using an index
(index seek + bookmark lookup for a non-clustered index or just a
clustered index seek for a clustered index) is actually more work than
scanning the table.
It's my understanding that SQL Server fails over to a table scan /
clustered index scan operation too soon in some cases. Your case just
may be one of those. I would keep the index hint and just keep an eye on
the query.
David Gugick
Imceda Software
www.imceda.com

Saturday, February 25, 2012

agents blocking each other

Contantly there seems to be a blocking issue between the distribution agent,
the log agent, and the distribution clean up agent.
This will eventually cause one of the agents to shut down (the log agent).
What can I do to elevate this situation and why is it happening?
--Kristy
Avoid using EM.
Through Query Analyzer run the distribution clean up agent at a time when
there are few users on the system.
Consider moving to pull subscriptions and possibly a remote distributor.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:%235Ev4idHFHA.3628@.TK2MSFTNGP15.phx.gbl...
> Contantly there seems to be a blocking issue between the distribution
agent,
> the log agent, and the distribution clean up agent.
> This will eventually cause one of the agents to shut down (the log agent).
> What can I do to elevate this situation and why is it happening?
> --Kristy
>
|||Thanks.
Why is it happening and what do the suggestions you provide buy me? Also,
would it be okay to schedule a job to run it? That way the off peak hours
could be utilized best. (I do have to sleep at some point ;-) )
Thanks again,
(and still anxiously awaiting my order to arrive)
Kristy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u0iLc2gHFHA.2132@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Avoid using EM.
> Through Query Analyzer run the distribution clean up agent at a time when
> there are few users on the system.
> Consider moving to pull subscriptions and possibly a remote distributor.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:%235Ev4idHFHA.3628@.TK2MSFTNGP15.phx.gbl...
> agent,
agent).
>
|||In addition Hilary, what does pull subscriptions offer over push ?
"Kristy" <pleasepostreply@.here.com> wrote in message
news:enALdThHFHA.1176@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Thanks.
> Why is it happening and what do the suggestions you provide buy me? Also,
> would it be okay to schedule a job to run it? That way the off peak hours
> could be utilized best. (I do have to sleep at some point ;-) )
> Thanks again,
> (and still anxiously awaiting my order to arrive)
> Kristy
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u0iLc2gHFHA.2132@.TK2MSFTNGP14.phx.gbl...
when
> agent).
>
|||EM, does lots of enumeration and runs far more commands than a simple
command (lets say sp_addsubscription) does. Run profiler to see this.
So if you run a command directly in isqlw you get far less locking.
You can schedule a job to run it, but then you have to log error messages
and make sure that everything runs fine. I would advise you to set your
alarm for 2:00 AM and then log in and do it at that time.
Another thing you can do is stop SQL Server Agent, make your change, and
then restart it.
Pull subscribers run the distribution agent on the subscriber, it connects
with the distribution database on the publisher and results in better
performance on the publisher and less locking.
You might also want to stagger when you agents run - for instance stagger
them to run at a prime number - like every 7 minutes, and don't run them
continuously.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasepostreply@.here.com> wrote in message
news:enALdThHFHA.1176@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Thanks.
> Why is it happening and what do the suggestions you provide buy me? Also,
> would it be okay to schedule a job to run it? That way the off peak hours
> could be utilized best. (I do have to sleep at some point ;-) )
> Thanks again,
> (and still anxiously awaiting my order to arrive)
> Kristy
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u0iLc2gHFHA.2132@.TK2MSFTNGP14.phx.gbl...
when
> agent).
>
|||btw - with pull subscriptions you have no central point of administration.
For instance, with push you connect to your publisher in EM and manage your
replication solution. With pull you have to connect to each subscriber and
expand replication monitor to see errors. Make sure you use Replication
Monitor Group to show all your subscribers (use Tools, Replication, Show
Replication Monitor Group and go to the bottom node and right click on
Replication Monitor Group and select add distributor to monitor).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasepostreply@.here.com> wrote in message
news:enALdThHFHA.1176@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Thanks.
> Why is it happening and what do the suggestions you provide buy me? Also,
> would it be okay to schedule a job to run it? That way the off peak hours
> could be utilized best. (I do have to sleep at some point ;-) )
> Thanks again,
> (and still anxiously awaiting my order to arrive)
> Kristy
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u0iLc2gHFHA.2132@.TK2MSFTNGP14.phx.gbl...
when
> agent).
>
|||answered above.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eQ1oolhHFHA.2420@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> In addition Hilary, what does pull subscriptions offer over push ?
> "Kristy" <pleasepostreply@.here.com> wrote in message
> news:enALdThHFHA.1176@.TK2MSFTNGP12.phx.gbl...
Also,[vbcol=seagreen]
hours[vbcol=seagreen]
> when
distributor.[vbcol=seagreen]
distribution
>

Sunday, February 19, 2012

After Transaction Log Grows, it won't shrink.

The other day I had someone truncate an extremely large transaction log (100GB) but it wouldn't return the space to the OS, not even after a backup and restore operation. There’s an interesting bit of trivia around shrinking this file. When the log file is truncated, you may not see all of the space returned to the OS. The reason is the way the data might be spread. Here’s the way we think data looks in the log file:

<BeginFile>xxxxxxxx--<EndFile>

So when you truncate it and it gets another couple of transactions, it should do this:

But in fact it might look more like this:

<BeginFile>xx-xx-xxxxxxx<EndFile>

So if you truncate the entries and a couple are left it really looks like this:

So when you shrink it, you don’t gain any space back because of this file marking. So what do you do?

There are two ways to shrink a large log file, both of which are a bit painful, and not always practical in production. The first is to write enough transactions into the log to “bump” the data all the way to the end. Since that makes it contiguous, when you truncate it you’ll get the shrink you’re looking for – probably. You can do this by creating a table, adding thousands or millions of rows to it, and then deleting them and the table. Of course this slows down a production system while you do it, and it’s difficult to figure the math.

The other way is more problematic in a production system, but works every time. Take a full backup of the database. Follow the process to “detach” the database as described in Books Online. Find the transaction log file for that database (and only that database) and delete it. Then, “attach” the database. The system will automatically build a new, clean log file. Obviously, there are a lot of places you can be careless here, so I recommend this process only for extreme cases.

Hi,

have a look here:

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

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Good info - but you'll find that the DBCC shrink commands won't work with the TL as I mentioned. It's less a question than some real-world experience - but thanks for the links! I think they will help people to understand how the logs and files are used.

Thursday, February 16, 2012

After replication how to TRUNCATE the LOG

After replication, my log has grown upto 300 MB and my Database size is of 20 MB.I want to truncate my LOG.Please let me know how to do it.
Help Needed
Thanks & Regards
Asif
Asif,
have a look at http://support.microsoft.com/?id=272318
hth,
Paul

After MSDE upgrade to SQL 2005, MDF is not found

After upgrading an MSDE 2000 instance to SQL 2005, the database appears to be intact - according to the log file:

2007-08-28 13:51:13.78 spid11s Starting up database 'HysterSuiteMainSQL'.

Then after a system shutdown:

2007-08-28 13:57:43.57 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.

It appears the MDF file disappeared mysteriously:

2007-08-28 14:03:11.84 spid51 Starting up database 'HysterSuiteMainSQL'.
2007-08-28 14:03:11.85 spid51 Error: 17207, Severity: 16, State: 1.
2007-08-28 14:03:11.85 spid51 FCB: Surprise pen: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\PROGRAM FILES\HYSTER SUITE\MSDE\Data\MSSQL$HS2000\Data\Hyster.mdf'. Diagnose and correct the operating system error, and retry the operation.

Has anyone encountered this issue? Any ideas on what might have gone wrong?

Thanks,

Mike

After upgrading to sql 2005 did you change the startup account of sql 2005 ? if yes may be the startup account might not have modify privilege to the path where the mdf and ldf resides........as the error is explicit that its related to permission.....

after moving tempdb SQL Server 2005 won't start

Greetings.

After altering the database to move tempdb to a new location SQL Server 2005 will not start. The error log follows. I can't seem to set the permission of the directory. Any suggestions on what to do next would be appreciated.

alan

2007-03-01 13:32:21.23 spid9s Clearing tempdb database.
2007-03-01 13:32:21.25 spid9s Error: 5123, Severity: 16, State: 1.
2007-03-01 13:32:21.25 spid9s CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.
2007-03-01 13:32:21.25 spid9s Error: 17207, Severity: 16, State: 1.
2007-03-01 13:32:21.25 spid9s FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'. Diagnose and correct the operating system error, and retry the operation.
2007-03-01 13:32:21.25 spid9s Error: 17204, Severity: 16, State: 1.
2007-03-01 13:32:21.25 spid9s FCB::Open failed: Could not open file D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data for file number 1. OS error: 5(Access is denied.).

what error says is the Startup account of MSSQLSERVER Service does not have permission to the new location of Tempdb ie. 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.. Give proper permission and try

Madhu

|||

Thanks.

I tried it, including changing the inheritance properties of the directory, but still get an error message. After changing the permissions, nothing is added to SQL Server error log.

alan

|||

It might be worth deleting any tempdb files that already exist in the new folder. It could be that the SQL Server service account doesn't have permissions to overwrite the existing files - this can happen if the files aren't set to inherit permissions. The tempdb database files will be re-created when SQL Server starts.

Chris

after moving tempdb SQL Server 2005 won't start

Greetings.

alan

Madhu

|||

Thanks.

I tried it, including changing the inheritance properties of the directory, but still get an error message. After changing the permissions, nothing is added to SQL Server error log.

alan

|||

Chris

Monday, February 13, 2012

After I change Log on of SQL Server 2005, my SQL service agent can not start, any idea?

I use localsystem to start SQL 2005, and use a domain/user to start SQL 2005 agent, the user is an domain admin and local admin, which works.

I had problem to use openrowset, so I changed the SQL 2005 to use the same domain.user to start, which works, after that, I can use openrowset well, but now, I can not start SQL 2005 agent, I get error:

SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).

and another enry in event viewer, not sure if they are related

SQL Server blocked access to procedure 'dbo.sp_sqlagent_get_startup_info' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.

It is SQL 2005 on window 2003.

Any idea how to fix it?

thanks!

In sql 2005 the agent xps are turned 'off by default' so I think the first thing you need to do is turn on the Agent Xps.

exec sp_configure 'show advanced options' , 1 ;reconfigure

exec sp_configure 'Agent XPs',1;reconfigure

hth,

-Steven Gott

S/DET

SQL Server

p.s. SQL 2005 does not need to run as a domain admin , It would be much safer if you set it to run as a normal user b/c the high privilleges of the service account combined with some of the xps like xp_cmdshell allow a dba who is sysadmin to manipulate your domain as the domain admin.

|||

I had problem to use openrowset, so I changed the SQL 2005 to use the same domain.user to start, which works, after that, I can use openrowset well ...

I suspect that your problem with using OPENROWSET had to do with permissions to access the file you were attempting to open. Since your original SQL Server account was not a domain account, you would not have been able to access domain resources, including files, with the MSSQL service. Once you changed to use the domain account that was set up for SQL Agent, the OPENROWSET worked since it could now access domain resources.

|||

Hi,

I think you mixed SQL server account with SQL server agent account, I think it is SQL server account that will access the files for openrowset.

Well, I changed the account for my SQL server from local system to a domain admin, that solves the openrowset problem, but more other problem come in.

First, the Memory used by SQL keeps going up, reached 1.7G, I remember how to solve this, I need give the account "lock page in memory" permission, that solves it

Second, I got error, my CLR reports problem, it says

A .NET Framework error occurred during execution of user-defined routine or aggregate "linkclr_x_pdf_createpdf":
System.DllNotFoundException: Unable to load DLL 'ABCpdfCE6.dll': Not enough storage is available to process this command.

When the error happens, the SQL only use 170M memory, the server has 3G memory.

Really a big problem, how can I just set an account with fully localsystem permssion plus permission to access domain file? Since the localsystem worked fine except the openrowset problem.

thanks

|||

My God, after caused me so much trouble, I found the openrowset may have nothing to do with the account. It only works for some time after each restart, even with local system, it works fine, it can access remote files, maybe because I set the permission to the sql server.

But the issue is it stops working after some time, someone said to set permission to temp folder, doesn't work for me yet.

Isn't that a big bug? could someone from the SQL team to shed a light here? I havn't been asking for the solution for many months.

|||

Steve,

when I run this command I get

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 5845, Level 16, State 1, Line 1

Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.

It just will not let me turn it on ..... is there any other way to change the option?

thank you

mitch

|||

You can try using the SQL Server Surface Area Configuration tool.

It will be in the configuration Tools folder under SQL Server

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Steve,

I tried this and I assume its the ole component? but I was able to fix some of the problem but it says it is not part of the sysadmin role which it is. I just dont know where to go from here.

thanks

mitch

After I change Log on of SQL Server 2005, my SQL service agent can not start, any idea?

I use localsystem to start SQL 2005, and use a domain/user to start SQL 2005 agent, the user is an domain admin and local admin, which works.

SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).

and another enry in event viewer, not sure if they are related

It is SQL 2005 on window 2003.

Any idea how to fix it?

thanks!

In sql 2005 the agent xps are turned 'off by default' so I think the first thing you need to do is turn on the Agent Xps.

exec sp_configure 'show advanced options' , 1 ;reconfigure

exec sp_configure 'Agent XPs',1;reconfigure

hth,

-Steven Gott

S/DET

SQL Server

|||

I had problem to use openrowset, so I changed the SQL 2005 to use the same domain.user to start, which works, after that, I can use openrowset well ...

|||

Hi,

I think you mixed SQL server account with SQL server agent account, I think it is SQL server account that will access the files for openrowset.

Well, I changed the account for my SQL server from local system to a domain admin, that solves the openrowset problem, but more other problem come in.

First, the Memory used by SQL keeps going up, reached 1.7G, I remember how to solve this, I need give the account "lock page in memory" permission, that solves it

Second, I got error, my CLR reports problem, it says

When the error happens, the SQL only use 170M memory, the server has 3G memory.

Really a big problem, how can I just set an account with fully localsystem permssion plus permission to access domain file? Since the localsystem worked fine except the openrowset problem.

thanks

|||

But the issue is it stops working after some time, someone said to set permission to temp folder, doesn't work for me yet.

Isn't that a big bug? could someone from the SQL team to shed a light here? I havn't been asking for the solution for many months.

|||

Steve,

when I run this command I get

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 5845, Level 16, State 1, Line 1

Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.

It just will not let me turn it on ..... is there any other way to change the option?

thank you

mitch

|||

You can try using the SQL Server Surface Area Configuration tool.

It will be in the configuration Tools folder under SQL Server

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Steve,

I tried this and I assume its the ole component? but I was able to fix some of the problem but it says it is not part of the sysadmin role which it is. I just dont know where to go from here.

thanks

mitch

Sunday, February 12, 2012

After DBCC SHRINKFILE

After DBCC SHRINKFILE(dbname_Log,10)
The message:
“Cannot shrink log file 2 (dbname_Log) because all logical log files are i
n
use.”
had been appeared.
How to decrease the log file size ?Did you backup the transaction log before trying to shrink it?
AMB
"Alur" wrote:

> After DBCC SHRINKFILE(dbname_Log,10)
> The message:
> “Cannot shrink log file 2 (dbname_Log) because all logical log files are
in
> use.”
> had been appeared.
> How to decrease the log file size ?
>|||... and sometimes you have to do this several times (backup, shrink, backup
, shrink...) Check out
DBCC LOGINFO (Google etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:67DA0AAF-F3F6-4AA4-8C55-4F86F916A02E@.microsoft.com...
> Did you backup the transaction log before trying to shrink it?
>
> AMB
> "Alur" wrote:
>|||I could not backup the transaction log.
"Alejandro Mesa" wrote:
> Did you backup the transaction log before trying to shrink it?
>
> AMB
> "Alur" wrote:
>|||What error are you getting when trying to backup the transaction log?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alur" <Alur@.discussions.microsoft.com> wrote in message
news:3B228137-B620-408E-9F8A-435F00286FAA@.microsoft.com...
>I could not backup the transaction log.
> "Alejandro Mesa" wrote:
>|||Something about insufficient disk space. And I cannot increase the space now
.
"Mike Epprecht (SQL MVP)" wrote:

> What error are you getting when trying to backup the transaction log?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Alur" <Alur@.discussions.microsoft.com> wrote in message
> news:3B228137-B620-408E-9F8A-435F00286FAA@.microsoft.com...
>
>

Thursday, February 9, 2012

advice on transaction log

I have a database that i make a copy of everynight. I delete all the tables
in the destination database and recreate it, ensuring that the destination
database reflects all the new tables and columns from the original. This
destination database is used for reporting only, so the last step i do in
the copy is set the destination database to readonly. The copy is done
using a dts package that is scheduled to run at a given time everynight.
Since i delete all the tables everynight, i don't do backups. Therefore my
transaction log continues to grow. I don't ever need any information from
the transaction log, since if the database fails then i would just recreate
the whole database by running the dts package (this only takes 10 minutes).
What is the best way to keep the transction log to a minimum size.
Thank you
Mike
Set the recovery mode to Simple on the readonly database. That will
cause any transactions completed to be automatically truncated from
the log.
Allen White
On Wed, 26 Jan 2005 10:34:28 -0500, "Mike Read"
<mike.read.spam.no@.allwaresolutions.com> wrote:

>I have a database that i make a copy of everynight. I delete all the tables
>in the destination database and recreate it, ensuring that the destination
>database reflects all the new tables and columns from the original. This
>destination database is used for reporting only, so the last step i do in
>the copy is set the destination database to readonly. The copy is done
>using a dts package that is scheduled to run at a given time everynight.
>Since i delete all the tables everynight, i don't do backups. Therefore my
>transaction log continues to grow. I don't ever need any information from
>the transaction log, since if the database fails then i would just recreate
>the whole database by running the dts package (this only takes 10 minutes).
>What is the best way to keep the transction log to a minimum size.
>Thank you
>Mike
>
|||Mike
The answer to your question is that you need to set your database to simple
recovery mode.
I would however suggest you look into using log shipping or snapshot
replication to maintain your read only database. These are much better
methods to do what you want.
Hope this helps
John
"Mike Read" wrote:

> I have a database that i make a copy of everynight. I delete all the tables
> in the destination database and recreate it, ensuring that the destination
> database reflects all the new tables and columns from the original. This
> destination database is used for reporting only, so the last step i do in
> the copy is set the destination database to readonly. The copy is done
> using a dts package that is scheduled to run at a given time everynight.
> Since i delete all the tables everynight, i don't do backups. Therefore my
> transaction log continues to grow. I don't ever need any information from
> the transaction log, since if the database fails then i would just recreate
> the whole database by running the dts package (this only takes 10 minutes).
> What is the best way to keep the transction log to a minimum size.
> Thank you
> Mike
>
>

advice on transaction log

I have a database that i make a copy of everynight. I delete all the tables
in the destination database and recreate it, ensuring that the destination
database reflects all the new tables and columns from the original. This
destination database is used for reporting only, so the last step i do in
the copy is set the destination database to readonly. The copy is done
using a dts package that is scheduled to run at a given time everynight.
Since i delete all the tables everynight, i don't do backups. Therefore my
transaction log continues to grow. I don't ever need any information from
the transaction log, since if the database fails then i would just recreate
the whole database by running the dts package (this only takes 10 minutes).
What is the best way to keep the transction log to a minimum size.
Thank you
MikeSet the recovery mode to Simple on the readonly database. That will
cause any transactions completed to be automatically truncated from
the log.
Allen White
On Wed, 26 Jan 2005 10:34:28 -0500, "Mike Read"
<mike.read.spam.no@.allwaresolutions.com> wrote:

>I have a database that i make a copy of everynight. I delete all the table
s
>in the destination database and recreate it, ensuring that the destination
>database reflects all the new tables and columns from the original. This
>destination database is used for reporting only, so the last step i do in
>the copy is set the destination database to readonly. The copy is done
>using a dts package that is scheduled to run at a given time everynight.
>Since i delete all the tables everynight, i don't do backups. Therefore my
>transaction log continues to grow. I don't ever need any information from
>the transaction log, since if the database fails then i would just recreate
>the whole database by running the dts package (this only takes 10 minutes).
>What is the best way to keep the transction log to a minimum size.
>Thank you
>Mike
>|||Mike
The answer to your question is that you need to set your database to simple
recovery mode.
I would however suggest you look into using log shipping or snapshot
replication to maintain your read only database. These are much better
methods to do what you want.
Hope this helps
John
"Mike Read" wrote:

> I have a database that i make a copy of everynight. I delete all the tabl
es
> in the destination database and recreate it, ensuring that the destination
> database reflects all the new tables and columns from the original. This
> destination database is used for reporting only, so the last step i do in
> the copy is set the destination database to readonly. The copy is done
> using a dts package that is scheduled to run at a given time everynight.
> Since i delete all the tables everynight, i don't do backups. Therefore m
y
> transaction log continues to grow. I don't ever need any information from
> the transaction log, since if the database fails then i would just recreat
e
> the whole database by running the dts package (this only takes 10 minutes)
.
> What is the best way to keep the transction log to a minimum size.
> Thank you
> Mike
>
>

Tuesday, March 27, 2012

Aliases not found on log in

Sunday, March 25, 2012

Alerts and Jobs for transaction log processing

Thursday, March 22, 2012

Alert not triggering

Alert not triggering

Alert not triggering

Tuesday, March 20, 2012

Alert Demo: Sev. 25 Errors - Fatal Error !

Monday, March 19, 2012

Aggregation Queries with > 80.000.000 rows

Aggregation Queries with > 80.000.000 rows

Aggregation Queries with > 80.000.000 rows

Saturday, February 25, 2012

agents blocking each other

Sunday, February 19, 2012

After Transaction Log Grows, it won't shrink.

Thursday, February 16, 2012

After replication how to TRUNCATE the LOG

After MSDE upgrade to SQL 2005, MDF is not found

after moving tempdb SQL Server 2005 won't start

after moving tempdb SQL Server 2005 won't start

Monday, February 13, 2012

After I change Log on of SQL Server 2005, my SQL service agent can not start, any idea?

After I change Log on of SQL Server 2005, my SQL service agent can not start, any idea?

Sunday, February 12, 2012

After DBCC SHRINKFILE

Thursday, February 9, 2012

advice on transaction log

advice on transaction log

SQL tool for MSDE

Blog Archive

About Me