Hiya : Full msdb Log message

Could anyone tell me how to check the size of the current msdb and tempdb log files and how do I deal with the following alerts :
Full msdb log Error 9002
Full tempdb Error 9002
Mnay thanks
ZahedFist off, since this thread is much more technical than it is "Hello, world" in nature, I'm going to move it from the New Users and Introductions to the MS-SQL forum.

Next, there are multiple ways to check the size of the databases. Assuming that you are running SQL 2000, probably the easiest way is to open SQL Enterprise Manager, in the navigation pane (leftmost on most systems) you'll find a tree control... Pick your server, then databases, then the database that interests you (such as msdb or tempdb). Right click, and select Properites from the menu. You'll see tabs for data and log files.

If you need to "clean house" because of a log file being full, close the properties dialog, right click again, select All Tasks, then Shrink Database.

-PatP|||Refer to BOoks online for this error, that has complete information to recover it. I would also suggest to check what kind of operation is making this alerts raised and add more disks to accomodate the resource intensive operations.

history of Process info

i think i might a have a slight clue about this but need help...
in SQL Server Enterprise Manager -- Management -- Current Activity --
Process Info i can see the current info for SPIDs and users and all sorts of
great info.
How can i view that same info from yesterday at a specific time?
thanks a million.
You could use a log reader tool of which there are many to choose eg
Lumigent's LogExplorer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||"Paul Ibison" wrote:

> You could use a log reader tool of which there are many to choose eg
> Lumigent's LogExplorer.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
> Lumgent wants $995
|||Ok there's a new low-cost ($195) alternative from Redgate:
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm.
BTW I'm assuming that you actually have the log as a backup, or have been
using full recovery mode. if you've used simple then this strategy won't
work for the particular transaction you're looking for.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

history of Process info

i think i might a have a slight clue about this but need help...
in SQL Server Enterprise Manager -- Management -- Current Activity --
Process Info i can see the current info for SPIDs and users and all sorts of
great info.
How can i view that same info from yesterday at a specific time?
thanks a million.You could use a log reader tool of which there are many to choose eg
Lumigent's LogExplorer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||"Paul Ibison" wrote:

> You could use a log reader tool of which there are many to choose eg
> Lumigent's LogExplorer.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
> Lumgent wants $995|||Ok there's a new low-cost ($195) alternative from Redgate:
http://www.red-gate.com/products/SQ...scue/index.htm.
BTW I'm assuming that you actually have the log as a backup, or have been
using full recovery mode. if you've used simple then this strategy won't
work for the particular transaction you're looking for.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

historical data

A general data design question:

We have data which changes every week. We had considered seperating
historical records and current records into two different tables with
the same columns, but thought it might be simpler to have them all
together in one table and just add a WeekID int column to indicate
which week it represents (and perhaps an isCurrent bit column to make
querying easier). We have a number of tables like this, holding weekly
data, and we'll have to query for historical data often, but only back
through the last year -- we have historical data going back to 1998 or
so which we'll rarely if ever look at.

Is the all-in-one-table approach better or the seperation of current
and historical data? Will there be a performance hit to organizing data
this way? I don't think the extra columns will make querying too much
more awkward, but is there anything I'm overlooking in this?

Thanks.rottytooth (rottytooth@.gmail.com) writes:
> A general data design question:
> We have data which changes every week. We had considered seperating
> historical records and current records into two different tables with
> the same columns, but thought it might be simpler to have them all
> together in one table and just add a WeekID int column to indicate
> which week it represents (and perhaps an isCurrent bit column to make
> querying easier). We have a number of tables like this, holding weekly
> data, and we'll have to query for historical data often, but only back
> through the last year -- we have historical data going back to 1998 or
> so which we'll rarely if ever look at.
> Is the all-in-one-table approach better or the seperation of current
> and historical data? Will there be a performance hit to organizing data
> this way? I don't think the extra columns will make querying too much
> more awkward, but is there anything I'm overlooking in this?

One thing you could consider is partitioned views, with one table
per year. (So each year you add a new table and change the view.)

A partioned view is constructed as:

SELECT col1, col2, ...
FROM tbl1
UNION ALL
SELECT col1, col2,
FROM tbl2
UNION ALL
...

In the tables there needs to be a check constraint on a column which is
part of the PK, and the constraint should define the range for that
table. Ranges must not overlap.

The point is that when you query the view, SQL Server will only access
the tables that are covered by the condition on the partioning column.

Please see Books Online for further details.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

High Availability Choices from Mgt. Perspective

I need to make a brief presentation to upper management about High Availability options in SQL Server 2005.

Current choices being considered are:

- Failover clustering

- Log shipping

- Mirroring

Q1: Are there other choices?

Q2: How do these choices compare in terms of cost, complexity of setup, ease of deployment, recovery procedures in case of a disaster?

Ben Aminnia

What is the amount of time you can afford to be offline?
The failover time is quite different between the 3 options.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

The obvious answer would be "The quicker the recovery, the better!"

Can you prioritize the 3 methods in terms of recovery speed? e.g. fastest, medium, slowest or 5-minutes, 30 minutes, 2-hours (or something like that)?

We can then look at them from the setup-complexity angle.

Thanks,

Ben

|||

In theory database mirroring provides failover in a matter of seconds when it is combined with .NET 2.0 which supports transparent failover.

Clustering is a matter of minutes and depends on the number of resources and how fast they start on your server.

Log shipping depends a bit on your procedures but it takes a bit more work because there is not automatic way to 'failover' to the log shipping database. You would have to get the database up and running on the destination server and redirect the application servers to the new destination server. All this is not as transparent as database mirroring but many people have more faith in log shipping because it is 'old' technology which has proven itself (just like clustering).

Hope this helps

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Inaddition to what Wesley said, you can refer this link for the comparision features between the 3.........

http://sql-articles.com/articles/diffdbmrr.htm Database mirroring seems to be the best as there's less downtime with automatic failover if you use high availability mode.........

|||

I followed the link and the descriptions seemed a little mixed up. For example, the description under mirroring was really talking about clustering and vice versa. Nonetheless, there are good points in it.

Thanks,

Ben

|||

Well, I think each option has its place, and that's why there are options: because one size doesn't fit all.

So let me ask another question: Let's say "money is no object" then which method is most suitable in what kind of scenario? For example, if we have three types of databases:

a. OLTP databases with heavy online transactions all day long

b. Data warehouses with bulk inserts / DTS / SSIS loads overnight

c. Middle tier databases, with complex business rules and interrelationships, but infrequent updates

What high-availability protection method is most suitable for each of the above 3 categories?

Ben

HIding databases that a user does not have permission to

I remember reading a blog post which showed you how to hide databases other than the ones the current user has permission to see. My ISP is getting a lot more SQL 2005 databases on the server and I would like to encourage them to only show me mine in Management Studio rather than the huge list I have to scroll down. Is this possible?

(the blog post *may* have been for SQL 2000 but I'm looking for a SQL 2005 solution)

Yes, there have been some recent discussions about this. You need to revoke the permission 'VIEW ANY DATABASE' from the role PUBLIC (and then grant it to any server principal who should have it but don't get it by default). Credit to Kalen Delaney and Dan Guzman who pointed this out... wrote in message news:b45aadf8-59d9-4531-9ba7-dac473bbcade@.discussions.microsoft.com... >I remember reading a blog post which showed you how to hide databases
> other than the ones the current user has permission to see. My ISP is
> getting a lot more SQL 2005 databases on the server and I would like to
> encourage them to only show me mine in Management Studio rather than the
> huge list I have to scroll down. Is this possible? >
> (the blog post *may* have been for SQL 2000 but I'm looking for a SQL
> 2005 solution) > > >
>