|
| | | 
Forum Guru
 
Group: Forum Members
Last Login: 6/22/2009 12:46:35 PM
Posts: 54, Visits: 86 |
| Ever since I upgraded to v12.x, I have been unable to get the built-in dB tools to function. Is anyone else having this same issue (how about IPSwitch, any help here would be great..these tools have been a serious issue since v11 came out, with all of us die-hards who are network admins and not dB admins, having to beg for helpand creating customized command-line maintenance scripts, but nothing out of IPSwitch on how to fix the issue in the program itself!)? Does anyone have a list of SQL commands, which can be used to perform the same functions that the built-in tools are supposed to be performing?? Thanks in advance to anyone who can make any specific suggestions, as dB Maintenance is a real pain in the butt right now, and even some of my old v11 command scripts do not function in v12.3.
------------------------------------------------------------
Bryan Harrell
Network Infrastructure - Tallahassee
Fla. Dept of Revenue
------------------------------------------------------------ |
| |
| | | 
Member of the WhatsUp Gold team
Group: Administrators
Last Login: 6/29/2009 4:20:10 PM
Posts: 513, Visits: 1,080 |
| Hi, Bryan,
Can you give some specifics on what's not working? Are you getting error messages?
We haven't seen any problems with database tools that I'm aware of, but I'd like to dig into your issues to find out what's going on.
__________________________________________________
Will Sansbury
Member of the WhatsUp Gold team
Twitter: @willsansbury
 | If you need help with anything, let me know!
|
Help make WhatsUp even better! Sign up for the WhatsUp Gold v14 Technical Preview Program!
|
| |
| | |
Forum Guru
Group: Forum Members
Last Login: 6/22/2009 12:46:35 PM
Posts: 54, Visits: 86 |
| Will Sansbury (11/10/2008)
Hi, Bryan,
Can you give some specifics on what's not working? Are you getting error messages?
We haven't seen any problems with database tools that I'm aware of, but I'd like to dig into your issues to find out what's going on.No error messages received, but if I check the table fragmentation under the Performance tools and select "Check for fragmented tables..." and then "optimize selected tables" using the built in tools, when I re-check, even after rebooting the server and my workstation after having flush my local I.E. cache, to be sure nothing culd be caught in memory, when I recheck the table fragmentation, it still shows the same numbers under the Max Index Frag - usually less than 100 no matter how long I let the services go before attempting them again or f I o it 10 minutes later (under v11.x and earlier versions of v12 prior to v12.3, this would "zero" all numbers indicating that the tables had been optimized)... Is it possible to get a list of the commands performed by the built-in tools so we can run them sequentially using a MS tools, to be sure that this is in fact broken on my system, and not an issue in our dB?
------------------------------------------------------------
Bryan Harrell
Network Infrastructure - Tallahassee
Fla. Dept of Revenue
------------------------------------------------------------ |
| |
| | | Supreme Being
Group: Forum Members
Last Login: 7/1/2009 4:31:27 PM
Posts: 107, Visits: 1,112 |
| | I've notice the same behaviour. Also, the file size and time stamp for both the mdf and ldf files do not change. That would seem to indicate (to me, at least) that nothing has been done to the db. |
| |
| | |
Member of the WhatsUp Gold team
Group: Administrators
Last Login: 6/29/2009 4:20:10 PM
Posts: 513, Visits: 1,080 |
| Are you guys running local or remote databases?
__________________________________________________
Will Sansbury
Member of the WhatsUp Gold team
Twitter: @willsansbury
| If you need help with anything, let me know!
|
Help make WhatsUp even better! Sign up for the WhatsUp Gold v14 Technical Preview Program!
|
| |
| | | Supreme Being
Group: Forum Members
Last Login: 7/1/2009 4:31:27 PM
Posts: 107, Visits: 1,112 |
| | I'm on a local SQLExp setup by the WUG 12.3 install. David |
| |
| | | Supreme Being
Group: Forum Members
Last Login: 7/1/2009 4:31:27 PM
Posts: 107, Visits: 1,112 |
| | I've been using this script that I found in the Ipswitch Knowledgebase. Does this do the same thing as the check for fragmented files and the validate and compact tools in the console? @echo off
set LOGFILE_DBMAINT=DB_Maintenance.log
set DSN=WhatsUp
set DB=WhatsUp
echo ***************************************************** >> %LOGFILE_DBMAINT%
echo %DATE% %TIME% : Starting maintenance >> %LOGFILE_DBMAINT%
echo %DATE% %TIME% : Starting maintenance
echo ***************************************************** >> %LOGFILE_DBMAINT%
echo %DATE% %TIME% : osql -E -D %DSN% -Q "DBCC SHRINKDATABASE (%DB%)"
echo %DATE% %TIME% : osql -E -D %DSN% -Q "DBCC SHRINKDATABASE (%DB%)" >> %LOGFILE_DBMAINT%
osql -E -D %DSN% -Q "DBCC SHRINKDATABASE (%DB%)" >> %LOGFILE_DBMAINT%
echo ***************************************************** >> %LOGFILE_DBMAINT%
echo %DATE% %TIME% : osql -E -D %DSN% -Q "EXEC sp_MsForEachTable 'DBCC DBREINDEX (''?'', '''', 0)'"
echo %DATE% %TIME% : osql -E -D %DSN% -Q "EXEC sp_MsForEachTable 'DBCC DBREINDEX (''?'', '''', 0)'" >> %LOGFILE_DBMAINT%
osql -E -D %DSN% -Q "EXEC sp_MsForEachTable 'DBCC DBREINDEX (''?'', '''', 0)'" >> %LOGFILE_DBMAINT%
echo ***************************************************** >> %LOGFILE_DBMAINT%
echo %DATE% %TIME% : osql -E -D %DSN% -Q "EXEC sp_createstats"
echo %DATE% %TIME% : osql -E -D %DSN% -Q "EXEC sp_createstats" >> %LOGFILE_DBMAINT%
osql -E -D %DSN% -Q "EXEC sp_createstats" >> %LOGFILE_DBMAINT%
echo ***************************************************** >> %LOGFILE_DBMAINT%
echo %DATE% %TIME% : osql -E -D %DSN% -Q "EXEC sp_updatestats"
echo %DATE% %TIME% : osql -E -D %DSN% -Q "EXEC sp_updatestats" >> %LOGFILE_DBMAINT%
osql -E -D %DSN% -Q "EXEC sp_updatestats" >> %LOGFILE_DBMAINT%
echo ***************************************************** >> %LOGFILE_DBMAINT%
echo %DATE% %TIME% : osql -E -D %DSN% -Q "EXEC sp_MsForEachTable 'sp_recompile ''?'''"
echo %DATE% %TIME% : osql -E -D %DSN% -Q "EXEC sp_MsForEachTable 'sp_recompile ''?'''" >> %LOGFILE_DBMAINT%
osql -E -D %DSN% -Q "EXEC sp_MsForEachTable 'sp_recompile ''?'''" >> %LOGFILE_DBMAINT%
echo ***************************************************** >> %LOGFILE_DBMAINT%
echo %DATE% %TIME% : osql -E -D %DSN% -Q "DBCC UPDATEUSAGE ('%DB%')"
echo %DATE% %TIME% : osql -E -D %DSN% -Q "DBCC UPDATEUSAGE ('%DB%')" >> %LOGFILE_DBMAINT%
osql -E -D %DSN% -Q "DBCC UPDATEUSAGE ('%DB%')" >> %LOGFILE_DBMAINT%
echo ***************************************************** >> %LOGFILE_DBMAINT%
echo %DATE% %TIME% : osql -E -D %DSN% -Q "EXEC sp_cycle_errorlog"
echo %DATE% %TIME% : osql -E -D %DSN% -Q "EXEC sp_cycle_errorlog" >> %LOGFILE_DBMAINT%
osql -E -D %DSN% -Q "EXEC sp_cycle_errorlog" >> %LOGFILE_DBMAINT%
echo ***************************************************** >> %LOGFILE_DBMAINT%
echo %DATE% %TIME% : End of maintenance >> %LOGFILE_DBMAINT%
echo %DATE% %TIME% : End of maintenance Thanks, David |
| |
| | | Time Traveler
Group: Ipswitch Employees
Last Login: Today @ 9:11:00 AM
Posts: 343, Visits: 1,825 |
| | To optimize a table following SQL query is used: DBCC DBREINDEX(TableName, '', 0) Second arguement is left empty to indicate that all indices need to be rebuilt and the third one is the fill factor. As for the "Compact and Validate", here is what is run... DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS, ALL_ERRORMSGS DBCC CHECKCATALOG DBCC CHECKDB ('WhatsUp', REPAIR_REBUILD) WITH ALL_ERRORMSGS, TABLOCK, TABLERESULTS DBCC UPDATEUSAGE ('WhatsUp') WITH COUNT_ROWS EXEC sp_updatestats |
| |
| | |
Forum Guru
Group: Forum Members
Last Login: 6/22/2009 12:46:35 PM
Posts: 54, Visits: 86 |
| Will Sansbury (11/20/2008)
Are you guys running local or remote databases?Local dB - SQLExpress2005 engine which was installed when I installed WUGv12. I also have the MS SQLExpress2005 tool I pulled off their WEB site to use in working around the issues with the built-in tools which do not seem to function (was installed long after the initial install of WUGv12).
------------------------------------------------------------
Bryan Harrell
Network Infrastructure - Tallahassee
Fla. Dept of Revenue
------------------------------------------------------------ |
| |
| | |
Forum Guru
Group: Forum Members
Last Login: 6/22/2009 12:46:35 PM
Posts: 54, Visits: 86 |
| AmbarK (11/26/2008)
To optimize a table following SQL query is used: DBCC DBREINDEX(TableName, '', 0) Second arguement is left empty to indicate that all indices need to be rebuilt and the third one is the fill factor. As for the "Compact and Validate", here is what is run... DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS, ALL_ERRORMSGS DBCC CHECKCATALOG DBCC CHECKDB ('WhatsUp', REPAIR_REBUILD) WITH ALL_ERRORMSGS, TABLOCK, TABLERESULTS DBCC UPDATEUSAGE ('WhatsUp') WITH COUNT_ROWS EXEC sp_updatestats Thanks Time Traveler... I'll give this a try.
------------------------------------------------------------
Bryan Harrell
Network Infrastructure - Tallahassee
Fla. Dept of Revenue
------------------------------------------------------------ |
| |
|
|