@@ERROR Handling

To All Gurus:
I am trying to log some information to another table when @.@.Error returns an
error. Below is the sample code to see how @.@.error works. In this sample
code, I am purposely failing INSERT INTO TEST command and want to log
informaiton in TestLog. Even though there is a error but the insert to
TestLog never works. What is the catch here ?
/* Create Sample Tables */
Create Table Test (ID Smallint)
Create Table TestLog (ID Smallint)
/*Fail the Insert statement */
INSERT INTO TEST Values (10, 20)
/*This insert should work because error exists. */
IF @.@.Error <> 0
BEGIN
INSERT INTO TESTLOG Values (10, 20)
END
/* No Records are found*/
Select * from testlog
If I run this code in pieces one by one, the code works. When I highlight
the entire code and run it, it never makes it to TestLog table.
Thanks in advance.Sorry, the second Insert statment was
INSERT INTO TestLog Values (10)
"Mark" wrote:

> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||Mark,
See Erland's articles:
http://www.sommarskog.se/error-handling-I.html
and
http://www.sommarskog.se/error-handling-II.html
HTH
Jerry
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:87927DFA-D785-42B2-BF22-972996A8F852@.microsoft.com...
> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
> an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||I read the article but it still didint make it clear as to why Insert into
TestLog never happened when the entire code was highlighted and executed.
Any comments on that one ?
"Mark" wrote:

> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||The insert failure aborts the batch, not the statement. If you put GO
between the statements, it should work. However you cannot use a
multi-batch technique inside of an object (e.g. stored procedure).
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>I read the article but it still didint make it clear as to why Insert into
> TestLog never happened when the entire code was highlighted and executed.
> Any comments on that one ?
> "Mark" wrote:
>|||Mark,
I think it has to do with the type of error you're creating. If you add a
CHECK constraint to Test and perform your insert and it violates the insert
then the value IS inserted into the TestLog table.
HTH
Jerry
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>I read the article but it still didint make it clear as to why Insert into
> TestLog never happened when the entire code was highlighted and executed.
> Any comments on that one ?
> "Mark" wrote:
>|||Thanks to both Jerry and Aaron. You are right, it has to do with what kind o
f
insert error it is. It either rollsback the batch or logs the info based on
the type of error. I created two scenarions and in one case it works and in
other one, it doesnt.
Once again thanks to both
"Jerry Spivey" wrote:

> Mark,
> I think it has to do with the type of error you're creating. If you add a
> CHECK constraint to Test and perform your insert and it violates the inser
t
> then the value IS inserted into the TestLog table.
> HTH
> Jerry
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>
>|||Mark (Mark@.discussions.microsoft.com) writes:
> Thanks to both Jerry and Aaron. You are right, it has to do with what
> kind of insert error it is. It either rollsback the batch or logs the
> info based on the type of error. I created two scenarions and in one
> case it works and in other one, it doesnt.
As noted in my article, an error can lead to termination on three
different levels:
1) Statement
2) Scope
3) Bacth.
Your error was of the second kind. Scope-aborting errors are, as far as
I know, always compilation errors. What is tricky, is that due to
deferred named resolution, compilation errors can happen at run-time.
Consider:
Create Table Test (ID Smallint)
Create Table TestLog (ID Smallint)
--go
print 'Hello!'
/*Fail the Insert statement */
INSERT INTO Test Values (10, 20)
/*This insert should work because error exists. */
IF @.@.Error <> 0
BEGIN
INSERT INTO TestLog Values (10)
END
go
SELECT * FROM TestLog
This script will print Hello!, but if you uncomment the go, it will not.
This is because in the script as it stands, Test does not exist, so SQL
Server defers compilation of that statement. If you uncomment the go,
the tables exists when the batch is compiled, and thus you get the error
directly, so execution never starts.
Finally, note that if you write:
INSERT INTO Test(ID) Values(10, 20)
the batch always fails to compile, as SQL Server does not need to know
the table definition. Incidently, most people agree that INSERT without
a column list is not good practice.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

@@CPU_BUSY stops incrementing and generates "Arithmetic overflow occurred"

I am using several globals including @.@.CPU_BUSY to log the performance of some stored procedures. It works fine on my dev server, but in production the value reported by @.@.CPU_BUSY on my production server has stopped at 134217727. And when I try to assign its value to a variable, it always generates an error.

For example:

DECLARE @.FOO BigInt
Set @.foo = @.@.CPU_BUSY
print @.Foo

generates

Arithmetic overflow occurred.
134217727

Note that

DECLARE @.FOO BigInt
Set @.foo = 134217727
print @.Foo

works fine.

I understand that when the value of @.@.CPU_BUSY reaches a certain point, its value becomes inaccurate according to MSDN TSQL Reference. I've also seen some descriptions that say the value is supposed to "wrap" back to zero.

So the questions are:
Is the value of @.@.CPU_BUSY supposed to wrap back to zero when it overflows?
Is there a better/more reliable way to do this?

Cheers,
Jeff
by the way:
Select @.@.version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)sql

/logger option

Hi,

I'm trying to use the below command to execute for generating the log file for SSIS package.

DTExec /FILE "C:\Documents and Settings\CP0808\My Documents\Test.dtsx" /logger "DTS.LogProviderTextFile;c:\log.txt"

But it got the error as below. Please advise.

Started: 10:03:20 AM
Error: 2006-10-06 10:03:21.15
Code: 0xC001000E
Source: Test
Description: The connection "c:\log.txt" is not found. This error is thrown b
y Connections collection when the specific connection element is not found.
End Error
Error: 2006-10-06 10:03:21.18
Code: 0xC001000E
Source: Test
Description: The connection "c:\log.txt" is not found. This error is thrown b
y Connections collection when the specific connection element is not found.
End Error
Error: 2006-10-06 10:03:21.21
Code: 0xC02020EA
Source: Test Log provider "{1AEAB490-1124-4A84-981F-7C1FDD80A721}"
Description: The connection manager "c:\log.txt" is not found. A component fa
iled to find the connection manager in the Connections collection.
End Error

Hi,

the logger option in DTEXEC specifies an already existing connection within the package. YOu will have to create a file location within the package to reference it.

Have a look here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72104

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

I still got the error as below.

DTExec /FILE "C:\Documents and Settings\CP0808\My Documents\Test.dtsx" /CONNECTION DestinationConnectionFlatFile;"c:\test.csv" /CONNECTION SourceConnectionOLEDB;"Data Source=test;User ID=user;Initial Catalog=DATA_CONV;Provider=SQLNCLI;Auto Translate=false;PASSWORD=password" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /l "DTS.LogProviderTextFile;log.txt" /Set "\package.Connections[log.txt].Properties[ConnectionString];c:\log.txt"

Error: 2006-10-10 09:57:21.07
Code: 0xC001000E
Source: Test
Description: The connection "log.txt" is not found. This error is thrown by C
onnections collection when the specific connection element is not found.
End Error
Error: 2006-10-10 09:57:21.10
Code: 0xC001000E
Source: Test
Description: The connection "log.txt" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error
Warning: 2006-10-10 09:57:21.14
Code: 0x8001F02F
Source: Test
Description: Cannot resolve a package path to an object in the package ".Connections[log.txt].Properties[ConnectionString]". Verify that the package path is valid.
End Warning
Warning: 2006-10-10 09:57:21.17
Code: 0x80012017
Source: Test
Description: The package path referenced an object that cannot be found: "\package.Connections[log.txt].Properties[ConnectionString]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set \package.Connections[log.txt].Properties[ConnectionString]
value to c:\log.txt.
Started: 9:57:20 AM
Finished: 9:57:21 AM
Elapsed: 0.5 seconds

/L*v C:\temp\logfile

Hi!
I found that using /L*v with the setup procedure creates a verbose log
file. Is there any way to add a verbose log file to an existing database?
Stefan
hi Stefan,
"Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
news:opsfqz6rois9ddfw@.news.individual.de
> Hi!
> I found that using /L*v with the setup procedure creates a verbose
> log file. Is there any way to add a verbose log file to an existing
> database?
> Stefan
/L*v is a setup parameter, which enables the setup logging features...
I do not understand your requirements regarding "add a verbose log file to
an existing database"...
all SQL Server databases do have a transaction log file at least, and the
logging mode is dependent to the recovery setting, as described in
http://msdn.microsoft.com/library/de...kprst_6rqr.asp
...
can you please elaborate your requirements?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 12 Oct 2004 11:05:25 +0200, Andrea Montanari
<andrea.sqlDMO@.virgilio.it> wrote:

> hi Stefan,
> "Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
> news:opsfqz6rois9ddfw@.news.individual.de
> /L*v is a setup parameter, which enables the setup logging features...
> I do not understand your requirements regarding "add a verbose log file
> to an existing database"...
> all SQL Server databases do have a transaction log file at least, and the
> logging mode is dependent to the recovery setting, as described in
> http://msdn.microsoft.com/library/de...kprst_6rqr.asp
> ..
> can you please elaborate your requirements?
I am having troubles when connecting to MSDE 1.0 after installing XP SP2:
The inital connection takes an eternity (10 seconds on lightning fast
machines, up to 90 seconds on my older working machine). In this time,
sqlsvr.exe causes 100% processor load and a lot of I/O stress. I'd simply
like to find out what is going on.
After the initial connection lag, operation continues normally.
We connect through Delphi 5 using ADO.
any pointers appreciated (upgrading to D7 is not an option; it's not my
decision)
Stefan
|||hi Stefan,
"Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
news:opsfq3c3uks9ddfw@.news.individual.de
> I am having troubles when connecting to MSDE 1.0 after installing XP
> SP2: The inital connection takes an eternity (10 seconds on lightning
> fast machines, up to 90 seconds on my older working machine). In this
> time, sqlsvr.exe causes 100% processor load and a lot of I/O stress.
> I'd simply like to find out what is going on.
> After the initial connection lag, operation continues normally.
> We connect through Delphi 5 using ADO.
> any pointers appreciated (upgrading to D7 is not an option; it's not
> my decision)
> Stefan
XP sp2 causes a lot of connection issues, see
http://www.michna.com/kb/WxSP2.htm for instance...
for related SQL Server issues please have a look at
http://support.microsoft.com/default.aspx?kbid=841249
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 12 Oct 2004 13:04:27 +0200, Andrea Montanari
<andrea.sqlDMO@.virgilio.it> wrote:

> XP sp2 causes a lot of connection issues, see
> http://www.michna.com/kb/WxSP2.htm for instance...
> for related SQL Server issues please have a look at
> http://support.microsoft.com/default.aspx?kbid=841249
Thanks for the pointers, Andrea. We've gone through the second one
already; I'll double check if we missed something there.
Do you think that an upgrade to MSDE 2000 would cure some issues?
Stefan, off reading
|||hi Stefan,
"Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
news:opsfq89i1ws9ddfw@.news.individual.de
> ..
> Thanks for the pointers, Andrea. We've gone through the second one
> already; I'll double check if we missed something there.
> Do you think that an upgrade to MSDE 2000 would cure some issues?
> Stefan, off reading
actually not, but MSDE 2000 can be worth upgrading becouse several
improvements in the database engine...
I only installed a Virtual Machine of WinXP sp 2 but had (fortunately) no
related issue to connectivity..
opened the TCP port required for network connections and enlisted a range of
subnet IP addresses... all is ok..
... and I hope to be that lucky when upgrading the real physical machine
=;-D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 12 Oct 2004 15:39:23 +0200, Andrea Montanari
<andrea.sqlDMO@.virgilio.it> wrote:

> hi Stefan,
> "Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
> news:opsfq89i1ws9ddfw@.news.individual.de
> actually not, but MSDE 2000 can be worth upgrading becouse several
> improvements in the database engine...
Yes; as long as we can easily handle upgrading issues, like the password
in all our connection strings.

> I only installed a Virtual Machine of WinXP sp 2 but had (fortunately) no
> related issue to connectivity..
> opened the TCP port required for network connections and enlisted a
> range of subnet IP addresses... all is ok..
> ... and I hope to be that lucky when upgrading the real physical machine
> =;-D
I'd rather set up a complete testing machine beforehand
Good luck and thanks,
Stefan

.ldf, .mdf and .ndf file extensions

Hi,
Is the following interpretation correct for MS-SQL file extensions:
.ldf -> transaction log file
.mdf -> "master" data file
.ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
----
---- --
----
----
----
--
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)Found this in the Books Online. This answers my query.
File type File name extension
Primary data file .mdf
Secondary data file .ndf
Transaction log file .ldf
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
Hi,
Is the following interpretation correct for MS-SQL file extensions:
.ldf -> transaction log file
.mdf -> "master" data file
.ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
----
---- --
----
----
----
--
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)|||Be aware that these are just conventions. SQL Server does not enforce or
require any particular file extensions for any data or log files. Still,
it is a good idea to stick to the conventions if you want to go on vacation
sometime.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
> Hi,
> Is the following interpretation correct for MS-SQL file extensions:
> .ldf -> transaction log file
> .mdf -> "master" data file
> .ndf -> "normal" data file
> Also, why is that the first data file that gets created in a database has
> the extension .mdf? Is it in some way related to the "master" database?
> Thanks,
> - V
> Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
> osql for an MS-SQL 2005 instance.
>
> fileid groupid size maxsize growth status perf
> name
> filename
> -- -- -- -- -- -- --
> ----
--
> ---- --
--
> ----
--
> ----
--
> ----
--
> --
> 1 1 38400 51200 10 1048578 0
> dataFile1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
> 2 0 12800 268435456 0 66 0
> marathon_log1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
> 3 1 4096 -1 128 2 0
> marathon_data4_444444
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
> 4 2 3968 -1 128 2 0
> marathon_data2
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
> 5 2 8576 -1 128 2 0
> marathon_data3
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
> 6 2 3840 -1 128 2 0
> marathon_data5_55555555
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
> 7 2 14208 -1 0 2 0
> marathon_data6_66666666
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
> 8 0 384 268435456 128 66 0
> marathon_log2
> M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
> (8 rows affected)
>

.ldf, .mdf and .ndf file extensions

Hi,
Is the following interpretation correct for MS-SQL file extensions:
.ldf -> transaction log file
.mdf -> "master" data file
.ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
----
---- --
----
----
----
--
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)Found this in the Books Online. This answers my query.
File type File name extension
Primary data file .mdf
Secondary data file .ndf
Transaction log file .ldf
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
Hi,
Is the following interpretation correct for MS-SQL file extensions:
.ldf -> transaction log file
.mdf -> "master" data file
.ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
----
---- --
----
----
----
--
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)|||Be aware that these are just conventions. SQL Server does not enforce or
require any particular file extensions for any data or log files. Still,
it is a good idea to stick to the conventions if you want to go on vacation
sometime.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
> Hi,
> Is the following interpretation correct for MS-SQL file extensions:
> .ldf -> transaction log file
> .mdf -> "master" data file
> .ndf -> "normal" data file
> Also, why is that the first data file that gets created in a database has
> the extension .mdf? Is it in some way related to the "master" database?
> Thanks,
> - V
> Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
> osql for an MS-SQL 2005 instance.
>
> fileid groupid size maxsize growth status perf
> name
> filename
> -- -- -- -- -- -- --
> ----
> ---- --
> ----
> ----
> ----
> --
> 1 1 38400 51200 10 1048578 0
> dataFile1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
> 2 0 12800 268435456 0 66 0
> marathon_log1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
> 3 1 4096 -1 128 2 0
> marathon_data4_444444
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
> 4 2 3968 -1 128 2 0
> marathon_data2
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
> 5 2 8576 -1 128 2 0
> marathon_data3
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
> 6 2 3840 -1 128 2 0
> marathon_data5_55555555
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
> 7 2 14208 -1 0 2 0
> marathon_data6_66666666
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
> 8 0 384 268435456 128 66 0
> marathon_log2
> M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
> (8 rows affected)
>

.ldf, .mdf and .ndf file extensions

Hi,
Is the following interpretation correct for MS-SQL file extensions:
..ldf -> transaction log file
..mdf -> "master" data file
..ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
---- --
----
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.nd f
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555. ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666. ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)
Found this in the Books Online. This answers my query.
File type File name extension
Primary data file .mdf
Secondary data file .ndf
Transaction log file .ldf
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
Hi,
Is the following interpretation correct for MS-SQL file extensions:
..ldf -> transaction log file
..mdf -> "master" data file
..ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
---- --
----
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.nd f
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555. ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666. ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)
|||Be aware that these are just conventions. SQL Server does not enforce or
require any particular file extensions for any data or log files. Still,
it is a good idea to stick to the conventions if you want to go on vacation
sometime.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
> Hi,
> Is the following interpretation correct for MS-SQL file extensions:
> .ldf -> transaction log file
> .mdf -> "master" data file
> .ndf -> "normal" data file
> Also, why is that the first data file that gets created in a database has
> the extension .mdf? Is it in some way related to the "master" database?
> Thanks,
> - V
> Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
> osql for an MS-SQL 2005 instance.
>
> fileid groupid size maxsize growth status perf
> name
> filename
> -- -- -- -- -- -- --
> ----
> ---- --
> ----
> ----
> ----
> --
> 1 1 38400 51200 10 1048578 0
> dataFile1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
> 2 0 12800 268435456 0 66 0
> marathon_log1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
> 3 1 4096 -1 128 2 0
> marathon_data4_444444
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.nd f
> 4 2 3968 -1 128 2 0
> marathon_data2
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
> 5 2 8576 -1 128 2 0
> marathon_data3
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
> 6 2 3840 -1 128 2 0
> marathon_data5_55555555
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555. ndf
> 7 2 14208 -1 0 2 0
> marathon_data6_66666666
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666. ndf
> 8 0 384 268435456 128 66 0
> marathon_log2
> M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
> (8 rows affected)
>

.ldf log file

Hi everybody, I have a 10.2Gb .ldf file that is giving me hard disk space problems. We already empty the file with the Enterprise Manager, but the file
still weigths the same...
Is there any procedure shrink this file??
thanks in advanceHi everybody, I have a 10.2Gb .ldf file that is giving me hard disk space problems. We already empty the file with the Enterprise Manager, but the file
still weigths the same...
Is there any procedure shrink this file??
thanks in advance

go to the database in question, type in CHECKPOINT. Wait a little while, then run a shrinkdb. If you are not going to be doing log backups, you need to set the recovery mode to Simple so you don't keep having trouble with this though. After you set it to simple, you can run a shrinkdb and you'll be fine.|||thanks,!
that worked pretty good...
regards,

.ldf files

Is the .ldf file the tranasction log, and if so,
How can I analyze the contents of the .ldf file?Yes, this is most likely your transaction log, and it is not intended to be
analyzable. It is for SQL Server's own internal purposes. There are some 3rd
party tools available which can analyze the contents, such as
http://www.red-gate.com/products/SQL_Log_Rescue/
and
http://www.lumigent.com/products/le_sql.html
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"mfm" <mfm@.discussions.microsoft.com> wrote in message
news:F937D3DE-463A-45F8-9FC6-CB78605552FD@.microsoft.com...
> Is the .ldf file the tranasction log, and if so,
> How can I analyze the contents of the .ldf file?
>|||mfm
http://dimantdatabasesolutions.blog...er.ht
ml
http://dimantdatabasesolutions.blog...le.html

"mfm" <mfm@.discussions.microsoft.com> wrote in message
news:F937D3DE-463A-45F8-9FC6-CB78605552FD@.microsoft.com...
> Is the .ldf file the tranasction log, and if so,
> How can I analyze the contents of the .ldf file?
>

.....SQL Server does not exist or access denied .. Help !

3 workstations previously installed and able to access server.
4th workstation am able to map to server access files but when
attempting to log in receive the following error.
EXTERNAL DATABASE OPEN FAILURE
Error: -2147467259[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL
Server does not exist.
The workstation that is unable to connect is running XP SP2, MDAC 2.7..
the difference btwn this and the others is that it is running wireless.
Ruled out wirelss as being the problem because hooked LAN cable and
receive the same error.
Checked that TCP/IP is enabled and port is 1433
Went to Adminstrative Tools ODBC to access server and receive the
following error:
Connection failed
SQLState: '01000'
SQL Server Error: 20
[Microsoft][ODBC SQL Server Driver][TCP/IP
Sockets]ConnectionOpen(PreLoginHandshake()).
Connection failed;
SQLState: '08001'
SQL Server Error:20
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Encryption support
on SQL Server
Tried some other setting changes as well can someone help.. PLEASE !!
*** Sent via Developersdex http://www.codecomments.com ***
What version of SQL is on your server?
From WS4, try the odbcping.exe utility. YOu can find it on your SQL Server
install media. This will determine if you can make a conn over odbc, and will
confirm the SQL instance responds to the ping...
Also - make sure the XP built in firewall (or other fw sw) is not blocking
comm...
Regards,
ChrisB
MCDBA OCP
www.MyDatabaseAdmin.com
"Jessica McMillan" wrote:

> 3 workstations previously installed and able to access server.
> 4th workstation am able to map to server access files but when
> attempting to log in receive the following error.
> EXTERNAL DATABASE OPEN FAILURE
> Error: -2147467259[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL
> Server does not exist.
> The workstation that is unable to connect is running XP SP2, MDAC 2.7..
> the difference btwn this and the others is that it is running wireless.
> Ruled out wirelss as being the problem because hooked LAN cable and
> receive the same error.
> Checked that TCP/IP is enabled and port is 1433
> Went to Adminstrative Tools ODBC to access server and receive the
> following error:
> Connection failed
> SQLState: '01000'
> SQL Server Error: 20
> [Microsoft][ODBC SQL Server Driver][TCP/IP
> Sockets]ConnectionOpen(PreLoginHandshake()).
> Connection failed;
> SQLState: '08001'
> SQL Server Error:20
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Encryption support
> on SQL Server
> Tried some other setting changes as well can someone help.. PLEASE !!
>
> *** Sent via Developersdex http://www.codecomments.com ***
>

.....SQL Server does not exist or access denied .. Help !

3 workstations previously installed and able to access server.
4th workstation am able to map to server access files but when
attempting to log in receive the following error.
EXTERNAL DATABASE OPEN FAILURE
Error: -2147467259[Microsoft][ODBC SQL Server Driver][Shared Mem
ory]SQL
Server does not exist.
The workstation that is unable to connect is running XP SP2, MDAC 2.7..
the difference btwn this and the others is that it is running wireless.
Ruled out wirelss as being the problem because hooked LAN cable and
receive the same error.
Checked that TCP/IP is enabled and port is 1433
Went to Adminstrative Tools ODBC to access server and receive the
following error:
Connection failed
SQLState: '01000'
SQL Server Error: 20
[Microsoft][ODBC SQL Server Driver][TCP/IP
Sockets]ConnectionOpen(PreLoginHandshake
()).
Connection failed;
SQLState: '08001'
SQL Server Error:20
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Encryption su
pport
on SQL Server
Tried some other setting changes as well can someone help.. PLEASE !!
*** Sent via Developersdex http://www.codecomments.com ***What version of SQL is on your server?
From WS4, try the odbcping.exe utility. YOu can find it on your SQL Server
install media. This will determine if you can make a conn over odbc, and wil
l
confirm the SQL instance responds to the ping...
Also - make sure the XP built in firewall (or other fw sw) is not blocking
comm...
Regards,
ChrisB
MCDBA OCP
www.MyDatabaseAdmin.com
"Jessica McMillan" wrote:

> 3 workstations previously installed and able to access server.
> 4th workstation am able to map to server access files but when
> attempting to log in receive the following error.
> EXTERNAL DATABASE OPEN FAILURE
> Error: -2147467259[Microsoft][ODBC SQL Server Driver][Shared M
emory]SQL
> Server does not exist.
> The workstation that is unable to connect is running XP SP2, MDAC 2.7..
> the difference btwn this and the others is that it is running wireless.
> Ruled out wirelss as being the problem because hooked LAN cable and
> receive the same error.
> Checked that TCP/IP is enabled and port is 1433
> Went to Adminstrative Tools ODBC to access server and receive the
> following error:
> Connection failed
> SQLState: '01000'
> SQL Server Error: 20
> [Microsoft][ODBC SQL Server Driver][TCP/IP
> Sockets]ConnectionOpen(PreLoginHandshake
()).
> Connection failed;
> SQLState: '08001'
> SQL Server Error:20
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Encryption
support
> on SQL Server
> Tried some other setting changes as well can someone help.. PLEASE !!
>
> *** Sent via Developersdex http://www.codecomments.com ***
>

*.ldf files are becoming smaller each night

While monitoring the logical log files I noticed that they were often smalle
r
in the morning. I've been trying to find what is doing the shrink.
I see nothing in the maintenance plan about trunking the log file and I have
found nothing else.
The only clue I have is that, on a slow day, the date stamp on the file is
just a couple minutes after the machine reboot.
I want to avoid the fragmentation that happens when the file grows.
Can anyone help me explain this?Autoshrink?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:DF4BED2D-93CD-4293-A2C8-819718C3A1EB@.microsoft.com...
> While monitoring the logical log files I noticed that they were often smal
ler
> in the morning. I've been trying to find what is doing the shrink.
> I see nothing in the maintenance plan about trunking the log file and I ha
ve
> found nothing else.
> The only clue I have is that, on a slow day, the date stamp on the file is
> just a couple minutes after the machine reboot.
> I want to avoid the fragmentation that happens when the file grows.
> Can anyone help me explain this?

**Problem with FormAuthentication **

Hi
i am working with RS formauthuntication .
while i am trying to log on to ReportManager.
I am getting the errror like.
"The request failed with HTTP status 401: Access Denied. "
I have been spending the wholetime for twodays ,on this .
but i could't understand.
so anbody can help me.
ThanksDid it work with windows authentication?
What did you do to set up forms authentication?
Mike G.
"simmi" <simmi@.discussions.microsoft.com> wrote in message
news:A2397EB5-BC8D-45FE-A09D-EFEE12D411F7@.microsoft.com...
> Hi
> i am working with RS formauthuntication .
> while i am trying to log on to ReportManager.
> I am getting the errror like.
> "The request failed with HTTP status 401: Access Denied. "
> I have been spending the wholetime for twodays ,on this .
> but i could't understand.
> so anbody can help me.
>
> Thanks|||Hi .
Yes windows authenticatin for RS is working.
To set form authentication i fallowed all steps as it is which had given in
MSDN.
"Mike G." wrote:
> Did it work with windows authentication?
> What did you do to set up forms authentication?
> Mike G.
>
> "simmi" <simmi@.discussions.microsoft.com> wrote in message
> news:A2397EB5-BC8D-45FE-A09D-EFEE12D411F7@.microsoft.com...
> > Hi
> >
> > i am working with RS formauthuntication .
> >
> > while i am trying to log on to ReportManager.
> >
> > I am getting the errror like.
> >
> > "The request failed with HTTP status 401: Access Denied. "
> >
> > I have been spending the wholetime for twodays ,on this .
> > but i could't understand.
> >
> > so anbody can help me.
> >
> >
> > Thanks
>
>