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 :

Current Service State

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

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.

G:\MSSQL\data\newdatabase_name.mdf
F:\MSSQL\logs\newdatabase_name_log.ldf

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?

Scenario:

Unable to login into windows server due to RDP sessions limit exceeded.

Solution:

1. Log into a server on the domain and open a windows command prompt
2. To view current connections on a server and type the following
3. qwinsta /server:<server name or ip address>
4. When the results are displayed pick an ID value that has a state of “Disc” (Disconnected)
5. To kill a session pick the ID with the state of “Disc” and type the following
6. 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:

sp_reassign_dtspackageowner 'Package_name’, ‘Package_id’, 'Newownerloginname’

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

 o.name as TableName,

 Max(i.rowcnt) as TotalRows

from

 sys.sysobjects o inner join sys.sysindexes i on o.id=i.id

-–Only User defined tables

 Where o.xtype='U'

Group by o.name

Role Membership scripts to be used before database refresh.

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

Script:

--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(obj.name) collate Latin1_General_CI_AS_KS_WS + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name) + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions' FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id LEFT JOIN sys.columns AS cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id ORDER BY usr.name --Database Level Permissions' SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) + perm.permission_name + SPACE(1) + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name) COLLATE database_default + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions' FROM sys.database_permissions AS perm INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE --usr.name = @OldUser --AND perm.major_id = 0 ORDER BY perm.permission_name ASC, perm.state_desc ASC

   Then keep the generated scripts to run after database refresh for to retain the existing roles in the database.

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?

Script:

SELECT  SDP.name AS [User Name],  SDP.type_desc AS [User Type],  UPPER(SDPS.name) 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 GO 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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

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

BUTTONS:

OK

Solution:

1. Run below scripts in the destination database and save the both scripts for future use.

n      
-- DROP CONSTRAINTS  
-- 
SELECT 'ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+ 
' DROP  CONSTRAINT ' + '[' + f.name  + ']' 
FROM .sys.foreign_keys AS f 
INNER JOIN .sys.foreign_key_columns AS fc 
ON f.OBJECT_ID = fc.constraint_object_id 
 
 
--  
-- RECREATE CONSTRAINTS 
-- 
SELECT 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id)+ ']' + 
' ADD CONSTRAINT ' + '[' +  f.name  +']'+ ' FOREIGN KEY'+'('+COL_NAME(fc.parent_object_id,fc.parent_column_id)+')' 
+'REFERENCES ['+OBJECT_NAME (f.referenced_object_id)+']('+COL_NAME(fc.referenced_object_id, 
fc.referenced_column_id)+')' as Scripts 
FROM .sys.foreign_keys AS f 
INNER JOIN .sys.foreign_key_columns AS fc 
ON f.OBJECT_ID = fc.constraint_object_id 

2. Then Run the saved output of DROP CONSRAINTS scripts to drop the all constraints.

3. Then truncate all tables by below method.

Use destinationdatabasename

Go

sp_tables

Copy all table_name column values and paste it in excel sheet.

Then join ‘TRUNCATE TABLE ‘with all tables in another excel column as like below

 Eg:

="TRUNCATE TABLE  " &A1 &" "

Table_name

TRUNCATE TABLE  Table_name

4. Then run the saved output of RECREATE CONSTRAINTS scripts to recreate all the dropped constraints.

5. Finally run the export and import wizard to copy all the tables’ data with no issue.

I hope you can do export with no issue.