Info!

Hi All, 2 doubts
1. How can I retrieve the exact command (T-SQL) being executed by an SPID on
the query Analyzer. You can get the same info using Ent. Mgr and right click
on SPID.
2. What's the best technic (light weight method) to log errors from a Stored
Proc? I don't wanna use xp_cmdshell...
TIA1) DBCC INPUTBUFFER (SPID) capped at 255 I believe, or use
fn_get_sql() SP3 + on SQl server 2000 take a look here
(http://vyaskn.tripod.com/fn_get_sql.htm)
2) log the errors in a ErrorLog table with procname, date, any other
info (parameters maybe) etc
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||DBCC INPUTBUFFER (SPID)
note - it will truncate the statement after a particular length.
"Vai2000" <nospam@.microsoft.com> wrote in message
news:e9STBJddGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Hi All, 2 doubts
> 1. How can I retrieve the exact command (T-SQL) being executed by an SPID
> on
> the query Analyzer. You can get the same info using Ent. Mgr and right
> click
> on SPID.
> 2. What's the best technic (light weight method) to log errors from a
> Stored
> Proc? I don't wanna use xp_cmdshell...
> TIA
>|||> 2. What's the best technic (light weight method) to log errors from a Storedd">
> Proc? I don't wanna use xp_cmdshell...
Where do you want these errors to be logged? RAISERROR and xp_logevent can b
e options, but hard to
tell without more info.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vai2000" <nospam@.microsoft.com> wrote in message news:e9STBJddGHA.1204@.TK2MSFTNGP02.phx.gb
l...
> Hi All, 2 doubts
> 1. How can I retrieve the exact command (T-SQL) being executed by an SPID
on
> the query Analyzer. You can get the same info using Ent. Mgr and right cli
ck
> on SPID.
> 2. What's the best technic (light weight method) to log errors from a Stor
ed
> Proc? I don't wanna use xp_cmdshell...
> TIA
>sql

Indexing job interferes with log shipping

The log shipping job fails right after indexing runs and the log file itself
turns up corrupt. Is it possible to run a command that temporarily disables
the log shipping while indexing runs and then upon completion of the indexing
(fail or not) re-enable the log-shipping job?
Regards,
Jamie
No, but it is recommended that you switch to bulk logged recovery model
before starting indexing as the log size is smaller.
Return to full after you have completed the indexing operation.
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
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:84589C1C-6598-4985-81F8-61A68813EA7D@.microsoft.com...
> The log shipping job fails right after indexing runs and the log file
> itself
> turns up corrupt. Is it possible to run a command that temporarily
> disables
> the log shipping while indexing runs and then upon completion of the
> indexing
> (fail or not) re-enable the log-shipping job?
> --
> Regards,
> Jamie
|||Paul,
I think I should have mentioned that this is partially a Red Gate error:
Data decompression error: Access violation at address 0050BA10 in
module'SQBCoreService.exe'. Read of address 7FF80000 Process terminated
unexpectedly. Error code: -2139684860
AND ON THE Application Level...
The description for Event ID '-1073724769' in Source 'MSSQLSERVER' cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display the message, or you may not have permission to
access them. The following information is part of the event:'18210',
'BackupVirtualDeviceSet::Initialize: Open failure on backup device
'SQLBACKUP_C2F85077-1436-4FC9-BBD0-7B4ECC822724'. Operating system error
0x80070002(The system cannot find the file specified.).
Red Gate suspects that this happens when the decompression routine is
running its copy operation. I didn't know this when I asked the question.
I thought it might be something to do with a locking issue but Hilary gave me
enough information for me to figure it out by myself.
Instead of trying to alter the logging job, I alter the indexing job to
switch to BULK_LOGGING mode and then return to FULL when the indexing is
done. Will let you know next week if it works or not.
Jamie
Regards,
Jamie
"Paul Ibison" wrote:

> Jamie - how does the log file 'turn up corrupt' - can you post any error
> message you're seeing.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>