sp_spaceused not refreshing

I ran the command

EXEC sp_msforeachdb @command1="use ? exec sp_spaceused"

which returned all the relevant information per database on a SQL Server instance I was troubleshooting.
On the basis of the information I adjusted one of the db sizes .

I then ran the same command and notices that the information was not refreshing .

Adding "@updateusage = N'TRUE'" derived the latest information , and reflected the changes I made.

EXEC sp_msforeachdb @command1="use ? exec sp_spaceused @updateusage = N'TRUE';"

Source:Jack Vamvas (http://www.sqlserver-dba.com)

Viewing legacy DTS packages in SSMS

I installed SQL Server Management Studio  , to view some a SQL Server instance 2000.
I attempted to access the packages via the : "Management | Legacy | Data Tramsformation Services"
and got the following message:

SELECT permission denied on table sysdtspackages, database 'master', owner 'dbo'

I was perplexed , because when I attempted to view the DTS packages via SQL Server 2000 Enterprise Manager - I was to view tehm.

I got around the problem , via creating a new role called "DTS_user" and assigned
EXECUTE on sp_enum_dtspackages and sp_get_dtspackage in msdb.

I then went into the user and assigned that role.

 

Source:Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server Surface Area Configuration command line interface

The SQL Server Surface Area Configuration command-line interface, sac.exe, allows you to import and export settings.
In a recent server consolidation exercise I exported the setting and used it as a form of backup , in case of any errors
during the consolidation process.
You can import and export settings on a per-instance basis and also on a per-service basis by using the command line parameters.

Using this command line utility requires sysadmin privileges

The following code is an example of exporting all settings from the default instance of SQL Server on server1 and importing them into server2:

sac out MYSERVER1.out –S MYSERVER2.out –U admin –I MSSQLSERVER
sac in MYSERVER1.out –S MYSERVER2

 

Source:Jack Vamvas (http://www.sqlserver-dba.com)

ole automation configuring for SQL Server 2005

There was a requirement to allow Ole Automation Procedures within SQL Server 2005 ,
this code will enable the feature.

-------------------CODE START------------------------
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

-------------------CODE FINISH--------------------------

Source:Jack Vamvas (http://www.sqlserver-dba.com)

Powershell and Batch files

Executing batch files throgh Powershell - is not doable by design. The cmd.exe is the runtime environment for .bat files , and it is
possible to issue a .bat call via the Powershell prompt

PS> cmd /c mybatchfile.bat