How to shrink the SMS_SITENAME database SMS 2003?

24 01 2008

The SMS 2003 infrastructure I am managing has one singe SMS database which became 4 GB. 1GB data and 3GB log. Today I planned the database maintenance and managed to shrink the files with 3GB. Open the Enterprise Manager or Microsoft SQL Server Management tools and make sure the database option is set to Auto Shrink = Auto (enhancement for the future).

image

Run the following sql script:

USE [SMS_123]

GO

DBCC SHRINKFILE (N‘SMS_123_log’, 0, TRUNCATEONLY)

GO

Another useful script can be found here which will create a stored procedure and show all table space sizes with rows and MB used.

exec sp_show_huge_tables


image

Comments : No Comments »
Categories : SMS

SMS Report - Delete Computer Object

13 06 2007

This report will delete a computer object from the SMS database. Use this report if you restage a computer with the same name. Normal process will take more than 30days to age to stale the record. 

  1. // *********************************************************************************
  2. //
  3. // Created by SMS Export object wizard
  4. //
  5. // Thursday, May 31, 2007 created
  6. //
  7. // File Name: Delete Computer Object.MOF
  8. //
  9. // Comments :
  10. //
  11. //
  12. // *********************************************************************************
  13. // ***** Class : SMS_Report *****
  14. [SecurityVerbs(140551)]
  15. instance of SMS_Report
  16. {
  17. Category = “Networknet-LocalIT tasks”;
  18. Comment = “Author:Ivan1980@Networknet.nl
  19. \nCreate date: 5/30/2007
  20. \nDescription: This report will delete a computer object from the SMS database. Use this report if you restage a computer with the same name. Normal process will take more than 30days to age to stale the record.
  21. \n
  22. \nNote: Select the computer you will restage and click Display. When report is displayed it will show 0 rows!”;
  23. GraphXCol = 1;
  24. GraphYCol = 2;
  25. MachineDetail = FALSE;
  26. MachineSource = FALSE;
  27. Name = “Delete Computer Object”;
  28. NumPrompts = 1;
  29. RefreshInterval = 0;
  30. ReportParams = {
  31. instance of SMS_ReportParameter
  32. {
  33. AllowEmpty = FALSE;
  34. DefaultValue = “”;
  35. PromptText = “Computername”;
  36. SampleValueSQL = “begin
  37. \n if (@__filterwildcard = ”)
  38. \n SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS ORDER By SYS.Netbios_Name0
  39. \n else
  40. \n SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS
  41. \n WHERE SYS.Netbios_Name0 like @__filterwildcard
  42. \n ORDER By SYS.Netbios_Name0
  43. \nend”;
  44. VariableName = “comp”;
  45. }};
  46. SecurityKey = “”;
  47. SQLQuery = “exec sp_RemoveComputerObject1 @comp”;
  48. StatusMessageDetailSource = FALSE;
  49. };
  50. // ***** End *****

Read the rest of this entry »

Comments : 2 Comments »
Categories : SMS