Showing posts from May, 2011

Monitor, Start, Stop SQL server services in query analyzer.

Monitor Sql Server Services:exec xp_servicecontrol 'querystate','mssqlserver'
exec xp_servicecontrol 'querystate','sqlserveragent'
exec xp_servicecontrol 'querystate','sqlbrowser'
exec xp_servicecontrol 'querystate','msdtc'
exec xp_servicecontrol 'querystate','MSSQLserverOLAPservice'
exec xp_servicecontrol 'querystate','Reportserver'
--Output : CurrentServiceState ------------------------------------------------------------------------------------------------------------------------------------ Running.
(1 row(s) affected)
For non sql services:
exec xp_servicecontrol ‘querystate’,'DHCPServer'

Start and Stop Sql services:
exec xp_servicecontrol ‘START’,'mssqlserver'
exec xp_servicecontrol 'STOP','sqlserveragent'

Hope above query will be useful to check sql server services state without service console help.

SQL Database Creation procedure:

Steps to be followed from the start:
Step1: Expand Databases in Query analyzer->Right Click->Choose New Database->Give database name.
Step 2: Find where mdf & ldf files are stored for another database in that instance using following query for comparison. select * from sysfiles

Place mdf and ldf files for the new database as like below path in the wizard.
Step 3: check mdf & ldf file path for new database is same as another existing database in the instance.
Step 4: set recovery model specific to the environment like Dev-Simple, Test-Simple, and Production-Full.
Step 5: Finally create the database.
Step 6: Give access for required users.
Step 7: Add new database in the existing maintenance plan like Fullbackup, Transaction backup, Rebuild Index plan, etc.
That’s’ it. Hope you will store data.

If remote desktop sessions exceeded means, how will you connect into windows server?

Unable to login into windows server due to RDP sessions limit exceeded.
Log into a server on the domain and open a windows command prompt To view current connections on a server and type the following qwinsta /server:<server name or ip address> When the results are displayed pick an ID value that has a state of “Disc” (Disconnected) To kill a session pick the ID with the state of “Disc” and type the following rwinsta <ID Value> /server:<server name or ip address> The remote desktop session has been killed and you are now able to Remote Desktop into your original SQL Server.  If you are asking yourself what does qwinsta and rwinsta mean… well here they are:
qwinsta = Query WINdows STAtion
rwinsta = Reset WINdows STAtion

Hope you can connect now.

Query to change the owner of the DTS package in sqlserver2000:

DTS Package owner change:

Parameters descriptive:
Package_name ->specify name of the package.
Package_id ->get the id value from below output for the corresponding packages.
use msdb GO select * from sysdtspackages
Moreover we can take idvalue for the latest ‘created date’ column from the above table.

List all tables and their Row Counts by a single query in a database?

Scenario:After replication setup or reinitialization we need to check the row counts of 2 databases in sql server2005 to verify the replication.Solution:By running below query ,we can able to find out tables and their corresponding row counts in a single database.
Select as TableName,
Max(i.rowcnt) as TotalRows
sys.sysobjects o inner join sys.sysindexes i on
-–Only User defined tables
Where o.xtype='U'

Group by

Role Membership scripts to be used before database refresh.

Run below scripts to get all the roles permission scripts for a specific database.

--Role Memberships'
SELECT --rm.role_principal_id, 'EXEC sp_addrolemember @rolename =' + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS '--Role Memberships' FROM sys.database_role_members AS rm ORDER BY rm.role_principal_id

--Object Level Permissions' SELECT CASE WHEN perm.state != 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) + perm.permission_name + SPACE(1) + 'ON '+ QUOTENAME(Schema_NAME(obj.schema_id)) + '.' + QUOTENAME( collate Latin1_General_CI_AS_KS_WS + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME( + ')' END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME( + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + '…

How do we identify users and their respective role for specific database in a sql server?

Identify users and their respective role for specific database in a sql server by below method?

SELECT AS [User Name],  SDP.type_desc AS [User Type],  UPPER( AS [Database Role] FROM sys.database_principals SDP INNER JOIN sys.database_role_members SDRM ON SDP.principal_id=SDRM.member_principal_id INNER JOIN sys.database_principals SDPS ON SDRM.role_principal_id = SDPS.principal_id

Could you please share any other methods?

How do we find out all the jobs related to the single database in a sql server?

How do we find out all the jobs related to the single database in a sql server?

USE msdb
SELECT * FROM sysjobs (NOLOCK) WHERE job_id IN
(SELECT job_id FROM sysjobsteps (NOLOCK) WHERE database_name
= 'database_name' OR step_name LIKE '%database_name%')

Share me any others methods ?

Error while export tables data only from production database to test server database.

While exporting all tables’ data only from one database in production server to test server database, the error I got as shown below.
TITLE: Microsoft.SqlServer.DtsTransferProvider ------------------------------ s description=Executing the query "TRUNCATE TABLE [database_name].[dbo].[tablename] " failed with the following error: "Cannot truncate table ‘[database_name].[dbo].[tablename]’because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} For help, click:
------------------------------ BUTTONS:
1. Run below scripts in the destination database and save the both scripts for future use.