Memory consumption by each db

--works for 2005 and 2008
--for memory consumption by each db

select isnull(db_name(database_id),'Total memory used') as Databasename,
convert(numeric(8,2),count(page_id)/128.0) as MB
from sys.dm_os_buffer_descriptors with (nolock)
where database_id !=32767
group by database_id
with rollup
order by count(page_id) desc
got this for memory consumption by each db

VMWare issue --The log for database 'XYZ' is not available.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=133004


What have this lesson learned me?

1) Don't use VMware 3.5 and definately not during snapshots and backups.
2) Rethink if virutal machine is the right choice for a terabyte datawarehouse, even if data files are on SAN.

Moving DTS packages from one server to other server

http://www.sqlservercentral.com/Forums/Topic697546-146-1.aspx

INSERT INTO msdb.dbo.sysdtspackages
SELECT *
FROM OPENDATASOURCE('SQLNCLI','Data Source=ServerName;Integrated Security=SSPI').msdb.dbo.sysdtspackages


--------------------------------------------------------------------------------

Error message while DBCC Checkdb

DBCC Checkdb

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

http://social.msdn.microsoft.com/Forums/en-US/sqlkjmanageability/thread/194c1c3b-3c6d-450b-a033-ceab67480433

" stack dump error "

Warning: Fatal error 211 occurred at Aug 21 2012  3:09PM. Note the error and time, and contact your system administrator.
A severe error occurred on the current command.  The results, if any, should be discarded. (Microsoft SQL Server, Error: 21)

Sol: Database ldf file location moved to different location then re-started the SQL Server Instance.The DB went to "Recovery Pending" state.Then we repaired with emergency mode with data lose. This resolveed after copying the ldf files to new location.

Identity column OFF and ON

http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/

CREATE TABLE dbo.Tmp_example1
(
eid INT NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_example1 ON
GO
IF EXISTS(SELECT * FROM dbo.example1)
EXEC('INSERT INTO dbo.Tmp_example1 (eid)
SELECT eid FROM dbo.example1 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_example1 OFF
GO
DROP TABLE dbo.example1
GO
EXECUTE sp_rename N'dbo.Tmp_example1', N'example1', 'OBJECT'
GO
COMMIT


If you run into the following error message:
An explicit value for the identity column in table ‘’ can only be specified when a column list is used and IDENTITY_INSERT is ON.
It can mean two things.

One you’ve not enabled identity insert on your table, meaning SQL Server will not let you insert into the Identity column.
This can be rectified with the following statement:
SET IDENTITY_INSERT table_name ON
And then turn it off again when done
SET IDENTITY_INSERT table_name OFF

However it can also mean that you are using for example INSERT INTO, in which cause the message tells you to specify the column names. This means using the following syntax:

INSERT INTO target_able_name (column_name1, column_name2…. column_nameN)
SELECT
YOUR_SELECT_LIST_WHICH_MATCHES_COLUMN_LIST
FROM source_table_name

SSAS port number change

--to change the analysis service ports for named instances,
is there anyother way..I did try to change in ini file..thats not working


-- remeber...there is some right click on the SSAS or instnace then give the port numbers


http://support.microsoft.com/kb/2466860
http://deangrant.wordpress.com/2011/11/30/change-the-port-number-of-ssas-instance/

To get a count of objects for all the databases on a Host -- Table_Count of rows

Table_Count of rows
--To get a count of objects for all the databases on a Host

sp_msforeachdb ‘select ”?” as DatabaseName, count(*) as CountObjects from ?.sys.objects’


  To get a count of rows from all the tables in a database
  sp_msforeachtable ‘select ”?”, Count(*) from ?’