query database table: dbcc

Showing posts with label dbcc. Show all posts

Monday, March 26, 2012

How to remove a file [dbcc shrinkfile(filename,empty) is not working]

I tried to remove a file from a filegroup by using the following two comamnd
1) dbcc SHRINKFILE('FileName1', EMPTYFILE)
2) ALTER DATABASE PED_PROD REMOVE FILE FileName1

but I am getting the following error message

Server: Msg 5042, Level 16, State 1, Line 1
The file ''FileName1'' cannot be removed because it is not empty.

Please help in this

Thanks in Advance,
SateeshHowdy

What version of SQL are you using?

Cheers

SG

Wednesday, March 21, 2012

How to reduce the unallocated space of a database?

I tried DBCC SHRINKDATABASE, however, can not reduce the unallocated space. Please help!Press rite click on a database u want n then goto all tasks>shrink database

press files button
select database file u want to shrink
then select shrink file to (type used space)
press ok button|||You can also set the database to auto_shrink. Enterprise manager: right-click on your database, choose properties and then the options tabl. You'll see just below the middle on the right the option auto_shrink.

how to reduce the size of transaction file

i've run "dbcc shrinkfile ('employ')"
The size of the file does not reduced.Win
It could be because the LOG file has an actve portions of transaction. Did
you run BACKUP LOG prior a SHRINKING?
select DB_ID('dbname')
Run DBCC LOGINFO(7)
If you see at the bottom transactions with status=2 that means SQL Server
'needs' them and you could not shrink.
However you can run dump inserting to moce those transaction at the
beginning of the LOG
"Win" <aaa@.aaa.com> wrote in message
news:%23Pgz2NkUGHA.328@.TK2MSFTNGP11.phx.gbl...
> i've run "dbcc shrinkfile ('employ')"
> The size of the file does not reduced.
>|||You'll find some information about why it might not shrink in
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Win" <aaa@.aaa.com> wrote in message news:%23Pgz2NkUGHA.328@.TK2MSFTNGP11.phx.gbl...arkred">
> i've run "dbcc shrinkfile ('employ')"
> The size of the file does not reduced.
>

Monday, March 12, 2012

how to recompile / refresh UDFs ?

Hi!
I need to refresh an entire database.

I can recompile SPs with sp_recompile (or DBCC FLUSHPROCINDB), and
refresh views with sp_refreshView, but I cannot find any way to
refresh my user-defined functions (some of them are like views, with
parameters).

Any help appreciated :) !

BenHi Ben,

Quote:

Originally Posted by

Hi!
I need to refresh an entire database.
>
I can recompile SPs with sp_recompile (or DBCC FLUSHPROCINDB), and
refresh views with sp_refreshView, but I cannot find any way to
refresh my user-defined functions (some of them are like views, with
parameters).

I'm afraid that no such procedure/DBCC command exists to recompile a
function. IMHO the best way to refresh function meta-data is to ALTER
it. That's better solution than dropping and creating (recreating) a
function, because when using ALTER FUNCTION permissions are retained.

--
Best regards,
Marcin Guzowski
http://guzowski.info|||Ben (benblo@.gmail.com) writes:

Quote:

Originally Posted by

What do you really want to achieve? sp_recompile and FLUSHPROCINDB just
removes plans out the query cache. sp_refreshview on the other hand
reinterprets the definition of the view, and this is necessary if
the view definition has an * in the select list. Thus the two serve
completely different purposes.

IF the problem is that you cannot refresh your inline table functions,
the simple solution is not to use SELECT *, which is generally considered
bad practice.

--
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|||What I'm trying to achieve, as I said:

Quote:

Originally Posted by

I need to refresh an entire database.

So, all SPs, functions, and views.

The refresh problems arose when I changed a few columns' order : I
still receive all the data from the functions but they're incorrectly
ordered and labelled.
It gets even worse as some functions are nested (say, I select all
valid clients according to dates criteria, then all valid orders from
those clients, etc), or other functions are used in CHECK constraints
and so I SQL Server refuses to alter them, so I have to kil the
constraint, alter the function, and re-create the constraint... nice.

I heard about the "select * is bad practice", but I'm dealing with a
constantly evolving database (not yet in production), so I use a lot
of it to just pump everything and send it back to webpages. And even
if I didn't all that would mean is I'd have to manually go into every
function and update them, which is exactly what I've been doing so far
(open, backspace to alter, save --seems to be the only way to
refresh).

None of this is unsolvable, it just takes unnecessary time (one change
can mean 20 functions to track), and I can't find a way to do it
automatically.
Plus I find it really frustrating to be faced with compile problems
using a language that is supposed to be interpreted!
I get enough trouble updating DLLs, plus at least VS provides the
"recompile all" function...

Maybe if I do enough nagging my boss'll get me a SQL Server 2005.
Would that solve at least some of this?

Cheers, Ben.

On Mar 6, 11:50 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Ben (ben...@.gmail.com) writes:

Quote:

Originally Posted by

>
What do you really want to achieve? sp_recompile and FLUSHPROCINDB just
removes plans out the query cache. sp_refreshview on the other hand
reinterprets the definition of the view, and this is necessary if
the view definition has an * in the select list. Thus the two serve
completely different purposes.
>
IF the problem is that you cannot refresh your inline table functions,
the simple solution is not to use SELECT *, which is generally considered
bad practice.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||Thanks, I knew about the ALTER solution, the only problem is I can
only do it manually (in the manager, open each function, backspace to
alter and enable the save button, and save).
Do you know any way to do the same automatically? (refresh ALL
functions, or the ENTIRE database) (PS: I can't rebuild it, have to
keep the data)

Cheers, Ben

On Mar 6, 6:17 pm, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.infowrote:

Quote:

Originally Posted by

Hi Ben,
>

Quote:

Originally Posted by

Hi!
I need to refresh an entire database.

Quote:

Originally Posted by

>
I'm afraid that no such procedure/DBCC command exists to recompile a
function. IMHO the best way to refresh function meta-data is to ALTER
it. That's better solution than dropping and creating (recreating) a
function, because when using ALTER FUNCTION permissions are retained.
>
--
Best regards,
Marcin Guzowskihttp://guzowski.info

|||Ben (benblo@.gmail.com) writes:

Quote:

Originally Posted by

Not really. If you have everything under version control, or at least
on disk, you can easily run a BAT file that loads all functions it can
find. The database is no place for source code; in my opinion that is
only a container for binaries.

And while it may seem easy to have SELECT *, it does come back and bite
you. As I understood, you got this problem because you changed the column
order. If you had used explicit column lists, you could just have
changed the column lists, and you would have to change the underlying
tables.

I work with a constantly evolving database, for over ten years now. One
thing I hate is to find a stored procedure to return about every column
in a table. Then I have to dig further into the client code so see if
the column I want to drop or redefine is actually use somewhere. So there
are very good reasons to only return the columns that actually are
in use. This makes it much easier to track down where things are used.

How to rebuild index ?

Hi,
We are using SQL Server 2000.
A developer asks us to rebuild the index of the database. However, from the
BOL, I find that the command DBCC DBREINDEX can be used to rebuild index for
a particular table.
Is there any command to do so ? So far as I know, rebuild index of the
database is part of the Database Maintenance Plan.
Thanks
DanielOne method is to generate and execute DBCC DBREINDEX commands using a script
like the example below.
DECLARE @.DBCC_Command nvarchar(4000)
DECLARE DBCC_Commands
CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'DBCC DBREINDEX(''' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
''')'
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
OPEN DBCC_Commands
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DBCC_Commands INTO @.DBCC_Command
IF @.@.FETCH_STATUS = -1 BREAK
RAISERROR(@.DBCC_Command, 0, 1) WITH NOWAIT
EXEC(@.DBCC_Command)
END
CLOSE DBCC_Commands
DEALLOCATE DBCC_Commands
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%23OsDDCj3GHA.1608@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We are using SQL Server 2000.
> A developer asks us to rebuild the index of the database. However, from
> the BOL, I find that the command DBCC DBREINDEX can be used to rebuild
> index for a particular table.
> Is there any command to do so ? So far as I know, rebuild index of the
> database is part of the Database Maintenance Plan.
> Thanks
> Daniel
>|||Peter wrote:
> Hi,
> We are using SQL Server 2000.
> A developer asks us to rebuild the index of the database. However, from t
he
> BOL, I find that the command DBCC DBREINDEX can be used to rebuild index f
or
> a particular table.
> Is there any command to do so ? So far as I know, rebuild index of the
> database is part of the Database Maintenance Plan.
> Thanks
> Daniel
>
There is no index "of the database", indexes are on tables. I suspect
you were asked to rebuild all of the indexes IN the database, possibly
to remove fragmentation to address a performance problem.
Have a look here:
http://realsqlguy.com/serendipity/a...realsqlguy.com|||Peter,
to reindex all tables in a database you could use:
EXEC sp_msForEachTable 'DBCC DBREINDEX ("?")'
The maintenance plan will also give this functionality. I'd recommend
reading up on the difference between DBCC DBREINDEX and DBCC INDEXDEFRAG as
the former command is essentially an offline operation and if you have a
24/7 operation you'll need to consider the latter.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

How to rebuild index ?

Hi,
We are using SQL Server 2000.
A developer asks us to rebuild the index of the database. However, from the
BOL, I find that the command DBCC DBREINDEX can be used to rebuild index for
a particular table.
Is there any command to do so ? So far as I know, rebuild index of the
database is part of the Database Maintenance Plan.
Thanks
Daniel
One method is to generate and execute DBCC DBREINDEX commands using a script
like the example below.
DECLARE @.DBCC_Command nvarchar(4000)
DECLARE DBCC_Commands
CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'DBCC DBREINDEX(''' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
''')'
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
OPEN DBCC_Commands
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DBCC_Commands INTO @.DBCC_Command
IF @.@.FETCH_STATUS = -1 BREAK
RAISERROR(@.DBCC_Command, 0, 1) WITH NOWAIT
EXEC(@.DBCC_Command)
END
CLOSE DBCC_Commands
DEALLOCATE DBCC_Commands
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%23OsDDCj3GHA.1608@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We are using SQL Server 2000.
> A developer asks us to rebuild the index of the database. However, from
> the BOL, I find that the command DBCC DBREINDEX can be used to rebuild
> index for a particular table.
> Is there any command to do so ? So far as I know, rebuild index of the
> database is part of the Database Maintenance Plan.
> Thanks
> Daniel
>
|||Peter wrote:
> Hi,
> We are using SQL Server 2000.
> A developer asks us to rebuild the index of the database. However, from the
> BOL, I find that the command DBCC DBREINDEX can be used to rebuild index for
> a particular table.
> Is there any command to do so ? So far as I know, rebuild index of the
> database is part of the Database Maintenance Plan.
> Thanks
> Daniel
>
There is no index "of the database", indexes are on tables. I suspect
you were asked to rebuild all of the indexes IN the database, possibly
to remove fragmentation to address a performance problem.
Have a look here:
http://realsqlguy.com/serendipity/ar...A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Peter,
to reindex all tables in a database you could use:
EXEC sp_msForEachTable 'DBCC DBREINDEX ("?")'
The maintenance plan will also give this functionality. I'd recommend
reading up on the difference between DBCC DBREINDEX and DBCC INDEXDEFRAG as
the former command is essentially an offline operation and if you have a
24/7 operation you'll need to consider the latter.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

How to rebuild index ?

Hi,
We are using SQL Server 2000.
A developer asks us to rebuild the index of the database. However, from the
BOL, I find that the command DBCC DBREINDEX can be used to rebuild index for
a particular table.
Is there any command to do so ? So far as I know, rebuild index of the
database is part of the Database Maintenance Plan.
Thanks
DanielOne method is to generate and execute DBCC DBREINDEX commands using a script
like the example below.
DECLARE @.DBCC_Command nvarchar(4000)
DECLARE DBCC_Commands
CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'DBCC DBREINDEX(''' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
''')'
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
OPEN DBCC_Commands
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DBCC_Commands INTO @.DBCC_Command
IF @.@.FETCH_STATUS = -1 BREAK
RAISERROR(@.DBCC_Command, 0, 1) WITH NOWAIT
EXEC(@.DBCC_Command)
END
CLOSE DBCC_Commands
DEALLOCATE DBCC_Commands
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%23OsDDCj3GHA.1608@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We are using SQL Server 2000.
> A developer asks us to rebuild the index of the database. However, from
> the BOL, I find that the command DBCC DBREINDEX can be used to rebuild
> index for a particular table.
> Is there any command to do so ? So far as I know, rebuild index of the
> database is part of the Database Maintenance Plan.
> Thanks
> Daniel
>|||Peter wrote:
> Hi,
> We are using SQL Server 2000.
> A developer asks us to rebuild the index of the database. However, from the
> BOL, I find that the command DBCC DBREINDEX can be used to rebuild index for
> a particular table.
> Is there any command to do so ? So far as I know, rebuild index of the
> database is part of the Database Maintenance Plan.
> Thanks
> Daniel
>
There is no index "of the database", indexes are on tables. I suspect
you were asked to rebuild all of the indexes IN the database, possibly
to remove fragmentation to address a performance problem.
Have a look here:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Peter,
to reindex all tables in a database you could use:
EXEC sp_msForEachTable 'DBCC DBREINDEX ("?")'
The maintenance plan will also give this functionality. I'd recommend
reading up on the difference between DBCC DBREINDEX and DBCC INDEXDEFRAG as
the former command is essentially an offline operation and if you have a
24/7 operation you'll need to consider the latter.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Friday, March 9, 2012

How to read the DBCC page through cleint application

Hi,
I am writing a application that require to read some DBCC Pages.
But when I execute dbcc page command through ADO, I get a page in terms of e
rror collection.
In that error collection each row of output is just a string.
I need to interpret that string value.
Is there some other way through which I can read DBCC Page.
I am interseted only in the memory dump portion of the page.
Is there some better way to read the data base page or I need to use this er
ror collection method only.
Any help is appreciated.
Thanks
PushkarHi
You may want to use DBCC BYTES but you will have to calculate the starting
address. Ken Henderson's "The Guru's Guide to Transact-SQL" ISBN
0-201-61576-2 gives some information on all these undocumented calls and
using them in production code carries the usual health warnings.
John
"Pushkar" wrote:

> Hi,
> I am writing a application that require to read some DBCC Pages.
> But when I execute dbcc page command through ADO, I get a page in terms of
error collection.
> In that error collection each row of output is just a string.
> I need to interpret that string value.
> Is there some other way through which I can read DBCC Page.
> I am interseted only in the memory dump portion of the page.
> Is there some better way to read the data base page or I need to use this
error collection method only.
> Any help is appreciated.
> Thanks
> Pushkar|||I believe that you can add WITH TABLERESULTS to get back a resultset instead
a string (messages).
Note: It is not supported or documented (but nor is DBCC PAGE, so you are on
your own anyhow :-) ).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:%23x6XXvsvFHA.1256
@.TK2MSFTNGP09.phx.gbl...
Hi,
I am writing a application that require to read some DBCC Pages.
But when I execute dbcc page command through ADO, I get a page in terms of e
rror collection.
In that error collection each row of output is just a string.
I need to interpret that string value.
Is there some other way through which I can read DBCC Page.
I am interseted only in the memory dump portion of the page.
Is there some better way to read the data base page or I need to use this er
ror collection method
only.
Any help is appreciated.
Thanks
Pushkar|||Thanks, I think this will solve my problem.
Pushkar
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OG8y%23h4vFHA.464@.TK2MSFTNGP15.phx.gbl...
>I believe that you can add WITH TABLERESULTS to get back a resultset
>instead a string (messages). Note: It is not supported or documented (but
>nor is DBCC PAGE, so you are on your own anyhow :-) ).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:%23x6XXvsvFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am writing a application that require to read some DBCC Pages.
> But when I execute dbcc page command through ADO, I get a page in terms of
> error collection.
> In that error collection each row of output is just a string.
> I need to interpret that string value.
> Is there some other way through which I can read DBCC Page.
> I am interseted only in the memory dump portion of the page.
> Is there some better way to read the data base page or I need to use this
> error collection method only.
> Any help is appreciated.
> Thanks
> Pushkar
>

How to Read SQL Transaction Log ...

Good day All
I am trying to read & Understand SQL Transaction Log.
For Online Log, I use DBCC LOG...but how to understand what Row Data means.
Also How to read BackedUp (Offline) Log File?
Any help would be much appreciated.
--
Ramanuj Brahmachary
Database Administrator
[ MCDBA, MCSE, MCSD ]Hi,
Try using the 3rd party tool Logexplorer from Lumigent
www.lumigent.com
Thanks
Hari
SQL Server MVP
"Ramanuj" <Ramanuj@.discussions.microsoft.com> wrote in message
news:68B9091E-649C-45C9-8B5B-4F9606FCC876@.microsoft.com...
> Good day All
> I am trying to read & Understand SQL Transaction Log.
> For Online Log, I use DBCC LOG...but how to understand what Row Data
> means.
> Also How to read BackedUp (Offline) Log File?
> Any help would be much appreciated.
> --
> Ramanuj Brahmachary
> Database Administrator
> [ MCDBA, MCSE, MCSD ]|||This is undocumented. There are third party tools that help you interpret
the log, such as Lumigent.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ramanuj" <Ramanuj@.discussions.microsoft.com> wrote in message
news:68B9091E-649C-45C9-8B5B-4F9606FCC876@.microsoft.com...
> Good day All
> I am trying to read & Understand SQL Transaction Log.
> For Online Log, I use DBCC LOG...but how to understand what Row Data
> means.
> Also How to read BackedUp (Offline) Log File?
> Any help would be much appreciated.
> --
> Ramanuj Brahmachary
> Database Administrator
> [ MCDBA, MCSE, MCSD ]|||I have some 3:rd party tools listed on my links page:
http://www.karaszi.com/SQLServer/links.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ramanuj" <Ramanuj@.discussions.microsoft.com> wrote in message
news:68B9091E-649C-45C9-8B5B-4F9606FCC876@.microsoft.com...
> Good day All
> I am trying to read & Understand SQL Transaction Log.
> For Online Log, I use DBCC LOG...but how to understand what Row Data means.
> Also How to read BackedUp (Offline) Log File?
> Any help would be much appreciated.
> --
> Ramanuj Brahmachary
> Database Administrator
> [ MCDBA, MCSE, MCSD ]|||There are a number of tools available
http://aspfaq.com/show.asp?id=2449
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ramanuj" <Ramanuj@.discussions.microsoft.com> wrote in message
news:68B9091E-649C-45C9-8B5B-4F9606FCC876@.microsoft.com...
> Good day All
> I am trying to read & Understand SQL Transaction Log.
> For Online Log, I use DBCC LOG...but how to understand what Row Data
> means.
> Also How to read BackedUp (Offline) Log File?
> Any help would be much appreciated.
> --
> Ramanuj Brahmachary
> Database Administrator
> [ MCDBA, MCSE, MCSD ]|||Thanks very much for the overwhelming response.
My apologies, I havent mentioned that I tried some 3rd party Log Readers &
what I understand is they do very similar but are able to interprete the Row
Data field output of DBCC LOG. I was trying to find out if I can also
interprete the value in Row Data.
Any help in interpreting the field [Row Data] of DBCC LOG is much
appreciated.
--
Ramanuj Brahmachary
Database Administrator, Bangalore
[ MCDBA, MCSE, MCSD ]
"Jasper Smith" wrote:
> There are a number of tools available
> http://aspfaq.com/show.asp?id=2449
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Ramanuj" <Ramanuj@.discussions.microsoft.com> wrote in message
> news:68B9091E-649C-45C9-8B5B-4F9606FCC876@.microsoft.com...
> > Good day All
> > I am trying to read & Understand SQL Transaction Log.
> > For Online Log, I use DBCC LOG...but how to understand what Row Data
> > means.
> > Also How to read BackedUp (Offline) Log File?
> >
> > Any help would be much appreciated.
> > --
> > Ramanuj Brahmachary
> > Database Administrator
> > [ MCDBA, MCSE, MCSD ]
>
>|||Also how to read BackedUp transaction LOG from file on disk.
--
Ramanuj Brahmachary
Database Administrator
[ MCDBA, MCSE, MCSD ]
"Ramanuj" wrote:
> Thanks very much for the overwhelming response.
> My apologies, I havent mentioned that I tried some 3rd party Log Readers &
> what I understand is they do very similar but are able to interprete the Row
> Data field output of DBCC LOG. I was trying to find out if I can also
> interprete the value in Row Data.
> Any help in interpreting the field [Row Data] of DBCC LOG is much
> appreciated.
> --
> Ramanuj Brahmachary
> Database Administrator, Bangalore
> [ MCDBA, MCSE, MCSD ]
>
> "Jasper Smith" wrote:
> > There are a number of tools available
> > http://aspfaq.com/show.asp?id=2449
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> > http://www.sqldbatips.com
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> > "Ramanuj" <Ramanuj@.discussions.microsoft.com> wrote in message
> > news:68B9091E-649C-45C9-8B5B-4F9606FCC876@.microsoft.com...
> > > Good day All
> > > I am trying to read & Understand SQL Transaction Log.
> > > For Online Log, I use DBCC LOG...but how to understand what Row Data
> > > means.
> > > Also How to read BackedUp (Offline) Log File?
> > >
> > > Any help would be much appreciated.
> > > --
> > > Ramanuj Brahmachary
> > > Database Administrator
> > > [ MCDBA, MCSE, MCSD ]
> >
> >
> >|||There is no documentation for interpreting the active log or a log backup. I have not seen any
scripts or anything to help with this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ramanuj" <Ramanuj@.discussions.microsoft.com> wrote in message
news:162E993E-BBA0-4AD0-858B-672E8D6C0198@.microsoft.com...
> Also how to read BackedUp transaction LOG from file on disk.
> --
> Ramanuj Brahmachary
> Database Administrator
> [ MCDBA, MCSE, MCSD ]
>
> "Ramanuj" wrote:
>> Thanks very much for the overwhelming response.
>> My apologies, I havent mentioned that I tried some 3rd party Log Readers &
>> what I understand is they do very similar but are able to interprete the Row
>> Data field output of DBCC LOG. I was trying to find out if I can also
>> interprete the value in Row Data.
>> Any help in interpreting the field [Row Data] of DBCC LOG is much
>> appreciated.
>> --
>> Ramanuj Brahmachary
>> Database Administrator, Bangalore
>> [ MCDBA, MCSE, MCSD ]
>>
>> "Jasper Smith" wrote:
>> > There are a number of tools available
>> > http://aspfaq.com/show.asp?id=2449
>> >
>> > --
>> > HTH
>> >
>> > Jasper Smith (SQL Server MVP)
>> > http://www.sqldbatips.com
>> > I support PASS - the definitive, global
>> > community for SQL Server professionals -
>> > http://www.sqlpass.org
>> >
>> > "Ramanuj" <Ramanuj@.discussions.microsoft.com> wrote in message
>> > news:68B9091E-649C-45C9-8B5B-4F9606FCC876@.microsoft.com...
>> > > Good day All
>> > > I am trying to read & Understand SQL Transaction Log.
>> > > For Online Log, I use DBCC LOG...but how to understand what Row Data
>> > > means.
>> > > Also How to read BackedUp (Offline) Log File?
>> > >
>> > > Any help would be much appreciated.
>> > > --
>> > > Ramanuj Brahmachary
>> > > Database Administrator
>> > > [ MCDBA, MCSE, MCSD ]
>> >
>> >
>> >|||wandering how 3rd party vendors interpreting it....
--
Ramanuj Brahmachary
Database Administrator
[ MCDBA, MCSE, MCSD ]
"Tibor Karaszi" wrote:
> There is no documentation for interpreting the active log or a log backup. I have not seen any
> scripts or anything to help with this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ramanuj" <Ramanuj@.discussions.microsoft.com> wrote in message
> news:162E993E-BBA0-4AD0-858B-672E8D6C0198@.microsoft.com...
> > Also how to read BackedUp transaction LOG from file on disk.
> > --
> > Ramanuj Brahmachary
> > Database Administrator
> > [ MCDBA, MCSE, MCSD ]
> >
> >
> > "Ramanuj" wrote:
> >
> >> Thanks very much for the overwhelming response.
> >>
> >> My apologies, I havent mentioned that I tried some 3rd party Log Readers &
> >> what I understand is they do very similar but are able to interprete the Row
> >> Data field output of DBCC LOG. I was trying to find out if I can also
> >> interprete the value in Row Data.
> >> Any help in interpreting the field [Row Data] of DBCC LOG is much
> >> appreciated.
> >> --
> >> Ramanuj Brahmachary
> >> Database Administrator, Bangalore
> >> [ MCDBA, MCSE, MCSD ]
> >>
> >>
> >> "Jasper Smith" wrote:
> >>
> >> > There are a number of tools available
> >> > http://aspfaq.com/show.asp?id=2449
> >> >
> >> > --
> >> > HTH
> >> >
> >> > Jasper Smith (SQL Server MVP)
> >> > http://www.sqldbatips.com
> >> > I support PASS - the definitive, global
> >> > community for SQL Server professionals -
> >> > http://www.sqlpass.org
> >> >
> >> > "Ramanuj" <Ramanuj@.discussions.microsoft.com> wrote in message
> >> > news:68B9091E-649C-45C9-8B5B-4F9606FCC876@.microsoft.com...
> >> > > Good day All
> >> > > I am trying to read & Understand SQL Transaction Log.
> >> > > For Online Log, I use DBCC LOG...but how to understand what Row Data
> >> > > means.
> >> > > Also How to read BackedUp (Offline) Log File?
> >> > >
> >> > > Any help would be much appreciated.
> >> > > --
> >> > > Ramanuj Brahmachary
> >> > > Database Administrator
> >> > > [ MCDBA, MCSE, MCSD ]
> >> >
> >> >
> >> >
>
>|||Thanks
--
Ramanuj Brahmachary
Database Administrator
[ MCDBA, MCSE, MCSD ]
"Tibor Karaszi" wrote:
> I have some 3:rd party tools listed on my links page:
> http://www.karaszi.com/SQLServer/links.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ramanuj" <Ramanuj@.discussions.microsoft.com> wrote in message
> news:68B9091E-649C-45C9-8B5B-4F9606FCC876@.microsoft.com...
> > Good day All
> > I am trying to read & Understand SQL Transaction Log.
> > For Online Log, I use DBCC LOG...but how to understand what Row Data means.
> > Also How to read BackedUp (Offline) Log File?
> >
> > Any help would be much appreciated.
> > --
> > Ramanuj Brahmachary
> > Database Administrator
> > [ MCDBA, MCSE, MCSD ]
>
>|||Thanks
--
Ramanuj Brahmachary
Database Administrator
[ MCDBA, MCSE, MCSD ]
"Hari Prasad" wrote:
> Hi,
> Try using the 3rd party tool Logexplorer from Lumigent
> www.lumigent.com
> Thanks
> Hari
> SQL Server MVP
> "Ramanuj" <Ramanuj@.discussions.microsoft.com> wrote in message
> news:68B9091E-649C-45C9-8B5B-4F9606FCC876@.microsoft.com...
> > Good day All
> > I am trying to read & Understand SQL Transaction Log.
> > For Online Log, I use DBCC LOG...but how to understand what Row Data
> > means.
> > Also How to read BackedUp (Offline) Log File?
> >
> > Any help would be much appreciated.
> > --
> > Ramanuj Brahmachary
> > Database Administrator
> > [ MCDBA, MCSE, MCSD ]
>
>

How to Read SQL Transaction Log ...

Sunday, February 19, 2012

How to query for transaction's isolation level...

This is one of the rows from DBCC USEROPTIONS
"SammyBar" <sammybar@.gmail.com> wrote in message
news:%23Bt97iglGHA.4708@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> Is it any way to query a running transaction to see the isolation level it
> is using? I'm debugging a mobile .net application that access a SQL 2K
> database and I need to verify if it is running transaction on the desired
> isolation level (read uncomitted). My idea is to run a test app that left
> a transaction opened, and test the isolation level by running some query
> from the QueryAnalyzer.
> Any hint is welcomed
> Thanks in advance
> Sammy
>
>Hi, Sammy
To determine the transaction isolation level currently set for a given
connection, execute the DBCC USEROPTIONS statement from that
connection.
Razvan|||> This is one of the rows from DBCC USEROPTIONS
but can I make a "DBCC USEROPTIONS" not for my own connection, but for
anoter process or spid?|||>> This is one of the rows from DBCC USEROPTIONS
> but can I make a "DBCC USEROPTIONS" not for my own connection, but for
> anoter process or spid?
Not that I know of. If you're interested in it for a specific procedure,
you could probably jam data into a table based on SPID at the beginning of
the proc, then you can query it for any active spids from other sessions.
However, if you're able to modify the proc to do this, you could probably
just check the proc manually to see if the default isolation level is being
overriden.
A|||In SQL Server 2005, the view sys.dm_exec_sessions (one of the replacements
for sysprocesses) shows the isolation level for every connection.
HTH
Kalen Delaney, SQL Server MVP
"SammyBar" <sammybar@.gmail.com> wrote in message
news:eGfdsQhlGHA.3528@.TK2MSFTNGP02.phx.gbl...
> but can I make a "DBCC USEROPTIONS" not for my own connection, but for
> anoter process or spid?
>|||Hi all,
Is it any way to query a running transaction to see the isolation level it
is using? I'm debugging a mobile .net application that access a SQL 2K
database and I need to verify if it is running transaction on the desired
isolation level (read uncomitted). My idea is to run a test app that left a
transaction opened, and test the isolation level by running some query from
the QueryAnalyzer.
Any hint is welcomed
Thanks in advance
Sammy|||This is one of the rows from DBCC USEROPTIONS
"SammyBar" <sammybar@.gmail.com> wrote in message
news:%23Bt97iglGHA.4708@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> Is it any way to query a running transaction to see the isolation level it
> is using? I'm debugging a mobile .net application that access a SQL 2K
> database and I need to verify if it is running transaction on the desired
> isolation level (read uncomitted). My idea is to run a test app that left
> a transaction opened, and test the isolation level by running some query
> from the QueryAnalyzer.
> Any hint is welcomed
> Thanks in advance
> Sammy
>
>|||Hi, Sammy
To determine the transaction isolation level currently set for a given
connection, execute the DBCC USEROPTIONS statement from that
connection.
Razvan|||> This is one of the rows from DBCC USEROPTIONS
but can I make a "DBCC USEROPTIONS" not for my own connection, but for
anoter process or spid?|||>> This is one of the rows from DBCC USEROPTIONS
> but can I make a "DBCC USEROPTIONS" not for my own connection, but for
> anoter process or spid?
Not that I know of. If you're interested in it for a specific procedure,
you could probably jam data into a table based on SPID at the beginning of
the proc, then you can query it for any active spids from other sessions.
However, if you're able to modify the proc to do this, you could probably
just check the proc manually to see if the default isolation level is being
overriden.
A

Monday, March 26, 2012

How to remove a file [dbcc shrinkfile(filename,empty) is not working]

Wednesday, March 21, 2012

How to reduce the unallocated space of a database?

how to reduce the size of transaction file

Monday, March 12, 2012

how to recompile / refresh UDFs ?

How to rebuild index ?

How to rebuild index ?

How to rebuild index ?

Friday, March 9, 2012

How to read the DBCC page through cleint application

How to Read SQL Transaction Log ...

How to Read SQL Transaction Log ...

How to Read SQL Transaction Log ...

Sunday, February 19, 2012

How to query for transaction's isolation level...

query database table

Blog Archive

About Me