SQLmaint Obsolete In SQL2005!?!
Jan 7, 2005:mad: It seems that the SQLmaint is going to obsolete in SQL 2005, what happen to my "sophisticated" SQLmaint scripts!!!! Any help or comment?
View 8 Replies
ADVERTISEMENT
Obsolete Snapshot
Oct 17, 2002Hi everybody,
We have merge replication between two servers with Sql Server 2000 Service Pack 2, and the Merge agent display the follow message :
The snapshot for this publication has become obsolete. The snapshot agent needs to be run again before the subscription can be synchronized.
But i have changes at the suscriber, How can I do to Merge the last changes if the merge agentes is stopped.
I appreciate any help.
thanks
Obsolete Objects
Apr 23, 2008
Hello,
I need to find all of the SQL Server objects, (tables, procs, functions, etc.) that my application is NOT using, so they can be removed from the system. Does anyone know how to accomplish this?
Thank you,
Dave
Obsolete Table Columns
Feb 4, 2005When I find that a column should no longer be used because of design changes. ( Moved column to other table to correct design normalization etc.)
I added a check to make sure the column must be NULL.
The following list the obsolete columns
SELECT col.TABLE_NAME + '.' + col.COLUMN_NAME AS FULL_COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS col
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON col.COLUMN_NAME = ccu.COLUMN_NAME AND col.TABLE_SCHEMA = ccu.TABLE_SCHEMA AND col.TABLE_NAME = ccu.TABLE_NAME
JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
ON ccu.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA AND ccu.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE IS_NULLABLE = 'YES' AND cc.CHECK_CLAUSE = '([' + col.COLUMN_NAME + '] IS NULL)'
ORDER BY col.TABLE_NAME, col.COLUMN_NAME
In the past columns could not be set to null.
This script list those columns that I need to add null checks to.
SELECT col.TABLE_NAME + '.' + col.COLUMN_NAME AS FULL_COLUMN_NAME, cc.CHECK_CLAUSE
FROM INFORMATION_SCHEMA.COLUMNS col
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON col.COLUMN_NAME = ccu.COLUMN_NAME AND col.TABLE_SCHEMA = ccu.TABLE_SCHEMA AND col.TABLE_NAME = ccu.TABLE_NAME
JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
ON ccu.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA AND ccu.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE cc.CHECK_CLAUSE LIKE '([[]' + col.COLUMN_NAME + '] =%' AND
NOT cc.CHECK_CLAUSE LIKE '% OR %' AND
NOT cc.CHECK_CLAUSE LIKE '% AND %' AND
NOT cc.CHECK_CLAUSE LIKE '% IS %' AND
NOT cc.CHECK_CLAUSE LIKE '% IN %'
ORDER BY col.TABLE_NAME, col.COLUMN_NAME
Tim S
Snapshot Files Obsolete?
Jun 1, 2007I recently had a problem with one of my publications that I haven't been able to figure out. Hopefully someone can help me with it.
I got this error last week: You must rerun snapshot because current snapshot files are obsolete. I assume this means the subscription needed to be re-initialized. Is this correct?
If so, I don't understand why. The publication was not changed. The subscription did not expire. The subscription was not marked for reinitialization.
Here are the specifics of my installation:
Merge publication with 1 pull subscription.
Subscriptions set to expire after 14 days.
Merge agent runs every day, every 5 minutes between 12:00:00A & 11:59:59P.
Snapshot agent was run once when publication was created and then disabled.
Publisher SQL ver: 2000 sp3
Publisher Win ver: 2003 sp1
Subscriber SQL ver: 2000 sp3
Subscriber Win ver: 2003 sp1
The subscription was successfully replicating every 5 minutes. The data for 12:35 shows the first problem. Here's the agent history at the time:
runstatus
start_time
comments
error_id
1
5/23/07 12:25
Initializing
0
3
5/23/07 12:25
Connecting to Publisher 'AASTAUSSQL01'
0
3
5/23/07 12:25
Uploaded 4 data changes (3 inserts, 1 updates, 0 deletes, 0 conflicts).
0
3
5/23/07 12:25
Downloaded 7 data changes (2 inserts, 5 updates, 0 deletes, 0 conflicts).
0
2
5/23/07 12:25
Merged 11 data changes (5 inserts, 6 updates, 0 deletes, 0 resolved conflicts).
0
1
5/23/07 12:30
Initializing
0
3
5/23/07 12:30
Connecting to Publisher 'AASTAUSSQL01'
0
3
5/23/07 12:30
Uploaded 4 data changes (3 inserts, 1 updates, 0 deletes, 0 conflicts).
0
3
5/23/07 12:30
Downloaded 20 data changes (14 inserts, 6 updates, 0 deletes, 0 conflicts).
0
2
5/23/07 12:30
Merged 24 data changes (17 inserts, 7 updates, 0 deletes, 0 resolved conflicts).
0
1
5/23/07 12:35
Initializing
0
3
5/23/07 12:35
Connecting to Publisher 'AASTAUSSQL01'
0
2
5/23/07 12:35
You must rerun snapshot because current snapshot files are obsolete.
2
1
5/23/07 12:40
Initializing
0
3
5/23/07 12:40
Connecting to Publisher 'AASTAUSSQL01'
0
2
5/23/07 12:40
You must rerun snapshot because current snapshot files are obsolete.
3
1
5/23/07 12:45
Initializing
0
3
5/23/07 12:45
Connecting to Publisher 'AASTAUSSQL01'
0
2
5/23/07 12:45
You must rerun snapshot because current snapshot files are obsolete.
4
Any help with this would be greatly appreciated.
Thanks!
The Snapshot For This Publication Has Become Obsolete !
Nov 21, 2006Hi,
I am in need of some help with this error - I am seeing on my PDA units. Here is my scenario:
I have 5 PDA units that use merge replication with SQL 2005. These PDA units replicate fine. But when I try to sync a 6th or 7th PDA unit, I see the following error:
Source : Microsoft SQL Server 2005 Mobile Edition - The snapshot for this publication has become obsolete. The snapshot agent needs to be run again before the subscription can be synchronized. -2147198698
I know that if I regenerate a new snapshot, I will be able to sync fine. But here are my concerns and questions:
What happens to the first 5 PDAs when I regenerate the a new snapshot.
Will they be able to sync without losing the data?
Does generating a new snapshot, relinitialize all the subscriptions?
Am I missing the whole picture with generating a new snapshot?
Thanks,
P
Cube Has Been Updated By The Server, Data Is Now Obsolete
Mar 22, 2004Client issues query which sends out individual requests to the 2 OLAP servers that are load balanced. The client evaluates the versions of the returned record sets to ensure the consist data being returned for one single query. Otherwise, this error will be seen:
The cube has been updated by the server the data is now obsolete.
These servers sit behind a cisco 11506 CSS with load balancing based on balance type: least busy server, also persistence based on cookies.
My developer says this worked fine for a long time then just 'started happening'.
any suggestions are appreciated.
Sql2005 Database Restore From Another Sql2005 Backup File Error.
Dec 15, 2005hi
i try to restore a bak file from another sql2005 server to my sql2005 server, but it show the error message as below :
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupackup.bak'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
pls some one can help me ???
thanks
chaus
Can You Open A Database Created In SQL2005 In SQL2005 Express?
Oct 12, 2007
Can you open/use a database created in SQL2005 in SQL2005 Express?
Thanks for the help!
Max
Help!! SQLMAINT
Feb 7, 2002Is anyone using the -DelBkUps option with their maintenance plan on
SQL 7.0?? if so is it actually deleting the files? Mine is running
but not deleting any of the old backup files.
thanks
SQLMAINT 6.5
Oct 6, 1999I would like to run the following SQLMAINT command on my database every
14days:
At 2:00 AM, this command needs to kick off: SQLMAINT.EXE -D cabinet
-CkAl -CkCat -UpdSts -RebldIdx 100 -Rpt D:MSSQLLOGcab_maint.rpt
Question: How can I "kill" all users, then place the database in "dbo"
mode, run "SQLMAINT", and then release database access back to the
public - all in an automated process.
Any suggestions would be "GREATLY" appreciated.
Thanks,
Chris
SQLMaint.exe
Jan 15, 2002Was wanting to get some feedback on using sqlmaint to produce database backups in a production environment. Especially on databases > 10GB. My preference is not to use it but I'm arguing with another DBA and wanted some outside feedback.
Thanks.
Carol
Sqlmaint.exe
Jun 22, 2004With MSDE 2K I get the following error message when trying to execute scheduled db maintenance/integrity checks:
"Failed executing API: CreateProcess. [SQLSTATE 42000] (Error 22029). The step failed. "
The Microsoft website tells me this is because sqlmaint.exe is not installed and that installing the latest service pack will solve the problem.
I installed the SP3a and sqlmaint.exe is in the following location:
C:/MSDE/Binn
But I still get the error...
Any Ideas?
SQLMAINT And Backups
Oct 30, 2001I am a big fan of the backup naming convention used by maintenance plans and the SQLMAINT utility. My problem is that I want to use the same naming convention "db_name_diff_YYYYMMddhhmmss" because of my situation: I'm in California, my databases are in Pennsylvania, I don't have domain access to the servers, I use a scheduling tool (required) that only uses T-SQL or cmd line utilities, and I want the backups named as above. SQLMAINT provides for full and tlog backups, which I have set up, but I want to do diff backups the same way, with the same naming convention. Any ideas?
View 1 Replies View RelatedSqlmaint Cmd Line
Jan 10, 2000I am having problems running sqlmaint against my servers.
From a command line I am running c:sqlmaint -S servername -U "username" -A "password" -D database -UpdSts as a test.
All i get is the list of arguments displayed. This never seems to work.
Any Ideas??
SQLMAINT.EXE Locking Up
Feb 3, 1999I run the SQLMAINT.EXE utility nightly to do DBCC checkdb, checkalloc, and checkcat checks on all databases. On one server this check seems to lock up the server displaying error 17832, "unable to read login packets". Once this occurs the only way we can find to get out is to reboot the NT Server. We have observed this behaviour once every two or three weeks. Anyone seen anything similar?
View 1 Replies View RelatedMS SQL Question - SQLMAINT.exe
Dec 15, 1998Does anyone know where I could get details on the various switches SqlMaint.exe offers and descriptions of each switches capabilities.
I know by typing in "sqlmaint", it will list all the various switches available but there is not description of what each one exactly means.
Thanks in advance. JLS
Sqlmaint.exe Failed
Jun 5, 2001Can anyone tell me how to troubleshoot the following error:
'sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.'
Thank you,
Ryan
Sqlmaint.exe Failed
Sep 17, 2001"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
I get this error message when trying to execute one of the jobs created by the maintenance plan wizard.
Any ideas what caused this error?
Question About Sqlmaint
Sep 22, 2005hi guys !
I have a simple question , i am using the following command to backup transaction logs :
exec dbo.xp_sqlmaint '-D Northwind -BkUpMedia DISK -BkUpLog "d:sqlackup" -BkExt "TRN" '
does it truncate the tranlog after backup i.e remove the unused entries from there ?
thanks
Sami
P.S: can you please CC me with your answer at esa019c@motorola.com ?
Sqlmaint.exe And -HTMLRpt
Apr 11, 2004SQL2000 SP3a I would like to generate an HTML output file as a report which should be possible. After creating the maintenance plan using the wizard I used the created syntax and established a second job which now uses the changed syntax - however the job doesnt run. In the log file it just states the basic sqlmaint switches.
Current job syntax - SQL uses build in system account.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 89CA81E2-181C-42F9- B39C-59EE21936079
-HTMLRpt "C:SystemDbs.html"
-DelHTMLRpt 1DAY
-VrfyBackup
-BkUpMedia DISK
-BkUpDB
-UseDefDir
-DelBkUps 2WEEKS
-CrBkSubDir
-BkExt "BAK"'
Sqlmaint.exe Failed
Sep 12, 2005I have a maintenance plan on a client's site, there's only one databasein the plan and it's set to backup db and log, this job failsconsistently. The one step in the job contains this line:EXECUTE master.dbo.xp_sqlmaint N'-PlanID392B2334-6800-4655-A6C7-5414D9072505 -WriteHistory -VrfyBackup-BkUpMedia DISK -BkUpDB "C:Program FilesMicrosoft SQLServerMSSQLBACKUPDailyBackups" -DelBkUps 4WEEKS -BkExt "BAK"'Which fails if run in QA with the message:Server: Msg 22029, Level 16, State 1, Line 0sqlmaint.exe failed.I've searched for this error and cannot find anything to help.There's no "-S" in the path, it writes absolutely no history on theplan. I set the plan to write history in the log directory on theserver, it wrote nothing to that directory.The database doesn't get backed up but is able to be backed up manuallyto the path specified in the plan. I also have a separate job with aSQL backup statement in it that works OK so SQLAgent is working.
View 9 Replies View RelatedSqlmaint.exe Failed
Sep 6, 2007
I am using SQL 2000,everyday we are running database backup plan but it shows a error like "Executed as user: NT AUTHORITYSYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
Our database recovery model is also in Full mode.Burt i don't know why its happening with us,before that database maintenance plan was working fine but for last 2 months its not working.
thanks,
-Amit
Upgraded SQL2000 To SQL2005, SQL2005 VERY Slow
Dec 27, 2005I just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?
View 3 Replies View RelatedSql2005 Standard To Sql2005 Enterprise Edition
Jan 4, 2007Hi
We have Sql2005 x64 bit standard edition server installed in windows 2003 64 bit editio server,
currently due to buisness requirements we need to have sql2005 x64 bit enterprise edition, please let me know how do i do the upgrade or change.
is it possible to retain all our custom settings in the standard edition after changing to enterprise edition.
This has to be done for our production and very critical, please help
Thanks
Samuel I
SQLMAINT.EXE -- Returns Only The Switches
May 13, 2003Hi,
I am trying to run the following:
SQLMAINT.EXE -USA -P -S abc -D db1 -CkDB -Rpt c:log_backup.rpt -DelTxtRpt 1days
But it simply returns the
SQLMaint switches and doesn't run the actual -CkDB?
Do I need to register any DLL to make this work ?
Thanks in advance,
SQLMAINT - No Message Error
Feb 24, 1999I have a situation where I am running sqlmaint on 10 - 12 databases on
the same server. All of them run successfully; However, one returns a
failure because of no message. MSDB is not full. There is no messages
in the errorlog. And the actual SQLMAINT executed just fine.
Any Clues?
Sometimes MS SQL is very frustrating!!!!!!!
SQLMAINT.EXE AND INITIALIZING BACKUPS
May 12, 1999I'm running SQLMaint.exe for DBCC and backups overnight, because I like the -BkUpOnlyIfClean switch, but I'm having a problem. I need
Due to space constraints, I need to overwrite the backup every night. The -DelBkUps switch is nice, but it accepts a parameter of weeks, not days. Is there some syntax I can use with SQLMaint.exe to get what I want, or am I going to have to do an NT 'at' commnad to get rid of the file just before th enext dump, or what? Any suggestions would be intensely appreciated.
SQLMaint Error: 22029
Mar 19, 2001I installed a new SQL Server and implementd a maintenance plan for user databases. In doing so, all the jobs seem to work except the Integrity checks. These jobs fail with the following error:
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Has anyone seen this? Or has an idea how to fix it?
Thanks in advance.
way0utwest
Sqlmaint.exe Failure W/MSDE 2K
Jul 29, 2004I get the following error when I try to run the Integrity check aspect of my maintenance plan...
Executed as user: MISSIONAdministrator. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Microsoft indicates this is b/c sqlmaint.exe is not installed as part of MSDE 2K and that upgrading to latest service pack will solve.
The optimizations work fine...Is there a way to invoke the integrity check w/out using the maintenance plan wizard?
Question On Sqlmaint Scripts
Dec 15, 2006New to SQL Server.
I am looking at some legacy sqlmaint scripts.
If I don't see the switches
ReblIdx
or
UpdOptiStats
Does that mean that there are no database reorgs and no update system
catalog statistics being done?
The rebuild index switch is SQL Server speak for a reorg correct?
Thanks in advance.
Gerry
Sqlmaint.exe Failed When Backup Db
Jul 23, 2005Hi! I have a SQL 7.0. The backups created by maintenance plan workedvery well before someone shut down the server to do some servermaintenance. After restart the server, the backup job is not workingany more. All the backups created by maintenance plan are not working,including the newely created one. The message is "Server: Msg 22029, Level 16, State 1, Line 0sqlmaint.exe failed."Please help me out!! Thanks.Saiyou
View 1 Replies View RelatedDump Database Vs. Sqlmaint.exe -bkupdb
Oct 25, 1999Both "dump database" and "sqlmaint.exe -bkupdb" would be able to backup database, one important advantage I can see from "sqlmaint" is that it has the option to cleanup the very old generations of backups by using -delbkups option in a scheduled maintenance backup job, while the "dump database" would first create a dump device and it'll grow with time.
Is there any suggested way to use the "dump transaction" but still we can get control on the size of dasd used ?
Any negative part of using "sqlmaint" utitity to backup database as compared to the "dump database " utility ?
Anthony