This is a handy little SQL statement we managed to whip up while working on a discovery problem. This script will show you a count of Machine based discoveries (not user) per site and per discovery type. Output will look something like this: Basically it will give you a much better chance of finding run-away discoveries in your infrastructure….discoveries that...
Updating Statistics in SQL (and why you need to do it)
SQL server uses statistics to keep track of values in an index, and determine when and how to use that particular index while processing a query. This is a horribly simplified definition (because I barely understand it), but basically it means that statistics are a way for SQL to find the best index to use. By default when you create...
Rebuilding Indexes in ConfigMgr Databases
If you suspect that you have fragmented SQL indexes in your ConfigMgr database then what are your options? You could wait for your “Rebuild Indexes” ConfigMgr maintenance task to come around again, or you could just go ahead and rebuild those indexes quickly from SQL Management Studio. If you want to rebuild all indexes in the ConfigMgr database, which is...
Check SQL index fragmentation on the ConfigMgr database
While working on a performance problem with a couple of very talented SQL gurus I was handed this script. It checks, among other things, the fragmentation of the indexes in the ConfigMgr database. This will help tell you if your rebuild indexes task is being run often enough, or if you need to target specific indexes more often with an...
List ConfigMgr SQL tables with row counts and size
Ever wonder what is taking up all the space in your ConfigMgr database? This SQL query will show row count, reserved/used data size, and reserved/used index size. This code works for any database, not just ConfigMgr. Enjoy! **********************CODE BELOW HERE************************* declare @id int declare @type character(2) declare @pages int declare @dbname sysname declare @dbsize dec(15,0) declare @bytesperpage dec(15,0) declare @pagesperMB...
Client Status Reporting workaround for "subquery returned more than 1 value" in chservice.log
After you have installed Client Status Reporting in R2, when trying to update client status in the ConfigMgr database you might see an error similar to this in the chservice.log Invalid SQL error – Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is...
Increase SCCM performance with Status Filter Rules
Status messages are an integral and important piece of the ConfigMgr puzzle. They tell us everything from package distribution status to how successful our last advertisement was. But lets face it – ConfigMgr likes status messages a little too much. Every little action gets reported…from the simple client action to massive site settings changes. The simple act of a client...
VBScript for Computer Association with logging
Here is a quick and dirty vbscript that will generate a computer associate between 2 resourceids, logging information to your systemdrive\ConfigMgrComputerAssociation.log. To use the script, call it with three arguments – script.vbs <sccmservername> <source-computer-resouceid> <destination-computer-resourceid> Enjoy! ‘******************Code Below Here**************** set colargs = wscript.arguments strSMSServer = colargs(0) strSourcePC = colargs(1) strDestinationPC = colargs(2) ‘Set Environment Variables Set oShell = CreateObject( “WScript.Shell”...
Script Skeleton for ConfigMgr with Logging
If you are like me, you want a consistent look to your scripts, verbose yet readable output, and a quick connection to your SCCM server or client. Here is a script framework that I use when developing new code. Simply change the SCCMServer variable, change the output log file name, and put your code after the comment ‘Start the bulk...
Check for open transactions on the ConfigMgr database
Open transactions – transactions that run too long or are hung – can cause havoc on the ConfigMgr database. Notice a backlog of files (DDRs or Mifs)? Slow processing in general? Collections having a problem updating? You might want to give this a quick check. First, lets find out what the oldest transaction on the ConfigMgr database is. Open SQL...
Check how long ConfigMgr Site Maintenance Tasks run
Here is a quick and dirty SQL script that will list the site maintenance tasks and how long they ran last. Run this in SQL Management Studio and target your ConfigMgr database. select *, floor(DATEDIFF(ss,laststarttime,lastcompletiontime)/3600) as Hours, floor(DATEDIFF(ss,laststarttime,lastcompletiontime)/60)- floor(DATEDIFF(ss,laststarttime,lastcompletiontime)/3600)*60 as Minutes, floor(DATEDIFF(ss,laststarttime,lastcompletiontime))- floor(DATEDIFF(ss,laststarttime,lastcompletiontime)/60)*60 as TotalSeconds from SQLTaskStatus
Prepare for ConfigMgr 2012 with these handy PowerShell scripts!
So the time is coming – you need to prepare to ConfigMgr 2012. Sure, the migration tool built into 2012 is an awesome product, but don’t you think you should clean up your ConfigMgr 2007 infrastructure before you run down the migration path? Yes…yes you should. Here are some scripts we have thrown together for a MMS 2011 presentation, but...
12
Rod Trent: That happens. Its a false positive because there are scripts in the zi...
Tim: Virus detected for the Right Click Tools Link!!...
Fandangoed up the river: Is there any way to have one shortcut to combine both of those menu ...
Ian Pickering: Sure is an option Scott - of course it has a reliance on your SCCM 200...
Carl: Hi rick, how do I uninstall these tools. The collection actions aren't...