Drawbacks of putting transaction log on system drive

Hi,
I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
Would I come across any drawbacks if I put all transaction log files of a
database onto the system (OS installation) drive along with a page file and
maybe the tempdb? Most of the load in my database is due to read operations.
-- Many thanks, Oskar.Oskar wrote:
> Hi,
> I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
> Would I come across any drawbacks if I put all transaction log files of a
> database onto the system (OS installation) drive along with a page file an
d
> maybe the tempdb? Most of the load in my database is due to read operation
s.
> -- Many thanks, Oskar.
>
Hi Oskar
There will most likely be a performance hit the more files you put on
the same drive, but if it will be noticable or a problem is hard to say
without knowing your load and application. The best advice it to test it
as good as you can and then make your decicion based on your tests.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Drawbacks of putting transaction log on system drive

Hi,
I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
Would I come across any drawbacks if I put all transaction log files of a
database onto the system (OS installation) drive along with a page file and
maybe the tempdb? Most of the load in my database is due to read operations.
-- Many thanks, Oskar.Oskar wrote:
> Hi,
> I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
> Would I come across any drawbacks if I put all transaction log files of a
> database onto the system (OS installation) drive along with a page file and
> maybe the tempdb? Most of the load in my database is due to read operations.
> -- Many thanks, Oskar.
>
Hi Oskar
There will most likely be a performance hit the more files you put on
the same drive, but if it will be noticable or a problem is hard to say
without knowing your load and application. The best advice it to test it
as good as you can and then make your decicion based on your tests.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

DR: Replication vs. log shipping vs. clustering vs. database mirroring........

Up to now we have gotten by without having any local DR copies of servers (if a sql server goes down we are usually able to get it back in less than 3 hours). But I want more now. I want to trim the "down" window to no more than 5 or 10 minutes. (Immedate failover would be nice but is not an essential requirement. The essential requirement is to loose no data!)

I have a spec of knowledge in these areas:

SQL 2005 Clustering (requires approved hardware, quorum disk, etc. involved)

SQL 2005 Replicaiton

SQL 2005 Log Shipping.

SQL 2005 Database mirroring. ( needs three servers)

Which approach do you think is the most straightforward, sparing of hardware, yet reliable way to get us back up and running after a sql server failure.

TIA,

barkingdog

All of the above, none of the above, some combination of the above. It isn't a yes or no answer and it isn't a one size fits all. Each option has pros and cons. I teach a 5 day hands on class on High Availability and at the end of about 46 hours of instruction, we cover about 1/4 of the topic. It gives you a basic foundation for understanding the pros and cons of each technology as well as how it fits into an environment. The hard part is the implementation. There are 800+ page books on the SQL Server HA stuff from SQL Server 2000. I'm working on one for SQL Server 2005 that is probably going to weigh in at a two volume set of almost 1700 combined pages. (About 800 pages of it are already done and I still have at least 3 technologies and 17 subjects to cover.) It depends upon your environment, business requirements, any needs for the application etc. If you can be a little more specific than "I want better availability", it might be possible to more closely target something.|||

We want to consolidate several sql 2000 development servers into a single sql 2005 server. BUT if the sql 2005 server goes down, until we can fix it, the developers are going to have a lot of free time on their hands! The goal here is to minimize the amount of down time (say less than 20 minutes) so our developers can get back on the road ASAP. Of course, if the problem is hardware-related we could be down for a lot longer than 20 minutes, implying another server (maybe even a virtual server?) should be part of the environment.

Barkingdog

|||Based on the fact that it is a development environment, I would simply drop in log shipping and be done. It would require developers to reconnect to a different SQL Server instance that might be missing a small amount of data, but that is unimportant in a dev environment. It gives you basic redundancy without going overboard. And you also have to keep in mind that everything in a dev environment should be able to be recreated by a developer, so you are simply looking fdor redundancy to reduce the amount of stuff that needs to be recreated in the event of a failure.|||

I am reading about log shipping. It seems to be very focused on "one database at a time" type operations. One way is to use SSMS, right-click the database of interest, go to Properties, indicate it is the primary database.... and do this for each database on the server.

I want to apply log shipping to ALL the databases at once, not one step at a time. Is there any accomodaiton to do this?

TIA,

Barkingdog

|||

I have just finished my first pass reading about Log Shipping. In point of fact, while the concept is simple, the implementation has lots of details, is error prone, and requires changes to the way things normally work around here.

My greatest concern with all such approaches is that we will set it up, it will run fine on auto pilot, we will forget about the details, and then the emergency hits. Since we are no longer "sharp" it is very possible that we could forget to do something or do the wrong something, undermining the purpose of LS. (I know ... have good documentation....) I have the same issue with other "Disaster Recovery" type products. too.

Barkingdog

|||

Since you mentioned Database Mirroring, my assumption is SQL Server 2005 for all of this. You don't have to click through SSMS. You can script the log shipping implementation. It will require that you connect to two different instances and execute the portions of the script in order, but you don't have to click through a GUI.

As for the other side, no one is going to be able to help with that issue. High Availability isn't technology in a box. It requires people, processes, and technology to work together to achieve your availability goals. Deploying technology without well tested and documented procedures for accomplishing a failover and possibly a failback is just a waste of time. The technology is only as good as the processes that are in place to ensure the technologies can meet your business needs. The processes are only as good as the documentation that you have in place. The processes are only as good as the people implementing and performing the processes. You can't have one without the other.

Implementing a high availability solution is 5% about technology. The other 95% is all about processes and failover procedures.

|||

Excellent answers thanks.

Your advise about failover procedures is sage-like. I do find that mirroring is more to my "liking". And why it may not be needed on a developer box it would be good practice ground for our sql 2005 production servers (which also need falover).

Barkingdog

sql

DPE writing to Event Log

Has anyone had success getting a custom data processing extenstion to write
to the Event Log? Writing to the log works fine on my machine in the IDE but
once deployed to the server it fails. I'm assuming its a security issue but
I do not know how to resolve it.
Any help is greatly appreciated.Hi Shawn
Make sure that you've given the correct Code Access Security (CAS)
Permissions to your assembly. If you look up the EventLog.WriteEntry
method, it requires the EventLogPermission. To see how you can add
permission attributes to your custom assembly methods, see
http://msdn2.microsoft.com/en-us/library/ms153587.aspx.
Also, you may need to add extra security attributes to your
AssemblyInfo.cs/vb file. Please review the BOL for Reporting Services
or visit http://msdn2.microsoft.com/en-us/library/ms153561.aspx for
more details.
Regards,
Thiago Silva
MCAD.NET
Shawn wrote:
> Has anyone had success getting a custom data processing extenstion to write
> to the Event Log? Writing to the log works fine on my machine in the IDE but
> once deployed to the server it fails. I'm assuming its a security issue but
> I do not know how to resolve it.
> Any help is greatly appreciated.|||Thanks for the information. I have tried what was suggested and believe that
I am still missing something.
I added the following code group to my config file. As you can see it is
set to "FullTrust".
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="FOCUSCodeGroup"
Description="Code group for my data processing extension">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="D:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting
Services\ReportServer\bin\MyDataExtension.dll"/>
</CodeGroup>
I tried the following two approaches in my assembly:
1)--
EventLogPermission elp = new
EventLogPermission(System.Security.Permissions.PermissionState.Unrestricted);
elp.Assert();
elog.WriteEntry(loc + " -- " + msg, entryType);
2)--
EventLogPermission eventLogPermission = new
EventLogPermission(EventLogPermissionAccess.Administer, ".");
eventLogPermission.PermitOnly();
elog.WriteEntry(loc + " -- " + msg, entryType);
(#2 came from: http://support.microsoft.com/?kbid=918122)
During execution both prompted me for authentication when the exception was
thrown and never wrote to the event log. Any ideas as to what I could still
be missing?|||I was able to resolve the problem by first creating the EventSource on the
server itself. The problem was caused by the creation of the source... NOT
the writing to the log itself. Once the souce was create manually (in the
registry) I was able to write to the log successfully using the
configurations provided earlier. Thanks for the help.
http://groups.google.com/group/microsoft.public.dotnet.framework.aspnet/browse_thread/thread/49c3188dba8b7b00/b7240a0183af6cd9?lnk=st&q=asp.net+2.0+eventlog+permission&rnum=10#b7240a0183af6cd9

Download database to local and use

I have SQL database hosted by my ISP. Every now and again we log on and create new tables using user XXX1. After getting a backup of the database, I have restored it on my local machine. When running the application on local, I get an error because there is a new user in database called XXX1.

I would like to change the user from XXX1 to dbo on my local machine for all tables, stored procedures and views. How do I do this easily?

Thanks in advance!

Dave

Hi,

http://groups.google.de/group/microsoft.public.sqlserver.programming/browse_frm/thread/f1625d70fb765701

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Doubts about Transaction Log

Hello to Everybody!
I have a doubts about Transaction Log. What's happen when I execute the
follow command
BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TRUN
CATE_ONLY
So, When should I use this command to avoid fill up the log?
Thanks a Lot
Juliano HortaDon't use it. Either have the db in simple recovery mode. Or do regular tran
saction log backups (the
log is emptied when you do a log backup).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Juliano H via droptable.com" <forum@.droptable.com> wrote in message
news:541010E0E3B40@.droptable.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TR
UNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta|||Only use this if you do not need to be recoverable via trans log, if so
maybe you should just use simple recovery mode
Below is from BOL
Note If backing up the log does not appear to truncate most of the log, an
old open transaction may exist in the log. Log space can be monitored with
DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log Backups.
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Juliano H via droptable.com" <forum@.droptable.com> wrote in message
news:541010E0E3B40@.droptable.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH
> TRUNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta|||Hi,
To add on to Tibor and David; Go ahead with SIMPLE recovery model if you do
not require a point in time recovery.
There is no difference between NO_LOG and TRUNCATE_ONLY. Both are same and
will truncate the inactive
portion of the log.
Thanks
Hari
SQL Server MVP
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uYKNlZKtFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Only use this if you do not need to be recoverable via trans log, if so
> maybe you should just use simple recovery mode
>
> Below is from BOL
>
> Note If backing up the log does not appear to truncate most of the log,
> an old open transaction may exist in the log. Log space can be monitored
> with DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log
> Backups.
>
> NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it
> and truncates the log. This option frees space. Specifying a backup device
> is unnecessary because the log backup is not saved. NO_LOG and
> TRUNCATE_ONLY are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes,
> immediately execute BACKUP DATABASE.
>
> "Juliano H via droptable.com" <forum@.droptable.com> wrote in message
> news:541010E0E3B40@.droptable.com...
>

Doubts about Transaction Log

Hello to Everybody!
I have a doubts about Transaction Log. What's happen when I execute the
follow command
BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TRUNCATE_ONLY
So, When should I use this command to avoid fill up the log?
Thanks a Lot
Juliano Horta
Don't use it. Either have the db in simple recovery mode. Or do regular transaction log backups (the
log is emptied when you do a log backup).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Juliano H via droptable.com" <forum@.droptable.com> wrote in message
news:541010E0E3B40@.droptable.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TRUNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta
|||Only use this if you do not need to be recoverable via trans log, if so
maybe you should just use simple recovery mode
Below is from BOL
Note If backing up the log does not appear to truncate most of the log, an
old open transaction may exist in the log. Log space can be monitored with
DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log Backups.
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Juliano H via droptable.com" <forum@.droptable.com> wrote in message
news:541010E0E3B40@.droptable.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH
> TRUNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta
|||Hi,
To add on to Tibor and David; Go ahead with SIMPLE recovery model if you do
not require a point in time recovery.
There is no difference between NO_LOG and TRUNCATE_ONLY. Both are same and
will truncate the inactive
portion of the log.
Thanks
Hari
SQL Server MVP
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uYKNlZKtFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Only use this if you do not need to be recoverable via trans log, if so
> maybe you should just use simple recovery mode
>
> Below is from BOL
>
> Note If backing up the log does not appear to truncate most of the log,
> an old open transaction may exist in the log. Log space can be monitored
> with DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log
> Backups.
>
> NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it
> and truncates the log. This option frees space. Specifying a backup device
> is unnecessary because the log backup is not saved. NO_LOG and
> TRUNCATE_ONLY are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes,
> immediately execute BACKUP DATABASE.
>
> "Juliano H via droptable.com" <forum@.droptable.com> wrote in message
> news:541010E0E3B40@.droptable.com...
>

Doubts about Transaction Log

Hello to Everybody!
I have a doubts about Transaction Log. What's happen when I execute the
follow command
BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TRUNCATE_ONLY
So, When should I use this command to avoid fill up the log?
Thanks a Lot
Juliano HortaDon't use it. Either have the db in simple recovery mode. Or do regular transaction log backups (the
log is emptied when you do a log backup).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Juliano H via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:541010E0E3B40@.SQLMonster.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TRUNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta|||Only use this if you do not need to be recoverable via trans log, if so
maybe you should just use simple recovery mode
Below is from BOL
Note If backing up the log does not appear to truncate most of the log, an
old open transaction may exist in the log. Log space can be monitored with
DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log Backups.
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Juliano H via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:541010E0E3B40@.SQLMonster.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH
> TRUNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta|||Hi,
To add on to Tibor and David; Go ahead with SIMPLE recovery model if you do
not require a point in time recovery.
There is no difference between NO_LOG and TRUNCATE_ONLY. Both are same and
will truncate the inactive
portion of the log.
Thanks
Hari
SQL Server MVP
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uYKNlZKtFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Only use this if you do not need to be recoverable via trans log, if so
> maybe you should just use simple recovery mode
>
> Below is from BOL
>
> Note If backing up the log does not appear to truncate most of the log,
> an old open transaction may exist in the log. Log space can be monitored
> with DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log
> Backups.
>
> NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it
> and truncates the log. This option frees space. Specifying a backup device
> is unnecessary because the log backup is not saved. NO_LOG and
> TRUNCATE_ONLY are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes,
> immediately execute BACKUP DATABASE.
>
> "Juliano H via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:541010E0E3B40@.SQLMonster.com...
>> Hello to Everybody!
>> I have a doubts about Transaction Log. What's happen when I execute the
>> follow command
>> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH
>> TRUNCATE_ONLY
>> So, When should I use this command to avoid fill up the log?
>> Thanks a Lot
>> Juliano Horta
>

Dos

I have a server that automatically updates itself every night. I have .bat files setup to detect any error messages in the log files that may be encountered during the process, and the job would abort. Furthermore, I need to have an alert e-mailed to me. The plan I came up with to do that is to somehow have another bat file chek my error log file. If the file is empty, the bat file would execute. Otherwise, it would simply fail. I need to have such bat file created, so I could schedule it using SQL Server 7 job scheduler. Now I don't know the dos command to check if the file is empty. Please, any suggestions would be greatly appreciated.Can't think of a dos command to help you but it sounds like a perfect candidate for DTS. You can use xp_sendmail and vbscript ... plus many others.

Don't update Files rtn, SQL secundary with warnings 208

I have 3 sql Server 2000 with log shipping, but the last files .rtn is
191207, don't update. In SQL Primary don't have events, in SQL Secundary
there are many warnings with the next descripcion:
Source: SQLserveragent, category: Job Engine, Type: Warning, Event ID:208
SQL server Sheduled Job 'Log Shipping Restore for
(local).BPP_logshipping'(0x6DA834715FFBFD4C8DF2F9226CA343F)
Status: Failed - Invoked on 2008-01-21 09:29:00 - Messae: The job failed.
The job was invoked bye schedule (Schedule 1). The lst step to run was step
1 (Log Shipping Restore).
In SQL enterpise, jobs, there is a Log shipping restore for local... in red.
the files rtn in SQL Secundary is the same in SQL Primary.
I don't find solution.Hi
I am not a log shipping expert, but it sounds like the backup may be
corrupt. I would stop the log shipping jobs, take a new backup of the
database and restore it on the secondary server and then restart the log
shipping jobs.
John
"Kerly" wrote:
> I have 3 sql Server 2000 with log shipping, but the last files .rtn is
> 191207, don't update. In SQL Primary don't have events, in SQL Secundary
> there are many warnings with the next descripcion:
> Source: SQLserveragent, category: Job Engine, Type: Warning, Event ID:208
> SQL server Sheduled Job 'Log Shipping Restore for
> (local).BPP_logshipping'(0x6DA834715FFBFD4C8DF2F9226CA343F)
> Status: Failed - Invoked on 2008-01-21 09:29:00 - Messae: The job failed.
> The job was invoked bye schedule (Schedule 1). The lst step to run was step
> 1 (Log Shipping Restore).
> In SQL enterpise, jobs, there is a Log shipping restore for local... in red.
> the files rtn in SQL Secundary is the same in SQL Primary.
> I don't find solution.
>

domain\user cannot log on SQL server 2005

Hi All,

I have an issue to connect to SQL 2005. My user is also declared in SQL 2005 as sysadmin.

C:\>ECHO %USERDOMAIN%\%USERNAME%
CMVAL\damien

C:\>sqlcmd -S CM-DB
1> quit

C:\>sqlcmd -S CM-DB -U "CMVAL\damien" -P "ThePassword"
Msg 18456, Level 14, State 1, Server CM-DB, Line 1
Login failed for user 'CMVAL\damien'.

C:\>

Seems that the system reject the domain I've provided. Some of my service failed to log to SQL Server due to this issue.

Thanks for your help.

Damien

By default, sqlcmd uses Windows Authentication. If you are using windows system, then you automatically get authenticated through windows authentication mode by not using -U options.

While if you do use -U, then it means you will be authenticated using Sql login mode, you need to use the sql server logins, not windows logins.

You may refer to http://msdn2.microsoft.com/en-us/library/ms165702.aspx for more information.

|||

Thanks LY.J for your answer.

In fact, my original issue is that I have a lot of this error in the event viewer :

Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18456
Date: 13/12/2006
Time: 09:42:29
User: CMVAL\damien
Computer: CM-DB
Description:
Login failed for user 'CMVAL\damien'. [CLIENT: 10.194.94.8]

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 18 48 00 00 0e 00 00 00 .H......
0008: 06 00 00 00 43 00 4d 00 ....C.M.
0010: 2d 00 44 00 42 00 00 00 -.D.B...
0018: 07 00 00 00 6d 00 61 00 ....m.a.
0020: 73 00 74 00 65 00 72 00 s.t.e.r.
0028: 00 00 ..

Then I suppose that if I can't connect to the DB with sqlcmd providing -U "CMVAL\damien", my service could not either connect.
But according to what you said, it seems that the issue is elsewhere.

Do you have an idea on how I can investigate on it?

Thanks
Damien

|||

Has the account CMVAL\damien expired?

This happens when you create an account specifically for a service and forget to set the 'does not expire' attribute.

If so, change the password, and logoff/logon

Also I had a case where a user could not be authenticated to SQL because he used a German keyboard which typed 'y' in some applications and 'z' in other applications... :)

|||

Try running a command prompt under the damien credentials, then connect using -E option. Also, have a look at the SQL Server ERRORLOG file and let us know what is the state of the error printed there (it will be different from the one printed by the client tool).

Thanks
Laurentiu

Domain Error variation

Hi there,
I am having problems generating the Geomean using "EXP(AVG(LOG()))" in
SQL Server 2005.
I have looked at the common causes of this, namely LOG(0) returning
NULL and hence causing the problem, but I have queried this out of the
test data and all values being used are greater than 0.
Another interesting point to note is that the query (which I'll post
below) performs this calculation over a grouping of values from
different tables, when I add a level of indirection to this (create a
temporary view of the grouping) it seems to work and returns no
problems this was why I threw the Log(0) theory for this problem out
the window.
I think it could be a rounding problem, but this same query ran fine on
SQL Server 2000 (once we set the flags correctly "SET ARITHABORT ON,
NUMERIC_ROUNDABORT OFF"), and am wondering if it is something wrong
with SQL Server 2005, the version I am using is 9.0.1399 and accessing
it through the Management Studio.
It is obviously very hard to post the test data to give you an accurate
way of reproducing it, but I was hoping that someone could place their
finger on the problem by having a look at the SQL.
SELECT
TEST_BATCH.MACHINE_ID AS MACHINE_ID,
TEST_BATCH.TEST_DATE AS TEST_DATE,
TEST.TEST_TYPE_ID AS TEST_TYPE_ID,
EXP(AVG(LOG(TEST.RESULT))) AS MEAN_RESULT,
COUNT(TEST.TEST_ID) AS RESULT_COUNT
FROM
TEST_BATCH
JOIN
TEST_PROCEDURE
ON
(TEST_BATCH.TEST_BATCH_ID = TEST_PROCEDURE.TEST_BATCH_ID)
JOIN
TEST
ON
(TEST_PROCEDURE.TEST_PROCEDURE_ID = TEST.TEST_PROCEDURE_ID)
JOIN
TEST_TYPE
ON
(TEST.TEST_TYPE_ID = TEST_TYPE.TEST_TYPE_ID)
JOIN
MACHINE_TEST_TYPE
ON
(TEST_BATCH.MACHINE_ID = MACHINE_TEST_TYPE.MACHINE_ID) AND
(TEST_TYPE.TEST_TYPE_ID = MACHINE_TEST_TYPE.TEST_TYPE_ID)
WHERE
(TEST_BATCH.MACHINE_ID IS NOT NULL) AND
(TEST_BATCH.TEST_DATE BETWEEN @.START_TEST_DATE AND
@.END_TEST_DATE) AND
(TEST_PROCEDURE.AUTO_REGISTRATION_TEST_REGIME_ID IS NULL) AND
(TEST_PROCEDURE.SAMPLE_TYPE = 'N') AND
(TEST_PROCEDURE.SAMPLE_ID IS NOT NULL) AND
(TEST_PROCEDURE.IS_SUPERSEDED = 'F') AND
(TEST_TYPE.RESULT_MEAN_TYPE = 'G') AND
(TEST.IS_MISSING_RESULT = 'F')
GROUP BY
TEST_BATCH.MACHINE_ID,
TEST_BATCH.TEST_DATE,
TEST.TEST_TYPE_ID
this query is correct with the data model that we have developed, and
as I say it has previously worked.
Many thanks for any light that can be shed on this
AlastairAlastair,
If you replace the offending expression with
MAX(CASE WHEN TEST.RESULT = 0e0 THEN 0 ELSE NULL END)
do you get NULL or 0? What is the data type of
TEST.RESULT?
I can't test anything on SQL Server 2005 until later, but
I suggest you try this as a workaround:
EXP(AVG(LOG(CASE WHEN TEST.RESULT <= 0 THEN NULL END)))
If there are any 0 values, it's possible that in one
query plan they are filtered out before LOG() is applied,
and in another they are filtered afterwards. The CASE
expression prevents them from causing a problem, regardless
of the actual query execution plan.
If that still fails, you could see what results you get if
you select things like
SUM(TEST.RESULT), MAX(LOG(TEST.RESULT)), and so on, to
consider whether the problem is really with LOG, and whether
there is some overflow somewhere.
What is the distribution of data like? What are the MIN, MAX,
and COUNT of TEST.RESULT, for example?
Steve Kass
Drew University
Alastair wrote:

> Hi there,
> I am having problems generating the Geomean using "EXP(AVG(LOG()))" in
> SQL Server 2005.
> I have looked at the common causes of this, namely LOG(0) returning
> NULL and hence causing the problem, but I have queried this out of the
> test data and all values being used are greater than 0.
> Another interesting point to note is that the query (which I'll post
> below) performs this calculation over a grouping of values from
> different tables, when I add a level of indirection to this (create a
> temporary view of the grouping) it seems to work and returns no
> problems this was why I threw the Log(0) theory for this problem out
> the window.
> I think it could be a rounding problem, but this same query ran fine on
> SQL Server 2000 (once we set the flags correctly "SET ARITHABORT ON,
> NUMERIC_ROUNDABORT OFF"), and am wondering if it is something wrong
> with SQL Server 2005, the version I am using is 9.0.1399 and accessing
> it through the Management Studio.
> It is obviously very hard to post the test data to give you an accurate
> way of reproducing it, but I was hoping that someone could place their
> finger on the problem by having a look at the SQL.
> SELECT
> TEST_BATCH.MACHINE_ID AS MACHINE_ID,
> TEST_BATCH.TEST_DATE AS TEST_DATE,
> TEST.TEST_TYPE_ID AS TEST_TYPE_ID,
> EXP(AVG(LOG(TEST.RESULT))) AS MEAN_RESULT,
> COUNT(TEST.TEST_ID) AS RESULT_COUNT
> FROM
> TEST_BATCH
> JOIN
> TEST_PROCEDURE
> ON
> (TEST_BATCH.TEST_BATCH_ID = TEST_PROCEDURE.TEST_BATCH_ID)
> JOIN
> TEST
> ON
> (TEST_PROCEDURE.TEST_PROCEDURE_ID = TEST.TEST_PROCEDURE_ID)
> JOIN
> TEST_TYPE
> ON
> (TEST.TEST_TYPE_ID = TEST_TYPE.TEST_TYPE_ID)
> JOIN
> MACHINE_TEST_TYPE
> ON
> (TEST_BATCH.MACHINE_ID = MACHINE_TEST_TYPE.MACHINE_ID) AND
> (TEST_TYPE.TEST_TYPE_ID = MACHINE_TEST_TYPE.TEST_TYPE_ID)
> WHERE
> (TEST_BATCH.MACHINE_ID IS NOT NULL) AND
> (TEST_BATCH.TEST_DATE BETWEEN @.START_TEST_DATE AND
> @.END_TEST_DATE) AND
> (TEST_PROCEDURE.AUTO_REGISTRATION_TEST_REGIME_ID IS NULL) AND
> (TEST_PROCEDURE.SAMPLE_TYPE = 'N') AND
> (TEST_PROCEDURE.SAMPLE_ID IS NOT NULL) AND
> (TEST_PROCEDURE.IS_SUPERSEDED = 'F') AND
> (TEST_TYPE.RESULT_MEAN_TYPE = 'G') AND
> (TEST.IS_MISSING_RESULT = 'F')
> GROUP BY
> TEST_BATCH.MACHINE_ID,
> TEST_BATCH.TEST_DATE,
> TEST.TEST_TYPE_ID
> this query is correct with the data model that we have developed, and
> as I say it has previously worked.
> Many thanks for any light that can be shed on this
> Alastair
>|||Hi Steve, thanks for the reply.
We chjecked all the MIN and MAX results and there is nothing out of the
ordinary that SQL Server couldn't handle, such as the case that we were
able to create an intermediate table an then perform the calculation on
that.

> What is the data type of TEST.RESULT?
NUMERIC(10, 4)
We did manage to solve the problem using your suggestion of a CASE
statement and also used (TEST.RESULT > 0) in the WHERE clause (although
again I'll state we know that the results are never 0 or below). It
seems that the if you do not explicitly state you do not want 0 or
below in your results then SQL Server seems to throw a wobbly and use
"Domain Error" as an excuse when using the LOG function.
Thanks for your help.
Alastair

Doing a Transaction Log Backup.

I am going to back up my databases using a Database Maintenence Plans (from
Enterprise Manager). I see there is a Complete Backup and a Transaction Log
Backup.
If I do a Complete Backup, should I do a Transaction Log Backup as well ?
What would you do a Transaction Log Backup ?
Thanks,
CraigHere is an excerpt from SQL Server Books OnLine:
Transaction Log Backups
The transaction log is a serial record of all the transactions that have
been performed against the database since the transaction log was last backe
d
up. With transaction log backups, you can recover the database to a specific
point in time (for example, prior to entering unwanted data), or to the poin
t
of failure.
When restoring a transaction log backup, Microsoft? SQL Server? rolls
forward all changes recorded in the transaction log. When SQL Server reaches
the end of the transaction log, it has re-created the exact state of the
database at the time the backup operation started. If the database is
recovered, SQL Server then rolls back all transactions that were incomplete
when the backup operation started.
Transaction log backups generally use fewer resources than database backups.
As a result, you can create them more frequently than database backups.
Frequent backups decrease your risk of losing data.
Note Sometimes a transaction log backup is larger than a database backup.
For example, a database has a high transaction rate causing the transaction
log to grow quickly. In this situation, create transaction log backups more
frequently.
Transaction log backups are used only with the Full and Bulk-Logged Recovery
models. For more information, see Using Recovery Models.
Using Transaction Log Backups with Database Backups
Restoring a database using both database and transaction log backups works
only if you have an unbroken sequence of transaction log backups after the
last database or differential database backup. If a log backup is missing or
damaged, you must create a database or differential database backup and star
t
backing up the transaction logs again. Retain the previous transaction logs
backups if you want to restore the database to a point in time within those
backups.
The only time database or differential database backups must be synchronized
with transaction log backups is when starting a sequence of transaction log
backups. Every sequence of transaction log backups must be started by a
database or differential database backup.
Usually, the only time that a new sequence of backups is started is when the
database is backed up for the first time or a change in recovery model from
Simple to Full or Bulk-Logged has occurred. For more information, see
Switching Recovery Models.
Let me know if it helps,
Edgardo Valdez
MCSD, MCDBA, MCSE
"Craig HB" wrote:

> I am going to back up my databases using a Database Maintenence Plans (fro
m
> Enterprise Manager). I see there is a Complete Backup and a Transaction L
og
> Backup.
> If I do a Complete Backup, should I do a Transaction Log Backup as well ?
> What would you do a Transaction Log Backup ?
> Thanks,
> Craig|||Thanks, Edgardo
That was helpful. It seems from that excerpt that I should do transaction
backups between database backups.
Just one more question on that:
When you restore a transaction log, do you restore it in the same way as
restoring a database ? i.e. Enterprise Manager / All Tasks / Retore / Select
the file and click OK.
Thanks,
Craig|||Sorry for not replying before.
I usually do Transaction Log Backups in between full backups, with a
frequency that varies from 10 min to 4 hours, depending of the type of
database (production, development, etc), disk space available, etc.
You can also refer to this article that can help you
(http://www.microsoft.com/technet/pr...st.mspx
)
, specially where it says "Performing Transaction Log Backups through
Enterprise Manager"
I hope it helps you.
"Craig HB" wrote:

> Thanks, Edgardo
> That was helpful. It seems from that excerpt that I should do transaction
> backups between database backups.
> Just one more question on that:
> When you restore a transaction log, do you restore it in the same way as
> restoring a database ? i.e. Enterprise Manager / All Tasks / Retore / Sele
ct
> the file and click OK.
> Thanks,
> Craig

Doing a diff on 2 identical databases?

I have a production database that has log shipping enabled to a remote
server. It has been about a year now, and i wanted to know if there was a
tool or command i can use to do a diff to assure that both databases are in
fact truly in sync. Due to the application utilizing the database, it is
running in Bulk-logged mode and not FULL. Any help is greatly appreciated.You could use DataCompare from Redgate or AdeptSQL Diff - both do
essentially the same.
HTH,
Paul Ibison|||On Thu, 19 Jul 2007 22:55:26 +0100, Paul Ibison wrote:

> You could use DataCompare from Redgate or AdeptSQL Diff - both do
> essentially the same.
> HTH,
> Paul Ibison
Also look into a free tool from Quest: ComparisonSuite for SQLServer I use
it and it works really well.
-Chuck|||ApexSQL Diff is very good.
TheSQLGuru
President
Indicium Resources, Inc.
"FS" <FS@.discussions.microsoft.com> wrote in message
news:A9351434-E932-4F55-837D-B3ED75B44BAF@.microsoft.com...
>I have a production database that has log shipping enabled to a remote
> server. It has been about a year now, and i wanted to know if there was a
> tool or command i can use to do a diff to assure that both databases are
> in
> fact truly in sync. Due to the application utilizing the database, it is
> running in Bulk-logged mode and not FULL. Any help is greatly appreciated.|||Thank you all for your replies.
"TheSQLGuru" wrote:

> ApexSQL Diff is very good.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "FS" <FS@.discussions.microsoft.com> wrote in message
> news:A9351434-E932-4F55-837D-B3ED75B44BAF@.microsoft.com...
>
>

Doing a diff on 2 identical databases?

I have a production database that has log shipping enabled to a remote
server. It has been about a year now, and i wanted to know if there was a
tool or command i can use to do a diff to assure that both databases are in
fact truly in sync. Due to the application utilizing the database, it is
running in Bulk-logged mode and not FULL. Any help is greatly appreciated.
On Thu, 19 Jul 2007 22:55:26 +0100, Paul Ibison wrote:

> You could use DataCompare from Redgate or AdeptSQL Diff - both do
> essentially the same.
> HTH,
> Paul Ibison
Also look into a free tool from Quest: ComparisonSuite for SQLServer I use
it and it works really well.
-Chuck
|||ApexSQL Diff is very good.
TheSQLGuru
President
Indicium Resources, Inc.
"FS" <FS@.discussions.microsoft.com> wrote in message
news:A9351434-E932-4F55-837D-B3ED75B44BAF@.microsoft.com...
>I have a production database that has log shipping enabled to a remote
> server. It has been about a year now, and i wanted to know if there was a
> tool or command i can use to do a diff to assure that both databases are
> in
> fact truly in sync. Due to the application utilizing the database, it is
> running in Bulk-logged mode and not FULL. Any help is greatly appreciated.
|||Thank you all for your replies.
"TheSQLGuru" wrote:

> ApexSQL Diff is very good.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "FS" <FS@.discussions.microsoft.com> wrote in message
> news:A9351434-E932-4F55-837D-B3ED75B44BAF@.microsoft.com...
>
>

Doing a diff on 2 identical databases?

I have a production database that has log shipping enabled to a remote
server. It has been about a year now, and i wanted to know if there was a
tool or command i can use to do a diff to assure that both databases are in
fact truly in sync. Due to the application utilizing the database, it is
running in Bulk-logged mode and not FULL. Any help is greatly appreciated.You could use DataCompare from Redgate or AdeptSQL Diff - both do
essentially the same.
HTH,
Paul Ibison|||On Thu, 19 Jul 2007 22:55:26 +0100, Paul Ibison wrote:
> You could use DataCompare from Redgate or AdeptSQL Diff - both do
> essentially the same.
> HTH,
> Paul Ibison
Also look into a free tool from Quest: ComparisonSuite for SQLServer I use
it and it works really well.
-Chuck|||ApexSQL Diff is very good.
--
TheSQLGuru
President
Indicium Resources, Inc.
"FS" <FS@.discussions.microsoft.com> wrote in message
news:A9351434-E932-4F55-837D-B3ED75B44BAF@.microsoft.com...
>I have a production database that has log shipping enabled to a remote
> server. It has been about a year now, and i wanted to know if there was a
> tool or command i can use to do a diff to assure that both databases are
> in
> fact truly in sync. Due to the application utilizing the database, it is
> running in Bulk-logged mode and not FULL. Any help is greatly appreciated.|||Thank you all for your replies.
"TheSQLGuru" wrote:
> ApexSQL Diff is very good.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "FS" <FS@.discussions.microsoft.com> wrote in message
> news:A9351434-E932-4F55-837D-B3ED75B44BAF@.microsoft.com...
> >I have a production database that has log shipping enabled to a remote
> > server. It has been about a year now, and i wanted to know if there was a
> > tool or command i can use to do a diff to assure that both databases are
> > in
> > fact truly in sync. Due to the application utilizing the database, it is
> > running in Bulk-logged mode and not FULL. Any help is greatly appreciated.
>
>

Does this log backup overwrite?

The following command is run every 5 minutes:
BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD , NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I think it is overwriting the TRN file every time.Yes -- the INIT option makes it overwrite.
--
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Jay" <nospam@.nospam.org> wrote in message
news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
> The following command is run every 5 minutes:
> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD , NAME
> = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
> NO_TRUNCATE
> I think it is overwriting the TRN file every time.
>|||Psssst! Wrong answer Hans!
I was really hoping to be told I'm a idiot and didn't RTFM right.
Thank you sir.
"Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
> Yes -- the INIT option makes it overwrite.
> --
> Adam Machanic
> SQL Server MVP - http://sqlblog.com
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD ,
>> NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
>> NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>|||You should really think about generating backups with a unique filename each
time. Appending to a single file can get pretty ugly since you can't delete
individual backups in the file. It's all or nothing.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospam@.nospam.org> wrote in message
news:%23hG$XGm6HHA.3900@.TK2MSFTNGP02.phx.gbl...
> Psssst! Wrong answer Hans!
> I was really hoping to be told I'm a idiot and didn't RTFM right.
> Thank you sir.
> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
> news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
>> Yes -- the INIT option makes it overwrite.
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD ,
>> NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
>> NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>>
>|||Actually, I've been here a month now and had initially only verified that
backups were being done. Why so little? Because, no one would ever overwrite
their log backup without having backed it up, or archived it first ... would
they?
I have since spoken to the guy that set it up (my boss) and he said he
thought that he was making a complete copy of the .ldf changes for the day
each time he wrote to the .trn. I literally had to stop myself when I heard
my tone in replying to him. Not a good idea to speak to your boss like
you're talking to an idiot.
Anyway, when drive space is available, I will be changing to my own backup
program that was based on RealSQLGuy's backup program. The only problem with
the program is that it assumes the job that puts it to tape will also be
removing old files. A situation that is not the case here.
Thanks,
Jay
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23tB8RCn6HHA.5012@.TK2MSFTNGP02.phx.gbl...
> You should really think about generating backups with a unique filename
> each time. Appending to a single file can get pretty ugly since you can't
> delete individual backups in the file. It's all or nothing.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:%23hG$XGm6HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> Psssst! Wrong answer Hans!
>> I was really hoping to be told I'm a idiot and didn't RTFM right.
>> Thank you sir.
>> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
>> news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
>> Yes -- the INIT option makes it overwrite.
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD ,
>> NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
>> NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>>
>>
>|||One possible way is to base the backups on weekday, day in month or similar. Take the easy route,
day of week. You have a Monday file, a Tuesday file etc. The first backup of the day, you do INIT,
then the rest of the backups of the day you do NONIT. You now have a tail of 6 backup files
(excluding the current day). This will cut down number of physical files (nice of you have lots of
databases and frequent log backups). However, some SQL persons aren't that familiar with several
backups on same file, so they don't understand the WITH FILE = option of the RESTORE command...
Pretty much a matter of taste. And, remember that the code you write need to be understood and
maintained by somebody... ;-).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:ui%23tvhn6HHA.5984@.TK2MSFTNGP04.phx.gbl...
> Actually, I've been here a month now and had initially only verified that backups were being done.
> Why so little? Because, no one would ever overwrite their log backup without having backed it up,
> or archived it first ... would they?
> I have since spoken to the guy that set it up (my boss) and he said he thought that he was making
> a complete copy of the .ldf changes for the day each time he wrote to the .trn. I literally had to
> stop myself when I heard my tone in replying to him. Not a good idea to speak to your boss like
> you're talking to an idiot.
> Anyway, when drive space is available, I will be changing to my own backup program that was based
> on RealSQLGuy's backup program. The only problem with the program is that it assumes the job that
> puts it to tape will also be removing old files. A situation that is not the case here.
> Thanks,
> Jay
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23tB8RCn6HHA.5012@.TK2MSFTNGP02.phx.gbl...
>> You should really think about generating backups with a unique filename each time. Appending to
>> a single file can get pretty ugly since you can't delete individual backups in the file. It's all
>> or nothing.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%23hG$XGm6HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> Psssst! Wrong answer Hans!
>> I was really hoping to be told I'm a idiot and didn't RTFM right.
>> Thank you sir.
>> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
>> news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
>> Yes -- the INIT option makes it overwrite.
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD , NAME = N''Skinstore Log
>> Differential'', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>>
>>
>

Does the transaction log on a publisher SQL server get truncated when the database is back

Hello All,
The setting is SQL server 7, on Windows NT.

If the distributor can not access the log on the publisher database ( but
subscribers are not deleted yet), will the full backup of the publisher
database truncate the transaction log? My guess is, it will not truncate the
log.
Can any one confirm please?

Thanks,
MoklesFull backups don't truncate the log; the log is truncated by BACKUP log.
From the Books Online:

<Excerpt href="http://links.10026.com/?link=\tsqlref.chm::/ts_ba-bz_35ww.htm">
Once the log is backed up, the space may be truncated when no longer
required by replication or active transactions.
</Excerpt
I believe the data is considered required for replication until retrieved by
the distributor

--
Hope this helps.

Dan Guzman
SQL Server MVP

"mokles" <mokles@.rogers.com> wrote in message
news:ncWdnd5XiKzAojvfRVn-sA@.rogers.com...
> Hello All,
> The setting is SQL server 7, on Windows NT.
> If the distributor can not access the log on the publisher database ( but
> subscribers are not deleted yet), will the full backup of the publisher
> database truncate the transaction log? My guess is, it will not truncate
> the
> log.
> Can any one confirm please?
> Thanks,
> Mokles|||Dan Guzman,

Thanks for your confirmation and explanation.

Mokles

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ZSspe.1864$751.1493@.newssvr30.news.prodigy.co m...
> Full backups don't truncate the log; the log is truncated by BACKUP log.
> From the Books Online:
> <Excerpt href="http://links.10026.com/?link=\tsqlref.chm::/ts_ba-bz_35ww.htm">
> Once the log is backed up, the space may be truncated when no longer
> required by replication or active transactions.
> </Excerpt>
> I believe the data is considered required for replication until retrieved
by
> the distributor
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "mokles" <mokles@.rogers.com> wrote in message
> news:ncWdnd5XiKzAojvfRVn-sA@.rogers.com...
> > Hello All,
> > The setting is SQL server 7, on Windows NT.
> > If the distributor can not access the log on the publisher database (
but
> > subscribers are not deleted yet), will the full backup of the publisher
> > database truncate the transaction log? My guess is, it will not truncate
> > the
> > log.
> > Can any one confirm please?
> > Thanks,
> > Mokles

does the transaction log keep track of who did what?

Hello Guys,

Does any one know if the transaction log keep track of the user who did the operation or not?

coz i tried the command which have been written in the following post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1054474&SiteID=1

but it didn't show the user who do the operation

MagedSalah wrote:

Hello Guys,

Does any one know if the transaction log keep track of the user who did the operation or not?

coz i tried the command which have been written in the following post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1054474&SiteID=1

but it didn't show the user who do the operation

Yes...the information is logged. If the command you tried was DBCC LOGINFO...it won't show you who did what. As mentioned in the thread, you need a third party tool to view who did what command. Using DBCC LOGINFO isn't going to help you much in analyzing transactions and activity based on what is in the log. It's used more for checking VLFs and statuses.

-Sue
|||

It is best to NOT rely upon 'undocumented' functionality.

Using Profiler, or one of the many third party log tools, you can determine who caused an action. (Profiler must be running in advance, whereas many of the third party tools can extract that infomation from the logs. Here are some of the options:

Lumigent SQL Server log explorer
ApexSQL Log Navigator
Log P.I.
SQL Log Rescue from red-gate