Question using Render method

I am using rendermethod to download the report in pdf form.

i use the following:

Dim stream As FileStream = File.OpenWrite(c:\reports\myreport.pdf)

Can you please tell me do i need to save the report on webserver(reportserver hosting server)

or on the client machine who is trying to download or view the report.

what is the method and where shoudl i download. if it is client computer, please help thank you very much for the information.

Displaying the report to the client would be to redirect the response stream to the windows and setting the response header to the right type:

http://support.microsoft.com/default.aspx/kb/875447

Saving would be the same with additional saving the byte[] to a filestream with the method Write() on the the filestream object.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.desql

Question related to Data Import

Hi,

I have a scenario where I either have an option of writing a VB based import routine to import "FORMATTED" data in a text file and populate it in my underlying tables (SQL2K). However someone suggested that I should use DTS.

I am not very familiar with DTS and would therefore appreciate:

1. What would be a better solution - using the VB based import routine that I was planning to have in place or use a DTS?

2. What are the benefits of using a DTS?

3. How easy is it to define/develop a DTS?

4. Aee DTSs flexible?

5. Can someone recommend a URL from where I can read more about DTS and ideally with an example which I can follow to define/develop a DTS myself?

I'll appreciate your reply. Thanks.DTS is the way to go. DTS is very functional in transforming data. Read this link for more info:
http://www.databasejournal.com/features/mssql/article.php/3086891

and also look up DTS in Books Online, and the internet

Hope this helps|||You need access to enterprise manager (to make life easier)

sql server's client side tools...

But why not use BULK INSERT with a Format file? Or bcp

I've seen DTS do wierd things...but that was back in 7.

And if your VB routine was going to do singleton inserts, I would recommend against taht...

Holy Tranny log batman...|||Thanks for your replies. I'll try out the DTS option. I am not clear what you mean by "bcp".

I have one question (for now) related to the DTS option:

If I go with the DTS approach, can I define/develop (I do know what is the correct terminology) a DTS package and then "DEPLOY" at the the user's side so that everytime the user receives a text file with a similar format he can simply "RUN" the DTS package without requiring any help?

My second related question is that if I want to use the DTS deplyment option at the end-user's machine, would that require the Enterprise Manager to be installed at the end-user's machine or can he simply invoke it in some othr manner. I have a feeling (based on my limited knowledge) that the user would require the Enterprise Manager to be installed on his/her machine but I want to confirm.

As you might have figured out, I want to put in place a solution which I can "deploy" and then after a litle bit of training, let the user(s) import the data without my involvement.

I'll appreciate your feedback based on the above.

Thanks.|||Originally posted by Joozh
Thanks for your replies. I'll try out the DTS option. I am not clear what you mean by "bcp".

I have one question (for now) related to the DTS option:

If I go with the DTS approach, can I define/develop (I do know what is the correct terminology) a DTS package and then "DEPLOY" at the the user's side so that everytime the user receives a text file with a similar format he can simply "RUN" the DTS package without requiring any help?

My second related question is that if I want to use the DTS deplyment option at the end-user's machine, would that require the Enterprise Manager to be installed at the end-user's machine or can he simply invoke it in some othr manner. I have a feeling (based on my limited knowledge) that the user would require the Enterprise Manager to be installed on his/her machine but I want to confirm.

As you might have figured out, I want to put in place a solution which I can "deploy" and then after a litle bit of training, let the user(s) import the data without my involvement.

I'll appreciate your feedback based on the above.

Thanks.

Once you create the DTS package, make sure it's portable, ie, it's can be used on virtually any computer. Read the Following article portability of DTS packages, it will give you some idea.

http://www.sqlservercentral.com/columnists/kfeit/portabledtspackages.asp

Secondly, once the DTS package is completed you can schedule a job for the package. This job can run on any server. So you can transform/format files or load the data from files to tables etc. So the user would just see the finished product of the DTS packages.

Hope this helps.

Question regarding sp_detach

Ran this command - EXEC sp_detach_db 'testlog', 'true'
Deleted the .ldf file associated with the database and
then ran this:
EXEC sp_attach_single_file_db @.dbname = 'testlog',
@.physname = 'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\testlog_data.mdf'
Then I got this error message
"Device activation error. The physical file
name 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\testlog_log.ldf' may be incorrect.
New log file 'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\testlog_log.LDF' was created."
Is this an expected error message?
Thanks Mike Allen
Novice DBAMike,
I think the message is more information, letting you know that SQL Server
created a log file for you. Note, however, that BOL states that a new log
file will/can be created only if you actually detached the database first
and only of you have one and only one log file and only one database file.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike Allen" <mikea@.atg.wa.gov> wrote in message
news:1157001c3bf6c$a15d8620$a601280a@.phx.gbl...
> Ran this command - EXEC sp_detach_db 'testlog', 'true'
> Deleted the .ldf file associated with the database and
> then ran this:
> EXEC sp_attach_single_file_db @.dbname = 'testlog',
> @.physname = 'd:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testlog_data.mdf'
> Then I got this error message
> "Device activation error. The physical file
> name 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\testlog_log.ldf' may be incorrect.
> New log file 'd:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testlog_log.LDF' was created."
> Is this an expected error message?
> Thanks Mike Allen
> Novice DBA
>|||Thanks
>--Original Message--
>Mike,
>I think the message is more information, letting you know
that SQL Server
>created a log file for you. Note, however, that BOL
states that a new log
>file will/can be created only if you actually detached
the database first
>and only of you have one and only one log file and only
one database file.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Mike Allen" <mikea@.atg.wa.gov> wrote in message
>news:1157001c3bf6c$a15d8620$a601280a@.phx.gbl...
>> Ran this command - EXEC sp_detach_db 'testlog', 'true'
>> Deleted the .ldf file associated with the database and
>> then ran this:
>> EXEC sp_attach_single_file_db @.dbname = 'testlog',
>> @.physname = 'd:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\testlog_data.mdf'
>> Then I got this error message
>> "Device activation error. The physical file
>> name 'D:\Program Files\Microsoft SQL
>> Server\MSSQL\data\testlog_log.ldf' may be incorrect.
>> New log file 'd:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\testlog_log.LDF' was created."
>> Is this an expected error message?
>> Thanks Mike Allen
>> Novice DBA
>
>.
>sql

Question regarding DataSet file (xsd) like the ones used on 3-teir design tutorial

Hi everyone,

I have a question. I am using a DataSet file as my data layer in my app. I'm trying to create a event TableAdaptor where one of the queries has two paramaters (@.start & @.end). Those two variables are used to search for items that have start dates that fall in between the two.

Every time I try to use this, the smart tab always brings up three paramaters (DataTable, start as date, end as date). How the hell does that DataTable get thrown in there. I executed the query in the builder section and it only asks for the two variables.

Any idea on this one?

Thanks!

A dataset is a collection of datatables|||

That part I knew, I'm just wondering why it's asking for it. I've created numerous tableAdapter and this is the first one that inserted a variable I don't need.

Here is the query I'm using inside the xsd, as you can see I only have two variables (@.start & @.end), so why does it generate a third variable of type DataTable?

========================================================================================

SELECT eventID,
eventTitle,
eventDescription,
eventStartTimeDate,
eventEndTimeDate,
eventCategoryID,
( SELECT eventCategoryName
FROM eventCategories
WHERE eventCategoryID = events.eventCategoryID
) AS eventCategoryName
FROM events
WHERE (eventStartTimeDate >= @.start AND eventStartTimeDate < @.end)
AND active = 1

|||

Hi,

Is the DataTable variable generated for the Fill method of DataTable? If yes, it is by design.

The Fill method needs to know the destination the select data flows to. In this case, you will need to supply an argument of DataTable type.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

question regarding bcp

Hi ,
I am executing the following command from query analyzer. It is
successfull but i couldnot see the output file. I am connecting to the
database server from my workstation through query analyzer.
exec master..xp_cmdshell 'bcp "SELECT * FROM pubs..authors ORDER BY
au_lname" queryout C:\authors.txt -U sa -P xyz -c'
Here is the output,
NULL
Starting copy...
NULL
23 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 16 Avg 0 (1437.50 rows per sec.)
The problem is i dont know where the output file is. Will it be in the
database server?. If so, then how can i save the output file in my
workstation.
Thanks in advance,
Vel.If you run BCP on the server it can only output to a location that it
can access from the server. Either output to a shared folder that can
be seen by both you and SQL's service account OR run BCP locally on
your workstation (from the command line).
David Portas
SQL Server MVP
--|||The command is executed on the server. If you want the file to be created on
the client, you need to
specify that, use an UNC drive for the client. the service account for SQL S
erver need permissions
for the share. I have a feeling that this isn't the best way to achieve this
, though. What about
having your client application create the file? Or what about executing BCP
on the client computer
instead of through xp_cmdshell on the server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"velmj" <velmj@.discussions.microsoft.com> wrote in message
news:A3FC958F-EC28-494F-AFDA-70497218DD69@.microsoft.com...
> Hi ,
> I am executing the following command from query analyzer. It is
> successfull but i couldnot see the output file. I am connecting to the
> database server from my workstation through query analyzer.
>
> exec master..xp_cmdshell 'bcp "SELECT * FROM pubs..authors ORDER BY
> au_lname" queryout C:\authors.txt -U sa -P xyz -c'
> Here is the output,
> NULL
> Starting copy...
> NULL
> 23 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.): total 16 Avg 0 (1437.50 rows per sec.)
> The problem is i dont know where the output file is. Will it be in the
> database server?. If so, then how can i save the output file in my
> workstation.
> Thanks in advance,
> Vel.|||The file should be in the root drive of the server where you saved it.
You might want to save it somewhere to a less "critial" folder than the
root drive. You can copy it across the network once you are done. Or you
can use the command-line BCP program right from your client and have the
final file automatically saved locally.
David Gugick
Imceda Software
www.imceda.com

question on using xp_cmdshell

I am trying to run an exe file like:

EXEC master.dbo.xp_cmdshell '\\Share\sharepoint\folder1\folder2\test.exe "input parameter"'

And executing the above with the input should give me an output.But the input parameter is not recognised. Any help.

Thanks

EXEC xp_cmdshell 'dir *.exe';

try removing the double quotes, above I do a DIR command passing in *.exe

|||Not recognized by whom? The test.exe command? What do you mean by input parameter. Can you post an actual example so we can try it?|||EXEC master.dbo.xp_cmdshell '\\Share\sharepoint\folder1\folder2\test.exe input_parameter'|||Did these replies solved your problem or not?|||

The test.exe is a tool to extract output when an input is given.

EXEC master.dbo.xp_cmdshell '\\Share\sharepoint\folder1\folder2\test.exe input'

if i do like above i should be able to get the output.

Thanks

|||

kast wrote:

The test.exe is a tool to extract output when an input is given.

EXEC master.dbo.xp_cmdshell '\\Share\sharepoint\folder1\folder2\test.exe input'

if i do like above i should be able to get the output.

Thanks

good, then mark a reply as the ANSWER.

Derek

|||

There is one problem still.

On the command prompt

I use as C:\folderA\folderB\test.exe "input" gives me the ouput desired.

When I do same thing in the query analyszer using xp_cmdshell as

EXEC master.dbo.xp_cmdshell 'C:\folderA\folderB\test.exe "input"'

it errors out saying

NULL
Error: c:\folderA\exam.cpp (100):Failed to open the key file
Error: c:\folderA\exam2.cpp (150):Invalid input
NULL

I checked and the relavant cpp files are in that path and also this error doesn't happen when i do in the cmd prompt directly.

Anything wrong with the xp_cmdshell syntax I have used?

Thanks

|||Does the key file exist on the path C:\folderA on the server? Note that the xp_cmdshell runs on the server so you need to copy all the necessary files for the EXEC to the corresponding directories on the server. There is nothing wrong with the xp_cmdshell syntax. The problem is in your environment / configuration.|||

One of two things are happening.

1. The files are not in the location expected

2. You have a security problem

When you execute it directly from a command line, the security context it will be running under is your Windows account and it will also be using the path statement set for your account to locate files.

When you execute this using xp_cmdshell, the security context the command is running under is the Windows account of the SQL Server service and also be using the path statement set for that account. The the SQL Server service account does not have the authority to open the key file this .exe is directly things through, then you are going to get this type of error.

|||

Hello

It seems like you are having a security issue here.

When you are using xp_cmdshell then you will "loose" your security context, and SQL Server will try to create a connection to the share with HIS security context. If you use a "Local Systemaccount" for SQL Server it wont be possible to access any network shares in your domain, since this account is "unknown" to the domain. So either move the file to a harddrive thats local to the SQL Server, or switch the SQL Server to use a domain account (I would prefer moving the file locally, since granting the SQL Server domain access would open attackers entry to your domain if they manage to compromise your SQL Server)

|||

EXEC master.dbo.xp_cmdshell 'C:\folderA\folderB\test.exe "input"'

Instead of doing as above I created a test.bat file as

cd\
cd folderA
cd folderB

Test.exe "input"

and then called it as

EXEC master.dbo.xp_cmdshell Test.bat "input"

This Worked.Thanks everyone for the effort.Thanks again.

|||

you may want to note that XPs are to no longer exist after SQL 2005 via this quote that is everywhere in BOL2005:

"This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CLR Integration instead."

And regardless of xp_cmdShells future I would encourage you to port what your doing into the CLR as it is a much more safer and secure environment than xps. As a matter of fact in my upcoming book we have an entire chapter dedicated to the topic as porting XPs is one of the big reasons to employ the technology.

Derek

|||Thanks for the information.|||Please mark an answer

question on using xp_cmdshell

I am trying to run an exe file like:

EXEC master.dbo.xp_cmdshell '\\Share\sharepoint\folder1\folder2\test.exe "input parameter"'

And executing the above with the input should give me an output.But the input parameter is not recognised. Any help.

Thanks

EXEC xp_cmdshell 'dir *.exe';

try removing the double quotes, above I do a DIR command passing in *.exe

|||Not recognized by whom? The test.exe command? What do you mean by input parameter. Can you post an actual example so we can try it?|||EXEC master.dbo.xp_cmdshell '\\Share\sharepoint\folder1\folder2\test.exe input_parameter'|||Did these replies solved your problem or not?|||

The test.exe is a tool to extract output when an input is given.

EXEC master.dbo.xp_cmdshell '\\Share\sharepoint\folder1\folder2\test.exe input'

if i do like above i should be able to get the output.

Thanks

|||

kast wrote:

The test.exe is a tool to extract output when an input is given.

EXEC master.dbo.xp_cmdshell '\\Share\sharepoint\folder1\folder2\test.exe input'

if i do like above i should be able to get the output.

Thanks

good, then mark a reply as the ANSWER.

Derek

|||

There is one problem still.

On the command prompt

I use as C:\folderA\folderB\test.exe "input" gives me the ouput desired.

When I do same thing in the query analyszer using xp_cmdshell as

EXEC master.dbo.xp_cmdshell 'C:\folderA\folderB\test.exe "input"'

it errors out saying

NULL
Error: c:\folderA\exam.cpp (100):Failed to open the key file
Error: c:\folderA\exam2.cpp (150):Invalid input
NULL

I checked and the relavant cpp files are in that path and also this error doesn't happen when i do in the cmd prompt directly.

Anything wrong with the xp_cmdshell syntax I have used?

Thanks

|||Does the key file exist on the path C:\folderA on the server? Note that the xp_cmdshell runs on the server so you need to copy all the necessary files for the EXEC to the corresponding directories on the server. There is nothing wrong with the xp_cmdshell syntax. The problem is in your environment / configuration.|||

One of two things are happening.

1. The files are not in the location expected

2. You have a security problem

When you execute it directly from a command line, the security context it will be running under is your Windows account and it will also be using the path statement set for your account to locate files.

When you execute this using xp_cmdshell, the security context the command is running under is the Windows account of the SQL Server service and also be using the path statement set for that account. The the SQL Server service account does not have the authority to open the key file this .exe is directly things through, then you are going to get this type of error.

|||

Hello

It seems like you are having a security issue here.

When you are using xp_cmdshell then you will "loose" your security context, and SQL Server will try to create a connection to the share with HIS security context. If you use a "Local Systemaccount" for SQL Server it wont be possible to access any network shares in your domain, since this account is "unknown" to the domain. So either move the file to a harddrive thats local to the SQL Server, or switch the SQL Server to use a domain account (I would prefer moving the file locally, since granting the SQL Server domain access would open attackers entry to your domain if they manage to compromise your SQL Server)

|||

EXEC master.dbo.xp_cmdshell 'C:\folderA\folderB\test.exe "input"'

Instead of doing as above I created a test.bat file as

cd\
cd folderA
cd folderB

Test.exe "input"

and then called it as

EXEC master.dbo.xp_cmdshell Test.bat "input"

This Worked.Thanks everyone for the effort.Thanks again.

|||

you may want to note that XPs are to no longer exist after SQL 2005 via this quote that is everywhere in BOL2005:

"This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CLR Integration instead."

And regardless of xp_cmdShells future I would encourage you to port what your doing into the CLR as it is a much more safer and secure environment than xps. As a matter of fact in my upcoming book we have an entire chapter dedicated to the topic as porting XPs is one of the big reasons to employ the technology.

Derek

|||Thanks for the information.|||Please mark an answer

Question on Transaction Log Backups

New to Sql Server, running SQL Server 2000.

Our transaction log file backups occasionally fail as the size of the
transaction log gets really huge. We'd like to schedule additional
transaction log backups. Does that require an exclusive on the database
or can the db be used during a transaction log backup?

Also, does switching to a bulk mode recovery model before a bulk
operation then switching back to full recovery mode after present any
issues?

Thanks in advance.DataPro wrote:
> New to Sql Server, running SQL Server 2000.
> Our transaction log file backups occasionally fail as the size of the
> transaction log gets really huge. We'd like to schedule additional
> transaction log backups. Does that require an exclusive on the database
> or can the db be used during a transaction log backup?
> Also, does switching to a bulk mode recovery model before a bulk
> operation then switching back to full recovery mode after present any
> issues?

It doesn't make sense to switch back and forth. You can permanently
switch to bulk logged because different logging for bulk operations is
all this recovery model is about.

See Microsoft's documentation for more details:

http://technet2.microsoft.com/Windo...3.mspx?mfr=true

Kind regards

robert|||DataPro (datapro01@.yahoo.com) writes:
> Our transaction log file backups occasionally fail as the size of the
> transaction log gets really huge. We'd like to schedule additional
> transaction log backups. Does that require an exclusive on the database
> or can the db be used during a transaction log backup?

Translog backups can be taken without conflict for other users. Else
it would be a quite meaningless operations. Some sites back up their
transaction log every 15 minutes or so.

> Also, does switching to a bulk mode recovery model before a bulk
> operation then switching back to full recovery mode after present any
> issues?

In difference to Robert, I think this makes sense. I believe we do this
in the maintenance procedure that we ship with our product. We switch
to bulk-logged before starting reindexing of tables, and then switch
back to full when it's done.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
>> Also, does switching to a bulk mode recovery model before a bulk
>> operation then switching back to full recovery mode after present any
>> issues?
> In difference to Robert, I think this makes sense. I believe we do this
> in the maintenance procedure that we ship with our product. We switch
> to bulk-logged before starting reindexing of tables, and then switch
> back to full when it's done.

Hm, I can see that it makes sense if you do this for *some* bulk
operations only. My understanding of the question was whether it makes
sense to do it always for bulk operations. And in that case I don't
think it does: if I switch from "full" to "bulk logged" before any bulk
op and switch back afterwards I get the same behavior as if "bulk
logged" was used all the time.

Kind regards

robert|||Many thanks for the thoughts. Being brand new to SQL Server (coming
over from DB2) have much to read and learn.

Here is my situation. An application upgrade occurred over the weekend
which made several database changes. Now the active log is at 12 Gigs.
Taking a full database backup and a log backup is not truncating the
log. Issuing a backup log truncate only has no effect.

I ussed a dbcc loginfo against the database and it returned 308 rows. I
am assuming that these 'virtual logs' represent active non-committed
transactions.

Is that right?

We are talking to the application vendor asking him why this occured.
In the meantime,,,,

Any thoughts on how I can reduce the size of this log? From what I've
read about shrinkfile it doesn't appear to be relevant here.

Any thoughts or suggestions would be appreciated.|||DataPro (datapro01@.yahoo.com) writes:
> Here is my situation. An application upgrade occurred over the weekend
> which made several database changes. Now the active log is at 12 Gigs.
> Taking a full database backup and a log backup is not truncating the
> log. Issuing a backup log truncate only has no effect.

It has, but that will not shrink the log file. (Unless autoshrink is in
effect, but you don't want that.) Only give you a lot of free space in
the file.

Note that TRUNCATE_ONLY invalidates the transaction log, so you need to
take a full backup after this operation.

> Any thoughts on how I can reduce the size of this log? From what I've
> read about shrinkfile it doesn't appear to be relevant here.

DBCC SHRINKFILE is indeed your guy. Be sure to set a target size when
you run it, so you don't make the log too small. In that case, it will
have to grow again, and that will only take performance from the system.
And the file may become fragmented on disk.

For things like an application upgrade, the best strategy may be to set
the recovery mode to simple, and set back to full recovery when it the
upgrade is done. Again, you need to take a full backup in this situation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Here is what the vendor is recommending:

1. Back up db and log via Windows Explorer or normal db backups
2. Stop services (application...EAS)
3. Disconnect/detach database
4. Delete log via Windows Explorer
5. Reconnect/reattach database....log will dynamically be allocated

Does this make sense?
They also want autoshrink turned on but its my understanding some SQL
DBA's don't want that. Your thoughts?

Thanks in advance.|||Well SQL Server definetely seems to be a different animal.

Here are some questions a newguy like myself would have about the
logging.

The documentation says that once a backup log truncate only is done, a
full backup should be taken as the recovery ability of the system has
been degraded. Why is that, if that command merely archives committed
transactions?

A SELECT @.@.TRANCOUNT yields a count of zero.
dbcc opentran(eas) says zero also.
dbcc loginfo yeilds 308 rows marked with a '2' which apparently implies
that they are open transactions. Why don't they show up in trancount
then?

If a log backup supposedly makes the log 'reusable' then why would a 12
gig log grow another Gig right within a few hours of the backup with
minimal database use?
Wouldn't most of that space be reusable.

Please straighten me out on my thinking :)

Thanks|||We shrink the transaction log every two hours without any noticeable
performance hit, but our total database size is less than 500 MB. I
have played with the simple logging one time and had problems with it,
so we have been sticking with our two hour shrink process (added to our
two hour backup process) which works fine for now.

DBCC SHRINKFILE(DBName_log, 1)
BACKUP LOG DBName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(DBName_log, 1)
go|||pb648174 (google@.webpaul.net) writes:
> We shrink the transaction log every two hours without any noticeable
> performance hit, but our total database size is less than 500 MB. I
> have played with the simple logging one time and had problems with it,
> so we have been sticking with our two hour shrink process (added to our
> two hour backup process) which works fine for now.
> DBCC SHRINKFILE(DBName_log, 1)
> BACKUP LOG DBName WITH TRUNCATE_ONLY
> DBCC SHRINKFILE(DBName_log, 1)

This appears completely pointless to me. If you have full recovery,
you are losing all the benefits by truncating the transaction log
every two hours. If you have simple recovery, there should not be
any need to shrink the file, unless there has been any extraordinary
actions going on.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||DataPro (datapro01@.yahoo.com) writes:
> Here is what the vendor is recommending:
> 1. Back up db and log via Windows Explorer or normal db backups
> 2. Stop services (application...EAS)
> 3. Disconnect/detach database
> 4. Delete log via Windows Explorer
> 5. Reconnect/reattach database....log will dynamically be allocated
> Does this make sense?

No! Never delete a log file, unless you are told so by a Microsoft
support professional. There are so many people who have lost their
database, because they deleted the log. I guess it's safe if you
detach first, but I would like to take a gamble with a production
database.

> They also want autoshrink turned on but its my understanding some SQL
> DBA's don't want that. Your thoughts?

Shrinking a database file only make sense if there has been some
exceptional activity. Running a schema upgrade certainly counts as one.
But doing it as a matter of routine, or with autoshrink, only serves
to degrade the performance of the application in three different
ways:

1) The shrinking takes resources.
2) The autogrow takes resources.
3) The log file becomes fragmented on disk.

Autogrow is a mixed blessing. Up to SQL Server 6.5 there was no autogrow,
which meant that if the data or log segment was filled up, it was a
hard stop. Autogrow that was added in SQL 7 avoids these situations,
as long as the disk has space. However, for the DBA there is not
really any difference: he should should still monitor database size
amd increase the size in due time. A 10% autogrow in a 200 GB database
is very painfully noticeable fot the users.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||DataPro (datapro01@.yahoo.com) writes:
> Well SQL Server definetely seems to be a different animal.
> Here are some questions a newguy like myself would have about the
> logging.
> The documentation says that once a backup log truncate only is done, a
> full backup should be taken as the recovery ability of the system has
> been degraded. Why is that, if that command merely archives committed
> transactions?

Up-to-the point recovery. You take a full backup at midnight, then
you backup the transaction log once an hour. at 10:23 you run an
UPDATE statement, but forget the WHERE clause. At this point you
can back up the transaction log.

You can the restore the full backup and reapply the transaction log
dumps but stop at 10:22, so you can recover the lost data. If you
somewhere along the line has backed up the transaction log with
NO_LOG or TRUNCATE_ONLY, the log chain is broken, and it's not
possible to continue to apply logs.

> A SELECT @.@.TRANCOUNT yields a count of zero.
> dbcc opentran(eas) says zero also.
> dbcc loginfo yeilds 308 rows marked with a '2' which apparently implies
> that they are open transactions. Why don't they show up in trancount
> then?
> If a log backup supposedly makes the log 'reusable' then why would a 12
> gig log grow another Gig right within a few hours of the backup with
> minimal database use?
> Wouldn't most of that space be reusable.

I'm not really sure what you are talking about here. If the log file
grew, apparently there was some action going on in the database.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your replies.
In regards to:

"> A SELECT @.@.TRANCOUNT yields a count of zero.
> dbcc opentran(eas) says zero also.
> dbcc loginfo yeilds 308 rows marked with a '2' which apparently implies
> that they are open transactions. Why don't they show up in trancount
> then?

> If a log backup supposedly makes the log 'reusable' then why would a 12
> gig log grow another Gig right within a few hours of the backup with
> minimal database use?
> Wouldn't most of that space be reusable.

I'm not really sure what you are talking about here. If the log file
grew, apparently there was some action going on in the database. "

1.) What I'm not clear on is this...a dbcc loginfo yeilds 308 rows
marked with a 2.
Are those 'active' uncommitted transactions? What do those rows
represent?

2.) If backing up a log makes the space within a log file 'reusable'
why did the log grow in size if the space in the log was made reusable?
Wouldn't the minimal activity I referenced use that reclaimed space
within the log instead of making it bigger?

3) And finally what is the relationship between the results of a dbcc
loginfo command, a dbcc opentran and a A SELECT @.@.TRANCOUNT|||DataPro (datapro01@.yahoo.com) writes:
> 1.) What I'm not clear on is this...a dbcc loginfo yeilds 308 rows
> marked with a 2.
> Are those 'active' uncommitted transactions? What do those rows
> represent?

DBCC LOGINFO is not documented, but I learn a little from SQL Server
MVP Tibor Karasiz's article on shrinking:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp. (I recommend
that you read this article in whole, although I summarise some details
below.)

Each row represents a VLF, a virtual log file, and 2 indicates that
that VLF is in use. You cannot shrink the log past the last active
VLF. And if all VLF's have a 2, then all VLFs are in use, and obviously
nothing can be shrunk.

You should backup the transaction log, either to disk or just toss it
with TRUNCATE_ONLY.

> 2.) If backing up a log makes the space within a log file 'reusable'
> why did the log grow in size if the space in the log was made reusable?

As I discussed above, the log is apparently not reusable.

> 3) And finally what is the relationship between the results of a dbcc
> loginfo command, a dbcc opentran and a A SELECT @.@.TRANCOUNT

@.@.trancount only reflects the state of the current process.

DBCC OPENTRAN reveals if there are any open transactions. The log
cannot be truncated past the oldest active transaction.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks much|||This has been a really usefull thread. I have the same problem, and
debated about starting new thread or continue this one:
DBCC LOGINFO shows my last log entry is in use (as indicated by STATUS
of 2)
DBCC OPENTRAN indicates no active open transactions
My file is 89% empty, Ive backed up the log files repeatedly (first one
was large 40+ GB, now they are < 500K)
I've tried taking the DB offline and back online. The last log entry
still shows in use and I cant shrink.
I read the article, which helps identify why I cant shrink (log file in
use) but now I'm stumped.
TIA
Rob|||rcamarda (robc390@.hotmail.com) writes:
> This has been a really usefull thread. I have the same problem, and
> debated about starting new thread or continue this one:
> DBCC LOGINFO shows my last log entry is in use (as indicated by STATUS
> of 2)
> DBCC OPENTRAN indicates no active open transactions
> My file is 89% empty, Ive backed up the log files repeatedly (first one
> was large 40+ GB, now they are < 500K)
> I've tried taking the DB offline and back online. The last log entry
> still shows in use and I cant shrink.
> I read the article, which helps identify why I cant shrink (log file in
> use) but now I'm stumped.

Is the last VLF the only that has status = 2? Or are there several?

Did you try running BACKUP TRANSACTION WITH NO_LOG? (Since you lose the
log chain, you need a full backup as well.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Question on SQL job step invoking .cmd

I have a simple SQL job that is scheduled to invoke a .cmd to perform a
file copy task.
How can I get SQL job to report to correct status when the copy task
fails, such as due to connectivity issue, or file not found, and etc?
thanks.One option is to capture the return code and do a raiserror
if the return code <> 0. Along the lines of:
DECLARE @.ret int
EXEC @.ret = master..xp_cmdshell 'copy ...etc' , NO_OUTPUT
IF @.ret <> 0 RAISERROR('File copy error',16, 1)
-Sue
On 28 Jul 2006 08:23:49 -0700, "Rose" <rose.say@.gmail.com>
wrote:
>I have a simple SQL job that is scheduled to invoke a .cmd to perform a
>file copy task.
>How can I get SQL job to report to correct status when the copy task
>fails, such as due to connectivity issue, or file not found, and etc?
>thanks.sql

Question on shrinkingTransaction Log file

Hi All,
For some unknown reason one of our SQL Server 2000
database transaction log file grew to 25 GB before we
noticed it.
Thank god we noticed it before we ran out of space
completely.
Currently, we are left with 1 GB of free space and
the Data file size of the database is about 1.5 GB.
I looked over the help documentation on Books Online
trying to figure out how to reduce the size of the
transaction log file.
I read that i need to truncate the transaction log file
before I could shrink the size of the transaction log
file.
I read that the "active" portion of the transaction log
file can never be truncated.
How would I figure out what portion of this 25 GB
transaction log file is active?
What if all of it is, then how can I shrink the size of
the file?
At this point I MUST shrink the transaction log file,
and later try to trace what caused it to grow so big.
Please advise the best way I can shrink the size of the
transaction log file to the initial size we originally
had allocated, 100 MB.
Thank you,
Mitra
First, make sure that you are either backing up your database logs, or have the database set to simple mode.
You can most easily check to see the status of the database logs in enterprise manager by highlighting the database name and then selecting taskpad under the view menu. This will provide you with a graphical view of the size and of how much of the log is
full.
If it is full, switch to simple recovery under Database Properties in SQL Enterprise manager, and then choose All Tasks -> shrink database checking the shrink file option with the correct file chosen from the drop down menu on the top. (you may have to d
o this twice)
|||Hi,
After Transaction log backup the physical file will not shrink automatically
if the "Auto shrink" option is enabled. Please do not
enable this option because this will affect your OLTP processng because the
Auto shrink will take more I/O.
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
MCDBA
"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
news:130cf01c4437a$fc073b60$a001280a@.phx.gbl...
> Hi All,
> For some unknown reason one of our SQL Server 2000
> database transaction log file grew to 25 GB before we
> noticed it.
> Thank god we noticed it before we ran out of space
> completely.
> Currently, we are left with 1 GB of free space and
> the Data file size of the database is about 1.5 GB.
> I looked over the help documentation on Books Online
> trying to figure out how to reduce the size of the
> transaction log file.
> I read that i need to truncate the transaction log file
> before I could shrink the size of the transaction log
> file.
> I read that the "active" portion of the transaction log
> file can never be truncated.
> How would I figure out what portion of this 25 GB
> transaction log file is active?
> What if all of it is, then how can I shrink the size of
> the file?
> At this point I MUST shrink the transaction log file,
> and later try to trace what caused it to grow so big.
> Please advise the best way I can shrink the size of the
> transaction log file to the initial size we originally
> had allocated, 100 MB.
> Thank you,
> Mitra
>
|||Hi,
I am not clear about what would happen when i try to back
up a transaction log that is 25GB and I've got only 1 GB
free disk space.
Are you saying that if i run the statement you wrote:
backup log <dbname> with truncate_only
I should be okay and don't need to worry about how much
free space is available?
Thank you,
Mitra

>--Original Message--
>Hi,
>After Transaction log backup the physical file will not
shrink automatically
>if the "Auto shrink" option is enabled. Please do not
>enable this option because this will affect your OLTP
processng because the
>Auto shrink will take more I/O.
>How to reduce the Transaction log size
>1. Backup the transaction log (Use BACKUP Log dbname to
>disk='c:\backup\dbname.tr1' or use enterprise manager)
>or (Since you done have the hard disk space truncate the
trasaction log)
>backup log <dbname> with truncate_only
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>Have a look into the below link.
>Shrinking the Transaction Log in SQL Server 2000 with
DBCC
>http://www.support.microsoft.com/?id=272318
>
>Thanks
>Hari
>MCDBA
>
>
>
>"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
>news:130cf01c4437a$fc073b60$a001280a@.phx.gbl...
>
>.
>

Question on shrinkingTransaction Log file

Hi All,
For some unknown reason one of our SQL Server 2000
database transaction log file grew to 25 GB before we
noticed it.
Thank god we noticed it before we ran out of space
completely.
Currently, we are left with 1 GB of free space and
the Data file size of the database is about 1.5 GB.
I looked over the help documentation on Books Online
trying to figure out how to reduce the size of the
transaction log file.
I read that i need to truncate the transaction log file
before I could shrink the size of the transaction log
file.
I read that the "active" portion of the transaction log
file can never be truncated.
How would I figure out what portion of this 25 GB
transaction log file is active?
What if all of it is, then how can I shrink the size of
the file?
At this point I MUST shrink the transaction log file,
and later try to trace what caused it to grow so big.
Please advise the best way I can shrink the size of the
transaction log file to the initial size we originally
had allocated, 100 MB.
Thank you,
MitraFirst, make sure that you are either backing up your database logs, or have
the database set to simple mode.
You can most easily check to see the status of the database logs in enterpri
se manager by highlighting the database name and then selecting taskpad unde
r the view menu. This will provide you with a graphical view of the size an
d of how much of the log is
full.
If it is full, switch to simple recovery under Database Properties in SQL En
terprise manager, and then choose All Tasks -> shrink database checking the
shrink file option with the correct file chosen from the drop down menu on t
he top. (you may have to d
o this twice)|||Hi,
After Transaction log backup the physical file will not shrink automatically
if the "Auto shrink" option is enabled. Please do not
enable this option because this will affect your OLTP processng because the
Auto shrink will take more I/O.
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
MCDBA
"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
news:130cf01c4437a$fc073b60$a001280a@.phx
.gbl...
> Hi All,
> For some unknown reason one of our SQL Server 2000
> database transaction log file grew to 25 GB before we
> noticed it.
> Thank god we noticed it before we ran out of space
> completely.
> Currently, we are left with 1 GB of free space and
> the Data file size of the database is about 1.5 GB.
> I looked over the help documentation on Books Online
> trying to figure out how to reduce the size of the
> transaction log file.
> I read that i need to truncate the transaction log file
> before I could shrink the size of the transaction log
> file.
> I read that the "active" portion of the transaction log
> file can never be truncated.
> How would I figure out what portion of this 25 GB
> transaction log file is active?
> What if all of it is, then how can I shrink the size of
> the file?
> At this point I MUST shrink the transaction log file,
> and later try to trace what caused it to grow so big.
> Please advise the best way I can shrink the size of the
> transaction log file to the initial size we originally
> had allocated, 100 MB.
> Thank you,
> Mitra
>|||Hi,
I am not clear about what would happen when i try to back
up a transaction log that is 25GB and I've got only 1 GB
free disk space.
Are you saying that if i run the statement you wrote:
backup log <dbname> with truncate_only
I should be okay and don't need to worry about how much
free space is available?
Thank you,
Mitra

>--Original Message--
>Hi,
>After Transaction log backup the physical file will not
shrink automatically
>if the "Auto shrink" option is enabled. Please do not
>enable this option because this will affect your OLTP
processng because the
>Auto shrink will take more I/O.
>How to reduce the Transaction log size
>1. Backup the transaction log (Use BACKUP Log dbname to
>disk='c:\backup\dbname.tr1' or use enterprise manager)
>or (Since you done have the hard disk space truncate the
trasaction log)
>backup log <dbname> with truncate_only
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>Have a look into the below link.
>Shrinking the Transaction Log in SQL Server 2000 with
DBCC
>http://www.support.microsoft.com/?id=272318
>
>Thanks
>Hari
>MCDBA
>
>
>
>"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
> news:130cf01c4437a$fc073b60$a001280a@.phx
.gbl...
>
>.
>

Question on shrinkingTransaction Log file

Hi All,
For some unknown reason one of our SQL Server 2000
database transaction log file grew to 25 GB before we
noticed it.
Thank god we noticed it before we ran out of space
completely.
Currently, we are left with 1 GB of free space and
the Data file size of the database is about 1.5 GB.
I looked over the help documentation on Books Online
trying to figure out how to reduce the size of the
transaction log file.
I read that i need to truncate the transaction log file
before I could shrink the size of the transaction log
file.
I read that the "active" portion of the transaction log
file can never be truncated.
How would I figure out what portion of this 25 GB
transaction log file is active?
What if all of it is, then how can I shrink the size of
the file?
At this point I MUST shrink the transaction log file,
and later try to trace what caused it to grow so big.
Please advise the best way I can shrink the size of the
transaction log file to the initial size we originally
had allocated, 100 MB.
Thank you,
MitraFirst, make sure that you are either backing up your database logs, or have the database set to simple mode
You can most easily check to see the status of the database logs in enterprise manager by highlighting the database name and then selecting taskpad under the view menu. This will provide you with a graphical view of the size and of how much of the log is full
If it is full, switch to simple recovery under Database Properties in SQL Enterprise manager, and then choose All Tasks -> shrink database checking the shrink file option with the correct file chosen from the drop down menu on the top. (you may have to do this twice)|||Hi,
After Transaction log backup the physical file will not shrink automatically
if the "Auto shrink" option is enabled. Please do not
enable this option because this will affect your OLTP processng because the
Auto shrink will take more I/O.
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
MCDBA
"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
news:130cf01c4437a$fc073b60$a001280a@.phx.gbl...
> Hi All,
> For some unknown reason one of our SQL Server 2000
> database transaction log file grew to 25 GB before we
> noticed it.
> Thank god we noticed it before we ran out of space
> completely.
> Currently, we are left with 1 GB of free space and
> the Data file size of the database is about 1.5 GB.
> I looked over the help documentation on Books Online
> trying to figure out how to reduce the size of the
> transaction log file.
> I read that i need to truncate the transaction log file
> before I could shrink the size of the transaction log
> file.
> I read that the "active" portion of the transaction log
> file can never be truncated.
> How would I figure out what portion of this 25 GB
> transaction log file is active?
> What if all of it is, then how can I shrink the size of
> the file?
> At this point I MUST shrink the transaction log file,
> and later try to trace what caused it to grow so big.
> Please advise the best way I can shrink the size of the
> transaction log file to the initial size we originally
> had allocated, 100 MB.
> Thank you,
> Mitra
>|||Hi,
I am not clear about what would happen when i try to back
up a transaction log that is 25GB and I've got only 1 GB
free disk space.
Are you saying that if i run the statement you wrote:
backup log <dbname> with truncate_only
I should be okay and don't need to worry about how much
free space is available?
Thank you,
Mitra
>--Original Message--
>Hi,
>After Transaction log backup the physical file will not
shrink automatically
>if the "Auto shrink" option is enabled. Please do not
>enable this option because this will affect your OLTP
processng because the
>Auto shrink will take more I/O.
>How to reduce the Transaction log size
>1. Backup the transaction log (Use BACKUP Log dbname to
>disk='c:\backup\dbname.tr1' or use enterprise manager)
>or (Since you done have the hard disk space truncate the
trasaction log)
>backup log <dbname> with truncate_only
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>Have a look into the below link.
>Shrinking the Transaction Log in SQL Server 2000 with
DBCC
>http://www.support.microsoft.com/?id=272318
>
>Thanks
>Hari
>MCDBA
>
>
>
>"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
>news:130cf01c4437a$fc073b60$a001280a@.phx.gbl...
>> Hi All,
>> For some unknown reason one of our SQL Server 2000
>> database transaction log file grew to 25 GB before we
>> noticed it.
>> Thank god we noticed it before we ran out of space
>> completely.
>> Currently, we are left with 1 GB of free space and
>> the Data file size of the database is about 1.5 GB.
>> I looked over the help documentation on Books Online
>> trying to figure out how to reduce the size of the
>> transaction log file.
>> I read that i need to truncate the transaction log file
>> before I could shrink the size of the transaction log
>> file.
>> I read that the "active" portion of the transaction log
>> file can never be truncated.
>> How would I figure out what portion of this 25 GB
>> transaction log file is active?
>> What if all of it is, then how can I shrink the size of
>> the file?
>> At this point I MUST shrink the transaction log file,
>> and later try to trace what caused it to grow so big.
>> Please advise the best way I can shrink the size of the
>> transaction log file to the initial size we originally
>> had allocated, 100 MB.
>> Thank you,
>> Mitra
>>
>
>.
>

question on recovery sql server 2000

Hi
I am using SQL 2000 std edition and hence using custom scripts to
restore the DB using standby file. I am backing up T-LOGs every 30 mins on
production and copying them on a network location which is accessible to
standby machine.
I run a job on standby machine which applies the transaction logs every 30
mins by checking in backmediaset & backupmediafamily tables of Production. DB
is running in read only and is working.
Question : In case my Production machine goes down and does not come up how
can I start the Standby "with data loss" if it has already appiled the last
T-logs generated on production.
Since Produciton (primary) is down I can not backup the last T-log.
Thanks
Mangesh
From BOL
This example sets up the MyNwind database on a standby server. The database
can be used in read-only mode between restore operations.
-- Restore the initial database backup on the standby server.
USE master
GO
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH STANDBY = 'c:\undo.ldf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH STANDBY = 'c:\undo.ldf'
GO
-- Apply the next transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH STANDBY = 'c:\undo.ldf'
GO
-- Repeat for each transaction log backup created on the
-- primary server.
-- Time elapses.. .. ..
-- The primary server has failed. Back up the
-- active transaction log on the primary server.
BACKUP LOG MyNwind
TO MyNwind_log3
WITH NO_TRUNCATE
GO
-- Apply the final (active) transaction log backup
-- to the standby server. All preceding transaction
-- log backups must have been already applied.
RESTORE LOG MyNwind
FROM MyNwind_log3
WITH STANDBY = 'c:\undo.ldf'
GO
-- Recover the database on the standby server,
-- making it available for normal operations.
RESTORE DATABASE MyNwind
WITH RECOVERY
GO
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:72D84926-67E3-4A3F-A011-E5F5A6ACA8CC@.microsoft.com...
> Hi
> I am using SQL 2000 std edition and hence using custom scripts to
> restore the DB using standby file. I am backing up T-LOGs every 30 mins on
> production and copying them on a network location which is accessible to
> standby machine.
> I run a job on standby machine which applies the transaction logs every 30
> mins by checking in backmediaset & backupmediafamily tables of Production.
DB
> is running in read only and is working.
> Question : In case my Production machine goes down and does not come up
how
> can I start the Standby "with data loss" if it has already appiled the
last
> T-logs generated on production.
> Since Produciton (primary) is down I can not backup the last T-log.
> Thanks
> Mangesh
>
|||So you are continuously doing RESTORE WITH STANDBY? And you now wand to make the database fully
accessible? If so:
RESTORE DATABASE dbname WITH RECOVERY
No actual restore is performed, only the recovery work to get it out of standby mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
news:72D84926-67E3-4A3F-A011-E5F5A6ACA8CC@.microsoft.com...
> Hi
> I am using SQL 2000 std edition and hence using custom scripts to
> restore the DB using standby file. I am backing up T-LOGs every 30 mins on
> production and copying them on a network location which is accessible to
> standby machine.
> I run a job on standby machine which applies the transaction logs every 30
> mins by checking in backmediaset & backupmediafamily tables of Production. DB
> is running in read only and is working.
> Question : In case my Production machine goes down and does not come up how
> can I start the Standby "with data loss" if it has already appiled the last
> T-logs generated on production.
> Since Produciton (primary) is down I can not backup the last T-log.
> Thanks
> Mangesh
>
|||Yes. I am doing a continuous restoration of my standby database using standby
file.
Thanks for help.
"Tibor Karaszi" wrote:

> So you are continuously doing RESTORE WITH STANDBY? And you now wand to make the database fully
> accessible? If so:
> RESTORE DATABASE dbname WITH RECOVERY
> No actual restore is performed, only the recovery work to get it out of standby mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
> news:72D84926-67E3-4A3F-A011-E5F5A6ACA8CC@.microsoft.com...
>
>

question on recovery sql server 2000

Hi
I am using SQL 2000 std edition and hence using custom scripts to
restore the DB using standby file. I am backing up T-LOGs every 30 mins on
production and copying them on a network location which is accessible to
standby machine.
I run a job on standby machine which applies the transaction logs every 30
mins by checking in backmediaset & backupmediafamily tables of Production. D
B
is running in read only and is working.
Question : In case my Production machine goes down and does not come up how
can I start the Standby "with data loss" if it has already appiled the last
T-logs generated on production.
Since Produciton (primary) is down I can not backup the last T-log.
Thanks
MangeshFrom BOL
This example sets up the MyNwind database on a standby server. The database
can be used in read-only mode between restore operations.
-- Restore the initial database backup on the standby server.
USE master
GO
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH STANDBY = 'c:\undo.ldf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH STANDBY = 'c:\undo.ldf'
GO
-- Apply the next transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH STANDBY = 'c:\undo.ldf'
GO
-- Repeat for each transaction log backup created on the
-- primary server.
--
-- Time elapses.. .. ..
--
-- The primary server has failed. Back up the
-- active transaction log on the primary server.
BACKUP LOG MyNwind
TO MyNwind_log3
WITH NO_TRUNCATE
GO
-- Apply the final (active) transaction log backup
-- to the standby server. All preceding transaction
-- log backups must have been already applied.
RESTORE LOG MyNwind
FROM MyNwind_log3
WITH STANDBY = 'c:\undo.ldf'
GO
-- Recover the database on the standby server,
-- making it available for normal operations.
RESTORE DATABASE MyNwind
WITH RECOVERY
GO
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:72D84926-67E3-4A3F-A011-E5F5A6ACA8CC@.microsoft.com...
> Hi
> I am using SQL 2000 std edition and hence using custom scripts to
> restore the DB using standby file. I am backing up T-LOGs every 30 mins on
> production and copying them on a network location which is accessible to
> standby machine.
> I run a job on standby machine which applies the transaction logs every 30
> mins by checking in backmediaset & backupmediafamily tables of Production.
DB
> is running in read only and is working.
> Question : In case my Production machine goes down and does not come up
how
> can I start the Standby "with data loss" if it has already appiled the
last
> T-logs generated on production.
> Since Produciton (primary) is down I can not backup the last T-log.
> Thanks
> Mangesh
>|||So you are continuously doing RESTORE WITH STANDBY? And you now wand to make
the database fully
accessible? If so:
RESTORE DATABASE dbname WITH RECOVERY
No actual restore is performed, only the recovery work to get it out of stan
dby mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in me
ssage
news:72D84926-67E3-4A3F-A011-E5F5A6ACA8CC@.microsoft.com...
> Hi
> I am using SQL 2000 std edition and hence using custom scripts to
> restore the DB using standby file. I am backing up T-LOGs every 30 mins on
> production and copying them on a network location which is accessible to
> standby machine.
> I run a job on standby machine which applies the transaction logs every 30
> mins by checking in backmediaset & backupmediafamily tables of Production.
DB
> is running in read only and is working.
> Question : In case my Production machine goes down and does not come up h
ow
> can I start the Standby "with data loss" if it has already appiled the las
t
> T-logs generated on production.
> Since Produciton (primary) is down I can not backup the last T-log.
> Thanks
> Mangesh
>|||Yes. I am doing a continuous restoration of my standby database using standb
y
file.
Thanks for help.
"Tibor Karaszi" wrote:

> So you are continuously doing RESTORE WITH STANDBY? And you now wand to ma
ke the database fully
> accessible? If so:
> RESTORE DATABASE dbname WITH RECOVERY
> No actual restore is performed, only the recovery work to get it out of st
andby mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message
> news:72D84926-67E3-4A3F-A011-E5F5A6ACA8CC@.microsoft.com...
>
>

question on recovery sql server 2000

Hi
I am using SQL 2000 std edition and hence using custom scripts to
restore the DB using standby file. I am backing up T-LOGs every 30 mins on
production and copying them on a network location which is accessible to
standby machine.
I run a job on standby machine which applies the transaction logs every 30
mins by checking in backmediaset & backupmediafamily tables of Production. DB
is running in read only and is working.
Question : In case my Production machine goes down and does not come up how
can I start the Standby "with data loss" if it has already appiled the last
T-logs generated on production.
Since Produciton (primary) is down I can not backup the last T-log.
Thanks
MangeshFrom BOL
This example sets up the MyNwind database on a standby server. The database
can be used in read-only mode between restore operations.
-- Restore the initial database backup on the standby server.
USE master
GO
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH STANDBY = 'c:\undo.ldf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH STANDBY = 'c:\undo.ldf'
GO
-- Apply the next transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH STANDBY = 'c:\undo.ldf'
GO
-- Repeat for each transaction log backup created on the
-- primary server.
--
-- Time elapses.. .. ..
--
-- The primary server has failed. Back up the
-- active transaction log on the primary server.
BACKUP LOG MyNwind
TO MyNwind_log3
WITH NO_TRUNCATE
GO
-- Apply the final (active) transaction log backup
-- to the standby server. All preceding transaction
-- log backups must have been already applied.
RESTORE LOG MyNwind
FROM MyNwind_log3
WITH STANDBY = 'c:\undo.ldf'
GO
-- Recover the database on the standby server,
-- making it available for normal operations.
RESTORE DATABASE MyNwind
WITH RECOVERY
GO
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:72D84926-67E3-4A3F-A011-E5F5A6ACA8CC@.microsoft.com...
> Hi
> I am using SQL 2000 std edition and hence using custom scripts to
> restore the DB using standby file. I am backing up T-LOGs every 30 mins on
> production and copying them on a network location which is accessible to
> standby machine.
> I run a job on standby machine which applies the transaction logs every 30
> mins by checking in backmediaset & backupmediafamily tables of Production.
DB
> is running in read only and is working.
> Question : In case my Production machine goes down and does not come up
how
> can I start the Standby "with data loss" if it has already appiled the
last
> T-logs generated on production.
> Since Produciton (primary) is down I can not backup the last T-log.
> Thanks
> Mangesh
>|||So you are continuously doing RESTORE WITH STANDBY? And you now wand to make the database fully
accessible? If so:
RESTORE DATABASE dbname WITH RECOVERY
No actual restore is performed, only the recovery work to get it out of standby mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
news:72D84926-67E3-4A3F-A011-E5F5A6ACA8CC@.microsoft.com...
> Hi
> I am using SQL 2000 std edition and hence using custom scripts to
> restore the DB using standby file. I am backing up T-LOGs every 30 mins on
> production and copying them on a network location which is accessible to
> standby machine.
> I run a job on standby machine which applies the transaction logs every 30
> mins by checking in backmediaset & backupmediafamily tables of Production. DB
> is running in read only and is working.
> Question : In case my Production machine goes down and does not come up how
> can I start the Standby "with data loss" if it has already appiled the last
> T-logs generated on production.
> Since Produciton (primary) is down I can not backup the last T-log.
> Thanks
> Mangesh
>|||Yes. I am doing a continuous restoration of my standby database using standby
file.
Thanks for help.
"Tibor Karaszi" wrote:
> So you are continuously doing RESTORE WITH STANDBY? And you now wand to make the database fully
> accessible? If so:
> RESTORE DATABASE dbname WITH RECOVERY
> No actual restore is performed, only the recovery work to get it out of standby mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
> news:72D84926-67E3-4A3F-A011-E5F5A6ACA8CC@.microsoft.com...
> > Hi
> >
> > I am using SQL 2000 std edition and hence using custom scripts to
> > restore the DB using standby file. I am backing up T-LOGs every 30 mins on
> > production and copying them on a network location which is accessible to
> > standby machine.
> >
> > I run a job on standby machine which applies the transaction logs every 30
> > mins by checking in backmediaset & backupmediafamily tables of Production. DB
> > is running in read only and is working.
> >
> > Question : In case my Production machine goes down and does not come up how
> > can I start the Standby "with data loss" if it has already appiled the last
> > T-logs generated on production.
> > Since Produciton (primary) is down I can not backup the last T-log.
> >
> > Thanks
> > Mangesh
> >
> >
>
>

Question on moving packages to another server

I am trying to move a package to another server, the package is on the file system and I made certain that the folders are the same and the paths match.

However when executing on the other server I get an error 0xC0012050 whil loadin package file...package failed validation from the Execute Package task. The package cannot run.

The path in the error is correct and the package name is correct.

This is one package executing 2 other packages.

Also then when it tried to run the package I received the "package cannot run" error - I received the ODBC error 08001 error during OPENP in DBOpen ERROR [08001] PLAN ALREADY EXISTS.

Am I getting the second error because I received the first error that the Package cannot run?

How do I determine why the package cannot run on the other server?

MaryOS wrote:

Am I getting the second error because I received the first error that the Package cannot run?

How do I determine why the package cannot run on the other server?

It may be the other way around; a problem with one of the connections may be causing the validation to fail. I don't know about that specific error; but make sure all the connection managers are valid.

Question on Flat File Import

I have a flat file that uses tabs as the column delimiters and cr-lf as row delimiters. The first portion of the file consists of only two columns for approximately 10 rows and then the file changes to 4 columns for the balance of the file, about 21 rows. The column names are in the first column and the data of interest is in the second column for the first 10 rows and then in the third column for the last 21 rows. Is it possible to set up something like this for parsing in SSIS? I've tried using two columns in the data flow task but then I get columns 1 and 2 through the whole file. If I tell it there are 4 columns in the file, it appends rows to each other so that there is a total of 4 columns in the first 10 rows. This reduces the row count to less than 10 and the data in these rows isn't in the proper place. Is there a way to handle this file in SSIS?

TIA

The way I've seen this handled in the past is to set up the flat file source to read the entire line as one column, then use a script task or a derived column to parse the columns.

Kirk Haselden
Author "SQL Server Integration Services"

|||

KirkHaselden wrote:

The way I've seen this handled in the past is to set up the flat file source to read the entire line as one column, then use a script task or a derived column to parse the columns.

Kirk Haselden
Author "SQL Server Integration Services"

i believe you mean "script component" not "script task".|||

Duane Douglas wrote:

KirkHaselden wrote:

The way I've seen this handled in the past is to set up the flat file source to read the entire line as one column, then use a script task or a derived column to parse the columns.

Kirk Haselden
Author "SQL Server Integration Services"

i believe you mean "script component" not "script task".

Ha ha. The teacher becomes the pupil

Nice one Duane!!

Question on exporting reports

I have 2 reports in my Access program(SQL DB) that I need to export into 1
text file. The OutputTo function works great for 1 file, but I can't figure
out an easy way to add the second report to the end of this text file.CD,
Might want to post that question to the Access NG.
From a SQL perspective, you can use the UNION clause to combine compatable
resultsets. The file can be generated by using OSQL, BCP (OUT) or the
easiest DTS.
HTH
Jerry
"CD" <CD@.discussions.microsoft.com> wrote in message
news:1D3CCE59-54C4-48E5-B4AE-2C1C88D43F79@.microsoft.com...
>I have 2 reports in my Access program(SQL DB) that I need to export into 1
> text file. The OutputTo function works great for 1 file, but I can't
> figure
> out an easy way to add the second report to the end of this text file.

Question on DTS

HI all,
Which is better, to use DTS for CSV type file imports or to write your own
import app.
Considering the following that it is most likely that the structure of the
import file will change a few times within the next year, and that this is
not inhouse but for an outside client, and that they would most probably be
using SQLXpress
Value your thoughts
Thanks
RobertDTS is the right tool for this task.
Although I would not be surprised if a very experienced C# developer with
basic DTS knowledge can write this application a lot faster than writing the
DTS package.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Robert Bravery" wrote:

> HI all,
> Which is better, to use DTS for CSV type file imports or to write your own
> import app.
> Considering the following that it is most likely that the structure of the
> import file will change a few times within the next year, and that this is
> not inhouse but for an outside client, and that they would most probably b
e
> using SQLXpress
> Value your thoughts
> Thanks
> Robert
>
>|||Thanks Ben,
Next question:
How easy would it be to update the dts package on a clients machine when and
if structure and protocol changes.
Thanks
Robert
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:256513B7-AABF-4276-9E7F-2343D256C30B@.microsoft.com...
> DTS is the right tool for this task.
> Although I would not be surprised if a very experienced C# developer with
> basic DTS knowledge can write this application a lot faster than writing
the
> DTS package.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Robert Bravery" wrote:
>
own
the
is
be|||Using the DTS GUI Interface (DTS Designer) is very easy. Depending on the
complexity of your DTS package you may want to document what to do to
implement these future changes, instead of trying to understand how the
package works after you have not seen it for ws or months.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Robert Bravery" wrote:

> Thanks Ben,
> Next question:
> How easy would it be to update the dts package on a clients machine when a
nd
> if structure and protocol changes.
> Thanks
> Robert
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:256513B7-AABF-4276-9E7F-2343D256C30B@.microsoft.com...
> the
> own
> the
> is
> be
>
>

Question on Different Types of Backups for SQL Server 2000

Does it matter which backup method you use with SQL Server
2000, as far as SQL Server recognizing the backup, and
keeping the transaction log file at a reasonable size?
You can backup your database in Enterprise Manager using the
Backup Wizard, the Backup Database Menu Option, or through a
Maintenance Plan.
What my documentation doesn't tell me, is this: Does it
matter which backup method you use, in order for the
transaction log to remain at a reasonable size (based on the
last full backup)?
In other words, will SQL Server 2000 recognize any of the
backups as full backups, and allow the transaction log to be
shrunk, back to the point of the last full backup?
Appreciate any help/advice.
jj
p.s. If you put the DB Backup on a SCHEDULE, using either
the Wizard, or the Backup Database menu option in EM, does
this require SQL Server Agent to be running? Or do you only
need Agent to run, when backing up via a Maintenance Plan?
Just wondering if ANY scheduled backups require Agent to be
running?Hello,
To answer your question, no it does not matter.
Personally I do it though Database Maintenance Plan,
purely cos i'm a GUI kinda guy
:D
Peter
>--Original Message--
>Does it matter which backup method you use with SQL
Server
>2000, as far as SQL Server recognizing the backup, and
>keeping the transaction log file at a reasonable size?
>You can backup your database in Enterprise Manager using
the
>Backup Wizard, the Backup Database Menu Option, or
through a
> Maintenance Plan.
>What my documentation doesn't tell me, is this: Does it
>matter which backup method you use, in order for the
>transaction log to remain at a reasonable size (based on
the
>last full backup)?
>In other words, will SQL Server 2000 recognize any of the
>backups as full backups, and allow the transaction log to
be
>shrunk, back to the point of the last full backup?
>Appreciate any help/advice.
>jj
>p.s. If you put the DB Backup on a SCHEDULE, using
either
>the Wizard, or the Backup Database menu option in EM,
does
>this require SQL Server Agent to be running? Or do you
only
>need Agent to run, when backing up via a Maintenance
Plan?
>Just wondering if ANY scheduled backups require Agent to
be
>running?
>.
>|||Thanks for the info. Very helpful. The reason I asked
this, is because each backup type uses a different naming
convention. I just need to make sure that, if I do a full
DB back, using either of the 3 methods, I'll be able to do a
DB RESTORE, using any of the backup files (regardless of
their naming convention, or which method I used to backup
the DB).
Is this correct?
Sorry for this one last question. I just need to make sure,
and my documentation doesn't provide answer to this
question. Many thanks.
jj
=============Kevin Brooks wrote:
> Do FULL DATABASE BACKUPS and LOG BACKUPS. We do a full backup once a day
> and backup the logs every 5min to 1 hour, depending on which database. For
> safety we also have a job that manually checks % log used(DBCC
> SQLPERF(LOGSPACE) not agent counters) every 30mins across all databases and
> will fire off the appropiate log backup and email us if they are getting
> full. A full backup will not clear the log and log size will always vary
> depending on usage. This can all be done through the GUI, but the GUI
> stinks. It is all about coding for best performance and most control, then
> again we had to build customer log shipping because SQL logshipping is not
> stable. Remember - if your logfile grows and you need to shrink the
> physical file, you have to use DBCC SHRINKFILE. This is all assuming you
> are running in FULL RECOVERY mode. HTH.
>
> "JJay" <jjj.nospam@.mindspring.com> wrote in message
> news:D4q_a.10456$M6.888848@.newsread1.prod.itd.earthlink.net...
>>Does it matter which backup method you use with SQL Server
>>2000, as far as SQL Server recognizing the backup, and
>>keeping the transaction log file at a reasonable size?
>>You can backup your database in Enterprise Manager using the
>>Backup Wizard, the Backup Database Menu Option, or through a
>> Maintenance Plan.
>>What my documentation doesn't tell me, is this: Does it
>>matter which backup method you use, in order for the
>>transaction log to remain at a reasonable size (based on the
>>last full backup)?
>>In other words, will SQL Server 2000 recognize any of the
>>backups as full backups, and allow the transaction log to be
>>shrunk, back to the point of the last full backup?
>>Appreciate any help/advice.
>>jj
>>p.s. If you put the DB Backup on a SCHEDULE, using either
>>the Wizard, or the Backup Database menu option in EM, does
>>this require SQL Server Agent to be running? Or do you only
>>need Agent to run, when backing up via a Maintenance Plan?
>>Just wondering if ANY scheduled backups require Agent to be
>>running?
>
>