Do SQL Server's developers even know what the datetime datatype is?

Just a bit of a rant here...
I've marveled for a long time that SQL Server developers chose to store
SQL Agent job history dates in in the sysjobhistory table as two
seperate columns in integer format instead of a single datetime column.
I mean come on.
Then today I discovered that the Windows 2003 Perfmon stores dates as
char(24) when the data is logged to a SQL Server database. CHAR(24)?!?!
Char (24) won't even do an explicit conversion to datetime. You have to
only grab the first 23 characters.
I think Bill G. needs to send around an internal memo reminding
everyone about the datetime data type.
/end rantI wish Gert had included some background on why this decision came about,
but otherwise a great resource:
http://www.sqldev.net/sqlagent/SQLAgentDateTime.htm
As for Perfmon, isn't it possible it was formatted to CHAR(24) because it
usually logs to a file, and the file needs to be understood by more systems
than just SQL Server?
<pshroads@.gmail.com> wrote in message
news:1164659681.627479.274240@.h54g2000cwb.googlegroups.com...
> Just a bit of a rant here...
> I've marveled for a long time that SQL Server developers chose to store
> SQL Agent job history dates in in the sysjobhistory table as two
> seperate columns in integer format instead of a single datetime column.
> I mean come on.
> Then today I discovered that the Windows 2003 Perfmon stores dates as
> char(24) when the data is logged to a SQL Server database. CHAR(24)?!?!
> Char (24) won't even do an explicit conversion to datetime. You have to
> only grab the first 23 characters.
> I think Bill G. needs to send around an internal memo reminding
> everyone about the datetime data type.
> /end rant
>

Do SQL Server's developers even know what the datetime datatype is?

Just a bit of a rant here...
I've marveled for a long time that SQL Server developers chose to store
SQL Agent job history dates in in the sysjobhistory table as two
seperate columns in integer format instead of a single datetime column.
I mean come on.
Then today I discovered that the Windows 2003 Perfmon stores dates as
char(24) when the data is logged to a SQL Server database. CHAR(24)?!?!
Char (24) won't even do an explicit conversion to datetime. You have to
only grab the first 23 characters.
I think Bill G. needs to send around an internal memo reminding
everyone about the datetime data type.
/end rantI wish Gert had included some background on why this decision came about,
but otherwise a great resource:
http://www.sqldev.net/sqlagent/SQLAgentDateTime.htm
As for Perfmon, isn't it possible it was formatted to CHAR(24) because it
usually logs to a file, and the file needs to be understood by more systems
than just SQL Server?
<pshroads@.gmail.com> wrote in message
news:1164659681.627479.274240@.h54g2000cwb.googlegroups.com...
> Just a bit of a rant here...
> I've marveled for a long time that SQL Server developers chose to store
> SQL Agent job history dates in in the sysjobhistory table as two
> seperate columns in integer format instead of a single datetime column.
> I mean come on.
> Then today I discovered that the Windows 2003 Perfmon stores dates as
> char(24) when the data is logged to a SQL Server database. CHAR(24)?!?!
> Char (24) won't even do an explicit conversion to datetime. You have to
> only grab the first 23 characters.
> I think Bill G. needs to send around an internal memo reminding
> everyone about the datetime data type.
> /end rant
>

Do comments work in EM job?

I've created a job in Enterprise Manager under Management | Jobs. I double
click the particular job, select the Steps tab, then double click the step
name. In the command window are four stored procedures that I execute:
exec sp1
exec sp2
exec sp3
exec sp4
This works fine. If I comment out sp1
-- sp1
does this have any affect? I did comment out all four SPs and they seem to
keep running. I had to disable the job to keep them from running.
Must I disable/enable a job for the comments to take affect?
Thanks,
BrettNot sure about that Brett,
I'd script out the job without/with comments and see what effect that had.
have a look in the scripts
From what you say, it sounds as tho you can't put comments in EM... which
isn't that surprising.
"Brett" <no@.spam.com> wrote in message
news:%23aq%23FmjCFHA.3280@.TK2MSFTNGP14.phx.gbl...
> I've created a job in Enterprise Manager under Management | Jobs. I
> double click the particular job, select the Steps tab, then double click
> the step name. In the command window are four stored procedures that I
> execute:
> exec sp1
> exec sp2
> exec sp3
> exec sp4
> This works fine. If I comment out sp1
> -- sp1
> does this have any affect? I did comment out all four SPs and they seem
> to keep running. I had to disable the job to keep them from running.
> Must I disable/enable a job for the comments to take affect?
> Thanks,
> Brett
>

Distribution table cleanup

Using SQL Server 2005. Replication working fine except the distribution table is continuely growing. Started to configure maintenance job (catagory: REPL-Distribution Cleanup) Any suggested steps that will not break the replication. Is there a SP available that will address my problem.

Thanks,

You can reduce the retention period using sp_changedistributiondb.

For more info, please refer to the following articles.

http://msdn2.microsoft.com/fr-fr/library/ms146868.aspx

http://msdn2.microsoft.com/fr-fr/library/ms181877.aspx

Regards,

Gary

Distribution table cleanup

Using SQL Server 2005. Replication working fine except the distribution table is continuely growing. Started to configure maintenance job (catagory: REPL-Distribution Cleanup) Any suggested steps that will not break the replication. Is there a SP available that will address my problem.

Thanks,

You can reduce the retention period using sp_changedistributiondb.

For more info, please refer to the following articles.

http://msdn2.microsoft.com/fr-fr/library/ms146868.aspx

http://msdn2.microsoft.com/fr-fr/library/ms181877.aspx

Regards,

Gary

Distribution Question

Hello
I'm just starting to look at replication for a job I have and I have a
question about setting up a distributor.
1. Can i have multiple distributors?
2. Is it possible to define which distributor is used for a certain
publication?
3. Can i set up the distributor to also be on the subscriber? (external to
the publisher)
Thanks for any and all help!
One distributor per publisher is what we can use.
The distributor can indeed be on the subscriber or the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Distribution Job Step Retries all in one connection and/or transaction?

When a distribution job step "Retry Attempts" is > 0 and the step has certain problems, the step is "retried" after "Retry Interval".

But I am uncertain as to the details. Are the retries within one database transaction? Does each "try" get its own transaction? And what about connection? Is the "retry" done with the same connection? I know this may sound funny but I would like to know exactly what is going on here.

Thanks,

I am pretty sure that each retry spawns its own transaction - any failed attempt will rollback the current transaction. And the retry is done in the same connection unless it's a retry due to a connection issue. I can try to clarify later.

Distribution job failed

When the distribution agent runs trying to apply the snapshot at the subscriber I get the following error 20253 sql 2005

End of file reached , terminator missing or field data incomplete in SQL 2005

Consult the BOL for more information on the bcp utility and its supported options.

To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below.
Consult the BOL for more information on the bcp utility and its supported options.

It could be that the snapshot files for one of your articles is corrupted. Try manually deleting the snapshot files from the snapshot folder and start over - regenerate the snapshot and try to apply it.

If it still fails, you need to tell us what's different about the article that's failing: what kinds of datatypes are in the table, did you manually edit any of the bcp files, what kind of subscriber is this, is this oracle publisher, etc.

|||

Hi,

Did you ever get this issue sorted?

I am receiving the same error message on 1 table during the snapshot sync phase on SQL2005 and it is failing the sync.

Cheers

Distribution job failed

When the distribution agent runs trying to apply the snapshot at the subscriber I get the following error 20253 sql 2005

End of file reached , terminator missing or field data incomplete in SQL 2005

Consult the BOL for more information on the bcp utility and its supported options.

To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below.
Consult the BOL for more information on the bcp utility and its supported options.

It could be that the snapshot files for one of your articles is corrupted. Try manually deleting the snapshot files from the snapshot folder and start over - regenerate the snapshot and try to apply it.

If it still fails, you need to tell us what's different about the article that's failing: what kinds of datatypes are in the table, did you manually edit any of the bcp files, what kind of subscriber is this, is this oracle publisher, etc.

|||

Hi,

Did you ever get this issue sorted?

I am receiving the same error message on 1 table during the snapshot sync phase on SQL2005 and it is failing the sync.

Cheers

Distribution database is 9 GBs

Our distribution database in up to 9 GBs, after only 3 weeks...
What job cleans out data from this database?
What do I need to check to verify this?
Thanks and God Bless,
ThomBeaux
Is that all. What's your secret?
Check space usage to see if this is the actual size and ensure its not the
transaction log bloat.
The distribution database is a queue which is purged by the distribution
clean up task. For anoymous subscribers you can get data hanging around till
the retention period has elapsed (or the lesser of the retention period and
the history).
It is normal for it to grow quite large if your publisher is replicating a
lot of commands.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ThomBeaux" <ThomBeaux@.NoSpam.Com> wrote in message
news:9421D30A-1F0D-4CC8-8980-00C09DDA6837@.microsoft.com...
> Our distribution database in up to 9 GBs, after only 3 weeks...
> What job cleans out data from this database?
> What do I need to check to verify this?
> --
> Thanks and God Bless,
> ThomBeaux
|||Primary Data is: 12.68 GB , 12.67 Used
Transaction Log: 9.9 GBs, 81.8% used.
Log and data files are growing by the hour.
What Agent Job should I watch to see if it is being cleaned out?
This system was just updeated to 2005, and we never had a distribution
database larger than 4-5 GBs in 2000, with the same system.
Thanks for the responce Hillary. I see your signature on lots of rpelication
issues, and it is appreciated. Will you be at PASS in denver?
Thanks and God Bless,
ThomBeaux
"Hilary Cotter" wrote:

> Is that all. What's your secret?
> Check space usage to see if this is the actual size and ensure its not the
> transaction log bloat.
> The distribution database is a queue which is purged by the distribution
> clean up task. For anoymous subscribers you can get data hanging around till
> the retention period has elapsed (or the lesser of the retention period and
> the history).
> It is normal for it to grow quite large if your publisher is replicating a
> lot of commands.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ThomBeaux" <ThomBeaux@.NoSpam.Com> wrote in message
> news:9421D30A-1F0D-4CC8-8980-00C09DDA6837@.microsoft.com...
>
>

Distribution Cleanup Job Fails

The distribution cleanup job that runs for our transactional replication on
sql server 2005 fails.
The job is:
EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 72
The Error is:
Msg 20015, Level 16, State 1, Procedure sp_MSreplremoveuncdir, Line 83
Could not remove directory
'\\sprs76\rysrepldata\unc\SPRS76_RYS_RYS_OPERATION ALTOREPLICAHIST\20060713140760\'.
Check the security context of xp_cmdshell and close other processes that may
be accessing the directory.
Replication-@.rowcount_only parameter must be the value 0,1, or 2. 0=7.0
compatible checksum. 1=only check rowcou: agent
RYSSprs76_distribution@.rowcount_only parameter must be the value 0,1, or 2.
0=7.0 compatible checksu scheduled for retry. Could not clean up the
distribution transaction tables.
If I remove this directory manually, would it cause the replication to
fail? I have tried this on a Test server and it seems that the replication
fails.
I tried to run this job in the Query window and it return this same error.
Our data file is growing and I cannot shrink it because of this issue.
What can i do to resolve this?
Thanks,
George Gopie
There's an MMC snapin to look at shared folders and their connections and
open files. This snapin could be used to drop the connections where needed.
If you have the folder open yourself locally, this snapin won't pick it up,
so you'd have to use the task manager to see what is likely to be accessing
it.
Another possibility is to drop the folder yourself then recreate it for the
distribution agent to later delete.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||No it would not cause replication to fail, only remove this portion
20060713140760
Make sure that xp_cmdshell is enabled and the SQL Serer agent account has
rights to list files and folders and delete files on the root of the
repldata directory and it subdirectories.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"georgeg" <ggg@.hotamil.com> wrote in message
news:DFE68859-01CF-4236-AC2A-F9CA596B9482@.microsoft.com...
> The distribution cleanup job that runs for our transactional replication
> on
> sql server 2005 fails.
> The job is:
> EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
> @.max_distretention = 72
> The Error is:
> Msg 20015, Level 16, State 1, Procedure sp_MSreplremoveuncdir, Line 83
> Could not remove directory
> '\\sprs76\rysrepldata\unc\SPRS76_RYS_RYS_OPERATION ALTOREPLICAHIST\20060713140760\'.
> Check the security context of xp_cmdshell and close other processes that
> may
> be accessing the directory.
> Replication-@.rowcount_only parameter must be the value 0,1, or 2. 0=7.0
> compatible checksum. 1=only check rowcou: agent
> RYSSprs76_distribution@.rowcount_only parameter must be the value 0,1, or
> 2.
> 0=7.0 compatible checksu scheduled for retry. Could not clean up the
> distribution transaction tables.
>
> If I remove this directory manually, would it cause the replication to
> fail? I have tried this on a Test server and it seems that the replication
> fails.
> I tried to run this job in the Query window and it return this same error.
> Our data file is growing and I cannot shrink it because of this issue.
> What can i do to resolve this?
> Thanks,
> --
> George Gopie
>

Distribution Cleanup Acting Weird

Hi.
In a previous post I asked if there was anything that we could do to
help the cleanup job run better. We then realized that we had over
12,000,000 records in MSreplcommands.
We have been "reducing" the max retention parameter. It was sitting at
72 hours. We started reducing this parameter by 1 hour, then 2 hours,
then 4 hours and the job would complete in about 4-5 minutes. It would
delete approximately 5000 rows/sec.
Suddenly the same job takes several hours while deleting around 80
rows/sec. This is horrible. Deleting 4 hours of old command takes 4
hours. I'll never be able to catch up.
Is there anything that we can do to bring back the 5000 rows/sec
performance of the earlier calls? What would cause the performance of
this job to decrease so drastically?
We're at a point where we are contemplating rebuilding the distributor
to start with a clean slate and set the distribution cleanup to a low
max retention figure (like 1 or 2 hours). We're hoping that with such a
low figure the job will not take so long to run and not block
replication for more than a few seconds.
One more thing. We're going to run without the distribution cleanup job
for the weekend where we have most of our activity. Will there be any
issues with replication if the distribution tables get super huge.
Regards,
Canadian Gambler.
*** Sent via Developersdex http://www.codecomments.com ***
You may be experiencing blocking betweek the cleanup agent, the logreader
agent which writes to the MSreplcommands table and the distribution agent
which reads from the same table. I'd recommend suspending the log reader and
distribution agents when you give the cleanup agent a decent time to clear
out the table. Your transaction logs may get large during this time, and
you'll need to be careful not to stray anywhere close to the distribution
retention period. If this succeeds, run the log reader and distribution
agents and suspend the cleanup agent while they clear the backlog from the
transaction log.
The above applies if the issue is indeed blocking. When dealing with several
million transactions per day I also got into the situation where the
MSreplcommands table balooned and transactions weren't removed even after
being applied to the single subscriber. Only when the retention period was
reached would they be removed. If this applies to your case, consider
synchronizing then really reduce the retention period.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the help.
this morning we pointed all the ODBCs to the main database, stop
replication and ran the CleanUp job. It took as long to complete.
We will have to synchronize next week, blow up the distribution database
and start fresh. Any issues setting the max retention as low as 1 or 2
hours.
Regards,
Canadian Gambler.
*** Sent via Developersdex http://www.codecomments.com ***
|||A low value for the distribution retention period is a bit risky. You're
assuming that all the transactions will get delivered tothe subscriber
during this period, and if there is a network problem, you'll have to
remember to immediately stop the cleanup agent. I've only ever set a low
value after synchronization to remove the redundant records from
MSreplcommands and then reset afterwards.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Distribution cleanup

I would love to be able to run the distribution cleanup job with a switch that says cleanup all distributed transactions.

Because when I use peer to peer replication the @.allow_initialize_from_backup publication property is set to true which is good. But it has the down side that transactions are stored the max retention period in the distribution database. I want to use the deafault 72 hours for my retention period so that the subscritions don't get deactivated but in a system with a high transaction rate there wil be a lot of transactions in 72 hours. This means that the cleanup job will have a tough time to figure out which transactions to delete so the cleanup job will run for a long time not a very big problem but the problem is that the cleanup job will keep the log reader agent from delivering trtansactions to the distribution database and the subscribers won't get their data in time.

Could Microsoft please give me a switch so I can choose when I want to save my transaction and when I want to delete them as soon as they have been delivered to all subscribers?

Is this a feature in SQL Server 2008? Could it be released in SP3 for SQL Server 2005. (The SP 2 cleanup job has a bug so I have to use the SP 1 verison of the cleanup job)

just out of curiosity what is the bug in SP2 cleanup job? Do you happen to have a bug number or description, i can look it up and see if there's a workaround for your problem.|||

I don't have the bug number My tech lead at Microsoft will come back to me when the dev team has made the correction. The workaround I use is to run the SP1 versions of some cleanup procs.

This workaround atleast lets me delete the records older than max retention.

But I still would lit to be able to give two dates to the cleanup job. One for checking the subscription deactivation and one for transaction cleanup.

Distribution clean up: distribution job not removing snapshots

On SQL Server 2005 SP2 for Publisher and Distributor on the same instance, my old snapshots are not being cleaned up.

The following error is in the agent history:

Executed as user: Domain\MyUser. Could not remove directory '\\vmsql01\ReplData\unc\Publication_TRANSACTIONAL\20070702104416\'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.

xp_cmdshell is enabled and I can run commands like :

exec master.dbo.xp_cmdshell ' md c:\TestFolder\'

The permissions to the snapshot share and file system are that Domain\MyUser has full control.

I have logged into the machine as this user and can remove snapshots so it does not seem to be a permission issue.

On other machines I do not get any errors but the snapshot folder still is not cleaned up.

Any suggestion as to what the problem could be?

Thanks,

Amy

hi amy,

i think there's a sql job in msdb

if i quite remember well to do the

clean up

you may manually trigger

regards,

joey

|||

Update:

I ran filemonitor while the cleanup job was running and noticed it was telling me the user that owns the SQL Server Service had access denied errors. I gave that user permissions to the snapshot folder and it is now cleaning up (well any of the really old stuff it is not but anything that has expired lately it has cleaned up).

My SQLUser1 owns the SQL Server Server and SQLUser2 owns the SQL Server Agent Service.

I thought the SQLUser2 would be the one who required permissions to the snapshot folder to clean it up?

Does xp_cmdshell need to be enabled for the cleanup job to work? I have not found anything in BOL to suggest that but ...

The other machines are still running cleanup jobs without error and still not removing any snapshots.

Any suggestions?

Thanks,

Amy