Asp.net / sql help: log

Showing posts with label log. Show all posts

Thursday, March 29, 2012

Attach norecovery database

Is there a way to attach a database to norecovery mode? We have a 2nd site
where the target database is in norecovery mode for continuous log files.
If we detach the database while it is in norecovery mode, could we attach
the database back in norecovery mode. I tried it and it turned into
recovery mode with few transactions rollback while detach. Thanks!
When you detach a db it rolls back any open trans so the answer is no. You
can attach it but it it will already have been put into recovery.
Andrew J. Kelly SQL MVP
"Kevin" <Kevin@.noemail.com> wrote in message
news:OWajj3qmEHA.1288@.TK2MSFTNGP10.phx.gbl...
> Is there a way to attach a database to norecovery mode? We have a 2nd
site
> where the target database is in norecovery mode for continuous log files.
> If we detach the database while it is in norecovery mode, could we attach
> the database back in norecovery mode. I tried it and it turned into
> recovery mode with few transactions rollback while detach. Thanks!
>
>

Attach norecovery database

Is there a way to attach a database to norecovery mode? We have a 2nd site
where the target database is in norecovery mode for continuous log files.
If we detach the database while it is in norecovery mode, could we attach
the database back in norecovery mode. I tried it and it turned into
recovery mode with few transactions rollback while detach. Thanks!When you detach a db it rolls back any open trans so the answer is no. You
can attach it but it it will already have been put into recovery.
--
Andrew J. Kelly SQL MVP
"Kevin" <Kevin@.noemail.com> wrote in message
news:OWajj3qmEHA.1288@.TK2MSFTNGP10.phx.gbl...
> Is there a way to attach a database to norecovery mode? We have a 2nd
site
> where the target database is in norecovery mode for continuous log files.
> If we detach the database while it is in norecovery mode, could we attach
> the database back in norecovery mode. I tried it and it turned into
> recovery mode with few transactions rollback while detach. Thanks!
>
>sql

Attach DB Without Transaction Logs

Hi, there,

I have multiple transaction log files. I don't need the transaction logs and intended to remove them. I have tried detaching the database, rename the logs and trying to re-attach the database without the logs but to no avail. The error message mentioned something like file activation failed and SQL couldn't rebuild the transaction logs.

Is there a way to get rid of the transaction log files?

Thank you.

Regards,

Yong Hwee

Hi,

you can use sp_attach_single_file_db but it works only on databases that have a single log file.

When sp_attach_single_file_db attaches the database to the server, it builds a new log file. If the database is read-only, the log file is built in its previous location.

...

OR by reading this thread attaching DB without .ldf file ?

tosc

|||

Hi, Tosc,

Thank you for your immediate reply. It seems like there is no workaround for database with multiple log files.

Regards,

Yong Hwee

|||

There are lot of limitation with sp_attach_single_file_db. The database should not have morethan one LOG and Datafile and the database should be properly detached. So in your case this process will not do. You requirement is to remove the Secondary LOG File, if am correct. What you can do is, Remove the secondary log file first using

DBCC SHRINKFILE (,EMPTYFILE)
alter database test remove file .

Then you can trucate the primary log file to any size you want. Please take backup of the database before starting anything

Madhu

Refer : http://madhuottapalam.blogspot.com/search?q=Transaction+Log

Madhu

|||

In SQL Server 2005 you can you use CREATE DATABASE syntax to rebuild the log. The ATTACH_REBUILD_LOG syntax should work even if there are multiple log files.

|||

Hi, there,

Thank you for your reply. I managed to delete the log by first backup with option to truncate log after which I change the database to simple recovery. I can delete the logs this way.

Thank you.

Regards,

Yong Hwee

Attach db without transaction log file

Hi!
Is it possible attach db without transaction log file?
With store procedure like sp_attach_db and sp_attach_single_file_db it isn't go. Server still looking for transaction log file.
Thanks your helpHi,
You can use sp_attach_single_file_db only on databases that have a single
log file.
Instead try to update the database status to emergency mode and create a new
database and use DTS to transafer all objects to new database.
Steps:
1. Update the database to Emergency mode
update sysdatabases set status=32768 where name=<name of the database>
2. Craete a new database
3. Use DTS to transfer all objects
Thanks
Hari
MCDBA
"Ondrik" <anonymous@.discussions.microsoft.com> wrote in message
news:B3D1753B-C678-4A7E-AEFF-5FD7725FCC2C@.microsoft.com...
> Hi!
> Is it possible attach db without transaction log file?
> With store procedure like sp_attach_db and sp_attach_single_file_db it
isn't go. Server still looking for transaction log file.
> Thanks your help|||Try this:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
--
Andrew J. Kelly
SQL Server MVP
"Ondrik" <anonymous@.discussions.microsoft.com> wrote in message
news:B3D1753B-C678-4A7E-AEFF-5FD7725FCC2C@.microsoft.com...
> Hi!
> Is it possible attach db without transaction log file?
> With store procedure like sp_attach_db and sp_attach_single_file_db it
isn't go. Server still looking for transaction log file.
> Thanks your help|||You must first: sp_detach_db
then: delete the ..._log.sfd
then: sp_attach_single_file_db
Bye
Uwe

attach db with a deleted log

Hello,
How can i attach a db where there is a log deleted.
When i want to attach the db, i cannot click the ok button because a
second log-file is missing.
Anyway to work around this?Don't use Enterprise Manager. Use sp_attach_db or
sp_attach_single_file_db. See Books Online for details and examples of
those commands.
Make sure you have a good backup of your MDF/LDF files before you try
to attach them.
David Portas
SQL Server MVP
--sql

Attach db w/no log file

I had a client run out of disk space because their transaction log grew too
large. They accidentally deleted the tran log (didn't go to Recycle bin
because of size), and their last good backup was from 3 weeks ago. If I try
to attach the database either through Enterprise Manager or QA it tells me
the physical file name for the ldf is incorrect. Is there a way to get
around this error? Can I restore the old backup to a different name and use
its ldf file? Where is the info stored that tells the system what log file
is associated with the database? Can I modify that somehow? Thanks!Look up sp_attach_single_file_db in BOL
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>I had a client run out of disk space because their transaction log grew too
> large. They accidentally deleted the tran log (didn't go to Recycle bin
> because of size), and their last good backup was from 3 weeks ago. If I
> try
> to attach the database either through Enterprise Manager or QA it tells me
> the physical file name for the ldf is incorrect. Is there a way to get
> around this error? Can I restore the old backup to a different name and
> use
> its ldf file? Where is the info stored that tells the system what log
> file
> is associated with the database? Can I modify that somehow? Thanks!|||Thanks for the reply. I just tried this and it is still telling me that the
ldf file 'may be incorrect.' Can I reattach using the old log file? Or
would that potentially mess up my database? Thanks.
"Michael C#" wrote:
> Look up sp_attach_single_file_db in BOL
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
> >I had a client run out of disk space because their transaction log grew too
> > large. They accidentally deleted the tran log (didn't go to Recycle bin
> > because of size), and their last good backup was from 3 weeks ago. If I
> > try
> > to attach the database either through Enterprise Manager or QA it tells me
> > the physical file name for the ldf is incorrect. Is there a way to get
> > around this error? Can I restore the old backup to a different name and
> > use
> > its ldf file? Where is the info stored that tells the system what log
> > file
> > is associated with the database? Can I modify that somehow? Thanks!
>
>|||You can't add an unmacthing transaction log file to a database. The purpose of a transaction log is
amongst other things to support recovery. SQL Server does a check at startup that the lsn number for
the log records matches with what to expect for the database. If restore isn't a viable option, I
suggest you let MS Support help you. They might have a way to get the database back, in some shape.
And, make sure you give access to the system to people who might do these operations as well as have
a good backup strategy in the future. But I guess you know this by now. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> Thanks for the reply. I just tried this and it is still telling me that the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>> Look up sp_attach_single_file_db in BOL
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >I had a client run out of disk space because their transaction log grew too
>> > large. They accidentally deleted the tran log (didn't go to Recycle bin
>> > because of size), and their last good backup was from 3 weeks ago. If I
>> > try
>> > to attach the database either through Enterprise Manager or QA it tells me
>> > the physical file name for the ldf is incorrect. Is there a way to get
>> > around this error? Can I restore the old backup to a different name and
>> > use
>> > its ldf file? Where is the info stored that tells the system what log
>> > file
>> > is associated with the database? Can I modify that somehow? Thanks!
>>|||Check and see if it actually attached. If I recall correctly,
sp_attach_single_file_db will give a warning message when it doesn't find
the log file, but it should still attach successfully.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> Thanks for the reply. I just tried this and it is still telling me that
> the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>> Look up sp_attach_single_file_db in BOL
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >I had a client run out of disk space because their transaction log grew
>> >too
>> > large. They accidentally deleted the tran log (didn't go to Recycle
>> > bin
>> > because of size), and their last good backup was from 3 weeks ago. If
>> > I
>> > try
>> > to attach the database either through Enterprise Manager or QA it tells
>> > me
>> > the physical file name for the ldf is incorrect. Is there a way to get
>> > around this error? Can I restore the old backup to a different name
>> > and
>> > use
>> > its ldf file? Where is the info stored that tells the system what log
>> > file
>> > is associated with the database? Can I modify that somehow? Thanks!
>>|||I double-checked and it is not attached. A few other users on a different
forum mentioned the same thing. Someone else mentioned an Emergency Mode and
a log re buid resolution they received from MS. Hopefully this will help.
Thank you for your responses.
"Michael C#" wrote:
> Check and see if it actually attached. If I recall correctly,
> sp_attach_single_file_db will give a warning message when it doesn't find
> the log file, but it should still attach successfully.
>
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> > Thanks for the reply. I just tried this and it is still telling me that
> > the
> > ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> > would that potentially mess up my database? Thanks.
> >
> > "Michael C#" wrote:
> >
> >> Look up sp_attach_single_file_db in BOL
> >>
> >> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> >> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
> >> >I had a client run out of disk space because their transaction log grew
> >> >too
> >> > large. They accidentally deleted the tran log (didn't go to Recycle
> >> > bin
> >> > because of size), and their last good backup was from 3 weeks ago. If
> >> > I
> >> > try
> >> > to attach the database either through Enterprise Manager or QA it tells
> >> > me
> >> > the physical file name for the ldf is incorrect. Is there a way to get
> >> > around this error? Can I restore the old backup to a different name
> >> > and
> >> > use
> >> > its ldf file? Where is the info stored that tells the system what log
> >> > file
> >> > is associated with the database? Can I modify that somehow? Thanks!
> >>
> >>
> >>
>
>|||One more thing. Make sure the MDF file is not marked READ-ONLY by the
operating system (for instance, if you burned it on a CD, and copied it
back). If it is, make it read/write and try again. Hope you find a
resolution quickly.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:00FC1F66-801D-4BC6-8C72-3884BBDDB250@.microsoft.com...
>I double-checked and it is not attached. A few other users on a different
> forum mentioned the same thing. Someone else mentioned an Emergency Mode
> and
> a log re buid resolution they received from MS. Hopefully this will help.
> Thank you for your responses.
> "Michael C#" wrote:
>> Check and see if it actually attached. If I recall correctly,
>> sp_attach_single_file_db will give a warning message when it doesn't find
>> the log file, but it should still attach successfully.
>>
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
>> > Thanks for the reply. I just tried this and it is still telling me
>> > that
>> > the
>> > ldf file 'may be incorrect.' Can I reattach using the old log file?
>> > Or
>> > would that potentially mess up my database? Thanks.
>> >
>> > "Michael C#" wrote:
>> >
>> >> Look up sp_attach_single_file_db in BOL
>> >>
>> >> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> >> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >> >I had a client run out of disk space because their transaction log
>> >> >grew
>> >> >too
>> >> > large. They accidentally deleted the tran log (didn't go to Recycle
>> >> > bin
>> >> > because of size), and their last good backup was from 3 weeks ago.
>> >> > If
>> >> > I
>> >> > try
>> >> > to attach the database either through Enterprise Manager or QA it
>> >> > tells
>> >> > me
>> >> > the physical file name for the ldf is incorrect. Is there a way to
>> >> > get
>> >> > around this error? Can I restore the old backup to a different name
>> >> > and
>> >> > use
>> >> > its ldf file? Where is the info stored that tells the system what
>> >> > log
>> >> > file
>> >> > is associated with the database? Can I modify that somehow?
>> >> > Thanks!
>> >>
>> >>
>> >>
>>

Attach db w/no log file

Tuesday, March 27, 2012

Attach db failure cause log not primary

Hi Everybody,

I need URGENT help with this. Because of space problem my collegue create a second log file on another drive. I wanted to clean the thing, made a detach, renamed the ldf into old, but now it won't attach anymore, I get the following messages:

EXEC sp_attach_db @.dbname='TARIFINFO_GEN',
@.filename1 = 'F:\Data\TARIFINFO_GEN_Data.mdf'

Server: Msg 5171 F:\Data\TARIFINFO_GEN_log.ldf is not a primary database file
Server: Msg 1813 Device activation error the physical file name 'F:\Data\TARIFINFO_GEN_log.ldf' may be incorrect

Do someone know what to do or where to look for a solution. Many thanks in advance, Isabelle.Try sp_attach_single_file_db 'TARIFINFO_GEN',
'F:\Data\TARIFINFO_GEN_Data.mdf'

Also, in many instances to clean logs you should be able to run something like:

backup log TARIFINFO_GEN with truncate_only

and then a dbcc shrinkfile.

Instead of detaching and re-attaching.

Eric

Attach database without log file

How can I attach a database file if my log file is not available. I only have the database file because the log file was deleted. When I try attaching the database file, I get the error that it can not find the log file.

What is strange is that if I delete the log file for another database, I can still attach the database file and it creates a new log file for me.

I am using SQL Server 2000.

Thanks

AlexanderThere is a system stored proc for doing this called Sp_attach_single_file_db.
Though this will only work if the database had previously been detached cleanly. i.e. if it was detached and then the log file was lost you should be ok. If you lost the log file before detaching, your best bet is to restore a backup.|||Ronba,

Thanks fro your reply, I later discovered the same thing that you said. I was trying to attach the single file, but the database was not previously detached cleanly.

Alexander

Attach database from network drive

Hello,

when I attach an database from a network drive in SQL Express 2005 the access permissions on the data and log files will be change.
This ends in a deadlock when the user doesn't have administrator right on the network server, because the permissions will allow access only for administrators and an unknown Usergroup on the network server. I think it's the SQLServer2005MSSQLUser$xxx$MSSQLSERVER group from the local server.

Example:

DBCC TRACEON(1807)
GO
PRINT 'ATTACH FIRST'
CREATE DATABASE TEST ON
( FILENAME = N'\\server\tmp\test.mdf' ),
( FILENAME = N'\\server\tmp\test_log.ldf' )
FOR ATTACH;
GO
PRINT 'Detach'
exec sp_detach_db TEST
GO

When this batch runs again after some minutes you will get

Msg 5120, Level 16, State 101, Line 2

Die physikalische Datei "\\server\tmp\test.mdf" kann nicht ge?ffnet werden. Betriebssystemfehler 5: "5(Zugriff verweigert)".

This could be translated like:

The physical file "\\server\tmp\test.mdf" could not be opend. OS Error 5 (Access Denied).

This error is the same when the user doesn't have full access on the share and db files.

In SQL2000/MSDE no security permissions will be changed, so the database could always be attached.

Is there any workaround?

Perhaps setting the security permissions could switched off?

You also need full access permissions on the share and files, wich also doesn't make sense.

Hi Dieter (thanks for translating ;-)),

you have to make sure that the SERVICE account which is starting up the SQL Server service has the appropiate permissions on the network drive. Unless this can′be accomplished you can′t attach the database via network.

HTH, Jens Suessmeyer.

|||

Hi Jens,

The Service account has permissions on the network drive.

The problem is that SQL Express need full rights because of changing the permissions when attaching the db files.

First time the database could attached, but then the permissions are changed an the database could not attach again.

Dieter Pelz

Attach database fails

Folks, i had a database with data and log file on seperate disks. After reboot, i've lost the log file; now i am trying to attache the data file using EM and QA, i get the following error; plz guide:

sp_attach_single_file_db 'production', 'd:\production_data.mdf'

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'production'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\production_log.LDF' may be incorrect.

Howdy!try this...creat an empty database with that name...then detach it, and then try you attach|||Hi Brett, same error. If i give the logfile for newly created database(as u suggested) i get the error can not associate files from different databases.

Howdy!

Sunday, March 25, 2012

Attach Database

Is there a way to attach a database and leave it in a read-
only mode to restore additional transaction log files just
like restoring from a backup file '
Thanks.You are talking about two different things here. When you attach a database,
the db is going to be online, unlike 'restoring from a backup'.
Could you explain what it is that you are trying to do?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
Is there a way to attach a database and leave it in a read-
only mode to restore additional transaction log files just
like restoring from a backup file '
Thanks.|||Why it is so different. In both cases, the database can be
online. It is much faster to attach a database than
restoring a database from a backup. If the database is
large (More that 50 GB) copying the *.mdf and *.ldf files
to a backup server rather than backing up it may take
shorter time.
>--Original Message--
>You are talking about two different things here. When you
attach a database,
>the db is going to be online, unlike 'restoring from a
backup'.
>Could you explain what it is that you are trying to do?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"David" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
>Is there a way to attach a database and leave it in a
read-
>only mode to restore additional transaction log files just
>like restoring from a backup file '
>Thanks.
>
>.
>|||Like the other post said the restoration and attaching are two different
things. Now if you want to put a database into read-only mode after the
attach you can issue the following T-SQL command:
exec sp_dboption 'yourdb','read only','true'
To turn off read only issue the follwoing:
exec sp_dboption 'yourdb','read only','false'
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
> Is there a way to attach a database and leave it in a read-
> only mode to restore additional transaction log files just
> like restoring from a backup file '
> Thanks.|||What?
If you're attaching a database you detached, how are you confident that you
can accurately apply transaction logs to it to bring it up to date?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fbb301c43e79$757bd070$a501280a@.phx.gbl...
> Why it is so different. In both cases, the database can be
> online. It is much faster to attach a database than
> restoring a database from a backup. If the database is
> large (More that 50 GB) copying the *.mdf and *.ldf files
> to a backup server rather than backing up it may take
> shorter time.
>
> >--Original Message--
> >You are talking about two different things here. When you
> attach a database,
> >the db is going to be online, unlike 'restoring from a
> backup'.
> >
> >Could you explain what it is that you are trying to do?
> >--
> >HTH,
> >Vyas, MVP (SQL Server)
> >http://vyaskn.tripod.com/
> >Is .NET important for a database professional?
> >http://vyaskn.tripod.com/poll.htm
> >
> >
> >"David" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
> >Is there a way to attach a database and leave it in a
> read-
> >only mode to restore additional transaction log files just
> >like restoring from a backup file '
> >
> >Thanks.
> >
> >
> >.
> >|||I am stopping the SQL Server services to copy the *.mdf
and *.ldf file. When I started the services transaction
log backups resumes according to the maintenance plan. SO,
what is so different. What does Stopping and restarting
services changes''/
>--Original Message--
>What?
>If you're attaching a database you detached, how are you
confident that you
>can accurately apply transaction logs to it to bring it
up to date?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"David" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fbb301c43e79$757bd070$a501280a@.phx.gbl...
>> Why it is so different. In both cases, the database can
be
>> online. It is much faster to attach a database than
>> restoring a database from a backup. If the database is
>> large (More that 50 GB) copying the *.mdf and *.ldf
files
>> to a backup server rather than backing up it may take
>> shorter time.
>>
>> >--Original Message--
>> >You are talking about two different things here. When
you
>> attach a database,
>> >the db is going to be online, unlike 'restoring from a
>> backup'.
>> >
>> >Could you explain what it is that you are trying to do?
>> >--
>> >HTH,
>> >Vyas, MVP (SQL Server)
>> >http://vyaskn.tripod.com/
>> >Is .NET important for a database professional?
>> >http://vyaskn.tripod.com/poll.htm
>> >
>> >
>> >"David" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
>> >Is there a way to attach a database and leave it in a
>> read-
>> >only mode to restore additional transaction log files
just
>> >like restoring from a backup file '
>> >
>> >Thanks.
>> >
>> >
>> >.
>> >
>
>.
>|||That's not a proper detach! So now you're assuming that your hack method
will allow you to attach such an MDF file without problems? Have you tested
this scenario? Why would you use this method as opposed to accepted and
proven methods?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fbfa01c43e7c$80b3cb70$a501280a@.phx.gbl...
> I am stopping the SQL Server services to copy the *.mdf
> and *.ldf file. When I started the services transaction
> log backups resumes according to the maintenance plan. SO,
> what is so different. What does Stopping and restarting
> services changes''/
>
> >--Original Message--
> >What?
> >
> >If you're attaching a database you detached, how are you
> confident that you
> >can accurately apply transaction logs to it to bring it
> up to date?
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >
> >
> >"David" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:fbb301c43e79$757bd070$a501280a@.phx.gbl...
> >> Why it is so different. In both cases, the database can
> be
> >> online. It is much faster to attach a database than
> >> restoring a database from a backup. If the database is
> >> large (More that 50 GB) copying the *.mdf and *.ldf
> files
> >> to a backup server rather than backing up it may take
> >> shorter time.
> >>
> >>
> >>
> >> >--Original Message--
> >> >You are talking about two different things here. When
> you
> >> attach a database,
> >> >the db is going to be online, unlike 'restoring from a
> >> backup'.
> >> >
> >> >Could you explain what it is that you are trying to do?
> >> >--
> >> >HTH,
> >> >Vyas, MVP (SQL Server)
> >> >http://vyaskn.tripod.com/
> >> >Is .NET important for a database professional?
> >> >http://vyaskn.tripod.com/poll.htm
> >> >
> >> >
> >> >"David" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
> >> >Is there a way to attach a database and leave it in a
> >> read-
> >> >only mode to restore additional transaction log files
> just
> >> >like restoring from a backup file '
> >> >
> >> >Thanks.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||It is a HACK method to you and not to me. What difference
would it make if I detach it and copy it '? The
question is why the same options (READ_ONLY, SINGLE_USER
e.t.c) that can be applied to backup restore can not be
applied to attach database...........
>--Original Message--
>That's not a proper detach! So now you're assuming that
your hack method
>will allow you to attach such an MDF file without
problems? Have you tested
>this scenario? Why would you use this method as
opposed to accepted and
>proven methods?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"David" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fbfa01c43e7c$80b3cb70$a501280a@.phx.gbl...
>> I am stopping the SQL Server services to copy the *.mdf
>> and *.ldf file. When I started the services transaction
>> log backups resumes according to the maintenance plan.
SO,
>> what is so different. What does Stopping and restarting
>> services changes''/
>>
>> >--Original Message--
>> >What?
>> >
>> >If you're attaching a database you detached, how are
you
>> confident that you
>> >can accurately apply transaction logs to it to bring it
>> up to date?
>> >
>> >--
>> >Aaron Bertrand
>> >SQL Server MVP
>> >http://www.aspfaq.com/
>> >
>> >
>> >
>> >
>> >"David" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:fbb301c43e79$757bd070$a501280a@.phx.gbl...
>> >> Why it is so different. In both cases, the database
can
>> be
>> >> online. It is much faster to attach a database than
>> >> restoring a database from a backup. If the database
is
>> >> large (More that 50 GB) copying the *.mdf and *.ldf
>> files
>> >> to a backup server rather than backing up it may take
>> >> shorter time.
>> >>
>> >>
>> >>
>> >> >--Original Message--
>> >> >You are talking about two different things here.
When
>> you
>> >> attach a database,
>> >> >the db is going to be online, unlike 'restoring
from a
>> >> backup'.
>> >> >
>> >> >Could you explain what it is that you are trying to
do?
>> >> >--
>> >> >HTH,
>> >> >Vyas, MVP (SQL Server)
>> >> >http://vyaskn.tripod.com/
>> >> >Is .NET important for a database professional?
>> >> >http://vyaskn.tripod.com/poll.htm
>> >> >
>> >> >
>> >> >"David" <anonymous@.discussions.microsoft.com> wrote
in
>> >> message
>> >> >news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
>> >> >Is there a way to attach a database and leave it in
a
>> >> read-
>> >> >only mode to restore additional transaction log
files
>> just
>> >> >like restoring from a backup file '
>> >> >
>> >> >Thanks.
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||> What difference would it make if I detach it and copy it '?
Because the system does a proper detach and makes sure the db/log are in
sync and can be attached to other systems flawlessly. If you stop the SQL
Server service, this synchronization is not necessarily going to be done for
you (because this SQL Server knows it can resume any ongoing operations, or
roll them back, when the server comes back online...).
What is the BENEFIT you are deriving from stopping the service and copying
the files (which is, absolutely, a hack) as compared to using generally
accepted methods (restore/backup, detach/attach, etc)?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||To make a long story short,
You cannot do this. In order to restore the transaction logs you have to
first restore the database and then restore all the logs in the proper
order, starting with the first one after teh most recent backup.. You
cannot pick and choose which logs you want to restore. So attaching is a
moot point because you still have to restore the database backup in order
to restore any of the logs..
To answer your question more simply:
SQL Server was not designed to work in the manner in which are suggesting.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Rand voiced my opinion much more succinctly. ;-)|||I did not mean to be harsh on anyone. I am aware of all of
this. It was just an idea I was throwing it may be
possible one day..........
I was looking for a way out from database backup taking 4
hours every night and restoring on the backup server. When
you have a few database on production server that take too
long to backup and restore along with all the other
processes (Nightly loads, data extracts e.t.c) there isn't
enough time to do everything. Plus a cheap management who
don't want to pay for any third party tools.
Thanks everyone.........
>--Original Message--
>To make a long story short,
>You cannot do this. In order to restore the transaction
logs you have to
>first restore the database and then restore all the logs
in the proper
>order, starting with the first one after teh most recent
backup.. You
>cannot pick and choose which logs you want to restore. So
attaching is a
>moot point because you still have to restore the database
backup in order
>to restore any of the logs..
>To answer your question more simply:
>SQL Server was not designed to work in the manner in
which are suggesting.
>Rand
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>|||Log shipping? Transactional replication? Clustering?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fd9401c43ea6$c3862c20$a601280a@.phx.gbl...
> I did not mean to be harsh on anyone. I am aware of all of
> this. It was just an idea I was throwing it may be
> possible one day..........
> I was looking for a way out from database backup taking 4
> hours every night and restoring on the backup server. When
> you have a few database on production server that take too
> long to backup and restore along with all the other
> processes (Nightly loads, data extracts e.t.c) there isn't
> enough time to do everything. Plus a cheap management who
> don't want to pay for any third party tools.
> Thanks everyone.........|||It is SQL Server 2000 Standard. Our manager thinks
Transactional replication is resource intense......
>--Original Message--
>log shipping is the best way and the cheapest way, if you
have sql server enterprise edition
> -- Aaron Bertrand - MVP wrote: --
> Log shipping? Transactional replication?
Clustering?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "David" <anonymous@.discussions.microsoft.com> wrote
in message
> news:fd9401c43ea6$c3862c20$a601280a@.phx.gbl...
> > I did not mean to be harsh on anyone. I am aware
of all of
> > this. It was just an idea I was throwing it may be
> > possible one day..........
> >> I was looking for a way out from database backup
taking 4
> > hours every night and restoring on the backup
server. When
> > you have a few database on production server that
take too
> > long to backup and restore along with all the other
> > processes (Nightly loads, data extracts e.t.c)
there isn't
> > enough time to do everything. Plus a cheap
management who
> > don't want to pay for any third party tools.
> >> Thanks everyone.........
>
>.
>|||> Our manager thinks
Well, that's scary on its own.
However, instead of letting the manager think something, why not test it and
prove it one way or the other? Rejecting an option because of someone's
perception is a huge mistake, and the manager should be the *first* to
recognize that, if he's worth his salt.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Log shipping can be performed with any edition, you just cannot just the built-in stuff in EM and Maint wiz
for it. Search and you will find scripts on the net that does the same.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"roy" <anonymous@.discussions.microsoft.com> wrote in message
news:7F7BB8B6-90B8-471F-91B9-6111E79BF8AE@.microsoft.com...
> log shipping is the best way and the cheapest way, if you have sql server enterprise edition
> -- Aaron Bertrand - MVP wrote: --
> Log shipping? Transactional replication? Clustering?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "David" <anonymous@.discussions.microsoft.com> wrote in message
> news:fd9401c43ea6$c3862c20$a601280a@.phx.gbl...
> > I did not mean to be harsh on anyone. I am aware of all of
> > this. It was just an idea I was throwing it may be
> > possible one day..........
> >> I was looking for a way out from database backup taking 4
> > hours every night and restoring on the backup server. When
> > you have a few database on production server that take too
> > long to backup and restore along with all the other
> > processes (Nightly loads, data extracts e.t.c) there isn't
> > enough time to do everything. Plus a cheap management who
> > don't want to pay for any third party tools.
> >> Thanks everyone.........
>
>

Attach Database

Is there a way to attach a database and leave it in a read-
only mode to restore additional transaction log files just
like restoring from a backup file ?
Thanks.
You are talking about two different things here. When you attach a database,
the db is going to be online, unlike 'restoring from a backup'.
Could you explain what it is that you are trying to do?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
Is there a way to attach a database and leave it in a read-
only mode to restore additional transaction log files just
like restoring from a backup file ?
Thanks.
|||Why it is so different. In both cases, the database can be
online. It is much faster to attach a database than
restoring a database from a backup. If the database is
large (More that 50 GB) copying the *.mdf and *.ldf files
to a backup server rather than backing up it may take
shorter time.

>--Original Message--
>You are talking about two different things here. When you
attach a database,
>the db is going to be online, unlike 'restoring from a
backup'.
>Could you explain what it is that you are trying to do?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"David" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
>Is there a way to attach a database and leave it in a
read-
>only mode to restore additional transaction log files just
>like restoring from a backup file ?
>Thanks.
>
>.
>
|||Like the other post said the restoration and attaching are two different
things. Now if you want to put a database into read-only mode after the
attach you can issue the following T-SQL command:
exec sp_dboption 'yourdb','read only','true'
To turn off read only issue the follwoing:
exec sp_dboption 'yourdb','read only','false'
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
> Is there a way to attach a database and leave it in a read-
> only mode to restore additional transaction log files just
> like restoring from a backup file ?
> Thanks.
|||What?
If you're attaching a database you detached, how are you confident that you
can accurately apply transaction logs to it to bring it up to date?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fbb301c43e79$757bd070$a501280a@.phx.gbl...[vbcol=seagreen]
> Why it is so different. In both cases, the database can be
> online. It is much faster to attach a database than
> restoring a database from a backup. If the database is
> large (More that 50 GB) copying the *.mdf and *.ldf files
> to a backup server rather than backing up it may take
> shorter time.
>
> attach a database,
> backup'.
> message
> read-
|||I am stopping the SQL Server services to copy the *.mdf
and *.ldf file. When I started the services transaction
log backups resumes according to the maintenance plan. SO,
what is so different. What does Stopping and restarting
services changes??/

>--Original Message--
>What?
>If you're attaching a database you detached, how are you
confident that you
>can accurately apply transaction logs to it to bring it
up to date?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"David" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:fbb301c43e79$757bd070$a501280a@.phx.gbl...
be[vbcol=seagreen]
files[vbcol=seagreen]
you[vbcol=seagreen]
just
>
>.
>
|||That's not a proper detach! So now you're assuming that your hack method
will allow you to attach such an MDF file without problems? Have you tested
this scenario? Why would you use this method as opposed to accepted and
proven methods?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fbfa01c43e7c$80b3cb70$a501280a@.phx.gbl...[vbcol=seagreen]
> I am stopping the SQL Server services to copy the *.mdf
> and *.ldf file. When I started the services transaction
> log backups resumes according to the maintenance plan. SO,
> what is so different. What does Stopping and restarting
> services changes??/
>
> confident that you
> up to date?
> message
> be
> files
> you
> just
|||It is a HACK method to you and not to me. What difference
would it make if I detach it and copy it ?? The
question is why the same options (READ_ONLY, SINGLE_USER
e.t.c) that can be applied to backup restore can not be
applied to attach database...........

>--Original Message--
>That's not a proper detach! So now you're assuming that
your hack method
>will allow you to attach such an MDF file without
problems? Have you tested
>this scenario? Why would you use this method as
opposed to accepted and
>proven methods?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"David" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:fbfa01c43e7c$80b3cb70$a501280a@.phx.gbl...
SO,[vbcol=seagreen]
you[vbcol=seagreen]
can[vbcol=seagreen]
is[vbcol=seagreen]
When[vbcol=seagreen]
from a[vbcol=seagreen]
do?[vbcol=seagreen]
in[vbcol=seagreen]
a[vbcol=seagreen]
files
>
>.
>
|||> What difference would it make if I detach it and copy it ??
Because the system does a proper detach and makes sure the db/log are in
sync and can be attached to other systems flawlessly. If you stop the SQL
Server service, this synchronization is not necessarily going to be done for
you (because this SQL Server knows it can resume any ongoing operations, or
roll them back, when the server comes back online...).
What is the BENEFIT you are deriving from stopping the service and copying
the files (which is, absolutely, a hack) as compared to using generally
accepted methods (restore/backup, detach/attach, etc)?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||To make a long story short,
You cannot do this. In order to restore the transaction logs you have to
first restore the database and then restore all the logs in the proper
order, starting with the first one after teh most recent backup.. You
cannot pick and choose which logs you want to restore. So attaching is a
moot point because you still have to restore the database backup in order
to restore any of the logs..
To answer your question more simply:
SQL Server was not designed to work in the manner in which are suggesting.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Attach And Detach Replication

I would like to attach and detach my database due to change the log file
since that logfile already contain too large data.
But the problem occur, It's said that must drop the replication before do
any attach on that data .But i can't drop the replication since i still need
replicate the data from the other server back to local server.
so, anyone can give me any suggestion to do the attach withour drop the
replication?
For a supported solution, you'll need to disable replication before the
detach and attach
(http://msdn2.microsoft.com/en-us/library/ms188769.aspx). Ideally script out
the publication and do a nosync initialization if you can prevent any data
access.
For an unsupported solution that doesn't involve dropping the publication,
search this newsgroup for Ponnu...
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
"sam" wrote:

> I would like to attach and detach my database due to change the log file
> since that logfile already contain too large data.
> But the problem occur, It's said that must drop the replication before do
> any attach on that data .But i can't drop the replication since i still need
> replicate the data from the other server back to local server.
> so, anyone can give me any suggestion to do the attach withour drop the
> replication?
>
sql

Thursday, March 22, 2012

Asynchronous ADO Command execute

I am getting a "Program Error" when I try to run a stored procedure. It says it generates an error log but I don't find it in Windows 2000.
Has anyone seen anything like this?
Please help,
ThanksIt also happens inconsistently. Sometimes, it just runs fine and sometimes, it just gives a Program Error and everything blows out.|||Originally posted by archnam
I am getting a "Program Error" when I try to run a stored procedure. It says it generates an error log but I don't find it in Windows 2000.
Has anyone seen anything like this?
Please help,

Thanks

Have you looked in the SQL Server error log in enterprise manager?|||Where exactly are error logs in the SQL Server under Enterprise Manager?
Also, if I put a breakpoint in my program, it seems to run fine and when I just run it, it bombs, do you think may be I should put some delay in it. The Command.Execute might be taking some time to run.

Sunday, March 11, 2012

Assigning properties to variables

I have a package level Event which runs a stored procedure, to log the error. What I want to do is assign some of the properties of the erroring task to package level variables so that I can use them as parameters.

I can see how to assign properties from variables, but not the other way round. Tongue Tied
Can anyone explain to me how to do this?

Many thanks

RudyHi,

i've done something like this with a Script Task in SSIS.

1. Create some Variables you need in the Variables Window
2. Create a Script Task and put you Readonly and Readwrite Varibales at the
Properties of the Script Task Editor.
3. Use the Design Scritp Button at this Task to define a Script.
4. My Script was designed to create dynamic SQL Statements:

Dim CreateTable, SelectTable, CreateIndex, CreateConstraint, DropConstraint As String

Dim Table As String = CStr(Dts.Variables("TSDES").Value)

Dim SB As New System.Text.StringBuilder(1024)

'Create Table

SB.Append("IF NOT EXISTS (SELECT * FROM sys.tables WHERE type ='U' and name = 'Dim" & CStr(Dts.Variables("TSDES").Value).Trim & "')" & vbNewLine)

SB.Append("BEGIN" & vbNewLine)

SB.Append(" BEGIN Transaction" & vbNewLine)

SB.Append(" CREATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value).Trim & vbNewLine)

SB.Append(" (" & vbNewLine)

SB.Append(" TDTYP numeric(38, 0) NOT NULL," & vbNewLine)

SB.Append(" TDIMX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TDESC nvarchar(30) NOT NULL," & vbNewLine)

SB.Append(" TPDIX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TEMNO numeric(38,0) NOT NULL" & vbNewLine)

SB.Append(" ) ON [PRIMARY]" & vbNewLine)

SB.Append(" COMMIT" & vbNewLine)

SB.Append("End" & vbNewLine)

SB.Append("ELSE" & vbNewLine)

SB.Append(" TRUNCATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value) & vbNewLine)

Dts.Variables("CREATETABLE").Value = SB.ToString

SB.Remove(0, SB.Length)
...

That's it.
I hope this could be helpfull for you.

Kind Regards
Andy L?wen

|||I am doing exactly this with a script task.

When you setup the script task, make sure you set the 'Read/Write Variables' property in the properties dialog, or the script code will fail.

Here is a sample of the script which should give you and idea how to set the value of a package level variable.

Public Sub Main()

Dim VarName As String = ""

Try

Dim varCurrent As Microsoft.SqlServer.Dts.Runtime.Variable

VarName = "User::ObjectName"

varCurrent = Dts.Variables.Item(VarName)

' Set the current value of the Variable
varCurrent.Value = "VTDW_PROD_CMS_AccountInstance"

VarName = "Completed"

Catch Ex As Exception

Dts.TaskResult = Dts.Results.Failure

Dts.Events.FireError(1, "Validate Variables", String.Format("Missing one of the following variables [User::Phase, User::ObjectName, User::ObjectType, User::StepName]. These Variables must be defined. Current Variable ='{0}'", VarName),Nothing, 0)

Return

End Try

' Found all variables. Let the Phase run

Dts.Events.FireInformation(0, "Valor DW DTS", String.Format("All step variables for Phase '{0}' and Step '{1}' have been set.", Phase, StepName), Nothing, 0, Nothing)

Dts.TaskResult = Dts.Results.Success

|||Andy

Can you explain me what your script task exactly does

I'm searching for a script that changes the sql-commands
Thx

Assigning properties to variables

I can see how to assign properties from variables, but not the other way round. Tongue Tied
Can anyone explain to me how to do this?

Many thanks

RudyHi,

i've done something like this with a Script Task in SSIS.

Dim CreateTable, SelectTable, CreateIndex, CreateConstraint, DropConstraint As String

Dim Table As String = CStr(Dts.Variables("TSDES").Value)

Dim SB As New System.Text.StringBuilder(1024)

'Create Table

SB.Append("IF NOT EXISTS (SELECT * FROM sys.tables WHERE type ='U' and name = 'Dim" & CStr(Dts.Variables("TSDES").Value).Trim & "')" & vbNewLine)

SB.Append("BEGIN" & vbNewLine)

SB.Append(" BEGIN Transaction" & vbNewLine)

SB.Append(" CREATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value).Trim & vbNewLine)

SB.Append(" (" & vbNewLine)

SB.Append(" TDTYP numeric(38, 0) NOT NULL," & vbNewLine)

SB.Append(" TDIMX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TDESC nvarchar(30) NOT NULL," & vbNewLine)

SB.Append(" TPDIX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TEMNO numeric(38,0) NOT NULL" & vbNewLine)

SB.Append(" ) ON [PRIMARY]" & vbNewLine)

SB.Append(" COMMIT" & vbNewLine)

SB.Append("End" & vbNewLine)

SB.Append("ELSE" & vbNewLine)

SB.Append(" TRUNCATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value) & vbNewLine)

Dts.Variables("CREATETABLE").Value = SB.ToString

SB.Remove(0, SB.Length)
...

That's it.
I hope this could be helpfull for you.

Kind Regards
Andy L?wen

|||I am doing exactly this with a script task.

When you setup the script task, make sure you set the 'Read/Write Variables' property in the properties dialog, or the script code will fail.

Here is a sample of the script which should give you and idea how to set the value of a package level variable.

Public Sub Main()

Dim VarName As String = ""

Try

Dim varCurrent As Microsoft.SqlServer.Dts.Runtime.Variable

VarName = "User::ObjectName"

varCurrent = Dts.Variables.Item(VarName)

' Set the current value of the Variable
varCurrent.Value = "VTDW_PROD_CMS_AccountInstance"

VarName = "Completed"

Catch Ex As Exception

Dts.TaskResult = Dts.Results.Failure

Return

End Try

' Found all variables. Let the Phase run

Dts.Events.FireInformation(0, "Valor DW DTS", String.Format("All step variables for Phase '{0}' and Step '{1}' have been set.", Phase, StepName), Nothing, 0, Nothing)

Dts.TaskResult = Dts.Results.Success

|||Andy

Can you explain me what your script task exactly does

I'm searching for a script that changes the sql-commands
Thx

Wednesday, March 7, 2012

Assertion errors? Error: 3624, Severity: 20, State: 1

I've had a sudden rush of about 22 assertion errors come out in the SQL
Server log, that stopped the active replication dead in it's tracks. Most of
the other clients and processes seemed unaffected but I can't understand the
source of some of these errors...
- This first one has been identified under kb 828337 BUT that doesn't help
me drill down to the source of the problem! It occurred the most - about 19
times in 4 minutes.
SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>,
line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
- This next one is mentioned in kb 324630 BUT I was not running DBCC
INDEXDEFRAG as suggested and the SQL Server is SP 3.
Location: page.cpp:1668
Expression: pageFull == 0
SPID: 6
Process ID: 2228
- This one seems more interesting since I cannot find any suitable
information about it on-line
Location: page.cpp:2610
Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
SPID: 6
Process ID: 2228
Any help to track down potential causes and or silent problems resulting
from this would be most appreciated - I am unwilling to run DBCC CHECKDB on a
database of this size (~100GB). The server has 13GB free disk space as well
so I can't see it being a problem related to anything so simple!
This really looks like a bug. Open a support incident with MS on this one.
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
"Robert" <MSDNNospam209@.nospam.nospam> wrote in message
news:50F9478D-E5AA-473F-B79C-0C42CD4F16DD@.microsoft.com...
> I've had a sudden rush of about 22 assertion errors come out in the SQL
> Server log, that stopped the active replication dead in it's tracks. Most
> of
> the other clients and processes seemed unaffected but I can't understand
> the
> source of some of these errors...
> - This first one has been identified under kb 828337 BUT that doesn't help
> me drill down to the source of the problem! It occurred the most - about
> 19
> times in 4 minutes.
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>,
> line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> - This next one is mentioned in kb 324630 BUT I was not running DBCC
> INDEXDEFRAG as suggested and the SQL Server is SP 3.
> Location: page.cpp:1668
> Expression: pageFull == 0
> SPID: 6
> Process ID: 2228
> - This one seems more interesting since I cannot find any suitable
> information about it on-line
> Location: page.cpp:2610
> Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
> SPID: 6
> Process ID: 2228
> Any help to track down potential causes and or silent problems resulting
> from this would be most appreciated - I am unwilling to run DBCC CHECKDB
> on a
> database of this size (~100GB). The server has 13GB free disk space as
> well
> so I can't see it being a problem related to anything so simple!
>
|||Hello Robert,
I understand that you encounter many asssertion errors in SQL logs. I'd
like to confirm if you have SQL 2000 SP4 and latest culmulative patch
installed. There is a related known issue was fixed in SP4
841776FIX: Additional diagnostics have been added to SQL Server 2000 to
detect unreported read operation failures
http://support.microsoft.com/default.aspx?scid=kb;EN-US;841776
Please note this issue is most likely caused by hardware or driver related
issues, you may want to enable trace flag 806 as descirbed in 841776 to see
more detailed errors.
Also, as Hilary mentioned, since the issue is related to internal error, if
you cannot solve the issue by above method, to find out the root cause of
this issue we may need to analyze memory dumps, this work has to be done by
contacting Microsoft Product Support Services. Therefore, we probably will
not be able to resolve the issue through the newsgroups. If the issue is
urgent, I recommend that you open a Support incident with Microsoft Product
Support Services so that a dedicated Support Professional can assist with
this case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Please let's know if you ahve any questions or concerns. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Robert,
You may have to run the DBCC CHECKDB afterall..
The KBA Peter has below talks about check that SQL will perform after you
install the fix and turn on the trace flag. And it also appears that you'd
probably take some degree of performance hit with the trace flag
enabled...because every read from the disk will be audited by SQL for
consistency once you turnon the trace flag.
But I'd think if the damage has been done already, in some form or fashion,
you'd want to know about it. You could may be restore the backup of the
database on another server and run CHECKDB there.
Thanks
Emaniel
"Peter Yang [MSFT]" wrote:

> Hello Robert,
> I understand that you encounter many asssertion errors in SQL logs. I'd
> like to confirm if you have SQL 2000 SP4 and latest culmulative patch
> installed. There is a related known issue was fixed in SP4
> 841776FIX: Additional diagnostics have been added to SQL Server 2000 to
> detect unreported read operation failures
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;841776
> Please note this issue is most likely caused by hardware or driver related
> issues, you may want to enable trace flag 806 as descirbed in 841776 to see
> more detailed errors.
> Also, as Hilary mentioned, since the issue is related to internal error, if
> you cannot solve the issue by above method, to find out the root cause of
> this issue we may need to analyze memory dumps, this work has to be done by
> contacting Microsoft Product Support Services. Therefore, we probably will
> not be able to resolve the issue through the newsgroups. If the issue is
> urgent, I recommend that you open a Support incident with Microsoft Product
> Support Services so that a dedicated Support Professional can assist with
> this case. If you need any help in this regard, please let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> Please let's know if you ahve any questions or concerns. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>

Thursday, March 29, 2012

Attach norecovery database

Attach norecovery database

Attach DB Without Transaction Logs

Attach db without transaction log file

attach db with a deleted log

Attach db w/no log file

Attach db w/no log file

Tuesday, March 27, 2012

Attach db failure cause log not primary

Attach database without log file

Attach database from network drive

Attach database fails

Sunday, March 25, 2012

Attach Database

Attach Database

Attach Database

Attach And Detach Replication

Thursday, March 22, 2012

Asynchronous ADO Command execute

Sunday, March 11, 2012

Assigning properties to variables

Assigning properties to variables

Wednesday, March 7, 2012

Assertion errors? Error: 3624, Severity: 20, State: 1

Assertion errors? Error: 3624, Severity: 20, State: 1

Asp.net / sql help

Blog Archive

About Me