How to find who dropped the database? Is that logged on SQL Server?

 

Are you one looking to find answer for questions like below.,

· How to find who dropped a database?

· How to find who created the database?

· How to find who altered the database?

· How to find who altered the database configurations?

· How to find who dropped the schema?

· How to find who altered the schema?

· How to find who altered the server configuration?

· How to find who modified the login?

· How to find who modified the table?

If it is then,  are you wondering is that possible on SQL Server?

Yes, SQL Server can beginning from version SQL Server 2005 onwards.

That is where SQL Server Default trace come into picture.

What is SQL Server Default Trace?

Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur.

To get the default trace options

SELECT * FROM ::fn_trace_getinfo(default)

To know what is audited on default trace

SELECT Trc.EventID, Trc.ColumnID, Evt.name as Event_Description, Col.name as Column_Description

  FROM ::fn_trace_geteventinfo(1) Trc

    JOIN sys.trace_events Evt ON Trc.eventID = Evt.trace_event_id

    JOIN sys.trace_columns Col ON Trc.columnid = Col.trace_column_id

Example:

Below is the screenshot  “Schema Changes History” of Standard Reports at Server Level.

See that “it shows the event of Drop and Alter of the database

 

The same can be queried from SQL Server Default Trace like;

select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\log.trc',DEFAULT)
where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2

Usage
Default trace can be queried to find answers on

1. To finmd the Create / Alter / Drop i.e., DDL changes at Database,Schema and Object level
2. To find the configuration changes at SQL Server Instance, Database, Schema and Object Level
3. To find Login creation , modifications and failures
4. To find performance issues like missing of statistics, join predicate, hash or sort warnings
5. Database file growth or shrinkage
6. DBCC operations

 

Points to ponder

1. Default trace is a Server Side trace and it is enable by default.
2. Default trace properties can not be modified, other than enabling and disabling of this trace.
3. Default trace by default enabled and stored on the “Log” folder
   
   
4. Default trace file will be created newly on start of the SQL Server service.
5. A single trace file can store  up to 20 MB, when it is full new file will be created.
6. SQL Server would maintain 5 default trace file, where the file with highest number is the latest.
7. Default trace is light weight and it is not replacement for DDL Trigger. Default trace log only basic information, when detailed information needed DDL trigger is the option.

Using Spatial Data type to draw circle in SQL Server 2008

 

--- Draw Circle in SQL Server 2008
declare @g Geometry
set @g = 'Point(10 5)'
select @g.STBuffer(5)

SQL 2000 SP4 Installation Error sp_vupgrade_replication(1)

 

Error

• Msg 170, Level 15, State 1, Server MS, Line 1

Line 1: Incorrect syntax near '-'.

The sqlsp.log file indicates:

Process Exit Code: (0)

Error running script: sp_vupgrade_replication (1)

Action CleanUpInstall:

Installation Failed.

Resolution:
1. Check if distribution database exist though the Replication is dropped
2. If so drop the distribution database
3. To drop the distribution database by over - restoring of some user database
4. Perform the patching / SQL SP again.

SQL2000 SP4 Installation Error sp_vupgrade_replication(1)

 

SQL Server 2005 Patching fails CREATE DATABASE failed.

Error:

Error Number : 29537

Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]CREATE DATABASE failed.
Some file names listed could not be created. Check related errors.. To continue, correct the problem, and then run SQL Server Setup again.

CAUSE:
By default the setup creates the Temp_MS_AgentSigningCertificate_database Database on specified default database location,
If the location doesn't exist, it fails to create the database and the setup fails.

RESOLUTION:
1. In SSMS, go to Server Properties > Database Settings > Database Default Locations ,check default location for Data and Log .

2. Try to access locations specified at Data and Log, Make sure files are available there.
3. If any of the files missing from above location , we hit the above error.
4. In that case, change the path to point to reall location of mdf/ldf files .
5. Setup should now succeed.

1. Reference: a recently applied update, kb955706, failed to install

Delete files using DOS command FORFILES

 

To delete te

--Delete all .bak files in the named directory and its subfolders where the file modified date is more than 2 days old.

 

EXEC xp_cmdshell 'FORFILES /p "L:\MSSQL$PRODSQL2KWEB\Backup" /s /m *.bak /d -2 /c "CMD /C echo Deleting the file : @file del /Q /F @FILE && del /Q /F @FILE " '

/*

FORFILES Parameters

Parameter Name Description

/p Path

/m Search Mask (default is *.*)

/s Subdirectories will be searched recursively if this parameter is included

/c <command> Command to be executed against each file in the result set, commands must be enclosed in double-quotes, default is "cmd c/ echo @file"

/d Date range for file selection, using Last Modified Date as the criterion for the file. When the /d parameter is in the form of MM/DD/YYYY, file meeting the criteria of +/- the specified date are included. When in the format of a smallint (-32,768 - 32,768) the files +/- the files with a modified date +/- that number of days from the current date are included in the file result set.

Variables

Variable Name Description

@FILE File name

@FNAME File name without extension

@EXT File extension

@PATH Full path of the file

@RELPATH Relative path of the file

@ISDIR Evaluates as TRUE if the file type is a directory

@FSIZE File size in bytes

@FDATE Last modified date stamp on the file

@FTIME Last modified timestamp on the file

*/

Privileges needed to run SQL Server Profiler without sysadmin privileges

 

In starting with SQL Server 2005 and above a SQL Server user or login does not need to have SYSADMIN privileges to use the SQL Server Profiler.

It can be accomplished by,

GRANT ALTER TRACE to [Login]

BOL explanation:

By default, running SQL Server Profiler requires the same user permissions as the Transact-SQL stored procedures that are used to create traces. To run SQL Server Profiler, users must be granted the ALTER TRACE permission

References: Permissions Required to Run SQL Server Profiler

Query to find when the table last accessed on SQL Server 2005 and above

 

------ Query to find when the User Table Last accessed ---------

WITH LastActivity (ObjectID, LastAction)
AS
(
SELECT object_id AS TableName, Last_User_Seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id)AS TableName,
    MAX(la.LastAction)as LastSelect
FROM
sys.objects so
LEFT JOIN LastActivity la
ON so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)

------ Query to find when the Database Last accessed ---------

SELECT name, last_access =(
                select X1= max(bb.xx)
                from ( select xx =
                        max(last_user_seek)
                        where max(last_user_seek)is not null
                        union all
                        select xx = max(last_user_scan)
                        where max(last_user_scan)is not null
                        union all
                        select xx = max(last_user_lookup)
                        where max(last_user_lookup) is not null
                        union all
                        select xx =max(last_user_update)
                        where max(last_user_update) is not null) bb)
FROM master.dbo.sysdatabases d
left outer join sys.dm_db_index_usage_stats s
on d.dbid= s.database_id
group by d.name

Deny specific user from Remote Login to SQL Server

SQL Server Query to deny user on remote login feature, let user connect to database engine only from localhost (or 127.0.0.1).

This query below applies to SQL Server 2005 with SP2 and above higher version. It uses the SQL Server Logon Trigger

 

CREATE TRIGGER Deny_Remote_Login_Login1_trigger ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'Login1' AND (SELECT * FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'Login1' --- Checking the given login and Host_Name not in ( 'SQLServerHostName') --- Connecting Remotely and Session_ID = @@SPID ) BEGIN PRINT 'RemoteLogin Attempt attempt of ' + ORIGINAL_LOGIN()+' denied, please login locally' ROLLBACK; END END;

SQL Server 2005 Uninstall Error: The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine Error: 2147749896 (0x80041008).

 

The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine Error:2147749896 (0×80041008).

Resolution: Copy and run the DOS batch file below

@echo on cd /d c:\temp if not exist %windir%\system32\wbem goto TryInstall cd /d %windir%\system32\wbem net stop winmgmt winmgmt /kill if exist Rep_bak rd Rep_bak /s /q rename Repository Rep_bak for %%i in (*.dll) do RegSvr32 -s %%i for %%i in (*.exe) do call :FixSrv %%i for %%i in (*.mof,*.mfl) do Mofcomp %%i net start winmgmt goto End :FixSrv if /I (%1) == (wbemcntl.exe) goto SkipSrv if /I (%1) == (wbemtest.exe) goto SkipSrv if /I (%1) == (mofcomp.exe) goto SkipSrv %1 /RegServer :SkipSrv goto End :TryInstall if not exist wmicore.exe goto End wmicore /s net start winmgmt :End

Thanks to: http://social.msdn.microsoft.com/Forums/en/Vsexpressinstall/thread/632ca405-1c38-405b-9ed3-01785c9f99d1

How to remove the subscription from Publication in SQL Server Replication

 

1. On SQL Server 2000 connect to Enterprise Manager
On SQL Server 2005 / 2008 connect to SQL Server Management studio

2. Expand Replication à Publication, select the Publication from which the subscribers to be removed.
On SQL Server 2005 / 2008 from the Replication à Local Publication select the Publication

3. Right click on the Publication

4. Select the Subscription tab

5. Select the Subscriber and click Delete button, that’s it

6. If you hold the “Shift” key you can delete more than one subscriber. However you have to execute the sp_subscription_cleanup each subscribers manually.

7. Rather if you delete one subscriber at a time, it is simple and no need to execute the stored procedures at subscriber.

How to apply Service pack on SQL Server 2005 Cluster

 

1. Backup of all the System + User Database

2.Windows Login account with "Local Administrator" privileges on all the nodes involved

3. Ensure all the recourses part of the SQL Server Cluster resource group are online.
   I recommend to reboot both nodes before installation. (to avoid pending reboot, hanging installers, terminal sessions etc. problems.)

4. Cross check if the cluster failover on the nodes involved successfully.

5. shut down all applications that use SQLServer (because you know the instance(s) will get offline at some point(s) during the upgrade)

6. Disable if any DDL Triggers

7. Verify if following services online on the nodes involved.

• Task Scheduler
• Com+ System Application Service
• Cryptographic Services
• Remote Registry Service 

8. Pause Database Mirroring

Log on the active node, apply the Service pack. ( It is opposite in case of SQL Server 2008 ! ). You cannot install SP3 from any passive nodes in the failover cluster.

Please note SP will not update the client tools on the passive node. you need to do that manually; though it is cluster aware.

Reference: 

How to install SQL Server 2005 SP3

SQL Server 2005 SP3 Release Notes

SQL Server 2008 failover cluster rolling patch and service pack process

SQL Server 2005 Service Pack 4

SQL Server 2005 Service Pack 4 ( SQL Server 2005 SP4 ) will be released by end of the year 2010 ( Quarter 4 of 2010 ). It will be the last Service pack for the SQL Server 2005.


SQL Server 2008 Service Pack 1 ( SQL Server 2008 SP1 ) will be released by third quarter of the year 2010 ( Quarter 3 of 2010 ). It will be the last Service pack for the SQL Server 2005.


Source: Shared by the Microsoft SQL Server release services group manager.

Learn SQL Server 2008 for free

 

Microsoft makes it easier than ever to attend SQL Server 2008 training, at little to no cost, without any travel necessary. 

It just needed a email account on hotmail or msn.

Subject matter includes:

· Database Administration

· Database architecture

· Database, programming and security. 

Click the courses below to see full descriptions or visit Microsoft Learning to browse the course catalog

DBA Track

Course 6339: Database Fundamentals in Microsoft SQL Server 2008

Clinic 10259: SQL Server 2008: Database Infrastructure and Scalability

Clinic 6188: What's New in Microsoft SQL Server 2008 for Enterprise Data Platform

Collection 6187: What’s New in Microsoft SQL Server 2008

Developer Track

Clinic 6190: What's New in Microsoft SQL Server 2008 for Database Development

Clinic 10164: Essential SQL Server 2008 for Developers

Clinic 10258: SQL Server 2008: Upgrade and Application Compatibility

Reporting Service

Clinic 6258: New Features Of Microsoft SQL Server 2008 Reporting Services

Business Intelligence

Clinic 6189: What's New in Microsoft SQL Server 2008 for Business Intelligence

DAC – Dedicated Administrator Connection

 

Use Command below to connect as DAC

SQLCMD -A

 

It is on by Default for local server.

For remote(via network) enable it by running query below

sp_configure 'remote admin connections'

 

For Clusters, recommended to enable remote admin connections as no local DAC available

 

---- Query to Find if DAC - Dedicated Admin Connection used -----   select s1.session_id, s1.program_name,s1.host_name,t1.name from sys.tcp_endpoints t1 join sys.dm_exec_sessions s1 on t1.endpoint_id = s1.endpoint_id where t1.name = 'Dedicated Admin Connection' --- End of the Query -----

--- Query to check the DAC ---- --- Scheduler_ID = 255 & Status = "VISIBLE ONLINE (DAC) --- select * from sys.dm_os_schedulers

 

Usage:

when SQL Server hangs, SQL Server does not respond, when SQL Server does not accept new connection

• Use DAC sparingly for monitoring and Troubleshooting
  
• Connect to master database first
  
• Use lightweight queries.
  
• Not advised for long running queries like ( DBCC CHECKDB, DBREINDEX, INDEXDEFRAG )
  

SQL Server Default Collation

Technorati Tags: SQL Server Setup

 

SQL Server Default Collation

SQL Server Latest Service Pack and Security Hot fixes

Applies to	Version Range	Type	Description	Target Version	Release Date	Security Bulletins	KB	Download
SQL Server 2000	Below 8.00.2039	Service Pack	SQL Server 2000 SP4	8.00.2039	6-Jun-05	MS03-031	KB884525	http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en
SQL Server 2000	8.00.2039-8.00.2054	GDR	SQL Server 2000 SP4 GDR	8.00.2055	10-Feb-09	MS09-004	KB960082	http://www.microsoft.com/downloads/details.aspx?familyid=d5bb816a-6e1a-47cb-92be-51c565ee184c&displaylang=en
SQL Server 2000	8.00.2148-8.00.2281	QFE	SQL Server 2000 SP4 QFE	8.00.2282	10-Feb-09	MS09-004	KB960083	http://www.microsoft.com/downloads/details.aspx?familyid=A93F3CFE-18C9-4218-A551-13BF415E418A&displaylang=en
SQL Server 2005	Below 9.00.4035	Service Pack	SQL Server 2005 SP3	9.00.4035	15-Dec-08	N/A	KB955706	http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en
SQL Server 2005	9.00.4035-9.00.4052	GDR	SQL Server 2005 SP3 GDR	9.00.4053	12-Oct-09	MS09-062	KB970892	http://www.microsoft.com/downloads/details.aspx?familyid=0d878f4b-71e8-4170-9a14-1bce684811ce&displaylang=en
SQL Server 2005	9.00.4205-9.00.4261	QFE	SQL Server 2005 SP3 QFE	9.00.4262	12-Oct-09	MS09-062	KB970894	http://www.microsoft.com/downloads/details.aspx?familyid=e6f307c1-8b21-406e-9c6f-b1a3a1e9a98f&displaylang=en
SQL Server 2008	Below 10.00.2531	Service Pack	SQL Server 2008 SP1	10.00.2531.00	27-Aug-09	N/A		http://www.microsoft.com/downloads/details.aspx?FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19&displaylang=en
	It lists only the latest SQL Server Service Pack and Security Hot fixes as on 22nd April 2010

Software Asset Management - SAM

Software Inventory Assistance

Whether we have just a few computers or a large network, a software inventory tool can make it easier to quickly know what we have and how it's being used.

The Microsoft Software Inventory Analyzer (MSIA) is a free tool available for download that can help us with our software inventory. It is built specifically to be a starting point to working with SAM.

We can use the MSIA to scan and inventory the Microsoft software that is installed on a single computer, or on multiple computers throughout a network. It generates a report that provides details of all installed Microsoft products, including the type and the number of licenses. The MSIA will work with networks that have 250 or fewer computers. The latest MSIA 5.1 features enable you to:

• Identify and report all installed Microsoft products listed under Add or Remove Programs.
  
• Consolidate the MSIA report with the Microsoft Licensing Statement (MLS) report (.xls only).
  

This information is useful for you, if you are taking care of the system administration, licensing and inventory of Microsoft software products in your organization.

BgInfo

BgInfo – Microsoft Windows SysInternal tool

How many times have you walked up to a system in your office and needed to click through several diagnostic windows to remind yourself of important aspects of its configuration, such as its name, IP address, or operating system version If you manage multiple computers you probably need BGInfo. It automatically displays relevant information about a Windows computer on the desktop's background, such as the computer name, IP address, service pack version, and more. You can edit any field as well as the font and background colors, and can place it in your startup folder so that it runs every boot, or even configure it to display as the background for the logon screen.

SQL Server 2005/ 2008 Query to find when the database last used

Below is the TSQL Query to find when the database last accessed for SQL Server 2005 / 2008

-- Query to find when the database last accessed on SQL Server 2005 / 2008 --- select d.name, x1 = (select X1= max(bb.xx) from (     select xx = max(last_user_seek)         where max(last_user_seek) is not null     union all     select xx = max(last_user_scan)         where max(last_user_scan) is not null     union all     select xx = max(last_user_lookup)         where max(last_user_lookup) is not null     union all         select xx = max(last_user_update)         where max(last_user_update) is not null) bb) FROM master.dbo.sysdatabases d left outer join sys.dm_db_index_usage_stats s on d.dbid= s.database_id group by d.name

 

 

 

Differentiate your connections to SQL Instances by coloring it

What would be the case. have you ever connected to a server, thought it was a testing or development system, and only a split second after you pressed "F5" to run that command, realized that it was the production server? Yeah….me neither :).

To make sure that's never you, when you start SQL Server 2008 Management Studio (SSMS), stop for a moment on the connection dialog and press "Options". You'll see that you can select a color for the connection – I set mine to red for production servers, yellow for testing servers, and green for development servers.

Once inside SSMS, you can also set the default color for all connections – I do this and set them all to red, so that if I forget, at least I'll have that visual indicator to make sure the change I'm making is on the right server.

It helps on classifying the SQL Server Instances by Production, Development, Test or QA etc.

Format Date or Time without “/”

Format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement	Output
select replace(convert(varchar, getdate(),111),'/','')	20100413
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')	20100413 004426
Select cast(int, convert(char,getdate(),111))	20100413

Remote Desktop Error - The terminal server has exceeded the maximum number of allowed connections

When trying to connect using a remote desktop session you may get this Login Failure Message, because there are too many remote sessions

To resolve the exceeded connection issue execute "mstsc /admin" ( mstsc /console )command from the command prompt as shown below

If a terminal server has exceed the maximum number of sessions an "/admin" session can still be created and this will allow you to still connect to the server.

The /admin sessions don't count towards the session limit that may be configured on a terminal server to limit the number of remote sessions, so this will give you a back door into the server if you get the message "The terminal server has exceeded the maximum number of allows connections".

SQL Server - Patching : An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

 

An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

 

The SQL Server team is moving away from the current priority-driven hotfix release model to a scheduled delivery model. In the scheduled delivery model, a customer can receive a hotfix to address their most critical situations with a short turn-around time. Additionally, a customer can receive a fix that has undergone more testing and that is released on a schedule basis. Therefore, the SQL Server team has created the following delivery mechanisms.

Critical On Demand - COD	On Demand- OD	Cumulative Update - CU	General Distribution Release - GDR
The hotfix is released on or before a mutually agreed upon date based on the customer's need	The hotfix is released on or before a mutually agreed upon date based on the customer's need	The update is released every 2 months.	A GDR addresses an issue that has a broad customer impact, that has security implications, or that has both. A GDR is determined and issued by Microsoft as appropriate and when appropriate. GDRs are kept to a minimum.
		It contains all previous critical on-demand hotfixes to date.

 

ISM eliminates the need for hotfix rollup releases. The first CU after a new release contains the hotfix rollup, plus any additional QFEs.

A hotfix package does not replace a service pack. A hotfix package is optional. A hotfix package can be installed or uninstalled at any time. Additionally, hotfix packages are cumulative. Therefore, the latest OD hotfix package or CU hotfix package includes all previously released hotfixes.

Quick Fix Engineering - QFEs are used for the majority of fixes where the effects of the problem are not widespread or severe enough to warrant a GDR.

Can we have database on SIMPLE Recovery model for Transactional Replication?

Yes. We can have Publisher database on SIMPLE recovery model.

The reason is that;

• When executing, the Log Reader Agent first reads the publication transaction log and identifies any INSERT, UPDATE, and DELETE statements, or other modifications made to the data transactions that have been marked for replication. Next, the agent batch copies those transactions to the distribution database at the Distributor.
  
• The Log Reader Agent uses the internal stored procedure sp_replcmds to get the next set of commands marked for replication from the log. The distribution database then becomes the store-and-forward queue from which changes are sent to Subscribers. Only committed transactions are sent to the distribution database.
  
• Only committed transactions are sent to the distribution database by LogReader agent.
  
• After the entire batch of transactions has been written successfully to the distribution database, it is committed. Following the commit of each batch of commands to the Distributor, the Log Reader Agent calls sp_repldone to mark where replication was last completed.
  
• Finally, the agent marks the rows in the transaction log that are ready to be truncated. Rows still waiting to be replicated are not truncated
  
• The transaction log on the Publisher can be dumped without interfering with replication, because only transactions not marked for replication are purged.
  

SQL Server 2005 Cluster: SQL Server Service not coming online, error on TCP port is already in use

 

SQL Server Error Log:

2010-06-04 15:19:37.520 Server Server is listening on [ 10.0.0.136 <ipv4> 2813].

2010-06-04 15:19:37.530 Server Error: 26023, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server Server TCP provider failed to listen on [ 10.0.0.136 <ipv4> 2813]. Tcp port is already in use.

2010-06-04 15:19:37.530 Server Error: 17182, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server TDSSNIClient initialization failed with error 0x2740, status code 0xa.

2010-06-04 15:19:37.530 Server Error: 17182, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server TDSSNIClient initialization failed with error 0x2740, status code 0x1.

2010-06-04 15:19:37.530 Server Error: 17826, Severity: 18, State: 3.

2010-06-04 15:19:37.530 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2010-06-04 15:19:37.530 Server Error: 17120, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Event Viewer Error

Event Type: Error

Event Source: MSSQL$PROD2K5ISD

Event Category: (2)

Event ID: 26023

Date: 4/06/2010

Time: 2:51:31 PM

User: N/A

Computer: PROD2K5ISD

Description:

Server TCP provider failed to listen on [ 10.0.0.136 <ipv4> 2813]. Tcp port is already in use.

Resolution

The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid

SQL Server 2005 /2008 Express Edition Backup automation

SQL Database Backup Script

declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)   select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB','AdventureWorks') while @IDENT is not null begin     SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT /*Change disk location here as required*/     SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''C:\SQLBACKUP\Backup\'+@DBNAME+'.BAK''WITH INIT' PRINT @SQL     EXEC (@SQL)     select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 and DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB','AdventureWorks') end

 

DOS Batch files to backup the databases

@echo off c: if not exist c:\SQLBackup\Logs MD C:\SQLbackup\Logs if not exist c:\SQLBackup\Backup MD C:\SQLbackup\Backup cd c:\SQLBackup Echo Backup Started >> c:\SQLBackup\Logs\SQLBAckup.log Date /T > c:\SQLBackup\Logs\SQLBAckup.log Time /T >> c:\SQLBackup\Logs\SQLBAckup.log Echo. echo *************************************************************************** >> c:\SQLBackup\Logs\SQLBAckup.log isql -S (local)\Express -E -i c:\sqlbackup\backup.sql >> c:\SQLBackup\Logs\SQLBAckup.log echo *************************************************************************** >> c:\SQLBackup\Logs\SQLBAckup.log echo. Date /T >> c:\SQLBackup\Logs\SQLBAckup.log Time /T >> c:\SQLBackup\Logs\SQLBAckup.log echo Backup Finished >> c:\SQLBackup\Logs\SQLBAckup.log echo. echo *************************************************************************** >> c:\SQLBackup\Logs\SQLBAckup.log

 

Folders will look like,

 
Schedule it on Windows Task Scheduler

That's it the SQL Server 2005 / 2008 Express Edition database backup is automated now!

SQL Server Lever collation change

1. Backup all databases.(both system and User)

2. If applicable, backup DTS packages, Jobs, SSIS packages, mailing profiles or other objects installed by the application.

3. Script/take screenshot of logins with level of access on server level as well as the database level, user objects for example - tables, functions, types and stored procs, extended stored procs, if any on model or master db.

4. Detach all the user databases.

5. Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI


6. Check the tempdb collation and it should be ?Latin1_General_CI_AS? create users with help of screenshot/Script and restore user objects from step 3. Run scripts for functions and types if any.

7. Attach user databases from step 4 / Restore all the databases from the backup from step 1 and all the objects from the backup taken in step1. Refer client mail for further clarifications.

Error message when you open SQL Server Configuration Manager in SQL Server 2008: "Cannot connect to WMI provider. You do not have permission or the se

While opening of SQL Server Configuration Manager if you get error message of

"Cannot connect to WMI provider. You do not have permission or the server is unreachable"
Cause:
This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server 2008. The 32-bit instance and the 64-bit instance of SQL Server 2008 share the same WMI configuration file. This file is located in the %programfiles(x86)% folder.

Workaround:
To work around this problem, type the following command, and then press ENTER:
mofcomp "%programfiles(x86)%\Microsoft\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Refer the KB# 956013

The SQL Server Service Broker or Database Mirroring transport is disabled or not configured

MOM raised alert of "The SQL Server Service Broker or Database Mirroring transport is disabled or not configured".

The Service Broker is enabled by default in SQL Server 2005. Service Broker is enabled by default when a new database is created. Please try to run the following query to see the Broker option on a database.

SELECT name, is_broker_enabled FROM sys.databases;

This alert is triggered by event 9666 in the Application Event log for the OpsMgr database server.However, this event is always generated when you restart SQL because neither the Service Broker nor Database Mirroring are enabled for the Master or
Model databases. "SELECT is_broker_enabled FROM sys.databases" identifies
that the Service Broker is enabled for all other DBs. The alert is a bit counter productive if the rule is there to try to identify which databases are missing this option that actually need it; e.g. the OpsMgr db.Currently my only option is to disable the rule.

How to Script Database Schema

1. On the database schema to be scripted out, right click
2. Select Tasks --> Generate Scripts
3. Select all the objects in the selected databases
4. Select the appropriate General & Table Options
5. Select the output format

Testing a Connection from Client to SQL Server using UDL

A connection from client to SQL Server can easily be checked with the help of a .udl file. This is also helpful for building a connection string.

Step 1 - Create a .udl file
Create a text file and name it for example test.udl
Double click on test.udl to open the connection dialog.

Step 2 - Choose the type of connection
Go to the first tab “Provider”
Choose “Microsoft OLE DB Provider for SQL Server” or any other provider according to your needs.

Step 3 - Set up connection properties
Go to the second tab “Connection”, choose the server, enter username/password and choose a database from the drop down list.

Step 4 - Connection String
Once you click on “OK” a connection string based on your connection settings is written to test.udl. After you open test.udl you will find a connection string like this.

[oledb]; Everything after this line is an OLE DB initstringProvider=SQLOLEDB.1;Password=wrf%32g;Persist Security Info=True;User ID=siva;Initial Catalog=msdb;Data Source=SQLTEST

CPU usage reaching 100% with SP3 on SQL Server 2005

Symptom:
You install an instance of Microsoft SQL Server 2005 on a computer that has many processors. You upgrade to SQL Server 2005 Service Pack 3 (SP3) or to Microsoft SQL Server 2008. You run a query that uses the Like operator in the WHERE clause or that uses a string comparison function, such as the CharIndex() function. When you run the query many times on multiple connections at the same time, the CPU usage reaches 100 percent, and the server performance decreases significantly.

Then refer the Microsoft article and apply the cumulative update

No Surface Area Configuration (SAC) Tool in SQL Server 2008

In SQL Server 2008, Surface Area Configuration (SAC) Tool has been added to the deprecated feature list.

So how to do the functionality of SAC configuring Service, Connection and features?

1. For Instance and Database Engine Features:
Select Facets on Properties of Instance / Database on SQL Server Management Studio.

2.For Connections, Protocols and StartUp options:
Use SQL Server Configuration Manager

3.SSAS Features:
It can be done at Property Settings

4.SSRS Features
It can be done by editing the RSReportServer.config file or by using the facets feature

Feature Pack for Microsoft SQL Server 2005 and SQL Server 2008

Please have a look on the Feature pack of SQL Server 2005 and SQL Server 2008.

SQL Server 2005

SQL Server 2008

SQL Agent fails on SQL Server 2005

The error log from starting sqlagent is the following:
[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
[165] ODBC Error: 0, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
[000] Unable to connect to server '\SQL2005'; SQLServerAgent cannot start
[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
[165] ODBC Error: 0, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
[382] Logon to server '\SQL2005' failed (DisableAgentXPs)
[098] SQLServerAgent terminated (normally)

Resolution:
-----------

Client side setting would have messed up. Please go to SQL Server Configuration Manager and click properties for "SQL Native Client Configuration", if correct, you should be able to see two flags, one is "force client encryption", the other one is "trust server certificate" and value were set "yes" or "no".

If you see "No properties are available", that means your client registry key was messed up and leads to the connection error . To fix this, suggest reinstall SNAC(SQL Native Client). Just run the sqlncli.msi

Microsoft SQL Server 2008 advantages over Oracle 11g

SQL Server compared to Oracle

Microsoft SQL Server 2008 advantages over Oracle 11g

* Better Performance and Scalability: SQL Data Services, Filtered Indexes, Advanced Sparse Columns, Column Prefix Compression
* Better Security: Module Signing using Certificate, and integrated updates and patching
* Better Business Intelligence (BI) tools: Optimized cube designers, on-demand processing and instance-based rendering, optimized integration with the 2007 Microsoft Office system

Find more on here

Get up to 25% off Select Microsoft Certification Exams

Get Your Voucher Code for your Discounted Exams

Find the exam of your choosing from the lists below and select "Get Voucher," complete the registration form and your discounted voucher code will be emailed to you. Once you have obtained your code, plan and schedule your next exam at http://www.prometric.com/Microsoft/default.htm.

http://www.prometric.com/microsoft/careeroffer.htm

* Step 1: Using the voucher number, schedule and pay for your initial exam via Prometrics's web site
o You will need to have your voucher number available during the registration process.
* Step 2: Take your exam.

Candidates must register, schedule and take their discounted exams by December 31

How to reset Internel Protocol TCP / IP

One of the components of the Internet connection on your computer is a built-in set of instructions called TCP/IP. TCP/IP can sometimes become damaged or corrupted. If you cannot connect to the Internet and you have tried all other methods to resolve the problem, TCP/IP might be causing it.

Use the command below:
netsh int ip reset c:\resetlog.txt

Reference : How to reset Internet Protocol (TCP/IP)

You may be unable to use a Cisco VPN client and IPsec at the same time in Windows XP

You may be unable to use a Cisco VPN client and IPsec at the same time in Windows XP or in Windows 2000 or in Windows Server 2003

You may be unable to use the following components at the same time in Microsoft Windows XP, in Microsoft Windows 2000, or in Microsoft Windows Server 2003 Standard Edition with Service Pack 1 (SP1):

* A Cisco virtual private network (VPN) client
* The Internet Protocol security (IPsec) implementation that is built into Windows XP, Windows 2000, and Windows Server 2003

In this situation, you cannot use a VPN tunnel to access resources on an enterprise network on which Domain and Server Isolation policies are deployed.

Refer this here

Do we need SSIS to create Maintenance Plan

Before SQL Server 2005 SP2 was released Maintenance plans were integreated with SSIS but after SP2 Microsoft has changed that and we do not need SSIS to be installed to run maintenance jobs. And it carried over to SQL Server 2008 too.

How to change the owner of a maintenance plan

Scenario 1:
-------------
One of our members of staff had left and we had the usual case of a few jobs failing with:

"Unable to determine if the owner (DOMAIN\xxx) of job has server access (reason: error code 0x534. [SQLSTATE 42000] (Error 15404))."

Scenario 2:
------------
A job for running a nightly transaction log maintenance plan was created and owned by an account that was a member of the Domain Admins group. The account was subsequently removed from the Domain Admins group and the job failed (owner did not have server access), as expected.

The job owner was changed to another account that is a member of the Domain Admins group, and the job ran successfully for seven days. On the eighth day, another user, also a member of Domain Admins group, edited the maintenance plan to add a database. After the maintenance plan was saved, job ownership reverted to the original job creator (no longer a Domain Admin) and the job failed on its next scheduled run.

/*Here's how to change the owner of a maintenance plan to dbo in SQL Server 2005*/
--to find the name and owner of the maintenance plan
--select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
--select * from sysusers

UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'

For SQL Server 2008:

update msdb.dbo.sysssispackages
set [ownersid] = suser_sid('sa')
where [name] = 'MaintenancePlan'

SQL Server 2000 Service account privileges

For SQL Server 2000, if you do not want the SQL Server or the SQL Server Agent startup account to be a member of the Local Administrators Group, then the startup account for the MSSQLServer service and the SQLServerAgent service (either a local Windows NT account, or a domain Windows NT account) must have these user rights:

* Act as Part of the Operating System = SeTcbPrivilege
* Bypass Traverse Checking = SeChangeNotify
* Lock Pages In Memory = SeLockMemory
* Log on as a Batch Job = SeBatchLogonRight
* Log on as a Service = SeServiceLogonRight
* Replace a Process Level Token = SeAssignPrimaryTokenPrivilege