SQL server Enterprise Manager is not opening when user was trying.

One of the user reported that he is not able open SQL server Enterprise Manager , giving below error:

when I also tried it was giving the same error( I have verified that there were no permission issue).

then go to the below path :C:\Program Files\Microsoft SQL Server\80\Tools\BINN   --->There will be
SQL server enterprise manager.MSC , if not exists then you can resolve performing below  steps

(In My case the SQL server enterprise manager  was exists).


Steps:

1.Go to services.msc ->ensure the Windows Management Instrumentation  is in started state.

2.Start->run ->type mmc.exe ->you can see console root will open and perform the below steps:

go to file Menu->Add/Remove Snapin..->In standalone Tab ->click on Add ->below window will open & select the Microsoft SQL Enterprise Manager ->click on Add-

Click on OK-

then go to File menu(in the same screen) ->Save as -> then give the Name as SQL server Enterprise Manager.MSC ->Save.


when closing existing one i,e which you have opened from add/remove snapin....  it will ask for  to save click on Yes see the below screenshot-

then go to the path :C:\Program Files\Microsoft SQL Server\80\Tools\BINN  -->verify  MMC for enterprise manager has been created(see below)  and set appropriate permissions.-

        




Note 2:  If already the SQL server enterprise manager.MSC exists in path still you have the same issue(as mentioned above error) then you can perform below steps:

Rename the old one SQL server enterprise manager.MMC to  any name(verfiy the permissions what it exists) , then follow the same steps as mentioned above i,e from Add/Remove snapin..

(or)

simple way -> you can copy from other server as well which has the same versions in to the path :C:\Program Files\Microsoft SQL Server\80\Tools\BINN  if doesnot exits.





Bottom Lines
---------------

Where is my Enterprise manger(SQL server 2000),SQL server Managment studio(SQL server 2005 and SQL server 2008) were exists at the OS level.


You can check below ways using simple way(There are many other way as well).

SQL server 2000
-------------

Start->All Programs->Microsoft SQL server ->Entrprise Manager->Right Click Properties-> open shortcut Tab ->you can see the path of where the MSC exists for SQL server Enterprise manager-

for ex->C:\WINDOWS\system32\mmc.exe /s "C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC"


SQL server 2005
------------
Start->All Programs->Microsoft SQL server  2005->SQL server Management Studio->Right click Properties->go to shortcut tab ->you can  see below path

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe"


Note ->SQLWB.EXE is for SQL server 2005, for SQL server 2008 it is different i,e ssms.exe

you can execute exe in start->run itself.


SQL server 2008
-------------
Start->All Programs->Microsoft SQL server  2008 ->SQL server Management Studio->Right click Properties->go to shortcut tab ->you can  see below path

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"



Note ->Still using SQL server 2000 query analyzer --you can connect to SQL sever 2005 & SQL server 2008.

About COPY_ONLY Backups in SQL server 2005.

Thanks to Microsoft for introducing the COPY_ONLY Backups in SQL server 2005.

As all we know that  is an special Backup and it won’t affects/Interrupt to the regular Backups/Restore sequences .

For example consider that your Backup strategy as mentioned below:

1.Full Backup ->Daily at 18PM EST

2.Differential Backup ->Thrice an day I,e 10AM, 13PM, 16PM

3.Log backup ->Every 10 Minute(s).

Now consider  that your customer asked that they need full back of the database from the production

Server at 14PM EST(just an example).

Consider now the time is  14PM EST .

So you have to give backups from last night full backup (+)All differential backup(+ )additional transaction log backups so this will be lengthy steps for the restoration on test server.

So if you take the full backup manually at 14PM est  then your next differential backups depends tightly on this full backup(I,e which we have taken full Backup at 14 PM EST) so your Backup strategy has been disturbed and for your restore recovery Plan as well (I,e you have very well plan of backup/restore sequences  incase of recovery of the database to point in time) & you need to restore the database according to same strategy, so thanks to Microsoft for  Introducing  new feature I,e called COPY_ONLY Backups.So this option will not disturb your planned Backup/restore sequence. 

 Now in your mind questions may arise what about the LSN sequences?

If yes, please see the below example.

Example:

I have taken full Backup of the database: AdventureWorksDW   so the LSN sequence as mentioned below-

See LSN’s

Now consider that trn log backup status-

(I have the Maintenance plan job which backups transactions every 10 Minute(s) )

so see below my next trn backup completed status:

The Lsn sequence as shown below-

See below the next trn backup run status as mentioned below-

The 08:10 AM LSN’s as mentioned below-

Now let take the Log backup manually using the COPY_ONLY Backup-

See the physical log backup file 

Now check the LSN’s-

So now My another log backup which runs at 08:20AM EST.

See the Log backup completed at 08:20AM EST and check the LSN’s-

So now you compare the COPY_ONLY backups and 08:20AM EST Log backups LSN’s sequence

Both are same (as shown below).

Now see how your restore works

1.I need to restore first full Backup .

2.Next I need to restore Trn Log backups I,e the  restore sequence is 08:01 TRN , 08:10 AM  and 08:20AM but note that I took log backup at 08:15AM EST by manually using COPY_ONLY Backup

Let see whether the restoration works or not(It has to work because I have already showed of LSN sequences ).

Step1:Restored the first  full Backup

So now you need to execute below queries:

Restore Log [AdventureWorksDW] FROM  DISK = N'C:\TRN Backups\AdventureWorksDW_backup_201111140801.trn' with Norecovery   --08:01AM

go

Restore Log [AdventureWorksDW] FROM  DISK = N'C:\TRN Backups\AdventureWorksDW_backup_201111140810.trn' with Norecovery   --08:10AM

go

Restore Log [AdventureWorksDW] FROM  DISK = N'C:\TRN Backups\AdventureWorksDW_backup_201111140820.trn' with recovery     --08:20AM

go

see below I have executed 08:01 & 08:10 AM EST log backups –those are Completed successfully and finally executed the 08:20AM.

output of 08:20 AM EST as mentioned below-

See below the database status àIt is running fine without any issues-

So this is how the  COPY_ONLY Backups works( Just FYI.. you can also test in your test boxes performing DML operations then do the test how copy_only backup works).

------------------------********--------------------------------------*******------------------------

Bottom Line:

1.Next question may arise that can  I do same restoration activity sequence Log backup taken using COPY_Backup T-sql  I,e restoring 08:15AM log backup instead of 08:20AM

Instead of 08:20AMWill Copy trn restore will work for restoration incase my 08:20 AM log backup deleted I,e I want restore the log backup which I used the COPY trn.

àI have tested and it is worked fine.

2. Also this may ask in the interview -àLike  without breaking the log shipping  how can you take the log backup at particular time?

I,e if you take log backup manually on primary database then the transaction log backup for log shipping will disturb  & Restore jobs will get fail –To resolve this you again manually need to restore the log backup which you taken log backup manually, hence Incase if you have fall in this situation COPY_ONLY Backup plays important role.

3.Note the full backup and log backup activity both are different it is not similar.

4.As per BOL –you need to keep below things in the mind

You can create a copy-only backup for any type of backup:

·         Copy-only data backups (all recovery models)

To create a copy-only data or differential backup, use the COPY_ONLY option in your BACKUP DATABASE statement. A data backup taken with the COPY_ONLY option cannot be used as a base backup and does not affect any existing differential backups.

·         Copy-only differential backups

These are identical to regular differential backups.

·         Copy-only log backups

To create a copy-only log backup, use the COPY_ONLY option in your BACKUP LOG statement; doing so causes backup to retain the current log archive point. Also, the transaction log is not truncated by a log backup taken using the COPY_ONLY option. Without COPY_ONLY, a BACKUP LOG WITH NORECOVERY would advance the log archive point.

Note:Copy-only backups can be created or restored only by using the BACKUP and RESTORE Transact-SQL statements. SQL Server Management Studio does not support these backups.

    COPY_ONLY backups are recorded in the backupset table in the is_copy_only column. 

When to Run DBCC CHECKDB or Best Practise?

When to Run DBCC CHECKDB or Best Practise?

If your database is very small then no worries you can run and see the results,how ever if the Database is very large then you have to take right decision when you can run--

Because DBCC CHECKDB is a CPU- and disk I/O intensive operation. Each data page it has to scan &   first it has to be read from disk into memory. SQL server database engine uses  tempdb to do sorting operations for it's own(note in SQL server 2005 if you have not specified Sort_tempdb =Off, the sorting operation runs on the same database which you fired the query DBCC CHECKDB)


So you can use  the below Recommendations for DBCC  considering the Performance point of view:

   1.Identify that the prefer time where the system usage is low.

   2. Ensure that you are not performing any other disk I/O operations, Like SQL database backups to disk.

   3.Always good Pratcise that place tempdb  database files on a separate disk system or a fast disk subsystem, so that you can Avoid I/O.

    Also ensure that you have enough space for tempdb to expand on the drive. you Use DBCC with ESTIMATE ONLY to estimate how much space will be needed for tempdb.

   4. Avoid running Bulk queries which can cause CPU to eat like intensive queries or batch jobs or adhoc queries.

   5. Reduce active transactions while a DBCC command is running.

Because If actively performing transactions while DBCC CHECKDB is running, the transaction log continues to grow because the DBCC command blocks log truncation until it has finished reading the log.

   6. Use the NO_INFOMSGS option DBCC command to reduce processing and tempdb usage significantly.

Note that :DBCC CHECKDB will fails/Terminates to complete in below situation(there are many reasons which fails few are which I have seen as mentioned below).

A)When system tables got corrupted.
B)When you have ran DBCC CHECKDB  during busy hours -due to insufficient memory available to run DBCC CHECKDB
C)When your Temdb database files runs out of space.

Please add if you know some more.

Ad hoc update to system catalogs is not supported. (Microsoft SQL Server, Error: 5808)

Today I was working on Configuring Database Mail in SQL server 2005, I got an below SQL words

"Ad hoc update to system catalogs is not supported. (Microsoft SQL Server, Error: 5808)"

So to resolve this issue : You need to perform below steps to work around

sp_configure 'allow updates', 0;
reconfigure;


Warning  Incorrect modification of the system catalogs can result in database corruption or data loss as per BOL, Also we shouldn't not do anything to the system catalog without understanding the Impact before & after Implement.

How to determine the version and edition of SQL Server and its components.

I hope everyone knows how to find the sql server version for Database engine & it's components.
If someone want to know you can refer the below KB link.
http://support.microsoft.com/kb/321185

How to Change Maintenance Plan owner in SQL server 2005.

Yesterday one of my colleagues modified the Maintenance plan, where that Maintenance plan performs

Backup and cleanup tasks for user databases (The jobs were running without any issues before the Maintenance plan modify).

Here is the small story-

We are getting the disk space alerts for one of our servers and to resolve this we plan to change the retention period of cleanup tasks for 3 days to some Hours(>48Hours). So my colleague modified the Maintenance Plan for cleanup task & saved the maintenance plan.

But today when I was in work received the alert that Job has failed which was created via Maintenance plan (the one which was modified yesterday).

Job failure information as mentioned below-

What happened the Maintenance plan was created by one of our colleagues and he moved to different

Account & his current accesses revoked by Security team as per our project policy.

Hence yesterday the maintenance plan modified by other colleague ->by default the Maintenance plan/Package owner has reflected to Job owner because of modification, hence the job was failed with the above reason.

To fix permanently we need to modify the Maintenance plan/Package owner –so I decided to put

Maintenance plan Owner as our service account and I have completed with the below  T-Sql

--You can use  the table of MSDB to get  who is the owner by taking  the information of Ownersid

Table name is as mentioned below

select * from dbo.sysdtspackages90

so using above you can use suser_name to get the owner for the maintenance plan/Package..

SELECT

name, description,suser_sname(ownersid),isencrypted

FROM

msdb.dbo.sysdtspackages90

--Now we have to update the owner to an appropriate domain account. Either the service account or any other account is Ok for you.

(Before performing below step –take MSDB Backup not required but best practice).

UPDATE

msdb.dbo.sysdtspackages90

SET

OWNERSID = SUSER_SID('YOUR_DOMAIN_NAME\ACCOUNT_NAME') --------to which account you are going to change

WHERE

OWNERSID = SUSER_SID('YOUR_DOMAIN_NAME\OLD_ACCOUNT') ----------The old account name ---i,e the job which is failing with this account.

after executing you will get output as 1(rows) affected.

Verify once again to check Maintenance plan owner changed or not, After this I have executed the job

It completed without any issues.

(If you need you change the job owner to SA).