SQL Server 2005/2008 : Generate scripts all jobs

SQL Server 2005: How to generate scripts for all jobs?
SQL Server 2005: Script all jobs

There are some TSQL methods that will generate the scripts to create all jobs but there is one major constraint with those scripts.

They all use a variable like @command which is usually nvarchar or varchar and this limits the number of characters in the job step. If you have a large TSQL in the job step then you can not use those TSQL queries to script out all job.

The best method to do this is using a VB Script that I found online. I am not the author of the script but wanted to list it here.

Dim conServer
Dim fso
Dim iFile
Dim oJB
Dim strJob
Dim strFilename
Const ioModeAppend = 8

Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect "yourservername"

strFilename = "C:\DR_Files\CreateJobs.sql"

For Each oJB In conServer.JobServer.Jobs
    strJob = strJob & "--------------------------------------------------" & vbCrLf
    strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
    strJob = strJob & "--------------------------------------------------" & vbCrLf
    strJob = strJob & oJB.Script() & vbCrLf
Next
Set conServer = Nothing

Set fso = CreateObject("Scripting.FileSystemObject")
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing

Save this to a file with the extension 'vbs' and execute it on a command line or via a command line job step using the following command. Make sure you edit the file to use 'yourservername' and also change the path to the output file. In the above example I am saving the file to C:\DR_Files\CreateJobs.sql.

C:\WINDOWS\system32\cscript.exe C:\DR_Files\Generate_Jobs.vbs

This should create a file that can be used to create all the jobs. Make sure you parse the file in a query window to make sure you can create the scripts. But DO NOT EXECUTE the output file.

SQL Server 2005/2008 : How to shrink TEMPDB?

On certain occasions you might see a necessity to shrink the data files for TEMPDB.
Before we proceed to answer the question, please investigate why do you want to shrink TEMPDB.

Firstly, it is never a good idea to shrink TEMPDB. There must be a reason/query/process that needs TEMPDB to be this large so if you shrink the TEMPDB then there is every chance that it might grow to the same size again.

So I would check the current open transaction in TEMPDB using the following command

USE TEMPDB

DBCC OPENTRAN

This should give you the SPID of the transaction and you can use that to find the query text. Lets say that the SPID of the current active transaction is 65 then run the following command and get the info.

DBCC INPUTBUFFER(65)

Take a look at the query and see it is going to get executed again. If yes, then shrinking the TEMPDB might not be the best course of action.

At this point if you still want to shrink the tempdb then try the dbcc shrinkfile command first. If that does not do the trick and you are running out of disk space then the best way to reduce the size of tempdb is to RESTART SQL Server instance. 
This will immediately reduce the size of TEMPDB. I would choose this action rather than running out of disk space and causing other problems with the database. 

SQL Server 2005/2008 : How to create Composite indexes?

Creating new indexes can be a significant change and it is important to note that adding unnecessary indexes is not a good strategy. While indexes lead to better READ performance they will reduce the WRITE performance on the tables.

If you have more than one SELECT query to tune and create indexes for then you have to analyze if there is one index that will satisfy all the queries. If one index won't suffice the queries then find the least number of indexes and their definitions that will improve the performance of the queries.

To illustrate the examples, I am going to use the following table 

CREATE TABLE [dbo].[table1](

            [col1] [int] NULL,

            [col2] [int] NULL,

            [col3] [int] NULL,

            [col4] [int] NULL

) ON [PRIMARY]

Consider the following SELECT statements and lets see what kind of indexes will help.

1) select col1, col2 from table1 where col1=10

2) select col1, col2, col3 from table1 where col1=10 and col3=3

3) select col3, col2 from table1 where col3=12

4) select col1, col2, col3, col4 from table1 where col1=10 and col3=12 and col4=15

Note: Any column that is part of the selected columns but is not part of the conditions in the WHERE clause, can be part of the included columns of the index. That column does not need to be part of the columns on which the index is created. In all the above queries col2 is part of the selected columns but not part of the WHERE clause.

For the first query all you need is an index on col1 that also includes col2. This will result in index seeks.

CREATE NONCLUSTERED INDEX [index1] ON [dbo].[table1]

( [col1] ASC ) INCLUDE ( [col2])

For the second query you will need an index on col1 and col3. Also include col2 in that index.

CREATE NONCLUSTERED INDEX [index2] ON [dbo].[table1]

( [col1] ASC, [col3] ASC ) INCLUDE ( [col2])

Note that since the first column in index2 is col1, it will lead to index seeks for the first query. Hence, index2 will make index1 redundant.

But for the third query the index ‘index2’ will not help. This query will lead to index scans. The order of the column in a composite index is important. In index2, col3 is not the first column in the order hence it will lead to index scans for any query which has a WHERE clause on just the col3.

 So the following index would be appropriate. This index will be created on col3 and include col2.

CREATE NONCLUSTERED INDEX [index3] ON [dbo].[table1]

( [col3] ASC ) INCLUDE ( [col2])

 

Now consider the 4^th query, to force an index seek you will have to create the following index.

CREATE NONCLUSTERED INDEX [index4] ON [dbo].[table1]

( [col1] ASC, [col3] ASC, [col4] ASC ) INCLUDE ( [col2])

Notice that this index4 will lead to index seeks for query 1 and 2. So there is no need create index1 and index2

If you analyze the indexes closely you will see that the Index4 will be sufficient for the Query 1, 2 and 4.

To force index seeks for all the queries mentioned above Index4 and Index 3 will be sufficient.

Silent mode or unattended installation of SQL Express

SQL Server 2005/2008 express edition install in silent mode.
SQL Server 2005/2008 express edition unattended installation.
SQL Server gives you an option to install the instance in a silent mode. All you have to do is to enter the specifications in a configuration file and install it. You will not be prompted to enter any values. This makes installing SQL Server a repeatable process and you can ensure that every time the specs are the same.

You need to be careful with saving the username/password for the service accounts in the config file. Make sure that you save the file in a secure location.

Steps to install SQL Server 2005 Express 32 bit in silent mode

1)      Download the file SQLServer2005_SSMSEE.msi to install the SQL Server Management studio and save it to a directory called ‘C:\SQLInstall\SSMS’

http://www.microsoft.com/download/en/details.aspx?id=8961

2)      Then download the SQLEXPR32.exe file from Microsoft and save it to a directory called ‘C:\SQLInstall’

http://www.microsoft.com/download/en/details.aspx?id=184

3)      Extract the install files for SQL Server Express using the following command and save it to ‘C:\SQLInstall\SQLExpress’

C:\SQLInstall\SQLEXPR32.exe /x

4)      Then save the following lines to a file called ‘C:\SQLInstall\SQLExpress_Install.ini’

[OPTIONS]

USERNAME=yourname

COMPANYNAME=yourcompanyname

ADDLOCAL=ALL

SQLACCOUNT="NT AUTHORITY\SYSTEM"

AGTACCOUNT="NT AUTHORITY\SYSTEM"

SQLBROWSERACCOUNT= "NT AUTHORITY\SYSTEM"

SQLBROWSERAUTOSTART=1

SQLAUTOSTART=1

AGTAUTOSTART=1

SECURITYMODE=SQL

SAPWD=yourpassword

5)      Then run the following commands to install SQL Server 2005 Express edition.

C:\SQLInstall\SQLExpress\start /wait setup.exe /settings C:\SQLInstall\ SQLExpress_Install.ini /qn

6)      Then run the following command to install Management Studio.

cd C:\SQLInstall

msiexec.exe /i “SQLServer2005_SSMSEE.msi” /qb /log C:\SQLInstall\SSMS.log

Get last backup information

Get the last full and log backup information for SQL Server 2005/2008

SELECT sdb.Name AS DatabaseName, sdb.recovery_model_desc,
COALESCE(CONVERT(VARCHAR(30), MAX(bus.backup_finish_date), 120),'-') AS LastBackUpTime
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name and bus.type='D'
GROUP BY sdb.Name, sdb.recovery_model_desc
order by sdb.Name


SELECT sdb.Name AS DatabaseName,sdb.recovery_model_desc,
COALESCE(CONVERT(VARCHAR(30), MAX(bus.backup_finish_date), 120),'-') AS LastBackUpTime
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name and bus.type='L'
where sdb.recovery_model_desc='FULL'
GROUP BY sdb.Name, sdb.recovery_model_desc
order by sdb.Name

If you need  commands to give the location of the backups then please leave a comment.

Move Primary key index to new filegroup

SQL Server Move index to a new filegroup
Recreating Primary key index

Today I had the task of moving the primary key index to a new file group. But before I explain the procedure, it is interesting to note the necessity to do this.

During the planning phase, we had recommended that the table data and the index data be placed on two different LUNs. This would give the best IO performance. But unfortunately the vendor made a slight error in giving us the DDL for the tables and indexes.

The CREATE table command also added the primary key constraints and specified the filegroup reserved for indexes for the primary key. I guess they thought since our recommendation was to have all indexes on a separate filegroup, they would create the primary key index also on the index filegroup.

But as most of the DBAs know the primary key index is nothing but the table itself. So when the primary key was created on the index filegroup it moved the data from the data filegroup to the index filegroup. So essentially we ended with both the data and the index on the same filegroup. The data filegroup was unused after this change.

But unfortunately the non clustered indexes had been created and the data was inserted.

Now if I tried to drop and recreate the primary key index then the non-clustered indexes would be rebuilt twice. So I had drop all indexes and then recreate them and this way I could avoid rebuilding the non clustered indexes twice.

Following is the summary of commands that I ran to achieve this.

1)      Script out the CREATE statements for the table including the CREATE statements for the indexes. (Always have a backup plan, if it helps take a backup of the database.

2)      Drop all the non clustered indexes.

            DROP INDEX [AK_Department_Name] ON [HumanResources].[Department]

WITH ( ONLINE = OFF )

3)      Drop the primary key index.

ALTER TABLE [HumanResources].[Department] DROP CONSTRAINT [PK_Department_DepartmentID]

4)      Create the new primary key index

ALTER TABLE [HumanResources].[Department] ADD  CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED

(

            [DepartmentID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Do not forget to mention the new filegroup for the primary key index.

5)      Create all the remaining non clustered indexes and specify the index filegroup.

CREATE NONCLUSTERED INDEX [AK_Department_Name] ON [HumanResources].[Department]

(

            [Name] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

      ALLOW_PAGE_LOCKS  = ON) ON [INDEXES]

Named instance perfmon counters

Collecting or adding perfmon counters for named instance for SQL Server.

Collecting and analyzing Performance Monitor (perfmon) counters for SQL Server and operating system is an integral part for the performance tuning. You can find SQL Server counters under the object ‘SQL Server’.

But on several occasions my colleagues have reported that they do not find the objects ‘SQL Server’.  This might be the case if the SQL Server has a named instance. In such scenarios the counters do not appear under SQL Server. To find the actual name run the following query.

use MASTER

select * from sysperfinfo where object_name like '%Buffer Manager%'

And you will find the actual counter names in the fist column. In the following screenshot the counter name is MSSQL$MIROR and you should find that in performance monitor.

All named instance counters objects start with ‘MSSQL$’ and followed by the name of the instance.

So look for counters starting with ‘MSSQL’ and not under ‘SQL Server’