Restart SQL Server and SQL Server Agent from Command Prompt.

Many times I need to restart SQL Server service and SQL Server Agent service in my testing server.. Generally people used to go at Control Panel->Administrative Tools->Services, find SQL Server service or/and SQL Server agent service and start, restart or stop it. This is general practice but I would like to do this from command prompt itself.

Syntax is:

Net Stop mssql$YourSQLServerInstanceName
Net Start mssql$YourSQLServerInstanceName
Above command is used to start and stop SQL Server Service.  Now, here is the command for SQL Server agent.
Net Stop sqlagent$YourSQLServerInstanceName
Net Start sqlagent $YourSQLServerInstanceName
My instance name of SQL Server 2008 is “SQL2k8” so my command would be
Net Stop mssql$SQL2K8
Net Start mssql$SQL2K8
Net Stop sqlagent$SQL2K8
Net Start sqlagent $SQL2K8
Indeed I have one batch file which do this stuff for me. Just go to command prompt and run .BAT file is really easy than going to Control Panel->Administrative Tools->Services, find services and restart it.
Look at the screen below for more details.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements

Find out column name, data type and maximum number of character in that column in SQL Server 2008

Yesterday I got one question about how to find column name, data_type and maximum number of character used in that column if it is varchar and if it is numeric than maximum number in that column. Basically you can find column_name and Data_type from the Information_Schema.Columns but it won’t return length of real data inserted into table so I tried to achieve it logically. Look at the script.
–using AdventureWorks database for demo
–if you don’t have AdventureWorks db
–you can use your own
use adventureworks
GO
declare @sql varchar(max), @table sysname
–going to show you demo on Person.Contact table
–in adventureworks database,
–you can change your own table name
select @table=‘Contact’, @sql=
select @sql=
case when character_maximum_length is null then
@sql+‘select
    ”’+column_name+”’ as column_name,
    ”’+data_type+”’ as data_type,
    max(‘+column_name+‘) as column_length
    from ‘+Table_Schema + ‘.’+ Table_Name+‘ union all ‘
else
    @sql+‘select
    ”’+column_name+”’ as column_name,
    ”’+data_type+”’ as data_type,
    max(len(‘+column_name+‘)) as column_length
    from ‘+Table_Schema + ‘.’+ Table_Name+‘ union all ‘
end
from information_schema.columns
where table_name=+@table+ and  data_type not in (‘xml’,‘bit’,‘datetime’,‘uniqueidentifier’)
set @sql=left(@sql,len(@sql)-9)
–print @sql
exec(@sql)
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Row count in big tables with DMV DM_DB_Partition_Stats and SP_SpaceUsed

I have seen people counting row by using COUNT or may be COUNT_BIG, if it is very big table, aggregate function. When you go for COUNT function, you will scan full table and it may take very big amount of time, especially when you are having VERY BIG table with few millions of rows. So .NET developer also uses same COUNT aggregate function in .NET front end application which is really very time consuming.
I just see one of my .NET developers was doing this for the table, having 150 million rows so I corrected him with following two ways and thought to share it with my blog readers.
Rather than scanning full table I always prefer to use either DMV or SP_SpaceUsed stored procedure. In my early days few years back, I was using SP_MSTABLESPACE but in SQL Server 2005+, SP_MSTABLESPACE is just for backward compatibility so I do not recommend using SP_MSTABLESPACE and promoting dm_db_partition_stats and SP_Spaceused.

Let us see  TSQL for each.

–Method1
SELECT
SUM(row_count) as TotRows
FROM
sys.dm_db_partition_stats
WHERE
object_name(object_id) = ‘YourTableName’
AND (Index_Id = 1)
GO
–Method2
SP_Spaceused ‘YourTableName’
GO
–Method3
SP_MSTABLESPACE ‘YourTableName’
GO
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Row count in big tables with DMV DM_DB_Partition_Stats and SP_SpaceUsed

I have seen people counting row by using COUNT or may be COUNT_BIG, if it is very big table, aggregate function. When you go for COUNT function, you will scan full table and it may take very big amount of time, especially when you are having VERY BIG table with few millions of rows. So .NET developer also uses same COUNT aggregate function in .NET front end application which is really very time consuming.
I just see one of my .NET developers was doing this for the table, having 150 million rows so I corrected him with following two ways and thought to share it with my blog readers.
Rather than scanning full table I always prefer to use either DMV or SP_SpaceUsed stored procedure. In my early days few years back, I was using SP_MSTABLESPACE but in SQL Server 2005+, SP_MSTABLESPACE is just for backward compatibility so I do not recommend using SP_MSTABLESPACE and promoting dm_db_partition_stats and SP_Spaceused.

Let us see  TSQL for each.

–Method1
SELECT
SUM(row_count) as TotRows
FROM
sys.dm_db_partition_stats
WHERE
object_name(object_id) = ‘YourTableName’
AND (Index_Id = 1)
GO
–Method2
SP_Spaceused ‘YourTableName’
GO
–Method3
SP_MSTABLESPACE ‘YourTableName’
GO
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Read default SQL Server port and change it in SQL Server 2008

Listening port plays really very vital role in any version of SQL Server. There are some port number like 1433, 1533, 1434 etc. are really widely known and famous so whenever hackers try to scan port of your SQL Server, they first try to scan these ports as those are very famous.
It is really good practice to change default SQL Server port to something else from 1433. After reading this short message, if you are not experience DBA and SQL Server developer, you might think, how can read the port of SQL Server? It is really very easy with few different ways.
Read/Change Default SQL Server Port:
Method 1:
1.)   
Go to Start->All Programs->Microsoft SQL Server 2008->Configuration Tools->SQL Server Configuration Manager
2.)    From the configuration manager, expand “SQL Server Network Configuration” from left hand side tree.
3.)    Now click on “Protocols for YourSQL ServerInstance”
4.)    From the right hand side, you can find “TCP/IP”, right click on it and click on property
5.)    From the dialog box, click on the “IP Addresses” tab and look at port no in “TCP Port” property under “IP ALL”.
6.)    If you want to change your port, change it in “TCP Port” and Restart your SQL Server instance
Method 2:
Read the port from your registry with below code snippet.
DECLARE @tcp_port nvarchar(10)
EXEC xp_regread
@rootkey    =    ‘HKEY_LOCAL_MACHINE’,
–SQL2K8 is my instanace name of SQL Server
–may be different in your case
@key        =    ‘SOFTWARE\MICROSOFT\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQLServer\SUPERSOCKETNETLIB\TCP\IPAll’,
@value_name    = ‘TcpPort’,
@value        =    @tcp_port OUTPUT
select @tcp_port
If you wish to change port # from registry, open “regedit” from command prompt, go to the path given in above SP. Path is already provided in @key argument. Double click on “TcpPort” key and change the value right from there.
I have used XP_RegRead stored procedure to read registry. Sometime back I have used other system stored procedure to read registry and find default installation path of your sql server. The name of that stored procedure is XP_Instance_RegRead.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Read registry from SQL Server with xp_instance_regread and find the path of your SQL Server installation

Some machines are really mess up with couple of successful and unsuccessful installation/un-installation of SQL Server and the result is you can find program files/Microsoft SQL Server folder in couple of drive. If you want to find current running installation of SQL Server than there are few different ways. Out of which I am going to show you couple of different approaches.
Method 1:
You can query sysaltfiles system catalog resides in MASTER database. You can see the path of system databases especially MASTER database. Most probably master database would be in the same folder of SQL Server.
Here is the TSQL:
select * from master..sysaltfiles
Method 2:
There is a system stored procedure in Master database, named “xp_instance_regread” which can help you to read registry.  There is a specific location in system registry which will give you path of the installation directory. This method is more appropriate than previous one. Here the code snippet of the same.
declare @regreader int, @directory nvarchar(4000)
exec @regreader = master.dbo.xp_instance_regread
     N’HKEY_LOCAL_MACHINE’,
     N’Software\Microsoft\MSSQLServer\Setup’,
     N’SQLPath’,
      @directory output, ‘no_output’
select @directory AS InstallationDirectory
GO
If the user you are using while running above snippet doesn’t have permission to read registry, you might greeted with permission error.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Read registry from SQL Server with xp_instance_regread and find the path of your SQL Server installation

Some machines are really mess up with couple of successful and unsuccessful installation/un-installation of SQL Server and the result is you can find program files/Microsoft SQL Server folder in couple of drive. If you want to find current running installation of SQL Server than there are few different ways. Out of which I am going to show you couple of different approaches.
Method 1:
You can query sysaltfiles system catalog resides in MASTER database. You can see the path of system databases especially MASTER database. Most probably master database would be in the same folder of SQL Server.
Here is the TSQL:
select * from master..sysaltfiles
Method 2:
There is a system stored procedure in Master database, named “xp_instance_regread” which can help you to read registry.  There is a specific location in system registry which will give you path of the installation directory. This method is more appropriate than previous one. Here the code snippet of the same.
declare @regreader int, @directory nvarchar(4000)
exec @regreader = master.dbo.xp_instance_regread
     N’HKEY_LOCAL_MACHINE’,
     N’Software\Microsoft\MSSQLServer\Setup’,
     N’SQLPath’,
      @directory output, ‘no_output’
select @directory AS InstallationDirectory
GO
If the user you are using while running above snippet doesn’t have permission to read registry, you might greeted with permission error.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com