Errorlog Retention
Apr 20, 2004I am trying to change the default number of SQL errorlogs from 6 to 12. Does anyone know how to change that?
View 7 Replies
ADVERTISEMENT
Writing Errorlog To Sql Errorlog Problem
Jun 14, 2001Hello all,
I don't know what I am doing wrong.
In my environment, we have monitoring tool that scan through sql errorlog
every 10minutes to capture some error that occurs.
I have been trying for days now to create an alert for any of our failed
backup to appear in the sql errorlog with the code below. After I add
message to the sysmessage table with sp_addmessage, and raising the error
with log option, I modified the existing scheduled backup job by creating
an alert with the num in the code.
For some reason, this error will not just show up in the sql errorlog when
it failed.
Has anyone any advise on what to do?
The code is:
sp_addmessage 50001, 25, 'Backup Failed for the '%.*ls' database.Please Contact MSSQL DBA Immediately!!!! '
GO
DECLARE @DBID INT
SET @DBID = DB_ID()
DECLARE @DBNAME NVARCHAR(128)
SET @DBNAME = DB_NAME()
RAISERROR (50001, 25, 1, @DBID, @DBNAME) with log
GO
Thanks
SA
Errorlog ..Help Please
Aug 14, 2000Hi!
Even though i see all the errorlog files physically stored in the log directory, I am not seeing any errorlog info on Enterprice Manager. I see all files like errorlog, errorlog1, errorlog2, etc, when i click current errorlog, it shows nothing, Please help me what could be the problem?
Appreciate all your ideas.
Mohan
Errorlog
Dec 9, 1998what does the error "login failed-User: Reason : Not associated with a trusted
SQL Server connection" means in the errorlog? it keeps on writing in the errorlog. what could be the caused of this? and how to correct this?
thanks a lot!
Retention Counts
Dec 4, 2007I have members in a database who have paid thru dates. I am creating retention reports
I created a cross tab in Crystal (using SQL) that counts records that paid within a certain year. I need to create a script that will let me find when members skip payment for a year. Any ideas?
I was thinking of running a count of all paid (Activity) records, but still kind of stuck.
DZ
Clear Errorlog In SQL 6.5
Sep 25, 2003Hi....Does anyone know if there is a similar command to SQL2K's DBCC ERRORLOG.
I'm currently writing a preventative maintenance script and want to force archiving of errorlogs. Any help greatly appreciated.
Error In Errorlog
Aug 13, 2002Hello-
I am getting a recurring error in my errorlog. This is the error:
"BlkHeader from strip 0 At 13531e00 ExpectedAt 13531e00 Size c00 PrevSize 200"
Has anyone seen this before?
Thanks,
Traci McPartland
ErrorLog Is Your Friend!
Dec 22, 2005I just spent the better part of a full day trying to figure out why my MSSQLSERVER service wouldn't start. Finally, after someone suggested I check out "C:Program FilesMicrosoft SQL Server[Instance Name]LogErrorLog" I found my problem. I opened it up to find: (newbie mistake, I know now!!)
2005-12-21 19:27:46.60 server SQL Server evaluation period has expired
So I thought I would pass this onto everyone I can think of so you will not make the mistake I just did.
It would have been nice if Microsoft, like just about every other software company out there, would have been loud & obnoxious about their trial software expiring. Instead they have to put a poor newbie soul thru what I just went thru.
*sigh*
Well if I helped 1 person with this, I've done some good!
Distribution Transaction Retention
Nov 6, 2007I currently have a simple transactional replication setup for a database. My publisher and distributor are on the same box. The subscription is setup using a push agent.
My question is related to recovery of the subscriber.
So lets say replication is setup and working fine. Suddenly we had a failure on the subscriber database. Now I could just reconfigure the subscription, and the subscribing database would be back up and good to go, but the problem is that over time, we have made some changes to the subscribing database that are not made in the publisher. For example, the tables have different indexes. Just reconfiguring the subscritpion would not recover these objects.
So I have to acutally restore the subscriber database. So I do that, and apply transaction logs up to the most recent transaction log backup. Now, consider that my transaction log backups on the subscriber happen every 4 hours, and the most recent transaction log backup I had was from 3 hours ago. So now at this point, my subscribing database is 3 hours behind my publisher.
Now, will the distribution agent resend the missing 3 hours of transactions?
In the distribution agent properties, there are two settings for transaction retention, "at least" and "but not more than". Currently they set to 0 and 72 hours respectivly. Now I would assume that if I set the "at least" setting to the subscriber transaction log backup period, in this case 4 hours, I would be covered, and the distribution agent would indeed re-replication the transactions that happend since the recovery point 3 hours.
I just wanted to verify that this is acutally what these settings are refering too, and that if I set the "at least" setting to 4 hours, I would be covered.
- Eric
Which Algorithm Is Better For Customer Retention
Jul 25, 2006Hi
Pl any one tell me which algorithm is better for Customer retention Using SQL server 2005 analysis services
It will be great if some one can give the same with example of data model with key column , and rest
Thanks in Advance
Rajesh Ladda
Transaction Retention - Just How Long Is Best?
Sep 19, 2007I'm curious what are considerations for choosing a good transaction retention time? The default SQL uses is 0 to 72 hours. With this setting I found that cleanup was taking upwards of 30 minutes (for a process that defaults to run every 10 minutes). I've read that lowering it can improve performance, and that also you don't want this running too long because of deadlock issues between this and the log reader. So how short is too short? Optimally, since the system this runs on is under heavy use I'd like to optimize this as much as possible, which makes me think that the smaller the retention the better, but is something like 1 or 2 hours too short? What are possible consequences of such a short period of time?
View 2 Replies View RelatedMoving Errorlog Files
Sep 17, 2001I need to move the errorlog files from the d: drive to the e: drive on my NT servers. Does anyone know a way to accomplish this without having to re-install?
Thanks
tcb
Errorlog Keep Appending -urgent
Jan 19, 2000We're running SQL 6.5 SP3, we recycle our SQL server every day, somehow starting last December, the errorlog kept appending to the previous one
without starting a new log, and it keeps on growing,
any one knows anywhere I should look into ?
If SQL behaves properly, it should starts a new log after each recycle.
I checked from Technet this problem may occur in 4.2 but I haven't seen anything in 6.5....Thanks
Anthony
Errorlog Monitoring && Job Outputs
Nov 2, 2004Considering email is setup, how would you send emails by scanning errorlog for any issues. Also, how to send program outputs using email.
Thanks
ErrorLog Size Limit
Sep 6, 2006Hi.
I have a service on a server which contains Sql Server Express.
this service adds documents to the database to allow full text search features.
From some reason, the index crashes and than it writes to the ErrorLog file, the errorlog jumps to 20GB(!) causing the server to crash.
How can :
1. limit the size of the error log.
2. find out why the log jumps to this size (I can't open the error log file ofcourse...)
Thanks!
Backup Data Retention Time?
Nov 12, 2007I have just started in the scary world of SQL Server admin and am trying to unravel the mysteries of backups etc.
If I run 'BACKUP DATABASE xxx TO DISK = 'D:DB_Backupsxxx.bak' WITH RETAINDAYS = 7' each day, each db backup if appended to the same '.bak' file and the RETAINDAYS protects the backup from being deleted by SQL Server. OK so far. But does anyone understand what criteria is used to decide when to overwrite the older backups? My backup file is getting bigger everyday, with no sign of any of the old data being deleted! Do I have to wait for the entire disk to become full before they start to get overwritten? Or should I just not worry and trust that it will do it all correctly?
Any ideas would be much appreciated.
Backup Retention Period Setting
Jul 13, 2006In sql2005 the database backup retention has been added in sql server properties in database setting.
In 2000 we had a comfortable option to set retention based on maintenance plan,files and also our space availabilty.It has helped the dba's a lot.But it has been removed in sql 2005.
Is that sql server setting is the only retention period setting or do we have to set in anyother tabs..
Thanks
Log Shipping History Retention Issue
Sep 11, 2007I want to change the history retention time because the history stores about 1 gb of detail per database per day in the msdb
Some of the log shipped databases have a monitor server option that has a setting for retention time
but most of the log shipped databases are not using a monitor server since the option was unavailable at setup.
So is there a way to change the history retention time
Print A Dump File For Errorlog
Aug 4, 1999Hi,
How print out a readable dump by using PRINTDMP, I didn't catch up the
parameters that I have to pass.
Thanks,
Herve Meftah
Using Findstr On SQL 2005 ERRORLOG File
Feb 28, 2008We have set up a couple of SQL Server 2005 systems and I have foundthat the format of the ERRORLOG files and the SQL Agent's log filesare Unicode or some format that findstr cannot parse properly. "find"parses them fine, but it doesn't have the capabilities that I need --specifically, I can't search for multiple strings in one search.I see the checkbox on the SQL Agent's for "Write OEM File", but it isgrayed out so I am not able to try checking that. I also don't knowif that would affect the server's ERRORLOG file too or just theAgent's log file.So what am I missing? What is everyone else doing who is used tohaving scripts to parse these files looking for strings that indicateproblems? Is there a server setting that will force it to go back toa plain ANSI text file format for log files? Is that a bad thing todo?Thanks in advance for any insight,Teresa Masino
View 4 Replies View RelatedRetention-based Meta Data Cleanup
Apr 18, 2005Hi
I am running a couple of sql 2000 SP3a servers with merge and snapshot replication. One server acting as publisher and distributor and the rest subscribers. On one of the server I have got the error below and have tried most of the suggestions by msdn. This server has not crashed ever before or any hardware problems. It has been running for a couple of months and no problems. This has not happened no any of the other servers. Any suggestions would be greatly appreciated as the only resolution I have left is to bring up a new instance, setup replication and see if this would resolve the issue. Stopping and starting of agents don't work.
[4/18/2005 11:59:20 AM]BRAAMPDC1ICAS2000.HO_Master: {call sp_MSgetversion }
[4/18/2005 11:59:20 AM]BRAAMPDC1ICAS2000.distribution: {call sp_MShelp_subscriber_info (N'BRAAMPDC1ICAS2000', N'EASTSRV3')}
Connecting to Subscriber 'EASTSRV3.ICASData'
Server: EASTSRV3
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/18/2005 11:59:27 AM]EASTSRV3.ICASData: {call sp_MSgetversion }
Percent Complete: 2
Connecting to Subscriber 'EASTSRV3'
Percent Complete: 3
Retrieving publication information
Percent Complete: 4
Retrieving subscription information
Percent Complete: 4
The merge process is cleaning up meta data in database 'HO_Master'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
The merge process is cleaning up meta data in database 'ICASData'.
The merge process could not perform retention-based meta data cleanup in database 'ICASData'.
Percent Complete: 0
The merge process could not perform retention-based meta data cleanup in database 'ICASData'.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147199467
Message: The merge process could not perform retention-based meta data cleanup in database 'ICASData'.
Percent Complete: 0
Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_mergemetadataretentioncleanup(?, ?, ?)}
Percent Complete: 0
Category:SQLSERVER
Source: EASTSRV3
Number: 11
Message: General network error. Check your network documentation.
SQL 2012 :: Table Property For Retention Date?
Feb 21, 2014I want to store data warehouse source tables and files in an Archive schema and then delete / drop them after a specified period of time.
Is there a table property that I can set (can't find one) or some other mechanism so that I can easily identify these tables with a script.
If there is no such property or feature within the database engine I will define a metadata table and record it there, but a property or similar that I can set at archive time would be very handy.
Transact SQL :: How To Deduct Retention Amount From Arrears Payment
Aug 27, 2015We have retention policy , and pay at the time year completion , now policy change and it is converted from yearly to monthly and this with effect from April-15.
if calculate the pay system will generate the Arrear payment of the employee from the month of April onward but i already paid the retention amount for month for two month April and May which i need to deduct the same otherwise this will double amount .
Not Able To Connet To SQLServer- Memory Related Messages In SQL Errorlog
Dec 1, 2004Hi
I was not able to connect to SQL Server machine. On examining the Error log (which was huge 53MB), I found the following messages that filled 95% of the logfile. Is this something to do with memory allocation.
Someone, please let me know what is going on. After the server reboot everything works fine.
I am worrired that this message may occur again.
Thanks
Machilu
2004-11-30 20:15:03.64 logon Login failed for user 'NT AUTHORITYSYSTEM'
2004-12-01 08:15:03.77 logon Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
2004-12-01 00:47:25.28 spid70 WARNING: Failed to reserve contiguous memory of Size= 65536.
2004-12-01 00:47:25.31 spid70 Buffer Distribution: Stolen=127590 Free=4176 Procedures=182443
Inram=0 Dirty=14180 Kept=0
I/O=0, Latched=154, Other=10049
2004-12-01 00:47:25.31 spid70 Buffer Counts: Commited=338592 Target=338592 Hashed=24383
InternalReservation=357 ExternalReservation=0 Min Free=256
2004-12-01 00:47:25.31 spid70 Procedure Cache: TotalProcs=66212 TotalPages=182443 InUsePages=88547
2004-12-01 00:47:25.31 spid70 Dynamic Memory Manager: Stolen=310033 OS Reserved=38512
OS Committed=38457
OS In Use=38388
Query Plan=332158 Optimizer=0
General=15540
Utilities=8 Connection=473
2004-12-01 00:47:25.31 spid70 Global Memory Objects: Resource=10685 Locks=119
SQLCache=4540 Replication=2
LockBytes=2 ServerGlobal=45
Xact=201
2004-12-01 00:47:25.31 spid70 Query Memory Manager: Grants=0 Waiting=0 Maximum=92118 Available=92118
2004-12-01 00:50:04.10 logon Login failed for user 'NT AUTHORITYSYSTEM'.
2004-12-01 00:50:04.32 logon Login failed for user 'NT AUTHORITYSYSTEM'.
2004-12-01 00:51:08.78 spid70 WARNING: Failed to reserve contiguous memory of Size= 65536.
2004-12-01 00:51:08.82 spid70 Buffer Distribution: Stolen=138829 Free=5944 Procedures=169283
Inram=0 Dirty=14431 Kept=0
I/O=0, Latched=154, Other=9951
2004-12-01 00:51:08.82 spid70 Buffer Counts: Commited=338592 Target=338592 Hashed=24536
InternalReservation=360 ExternalReservation=0 Min Free=256
2004-12-01 00:51:08.82 spid70 Procedure Cache: TotalProcs=67783 TotalPages=169283 InUsePages=76116
2004-12-01 00:51:08.82 spid70 Dynamic Memory Manager: Stolen=308112 OS Reserved=38512
OS Committed=38457
OS In Use=38398
Query Plan=330249 Optimizer=0
General=15535
Utilities=8 Connection=476
2004-12-01 00:51:08.82 spid70 Global Memory Objects: Resource=10685 Locks=118
SQLCache=4540 Replication=2
LockBytes=2 ServerGlobal=45
Xact=202
Publication Retention To Increase, How To Avoid Missing Metadata After Cleanup
Mar 29, 2007I currently use 7 days for subscription expiration setting for my two merge publications, which will cause metadata to clean up very 7 days. Now I need to increase the retention period to be 14 days. How I can avoid missing metadata after cleanup? Microsoft ms151188 (http://msdn2.microsoft.com/en-us/library/ms151188.aspx) warns that publisher may not have enough metadata, which may lead to non-convergence. I want to change this setting without causing any data loss.
Thanks much,
Recovery :: How To Perform Server Database Backup Retention Periods
Sep 22, 2015For the best practice I issued full SQL Server database, differential and transaction log backups. I have setup a process to backup to local disks and then also copy the files to a centralized set of storage. On a weekly basis the centralized file system is backed up to a tape backup device. I know I can get data off of the tapes, but that process is time consuming, not well tested from my perspective and I am not in control of the overall process. Can you offer some recommendations from a SQL Server backup retention perspective?
View 6 Replies View RelatedDeadlock: Could Not Perform Retention-based Meta Data Cleanup
Sep 8, 2006Hi SQL Replication Gurus:
I got some issues in my production environment, so please help me out. The following is the message I got from the replication monitor and I don't what to at this point.
Appreciate you help.
Yong
==========================================================================================
Command attempted:
{call sp_mergemetadataretentioncleanup(?, ?, ?)}
Error messages:
The merge process could not perform retention-based meta data cleanup in database 'TT'. (Source: Merge Replication Provider, Error number: -2147199467)
Get help: http://help/-2147199467
Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Source: ply-db-svr1, Error number: 1205)
Get help: http://help/1205
[Microsoft][ODBC SQL Server Driver][SQL Server]Login Failed For User 'ErrorLog'.
Feb 22, 2005Hi, all
recently, I use the following script(somebody else) to create a database on a remote server: the script is as follow:
/*************************/
CREATE DATABASE [ErrorLog]
GO
Use ErrorLog
CREATE TABLE [dbo].[Errors] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[SessionID] [char] (12) NULL ,
[RequestMethod] [char] (5) NULL ,
[ServerPort] [char] (5) NULL ,
[HTTPS] [char] (3) NULL ,
[LocalAddr] [char] (15) NULL ,
[HostAddress] [char] (15) NULL ,
[UserAgent] [varchar] (255) NULL ,
[URL] [varchar] (400) NULL ,
[CustomerRefID] [varchar] (20) NULL ,
[FormData] [varchar] (2000),
[AllHTTP] [varchar] (2000),
[ErrASPCode] [char] (10) NULL ,
[ErrNumber] [char] (11) NULL ,
[ErrSource] [varchar] (255) NULL ,
[ErrCategory] [varchar] (50) NULL ,
[ErrFile] [varchar] (255) NULL ,
[ErrLine] [int] NULL ,
[ErrColumn] [int] NULL,
[ErrDescription] [varchar] (1000) NULL ,
[ErrAspDescription] [varchar] (1000) NULL ,
[InsertDate] [datetime] NOT NULL
) ON [PRIMARY]
--Create the user "ErrorLog"
if not exists (select * from master.dbo.syslogins where loginname = N'ErrorLog')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'Navigator', @loginlang = N'us_english'
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
select @logindb = N'master'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
exec sp_addlogin N'ErrorLog', 'secret', @logindb, @loginlang
END
GO
if not exists (select * from dbo.sysusers where name = N'ErrorLog' and uid < 16382)
EXEC sp_grantdbaccess N'ErrorLog', N'ErrorLog'
GO
Grant select, insert On Errors to ErrorLog
/***************************/
in my *.asp program, I used the following string to connect to the database on the remote server.
/********
con.open "dsn=ErrorLog;uid=ErrorLog;pwd=secret;"
********/
the following message comes up:
/********/
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ErrorLog'.
/*********/
what is the problem here?
when I set up dsn ErrorLOG, I used "abc" userID and password "XXX" which is our server database administrator assigned to me. I tested connection in odbc, it is OK
I just don't get, ther user ErrorLog already had login id and granted access to database errorlog. any clue, please help!
Betty