My Favorite Blogs

Friday, November 27, 2009

Some useful scripts for DBAs

Every day in our life as DBA, we may encounter these queries in our environments. Hope this basic queries for SQL server 2005/2008 would stay as repository..



1. Whenever SQL server restarts, it kicks off the startup procedures. How do you identify the startup procedures in your system?

Answer:

SELECT ROUTINE_NAME FROM MASTER.INFORMATION_SCHEMA.ROUTINES WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1

2. In order to fire those startup procedures, SQL Server should be configured for startup procs. How do you know whether SQL server is configured to check for Startup procedures?

Answer:

USE MASTER
GO

SELECT VALUE, VALUE_IN_USE, DESCRIPTION FROM SYS.CONFIGURATIONS WHERE NAME = 'scan for startup procs'
GO



It requires SQL server restart to take into effect..


3. How to execute procedures automatically whenever SQL server starts?

Answer:

USE MASTER
GO

EXEC SP_PROCOPTION usp_capture_process, 'STARTUP', 'ON'
GO

Importantly note that startup procedures should be in master database.

4. Filegroup can hold datafile/tables. How can I create a filegroup on a database?

Answer:

USE SQLDbops
GO

ALTER DATABASE SQLDbops ADD FILEGROUP FG_ViewOnly
GO

5. How can I add files to a filegroup?

Answer:

ALTER DATABASE SQLDbops ADD FILE ( NAME = SQLDbops_data1,
FILENAME = 'I:\SQLDbops_data1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP FG_ViewOnly
GO

6. How can I add a table to a filegroup?

Answer:

CREATE TABLE dbo.SQLDbopsOwners(ID int IDENTITY(1,1) NOT NULL,Name VARCHAR(150) NOT NULL,Areas VARCHAR(100) NOT NULL,LocationID INT NOT NULL, Location VARCHAR(100)) ON FG_ViewOnly
GO

7. How can I add an Index to a filegroup?

Answer:

CREATE INDEX IX_SQLDbopsOwners_LocationID ON dbo.SQLDbopsOwners(LocationID) ON FG_ViewOnly
GO

8. How can I move an object from a Primary file group to another filegroup?

Answer:

Let us take different scenarios. Table with Clustered Index and Table without Clustered Index.

Table with Clustered Index:

Suppose a table has the Clustered Index defined, hence moving the clustered Index will move the table along..
CREATE CLUSTERED INDEX IDX_SQLDBops_RenID ON dbo.SQLDbopsLicense(RenewID) ON FG_ViewOnly
GO

To move Non-Clustered index:

CREATE INDEX NC_IDX_LocID ON dbo.SQLDbopsLicense(LocationID) WITH (DROP_EXISTING = ON) ON FG_ViewOnly
GO

Table without Clustered Index:

Suppose if a table doesn't have any clustered Index, create a clustered Index on the filegroup where you want to move and then drop it..

CREATE CLUSTERED INDEX IDX_SQLDBops_RenID ON dbo.SQLDbopsLicense(RenewID) ON FG_ViewOnly
GO


DROP INDEX IDX_SQLDBops_RenID ON dbo.SQLDbopsLicense

9. How can I determine objects stored in which filegroup?

Answer:

USE [SQLDbops]
GO
SELECT sao.[name], sao.[type], si.[name], si.[index_id], sf.[name]FROM sys.indexes si INNER JOIN sys.filegroups sf ON si.data_space_id = sf.data_space_id INNER JOIN sys.all_objects sao ON si.[object_id] = sao.[object_id] WHERE si.data_space_id = 2
GO

data_space_id is id of filegroup where you want to look at..

below query helps to identify data_space_id of that filegroup.


USE [SQLDbops]
GO
SELECT data_space_id FROM sys.filegroups WHERE name = 'FG_ViewOnly'
GO

10. Usually in production servers, mostly windows account group will be given the permissions. How to know who are all members of that group account?

Answer:

xp_logininfo [Columbus\Webdevelopers]
GO

11. How to know schema difference between the tables on different servers?

tablediff -sourceserver "prod.columbus.sqldbops.com" -sourcedatabase "SQLDbops" -sourcetable "SQLDBopsOwners" -destinationserver "prod2.columbus.sqldbops.com" -destinationdatabase "SQLDbops2" -destinationtable "SQLDBopsOwners2" -q

it's DOS command. Table diff will be located in Binn directory.
Other way is to use master..xp_cmdhsell..

12. Date and Time functions?

Answer:

SELECT {fn curtime()}
SELECT {fn now()}

SELECT GETDATE()

13. How to find different collations available in a server?


Answer:

SELECT * FROM ::fn_helpcollations()

14. How to know Server collation?

Answer:

sp_helpsort

15. How to know collation of a database?

Answer:

SELECT DATABASEPROPERTYEX('SQLDbops','Collation');

16. How to remove windws policy for a SQL login?

Answer:

Two steps to follow:

In order to remove windows policy for a user, it should have MUST_CHANGE option to be OFF

1.To change the password and keep the MUST_CHANGE flag:

ALTER LOGIN surpal WITH PASSWORD = 'Y' UNLOCK MUST_CHANGE

2. To uncheck the checkboxes for expiration and/or policy:

ALTER LOGIN surpal WITH CHECK_EXPIRATION = OFF

ALTER LOGIN surpal WITH CHECK_POLICY = OFF

16. How to change password for a SQL login?

Answer:

ALTER LOGIN MyLogin WITH PASSWORD = 'newpassword'

17. How to check if DAC on your session?

SELECT t2.session_id, t2.program_name, t2.host_name, t1.name FROM sys.tcp_endpoints as t1 JOIN sys.dm_exec_sessions as t2 ON t1.endpoint_id=t2.endpoint_id WHERE t1.name='Dedicated Admin Connection'

No comments:

Post a Comment