applying transaction logs from a crashed server

I would like to know the process to rebuilt a new server if the current
server crashes. I can install the server with the same service pack level,
then restore the system databases and then the User databases from the
backups on the new server.
How do we bring in and apply the Transaction logs from the crashed server to
the new server?You mean "the last" log backup? I.e., the log records produced since you produced your most recent
log backup?
It depends on how the server crashed. If the SQL Server is still available, then you just do
BACKUP LOG crashedDb TO .. WITH NO_TRUNCATE
If that SQL Server isn't accessible, then you do the following:
On a working server, create a new database.
Stop that SQL Server.
Delete the database files.
Copy the ldf file from the crashed server in place of the ldf file which you deleted in above step.
Start this SQL Server
BACKUP LOG dbname to ... WITH NO_TRUNCATE
You now have a chain of log backups up until the crash which you can use for your restore. Of
course, all this assumes that you do log backups in the first place and that you can access the ldf
file for your crashed database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F4187089-40A8-420B-B912-77F1B5A79834@.microsoft.com...
>I would like to know the process to rebuilt a new server if the current
> server crashes. I can install the server with the same service pack level,
> then restore the system databases and then the User databases from the
> backups on the new server.
> How do we bring in and apply the Transaction logs from the crashed server to
> the new server?

Application-controlled transactions, isolation level and commit/rollbacks

If application code controls all transaction processing to SQL Server, so it starts a transaction, does any commit or rollback on teh application side, how does that actually work ON SQL Server 2005... Meaning, If the app passes in a isolation level of Repeatable Read, and the database default is different, how can I see what is being used, as a DBA? Can I see any of that via Profiler? can I see when those commits/rollbacks are issued from teh application. They are not sending in "SQL" commit/rollback transaction commands. It's built-in to their architecture to control all that... How can I see what's happening on the database if these are not SQL commands for transaction handling? and how does that work, to start a transaction on the app side, and hold locks etc, on SQL Server if normal SQL Server commands are not being sent? If anyone can point me at decent references to read on that, thanks! Bruce

Are you talking about application controlled transactions as in the application only works through stored procedures? Or do you mean it use DTC Transactions? Either way, you should be able to catch these with SQL Profiler.

|||Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce|||I was also hoping to see what Isolation Level my stored procedures are being executed at via the profiler. I just don't see how I can do that, or anything like it.

I would be grateful of any further information or help.

Regards
Darren
|||

Bruce dBA wrote:

Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce

In .NET 2.0 and above there are two other kinds of transactions that uses isolation levels and rollbacks one is atomic like the SQL Server transactions while the other is not but SQL Server 2005 is equiped to handle the none atomic transaction while 2000 is not so DTC is needed to run the none atomic in SQL Server 2000. The transactions from ADO.NET System.Data is atomic while the transaction from System.Transaction which uses TransactionScope it not. Try the links below for details.

http://msdn2.microsoft.com/en-us/library/system.transactions.aspx

http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx

Application-controlled transactions, isolation level and commit/rollbacks

If application code controls all transaction processing to SQL Server, so it starts a transaction, does any commit or rollback on teh application side, how does that actually work ON SQL Server 2005... Meaning, If the app passes in a isolation level of Repeatable Read, and the database default is different, how can I see what is being used, as a DBA? Can I see any of that via Profiler? can I see when those commits/rollbacks are issued from teh application. They are not sending in "SQL" commit/rollback transaction commands. It's built-in to their architecture to control all that... How can I see what's happening on the database if these are not SQL commands for transaction handling? and how does that work, to start a transaction on the app side, and hold locks etc, on SQL Server if normal SQL Server commands are not being sent? If anyone can point me at decent references to read on that, thanks! Bruce

Are you talking about application controlled transactions as in the application only works through stored procedures? Or do you mean it use DTC Transactions? Either way, you should be able to catch these with SQL Profiler.

|||Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce|||I was also hoping to see what Isolation Level my stored procedures are being executed at via the profiler. I just don't see how I can do that, or anything like it.

I would be grateful of any further information or help.

Regards
Darren
|||

Bruce dBA wrote:

Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce

In .NET 2.0 and above there are two other kinds of transactions that uses isolation levels and rollbacks one is atomic like the SQL Server transactions while the other is not but SQL Server 2005 is equiped to handle the none atomic transaction while 2000 is not so DTC is needed to run the none atomic in SQL Server 2000. The transactions from ADO.NET System.Data is atomic while the transaction from System.Transaction which uses TransactionScope it not. Try the links below for details.

http://msdn2.microsoft.com/en-us/library/system.transactions.aspx

http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx

Application Upgrade Issue

Hi,
We have recently upgraded one of our application after which we have been
facing lot of blocking issues at DB level.Our application vendor claims that
nothing much has been changed at DB layer level and he indicates something i
s
wrong with our DB enviornment.Can somebody help me in allevaiteing these DB
blocks?
RectThat's a silly response from the vendor - so they changed
the application and don't think that could have anything to
do with the sudden issues with blocking? It could very much
play a part. The indexing needs often change with a new app
as well.
The following links should help you out with setting up
monitoring for blocking:
INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Problems
http://support.microsoft.com/?id=224453
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
-Sue
On Tue, 25 Apr 2006 16:52:01 -0700, Rect
<Rect@.discussions.microsoft.com> wrote:

>Hi,
>We have recently upgraded one of our application after which we have been
>facing lot of blocking issues at DB level.Our application vendor claims tha
t
>nothing much has been changed at DB layer level and he indicates something
is
>wrong with our DB enviornment.Can somebody help me in allevaiteing these DB
>blocks?
>Rect

Application Upgrade Issue

Hi,
We have recently upgraded one of our application after which we have been
facing lot of blocking issues at DB level.Our application vendor claims that
nothing much has been changed at DB layer level and he indicates something is
wrong with our DB enviornment.Can somebody help me in allevaiteing these DB
blocks?
RectThat's a silly response from the vendor - so they changed
the application and don't think that could have anything to
do with the sudden issues with blocking? It could very much
play a part. The indexing needs often change with a new app
as well.
The following links should help you out with setting up
monitoring for blocking:
INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Problems
http://support.microsoft.com/?id=224453
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
-Sue
On Tue, 25 Apr 2006 16:52:01 -0700, Rect
<Rect@.discussions.microsoft.com> wrote:
>Hi,
>We have recently upgraded one of our application after which we have been
>facing lot of blocking issues at DB level.Our application vendor claims that
>nothing much has been changed at DB layer level and he indicates something is
>wrong with our DB enviornment.Can somebody help me in allevaiteing these DB
>blocks?
>Rect

Append Query Problems

Hi

I am doing an A level database project. I need to build a system to deal with ordering and selling products.

I am trying to make an append query that will append all the products bought in my "invoices" table to my "archive" table. I want invoices that are three months old to be transferred the the "archive" table. I have been trying to put a criteria in the query for the Date field but I haven't managed to find an expression that works.

I'm sorry if this is too simple and you guys think im just being stupid.
I figured it was worth asking on here!! It's really bugging me.Show us what you got so far. Also ... look up dateadd and datediff in BOL.|||Sorry, what is BOL?
"Date"<=#01/01/2006#

Is that what you mean by show? I don't know how to show the whole query :-s|||Can you write a SELECT statement to retrieve the rows that you want to archive?

-PatP|||Thanks but I don't know what a select statement is! Is it the same as a select query? there's something about that in one of my books.|||Is this project in Access? You may need to ask over in the Access forum...|||Yes it is. I realised after I posted here that there is an access forum too. I have posted in there now thanks!

Anyone using Excel 2007 as client ?

Overall pretty cool, but apparently you can't select a single level of a dimension in the pivot table.

For instance, I have level Year, Quarter, Month, Week, Day.

Having to look at last week's sales per day, I'd be selecting a specific week and I'd have the Day level only as row labels.

This worked in Excel 2003, but I can't figure out how to leave out the Year, Q, M and Week levels.

In the PivotTable Field List, you have a check box next to the Time dimension, but nothing next to each level in the dimension. So you have to have all levels of the dimension in the rows of the pivot.

This means a lot of useless drillthrough for the users as they go down from Year to Quarter to Month to Week to Days.

Am I missing something ?

In Excel 2007 you can display the Day level only by 'hiding' the other levels. If you drill down to the lowest level day, right click on any item | Show/Hide Fields | Uncheck the Year level - the year level is hidden. Repeat for the other levels you want to hide.

Hope this helps!

|||

Thanks Zaheera it does help, that's what I needed.

I think this is a feature that needs improvement, since I need 17 clicks before I have drilled down to Days, and then removed the levels one by one.

|||

To accomplish this in fewer clicks - you can right click on an item in the highest level | Expand/Collapse | Expand to <lowest level>. In one-click you've drilled down to the lowest level. Now you'll need to hide each level one by one as described above (which can be a pain - I agree). Or you can do this in one-step in VBA (Alt + F11 to open the editor, Cntrl + G to go to the immediate window): Activecell.PivotField.CubeField.HiddenLevels = 3 and the top 3 levels will be hidden at once.